Long SQL query in PHP querying multiple tables

[system]

I’m sorry if this is the wrong place to ask this, seeing as I hardly ever use pure SQL and I usually do everything through PHP, I thought I’d ask here - but if it’s the wrong place, I apologize.

I’m trying to code a search feature into a website. The search feature needs to search multiple tables for the keywords used and then paginate the results.
I tried this with separate queries at first, which worked but was impossible to paginate properly, so I’m now trying to do it all in the one query - Problem is the query is taking a while and returning “MySQL client ran out of memory”.
I’m not using joins (or at least, not properly if I am) and don’t really get how they wok as everything I’ve ready up about them thus far seems to try to match identical data between tables/columns, whereas I just need to see if a keyword exists in each of the tables/columns.

The code I’m using for the query is:
[embed=425,349]
$sql = “SELECT * FROM
subsections,
products,
pages,
blog_entries
WHERE
subsections.name LIKE '%”.mysql_real_escape_string($keywords)."%’ OR
subsections.description LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
products.code LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
products.name LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
products.description LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
pages.title LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
pages.content LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
blog_entries.name LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
blog_entries.content LIKE ‘%".mysql_real_escape_string($keywords)."%’";
[/embed]

Would someone mind helping me do this properly?

Cheers,

• V.

[Topcoder]

It sounds like the first way worked with separate queries for you, I suggest going back to that way and then use the UNION statement to combine them. You’re just going to have to play with the select clause to get them to match up right, here’s an example

I don’t know if your subsections table has a column named “code in it” so I’m just going to add one with a blank character in the select, so it matches your products table. Then union the two tables together. You can union as many queries as you want together and you can make up or rename column names to match.

[php] $sql =“SELECT ‘’ as code, name, description FROM subsections where subsections.name LIKE '%”.mysql_real_escape_string($keywords)."%’ OR subsections.description LIKE ‘%".mysql_real_escape_string($keywords)."%’
UNION
SELECT code, name, description FROM products.code LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR products.name LIKE ‘%".mysql_real_escape_string($keywords)."%’ OR
products.description LIKE ‘%".mysql_real_escape_string($keywords)."%’ ";[/php]

[benanamen]

You need to learn about indexes. Here is a good start: https://blog.marceloaltmann.com/en-using-the-mysql-fulltext-index-search-pt-utilizando-mysql-fulltext/