Page 1 of 1

Selecting from 2 MySQL database tables

Posted: Wed May 14, 2008 9:15 pm
by Mr Tech
I have listings in my mysql database in their own table. I also have custom fields in another table which hook up to the listings.

Basically I want to enable people to search both the listing table and the custom fields table.

Here is my code:

Code: Select all

$listings_query = mysql_query("SELECT * FROM listings_items AS l JOIN listings_customfields AS c ON l.id = c.listingid WHERE 
    l.title rlike '".make_safe($search_keywords)."' or 
    l.code rlike '".make_safe($search_keywords)."' or 
    l.summary rlike '".make_safe($search_keywords)."' or 
    l.description rlike '".make_safe($search_keywords)."' or
    c.value rlike '".make_safe($search_keywords)."'
    GROUP BY l.id") or die(mysql_error());
This works fine however if there are no rows in the listings_customfields table for the listing, it is ignored in the search even if the keyword exists in the listings_items table.

Any idea on how I can make it still show results if nothing exists in the listings_customfields table?

Re: Selecting from 2 MySQL database tables

Posted: Fri May 16, 2008 12:25 am
by rvijay_eee
In your query, instead of join use left join, as the query listed below

Code: Select all

$listings_query = mysql_query("SELECT * FROM listings_items AS l [b]LEFT JOIN[/b] listings_customfields AS c ON l.id = c.listingid WHERE 
    l.title rlike '".make_safe($search_keywords)."' or 
    l.code rlike '".make_safe($search_keywords)."' or 
    l.summary rlike '".make_safe($search_keywords)."' or 
    l.description rlike '".make_safe($search_keywords)."' or
    c.value rlike '".make_safe($search_keywords)."'
    GROUP BY l.id") or die(mysql_error());

Re: Selecting from 2 MySQL database tables

Posted: Fri May 16, 2008 2:25 am
by Mr Tech
LEGEND! Thanks mate ;)