Page 4 - Cant get a select list working with joined table. HELP

Dev Shed Forums Sponsor:

#46

October 4th, 2012, 09:24 AM

jonnyfreak

Contributing User

Join Date: Sep 2012

Posts: 67

Time spent in forums: 15 h 54 m 55 sec
Reputation Power: 1

Quote:

Originally Posted by badger_fruit

Wait a second, you only have 1 product with product ID 5 so then your SQL needs to be totally different ... if you're wanting to show all available sizes for a certain product then try this instead ...

SELECT *
FROM `poochieSizes` AS `sizes`
RIGHT JOIN `poochieStock` AS `stock` ON `stock`.`SizeID` = `sizes`.`SizeID`
WHERE `stock`.`stockID` = 5;

I mean, that's just a stab in the dark but if you're selecting FROM Products and you only have 1 that will match your WHERE then you're only going to get 1 result.

I think .... although I'm pretty sure that's right

:S

Try it, your code doesn't work now so it's not going to make it any worse lol !

EDIT > If that SQL above works then you can RIGHT JOIN the products table to it if need be

yes true, thanks for the input. will do your suggestion and get back to you,

#47

October 4th, 2012, 09:26 AM

jonnyfreak

Contributing User

Join Date: Sep 2012

Posts: 67

Time spent in forums: 15 h 54 m 55 sec
Reputation Power: 1

Quote:

Originally Posted by jonnyfreak

yes true, thanks for the input. will do your suggestion and get back to you,

just had a thought. in the DB the id 5 dies only currently have one size but more will be added, should i still change to your suggestion?

#48

October 4th, 2012, 09:33 AM

badger_fruit

Confused badger

Join Date: Mar 2009

Location: West Yorkshire

Posts: 760

Time spent in forums: 1 Week 4 Days 5 h 15 m 18 sec
Reputation Power: 339

Quote:

Originally Posted by jonnyfreak

just had a thought. in the DB the id 5 dies only currently have one size but more will be added, should i still change to your suggestion?

Yes, try other product IDs as well though, for example something with more than 1 result

__________________
The number for UK Emergencies is changing, the new number is 0118 999 881 999 119 7253

"For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
- George Orwell, 1984

#49

October 4th, 2012, 09:38 AM

jonnyfreak

Contributing User

Join Date: Sep 2012

Posts: 67

Time spent in forums: 15 h 54 m 55 sec
Reputation Power: 1

Quote:

Originally Posted by badger_fruit

Yes, try other product IDs as well though, for example something with more than 1 result

ok will do and let you know.

#50

October 4th, 2012, 04:51 PM

jonnyfreak

Contributing User

Join Date: Sep 2012

Posts: 67

Time spent in forums: 15 h 54 m 55 sec
Reputation Power: 1

Quote:

Originally Posted by badger_fruit

Yes, try other product IDs as well though, for example something with more than 1 result

looking at this all stock ID's will be different wont they as these are the auto_increment?

#51

October 5th, 2012, 05:04 AM

jonnyfreak

Contributing User

Join Date: Sep 2012

Posts: 67

Time spent in forums: 15 h 54 m 55 sec
Reputation Power: 1

sorted it and started from scratch

just joined all the table in one query and echoed out what i needed too.....i was over complicating things

$query_rsProdList = sprintf("SELECT * FROM poochieProd, poochieCat, poochieSizes, poochieStock WHERE poochieProd.CatID = poochieCat.CatID AND poochieProd.ProdID = poochieStock.ProdID AND poochieSizes.SizeID = poochieStock.sizeID AND poochieProd.ProdID = %s ", GetSQLValueString($var1_rsProdList, "int"));

<select name="os0" class="text" id="selectSize">
<option value="Select Size">Select Size</option>
<?php do { ?>
<option value="<?php echo $row_rsProdList['SizeID']; ?>"><?php echo $row_rsProdList['Size']; ?></option>
<?php } while ($row_rsProdList = mysql_fetch_assoc($rsProdList)); ?></select>

the thing i need to add though is if the stock = 0 dont display size or show a "sold out" next to the size

thanks for everybodys help

Page 4 - PHP-General - Cant get a select list working with joined table. HELP

Developer Shed Advertisers and Affiliates