Using data from one table to get data from second

[schmidt82]

Hi,

I've just started working with PHP and started to create my own webpage and have run into a problem I can't seem to fix.

I'm trying to get data from two tables. One called 'barer' and the other 'indeks'. 'barer' is a list of stores with name, address, website etc. 'indeks' is a table with prizes, opening hours discounts etc.

What I'm trying to do is: when the user chooses the city and opening hours they want, I would like the site to find the city_id in 'barer' and find the matches in 'indeks'. When it knows which stores are in the chosen city, I need it to sort the list according to opening hours, so that stores with opening hours outside of the chosen will not be shown.

This is what I have so far:

 

$findBarIdBy_sql = "SELECT * FROM barer WHERE city_id = '$city_id'";
		$findBarIdBy_query = mysql_query($findBarIdBy_sql) or die(mysql_error());
		while($rsfindBarIdBy = mysql_fetch_assoc($findBarIdBy_query)){
		$store_id = $rsfindBarIdBy['store_id']." - ";

		echo $store_id;

 

This works ok to show the store_id, but I don't know where to go from there to get the rest of the information. I would like to show storename, address, city, webpage from 'barer' and open, close, brand and prices from 'indeks'

 

I hope this makes sence, and someone will be able to help me out.

Thanks

[schmidt82]

Would it be possible to get each information that I want out as variables and then put it all together after? To make discount calculations and put it into a table

[fugix]

Look into using a Join

[YoungNate_Black_coder]

join is good, but to keep it simple u could do what u are trying to just exclude the while loop like this:

 

$findBarIdBy_sql = "SELECT * FROM barer WHERE city_id = '$city_id'";

$findBarIdBy_query = mysql_query($findBarIdBy_sql) or die(mysql_error());

$row_barid = mysql_fetch_assoc($findBarid)or die(mysql_error());

 

// then put the row u want into a varible and query again with sed varible

$barid = $row_barid['store id '];

 

//

query again and put that $barid into your query .....

the method i use i never use joins take longer !

[HDFilmMaker2112]

join is good, but to keep it simple u could do what u are trying to just exclude the while loop like this:

 

$findBarIdBy_sql = "SELECT * FROM barer WHERE city_id = '$city_id'";

$findBarIdBy_query = mysql_query($findBarIdBy_sql) or die(mysql_error());

$row_barid = mysql_fetch_assoc($findBarid)or die(mysql_error());

 

// then put the row u want into a varible and query again with sed varible

$barid = $row_barid['store id '];

 

//

query again and put that $barid into your query .....

the method i use i never use joins take longer !

 

JOIN is actually quicker... Let MySQL do as much as possible. Every time you do a new query, your starting...stopping... and then starting MySQL again. If I'm not mistaken. And it's cleaner code.

[Zane]

I would like to show storename, address, city, webpage from 'barer' and open, close, brand and prices from 'indeks'

$store_id = $rsfindBarIdBy['store_id']." - ";

This line is where you would get such information.

 

to get the storename you would simply add this

$theName = $rsfindBarIdBy['storename'];

etcetera...

[schmidt82]

Thanks for helping me out.

 

I tried the join and got this:

 

"SELECT barer.by_id as 'by_id',
		barer.bar_id as 'bar_id',
		barer.bar as 'bar',
		barer.adresse as 'adresse',
		$valgt_tabel.dag as 'dag',
		FROM $valgt_tabel 
		INNER JOIN barer
		ON barer.bar_id = $valgt_tabel.bar_id
		WHERE by_id = '$by_id'";

 

But I get an error message that I can't figure out how to fix:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM oelindeks INNER JOIN barer ON barer.bar_id = oelindeks.bar_id ' at line 6

 

I'll try to explain what I'm trying to do:).

$valgt_tabel is the table the user chooses on the site, and it is a variable since i have three different indeks tables to choose from.

So, the user chooses an indeks table and a city. The chosen city gives a 'by_id' and using that id, it should find the stores with matching 'by_id' in the 'barer' table, find their 'bar_id', and find the matching 'bar_id's' in the $valgt_tabel (the chosen indeks table), and also show the 'dag' column from the $valgt_tabel.

 

Hope this makes sence:)

[schmidt82]

I figured it out with a join. Thank you very much for all your help