Help optimizing a looping SQL query?

[SpankMarvin]

Hi there

 

I have fairly rudimentary knowledge of SQL syntax. I am using a PHP script to query a database of company sponsors, but am pretty sure I am not using an efficient query to get list results.

 

I have several tables relating to categories, sponsors, geographical zones. Each of these has linkage tables to sponsors, and each of them has an activation status (i.e. sponsors and zones could be deemed 'inactive' and therefore should not be displayed)

 

There is a 'complete listing' section, in which I need to assess the following criteria before spitting out some results:

 

- There is at least one sponsor with an activation status of 'active' that falls into the category

- The sponsor belongs to at least one geographical zone that is 'active'

 

I currently have a script that loops through each main category. From that main category, there is an array produced of categories that fall under that main category. For these, I have attempted the following approach, passing the array of sub-categories into the function. (db_connect is simply a function that takes care of the db connection.)

 

I do not need a list of all sponsors, just the ability to work out whether there is at least one qualifying sponsor falling into each category, and if so, list that category. I would really appreciate any input  into how this might be improved to be more efficient.

 

function display_valid_sponsors($cat_array){
if(!is_array($cat_array)){
echo("<div class=\"subcats\"><ul><li>No categories available</li></ul></div>");
return;
}
echo("<div class=\"subcats\"><ul>");


$conn = db_connect();


$result_track = 0;
foreach($cat_array as $cat){
$catid = $cat['catid'];
$query = "select linksponsorcats.catid, catname from categories, linksponsorcats, sponsors, zones, linksponsorzones where linksponsorcats.catid = '".$catid."' and linksponsorcats.catid=categories.catid and linksponsorcats.sponsorcode=sponsors.sponsorcode and sponsors.sponsoractivestatus='active' and sponsors.sponsorcode=linksponsorzones.sponsorcode and zones.zoneactivestatus='active' limit 0,1";
$result = $conn->query($query);
$counter = $result->num_rows;
$result = $result->fetch_assoc();

// Get number here!
if($counter > 0){
$result_track = 1;
	/*$newq = "select catname from categories where catid='".$catid."'";
	$result = $conn->query($newq);
	$result = $result->fetch_assoc();*/
	echo("<li><a href=\"list-sponsors.php?catid=".$catid."\">".$result['catname']."</a>");
			if (isset($_SESSION['valid_admin'])) { // User is admin; add editing option for the category
				echo(" | <a href=\"".$URL_ROOT."/admin/edit-category.php?catid=".$catid."\">Edit this category</a>");
			}
			echo("</li>");

}
}
if($result_track != 1){
	echo("<li>No categories available</li>");
}	
echo("</ul></div>");
$conn->close();
}

 

 

[kickstart]

Hi

 

Doing it with a single piece of SQL should be significantly more efficient.

 

Something like this (not tested):-

 

<?php
function display_valid_sponsors($cat_array)
{
if(!is_array($cat_array))
{
	echo("<div class=\"subcats\"><ul><li>No categories available</li></ul></div>");
	return;
}
echo("<div class=\"subcats\"><ul>");


$conn = db_connect();


$result_track = false;
$catid_array = array();
foreach($cat_array as $cat)
{
	$catid_array[] = $cat['catid'];
}

$query = "SELECT DISTINCT linksponsorcats.catid, catname 
FROM categories
INNER JOIN linksponsorcats ON linksponsorcats.catid=categories.catid
INNER JOIN sponsors ON and linksponsorcats.sponsorcode=sponsors.sponsorcode and sponsors.sponsoractivestatus='active' 
INNER JOIN linksponsorzones ON sponsors.sponsorcode=linksponsorzones.sponsorcode 
INNER JOIN zones ON linksponsorzones.zonecode = zones.zonecode AND zones.zoneactivestatus='active'  
WHERE linksponsorcats.catid IN ('".implode("','",$catid_array)."') ";

$rs1 = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($rs1))
{
	$result_track = true;
	echo("<li><a href=\"list-sponsors.php?catid=".$row['catid']."\">".$row['catname']."</a>");
	if (isset($_SESSION['valid_admin'])) 
	{ // User is admin; add editing option for the category
		echo(" | <a href=\"".$URL_ROOT."/admin/edit-category.php?catid=".$row['catid']."\">Edit this category</a>");
	}
	echo("</li>");
}
if($result_track != 1)
{
	echo("<li>No categories available</li>");
}	
echo("</ul></div>");
$conn->close();
}
?>

 

All the best

 

Keith

[SpankMarvin]

Hi

 

Doing it with a single piece of SQL should be significantly more efficient.

 

 

Hi Keith.

 

Thank you, thank you, thank you. There were a couple of things I needed to change (the result of my original post not being 100% clear) but this works incredibly well. Noticeably faster results, absolutely wonderful.

 

For the potential aid of others who might be interested, once I have tidied up my code a little, I will report back and post it for info. I also have to work on a similar query with other criteria and will post this up too!

 

Thanks once again. Excellent advice, and it gives me some useful places to continue my SQL learning.

 

John

[SpankMarvin]

