Jump to content


Photo

Extracting info from database using php


  • Please log in to reply
3 replies to this topic

#1 OriginalSunny

OriginalSunny
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts

Posted 06 March 2006 - 10:20 PM

Hi, i need to extract info and display it. The only thing is i have 2 tables. As you can see below this code works when extracting info from one table.

$query_food = "SELECT * FROM stock WHERE
tariff='$_POST[interest]' AND
prodDesc = 'paygo' ORDER BY tariff";
$result = mysql_query($query_food,$connect)
or die ("query_food: ".mysql_error($connect));

This works as when the user selects a certain tariff the above code selects the correct products. However i also want to do a query using the manufacturer name. But the maufacturer is stated in another table called stock_item. (if i therefore swap manufacturer for tarrif in the above code it wont work as manufacturer is in a different table). So how do i do this?? The tables i have used are:

stock(stockID, colour, modelnum, tariff, pic)
stock_item(modelnum, manufacturer)

Thanks.

#2 Pneumonic

Pneumonic
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 06 March 2006 - 10:38 PM

I may not understand what you're trying to do, but wouldn't this work? Are you trying to retrieve the manufacturer from when the user selects the tariff? If that's the case, then you'd make a field in your form 'modelnum' and just use:
$query_man = "SELECT * FROM stock_item WHERE modelnum = $_POST['modelnum']";
$query_man_result = mysql_query($query_food, $connect) or die ("query_man: ".mysql_error($connect));


#3 OriginalSunny

OriginalSunny
  • Members
  • PipPipPip
  • Advanced Member
  • 95 posts

Posted 06 March 2006 - 11:37 PM

[!--quoteo(post=352289:date=Mar 6 2006, 05:38 PM:name=Pneumonic)--][div class=\'quotetop\']QUOTE(Pneumonic @ Mar 6 2006, 05:38 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I may not understand what you're trying to do, but wouldn't this work? Are you trying to retrieve the manufacturer from when the user selects the tariff? If that's the case, then you'd make a field in your form 'modelnum' and just use:
$query_man = "SELECT * FROM stock_item WHERE modelnum = $_POST['modelnum']";
$query_man_result = mysql_query($query_food, $connect) or die ("query_man: ".mysql_error($connect));
[/quote]

No not quite. The user is given a list of options to choose from. These options are now the list of manufacturers (previously they were tariff, thats why before the user selected the tariff and the products under that tariff were displayed). Now the manufacturers are displayed where $_POST['interest'] (interest is the manufacturer selected by the user). So i have to get the manufacturer and display the phones under the manufacturer, only the phone details are in the stock table and the manufacturer is in the stock_item table. The link from the stock table to the stock_item table is via 'modelnum'. Any suggestions??


#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 March 2006 - 11:44 PM

You need to join the two tables using the modelnum columns

SELECT s.* , i.manufacturer
FROM stock s
INNER JOIN stock_item i 
ON s.modelnum = i.modelnum
WHERE i.manufacturer = '$mfr'


If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users