schmidt82 Posted June 19, 2011 Share Posted June 19, 2011 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 Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/ Share on other sites More sharing options...
schmidt82 Posted June 19, 2011 Author Share Posted June 19, 2011 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 Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1231653 Share on other sites More sharing options...
fugix Posted June 19, 2011 Share Posted June 19, 2011 Look into using a Join Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1231657 Share on other sites More sharing options...
YoungNate_Black_coder Posted June 19, 2011 Share Posted June 19, 2011 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 ! Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1231665 Share on other sites More sharing options...
HDFilmMaker2112 Posted June 19, 2011 Share Posted June 19, 2011 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. Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1231676 Share on other sites More sharing options...
Zane Posted June 19, 2011 Share Posted June 19, 2011 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... Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1231757 Share on other sites More sharing options...
schmidt82 Posted June 19, 2011 Author Share Posted June 19, 2011 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:) Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1231847 Share on other sites More sharing options...
schmidt82 Posted June 22, 2011 Author Share Posted June 22, 2011 I figured it out with a join. Thank you very much for all your help Link to comment https://forums.phpfreaks.com/topic/239761-using-data-from-one-table-to-get-data-from-second/#findComment-1233454 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.