CODEWITHSUNDEEP
phpmysql
decaye
decaye

Reputation: 107

select and update mysql same query

I'm running a query to select an array of id's from one table, so that I can update another table with the data from the resulting dataset.

//db query result
$query = "SELECT image_id FROM jos_jxgallery_images ORDER BY jos_jxgallery_images.image_id DESC LIMIT 25";
 $query_execute = mysql_query($query);
mysql_close($db_config);

 while ($items = mysql_fetch_array($query_execute)) {
 echo $items['image_id']; 
 echo '<br/>';
}

I think I need to do it in the while loop, I just have the echo there to see what's in items variable. That works ok. I think the thing to do is in the while loop..I'd like to replace the 'echoing' with an actual update SET query for my other table. Something like...

while ($items = mysql_fetch_array($query_execute)) {
$q = "UPDATE jx_gallery_images_ratings SET image_id ='".$items.""; 
mysql_query($q);

But the new table has no data. Is there just a better way to write this...maybe even as one query or something? Any help is appreciated.

EDIT: I should explain a little better. The table is empty, and I could go ahead and use an insert from one table to another just to get the id's there. However, after that...in a way it is somewhat a 'temp' table. But not really. Whatever ordering of image_id's I have created in my SELECT query from my first table (There are other rows to sort by other than image_id, like 'hits', for example)...so the second table needs to be updated with the same ordering of image_id's. Probably be running this several little snippet several times with a cron job. So, yeah, I'm trying to update the second table with the ordering of the SELECT query of the first table and just put the id's in my second table, again...according to the order of the first SELECT query.

Upvotes: 0

Views: 5589

Answers (3)

mpmpeiris
mpmpeiris

Reputation: 3

INSERT INTO jx_gallery_images_ratings (image_id) (SELECT image_id FROM jos_jxgallery_images ORDER BY jos_jxgallery_images.image_id DESC LIMIT 25)

Or

Easy way is create trigger that updates table after selection with Dynamic SQL

Upvotes: 0

MarkR
MarkR

Reputation: 187

Something like:

UPDATE tbl_updateme SET row_to_update = (SELECT row_you_need from tbl_target WHERE tbl_updateme.comparison_row = tbl_target.comparison_row)

Upvotes: 3

Mike Brant
Mike Brant

Reputation: 71422

If the table is empty, you should do an insert. You can do it in a single query like this:

INSERT INTO jx_gallery_images_ratings (image_id)
SELECT image_id FROM jos_jxgallery_images ORDER BY jos_jxgallery_images.image_id DESC LIMIT 25

Note that you probably wouldn't really need the ORDER BY, adn you could do it for all images at once by removing the LIMIT

Upvotes: 4

Related Questions