mysql query need some loop or array

Question

I need help with a mysql query. I have several products with the same order_id and then want to show everyone but it only shows one product of 30 products.

if (!$_GET['orderid']) {

   } 
else
{
    $order_id = ereg_replace("[^0-9]", "", $_GET['orderid']); // filter everything but numbers for security
}

$sqlCommand = "SELECT * FROM tabell_order_product WHERE order_id='$order_id'"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
while ($row = mysqli_fetch_array($query)) { 
    $product_id = $row["product_id"];

} 
mysqli_free_result($query); 
//---------------------------------------------------------------------------------
$sqlCommand = "SELECT * FROM tabell_product WHERE product_id='$product_id'"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
while ($row = mysqli_fetch_array($query)) { 
    $model = $row["model"];
    $location = $row["location"];
} 
mysqli_free_result($query); 
//---------------------------------------------------------------------------------

$sqlCommand = "SELECT * FROM tabell_product_description WHERE product_id='$product_id'"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
while ($row = mysqli_fetch_array($query)) { 
    $name = $row["name"];
} 
mysqli_free_result($query); 
$output .= '<tr>
    <td>' . $product_id . '</td>
    <td>' . $name . '</td>
    <td>' . $model . '</td>
    <td>' . $location . '</td>
  </tr>';?>

You should seriously look up a join for your query. That way, you can minimize all of your queries. — Daryl Gill
– Daryl Gill, Commented Sep 4, 2013 at 8:13
Your product_id, model, location and name will only hold the last row from the database, you are overwriting them in every while loop. — slash197
– slash197, Commented Sep 4, 2013 at 8:16
You should either move the logic to make the html table inside the while loop or retrieve values in an array and use another loop — skv
– skv, Commented Sep 4, 2013 at 8:17

Rohan Kumar · Accepted Answer · 2013-09-04 08:27:25Z

1

Why are you calling three queries if it can be done in a single query

Try this,

<?php
    $sqlCommand = "SELECT t2.product_id,t2.name,t3.model,t3.location FROM
       tabell_order_product t1, tabell_product t2, tabell_product_description t3 
       WHERE t1.order_id='$order_id' AND t2.product_id=t3.product_id"; 
    $query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
    while ($row = mysqli_fetch_array($query,MYSQLI_ASSOC)) { 
        $product_id=$row['product_id'];
        $name=$row['name'];
        $model=$row['model'];
        $location=$row['location'];
        $output .= '<tr>
            <td>' . $product_id . '</td>
            <td>' . $name . '</td>
            <td>' . $model . '</td>
            <td>' . $location . '</td>
        </tr>';
    }
?>

Read mysqli-fetch-array

answered Sep 4, 2013 at 8:27

Rohan Kumar

40.7k11 gold badges81 silver badges111 bronze badges

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

1 Comment

Pontus Johansson Over a year ago

Thanks, precisly what i needed :) i didn't know how to explain :)

Donald · Accepted Answer · 2013-09-04 08:57:46Z

0

while ($row = mysqli_fetch_array($query)) { 
    $product_id = $row["product_id"];
} 
var_dump($product_id);

above will only get the last product_id of the 30 product_ids，i guess you mean push them into an array like this

while ($row = mysqli_fetch_array($query)) { 
    $product_id[] = $row["product_id"];
} 
var_dump($product_id);

and you have the some errors in the follow code, try to fix them in this way

answered Sep 4, 2013 at 8:57

Donald

1247 bronze badges

Collectives™ on Stack Overflow

mysql query need some loop or array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related