HenryCan Posted April 25, 2012 Share Posted April 25, 2012 I'm encountering some inconsistent behaviour with respect to variable substitution. I hope someone here can help me figure out why. Let me explain what I'm trying to do. I am working on a prototype that is supposed to display a simple three-column MySQL table in ascending order by one or the other of the three columns. Exactly the same data (rows and columns) is shown each time; only the sequence of the rows differs. To accomplish this, I show the user a form with three radio buttons in a single group. The user chooses the radio button that represents the column which is to be used in the sort, then presses Submit. That invokes a script that contains PHP. The script reads the value passed from the form, which is the name of one of the three columns of the table, Series, Character, or Actor. and stores it in a variable name $SortKey. Then, the variable is used in ORDER BY clause of the SQL Query to cause sorting on that column. This all works perfectly for the first and third columns, Series and Actor. But when script executes for the column named Character, the result is an empty table. There is no error message of any kind. I'm baffled by this. Obviously, my approach is correct otherwise the first and third columns would fail as well. But I can't see anything different about the second column. I'm very new to PHP but know HTML quite well and have substantial experience with Java and a little bit with Perl so I'm not new to programming by a long shot. Here's the form, which is called Captains_form.php: <html> <head> <link rel="stylesheet" type="text/css" href="css/main.css" media="screen"/> <link rel="stylesheet" type="text/css" href="css/print.css" media="print"/> </head> <body> <h1>Enterprise Captains</h1> <p>You can view the list of Enterprise Captains in any of the following sequences:</p> <form action="Captains.php" method="post"> <input type="radio" name="sort" value="Series" /> Series (ascending)<br /> <input type="radio" name="sort" value="Character" /> Character (ascending)<br /> <input type="radio" name="sort" value="Actor" /> Actor (ascending)<br /> <input type="submit" /> </form> </body> </html> Here's the script, which is called Captains.php: <html> <head> <link rel="stylesheet" type="text/css" href="css/main.css" media="screen"/> <link rel="stylesheet" type="text/css" href="css/print.css" media="print"/> </head> <body> <h2>Enterprise Captains List</h2> <?php $con = mysql_connect("localhost:3306","my_All","******"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("my_Sandbox", $con); //echo $_POST["sort"]; $SortKey=$_POST["sort"]; $result = mysql_query("SELECT * FROM Captains order by $SortKey"); echo "<table border='1' cellpadding='5' cellspacing='0'>"; echo "<tr class='heading'><th>Series</th><th>Character</th><th>Actor</th></tr>"; while($row = mysql_fetch_array($result)) { echo "<tr class='detail'><td>" . $row['Series'] . "</td><td>" . $row['Character'] . "</td><td>" . $row['Actor'] . "</td></tr>"; } echo "</table>"; mysql_close($con); ?> </body> </html> Can anyone tell me why the second column, Character, produces an empty result when I select it on the form? Also, if there is an error message being produced when the query executes, how can I get it? I think I'm handing errors in the connection correctly but I'm not sure how to detect an error in the execution of the query. Quote Link to comment Share on other sites More sharing options...
kicken Posted April 25, 2012 Share Posted April 25, 2012 Your query is likely failing because CHARACTER is a reserved word in mysql. You can quote it using backticks (`) to use it. You should get in the habit of including some kind of error reporting in your scripts, especially for queries. Use mysql_error() to see the error message that occurred. Quote Link to comment Share on other sites More sharing options...
HenryCan Posted April 25, 2012 Author Share Posted April 25, 2012 Many thanks, kicken!! Two things. First, can you tell me WHERE to backtick "Character" in this case? Would I do it in the form itself? Or in the assignment statement for $SortKey? Or in the assignment statement for $result? Will having the backticks in either of the latter two assignment statements mess up the behaviour of the other columns given that they aren't reserved words? Second, how do I do the error handling for the fetch? With a try/catch block? Or is there a better way? Quote Link to comment Share on other sites More sharing options...
Maq Posted April 25, 2012 Share Posted April 25, 2012 1) The ideal solution would be to alter your table name to something non-reserved. But if not, you put the backticks around the column name: SELECT * FROM Captains order by `$SortKey` 2) Read this for proper error handling - http://www.phpfreaks.com/blog/or-die-must-die I would also check to see if there were 0 rows returned with mysql_run_rows and give proper feedback to the user. Quote Link to comment Share on other sites More sharing options...
HenryCan Posted April 27, 2012 Author Share Posted April 27, 2012 Thanks Maq and kicken! I got the backticks to work by following your suggestion. I wanted to do that just in case MySQL didn't want to let me change the column name. Then I successfully changed the column name and took the backticks out again because changing the column name was definitely the best way to proceed. The article on error handling was also very helpful. I can see it's going to take a bit of time to evolve a good, clean error handling style, just as it has with Java. But I'm started down the right road now, thanks to your suggestion. 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.