Page 1 of 1

955 Queries for a search? Wow, can I get some query help?

Posted: Wed Dec 05, 2007 1:47 pm
by waradmin
I am working on a search that goes through my database and grabs the results (duh). However, the way I have it setup right now, it is taking 955 queries on the DB to generate the results for a single search! And that is just using 2 tables, wow.

Here is the code:

Code: Select all

function searchLinks($terms)
{
	$result = mysql_query("SELECT codeid, link FROM links WHERE link LIKE '%$terms%' ORDER BY noinset Desc")
		or die(mysql_error());
	$_SESSION['totalqueries']++;
	$num_rows = mysql_num_rows($result);
	$_SESSION['results'] += $num_rows;
	for($i = 0; $i < $num_rows; $i++)
	{
		$row = mysql_fetch_array($result);
		$id = $row['codeid'];
		$result2 = mysql_query("SELECT title, getcode FROM codes WHERE id='$id'")
			or die(mysql_error());
		$row2 = mysql_fetch_array($result2);
		$_SESSION['totalqueries']++;
		$link = str_replace($terms, "<b>" . $terms . "</b>", $row['link']);
		echo "<tr class=\"searchresultsresults\"><td colspan=\"3\" width=\"50%\"><a href=\"index.php?page=get&code=" . $row2['getcode'] . "\">$link</a></td></tr>";
	}
	if($num_rows == "0")
	{
		echo "<tr><td colspan=\"3\"><div align=\"center\">No results found</div></td></tr>";
	}
} // end searchLinks
So, as you can see (perhaps) the 'links' table contains links to files as well as what ID in the codes table the links belong to, and the 'codes' table contains the title for the link compilations. What I am trying to do is find links (in the links table) that match the search term, then grab the title for that link from the codes table. Is there a better way to do it than how I am doing it, because its insanely bloated right now (1 query per found result).

Example:
user searches for bob, the links table is searched for "bob" (+1 query), if a result is found the codeid of that link is matched up with the id of the title in the codes table (+1 query).

However there may be 50 to 100+ links associated with a single codeid that links to a id in the codes table, so for each found link it is also re-querying the codes table to get the same information.

Any way to slim down the queries so it doesn't take 900 queries to display 900 search results?

Posted: Wed Dec 05, 2007 1:59 pm
by louie35
Did you try inner join?

Posted: Wed Dec 05, 2007 2:01 pm
by John Cartwright

Code: Select all

SELECT codeid, link FROM links 
INNER JOIN codes ON links.codeid = codes.id
WHERE link LIKE '%$terms%' ORDER BY noinset Desc
Yes, you can do it with one query.

Posted: Wed Dec 05, 2007 2:01 pm
by waradmin
EDIT: Just saw the query, ill give her a try.

Posted: Wed Dec 05, 2007 2:02 pm
by Christopher
Use a JOIN and you can do it all in one query:

Code: Select all

SELECT links.link, codes.title, codes.getcode FROM codes LEFT JOIN links ON codes.id=links.codeid WHERE links.link LIKE '%$terms%' ORDER BY links.noinset Desc

Posted: Wed Dec 05, 2007 2:09 pm
by waradmin
I used Jcart's query, worked like a charm. Cut it down from 950+ queries to 1. Thanks for the help everyone!!

Second thing if possible:

Code: Select all

$link = str_replace($terms, "<b>" . $terms . "</b>", $row['link']);
I am using that to bold the search term, however the mysql LIKE ignore capitals which is nice, however is a user searches Bob the query will find it but will not make it bold if the link is "bob". Is there a way to do str_replace while ignoring the casing of the search term and $row['link'].

Example:
User searches BOB, mysql LIKE finds bob, but still have str_replace make it bold.

I thought about converting it to lower and upper case, but if the user searches bOb and the link is BoB doing the upper and lower conversion then str_replace will still not work.

Posted: Wed Dec 05, 2007 2:12 pm
by John Cartwright
php4 - preg_replace() with i modifier
php5 - str_ireplace()

Posted: Wed Dec 05, 2007 2:18 pm
by waradmin
Thank you much!

Posted: Thu Dec 06, 2007 4:53 am
by Mordred
I can't help but notice the lack of escaping of $terms, and - unless it is numeric, but even then - $id = $row['codeid'];