Help: SQL syntax error (PHP)

[znaji]

Hi

 

Hoping for some help after resolving other errors/warnings and spending abit on one issue and havent figured it out. I was hoping for some help here. Thanks for any help.

 

Error:

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/content/91/12175191/html/php/file.php on line 16

 

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 '0, 1' at line 1

<?php
function outputGrumbles($grumble, $home = false) {
	global $conn;
	if($grumble) {
		setTimezone();
		$sql = "SELECT cg.category_name, cg.category_url, scg.sub_category_id, scg.sub_category_name, scg.sub_category_description, scg.sub_category_url, " .
			"(COUNT(DISTINCT sg.status_id) + COUNT(DISTINCT ulg.user_like_id)) AS grumble_number, " . 
			"DATE_FORMAT(scg.sub_category_created, '%b %e, %Y %l:%i %p') AS sub_category_created " . 
			" FROM sub_category_grumble AS scg " .
			"LEFT OUTER JOIN categories_grumble AS cg ON cg.category_id = scg.category_id " .
			"LEFT OUTER JOIN status_grumble AS sg ON sg.sub_category_id = scg.sub_category_id " .
			"LEFT OUTER JOIN user_likes_grumble AS ulg ON ulg.sub_category_id = scg.sub_category_id " .
			"WHERE scg.sub_category_id = " . $grumble . 
			"LIMIT 0, 1";	
		$result = mysql_query($sql, $conn);
		if($row = mysql_fetch_array($result)) {
			echo '<div class="grumble-holder">';
				echo '<div class="';
				if($home) {
					echo 'content-padding-home';	
				}
				else {
					echo 'content-padding';
				}
				echo '">';
				echo '<div class="grumble-comment-number">';
					if($home)
						echo '<p><a href="/category/' . $row["category_url"] . '" class="colored-link-1 grumble-cat-name" title="' . $row["category_name"] . '">' . $row["category_name"] . '</a></p>';
					echo '<p class="grumble-comment-font" title="' . $row["grumble_number"] . ' comments/votes on this Grumble">'; 
							echo $row["grumble_number"];
					echo '</p>';
					echo '</div>';
					echo '<div class="grumble-text-holder">';
							echo '<h3><a href="/' . ($row["category_url"]) . '/' . $row["sub_category_url"] . '/' . $row["sub_category_id"] . '" data-id="' . $row["sub_category_id"] . '" class="colored-link-1">' . stripslashes($row["sub_category_name"]) . '</a></h3>';
							echo '<p class="grumble-description">' . stripslashes($row["sub_category_description"]) . '</p>';
					echo '</div>';
				echo '</div>';
			echo '</div>';
		}


		if (false === $result) {
    echo mysql_error();
}
		

	}
}
?>

Again thank you for any help.

 

 

[dalecosp]

Have your script print the SQL query to standard output/screen/debugger/log so you can actually read it.  The syntax error is almost undoubtedly causing the boolean/resource error message.

[znaji]

Have your script print the SQL query to standard output/screen/debugger/log so you can actually read it.  The syntax error is almost undoubtedly causing the boolean/resource error message.

Thank you for the feedback and info. However still new to php and MySQL. How would i go about doing this.

[Barand]

Could be the lack of space between $grumble and LIMIT

[znaji]

Could be the lack of space between $grumble and LIMIT

D'ah (On my part)....

 

Your are the best Thank you so much! It worked.

 

Changed:

 

From:

"WHERE scg.sub_category_id = " . $grumble .

"LIMIT 0, 1";

 

To:

"WHERE scg.sub_category_id = " . $grumble . " LIMIT 0,1";

[znaji]

Sorry to add to this thread however getting another issue same as before. Any help would be appreicated.

 

Warning/Error

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/content/91/12175191/html/file.php on line 72

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/content/91/12175191/html/file.php on line 144

 

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 'BY status_id DESC LIMIT 10' at line

