[RESOLVED] Loop between tables

[vinny199]

Hello,

I hope you can help me, been trying to solve this for hours.

I have 2 tables:

stoverview
and
stproducts

Both have the same primary key fireld: productid

I want to display on my page all items from table stproducts where they have a matching item in table st overview

for instance, table stoverview has 5 items

product id:
1
2
5
20
30

My table stproducts has 2000 items in it, but I only want to retrieve the results form that table where the productid number is also listed in teh stoverview table.

So, I should return 5 records out of that table.

I have tried all sorts of recordsets. no luck.

my closest result has been:

$query_rs_overview = "SELECT * FROM stoverview";
$rs_overview = mysql_query($query_rs_overview, $to_octo) or die(mysql_error());
$row_rs_overview = mysql_fetch_assoc($rs_overview);
$totalRows_rs_overview = mysql_num_rows($rs_overview);

$prod_id = $row_rs_overview['productid']; 

mysql_select_db($database_to_octo, $to_octo);
$query_rs_product = "SELECT * FROM stproducts WHERE stproducts.productid = '".$prod_id."'";
$rs_product = mysql_query($query_rs_product, $to_octo) or die(mysql_error());
$row_rs_product = mysql_fetch_assoc($rs_product);
$totalRows_rs_product = mysql_num_rows($rs_product);

but when I loop my table to retrieve all results, only the first one comes up...

Can you please help?

Many thanks,

Vinny

[laserlight]

You should use a join, e.g.:

SELECT * FROM stoverview, stproducts WHERE stoverview.productid=stproducts.productid

Instead of selecting all, select only those columns that you need.

[bpat1434]

What you'd want here is a SQL Join. Here is a good spot for a LEFT or RIGHT Join. Just know which way you want to go. I'll use RIGHT Join here, but it could also be done with a left join (direction of the join depends on (a) which direction you specify (RIGHT, LEFT) and (b) which table is on which side of the join operand).

So you want to limit the number of rows returned to be the amount of those found in stoverview. So we'll start with stproducts 😉

SELECT p.*
FROM `stproducts` p
RIGHT JOIN `stoverview` o
    ON p.`productid` = o.`productid`

Notice how the overview table productid is on the right of the "=" (operand)? For a left join, you'd have to switch them around so o.productid is on the left of the =. It's confusing, but if you read up on some of it, you should get the general idea.

Then once you have your result-set, you can just loop through it like any other result:

$query = "SELECT p.*
FROM `stproducts` p
RIGHT JOIN `stoverview` o
    ON p.`productid` = o.`productid`";
$result = mysql_query($result) or die('MySQL Error: ' . mysql_error());
while($row = mysql_fetch_assoc($result))
{
    print_r($row);
}

[laserlight]

What you'd want here is a SQL Join. Here is a good spot for a LEFT or RIGHT Join. Just know which way you want to go. I'll use RIGHT Join here, but it could also be done with a left join (direction of the join depends on (a) which direction you specify (RIGHT, LEFT) and (b) which table is on which side of the join operand).

I think an inner join suffices here since vinny199 wants to display "all items from table stproducts where they have a matching item in table st overview".

[bpat1434]

yes, but a RIGHT/LEFT join is more specific and less likely for confusion later. And I'm not sure if the innerjoin would return first all rows in both tables and then filter out, or only take rows that are necessary. Not sure if one would be faster or not. I tend not to use ambiguous queries so that later when I want to debug or change something, I know exactly what's going on and I don't have to "decipher" the meaning of the SQL. Just a personal preference.

[laserlight]

yes, but a RIGHT/LEFT join is more specific and less likely for confusion later.

But those are outer joins, which as far as I can tell, are not necessary here.

And I'm not sure if the innerjoin would return first all rows in both tables and then filter out, or only take rows that are necessary.

Neither are you sure how RIGHT and LEFT outer joins are implemented since the details may differ based on the database engine.

Not sure if one would be faster or not.

Faster than what?

I tend not to use ambiguous queries so that later when I want to debug or change something, I know exactly what's going on and I don't have to "decipher" the meaning of the SQL. Just a personal preference.

If so, then write:

SELECT * FROM stoverview INNER JOIN stproducts ON stoverview.productid=stproducts.productid

[bpat1434]

These are just two different views as to how to arrive at the same solution. Either way is perfectly valid.

I wasn't sure if an inner or outer join for this simple of a query would be faster. It may be that outer joins will be faster for larger data sets than smaller; however, I haven't done benchmarking to find out.

and yes, specifying INNER JOIN will make it more specific.

I'd also like to point out that your join is technically called a Gaussian join in that each row of every table is matched up with the other. The WHERE clause limits what rows are returned. This may be a performance hit with larger datasets; whereas, the outer joins might be faster because they're not creating a huge dataset and then limiting, but limiting as it goes. I'm not sure about it; however, it's something to look into.

I don't declare myself to be a SQL god; however, both methods are perfectly valid ways to get the same solution. One may be more/less resource intensive and faster/slower than the other.

[laserlight]

These are just two different views as to how to arrive at the same solution. Either way is perfectly valid.

hmm... what I had in mind was a situation where a row in stoverview does not match any in stproducts. The desired outer join would produce an incorrect result set, but an inner join would not. On the other hand, I overlooked that there may well be a foreign key constraint that eliminates this anomaly, in which case the desired outer join would produce identical results to the inner join.

I'd also like to point out that your join is technically called a Gaussian join in that each row of every table is matched up with the other. The WHERE clause limits what rows are returned. This may be a performance hit with larger datasets;

I had the impression that this is merely conceptual: it is not a given that the Cartesian product must be computed, and only then restriction begins. After all, outer joins can be constructed in a similiar way.

[bpat1434]

laserlight wrote:

hmm... what I had in mind was a situation where a row in stoverview does not match any in stproducts. The desired outer join would produce an incorrect result set, but an inner join would not. On the other hand, I overlooked that there may well be a foreign key constraint that eliminates this anomaly, in which case the desired outer join would produce identical results to the inner join.

The OP specified that both tables had the "productid" column which was the link between them. Hence why I jumped to an outer-join. From what I understand, the OP wants only those rows in stproducts where there is a corresponding item in stoverview. So an outer join using stoverview as its basis would produce a correct result set (from my thinking, haven't tried it). I think it all depends on how you set it up 😉

[laserlight]

So an outer join using stoverview as its basis would produce a correct result set

Yes, assuming that the productids of stoverview was a subset of the productids of stproducts. I think that this is probably the case, but such an assumption would be unnecessary with an inner join.

I think it all depends on how you set it up

I agree.

[vinny199]

Wow, i didn't realize I'd started such a debate!

yes, the inner join was perfect.

I used this

SELECT * FROM stoverview, stproducts WHERE stoverview.productid=stproducts.productid

Did exactly what I needed.

But I've read all your threads and learned plenty.

Thank a lot laserlight & bpat1434.

just great.

vinny