MYSQL PHP Select question

[boko605]

Hi, i have some troubles with a script.

I have 2 DB.

DB1 - in witch i collect information about propeties(real estates).

DB2 - in witch i have the names of the cities in cyrilic and in latin.

My web site have two version. Bulgarian and English.

When someone make a search by city, each city has a number that is the same as the ID in DB2.

 

$query = "select d1.* , db2.citynameinenglish // must select all the info from DB1 and the name of the city in english

              from DB1 d1, DB2 d2

where d1.salerent='$salerent'"; // sale or for rent

if($type!="")  $query=$query." and d1.type='$type'"; // if the type of the property is selected

if($city!="")   $query=$query." and d2.city='$city'"; // if a city is selected

if($price!="") $query=$query."  and d1.price<='$price"; // if a price is inputed

 

$result = mysql_query($query);

$num_results = mysql_num_rows($result);

 

Ok, in my opinion this must work but it doesnt. I have 10 entries in DB1 and the result of this query is more than 400 entries?? ???

Any suggestions?

[marmite]

I'm not certain what you're trying to select - a list of city names?

 

I would guess that the problem is the d1.*

 

Perhaps it's multiplying all d1 rows by number of db2.citynameinenglish

 

Does that sound possible?

 

Also, you refer to one db as d1 (as you've named it) and the other as db2. (as though you haven't named it). Maybe try d2. instead?

 

[bubblegum.anarchy]

Assuming that DB1 and DB2 are not actually databases and refer to tables, I do not see any SQL that joins DB1 and DB2, something like:

 

SELECT d1.*, d2.citynameinenglish
FROM DB1 d1, DB2 d2
WHERE d1.cityid = d2.cityid

OR

SELECT d1.*, d2.citynameinenglish
FROM DB1 d1
     INNER JOIN DB2 d2 ON d1.cityid = d2.cityid

 

FYI: db2 should be d2 in the SELECT clause of your query, boko605.