Inner while loop not working.. it limit to only one row

thara · May 21, 2012

This is a code pinch from a webpage of my project. Here I want to display user selected categories and then want to display its subjects that belong to category. There, users could have more than 1 category and It no problem I can print all those category in my first while loop... but problem is when Im try to print subjects it has only one row as a result.. there are more subjects in each category. can anybody tell me what has happens?

this is my code.... Note: both queries are working properly.. I tried those user mysql client program.

<?php

require_once ('../../includes/config.inc.php');
require_once( MYSQL1 );

$q = "SELECT institute_category.category_id, category_name
		FROM institute_category
		INNER JOIN category ON institute_category.category_id = category.category_id
		WHERE institute_category.institute_id = $instituteId";	

$r = mysqli_query( $dbc, $q);


while ( $row = mysqli_fetch_array ( $r, MYSQLI_ASSOC) )   {

	$categoryId = $row['category_id']; 
	$category = $row['category_name']; 

	echo '<fieldset class="alt">
			<legend><span>Category : <em style="color: red;">' . $category . '</em></span></legend>';



				$qy = "SELECT category_subject.category_id, category_subject.subject_id, subjects
						FROM category_subject
						INNER JOIN category ON category_subject.category_id = category.category_id
						INNER JOIN subject ON category_subject.subject_id = subject.subject_id
						WHERE category_subject.category_id = $categoryId";	

				$result = mysqli_query( $dbc, $qy);

				$c = $i = 0;

					echo '<table class="form_table" ><tr>'; 

							while($row = mysqli_fetch_array( $result, MYSQLI_ASSOC  )){

								// if remainder is zero after 2 iterations (for 2 columns) and when $c > 0, end row and start a new row:  
								if( ($c % 2) == 0 && $c != 0){
									echo "</tr><tr>";
								}

								echo '<td width="50%"><input type="checkbox" name="subject[]"  value="' . $row['category_id'] . ":" . $category . ":"  . $row['subject_id'] . ":". $row['subjects'] . '" />  ' . $row['subjects'] . '</td>' . "\n";

								$c++; 

							} // while..
								// in case you need to fill a last empty cell:
								if ( ( $i % 2 ) != 0 ){

								// str_repeat() will be handy when you want more than 2 columns
								  echo str_repeat( "<td> </td>", ( 2 - ( $i % 2 ) ) );
								}
							echo "</tr></table>";	

} 

echo '</fieldset>';	
?>

any comments are greatly appreciated..

thank you

Psycho · May 21, 2012

You should never run queries within loops. You need to figure out how to JOIN the result set so you can run one query.

