Selecting from 2 MySQL database tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Selecting from 2 MySQL database tables

Post 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?
rvijay_eee
Forum Newbie
Posts: 4
Joined: Tue May 13, 2008 4:16 am

Re: Selecting from 2 MySQL database tables

Post 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());
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Selecting from 2 MySQL database tables

Post by Mr Tech »

LEGEND! Thanks mate ;)
Post Reply