Adding curdate to already long query

troutmissile

The query updates the db, works perfectly without the date part (that is, if I leave out everything after "LIMIT 1"). But when I include the date part, it does not update the db, and there is no error message.

$resource = mysql_query("select lname from users where username = '$_SESSION[username]'");
$lrow = mysql_fetch_array($resource);
$lname = $lrow['lname'];
mysql_free_result($resource);  

$resource1 = mysql_query("select field2 from users where username = 'specific'");
$row1 = mysql_fetch_array($resource1);
$optout1 = $row1['field2'];
mysql_free_result($resource1);  

mysql_query("update users set field1='1' where field2 = '$field2' and lname = '$lname' and id > '$id' LIMIT 1 and (curdate() < startdate or curdate() > enddate)");

I've also tried

mysql_query("update users set field1='1' where field2 = '$field2' and lname = '$lname' and id > '$id' LIMIT 1 and (curdate() not between startdate and enddate)");

In the mysql structure both startdate and enddate are DATE types, so the format is 0000-00-00.

It is set up so that in the form the user can only choose one startdate and one enddate at a time.

I have thought of using another table but since there is no one-to-many relationship involved, it seems like it would be an unnecessary complication to make certain the ids correspond in auto-increment, as discussed here:
webproworld.com/webmaster-forum/threads/85137-php-insert-different-records-into-multiple-mysql-tables-with-same-unique-id

bradgrafelman

troutmissile wrote:
there is no error message.

Of course there isn't - that's because you never check for any errors and output the error message! 😉

Why do you have this:

and (curdate() not between startdate and enddate)

after a LIMIT clause? The LIMIT clause is in the form of either "LIMIT n" or "LIMIT m,n", so the above code is just some random garbage at the end of the SQL query that MySQL has no idea what to do with.

Perhaps you meant for it to be part of the WHERE clause?

troutmissile

yes, i wanted it to be part of the where clause.:evilgrin:

thanks for the tip about LIMIT.

	mysql_query("update users set field1='1' where field2 = '$field2' and lname = '$lname' and (CURDATE() > enddate or CURDATE() < startdate) and (id > '$id' or id > 0) LIMIT 1");

As you can see I have added "or id >0."
The (CURDATE() > enddate or CURDATE() < startdate) part works, I've tested it in another query. But the id part seems to be wrong, it won't update the db. Running out of syntax variations to try. Order by id desc (and asc), no luck. Wondering if the problem is syntax or if there is something else wrong.

johanafm

Change your php code to

$qry = "update users set field1='1' where field2 = '$field2' and lname = '$lname' and (CURDATE() > enddate or CURDATE() < startdate) and (id > '$id' or id > 0) LIMIT 1";
# allows inspection of what the query really looks like
# you might also go with error_log() instead
echo $qry;
mysql_query($qry);

Also note that id > $id is superflous

"(id > '$id' or id > 0)"

Since for any given $id > 0 and id > 0, the whole clause is always true due to id being greater than 0. Also, I'm assuming id is an auto incremented primary key, which means it's always > 0, which makes even id > 0 superflous.

Furthermore, your most likely outcome of the query is to affect several rows (disregarding the limit clause), yet you limit it to updating one random record. The database table has no specific ordering unless you specify that you want one. For example

WHERE someConditional
ORDER BY start ASC
LIMIT 1

would update the record having the lowest start date (the first record among chonologically ordered records). Without the order, you might update the first, the last, or any other record that matches the where clause.

Finally, as brad pointed out, you never check for success/failure

# Read the documentation: http://se.php.net/manual/en/function.mysql-query.php
$result = mysql_query();
if (!$result)
{
	# never echo in production, user error_log() instead
	echo mysql_errno() . ': ' . mysql_error();
}
else
{
	# success!
}