Display message in PHP when the result of mysql query has no value

Question

I have a table in PHP which displays the result of the MySQL query.

The database table item_em has the price details of the items that are available in hypermarkets available in the database table hypermarket_em.There are some items that aren't available in the hypermarket. Hence when i display the prices in the table I want those cells with no price as N/A.

So my problem is I tried a code myself to display message when database table field has no value.But it doesn't work and I don't get any error as well. Any ideas much appreciated. Thank you.

Here is my code

$res = mysql_query("SELECT h_id FROM hypermarket_em") or die(mysql_error());
echo"<tbody><tr>";
while($row = mysql_fetch_array( $res ))
{   
    $result = mysql_query("SELECT item_name FROM items_em WHERE h_id=".$row['h_id'])or die(mysql_error());
    while($drop_4 = mysql_fetch_array( $result ))
    {
         echo"<th scope=row>".$drop_4['item_name']."</th>";
         $rslt = mysql_query("SELECT price FROM items_em WHERE item_name='".$drop_4['item_name']."' and h_id=".$row['h_id'])or die(mysql_error());
         if (mysql_num_rows($rslt) == 0)
             echo"<td>N/A</td>";
         while($drop = mysql_fetch_array( $rslt ))
         {
             echo"<td>".$drop['price']."</td>";
         }
    }
}
echo"</tr></tbody>";

Try to get size of the $res, like this: size($res), if zero, means no result. — djakapm
– djakapm, Commented Nov 25, 2012 at 8:55
Can you explain what the X and Y axis are? What column do they correspond to? — Ayman Safadi
– Ayman Safadi, Commented Nov 25, 2012 at 9:19
X=h_name from hypermarket_em ,Y=item_name from items_em,cells are the price in items_em — Wafa
– Wafa, Commented Nov 25, 2012 at 9:20

Sean · Accepted Answer · 2012-11-25 09:21:20Z

1

Try reversing your mysql_num_rows and check if you have > than 0 rows-

if (mysql_num_rows($rslt) > 0){
    while($drop = mysql_fetch_array( $rslt )){
      echo"<td>".$drop['price']."</td>";}
}
else{
     echo"<td>N/A</td>";}

Edit-

I think what you are wanting to do then is something like this-

while($drop = mysql_fetch_array( $rslt )){
    if ($drop['price'] == ''){
      echo"<td>N/A</td>";}
    else{
      echo"<td>".$drop['price']."</td>";}

edited Nov 25, 2012 at 9:21

answered Nov 25, 2012 at 9:03

Sean

12.4k3 gold badges31 silver badges49 bronze badges

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

5 Comments

Wafa Over a year ago

as many times as the price for the item for a particular hypermarket is not available

Wafa Over a year ago

I have tried this code already. But the line "if ($drop['price'] == '')" doesn't seem to work, that's why i used mysql_num_rows..

Sean Over a year ago

what do you get when you echo mysql_num_rows($rslt)? Is it 6, what your screenshot shows?

Wafa Over a year ago

Hey thanks. Now i get it. I have to write the code in my previous while loop.

Sean Over a year ago

Yeah your SELECT is only getting the row where h_id=".$row['h_id'], so you won't get additional rows until you loop again at the top with while($row = mysql_fetch_array( $res ))

Habibillah · Accepted Answer · 2012-11-25 08:55:19Z

0

$data = mysql_fetch_array( $res );
if (!empty($data)) {
   while($row = mysql_fetch_array( $res ))
   {   
      $result = mysql_query("SELECT item_name FROM items_em WHERE h_id=".$row['h_id'])or die(mysql_error());
      while($drop_4 = mysql_fetch_array( $result ))
      {
         echo"<th scope=row>".$drop_4['item_name']."</th>";
         $rslt = mysql_query("SELECT price FROM items_em WHERE item_name='".$drop_4['item_name']."' and h_id=".$row['h_id'])or die(mysql_error());
         if (mysql_num_rows($rslt) == 0)
             echo"<td>N/A</td>";
         while($drop = mysql_fetch_array( $rslt ))
         {
             echo"<td>".$drop['price']."</td>";
         }
      }
   }
} else {
   echo "<td>No record found</td>";
}

answered Nov 25, 2012 at 8:55

Habibillah

28.8k5 gold badges40 silver badges59 bronze badges

1 Comment

Wafa Over a year ago

this did work. but the message is being displayed in wrong order.

Ayman Safadi · Accepted Answer · 2012-11-25 09:41:30Z

0

Not really sure what you're trying to do here, but it seems like you might want to simplify your query as well. Try something like:

SELECT
    *
FROM
    hypermarket_em h
    LEFT JOIN items_em i ON ( h.h_id = i.h_id );

Using this query, you can do something like:

while($drop = mysql_fetch_array( $rslt )){
    echo"<td>" . ( empty( $drop['price'] ) ? 'N/A' : $drop['price'] ) . "</td>";
}

edited Nov 25, 2012 at 9:41

answered Nov 25, 2012 at 9:07

Ayman Safadi

11.6k1 gold badge30 silver badges42 bronze badges

Comments

Wafa · Accepted Answer · 2012-11-27 06:38:37Z

So here is my answer.

$res = mysql_query("SELECT DISTINCT h_id FROM hypermarket_em") or die(mysql_error());
echo"<tbody><tr>";
while($row = mysql_fetch_array( $res ))
{   
    $result = mysql_query("SELECT item_name FROM items_em WHERE h_id=".$row['h_id'])or die(mysql_error());
    while($drop_4 = mysql_fetch_array( $result ))
    {
            echo"<th scope=row>".$drop_4['item_name']."</th>";
            $rslt = mysql_query("SELECT price FROM items_em WHERE item_name='".$drop_4['item_name']."' and h_id=".$row['h_id'])or die(mysql_error());
        while($drop = mysql_fetch_array( $rslt ))
        {
            echo"<td>".$drop['price']."</td>";
        }
    }
}
$res = mysql_query("SELECT DISTINCT h_id FROM hypermarket_em") or die(mysql_error());
while($row = mysql_fetch_array( $res ))
{
    $result = mysql_query("SELECT item_name FROM items_em WHERE h_id=".$row['h_id'])or die(mysql_error());
    if(mysql_num_rows($result)<=0)
        echo"<td>N/A</td>";
}
echo"</tr></tbody>";

I had to execute the same query twice as i want the item names and the price values in the order. So I first print the item names and then only the prices or "N/A" message.

Thank you all for your help..:)

Collectives™ on Stack Overflow

Display message in PHP when the result of mysql query has no value

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related