[RESOLVED] SQL Syntax Error

[Senatorlaw]

I've been trying to insert the info for a status update into my database, but I keep getting a sql syntax error. How do I fix the code so that it properly inserts the info?

<?PHP 
//Include connection to database
include_once("scripts/checkuserlog.php");
$cacheBuster = rand(999999999,9999999999999); // Put on an image URL will help always show new when changed

$id = "";
$pro_id = "";
$mem_id = "";
$username = "";
// ------- ESTABLISH THE PAGE ID ACCORDING TO CONDITIONS ---------
if (isset($_GET['id'])) {
	 $pro_id = preg_replace('#[^0-9]#i', '', $_GET['id']); // filter everything but numbers
} else if (isset($_SESSION['idx'])) {
	 $id = $logOptions_id;
}

$sql_status = mysql_query("SELECT mem_id FROM status WHERE wall_id='$pro_id' ORDER BY date DESC LIMIT 50");

while($row = mysql_fetch_array($sql_status)){
	$mem_id = $row['mem_id'];
	// Inner sql query
	$sql_mem_data = mysql_query("SELECT username FROM myMembers WHERE id='$mem_id' LIMIT 50");
	while($row = mysql_fetch_array($sql_mem_data)){
		$username = $row['username'];	
	}
}
///////  Mechanism to Display Pic. See if they have uploaded a pic or not  //////////////////////////
$check_pic = "members/$mem_id/image01.jpg";
$default_pic = "members/0/image01.jpg";
if (file_exists($check_pic)) {
$user_pic = "<img src=\"$check_pic?$cacheBuster\" width=\"50px\" />"; 
} else {
$user_pic = "<img src=\"$default_pic\" width=\"50px\" />"; 
}
// Delete blab display
	if ($logOptions_id != $mem_id) {
		$deleteButton = '<a href="delete_status.php?id=' . $mem_id . '"></a>';
	} else {
		$deleteButton = '<a href="delete_status.php?id=' . $mem_id . '">DELETE</a>';	

}

//Get posted values from form
$status=$_POST['status'];

//Strip slashes
$status = stripslashes($status);

//Strip tags 
$status = strip_tags($status);
// Apply Values
$username = $_SESSION['username'];
$zero = "0";
//Inset into database
$insert_status = mysql_query("INSERT INTO status ('mem_id, wall_id, vid_id, art_id, sName, status, date') VALUES ('$logOptions_id','$pro_id','$zero','$zero','$username','$status',now())") or die (mysql_error());
while($row = mysql_fetch_array($insert_status)){
$sName = $row['sName'];
$status=$row['status'];
$date=$row['date'];
}


//Line break after every 80
$status = wordwrap($status, 80, "\n", true);

//Line breaks
$status=nl2br($status);

//Display status from data base
echo '<div style="background-color:#FFF" class="load_status">
	  <div class="status_img"><img src="' . $user_pic . '" /></div>
	  <div class="status_text"><a href="profile.php?id=' . $mem_id . '"><strong>' . $sName . '</strong></a><p class="text">'.$status.'</p>
	  <div class="date">'.$date.' &middot; <strong>' . $deleteButton . '</strong></div>
	  </div>
	  <div class="clear"></div>
	  </div>';
?>

[bradgrafelman]

What's the error message returned by MySQL? You should always check to see if [man]mysql_query/man returns boolean FALSE (indicating an error occurred) and, if so, either log or output (depending upon if you're in the production or the development environment) the error message returned by MySQL (see [man]mysql_error/man).

Also note that queries-within-loops-of-other-queries like this pair:

$sql_status = mysql_query("SELECT mem_id FROM status WHERE wall_id='$pro_id' ORDER BY date DESC LIMIT 50"); 

while($row = mysql_fetch_array($sql_status)){ 
    $mem_id = $row['mem_id']; 
    // Inner sql query 
    $sql_mem_data = mysql_query("SELECT username FROM myMembers WHERE id='$mem_id' LIMIT 50"); 
    while($row = mysql_fetch_array($sql_mem_data)){ 
        $username = $row['username'];     

    } 
} 

is often a huge red flag for unoptimized SQL queries. Rather than executing two separate queries, simply use a JOIN. Also, why have a 'LIMIT 50' if you're only ever going to get a single value back (the last one) since you're overwriting the value stored in $username on each iteration without ever using it?

[Derokorian]

//Inset into database
$insert_status = mysql_query("INSERT INTO status ('mem_id, wall_id, vid_id, art_id, sName, status, date') VALUES ('$logOptions_id','$pro_id','$zero','$zero','$username','$status',now())") or die (mysql_error()); 

Should be:

//Inset into database
$insert_status = mysql_query("INSERT INTO status (`mem_id`, `wall_id`, `vid_id, `art_id`, `sName`, `status`, `date`) VALUES ('$logOptions_id','$pro_id','$zero','$zero','$username','$status',now())") or die (mysql_error()); 

[Senatorlaw]

Okay I understand what you mean about the $username, and LIMIT part. Can you provide an example for boolean FALSE? Wouldn't the boolean be the values within parenthesis? I'm a super newbie so explanation needs to be somewhat detailed. I've read the MySQL manual for 5.0 which is the version I currently have, but I can't see where I have gone wrong. All of my values in each row are consistent as far as I can see.

This is the error message given:
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 ''mem_id, wall_id, vid_id, art_id, sName, status, date') VALUES ('1','1','0','0',' at line 1

[Senatorlaw]

Derokorian;10985905 wrote:

