SELECT Query calling 2 Tables

KrisCons

Hi, (lets begin!)

I have 2 forms, 1st being a DIV STYLE Table containing a list of 'Products' (productlist.php) which when one item from the list is clicked, it takes you to a 2nd form (products.php) which pulls through data from a database and echos the information through this standard form relating to that 'Product'.

Thats working soopa doopa, but I have one section on the second form which calls data from two different tables and I have done this by using JOINS. The query works a treat if you actually specify a single 'Product' in the SELECT Query, but I want it to actually pull through the correct 'UNITS' relating to the 'Product' selected on the 1st Form. (I hope that makes sense).

Basically, when you select a Product on Form 1 the PRODUCTUNIQUEID is passed to the variable $PRODID. FORM 2 (attached) pulls all the relative information for that selected product from tblproducts and echos it to the screen. But i need the relative 'UNITS' from tblproductunits to also come through on the same form but everytime I run the query, ALL units for ALL of my PRODUCTS appear each time no matter what PRODUCT I select on Form 1. :mad:

How can I get it so only the relative units for the selected PRODUCT come through?

(If anyone is still reading this and hasn't fallen asleep,I would be very grateful for any help..)

Chimera

Please post the query you are using to get the data from tblproductunits, so we can see how it's working.

KrisCons

This is the query, Thanks for your help..!

?php

$SQLQuery = "SELECT tblproducts.productuniqueid, tblproductunits.unitno, tblproductunits.unitname

FROM tblproductunits, tblproducts
WHERE tblproducts.productcode = tblproductunits.productcode;";

$Result = mysql_query($SQLQuery);
while ($Row = mysql_fetch_array($Result)) {
$unitno = $Row["unitno"];
$unitname = $Row["unitname"];
echo " <tr>
<td width=\"20%\" height=\"22\" valign=\"top\">$unitno</td>
<td width=\"60%\" valign=\"top\">$unitname</td>
</tr>\n";
}
mysql_free_result($Result);

Any Ideas?

liquidchild

Where in the query do you actually pass the value you are getting from the form i.e the product id?

KrisCons

}
if ($prodid > 0) {

$SQLQuery = "SELECT productcode, productname, sector, producttype, glh, level, weightingfactor, lscqualificationcode, accreditationstartdate, accreditationenddate, certificationenddate, fundingtype, replaces, minimumentryage, productinactive, dateinactive, underdevelopment, targetgroup, possibleprogressionroute, additionalinformation, noofmandunitrqd, noofoptunitrqd, feecode, publishtowebsite, internalassessment, externalassessment, infoforprocessing FROM tblproducts

WHERE productuniqueid = $prodid";

$Result = mysql_query($SQLQuery);
while ($Row = mysql_fetch_array($Result)) {
$prodid = $Row["id"];
$productcode = $Row["productcode"];
$productname = $Row["productname"];
$sector = $Row["sector"];
$producttype = $Row["producttype"];
$glh = $Row["glh"];
$level = $Row["level"];
$weightingfactor = $Row["weightingfactor"];

And the list goes on from here...

I also have a hidden field at the bottom of my page :

Is this helping?
I have tried allsorts of changes, but each time I either get all Units for all of my products, or a mysql error referring to fetch arrays..

Chimera

Try this for your second query :

$SQLQuery = "SELECT tblproducts.productuniqueid, tblproductunits.unitno, tblproductunits.unitname

FROM tblproductunits, tblproducts
WHERE tblproducts.productcode = tblproductunits.productcode AND tblproducts.productuniqueid = $prodid";

KrisCons

Thats worked!

I had to comment out my $prodid = $Row["id"]; for whatever reason, but your advice was what i needed - thanks very much!

🙂 🙂 🙂 🙂 🙂