query help


#1

I am trying to collect data from 3 tables. First I post restCity from search_city.php. That takes me to results_page.php, which has an include that calls for searchResults.php to create the table for the results. In searchResults.php it calls for searchValue.php which is a script that collects the posted field of searchValue and has an if loop statement which writes the results table based on what page the query came from, restName, restCity, or restCuisine.

What I am trying to do is take the restCity to take data from tblRestaurant which basically holds all the basic information. Then I have another include which queries the tblRestaurant to find the restID based on the city, and then use the restID to get the rest of the information from tblRestaurantFeatures. Does anyone know a good turorial that I can use to figure out this problem?

Brian


#2

I am unclear as to what you are trying to do… Are you trying to get data from 3 different tables (Quote: collect data from 3 tables) or the 2 different tables (tblRestaurant and tblRestaurantFeatures) you mention? You might want to check out SQL JOIN’s

I think you are asking the user to pick a city and then displaying the restaurants in that city. The user then picks a restaurant and then you display the specific data for that restaurant. Is that correct? You can do this on one or multiple pages - your choice. I would suggest you look at session variables ( http://www.php.net/manual/en/ref.session.php ) to “remember” things for you.

Other then that I don’t understand your question. I can’t recommend a tutorial since I am unsure of what you want to know.


#3

Lig,
Thanks for taking the time to look at my post, I figured out what I had to do. I didn’t use a join, but my code is this:

$sql=“SELECT *
FROM tblRestaurant r, tblRestaurantFeatures f, tblCuisine c, tblType t
WHERE r.restID = f.restID && restCity = ‘$_POST[restCity]’ && f.primaryCuisine = c.cuisineID && f.primaryType = t.typeID order by restName”;

It works.

Next problem, hehe.
I am trying to format currency. I am using a double data type. When I have 10.50 as a price, it returns as 10.5 What is the best way to format currency? What data type would work the best. I also tried using a format function, however it just added 2 zeroes, like 150(a dollar fifty) would come back as 150.00. Thanks for the help.


#4

Actually you did use a JOIN

FROM tblRestaurant r, tblRestaurantFeatures f, tblCuisine c, tblType t

ANSI SQL does not require you to use the MYSQL keyword.

for the money part - if you are using MySQL check out the Numeric Column Types. Pay particular attention to the Decimal and Numeric types.

for PHP have you looked at money_format()?

Reference:
http://dev.mysql.com/doc/mysql/en/Numeric_types.html
http://jp2.php.net/manual/en/function.money-format.php