MySQL Query not working

[BuckeyeTheDog]

Hello all, I am using the Lynda.com PHP course to further learn php.  On one of the examples, they are using a SQL Query that works for them but is erroring out for me.  It is the query below for the $page_set variable, particularly the use of the WHERE subject_id = {$subject["id"]}". 

 

 

<?php require_once ('includes/conf.php'); ?>
<?php include ('includes/header.php'); ?>
<?php require_once ('includes/functions.php'); ?>

<table id="stucture">
<tr>
	<td id="navigation"> 
		<ul class="subjects">
			<?php 
				$subject_set = mysql_query("SELECT * FROM subjects", $connection);
				if (!$subject_set) {
					die ("Database query failed: " . mysql_error());
				}

				while ($subject = mysql_fetch_array($subject_set)) {
					echo "<li>{$subject["menu_name"]}</li>";
				}

				$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject["id"]}");  // This appears to be the problem area, unless it is fooling me. It works with this exact statement in their code.
				if (!$page_set) {
					die ("Database query failed: " . mysql_error());
				}					
		echo "<ul class='pages'>";
				while ($page = mysql_fetch_array($page_set)) {
					echo "<li>{$page['menu_name']}</li>";
				}
				echo "</ul>";
			?>			
			</ul>
	</td>
	<td id="page"><h2>Content Area</h2>
	</td>
</tr>
</table>
<?php require ('includes/footer.php'); ?>

 

 

The error listed is this:    Database query failed: 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 '' at line 1

 

 

Thanks,  you all are the best!  BtD

 

[BuckeyeTheDog]

I apologize if this is in the wrong category.  I'm not sure if this is a MySQL statement problem or a php variable problem..

[PFMaBiSmAd]

$subject['id'] contains something other than a number. If you form the query statement in a php variable, you can echo the actual query as part of the die() statement so that you can see what the query actually is.

 

Right before the query that uses $subject['id'], use var_dump($subject); to see what $subject contains. Also, try echo mysql_num_rows($subject_set); at that same point to see if the first query actually matched any rows.

[kicken]

The error would seem to indicate that your variable, $subject['id'] has no value.  Perhaps this value in NULL in the db, or perhaps your using the wrong key name.

 

Use var_dump($subject) before your query to output the structure of the $subject variable.  Also, when your debugging queries it is a good idea to store the query in a variable (say, $sql) so that you can print out your query in case of an error.  That will let you check it for syntax errors or run it in mysql directly to see if it works as expected.

 

var_dump($subject); //debug subject var
$sql = "SELECT * FROM pages WHERE subject_id = {$subject["id"]}";
$page_set = mysql_query($sql);
if (!$page_set) {
die ("Database query failed: " . mysql_error()."<br>Query was: ".$sql);
}

 

[BuckeyeTheDog]

Thanks, PFM and Kicken.  I have found the problem.

 

I have attached the updated code below (still going to clean it up).

 

Still learning PHP.  Best I can tell, the usage of the {$subject["id"]} occurred outside of my while statement, and therefore somehow it was not acknowledging the this var/array.  When I ran the var_dump outside this while {} statement, I got NULL and some other things like it.  When I moved it back in the while statement, I got the results i expected and got the var_dump to dump lots.

 

I'm not sure I understand why this is the case.  And why things are lost outside that while statement.  If you could explain that to me- it would be helpful. 

 

Thanks, and here is my working code (just not totally cleaned up)...

<?php require_once ('includes/conf.php'); ?>
<?php include ('includes/header.php'); ?>
<?php require_once ('includes/functions.php'); ?>

<table id="stucture">
<tr>
	<td id="navigation"> 
		<ul class="subjects">
			<?php 
				$subject_set = get_all_subjects();

				while ($subject = mysql_fetch_array($subject_set)) {
					echo "<li>{$subject['menu_name']}</li>";

				$query2 = "SELECT * 
					FROM pages
					WHERE subject_id = {$subject["id"]}";
				$page_set = mysql_query($query2);	
				if (!$page_set) {
					die ("Database query failed: " . mysql_error()."<br>Query was: ".$page_set);}	

				echo "<ul class='pages'>";

				while ($page = mysql_fetch_array($page_set)) {
					echo "<li>{$page['menu_name']}</li>";
				}
				echo "</ul>";
			}
			?>

			</ul>

	</td>
	<td id="page"><h2>Content Area</h2>
	</td>
</tr>
</table>
<?php require ('includes/footer.php'); ?>

 

[kicken]

I'm not sure I understand why this is the case.  And why things are lost outside that while statement.  If you could explain that to me- it would be helpful. 

 

Your while loop will not end until mysql_fetch_array returns false.  When it does that, the false value is going to be assigned to $subject making it false.  You would have to store all the return values in another array which you can reference later if you wanted to use them outside of the while loop.

 

This may be somewhat more advanced than your current level of knowledge, but judging by your code structure, it's likely that you could accomplish what you want by using a single query with a JOIN statement.  If you want to post your table structures, we could give you an example.

 

As a general rule, if you find yourself doing another query while inside the loop processing a previous query (as you are) then chances are good a JOIN could be used to combine the two queries into a single query, resulting in faster processing.