Selecting from multiple tables - Returning "MySQL client ran out of memory"

[Valace]

I'm trying to code a search feature for a client on a website that was built years ago, so the way it was coded is a little outdated, but still works just fine.

Basically I'm trying to get the search results to paginate. I tried with multiple queries but I couldn't get them to paginate properly, so I figured I'd try searching all necessary tables with a single query, which is returning the error "MySQL client ran out of memory".

I'm not using any joins, which may be the issue.
I've tried looking up how to incorporate joins, but the articles that I've read seem to be geared toward looking for matching data between tables, whereas I'm trying to search all tables for keywords submitted by the user's search.

Can someone tell me if I'm using this correctly? And maybe give me some advice?

	$sql	=	"SELECT
				subsections.id,
				subsections.name,
				subsections.description,
				products.id,
				products.code,
				products.name,
				products.description,
				pages.id,
				pages.title,
				pages.content,
				blog_entries.id,
				blog_entries.name,
				blog_entries.content
			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)."%'";

Any help would be greatly appreciated.

[Barand]

You ARE using joins.

 

If you SELECT ... FROM A,B,C without any join conditions you create a cartesian join which joins every record in each table with every record in every other table. So if A, B and C each contain 1000 rows then you return 1000 x 1000 x 1000 rows ie 1 billion. So now you know where the memory is going

[mac_gyver]

since this data in the different tables is not related to each other, how do want the output to appear, especially if you are paginating it?

 

in any case, to do this in one query, you would use a UNION query, but you would have to select the same number and meaning columns (even if you have to select a static/dummy text string), so that similar meaning data from the different tables would fall into the same column in the result set (i.e. where you are selecting products.code in the second table, you would need to select a dummy value in the other UNION queries, in that same position in the select list, so that the name and description/content column data from all the tables would align in the correct columns in the result set.)

 

edit: you would also want to apply your database's string escape function only once to the $keywords value, then use that escaped value in the queries (or use prepared queries.)