Fluoresce Posted February 19, 2009 Share Posted February 19, 2009 I want to do something which is way out of my newbie league. I am going to describe it and hope that one of you pros can give me some tips on how to start. Any help will be appreciated. -------------------- Imagine this simple database of car makes and models: make_table: makeid make model_table: modelid model makeid -------------------- I want to display all of the car makes in alphabetical order, in two columns down a page. The As, Bs, Cs, etc., must be separate, and must have the appropriate letter as a heading. For example: A Astom Martin Audi B Bentley BMW -------------------- Each car make must show the number of models it has, in brackets. For example: A Astom Martin (12) Audi (15) B Bentley (10) BMW (22) -------------------- At the top of the page, there must be links to the As, Bs, Cs, etc., thus: A ¦ B ¦ C A Astom Martin (12) Audi (15) B Bentley (10) BMW (22) Quote Link to comment Share on other sites More sharing options...
RichardRotterdam Posted February 19, 2009 Share Posted February 19, 2009 thats not that complicated. First of you display has nothing to do with you database design. You separate the cars alphabetically using php. You use a query to fetch all cars or all cars starting with a letter. your database table would be like cars car_id (int primairy key) brand_id (int foreign key) car_name (varchar) car_description (text) car_image_url (varchar 255) brands brand_id brand_name if you want a car to have multiple images then have a separate table for that 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 effort, DJ Kat. I know that stuff, dude. Quote Link to comment Share on other sites More sharing options...
Gighalen Posted February 19, 2009 Share Posted February 19, 2009 when you execute the query to retrieve the data from the database you will have to use a ORDER BY clause at the end of the query. (SELECT * FROM cars ORDER BY car_name ASC) will select the cars in alphabetical order from the database and display them. To show the number of cars in each category, simply use the mysql_num_rows function. To select stuff by A, B, C etc, use the MySQL LIKE statement. LIKE "A", LIKE "B", etc. Hope this points you in the right direction. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 19, 2009 Author Share Posted February 19, 2009 Thanks to MjDamato and others, I have this: <?php $conn = mysql_connect('localhost','deleted', 'deleted') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('databasename', $conn) or trigger_error("SQL", E_USER_ERROR); //Query ALL the records ordered by make $query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count FROM tblmake, tblmodel WHERE tblmake.makeid = tblmodel.makeid ORDER BY tblmake.make ASC"; $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 { echo "None"; } ?> Unfortunately, it doesn't work properly: "Parse error: syntax error, unexpected T_ECHO in [Location] on line 126" Can anyone see the problem? Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 20, 2009 Share Posted February 20, 2009 You apparently are only posting part of the code from your page, because that code only has 38 lines and the error is occuring on line 126. When you have a line number to work with it is much easier to find the problem. In this case though, I was able to spot the problem (as well as another one): In this section, I 1) forgot to change the "!=" to just "=" in the line after the IF condition and 2) forgot to include a semi-colon at the end of the same line //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 />"; } NOTE: I only post my code as a guide on a methodology that should work. I typically do not test it - especially when it involves a database that I do not have access to and do not want to try and replicate. I figure syntax errors should be easily solved by the person receiving the code. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 20, 2009 Author Share Posted February 20, 2009 Thanks for the response, MJ! I made the changes and they stopped the error. The problem is, the code doesn't work as intended. It only shows one result for the letter A, and that's it. It looks something like this: A Audi (90) Note the count of 90. It includes all of the models in the database, not just the models for Audi. I will play with the script to see what I can do. If you - or anyone else - can think of anything, I will appreciate the help. Otherwise, thanks for your help so far. Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 20, 2009 Share Posted February 20, 2009 The query you posted above is not the one I posted for you in a different thread. There is no GROUP BY clause - which is probably the problem. <?php $conn = mysql_connect('localhost','deleted', 'deleted') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('databasename', $conn) or trigger_error("SQL", E_USER_ERROR); //Query ALL the records ordered by make $query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count FROM tblmake LEFT JOIN models ON tblmake.makeid = tblmodel.makeid GROUP BY tblmodel.modelid ORDER BY tblmake.make ASC"; $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 { echo "None"; } ?> Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 20, 2009 Author Share Posted February 20, 2009 MJ, it worked! Thank you very much! I had to make a few tiny changes, but in the end, it worked: <?php //Query ALL the records ordered by make $query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count FROM tblmake LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid GROUP BY tblmake.makid ORDER BY tblmake.make ASC"; $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>"; } //Display the current make with count echo "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />"; } // end while } // end if else { echo "None"; } ?> Can I ask you another quick question, please? Is it possible to echoe the results in a table with two colums, with 13 letters on either side? Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 20, 2009 Share Posted February 20, 2009 Um, not sure what you are asking for is what you really want. There will be many letters without any results. Do you want letters without results listed (e.g. 'Z')? And how do you want the letters displayed? Like this A B C D E f ... Or like this A N B O C P ... Edit: Also, post the code that you have working for any changes to be made. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 20, 2009 Author Share Posted February 20, 2009 Um, not sure what you are asking for is what you really want. There will be many letters without any results. Do you want letters without results listed (e.g. 'Z')? And how do you want the letters displayed? Like this A B C D E f ... Or like this A N B O C P ... Edit: Also, post the code that you have working for any changes to be made. Yes, I would like all letters to display, even if there are no makes for a particular letter (otherwise, the letter links at the top of the page won't work). This will give me two neat columns with 13 letters each, like this: A N B O Is that difficult? Here's my code so far: <table class="center" style="text-align:center" width="400px" border="0" cellpadding="5" cellspacing="5"> <tr> <td> <a href="#A">A</a> <a href="#B">B</a> <a href="#C">C</a> <a href="#D">D</a> <a href="#E">E</a> <a href="#F">F</a> <a href="#G">G</a> <a href="#H">H</a> <a href="#I">I</a> <a href="#J">J</a> <a href="#K">K</a> <a href="#L">L</a> <a href="#M">M</a> </td> </tr> <tr> <td> <a href="#N">N</a> <a href="#O">O</a> <a href="#P">P</a> <a href="#Q">Q</a> <a href="#R">R</a> <a href="#S">S</a> <a href="#T">T</a> <a href="#U">U</a> <a href="#V">V</a> <a href="#W">W</a> <a href="#X">X</a> <a href="#Y">Y</a> <a href="#Z">Z</a> </td> </tr> </table> <?php $conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR); //Query ALL the records ordered by make $query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count FROM tblmake LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid GROUP BY tblmake.makid ORDER BY tblmake.make ASC"; $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>"; } //Display the current make with count echo "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />"; } // end while } // end if else { echo "None"; } ?> I really do appreciate the help, MJ. I'm learning loads off you. Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 20, 2009 Share Posted February 20, 2009 Well, you could just as easily create the links at the top to only include the letters for which there are results! What you are asking is easy enough, but one possible issue is that the lists on each side might not be uniform. For example, if there are a lot of records in the A-M range, the left side of the list could be much longer than the list on the right. <?php $conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR); //Query ALL the records ordered by make $query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count FROM tblmake LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid GROUP BY tblmake.makid ORDER BY tblmake.make ASC"; $result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR); $total_records = mysql_num_rows($result); if($total_records > 0) { //There were results, let's display them $letters = array ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'); $row = mysql_fetch_assoc($result) foreach($letters as $letter) { $letter_idx = array_search($letter, $letters); $side = ($letter_idx < 13)?'left':'right'; $td[$side] .= "<br /><h1><a name=\"$letter\">$letter</a></h1>\n"; while (substr($row['make'], 0, 1) == $letter) { $td[$side] .= "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />\n"; if (!$row = mysql_fetch_assoc($result)) { break; } } } } // end if else { echo "No results"; } ?> <table class="center" style="text-align:center" width="400px" border="1" cellpadding="5" cellspacing="5"> <tr> <td colspan="2"> <a href="#A">A</a> <a href="#B">B</a> <a href="#C">C</a> <a href="#D">D</a> <a href="#E">E</a> <a href="#F">F</a> <a href="#G">G</a> <a href="#H">H</a> <a href="#I">I</a> <a href="#J">J</a> <a href="#K">K</a> <a href="#L">L</a> <a href="#M">M</a> </td> </tr> <tr> <td colspan="2"> <a href="#N">N</a> <a href="#O">O</a> <a href="#P">P</a> <a href="#Q">Q</a> <a href="#R">R</a> <a href="#S">S</a> <a href="#T">T</a> <a href="#U">U</a> <a href="#V">V</a> <a href="#W">W</a> <a href="#X">X</a> <a href="#Y">Y</a> <a href="#Z">Z</a> </td> </tr> <tr> <td valign="top"><?php echo $td['left']; ?></td> <td valign="top"><?php echo $td['right']; ?></td> </tr> </table> Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 20, 2009 Author Share Posted February 20, 2009 MJ, thank you for yet another great response! Your new code looks great, but when I tried it, I got an error: "Parse error: syntax error, unexpected T_FOREACH in [Location] on line 123" I looked through the code to try to identify the problem, but then I remembered that I can't read PHP properly yet! Can you - or anyone else - see the problem? Whatever the case, I hereby dub MJ my PHP Grand Master! Quote Link to comment Share on other sites More sharing options...
samshel Posted February 20, 2009 Share Posted February 20, 2009 there is syntax error 1 row above, semicolon is missing....please read the error carefully ! Read Mj's signature Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 20, 2009 Author Share Posted February 20, 2009 there is syntax error 1 row above, semicolon is missing....please read the error carefully ! Read Mj's signature Thanks, Samshell! Grand Master MJ, it works! Thank you very much! Let me play with it a bit - I'm talking about the code - to see if everything's cool. Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 21, 2009 Share Posted February 21, 2009 ....please read the error carefully ! Read Mj's signature Wow, someone actual read that! I will try to test code if it doesn't mean I have to put a lot of work into it. If a database is involved I usually won't. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 21, 2009 Author Share Posted February 21, 2009 I've been playing with that code, MJ. Great work! It's almost perfect. There are two minor problems: 1) When there are no car makes for a letter, the letter appears but not the word 'None': Q R Renault (7) Rolls Royce (21) 2) As you prediced, one column is much longer than the other. The only way this can be fixed is if A and N, B and O, etc., are put into table rows, thus: A N --- B O --- C P Would that require a major change of the code? Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 21, 2009 Share Posted February 21, 2009 1) When there are no car makes for a letter, the letter appears but not the word 'None' And, where did you ask for that? I do like helping people, but there's nothing more frustrating than taking the time to help someone only to have them ask for something different once you provide what they originally asked for. The code below should get you what you want (not tested). <?php $conn = mysql_connect('localhost','hidden', 'hidden') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('hidden', $conn) or trigger_error("SQL", E_USER_ERROR); //Query ALL the records ordered by make $query = "SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count FROM tblmake LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid GROUP BY tblmake.makid ORDER BY tblmake.make ASC"; $result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR); $total_records = mysql_num_rows($result); if($total_records > 0) { //There were results, let's display them $letters = array ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'); $row = mysql_fetch_assoc($result); foreach($letters as $idx => $letter) { $no_makes_in_letter = true; $td[$idx] .= "<td><h1><a name=\"$letter\">$letter</a></h1><br />\n"; while (substr($row['make'], 0, 1) == $letter) { $no_makes_in_letter = false; $td[$idx] .= "<a href=\"{$row['url']}\">{$row['make']}</a> ({$row['model_count']})<br />\n"; if (!$row = mysql_fetch_assoc($result)) { break; } } if($no_makes_in_letter) { $td[$idx] .= "None<br />\n"; } $td[$idx] .= "</td>\n"; } $output = ""; for($i=0; $idx<13; $i++) { $output .= "<tr>\n"; $output .= $td[$idx]; $output .= $td[$idx+13]; $output .= "</tr>\n"; } } // end if else { echo "No results"; } ?> <table class="center" style="text-align:center" width="400px" border="1" cellpadding="5" cellspacing="5"> <tr> <td colspan="2"> <a href="#A">A</a> <a href="#B">B</a> <a href="#C">C</a> <a href="#D">D</a> <a href="#E">E</a> <a href="#F">F</a> <a href="#G">G</a> <a href="#H">H</a> <a href="#I">I</a> <a href="#J">J</a> <a href="#K">K</a> <a href="#L">L</a> <a href="#M">M</a> </td> </tr> <tr> <td colspan="2"> <a href="#N">N</a> <a href="#O">O</a> <a href="#P">P</a> <a href="#Q">Q</a> <a href="#R">R</a> <a href="#S">S</a> <a href="#T">T</a> <a href="#U">U</a> <a href="#V">V</a> <a href="#W">W</a> <a href="#X">X</a> <a href="#Y">Y</a> <a href="#Z">Z</a> </td> </tr> <?php echo $output; ?> </table> Quote Link to comment Share on other sites More sharing options...
aliento Posted February 21, 2009 Share Posted February 21, 2009 you are fucking experts i really like you and jealous you Great Incredible you are indeed freaks Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 21, 2009 Author Share Posted February 21, 2009 And, where did you ask for that? I do like helping people, but there's nothing more frustrating than taking the time to help someone only to have them ask for something different once you provide what they originally asked for. Sorry, dude! I really do appreciate your help. You're a PHP machine! Do you talk in PHP, too! Now, off to test the new code . . . Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 21, 2009 Author Share Posted February 21, 2009 That's strange . . . There doesn't seem to be any output. There isn't even an error message. When I do a 'View source', all that appears is this: <table class="center" style="text-align:center" width="400px" border="1" cellpadding="5" cellspacing="5"> <tr> <td colspan="2"> <a href="#A">A</a> <a href="#B">B</a> <a href="#C">C</a> <a href="#D">D</a> <a href="#E">E</a> <a href="#F">F</a> <a href="#G">G</a> <a href="#H">H</a> <a href="#I">I</a> <a href="#J">J</a> <a href="#K">K</a> <a href="#L">L</a> <a href="#M">M</a> </td> </tr> <tr> <td colspan="2"> <a href="#N">N</a> <a href="#O">O</a> <a href="#P">P</a> <a href="#Q">Q</a> <a href="#R">R</a> <a href="#S">S</a> <a href="#T">T</a> <a href="#U">U</a> <a href="#V">V</a> <a href="#W">W</a> <a href="#X">X</a> <a href="#Y">Y</a> <a href="#Z">Z</a> </td> </tr> </table> Since there's no error message, I created a file with this code: <?php error_reporting(E_ALL); ini_set("display_errors", 1); include("file_with_errors.php"); ?> When I ran this file, I got these notices: "Notice: Undefined offset: 0 in [Location] on line 124 Notice: Undefined offset: 1 in [Location] on line 124 Notice: Undefined offset: 2 in [Location] on line 124" Etc., etc., all the way up to: "Notice: Undefined offset: 25 in [Location] on line 124" The problem is, I don't know which line 124 is. It's blank when I look at my code in Notepad. Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 22, 2009 Share Posted February 22, 2009 I made a last minute change in some variable names and didn't get them all correct. Change the last loop as follows: $output = ""; for($idx=0; $idx<13; $idx++) { $output .= "<tr>\n"; $output .= $td[$idx]; $output .= $td[$idx+13]; $output .= "</tr>\n"; } Still not tested, so there may be more minor errors. As for the error messages, the line numbers refer to the PHP file NOT the HTML output. Quote Link to comment Share on other sites More sharing options...
Fluoresce Posted February 22, 2009 Author Share Posted February 22, 2009 MJ, it worked! Everything's working brilliantly. With a great big thank you to you and to anyone else who helped, I hereby declare this thread solved. 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.