Fluoresce Posted February 18, 2009 Share Posted February 18, 2009 I have a page on my site which presents an A-to-Z list of automotive makes. The page looks like this: Acura Alfa Romeo Aston Martin Audi Bentley Bitter BMW Etc. The following code is used to select the makes from the database: $query = "SELECT make, url FROM tmake WHERE make LIKE 'A%'"; $result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR); if(mysql_num_rows($result) >= 1) { while($row = mysql_fetch_assoc($result)) { echo "<a href=\"" . $row['url'] . "\">" . $row['make'] . "</a><br />"; } // end while } // end if else { echo "None"; } All I modify is the end of the top line to select makes that begin with a different letter. For example, if I change 'A%' to 'B%', the code will select all makes beginning with the letter B instead of A. Question 1) Is it OK to use the same code 26 times on the same page, once for each letter? Another, related, question . . . I want the page to also present how many models there are for each make in the database, like this: Acura (78) Alfa Romeo (20) Aston Martin (10) Audi (77) Bentley (7) Bitter (4) BMW (99) The problem is, the models are in another table of the database. Question 2) If I add a SELECT COUNT(*) command to every letter, there will be 52 commands on one page! Will that be OK? Quote Link to comment Share on other sites More sharing options...
trq Posted February 18, 2009 Share Posted February 18, 2009 1: There is absolutely no need to do that. Just get all the results ordered alphabetically and display them that way. 2: Why would you need that? Your already selecting all the records, simply use mysql_num_rows to count them. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 18, 2009 Author Share Posted February 18, 2009 Thanks for the response, Thorpe! "1: There is absolutely no need to do that. Just get all the results ordered alphabetically and display them that way." I was trying to keep the As away from the Bs, etc. They must be separate, because, at the top of the page, I have an alphabet. When visitors hit A, they are dropped down to the As, which sit under a huge A headline. It looks like this: A | B | C | D A Acura Audi B Bentley BMW Can I still do it your way? "2: Why would you need that? Your already selecting all the records, simply use mysql_num_rows() to count them." The first one is a SELECT command; the second one is a COUNT command. The COUNT has to count rows which occur in a completely different table - hence my confusion. How might I be able to do this? A little push in the right direction would be appreciated. Quote Link to comment Share on other sites More sharing options...
killah Posted February 18, 2009 Share Posted February 18, 2009 $a_o_l = array ( 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', ); if( empty($a_o_l) ) { echo 'None.'; } else { for($i = 0; $i < count($a_o_l); $i++) { $query = mysql_query("SELECT make, url FROM tmake WHERE make LIKE '".$a_o_l[$i]."%'"); if( mysql_num_rows($query) >= 1 ) { while($soc = mysql_fetch_assoc($query)) { $numb = mysql_num_rows(mysql_query("SELECT ID FROM table WHERE make = '".$soc['make']."'"); echo '<a href="'.$soc['url'].'">'.$soc['make'].'</a> ('.$numb.')<br />'; } } } } UNTested! Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 18, 2009 Author Share Posted February 18, 2009 Hi, Killah. I appreciate the effort, man. Thank you very much. But I'm a newbie, dude. I don't understand any of that code. I tried it but it didn't work - even after I changed everything to suit my database. In one table I have the car makes; in another I have the models. I need the makes to be selected from their table, and the models to be counted in their table. Is that possible? Quote Link to comment Share on other sites More sharing options...
premiso Posted February 18, 2009 Share Posted February 18, 2009 <?php $query = "SELECT make, url FROM tmake ORDER BY make"; $result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR); if(mysql_num_rows($result) > 0) { $last_start = "A"; while($row = mysql_fetch_assoc($result)) { $start = substr($row['make'], 0); if ($start != $last_start) { $last_start = $start; echo "<br /><b>" . $start . "</b><br />"; } $tResult = mysql_query("SELECT count(model) FROM models WHERE make = '{$row['make']}'"); $total = mysql_result($tResult, 0); echo "<a href=\"{$row['url']}\">{$row['make']}</a> ({$total})<br />"; } // end while }// end if else { echo "None"; } ?> That should get you what you want. The inner query can probably be compressed into 1 query if I wanted to take the time to do it. But this will be better than your original code by far. Note that the tResult query will need to be modified to fit your models table structure. EDIT: Added the "A" "B" to the top etc. Quote Link to comment Share on other sites More sharing options...
premiso Posted February 18, 2009 Share Posted February 18, 2009 One other minor fix (edit time was up) $start = substr($row['make'], 0); Should be $start = substr($row['make'], 0, 1); Sorry about that. Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 18, 2009 Share Posted February 18, 2009 No offense Permiso, but doing queries within loops is bad form in my opinion. It can lead to severe performance issues. Note: you will need to modify the table and field names in the JOIN part of the query as appropriate for your database. //Query ALL the records ordered by make $query = "SELECT tmake.make, tmake.url, COUNT(models.model) as model_count FROM tmake JOIN models ON models.makeid = tmake.makeid ORDER BY tmake.make GROUP BY models.model"; $result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR); if(mysql_num_rows($result) >= 1) { //There were results, let's display them $current_letter = false; while($row = mysql_fetch_assoc($result)) { //Detected a new letter. Insert header anchor if($current_letter != substr($row['make'], 0, 1)) { $current_letter != substr($row['make'], 0, 1) echo "<br /><h1><a name=\"$current_letter\">$current_letter</a></h1><br />"; } //Display the current make with count echo "<a href=\"{$row['url']}\">{$row['make']} ({$row['model_count']})</a><br />"; } // end while } // end if else { //There were no results echo "None"; } Quote Link to comment Share on other sites More sharing options...
premiso Posted February 18, 2009 Share Posted February 18, 2009 No offense Permiso, but doing queries within loops is bad form in my opinion. It can lead to severe performance issues. None taken. I know it is bad, but my brain is not working right for SQLs today, so yea. Thanks for posting a correct way to do that. lol and that is why I said: The inner query can probably be compressed into 1 query if I wanted to take the time to do it Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 19, 2009 Author Share Posted February 19, 2009 Premiso and MjDamato, thank you both very much! I will try your suggestions immediately. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 19, 2009 Author Share Posted February 19, 2009 Thanks for the help, guys! I really appreciate it. What I want to do has become much more complicated. I must go away and think about it. As such, this thread will now be marked 'Solved'. Once again, thanks for everything. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.