Database results to array
Hard to title this one.
What I'm trying to do is put all var* into an array and sort using array_unique() from a database query
my workaround works but uses to many database queries
Select var1, var2, var3, var4 from vartable ..... (could be over 200 rows)
while($row = mysql_fetch_array($result))
{
if($row[var1] != ''){
update another database table set var1 to stuff
}
if($row[var2] != ''){
update another database table set var2 to stuff
}
if($row[var3] != ''){
update another database table set var3 to stuff
}
if($row[var4] != ''){
update another database table set var4 to stuff
}
}
there are a lot of faults with the above, updates duplicates, can be doing 800+ queries ...
Thought about using CONCAT() on the intial query, seperating each by comma into an array, then using array_unique() but .. I don't know, feel like I'm missing something, somewhere.
**stress does weird stuff to you**
Suzanne posted this at 20:04 — 15th July 2004.
She has: 5,507 posts
Joined: Feb 2000
This is probably very obvious to you, but what are you updating, exactly? I haven't been following along.
Busy posted this at 21:31 — 15th July 2004.
He has: 6,151 posts
Joined: May 2001
The first query takes all the images used in posts, the second updates them to "being used" or "not used" depending on the results.
Ideally there shouldn't be any doubles but it happens.
I have another part of this script which actually removes the images after 30 days of no use but it too is causing some problems as it's creating an overhead in phpmyadmin which needs repair or optimise to osrt it out. There is also a third part to it (all parts are in different places) that is fine.
Suzanne posted this at 22:40 — 15th July 2004.
She has: 5,507 posts
Joined: Feb 2000
by "used" what do you mean? (I feel really dense, I'm sorry, but I don't understand why you need to data mine to get this information?)
Busy posted this at 00:18 — 16th July 2004.
He has: 6,151 posts
Joined: May 2001
Sorry, "in use" or "not in use"
Example:
Say this forum allows us to upload 10 images for things, if the image is used in a post it is marked "in use" but if the image isn't being used (could be from never been used, post removed, edited ...) then shows "not in use". Thats the basic idea of it, it's more complex as I also use time for auto removal, and hiding etc which isnt needed for this section of code.
It's not really a big part of the site, just something to help keep some sanity on my part so can keep track of who, what when and sometimes why
The method I am playing with at the moment is opposite to what I posted above, am working with a join to find the images in use and setting them to "in use". It's three queries less per set.
Just found EXPLAIN so am changing stuff to suit.
Suzanne posted this at 03:23 — 16th July 2004.
She has: 5,507 posts
Joined: Feb 2000
I think I understand, but if that's going to be happening a lot you might be better to read the images into the database when they are uploaded, and have a lookup table for what post uses what image. When the post is gone, the image is deleted as well, or alternatively you could check the dates on the images with a cron job and prune from there?
vexcom posted this at 05:07 — 16th July 2004.
He has: 21 posts
Joined: Jul 2004
Not sure how to so it in php but i know it can be done, you can get the column list in SQL then select * and use the column list for your loop
I don't use php so this will be kinda vague, you will have to check out MySQL documentation, but it would work like so:
Select * from vartable .....
while($row = mysql_fetch_array($result))
{
if($row[$AAA] != ''){
update another database table set $AAA to stuff
}
this is only concept of course - im a coldfusion programmer (background in perl) i never reallly got into php, but that general idea should work the same in php...
well maybe it will give you an idea anyway -
- Wesley E. Warren, CEO - Vexcom Advanced Systems Development
Realty-SEO Real Estate Marketing
Freelance Graphic and Web Designers Directory - Join Free
"All limitations are self imposed" ~ Malaclypse The Younger
WebsiteDistrict posted this at 02:20 — 30th July 2004.
They have: 4 posts
Joined: Jul 2004
I'm not entirely clear about the problem here.
should be something like this. I can't give you my exact codes here since I lost it
<?php
function GetResults(){
$connection = CreateConnection(); // this is a a function i made to for my database connection
$QueryString = \"SELECT .....\";
$ResultSet = mysql_query($QueryString, $connection) or die('Cannot update session');
return $ResultSet;
}
$ArrayVariable = GetResults();
?>
Hope that helps.
Regards,
John
Website District
http://www.websitedistrict.net
John Vincent Mombay
Website District
Web Hosting that understands your needs.
http://www.websitedistrict.net
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.