Hi all
this below query works fine and joins two tables
But it shows results of all order_id’s
How i can fetch result of only 1 order_id=751
$q = "select * from order_table as nw
inner join order_details as odt
on nw.order_id=odt.order_id";But if i want only to fetch rows from both tables where order_id=751 then how can i fetch it ??
where can i put the condition to show result of only order_id=751 ??
vineet
SELECT something
, anything
, just_not_the_dreaded_evil_select_star
FROM order_table as nw
INNER
JOIN order_details as odt
ON odt.order_id = nw.order_id
WHERE nw.order_id = 751
hi r937
thanks for the reply.
the above problem is solved.
A new problem has arisen.
can you help me with it.
order_table contains order_id and order_total
order_details table contains order_id,product_name, quantity, price
now i want to display order_id and order_total only once
But want to show all products from order_detail_table associated with that order_id under it.
Problem i am facing is order_id is inside while loop
$q = "select nw.order_id,nw.order_total,odt.product_name,odt.quantity,odt.price from order_table as nw
inner join order_details as odt
on nw.order_id=odt.order_id
where nw.order_id=751";
$result = mysql_query($q);
while($row = mysql_fetch_array($result))
{
echo $row['order_id'];
echo "<br>";
echo $row['order_total'];
echo "<br>";
echo $row['product_name'];
echo "<br>";
echo $row['quantity'];
echo "<br>";
echo $row['price'];
echo "<br>";
}so if there are two products to be shown then “order_id” is displayed two times.
The above code outputs results as
**order_id = 751**
order_total = 1025.00
product_name = nokia mobile
quantity = 2
price = 250.00
**order_id = 751**
order_total = 1025.00
product_name = samsung mobile
quantity = 3
price = 175.00
if i take out “order_id” out of while loop then while loop outputs only 1 LAST product, not all.
$q = "select nw.order_id,nw.order_total,odt.product_name,odt.quantity,odt.price from order_table as nw
inner join order_details as odt
on nw.order_id=odt.order_id
where nw.order_id=751";
$result = mysql_query($q);
$row = mysql_fetch_array($result);
echo $row['order_id'];
echo "<br>";
echo $row['order_total'];
echo "<br>";
while($row = mysql_fetch_array($result))
{
echo $row['product_name'];
echo "<br>";
echo $row['quantity'];
echo "<br>";
echo $row['price'];
echo "<br>";
}How can i display “order_id” only once but all products below it.
order_id = 751
order_total = 1025.00
product_name = nokia mobile
quantity = 2
price = 250.00
product_name = samsung mobile
quantity = 3
price = 175.00
How can i display “order_id” only once but all products below it like the above example.
i dont want to display order_id again and gain after every product.
vineet
with php, not sql
the sql should be a simple join
in your loop where you process each result row of the query, you need “prev/current” logic – if the order number is the same, don’t print it
with if statement
while($row = mysql_fetch_array($result))
{
if($row['order_id'] == $row['order_id'])
{
echo $row['order_id'] == "NULL";
echo "<br>";
}it will not echo value at all.
i want to echo it atleast once.
may be my if statement is not correct ??
vineet
i can’t help you, i don’t do php
perhaps you might want to ask one of the forum moderators to move this thread to the php forum
hi moderators
can you please move my post or thread to “PHP FORUM” for more help on my this topic
thanks
vineet
Moved.
This bit of code here:
while($row = mysql_fetch_array($result))
{
if($row['order_id'] == $row['order_id'])
{
echo $row['order_id'] == "NULL";
echo "<br>";
}isn’t correct. Your if() statement checks whether a variable is the same as itself, which of course it always is. If it is (i.e. always), you display the result of whether the same variable is NULL, which will give true or false.
If you’re trying to only display the order id the first time it changes, then as @r937 said above, you need some logic to remember the order_id from the previous run around the loop, check if it’s changed, and only display if it is. If you’re only actually displaying one order, as your code in post #3 suggests, then I don’t understand the problem. The second code you put there, where you echo order_id before the loop, is fine, but you have to remember that you’ve already done one fetch instruction, so the first row has already been retrieved. So when you do the loop, the product that’s in the first row won’t appear. So the easiest way is to probably display the details from the first item before the loop.
$row = mysql_fetch_array($result); // this retrieves the first row, with the first product.
echo $row['order_id'];
echo "<br>";
echo $row['order_total'];
echo "<br>";
echo $row['product_name'] . "<br>" . $row['quantity'] . "<br>" . $row['price']; // this is the first row
while($row = mysql_fetch_array($result))
{
echo $row['product_name'];
echo "<br>";
echo $row['quantity'];
echo "<br>";
echo $row['price'];
echo "<br>";
}Because your order only has two lines in it, it seems that it is only outputting the last one where in fact it is outputting everything except the first one.
If your code is going to output more than one order, then some kind of logic for tracking the previous order_id is better. Also have a look at mysqli or PDO for accessing the database as the old-style functions you’re using may cause you some problems soon as they have been removed from the latest version of the language.
hi droopsnoot
thanks for the reply
Althought you given code works correctly
but i would like to know or clear my doubts on this code
why cant we just echo only two columns (order_id and order_total) before while loop ??
why do we need to add rest of the columns also when we dont need to echo those columns values before while loop ??
we are echoing 5 column values before while loop, when we need only 2 column values ??
if we echo only 2 column names and remove rest 3 column names before
while loop then while loop doesnt echo all products ?? why ??
vineet
Your code basically does this:
run the query
get the first result
display the order id and order total from the first result
-- start a loop
get the next result
display the name, quantity and price from the next result
-- end the loopSo, you can see there that when you get the first result to display the order_id and order_total, you’ve also got a name, quantity and price in that result that you don’t do anything with. By the time you get into your loop to display the records, you’ve now read the second result and the first is gone. Hence you need to display the extra information from the first result somewhere.
Another way around the problem would be to use a do…while loop instead. This will always execute the first time you run it:
$row = mysql_fetch_array($result);
echo $row['order_id'];
echo "<br>";
echo $row['order_total'];
echo "<br>";
do
{
echo $row['product_name'];
echo "<br>";
echo $row['quantity'];
echo "<br>";
echo $row['price'];
echo "<br>";
} while($row = mysql_fetch_array($result));In this case, the code to get the second and subsequent results comes after you’ve displayed the information from the first row, so would do the trick. But you would need to add some code to check that you’ve had any results at all, which to be fair you should do in either approach.
Hi droopsnoot
thanks the detailed explanation and solution
vineet
No problem, glad it helped.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.