SpankMarvin

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

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...

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

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.

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

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(); }

As the above post says, $_REQUEST is what you need to print to obtain variables passed through POST, GET and COOKIE methods. It's a superglobal and the print function will give you what you're looking for, unless what you have described is in error. However, the session id output IS what you're after, as I believe that refers to cookie information. Which is what you wanted, right?

Hi all I've made the fairly obvious step of taking the AND clauses outside of the OR clauses, and putting them only once at the end of the query, which obviously cuts down on the amount of querying significantly. New code: 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(); $query = "select categories.catid, catname from categories, linksponsorcats, sponsors, zones, linksponsorzones where"; $count = 1; foreach($cat_array as $key){ $catid = $key['catid']; $catname = $key['catname']; if($count == 1){ $query .= "(linksponsorcats.catid='".$catid."'"; } else { $query .= " or linksponsorcats.catid='".$catid."'"; } $count ++; } $query .= ") and linksponsorcats.catid=categories.catid and linksponsorcats.sponsorcode=sponsors.sponsorcode and sponsors.sponsoractivestatus='active' and sponsors.sponsorcode=linksponsorzones.sponsorcode and zones.zoneactivestatus='active' group by categories.catid order by categories.catname"; $result = $conn->query($query); if ($result->num_rows > 0){ $result = db_result_to_array($result); foreach($result as $key){ echo("<li><a href=\"list-sponsors.php?zip=".$zip."&catid=".$key['catid']."\">".$key['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=".$key['catid']."\">Edit this category</a>"); } } echo("</li>"); } else { echo("<li>No categories available</li>"); } echo("</ul></div>"); } I would still be interested if anyone could weigh in on any ways I could optimize further, though. Thanks J

Hi there! I have a sponsor listing in which I have to run a query to produce a list of categroies into which sponsors might fall. This is outlined as follows: - User selects to browse all sponsors by category - Categories fall under main categories, sponsor can be linked to one or more (or none) of the categories but not to a main category - Sponsors can have activation status for non-admin views (they can be active and visible to the public in results, or non-active, and invisible) - Geographical zones are also specified, which, like sponsors, can be active or non-active The results themselves (e.g. sponsor names) are not important for this particular listing. All that's important is whether or not the category should be listed at all, which it shouldn't be if there are no relevant sponsors to display when clicking through. I have a query, as shown below, within a function that references a category array for each main category (so this function is triggered for each main cat). For each query, I have to ensure that the category is only displayed if AT LEAST ONE sponsor is tied in to that category, AND that sponsor is active, AND that sponsor belongs to a geographical zone that is also active. Before, I had a query within a foreach for every category, which seemed wasteful. However, I was able to limit each of those queries to one row returned. It was, however, slow. In this modified version I am making a query for each main category, but each query is longer as a result. The result is still, at times, very slow to come through. Here is my function: 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(); $query = "select categories.catid, catname from categories, linksponsorcats, sponsors, zones, linksponsorzones where"; $count = 1; foreach($cat_array as $key){ $catid = $key['catid']; $catname = $key['catname']; if($count == 1){ $query .= " (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')"; } else { $query .= " or (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')"; } $count ++; } $query .= " group by categories.catid order by categories.catname"; $result = $conn->query($query); if ($result->num_rows > 0){ $result = db_result_to_array($result); foreach($result as $key){ echo("<li><a href=\"list-sponsors.php?zip=".$zip."&catid=".$key['catid']."\">".$key['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=".$key['catid']."\">Edit this category</a>"); } } echo("</li>"); } else { echo("<li>No categories available</li>"); } echo("</ul></div>"); } I am convinced that I could be rewriting this query more efficiently, but my limits of SQL are hitting me hard. Can anyone give me some tips on how to shape it up? I'd really appreciate any advice. Thank you John

Is the dollar sign before your adminmembers table a typo?

I think you are producing a multidimensional array here. Try the following. I'm assuming you want a list of the "contents" rows from your results... // Grabs the current downloads $NameDownload = "Downloads"; $DownloadQuery = mysql_query("SELECT * FROM site WHERE name = '".$NameDownload."'"); $DownloadFetch = mysql_fetch_array($DownloadQuery); echo("<ul>"); foreach($DownloadFetch as $row){ $Downloads = $row['Contents']; echo("<li>".$Downloads."</li>"); } echo("</ul>"); Any luck?

Do you have a reference to a database connection in that script?

Whack it in an if clause: if(get_magic_quotes_gpc()){ // Automatic adding of slashes? If so, strip em first $string = stripslashes($string); } You can turn that into a function too, to save yourself time...

Am I correct in assuming that $cat is defined by some sort of $_GET['cat'] input? If so, try deleting the if(empty... line completely, and placing the if clauses into a function as follows: function get_forums($cat = "") { if (!$cat) { $query = " select * from mgb_cat "; $result = mysql_query($query); $row = mysql_fetch_array($result); echo "<card title=\"Categories\"><p align=\"center\"> <img src=\"../logos/forum.gif\" alt=\"forums\" /><br/><b>Categories</b><br/><br/>"; while($row) { echo "<a href=\"forums.php?ses=$ses&cat=".$row["id"]."\" title=\"".$row["name"]."\">".$row["name"]."</a><br/>"; $row = mysql_fetch_array($result); } } // Shows the forums if ($cat) { $query = " select * from mgb_forums where cat='$cat'"; $result = mysql_query($query); $row = mysql_fetch_array($result); echo "<card title=\"Forums\"><p align=\"center\"> <img src=\"../logos/forum.gif\" alt=\"forums\" /><br/><b>Forums</b><br/><br/>"; while($row) { echo "<a href=\"topics.php?ses=$ses&forum=".$row["id"]."\" title=\"".$row["name"]."\">".$row["name"]."</a><br/>"; $row = mysql_fetch_array($result); } } } then in your main code, pass $cat through the function: get_forums($cat); This way, if there is truly no $cat value, the function should run as expected.

Thank you! Yes, I thought this would not be a bad thing to do, but wanted to check! Thanks for your response. J