Hmm, I'm having an issue trying to expand the query. I am adding a zipcode to the query. Having added the array of zips and added zipcodes to the FROM clause, my query produces, e.g., the following:

 

SELECT DISTINCT linksponsorcats.catid, catname FROM zipcodes, categories 
INNER JOIN linksponsorcats ON linksponsorcats.catid = categories.catid 
INNER JOIN sponsors ON linksponsorcats.sponsorcode = sponsors.sponsorcode 
AND sponsors.sponsoractivestatus = 'active' 
INNER JOIN linksponsorzones ON sponsors.sponsorcode = linksponsorzones.sponsorcode 
INNER JOIN zones ON linksponsorzones.zoneid = zones.zoneid 
AND zones.zoneactivestatus = 'active' 
WHERE linksponsorcats.catid IN ('197','15','20','239','17','16','19','18') 
AND zipcodes.zip IN ('85016','85018','85028','85032','85044','85045','85048','85050','85054','85086','85118','85119','85120','85205','85206','85207','85208','85209','85212','85213','85215','85218','85219','85220','85224','85225','85226','85233','85234','85248','85249','85250','85251','85253','85254','85255','85256','85257','85258','85259','85260','85262','85268','85283','85284','85286','85295','85296','85297','85298','85340','85377','85387','85395') order by catname

 

As before, this is now very slow. Is there something caused by my addition of zipcodes to the FROM clause that is wasteful here?

 

Any advice, again, greatly appreciated.

[kickstart]

Hi

 

You do not appear to have specified a column to JOIN the zip table to the others on, hence it is probably joining every row on the zip table with every row the rest of the query returns.

 

What column on the other tables matches the zip code? How do you want to deal with cases where the zip code doesn't match (ie, I take it you are only interested in sponsors who are the specified zip codes).

 

Guessing at it:-

 

SELECT DISTINCT linksponsorcats.catid, catname 
FROM categories 
INNER JOIN linksponsorcats ON linksponsorcats.catid = categories.catid 
INNER JOIN sponsors ON linksponsorcats.sponsorcode = sponsors.sponsorcode 
AND sponsors.sponsoractivestatus = 'active' 
INNER JOIN zipcodes ON sponsors.zipcodeid = zipcodes.zipcodeid
AND zipcodes.zip IN ('85016','85018','85028','85032','85044','85045','85048','85050','85054','85086','85118','85119','85120','85205','85206','85207','85208','85209','85212','85213','85215','85218','85219','85220','85224','85225','85226','85233','85234','85248','85249','85250','85251','85253','85254','85255','85256','85257','85258','85259','85260','85262','85268','85283','85284','85286','85295','85296','85297','85298','85340','85377','85387','85395')
INNER JOIN linksponsorzones ON sponsors.sponsorcode = linksponsorzones.sponsorcode 
INNER JOIN zones ON linksponsorzones.zoneid = zones.zoneid 
AND zones.zoneactivestatus = 'active' 
WHERE linksponsorcats.catid IN ('197','15','20','239','17','16','19','18') 
order by catname

 

All the best

 

Keith

[SpankMarvin]

I have a linksponsorzones table and a zipcodes table. The zipcodes table contains the zip code, and the zone number (zoneid) into which that zip might fall. The linksponsorzones table contains the sponsorcode and the zoneid.

 

So, I guess the join up could be where the zoneids are the same, but perhaps also where the sponsors.sponsorcode matches linksponsorzones.sponsorcode? I'm finding this kind of headache inducing!

 

Thank you!

 

J

[SpankMarvin]

Could this... possibly... be correct???!

 

SELECT DISTINCT linksponsorcats.catid, catname FROM categories 
INNER JOIN linksponsorcats ON linksponsorcats.catid = categories.catid 
INNER JOIN sponsors ON linksponsorcats.sponsorcode = sponsors.sponsorcode 
AND sponsors.sponsoractivestatus = 'active' 
INNER JOIN linksponsorzones ON sponsors.sponsorcode = linksponsorzones.sponsorcode 
AND sponsors.sponsorcode = linksponsorzones.sponsorcode 
INNER JOIN zipcodes on linksponsorzones.zoneid=zipcodes.zoneid 
AND zipcodes.zip in ('85016', '85018', '85028', '85032', '85044', '85045', '85048', '85050', '85054', '85086', '85118', '85119', '85120', '85205', '85206', '85207', '85208', '85209', '85212', '85213', '85215', '85218', '85219', '85220', '85224', '85225', '85226', '85233', '85234', '85248', '85249', '85250', '85251', '85253', '85254', '85255', '85256', '85257', '85258', '85259', '85260', '85262', '85268', '85283', '85284', '85286', '85295', '85296', '85297', '85298', '85340', '85377', '85387', '85395') 
INNER JOIN zones ON linksponsorzones.zoneid = zones.zoneid 
AND zones.zoneactivestatus = 'active' 
WHERE linksponsorcats.catid IN ('238','40') order by catname

 

I feel like I'm really close with this now...

[SpankMarvin]

Oh BTW ignore that the catids and zipcodes might be different...

[kickstart]

Hi

 

For my understanding of what you want that does look right. Can't see any errors but can't really test it without setting up a load of tables and data though.

 

All the best

 

Keith