MySQL help

[ned69]

I have a joke script which currently displays a new joke each day and I want to change it to where it displays one every week. I keep getting the SQL error below but when I run it in phpMyAdmin it works just fine.

Most likely some little thing which I keep overlooking. Don't worry if the logic is off ... just need the code straightened. Here is the error I keep getting.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' now(), "2009-09-10 ")' at line 1

Thanks in advance.

<?

	function reset_jow($jow_count)
	{

		$next_monday = date("Y-m-d", strtotime('next monday'));
		
		If($jow_count > 0)
		{
			$sql = 'DELETE from JOW '  ;
			mysql_query($sql);
			$delete_result = mysql_affected_rows();
			
			echo 'DEL SUCCESS <BR>' ;
		}
			
			//Get the next JOW in Line for display
			$min_jod_query = 'SELECT min(id) FROM jokes where status = 2';
			$min_jod = mysql_query($min_jod_query);
			$min_result = mysql_fetch_array($min_jod);			
			
			print_r($min_result) . '<BR>';
			
			//Next JOW in Line
			echo '<BR>LATEST JOW: ' . $latest_jow_id = $min_result['min(id)'] . '<BR>';
			
			$jokes_id = $latest_jow_id;	
			
			$sql = 'INSERT INTO jow (jokes_id, date_added, date_end)'; 
			$sql .= ' VALUES(';
			$sql .= $jokes_id . ','  ; 
			//$sql .= '1545 ,';
			//$sql .= ', 5, ';
			$sql .=	' now(),'; 
			$sql .=	' "' . $next_monday  ;
			$sql .=	'   ")';
			echo $sql . '<BR>';
			mysql_query($sql) or die(mysql_error());
			
			echo 'INSERTS: ' . $update = mysql_affected_rows() . '<BR>';
			
			$min_result = mysql_fetch_array($min_jod);
			
			$sql_updt = 'UPDATE jokes SET
						status = 1,
						last_modified = now()
					WHERE id = '. $latest_jow_id ;
			mysql_query($sql_updt) or die(mysql_error());
			echo $sql_updt . '<BR>';
			echo 'UPDATE: ' .  $update = mysql_affected_rows() . '<BR>';
		

		
	}

	
	echo 'TODAY: ' . $today = date("Y-m-d", strtotime('now'));
	echo '<BR>';
	echo 'LAST MONDAY: '. $last_monday = date("Y-m-d", strtotime('last monday'));
	echo '<BR>';
	echo 'NEXT MONDAY ' . $next_monday = date("Y-m-d", strtotime('next monday'));
	echo '<BR><BR>';

	$jow_query =  'SELECT * FROM jokes WHERE status = 2';
	$jow_result = mysql_query($jow_query);
	$jokes_count = mysql_num_rows($jow_result);
	
	// Check JODs
	$jow_query =  'SELECT * FROM jow';
	$jow_result = mysql_query($jow_query);
	$jow_count = mysql_num_rows($jow_result);
	
	If($jow_count > 0)
		$joke_end_date = mysql_result($jow_result,0,'date_end');
	
	
	echo 'Status 2s: ' . $jokes_count . '<BR>';
	echo 'JOW: ' . $jow_count . '<BR>';
	echo 'Date End: ' . $joke_end_date . '<BR>';

		if($jokes_count > 0)
		{
			
			if( $today == $next_monday || $joke_end_date < $today )
			{
				echo '<BR> IN THE RESET IF <BR>';
				//Get rid of checks in function
				reset_jow($jow_count) ;
				
			}
			
		}
		


?>

[ShawnCplus]

Where you have mysql_query($sql_updt) or die(mysql_error()); put echo $sql_updt; and paste the the output here

[ned69]

Where you have mysql_query($sql_updt) or die(mysql_error()); put echo $sql_updt; and paste the the output here

Thanks for your response. Please see SQL below:

INSERT INTO jow (jokes_id, date_added, date_end) VALUES(1546
, now(), "2009-09-10")

UPDATE jokes SET status = 1, last_modified = now() WHERE id = 1546

[mazeroth]

Could it be that now() returns date time and your field is only date?

[ned69]

Could it be that now() returns date time and your field is only date?

I will check when I get home but if that were the case why doesn't it complain when I run the SQL by itself?

[ned69]

I will check when I get home but if that were the case why doesn't it complain when I run the SQL by itself?

That was not the issue, thanks.

[cwarn23]

Try this:

$sql = 'INSERT INTO jow (jokes_id, date_added, date_end)'; 
			$sql .= ' VALUES(';
			$sql .= $jokes_id . ','  ; 
			//$sql .= '1545 ,';
			//$sql .= ', 5, ';
			$sql .=	' now(),'; 
			$sql .=	'"'.$next_monday.'"';
			$sql .=	',)';
			echo $sql . '<BR>';

According to your commented sql columns there was one last column left blank.

[ned69]

Try this:

$sql = 'INSERT INTO jow (jokes_id, date_added, date_end)'; 
			$sql .= ' VALUES(';
			$sql .= $jokes_id . ','  ; 
			//$sql .= '1545 ,';
			//$sql .= ', 5, ';
			$sql .=	' now(),'; 
			$sql .=	'"'.$next_monday.'"';
			$sql .=	',)';
			echo $sql . '<BR>';

According to your commented sql columns there was one last column left blank.

THANK YOU!

The issue wasn't quite with the $next_monday var but moreso with the jokes_id column. I have it defined as an int in the DB but for some reason it wants to be wrapped in quotes like what you did with $next_monday... don't know why since its an int???