<?php
$grumble = true;
$exist = false;
if(isset($_GET["subcat"]) && is_numeric($_GET["subcat"])) {
    $subcat = mysql_real_escape_string($_GET["subcat"]);

	$sql = "SELECT status_id FROM status_grumble " .
		"WHERE sub_category_id = " . $subcat . 
		"ORDER BY status_id DESC LIMIT 10";
	$result = mysql_query($sql, $conn);
	/* SQL syntax Error Check */
		if (false === $result) {
    echo mysql_error();
}
		/* SQL syntax Error Check */
	$sql = "SELECT scg.sub_category_id, scg.sub_category_name, scg.sub_category_created, scg.grumble_number, " .
        "scg.sub_category_description, scg.sub_category_url, cg.category_name, cg.category_id, cg.category_url, " .
        "ug.username, COUNT(ulg.user_like_id) AS votes_up FROM sub_category_grumble AS scg " .
		"LEFT OUTER JOIN categories_grumble AS cg ON scg.category_id = cg.category_id " .
		"LEFT OUTER JOIN users_grumble AS ug ON scg.user_id = ug.user_id " .
        "LEFT OUTER JOIN user_likes_grumble AS ulg ON ulg.sub_category_id = scg.sub_category_id " .
		"WHERE scg.sub_category_id = " . $subcat . " LIMIT 0,1";
	$result2 = mysql_query($sql, $conn);
	if(mysql_num_rows($result2) != 0)
		$exist = true;
}
if($exist) {
	$row = mysql_fetch_array($result2);
?>

Line 72

if(mysql_num_rows($result) == 0) {
        echo '<div id="comments-left">';
        if(isset($_SESSION["username"])) {

Line 144

if(mysql_num_rows($result) < 10)

Thanks again for any help. I am still going to try to figure it out on my own if i dont get help figuring this out first

[mac_gyver]

the cause of the current error is exactly the same reason as the previous error, no white-space where some is needed so that the sql parser can distinguish between different elements in your query.

 

i recommend that you NOT form php strings, in this case your sql query statement, by concatenating several different lines together. this method of forming strings usually causes php/sql errors because of the switching back/forth between different contexts. all the syntax gets in the way of what you are actually trying to accomplish. just form strings using one context. the following is perfectly valid and less error prone -

$sql = "SELECT status_id FROM status_grumble
    WHERE sub_category_id = $subcat
    ORDER BY status_id DESC LIMIT 10";

some notes about the above -

 

1) when php variables are inside of an overall double-quoted php string, they are replaced with their values. there's no need to close a string, put a php variable, open/start the string again. for associative array variables, i.e. $some_array['some_index'], you need to surround the variable with {} to tell php where it ends and starts inside the string.

 

2) in addition to the new-lines at the end of the first two lines in that string that are formatting the visual appearance in the program editor, there are at least one space/tab before the WHERE and ORDER keywords that both format the visual appearance in the program editor and separate them from the element that comes before them.

[znaji]

the cause of the current error is exactly the same reason as the previous error, no white-space where some is needed so that the sql parser can distinguish between different elements in your query.

 

i recommend that you NOT form php strings, in this case your sql query statement, by concatenating several different lines together. this method of forming strings usually causes php/sql errors because of the switching back/forth between different contexts. all the syntax gets in the way of what you are actually trying to accomplish. just form strings using one context. the following is perfectly valid and less error prone -

$sql = "SELECT status_id FROM status_grumble
    WHERE sub_category_id = $subcat
    ORDER BY status_id DESC LIMIT 10";

some notes about the above -

 

1) when php variables are inside of an overall double-quoted php string, they are replaced with their values. there's no need to close a string, put a php variable, open/start the string again. for associative array variables, i.e. $some_array['some_index'], you need to surround the variable with {} to tell php where it ends and starts inside the string.

 

2) in addition to the new-lines at the end of the first two lines in that string that are formatting the visual appearance in the program editor, there are at least one space/tab before the WHERE and ORDER keywords that both format the visual appearance in the program editor and separate them from the element that comes before them.

 Thank you for the soultion and lesson.