Topic: I need help comparing two queries.

[co.ador]

Code: [Select]

<?php 

$query1= "SELECT id, name
FROM products 
$result = mysql_query($query1, $connection);
 while ($row = mysql_fetch_array($result)) {
echo'<div id="namerating">
<p class="name">Name:</p><p class="p">'. $row['name'] . '</p>
</div>';

$query2="SELECT product_id, variety, price, description,image 
FROM product_varieties
WHERE product_id= "the id in $query1, How can I script it in here?" 
$result2 = mysql_query($query2, $connection);
$row2=mysql_fetch_array($result2) {
echo '<div class="firstiteration">
<div class="small">
<p class="size">'. $row2['variety'] . '</p>
<img src="'. $row2['image'] . '" width="80" height="90"/>
<p class="price">'. $row2['price'] . '</p>
<p class="serve1">'. $row2['description'] . '</p>
</div>';
}
}
?>
How can I compare the product_id with the id in $query1?

Help...

[jl5501]

You only need 1 query

Code: [Select]

<?php 
$query1= "p.id, p.name,v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.id = v.product_id
FROM products 
$result = mysql_query($query1, $connection);
?>

[jl5501]

sorry


<?php 
$query1= "select p.id, p.name,v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.id = v.product_id 
$result = mysql_query($query1, $connection);
?>


that is correct

[co.ador]

ok the SELECT thank you..

That would be call a join of table?

[jl5501]

That is an implicit join yes

I am also using table aliases to simplify the typing

[co.ador]

The aliases like p and v. Thank you for the explanation but, I did one mistake I apologizes. I miss to put in the first post that products table has a field called p.colon_id and it is compare with a variable coming from the url call menu.

the query alone would look like:


$query1= "select p.id, p.name, p.image, p.colon_id
from products p
where p.colon=" .(int) $_GET['menu']
$result=mysql_query($query1,$connection);
$row = mysql_fetch_array($result) {


But I wonder how it would look with the implicit join?  I have done as below but don't know if it is correct.
correct me if I am wrong please.

<?php 
$query1= "select p.id, p.name, p.image, p.colon_id, v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.colon=" .(int) $_GET['menu'] AND p.id = v.product_id 
$result = mysql_query($query1, $connection);
?>

[jl5501]

you forgot to go back into quotes, but essentially correct

Code: [Select]

$query1= "select p.id, p.name, p.image, p.colon_id, v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.colon=" .(int) $_GET['menu'] ."AND p.id = v.product_id";


[jl5501]

with a space inside the quotes before the and

[co.ador]

Code: [Select]

$query1= "select p.id, p.name, p.image, p.colon_id, v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.colon=" .(int) $_GET['menu'] ." AND p.id = v.product_id ";
What's the reason for the quotes before AND thanks.

[co.ador]

$query1= "select p.id, p.name, p.image, p.colon_id, v.product_id,v.price,v.description,v.image from products p,product_varieties v where p.colon=" .(int) $_GET['menu'] ." AND p.id = v.product_id ";
$result = mysql_query($query3, $connection);
while ($row4 = mysql_fetch_array($result)) { // line 258
html....
}



PHP is throwing the following WARNING and ít's not letting the data to query and display

Code: [Select]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
public_html/example2.php on line 258
Help...

[co.ador]

any suggesstion on reply #9?

Thanks

[jl5501]

yes

you have p.colon  in there where clause. Should that be p.colon_id?

[co.ador]

I went to phpmyadmin and the query had a field that didn't exist.

[jl5501]

exactly

[co.ador]

Now it is displaying three row of the same product, Plus prices and varieties of one product is displaying it in different rows as well. 

What I am trying to aim is the price 20.30 to display in small tray of the first row, then 25.90 display in the medium tray of the first row, and 30.90 display on large tray of the first row. As of displaying as it is now.

Code: [Select]

CREATE TABLE products (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(255)
);

CREATE TABLE product_varieties (
product_id INT,
variety VARCHAR(100),
price DOUBLE,
description TEXT,
PRIMARY KEY (product_id, variety)
);

INSERT INTO products (id, name) VALUES (1, 'Cotél de camarone');
INSERT INTO product_varieties (product_id, variety, price) VALUES (1, 'Small Tray',   2.9, );
INSERT INTO product_varieties (product_id, variety, price) VALUES (1, 'Medium Tray', 6.9,);
INSERT INTO product_varieties (product_id, variety, price) VALUES (1, 'Large Tray', 8.9, );

This is the link
http://www.nyhungry.com/example2.php?subject=4&id=2&register=1&menu=38

What's your take on this.

[co.ador]

Thank you guys I got it.