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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

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

Post 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?
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

Did you try inner join?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

EDIT: Just saw the query, ill give her a try.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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
(#10850)
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

php4 - preg_replace() with i modifier
php5 - str_ireplace()
User avatar
waradmin
Forum Contributor
Posts: 240
Joined: Fri Nov 04, 2005 2:57 pm

Post by waradmin »

Thank you much!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post 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'];
Post Reply