ereignis Posted March 2, 2007 Share Posted March 2, 2007 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! Quote Link to comment Share on other sites More sharing options...
btherl Posted March 2, 2007 Share Posted March 2, 2007 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>"; Quote Link to comment Share on other sites More sharing options...
chronister Posted March 2, 2007 Share Posted March 2, 2007 $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 Quote Link to comment Share on other sites More sharing options...
ereignis Posted March 2, 2007 Author Share Posted March 2, 2007 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... Quote Link to comment Share on other sites More sharing options...
bwochinski Posted March 2, 2007 Share Posted March 2, 2007 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? Quote Link to comment Share on other sites More sharing options...
ereignis Posted March 2, 2007 Author Share Posted March 2, 2007 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. Quote Link to comment Share on other sites More sharing options...
bwochinski Posted March 2, 2007 Share Posted March 2, 2007 <?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) Quote Link to comment Share on other sites More sharing options...
ereignis Posted March 5, 2007 Author Share Posted March 5, 2007 perfect! thanks so much! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.