//Inset into database
$insert_status = mysql_query("INSERT INTO status ('mem_id, wall_id, vid_id, art_id, sName, status, date') VALUES ('$logOptions_id','$pro_id','$zero','$zero','$username','$status',now())") or die (mysql_error()); 

Should be:

//Inset into database
$insert_status = mysql_query("INSERT INTO status (`mem_id`, `wall_id`, `vid_id, `art_id`, `sName`, `status`, `date`) VALUES ('$logOptions_id','$pro_id','$zero','$zero','$username','$status',now())") or die (mysql_error()); 

That didn't work. I got this error:

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 'art_id,sName,status,date`) VALUES ('1','1','0','0','SenatorLaw','test 2' at line 1

[bradgrafelman]

Senatorlaw;10985907 wrote:

Can you provide an example for boolean FALSE?

Um, sure, I guess...

$example = FALSE;

Senatorlaw;10985907 wrote:

Wouldn't the boolean be the values within parenthesis?

The return value of [man]mysql_query/man has nothing to do with 'values in parenthesis', no. mysql_query() is a function. Functions return values. You should check if this particular function returns a value of FALSE, indicating an error has occurred. See the PHP manual for [man]mysql_query/man for more info on the values that function returns and also take a look at the coding examples.

Senatorlaw;10985907 wrote:

I'm a super newbie so explanation needs to be somewhat detailed. I've read the MySQL manual for 5.0 which is the version I currently have, but I can't see where I have gone wrong. All of my values in each row are consistent as far as I can see.

Not sure what this was directed at, but as for checking to see if mysql_query() executes the query successfully, the MySQL manual is irrelevant - it's PHP that you're using to interact with MySQL, so the PHP manual is the relevant resource in this case.

Senatorlaw;10985907 wrote:

This is the error message given:
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 ''mem_id, wall_id, vid_id, art_id, sName, status, date') VALUES ('1','1','0','0',' at line 1

And there you have it - MySQL got confused when it saw a single quote in a place where it expected to find a list of identifiers (e.g. names of columns in the table). Why did you surround the column names in a pair of single quotes, making it one long string instead of a list of identifiers?

Senatorlaw;10985909 wrote:

That didn't work. I got this error:

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 'art_id,sName,status,date) VALUES ('1','1','0','0','SenatorLaw','test 2' at line 1[/QUOTE]
Derokorian had a typo and missed one of the backticks (). So the solution would be either a) add the missing backtick, or b) removing all other backticks since they are optional.

[Senatorlaw]

Okay got everything working except for line 53. I rewrote the code like this:

<?PHP 
//Include connection to database
include_once("scripts/checkuserlog.php");
$cacheBuster = rand(999999999,9999999999999); // Put on an image URL will help always show new when changed

$id = "";
$date = "";
$pro_id = "";
$sName = "";
$username = "";
// ------- ESTABLISH THE PAGE ID ACCORDING TO CONDITIONS ---------
if (isset($_GET['id'])) {
	 $pro_id = preg_replace('#[^0-9]#i', '', $_GET['id']); // filter everything but numbers
} else if (isset($_SESSION['idx'])) {
	 $id = $logOptions_id;
}

$sql_status = mysql_query("SELECT mem_id FROM status WHERE wall_id='$pro_id' ORDER BY date DESC LIMIT 50");

while($row = mysql_fetch_array($sql_status)){
	$mem_id = $row['mem_id'];
}
///////  Mechanism to Display Pic. See if they have uploaded a pic or not  //////////////////////////
$check_pic = "members/$mem_id/image01.jpg";
$default_pic = "members/0/image01.jpg";
if (file_exists($check_pic)) {
$user_pic = "<img src=\"$check_pic?$cacheBuster\" width=\"50px\" />"; 
} else {
$user_pic = "<img src=\"$default_pic\" width=\"50px\" />"; 
}
// Delete blab display
	if ($logOptions_id != $mem_id) {
		$deleteButton = '<a href="delete_status.php?id=' . $mem_id . '"></a>';
	} else {
		$deleteButton = '<a href="delete_status.php?id=' . $mem_id . '">DELETE</a>';	

}

//Get posted values from form
$status=$_POST['status'];

//Strip slashes
$status = stripslashes($status);

//Strip tags 
$status = strip_tags($status);
// Apply Values
$username = $_SESSION['username'];
$zero = "0";
//Inset into database
//Inset into database
$insert_status = mysql_query("INSERT INTO status (`mem_id`, `wall_id`, `vid_id`, `art_id`, `sName`, `status`, `date`) VALUES ('$logOptions_id','$pro_id','$zero','$zero','$username','$status',now())") or die (mysql_error());
while($row = mysql_fetch_array($insert_status)){
$sName = $row['sName'];
$status = $row['status'];
$date = $row['date'];
$mem_id = $row['mem_id'];
}


//Line break after every 80
$status = wordwrap($status, 80, "\n", true);

//Line breaks
$status=nl2br($status);

//Display status from data base
echo '<div style="background-color:#FFF" class="load_status">
	  <div class="status_img"><img src="' . $user_pic . '" /></div>
	  <div class="status_text"><a href="profile.php?id=' . $mem_id . '"><strong>' . $sName . '</strong></a><p class="text">' . $status . '</p>
	  <div class="date">' . $date . ' &middot; <strong>' . $deleteButton . '</strong></div>
	  </div>
	  <div class="clear"></div>
	  </div>';
?>

I'm getting this error now:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/50/7732550/html/colent/status_update.php on line 53

Apparently I have a long way to go when it comes to understanding PHP.

[Derokorian]

An insert query doesn't provide a return resource. It returns true on success and false on failure.

[Senatorlaw]

I think I got it fixed finally. You guys are a blessing. Thank you.