But, I can answer your question pretty simply. Both of your while loops are using (row =

So, when the inner loop exits on the first iteration of the outer loop it will also exit the outer loop because $row will equal FALSE

thara · May 21, 2012

thanks for reply...

I change those variable names... but still result is one row output..

this is my new code..

<?php

require_once ('../../includes/config.inc.php');
require_once( MYSQL1 );

$outQuery = "SELECT institute_category.category_id, category_name
		FROM institute_category
		INNER JOIN category ON institute_category.category_id = category.category_id
		WHERE institute_category.institute_id = $instituteId";	

$outResult = mysqli_query( $dbc, $outQuery);


while ( $outRow = mysqli_fetch_array ( $outResult, MYSQLI_ASSOC) )   {

	$categoryId = $outRow['category_id']; 
	$category = $outRow['category_name']; 

	echo '<fieldset class="alt">
			<legend><span>Category : <em style="color: red;">' . $category . '</em></span></legend>';



				$innerQuery = "SELECT category_subject.category_id, category_subject.subject_id, subjects
						FROM category_subject
						INNER JOIN category ON category_subject.category_id = category.category_id
						INNER JOIN subject ON category_subject.subject_id = subject.subject_id
						WHERE category_subject.category_id = $categoryId";	

				$innerResult = mysqli_query( $dbc, $innerQuery);

				$c = $i = 0;

					echo '<table class="form_table" ><tr>'; 

							while($innerRow = mysqli_fetch_array( $innerResult, MYSQLI_ASSOC  )){

								// if remainder is zero after 2 iterations (for 2 columns) and when $c > 0, end row and start a new row:  
								if( ($c % 2) == 0 && $c != 0){
									echo "</tr><tr>";
								}

								echo '<td width="50%"><input type="checkbox" name="subject[]"  value="' . $innerRow['category_id'] . ":" . $category . ":"  . $innerRow['subject_id'] . ":". $innerRow['subjects'] . '" />  ' . $innerRow['subjects'] . '</td>' . "\n";

								$c++; 

							} // while..
								// in case you need to fill a last empty cell:
								if ( ( $i % 2 ) != 0 ){

								// str_repeat() will be handy when you want more than 2 columns
								  echo str_repeat( "<td> </td>", ( 2 - ( $i % 2 ) ) );
								}
							echo "</tr></table>";	

} 

echo '</fieldset>';	
?>

Psycho · May 21, 2012

Here is a quick rewrite that *should* do what you need only using one query. I did this without any testing so there may be some minor errors. But, this is the way it should be done

    require_once ('../../includes/config.inc.php');
    require_once( MYSQL1 );

    $q = "SELECT c.category_id, c.category_name, s.subject_id, s.subjects
          FROM category AS c
          INNER JOIN category_subject AS cs USING(category_id)
          INNER JOIN subject AS s USING(subject_id)
          INNER JOIN institute_category AS ic USING (category_id)
          WHERE ic.institute_id = $instituteId
          ORDER BY c.category_name, s.subjects";
    $result = mysqli_query( $dbc, $q);

    $catID = false;
    $max_columns = 2;

    while ($row = mysqli_fetch_assoc($result, MYSQLI_ASSOC))
    {
        $categoryId = $row['category_id']; 
        $category = $row['category_name']; 

        //Detect change in category
        if($catID != $row['category_id'])
        {
            if($catID!=false)
            {
                if($recCount % $max_columns != 0)
                {
                    //Close previous row
                    echo "</tr>\n";
                }
                //Close previous table
                echo "</table>\n";
            }
            $catID = $row['category_id'];
            echo "<fieldset class='alt'>\n";
            echo "<legend><span>Category : <em style='color: red;'>{$category}</em></span></legend>\n";
            echo "<table class='form_table'><tr>\n";
            $recCount = 0;
        }

        $recCount++;

        if($recCount % $max_columns == 1)
        {
            echo "<tr>\n";
        }

        $value = "{$row['category_id']}:{$category}:{$row['subject_id']}:{$row['subjects']}";
        echo "<td width='50%'>";
        echo "<input type='checkbox' name='subject[]' value='{$value}' />  {$row['subjects']}";
        echo "</td>\n";

        if($recCount % $max_columns == 0)
        {
            echo "</tr>\n";
        }
   }

    if($recCount % $max_columns != 0)
    {
       //Close last row
       echo "</tr>\n";
    }
    //Close last table
    echo "</table>\n";

    echo "</fieldset>";

thara · May 21, 2012

I tried with your code.. but then I get this error message...

An error occurred in script 'C:\wamp\www\lanka_institute\edit_profiles\centers\pick_subjects.php' on line 63: mysqli_fetch_assoc() expects exactly 1 parameter, 2 given .....................................

then I change this

while ($row = mysqli_fetch_assoc($result, MYSQLI_ASSOC))

like this

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))

Then I can get one subject row as an output... the result is same for my earlier script...

thara · May 21, 2012

I checked query with mysql client and I could get my expecting result... this is that result

mysql> SELECT c.category_id, c.category_name, s.subject_id, s.subjects
    ->           FROM category AS c
    ->           INNER JOIN category_subject AS cs USING(category_id)
    ->           INNER JOIN subject AS s USING(subject_id)
    ->           INNER JOIN institute_category AS ic USING (category_id)
    ->           WHERE ic.institute_id = 37
    -> ORDER BY c.category_name, s.subjects;
