Using WHERE in a multiple query

Question

This is based on a search engine

I am receiving duplicate search results from this...

$construct ="SELECT * FROM Rating, Attraction WHERE $construct" ;

Then I was informed that it was selecting all results and rows and therefore repeating it so i used this query from another page I have and added the relevant WHERE on the bottom.

$construct  = "SELECT Attraction.*, Type.TypeName, Rating.RatingUrl ";
$construct .= "FROM Attraction ";
$construct .= "INNER JOIN Type ON Attraction.Type = Type.TypeID ";
$construct .= "INNER JOIN Rating ON Attraction.AttractionID = Rating.AttractionID ";
$construct .= "WHERE Attraction = '$construct' ";

Have do i structure this correctly, it is not working and recognising the query.

$construct is a query for matching keywords with the database

Print out the final string of the query, it's much easier to see errors, and tell us which RDBMS you are using, MySQL? — MrCode, Dec 07 '12 at 13:13
In `WHERE Attraction = '$Construct' ` , Attraction is a database field or is it the table name? — Boomer, Dec 07 '12 at 13:17
When debugging a SQL issue, the most important bits of info are: 1) DBMS 2) Actual SQL code. You've omitted both. — Álvaro González, Dec 07 '12 at 13:22

score 1 · Answer 1 · answered Dec 07 '12 at 13:16

The var you store your query in is called $construct and you are also including it on the last line "WHERE Attraction = '$construct' " so you are actually including the first 4 lines inside the last line, which wont give desired result.

Perhaps renaming the query variable to make it work is enough.

Also if you dont want duplicates, just use DISTINCT

SELECT DISTINCT * FROM Rating, Attraction WHERE $construct

score 1 · Answer 2 · answered Dec 07 '12 at 13:16

1

I believe it is because of this

 $construct .= "WHERE Attraction = '$construct' ";

U are using the same variable name ?

answered Dec 07 '12 at 13:16

v0d1ch

2,738
1
22
27

score 0 · Answer 3 · answered Dec 07 '12 at 13:19

0

i am not sure but please user group by TypeID may be its work

answered Dec 07 '12 at 13:19

vaibhav

129
4

Using WHERE in a multiple query

3 Answers3