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

Not sure how your tables are structured but the query would be something like "select table2.image from table1, table2 where table1.id='whatever-the-id-number-is' and table1.id=table2.id" J

Ahem, got dollar-happy. Of course, the return false should be without the $. Still interested in the necessity of the autocommit declaration though. J

Does it help if you replace the while clause with foreach($row as $value) { // Stuff in loop } You would then need to change values such as $row['id'] to $value['id']... Worth a try?

It seems like your original method and the suggested other approaches are all as valid as each other, and more dependent on how you feel most comfortable querying your data once it's being populated. The first response in this thread is how I would approach it. You have a contact table, and that should contain all the unique info for which there will be only one value. Then, having a relational table that houses all other contact types means that, as long as you can define the types, you can query the data easily enough. When displaying a contact, you would query the contact info table, adding a clause that would filter by type as well as by contact id. Convert the results to an array and you have your list of, e.g. email address belonging to that contact. Seems like a clean enough approach for such an application... J

Hi all I am running a query that loops through an array of id numbers for a given table. Basically the user would have selected one or several rows in a table that I have outputted, and the script goes through each id number and deletes that entry from the corresponding SQL table. In order to do this, I am turning OFF autocommit so I get all or nothing -- either all the selected records are deleted, or nothing is changed in the event of an error. Within my foreach loop that references the array of id numbers, I have the following: foreach($deletions as $action){ $query = "delete from my_table where id='".$action."'"; $result = $conn->query($query); if(!$result){ $return false; } } Following this loop, I commit the changes, and switch autocommit back to TRUE before returning true. My long-winded question is, do I need to add an "$conn->autocommit(TRUE);" line before the "return false" line? Will the autocommit remain OFF if I do not do this and an error quits the function? Or, to put it another way, am I in any way interrupting the autocommit setting by adding this? It seems like it ought to be harmless, but I wanted to check! Thanks J

Can you post the values of the setup of the relevant field? For example, is there a limit value imposed on the SQL field? This might happen with a varchar type field.

Interesting idea. However, am I right in thinking that https pages are not browser cached? Are there reasons such as this (i.e. production of more hefty bandwidth usage) for changing the setup of the site rather than applying SSL site-wide? Just trying to weigh up the options... Thanks! J

Try adding an overflow: hidden value to the same tag that has the block display?

Hmm, I was thinking the same. This presents a problem, in that people coming to the site for the first time are 99% likely not to go via https. I guess I could redirect. Thank you for the input! J

Hi there I have implemented several apps in the past where secure info is submitted over an SSL connection. However, I am working on a project where there is an included sidebar form, so it appears on EVERY page. My question is: does this mean EVERY page needs to be https, or am I good as long as my form's action leads to https? I hope this makes sense... Thanks in advance John