+-------------+----------------------------+------------+------------------------+
| category_id | category_name              | subject_id | subjects               |
+-------------+----------------------------+------------+------------------------+
|           6 | grade 12 - 13 (A/L)        |          7 | Catholicism            |
|           3 | grade 5 (scholarship exam) |          1 | Agro & Food Technology |
|           3 | grade 5 (scholarship exam) |          2 | Art                    |
|           3 | grade 5 (scholarship exam) |          3 | Art & Craft            |
|           3 | grade 5 (scholarship exam) |          4 | Bialogy                |
|           4 | grade 6 - 10               |          2 | Art                    |
|           4 | grade 6 - 10               |          3 | Art & Craft            |
|           4 | grade 6 - 10               |          4 | Bialogy                |
|           4 | grade 6 - 10               |          5 | Buddhism               |
+-------------+----------------------------+------------+------------------------+
9 rows in set (0.00 sec)

query is ok.. but I think php has a error.. It only display one subject row under category name and if there is a one subject under category it is not display....

any comments are greatly appreciated..

thank you..

thara · May 22, 2012

this attachment is my from my form. I have attached it for your better understanding

Psycho · May 22, 2012

Well, something is definitely off. The output you've posted is not in the same order as the query results you posted previously. One thing I do see that is off in the code is the FIELDSET tags. You need to include a closing FIELDSET right before the closing table tag inside the loop. I can't see why the other records are not displaying, but it is difficult to debug these types of errors when I have to do it without the benefit of real data and the database.

I'd suggest adding some echo statements into the while loop for debugging purposes to see what is happening. If you want more help then post an export of the relevant DB tables so I can put them into a test db.

thara · May 23, 2012

I used again my earlier code for my experiments and use some echo statements in inner while loop to check weather every my subjects come or not into my second while loop.. There I can see every subject relevant to categories has come into the while loop.. problem is when Im going to print those subjects in a two columns table using checkbox input tag... still its print a one subject row only...

This is that experimented code...

<?php

	require_once ('../../includes/config.inc.php');
require_once( MYSQL1 );

$outQuery = "SELECT institute_category.category_id, category_name
		FROM institute_category
		INNER JOIN category ON institute_category.category_id = category.category_id
		WHERE institute_category.institute_id = $instituteId";	

$outResult = mysqli_query( $dbc, $outQuery);


while ( $outRow = mysqli_fetch_array ( $outResult, MYSQLI_ASSOC) )   {

	$category = $outRow['category_name']; 

	echo '<fieldset>';
	echo '<legend><span>Category : <em style="color: red;">' . $category . '</em></span></legend>';

				$innerQuery = "SELECT subject.subjects, subject.subject_id 
								FROM subject
								INNER JOIN category_subject ON category_subject.subject_id= subject.subject_id
								WHERE category_subject.category_id = {$outRow['category_id']}";

				$innerResult = mysqli_query( $dbc, $innerQuery);

				$c = $i = 0;

				echo '<table class="form_table" ><tr>'; 

				while ( $innerRow = mysqli_fetch_array( $innerResult, MYSQLI_ASSOC  )){		

					// if remainder is zero after 2 iterations (for 2 columns) and when $c > 0, end row and start a new row:  
					if( ($c % 2) == 0 && $c != 0){
						echo "</tr><tr>";
					}

					//echo $innerRow['subjects']; // here I can get all subject that relevant to one category 


					echo '<td width="50%"><input type="checkbox" name="subject[]"  value="" />  ' . $innerRow['subjects'] . '</td>' . "\n";

					$c++; 

				} // while..

				// in case you need to fill a last empty cell:
				if ( ( $i % 2 ) != 0 ){
					// str_repeat() will be handy when you want more than 2 columns
					echo str_repeat( "<td> </td>", ( 2 - ( $i % 2 ) ) );
				}

				echo "</tr></table>";	

	echo '</fieldset>';	
}	
?>

This code and Psycho provide code are working in similar way.. there is nothing difference.

Psycho · May 23, 2012

Why are you not using the single query? You are welcome to use two queries if you wish, but I will not help in building a poor solution.

thara · May 23, 2012

I used it to my experiments only.. I change my html structure and change css style as well. Then I could to get my expecting result. .. now I used a single query which you have coded one for my.. Its working very well.

thanks in advance for your cooperation....

Sign In

Inner while loop not working.. it limit to only one row

Recommended Posts

thara

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

thara

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information