MySQL query only showing one row

[jay83rr]

i think the error is in query3 im not sure if i've formatted this correctly.

 

 

<?php
session_start();
//printf('<pre>%s</pre>', print_r($_SESSION, true));
require('includes/config.inc.php');
require('includes/session.php');
//WORKING
DB_Connect();
$query = "SELECT * FROM food_delivery_orders_items WHERE food_delivery_orders_items.type = 'product' AND food_delivery_orders_items.order_id=".$_SESSION['pdf_quote']['id']."";
  
$result = mysql_query( $query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
   {
   $hash = $row['hash'];
   $foreignid = $row['foreign_id'];
    $qty = $row['cnt'];     
   }
   
     
$query2 = "SELECT * FROM food_delivery_products WHERE food_delivery_products.id = ".$foreignid."";
$result2 = mysql_query( $query2) or die(mysql_error());
while($row = mysql_fetch_assoc($result2))
   {
   $name = $row['name'];
   $description = $row['description'];    
   }
 
 
 //---------------------------------------------------------------------------------------------------------------
 
 $query3 = "SELECT * FROM food_delivery_orders_items WHERE food_delivery_orders_items.type = 'extra' AND food_delivery_orders_items.order_id=".$_SESSION['pdf_quote']['id']."";
  
$result3 = mysql_query( $query3) ;
while($row = mysql_fetch_array($result3))
   {
 
   $foreignidextra = $row['foreign_id']; 
   $qtyextra = $row['cnt'];   
   }
   print $row;
   
   $query4 = "SELECT * FROM food_delivery_extras WHERE food_delivery_extras.id = ".$foreignidextra."";
$result4 = mysql_query( $query4) or die(mysql_error());
while($row = mysql_fetch_array($result4))
 
   {
   $nameextra = $row['name'];
     
   }
 echo $nameextra;
 echo $qtyextra;
DB_Disconnect();
 
 
//$products = implode(", ",$_SESSION['pdf_quote']['product_arr']);;
//print $products
?>

[mac_gyver]

here are some programming/forum hints (yes one of them addresses getting only one result from a query) -

 

1) when posting code in a forum, surround it with the forum's

bbcode tags (the edit form's <> button). this makes the code easier to distinguish from the rest of you post, highlights syntax problems, if any, in the code, and prevents the forum software from modifying the code so that someone could copy/paste it if needed and get the actual code.

 

2) if you only expect one row from a query, don't use a loop to fetch the results. this wastes processing time and confuses anyone reading the code since a loop implies there could be more than one row in the result set.

 

3) if you do expect more than one row from a query, use a loop to fetch the results. you must then either use the data from each row inside the loop or you must store that data in an array to be used later in the code.

 

4) your sql query statements are cluttered by unnecessarily repeating the (verbose) table name with each column reference. clutter in code generally makes it harder to pick out the actual meaning/logic.

 

5) your separate queries are related and should be JOIN'ed into just one (or two) query. this will also eliminate the need for the variables ending in numbers, which is a sign you are writing too much code to accomplish a task.

 

at this point, no one here knows which of your queries you expect to return more than one row and that the data should be looped over, because you are looping to fetch the result from all the queries, but you are only storing the fetched data in scaler variables, so only the last result from each query will be available after the end of the loops.

Edited October 16, 2013 by mac_gyver

[gizmola]

No, it looks like it's a logic error.  Truthfully it's hard to understand what you're trying to do, as your indentation is a mess, however, it looks like you want to loop on the result3 query and do the result4 query for every result, but you do not have the right block setup for that.  As it is now, you loop through all the query3 results and you're left with the last one at the end.

 

 

while($row = mysql_fetch_array($result3))
   {
 
   $foreignidextra = $row['foreign_id']; 
   $qtyextra = $row['cnt'];   
   }
   print $row;

 

Note how your while block does the fetch_array repeatedly and then when the loop is done you print out the last row it would have fetched.

 

If you just move the print $row up, you'll see the effect:

 

while($row = mysql_fetch_array($result3)) 
   {
 
       $foreignidextra = $row['foreign_id']; 
       $qtyextra = $row['cnt'];  
        print $row;
   }
   

 

Of course you need to actually move all the query 4 code inside this block, because the foreignidextra variable you are setting in the loop will also only have the last value and you will only query once, with your current blocking.

[jay83rr]

i managed to do it another way, by doing a LEFT JOIN and it gave me the desired result. I would like to try and consolidate my queries but it's so confusing as it reads data from multiple tables and queries them to give a result.

 

also the fact that im trying to do something that im a novice at creates more problems!

Edited October 16, 2013 by jay83rr

[cyberRobot]

I have marked the topic as solved. If you need anything else, please mark it as unsolved...or start a new topic. 

[mac_gyver]

by doing a LEFT JOIN

 

 

since the only way to get an item in to an order would be for that item to exist, just a regular/inner JOIN would be all that you need.

 

you can further reduce the code/logic if your 'extra' items and the 'product' items were all in one table. they are all things that you can select and add to a cart. putting them in separate tables only complicates what you must do at each step.

 

once they are in one table, you can directly get all the details for an order using one JOIN query.