[SOLVED] Query within a query

[ereignis]

Hello, I'm trying to execute an update query based on the results of a select query... This doesn't work - any ideas? This is the code I'm trying to get to run (it updates all products which are set to arrive by a certain date if that date has arrived or passed - and there are products that should and should not be updated in the db this is being tested with...):

 

$results = mysql_query("SELECT * FROM tbldogs WHERE isordered=1");

while ($row = mysql_fetch_row($results)) {

    $arrives=strtotime($row[10]);

    $today1=date("Y-m-d");

    $today2=strtotime($today1);

    if ($arrives >= $today2) {

          $query="UPDATE tbldogs SET isordered=0 where id='".row[8]."'";

          mysql_query($query) or die(mysql_error());

    }

}

 

Thanks!

[btherl]

How doesn't it work?  Blank screen?  Are the queries being executed?

 

I would start by adding an echo inside the if statement:

 

echo "Updating id {$row[8]} because $arrives >= $today2 <br>";

[chronister]

$query="UPDATE tbldogs SET isordered=0 where id='".row[8]."'";

 

should be

 

$query="UPDATE tbldogs SET isordered=0 where id='".$row[8]."'";

 

The troubleshooting steps I go through when something fails is this....

 

echo dependent variables to ensure they have values,

echo queries to ensure they too are receiving the proper values.

 

if those two items are doing what I expect, then I start checking the logic and data being worked with e.g. Is $var1 really greater than $var2? Are all the if / else statements being executed when they are supposed to be.

 

That kind of thing.

 

Good luck

 

 

[ereignis]

hello,

 

thanks for the responses. i've checked the variables and program logic, they seem fine... i thought it had to do with executing the update query on the table that was queried previously for the select... it would be much easier if i could just use one query and put the comparison in the WHERE part of the sql statement, but i don't know quite how that would work... can WHERE do a comparison like "WHERE 1>3" rather than referring to at least one stored data value? btw, the page shows up blank, there is a confirmation javascript that never gets rendered when i try this script...

[bwochinski]

This definately looks like something that could be accomplished in a single UPDATE.

 

Can you post your table structure and a description of exactly what you need to do?

[ereignis]

it's on a shared host so i don't have access to the command line and phpmyadmin decided to stop working to get you the structure, but it's pretty simple as dbs go... essentially, i'm trying to create a function where a user clicks a button (because i can't schedule events for mysql on this host) which selects all products designated as 'coming soon' (boolean/tinyint(1)), then loops through the resulting subset of rows and compares each product's 'date of arrival' with today's date - and if any are scheduled to have arrived by today's date, 'coming soon' is turned off (set to 0) and the 'date of arrival' is cleared out.

[bwochinski]

<?php
$query="UPDATE tbldogs SET isordered=0 WHERE isordered=1 AND arrival_date <= CURDATE()";
?>

 

That statement will take care of it all, assuming your "arrival_date" field is of type "DATE", which it certainly should be.  And if it isn't, you should convert the values over.  (backup the table first of course)

[ereignis]

perfect! thanks so much!