lilgezuz Posted December 30, 2011 Share Posted December 30, 2011 I have the below code and it all works right expect for numbers, how would I get all titles that start with a number to show up under the # link? Any help would be awesome <?php $alphabet = 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'); foreach ($alphabet as $letter) { echo "<a href=\"?letter=" . $letter . "\">" . $letter . "</a> ¦ ";} ?><?php require("db.php"); $letter=$_GET['letter']; //if(isset($letter)){ $query = "SELECT COUNT(*) as num FROM games WHERE title LIKE '$letter%'"; $total_pages = mysql_fetch_array(mysql_query($query)); include("pagination.php"); $sql = "SELECT * FROM games WHERE title LIKE '$letter%' LIMIT $start, $limit"; $result = mysql_query($sql); while($rows=mysql_fetch_array($result)) { echo '<tr><td>'.$rows['title'].'<br></td></tr>'; } // } ?> </table> <?php echo $pagination; ?> Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted December 30, 2011 Share Posted December 30, 2011 This should do the job: <?php $alphabet = 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'); foreach ($alphabet as $letter) { echo "<a href=\"?letter=" . $letter . "\">" . $letter . "</a> ¦ ";} ?><?php require("db.php"); $letter=$_GET['letter']; //if(isset($letter)){ if (chr($letter) >= '1' && chr($letter) <= '9' ){ $where = " WHERE LEFT(title, 1, 1) BETWEEN 0 AND 9"; } else{ $where = " WHERE LEFT(UPPER(title), 1, 1) = UPPER('$letter')"; } $query = "SELECT COUNT(*) as num FROM games $where"; $total_pages = mysql_fetch_array(mysql_query($query)); include("pagination.php"); $sql = "SELECT * FROM games WHERE title LIKE '$letter%' LIMIT $start, $limit"; $result = mysql_query($sql); while($rows=mysql_fetch_array($result)) { echo '<tr><td>'.$rows['title'].'<br></td></tr>'; } // } ?> </table> <?php echo $pagination; ?> Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2011 Share Posted December 30, 2011 One correction to Muddy_Funster's solution. The value of $letter will be "#" when searching for numeric titles. But, the code above assumes that the user will be selecting 1-9. A couple other things: The original code is appending a " | " after each link - including the last. I assume that is not what you really want (solved below). Also, the isset($letter) check is worthless since you set $letter on the previous line (even if $_GET['letter'] is not set $letter will be). Instead you should be checking if $_GET['letter'] is set. Plus, there is no validation of the input from sql injection. Lastly, the is no handling in the case where the GET value is not in the approved list of values which would cause the script to fail. <?php $alphabet = 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'); foreach ($alphabet as &$letter) { $letter = "<a href=\"?letter={$letter}\">{$letter}</a>"; } $page_links = implode(' | ', $alphabet); echo $page_links; require("db.php"); $where = false; if(isset($_GET['letter'])) { $letter = mysql_real_escape_string(trim(strtoupper($_GET['letter']))); if ($letter=='#'){ $where = " WHERE LEFT(title, 1, 1) BETWEEN 0 AND 9"; } else{ $where = " WHERE LEFT(UPPER(title), 1, 1) = '$letter'"; } } //Only run page query if 'letter' was valid - i.e. $where is set if($where != false) { $query = "SELECT COUNT(*) as num FROM games $where"; $total_pages = mysql_fetch_array(mysql_query($query)); include("pagination.php"); $sql = "SELECT title FROM games {$where} LIMIT $start, $limit"; $result = mysql_query($sql); echo "<table>\n"; while($row = mysql_fetch_array($result)) { echo "<tr><td>{$row['title']}<br></td></tr>\n"; } echo "</table>\n"; echo $pagination; } ?> Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted December 30, 2011 Author Share Posted December 30, 2011 One correction to Muddy_Funster's solution. The value of $letter will be "#" when searching for numeric titles. But, the code above assumes that the user will be selecting 1-9. A couple other things: The original code is appending a " | " after each link - including the last. I assume that is not what you really want (solved below). Also, the isset($letter) check is worthless since you set $letter on the previous line (even if $_GET['letter'] is not set $letter will be). Instead you should be checking if $_GET['letter'] is set. Plus, there is no validation of the input from sql injection. Lastly, the is no handling in the case where the GET value is not in the approved list of values which would cause the script to fail. <?php $alphabet = 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'); foreach ($alphabet as &$letter) { $letter = "<a href=\"?letter={$letter}\">{$letter}</a>"; } $page_links = implode(' | ', $alphabet); echo $page_links; require("db.php"); $where = false; if(isset($_GET['letter'])) { $letter = mysql_real_escape_string(trim(strtoupper($_GET['letter']))); if ($letter=='#'){ $where = " WHERE LEFT(title, 1, 1) BETWEEN 0 AND 9"; } else{ $where = " WHERE LEFT(UPPER(title), 1, 1) = '$letter'"; } } //Only run page query if 'letter' was valid - i.e. $where is set if($where != false) { $query = "SELECT COUNT(*) as num FROM games $where"; $total_pages = mysql_fetch_array(mysql_query($query)); include("pagination.php"); $sql = "SELECT title FROM games {$where} LIMIT $start, $limit"; $result = mysql_query($sql); echo "<table>\n"; while($row = mysql_fetch_array($result)) { echo "<tr><td>{$row['title']}<br></td></tr>\n"; } echo "</table>\n"; echo $pagination; } ?> The letters display across the top of the page then I get these 2 errors one for each fetch array Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2011 Share Posted December 30, 2011 The letters display across the top of the page then I get these 2 errors one for each fetch array Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource Then the query is failing. I provide code as a guide as to the process you can follow to achieve the desired result. I give no guarantees that it is error free (see my signature). I leave it to the recipient to debug any typos and such since I typically don't have the input data (e.g. your database) in order to easily test the code. I didn't check the syntax of the query that Muddy_Funster provided. The error is due to the format of the LEFT() command. He had three parameters, but LEFT() only takes two. Try replacing the if/else section for the where clause with this if ($letter=='#'){ $where = " WHERE LEFT(title, 1) BETWEEN 0 AND 9"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted December 30, 2011 Author Share Posted December 30, 2011 The letters display across the top of the page then I get these 2 errors one for each fetch array Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource Then the query is failing. I provide code as a guide as to the process you can follow to achieve the desired result. I give no guarantees that it is error free (see my signature). I leave it to the recipient to debug any typos and such since I typically don't have the input data (e.g. your database) in order to easily test the code. I didn't check the syntax of the query that Muddy_Funster provided. The error is due to the format of the LEFT() command. He had three parameters, but LEFT() only takes two. Try replacing the if/else section for the where clause with this if ($letter=='#'){ $where = " WHERE LEFT(title, 1) BETWEEN 0 AND 9"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } The erros went away but when I click on the # link it just displays the first result from my database which doesn't begin with a number Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2011 Share Posted December 30, 2011 Hmm . . . I think the problem is that when you use the BETWEEN operator MySQL is expecting a numeric comparison. So, it is converting the string value from the title into a numeric value. So, anything not a number is converting to 0. This should work if ($letter=='#'){ $where = " WHERE LEFT(title, 1) BETWEEN 0 AND 9"; } else{ $where = " INSTR('012345679', LEFT(title, 1)) > 0"; } Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted December 30, 2011 Author Share Posted December 30, 2011 Hmm . . . I think the problem is that when you use the BETWEEN operator MySQL is expecting a numeric comparison. So, it is converting the string value from the title into a numeric value. So, anything not a number is converting to 0. This should work if ($letter=='#'){ $where = " WHERE LEFT(title, 1) BETWEEN 0 AND 9"; } else{ $where = " INSTR('012345679', LEFT(title, 1)) > 0"; } Thanks for your help it worked after I put WHERE in front of INSTR. Your awesome for taking the time to help me out Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted December 30, 2011 Author Share Posted December 30, 2011 well I thought that worked but now on every letter I click it shows only results starting with a number Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2011 Share Posted December 30, 2011 I "fixed" the wrong one if ($letter=='#'){ $where = " INSTR('012345679', LEFT(title, 1)) > 0"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted December 31, 2011 Author Share Posted December 31, 2011 I "fixed" the wrong one if ($letter=='#'){ $where = " INSTR('012345679', LEFT(title, 1)) > 0"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } Still not working correctly. When I click on any letter that shows the correct result but when I click on the # link it shows the first record from my database only and it begins with a letter not a number. The first record in my database had a blank space before the title I guess thats why it was showing up unders the # link. But the titles that start with a number still don't show up Quote Link to comment Share on other sites More sharing options...
laffin Posted December 31, 2011 Share Posted December 31, 2011 Ur missing your WHERE keyword Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted December 31, 2011 Author Share Posted December 31, 2011 Ur missing your WHERE keyword It doesn't work with WHERE in it either Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 31, 2011 Share Posted December 31, 2011 Laffin is correct, the WHERE clause was missing. I will say it again I provide code as a guide as to the process you can follow to achieve the desired result. I give no guarantees that it is error free (see my signature). I leave it to the recipient to debug any typos and such since I typically don't have the input data (e.g. your database) in order to easily test the code. These are all simple mistakes and ones that would be easily found/fixed if I was working in a live environment. But, I am providing this code with absolutely no way to test with your script/database. I expect people in this forum to be able to debug simple errors. if ($letter=='#'){ $where = " WHERE INSTR('012345679', LEFT(title, 1)) > 0"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted January 1, 2012 Author Share Posted January 1, 2012 Laffin is correct, the WHERE clause was missing. I will say it again I provide code as a guide as to the process you can follow to achieve the desired result. I give no guarantees that it is error free (see my signature). I leave it to the recipient to debug any typos and such since I typically don't have the input data (e.g. your database) in order to easily test the code. These are all simple mistakes and ones that would be easily found/fixed if I was working in a live environment. But, I am providing this code with absolutely no way to test with your script/database. I expect people in this forum to be able to debug simple errors. if ($letter=='#'){ $where = " WHERE INSTR('012345679', LEFT(title, 1)) > 0"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } I've been trying to debug it, I tried to put WHERE in the code already and it didn't solve the problem. It doesn't display anything under the # link unless I put a blank space in front of the game titles that start with a number Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted January 1, 2012 Author Share Posted January 1, 2012 Well your code was right the # was through it off, so I changed both # signs to 0-9 and it worked. Thanks for all the help. Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted February 10, 2012 Author Share Posted February 10, 2012 How can I make this so it shows titles beinging with a symbol( ., {, [ ) I have an idea but not 100% sure. if ($letter=='0-9'){ $where = " WHERE INSTR('012345679', LEFT(title, 1)) > 0"; } else{ $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 10, 2012 Share Posted February 10, 2012 How can I make this so it shows titles beinging with a symbol( ., {, [ ) I have an idea but not 100% sure. When you say symbol, don't you really mean anything that is NOT a letter or a number? And, what was your idea? I would just use the same logic applied for numbers, but use a negative check. if ($letter=='0-9') { //Number search $where = " WHERE INSTR('012345679', LEFT(title, 1)) = 0"; } elseif($letter=='SYMBOL') //Change to the value you use for symbol { //Symbol search $where = " WHERE INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345679', LEFT(UPPER(title), 1)) = 0"; } else { //Letter search $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted February 10, 2012 Author Share Posted February 10, 2012 How can I make this so it shows titles beinging with a symbol( ., {, [ ) I have an idea but not 100% sure. When you say symbol, don't you really mean anything that is NOT a letter or a number? And, what was your idea? I would just use the same logic applied for numbers, but use a negative check. if ($letter=='0-9') { //Number search $where = " WHERE INSTR('012345679', LEFT(title, 1)) = 0"; } elseif($letter=='SYMBOL') //Change to the value you use for symbol { //Symbol search $where = " WHERE INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345679', LEFT(UPPER(title), 1)) = 0"; } else { //Letter search $where = " WHERE LEFT(UPPER(title), 1) = '$letter'"; } That did not work, just shows a blank page Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 10, 2012 Share Posted February 10, 2012 That did not work, just shows a blank page Well then, it sucks to be you. Did you even try to figure out what was wrong? Did you run the query through PHPMyAdmin first to see what that produced before trying to implement it into your code. I did mistakenly alter the numeric search by changing the "> 0" to "= 0". That one should be using the greater than operator and the symbol search should be using the equal operator. but, in either case you would not get 0 results - unless you legitimately had no matching records. I tested the process above and it worked as I expected. I created sample data as follows: A - Alpha 1 x - Alpha 2 0 - Numeric 1 9 - Numeric 2 $ - Symbol 1 @ - Symbol 2 . - Symbol 3 When I used WHERE LEFT(UPPER(title), 1) = 'A' and WHERE LEFT(UPPER(title), 1) = 'X' I received the results of 'A - Alpha 1' and 'x - Alpha 2', respectively. When I used WHERE INSTR('012345679', LEFT(title, 1)) > 0 I received the results of '0 - Numeric 1' & '9 - Numeric 2'. When I used WHERE INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345679', LEFT(UPPER(title), 1)) = 0, the results were: '$ - Symbol 1', '@ - Symbol 2', & '. - Symbol 3'. Test your queries in your database management APP before you try and implement in your code. Then, once you implement, if you do not get the results you expect 1) validate that the query you are running is the one you expect, 2) check if the query is returning errors, i.e. mysql_error(), 3) If no errors, see if the query is returning results, i.e. mysql_num_rows(). If the query is completing w/o errors and mysql_num_rows() is >t 0 and you are getting a blank page then there is most likely a problem in the PHP code for outputting the results. Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted February 10, 2012 Author Share Posted February 10, 2012 That did not work, just shows a blank page Well then, it sucks to be you. Did you even try to figure out what was wrong? Did you run the query through PHPMyAdmin first to see what that produced before trying to implement it into your code. I did mistakenly alter the numeric search by changing the "> 0" to "= 0". That one should be using the greater than operator and the symbol search should be using the equal operator. but, in either case you would not get 0 results - unless you legitimately had no matching records. I tested the process above and it worked as I expected. I created sample data as follows: A - Alpha 1 x - Alpha 2 0 - Numeric 1 9 - Numeric 2 $ - Symbol 1 @ - Symbol 2 . - Symbol 3 When I used WHERE LEFT(UPPER(title), 1) = 'A' and WHERE LEFT(UPPER(title), 1) = 'X' I received the results of 'A - Alpha 1' and 'x - Alpha 2', respectively. When I used WHERE INSTR('012345679', LEFT(title, 1)) > 0 I received the results of '0 - Numeric 1' & '9 - Numeric 2'. When I used WHERE INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345679', LEFT(UPPER(title), 1)) = 0, the results were: '$ - Symbol 1', '@ - Symbol 2', & '. - Symbol 3'. Test your queries in your database management APP before you try and implement in your code. Then, once you implement, if you do not get the results you expect 1) validate that the query you are running is the one you expect, 2) check if the query is returning errors, i.e. mysql_error(), 3) If no errors, see if the query is returning results, i.e. mysql_num_rows(). If the query is completing w/o errors and mysql_num_rows() is >t 0 and you are getting a blank page then there is most likely a problem in the PHP code for outputting the results. Thanks for the help. I'm getting there, tested in myphpadmin and it did display results.I will try and test now to figure out why its not displaying the results Quote Link to comment Share on other sites More sharing options...
lilgezuz Posted February 11, 2012 Author Share Posted February 11, 2012 I figured the problem out I had it like this elseif($letter=='Other') When it should have been elseif($letter=='OTHER') 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.