Page 1 of 1

looping within loops through tables

Posted: Fri Sep 03, 2010 10:42 am
by alexuk2463
i have some code which loops through a table, and displays the results. i have then added extra code so that it loops through another table, and prints out the reults, that are related to the first loop. so the page should look like

menu
submenu 1
submenu 2
submenu 3
menu 2
submenu 4 etc
The problem is that the code only prints out the first row from the first loop and nothing else.

Code: Select all

<?php
$list = "SELECT * FROM section_main";
$result = mysql_query($list) or die ("Query failed");

$numofrows = mysql_num_rows($result);
echo "<table border='1' id='section_list'>";
echo "<tr><th>section_id</th><th>section_title</th></tr>";
for($j = 1; $j < $numofrows; $j++) { 
    echo '<tr>';
	$row = mysql_fetch_array($result);  
    echo "<tr><td>". $row['section_id'] . "</td><td>". $row['section_title'] . "</td></tr>";

	$query2 = "SELECT * FROM section_sub WHERE section_title = ".$row['section_title']."ORDER BY section_sub_title";

	$result2 = mysql_query($query2) or die ("query failed2");//This part does not work
	$numofrows2 = mysql_num_rows($result2);

	for($i = 0; $i<$numofrows2; $i++){
		$row2 = mysql_fetch_array($result2);
		echo '<tr><td>'.$row2['section_sub_title'].'</td></tr>';
	}
}


echo '</table>';
?>

i havnt completed all of the html yet, i just wanted to get the php working. any ideas on why the query failed?
Any help on whats wrong would be great

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 10:51 am
by mikosiko
your query:

Code: Select all

$query2 = "SELECT * FROM section_sub WHERE section_title = ".$row['section_title']."ORDER BY section_sub_title";
should be in this way

Code: Select all

$query2 = "SELECT * FROM section_sub WHERE section_title = ".$row['section_title']." ORDER BY section_sub_title";
only difference is the space before ORDER ... without that space your select is incorrect.. in addition eliminate the space here :

Code: Select all

die ("query failed2");
should be

Code: Select all

die("query failed2");

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 11:13 am
by alexuk2463
that didnt make any difference. i removed the die clause so it would print the error

"Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\Users\Alex\Desktop\xampp\htdocs\SciFiStorm\forum\index.php on line 30"

i've commented in line 30 below

Code: Select all


<?php
$list = "SELECT * FROM section_main";
$result = mysql_query($list) or die ("Query failed");

$numofrows = mysql_num_rows($result);
echo "<table border='1' id='section_list'>";
echo "<tr><th>section_id</th><th>section_title</th></tr>";
for($j = 0; $j < $numofrows; $j++) { 
    echo '<tr>';
	$row = mysql_fetch_array($result);  
    echo "<tr><td>". $row['section_id'] . "</td><td>". $row['section_title'] . "</td></tr>";

	
	$query2 = "SELECT * FROM section_sub WHERE section_title = ".$row['section_title']." ORDER BY section_sub_title";

	$result2 = mysql_query($query2);//This part does not work
	$numofrows2 = mysql_num_rows($result2); //line 30

	for($i = 0; $i<$numofrows2; $i++){
		$row2 = mysql_fetch_array($result2);
		echo '<tr><td>'.$row2['section_sub_title'].'</td></tr>';
	}
}


echo '</table>';
?>

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 11:20 am
by mikosiko
alexuk2463 wrote:that didnt make any difference. i removed the die clause so it would print the error

"Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\Users\Alex\Desktop\xampp\htdocs\SciFiStorm\forum\index.php on line 30
sure it DOES a big differente... without the change your select is incorrect... and still incorrect according to you error message... and do not remove the die clause.. because is that clause where you can control/display the error.. no the other way... add your die clause again.. now in this way

Code: Select all

