DaniWeb Forum Index > Web Development > PHP

sql query string logical problem

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2008

Posts: 42

Reputation: marcmm is an unknown quantity at this point

Solved Threads: 0

marcmm

Offline

Light Poster

sql query string logical problem

Dec 7th, 2008

Ok, I'm having a big problem with an sql string for a query.

I want to implement a methode that allows the user to search for hotels by entering the minimal price ( so if they enter say 20 it will only display hotels that have prices above 20 ).

SO I setup a textbox to colect the value then I was to create the sql string to make the query. Well at that point everything whent wrong.

Well this is my sql string B4 implementing the minimal price search:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql_hstring="SELECT DISTINCT tables.names
		        FROM base table, satelite1, satelite2, satelite3
			WHERE ((basetable.city = satelite1.ID )
			AND (satelite1.name = satelite2.ID)
			AND (basetable.something=satelite3.ID))
                        ORDER BY localitate.den_localitate ASC";
$sql_hstring="SELECT DISTINCT tables.names
		        FROM base table, satelite1, satelite2, satelite3
			WHERE ((basetable.city = satelite1.ID )
			AND (satelite1.name = satelite2.ID)
			AND (basetable.something=satelite3.ID))
                        ORDER BY localitate.den_localitate ASC";

basicly it means I have a few many to 1 conections already in the condition area of the query. the query works perfectly at this point.

Well, the hotel prices are stored in yet another table ( because I have more then one price for a hotel ).

SO the new query would logically be like this:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql_string="SELECT DISTINCT tables.names
		        FROM base table, satelite1, satelite2, satelite3, prices
			WHERE ((basetable.city = satelite1.ID )
			AND (satelite1.name = satelite2.ID)
                        AND (basetable.hotel_id = prices.ID_hotel)
			AND (basetable.something=satelite3.ID))
                        ORDER BY localitate.den_localitate ASC";
$sql_string="SELECT DISTINCT tables.names
		        FROM base table, satelite1, satelite2, satelite3, prices
			WHERE ((basetable.city = satelite1.ID )
			AND (satelite1.name = satelite2.ID)
                        AND (basetable.hotel_id = prices.ID_hotel)
			AND (basetable.something=satelite3.ID))
                        ORDER BY localitate.den_localitate ASC";

The link is yet another many to 1 link... linking the unick ID hotel in the base table with a column that identifiew wich price belongs to wich hotel.

at the moment the prices table looks like this:

ID hotel_ID price
1 1 xx
2 1 xy
3 2 yy

meaning thet the first two prices belong to the hotel with the 1 identifyer in the base table

and the 3rd price belongs to the htoel with the 2 identifyer in the basetable.

but addint the extra many to 1 link in the condition area of the string ruins everything...

without specifying any conditions o nthe user's behalf ( meaning it should display ALL the hotels... and there are about 8 of them in the test DB at the moment ) it displays 3 results... namelly it displays the 1 identifyed hotel twice and the 2 identifyed hotel 1 time... and this is DESPITE there beying a DISTINCT select at the beginning of the querry.

i'm sure that this extra condition:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

AND (basetable.hotel_id = prices.ID_hotel)

ruins the logical flow of the string... but what can I do about it?
I can't imagine a way in wich to make a separate query to handle the price since the price HAS to be part of the main query... but adding it to the main query ruins the results... can anybody give me an idea about what I coudl do?

Last edited by marcmm; Dec 7th, 2008 at 11:29 am.

•

Join Date: Aug 2007

Posts: 189

Reputation: martin5211 is an unknown quantity at this point

Solved Threads: 14

martin5211 martin5211 is offline

Offline

Junior Poster

Re: sql query string logical problem

Dec 7th, 2008

Use a INNER JOIN statement in your SQL query, placing it before WHERE . You may be able to link a common ID field between the two tables and show a result into one set.

•

Join Date: Oct 2008

Posts: 42

Reputation: marcmm is an unknown quantity at this point

Solved Threads: 0

marcmm

Offline

Light Poster

Re: sql query string logical problem

Dec 8th, 2008

•

Originally Posted by martin5211

Use a INNER JOIN statement in your SQL query, placing it before WHERE . You may be able to link a common ID field between the two tables and show a result into one set.

well yes... but my problem is that it's displaying the same hotel twice ( and if the prices table will have for example 4 prices atributed to the same hotel it will display that hotel 4 times ) despite there beying a DISTINCT keyword set at the beginning of the query string.

at this point I just need to prevent any duplicate posts. To the best of my knowledge DISTINCT is supposed to handle that... but for some reason it backfires in this example and I cannot understand why.

I need it to display results only once.