PHP/MYSQL while loop problem

Question

<?php
include 'db.php';
$i=0;
$result15=mysql_query("select c.dishes from c");
 while($row=mysql_fetch_array($result15))
{
  if($row['dishes']!=NULL)
  {
  $dish[$i]=$row['dishes'];
 $i++;
}

}

  //$j=0;
 //while($j<$i)
 $j=0;
 while($j<$i)
 {

$result16=mysql_query("select * from dish_box where dish_name='$dish[$j]'");
while($row=mysql_fetch_array($result16))
{
    $v_id=$row['dish_id'];
    echo $v_id.'<br />';
}
$j++;

 }

 mysql_close();
 ?>

This while loop is echoing value only once. Please figure it out why loop is working onlu once?

1) var_dump($dish) outside the loop to inspect it, and; 2) make sure each version of the query matches at least one record in your database table, otherwise the inner while will be skipped. — chelmertz
– chelmertz, Commented Jul 21, 2011 at 21:10
Please also add sample data in your tables of c and dish_box — Imre L
– Imre L, Commented Jul 21, 2011 at 21:39

Marc B · Accepted Answer · 2011-07-21 21:08:45Z

1

Most likely the query you store in $result16 is only returning a single row. Try doing:

$result16 = mysql_query(...);
echo "Total rows: ", mysql_num_rows($result16), "<br />";
while (...) {
etc.....

As well, you might want to try doing:

$result16 = mysql_query(...) or die(mysql_error());

Assuming your query has succeeded will only cause pain down the road when things fail.

answered Jul 21, 2011 at 21:08

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wrikken Over a year ago

Checking whether a query has failed is good, but there can't be enough mentions that the or die(mysql_error()); is for during development, and an actual failure on a live site can be handled differently for a far better user experience.

Community · Accepted Answer · 2017-05-23 12:19:50Z

1

Better solution perhaps:

// if you're using user input here, be sure to use mysql_real_escape_string
/*
  $dishes = "'";
  foreach( $dish as $key => $val ) 
       $dishes .= "'" . mysql_real_escape_string( $val ) . "',";
  $dishes = substr( $dishes, 0, strlen( $dishes ) -2 ); // remove last ,
*/
// otherwise implode the list
$dishes = "'".implode("','", $dish)."'";

// in either case use the IN operator
// the semi-colon that was in the query won't work.
$result16=mysql_query("select * from dish_box where dish_name in ($dishes)");
// to confirm how many rows
echo "result16 returned " . mysql_num_rows($result16) . 'rows.';
while($row=mysql_fetch_array($result16))
{
    $v_id=$row['dish_id'];
    echo $v_id.'<br />';
}

I just recalled that you were doing something like this in another question. You actually can combine both queries and save yourself some time:

$result16=mysql_query("select * from dish_box where dish_name in ".
                      "(select c.dishes from c)");
while($row=mysql_fetch_array($result16))
{
    $v_id=$row['dish_id'];
    echo $v_id.'<br />';
}

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jul 21, 2011 at 21:08

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

5 Comments

Wrikken Over a year ago

Euhm, escaping might be nice.

cwallenpoole Over a year ago

@Wrikken look at the other question I cited. I am fairly certain these are flip-sides of the same coin.

Wrikken Over a year ago

OK, the second answer is probably more correct. Do do something about non-escaped values in the first though...

cwallenpoole Over a year ago

I've added the escape code as comment, but, in this case, I don't think its needed.

Webroots Over a year ago

My prbolem not solved yet... i have updated the complete source code. Please give it a look

Digital Craft Studios · Accepted Answer · 2011-07-21 21:34:10Z

0

     $i=5;   
$j=0;
while($j<$i)
{

$result16=mysql_query("select * from dish_box where dish_name='$dish[$j]'");
while($row=mysql_fetch_array($result16))
{

$v_id[]=$row['dish_id'];
}
$j++;

}
var_dump($v_id);

You are over-writing the value of $v_id with each loop- hence it will only return the last value. making $v_id an array and writing to it will help

edited Jul 21, 2011 at 21:34

answered Jul 21, 2011 at 21:28

Digital Craft Studios

2301 gold badge2 silver badges12 bronze badges

1 Comment

Imre L Over a year ago

No, the value gets echoed right after assignment, so thats not a problem

Collectives™ on Stack Overflow

PHP/MYSQL while loop problem

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related