TEMPORARY table problem

[selftaught]

I am trying to create a temp table, populate with results of first query, then query temp table to find my final results.... my code is flawed 😕

Heres my last revision:

$query2 = "CREATE TEMPORARY TABLE city_room SELECT * FROM table_1 " . $search_where;
$result2 = mysql_db_query($datab, $query2);
$query3 = "SELECT * FROM city_room " . $amenities_where;
$result3 = mysql_db_query($datab, $query3);

The above will create this:

query2 = CREATE TEMPORARY TABLE city_room SELECT * FROM table_1 WHERE city = 'city_1' OR 'city_2' AND rooms = 'studio' OR '1' AND listing_status = '1'
query3 = SELECT * FROM city_room WHERE amenities_1 = 'yes' AND amenities_2 = 'yes' AND price BETWEEN '0' AND '1000' 

TIA!

[pete_bisby]

$query2 is slightly wrong. It's getting confused with the OR statements. Have a look at the following:

CREATE TEMPORARY TABLE city_room SELECT * FROM table_1 WHERE (city='city_1' OR city='city_2') AND (rooms='studio' OR rooms='1') AND listing_status = '1';

You have to repeat the attribute name for each value you want to search on, and you have to use parentheses to separate youre AND and OR statements. Alternatively, use the following query, which should produce the same results:

CREATE TEMPORARY TABLE city_room
SELECT * FROM table_1 WHERE city IN ('city_1', 'city_2') AND rooms IN ('studio', '1') AND listing_status = '1';

The IN statement replaces the OR statements, runs quicker and makes the SQL query easier to read and more compact.

[selftaught]

Thanks!!!!
That did the trick and yes I like the "IN" statement it does look good.
😃