$result2 = mysql_query($query2) or die("Select Error :" . mysql_error());
and you will see the error that you select statement has.

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 11:34 am
by alexuk2463
right, i'm getting the error "Select Error :Unknown column 'test' in 'where clause' "
test is a value in the column that is common to both tables.
i dont understand the error. it says that the column "test" isnt there, but it is supposed to be reading the value in the column.

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 11:54 am
by mikosiko
this post is exactly a duplicated of another post in a different forum...

either way... if you want to have answers in both places (or you are a different student :) )... post your table description (both tables) and the last code that you actually have.

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 12:03 pm
by alexuk2463
ye, same person.
the tables are:


section_main table

section_id - int
section_title - varchar



section_sub table
section_sub_id - int
section_sub_title - varchar
section_sub_desc - varchar
section_title - varchar

i have two pieces of code trying to do the same thing, one i wrote, and another someone gave me, but they both give the same error

my code

Code: Select all

<?php
$list = "SELECT * FROM section_main";
$result = mysql_query($list) or die ("Query failed");

$numofrows = mysql_num_rows($result);
echo "<table border='1' id='section_list'>";
echo "<tr><th>section_id</th><th>section_title</th></tr>";
for($j = 0; $j < $numofrows; $j++) { 
    echo '<tr>';
	$row = mysql_fetch_array($result);  
    echo "<tr><td>". $row['section_id'] . "</td><td>". $row['section_title'] . "</td></tr>";

	$answer = $row['section_title'];
	 
	$query2 = "SELECT * FROM section_sub WHERE section_title = ".$answer." ORDER BY section_sub_title";

	$result2 = mysql_query($query2) or die("Select Error :" . mysql_error());//This part does not work
	$numofrows2 = mysql_num_rows($result2);

	for($i = 0; $i<$numofrows2; $i++){
		$row2 = mysql_fetch_array($result2);
		echo '<tr><td>'.$row2['section_sub_title'].'</td></tr>';
	}
}


echo '</table>';
?>
other code

Code: Select all

<?php
$query = 'SELECT m.section_id,
       m.section_title,
       s.section_sub_title,
       s.section_sub_desc
FROM section_main AS m
LEFT JOIN section_sub as s ON s.section_title = m.section_title
ORDER BY s.section_id, s.section_sub_title ASC';
$result = mysql_query($query) or die("Select Error: " . mysql_error());

$data = array();
while($row = mysql_fetch_assoc($result))
    $data[$row['section_title']][] = $row;
?>

<dl>
<?php foreach($data as $section_title => $section): ?>
    <dt><h1><?php echo $section_title; ?></h1></dt>
    <?php foreach($section as $sub_section): ?>
        <dd><?php echo $sub_section['section_sub_title']; ?></dd>
    <?php endforeach; ?>
<?php endforeach; ?>
</dl>


<?php
$query = "SELECT * FROM section_main";

$result2 = mysql_query($query) or die("Select Error: " . mysql_error());
//let's get the number of rows in our result so we can use it in a for loop
$numofrows = mysql_num_rows($result2);
?>
<?php


?>


<?php
for($i = 1; $i <= $numofrows; $i++) {
	$row = mysql_fetch_array($result2); //get a row from our result set

	echo "
	<br/><h2>Question ".$row['section_id'].": " .$row['section_title']."</h2>
	\n";

 $query2 = "SELECT section_sub.section_sub_id, section_sub.section_sub_title, section_sub.section_sub_desc, section_sub.section_title,
   WHERE section_sub.section_title = ".$row['section_title'];
	



	$result3 = mysql_query($query2);
	//let's get the number of rows in our result so we can use it in a for loop
	$numofrows2 = mysql_num_rows($result3);


for($j = 0; $j < $numofrows2; $j++) { 
    $row2 = mysql_fetch_array($result3); //get a row from our result set 
    echo ' 
    <p> 
        Answer '.$row2['section_sub_title'].'  
        
    </p> 
'; // Notice how cutting in/out of SINGLE-QUOTES is much easier to read and debug 

		
}
}
?>

Re: looping within loops through tables

Posted: Fri Sep 03, 2010 12:09 pm
by mikosiko
Ok... follow the thread in the other forum.... I just gave you answer there.