Peuplarchie Posted April 29, 2010 Share Posted April 29, 2010 Good day to you all, I'm working on a script which would read a url var and sort mysql table by the variable, but I always get the errore that : Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in DB_API/sort_column.php on line 13 Can somebody help me fix my problem. I think it's the variable that is not use right in the MYSQL query ( $result = mysql_query("SELECT * FROM NHL_GBG_PLAYERS ORDER BY {$col}"); ) Here is my code : <?php $con = mysql_connect("localhost","sports","sports"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("sports", $con); $col = $_POST['coll']; $result = mysql_query("SELECT * FROM players ORDER BY {$col}"); while($row = mysql_fetch_array($result)) { echo "<div style=\"width:1090px; align-left:auto; margin-right:auto;\">"; echo "<div style=\"float:left; width:30px; border:1px solid #000000; \">" . $row['id'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['date'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['heure'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['lieu'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['assistance'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['adversaire'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['equipe'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['nom'] . "</div>"; echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['b'] . "</div>"; echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['p'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['pts'] . "</div>"; echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['l'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['mdp'] . "</div>"; echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['bg'] . "</div>"; echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['be'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bea'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bed'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['PM'] . "</div>"; echo "</div><br>"; } mysql_close($con); ?> Thanks! Link to comment https://forums.phpfreaks.com/topic/200112-php-variable-in-mysql-query/ Share on other sites More sharing options...
andrewgauger Posted April 29, 2010 Share Posted April 29, 2010 When you pass text to mysql it varies how it treats your text. In this example, you wouldn't want the {} around your variable name, because to MySQL you are going to want to reference a column name. I think what you want is backticks such are found on the ~ key : $result = mysql_query("SELECT * FROM NHL_GBG_PLAYERS ORDER BY `$col`"); Another possibility is that the $_POST is not what you were expecting. How are you validating that what is sent is a column name? Is it a drop down box? If so is it sending an id or is it actually sending the column name? Link to comment https://forums.phpfreaks.com/topic/200112-php-variable-in-mysql-query/#findComment-1050302 Share on other sites More sharing options...
mrMarcus Posted April 29, 2010 Share Posted April 29, 2010 changed a couple things up. run this code: <?php $sql = "SELECT * FROM players ORDER BY {$col}"; echo '<pre>'. $sql .'</pre>'; //will echo query so you can see if it's what you expect it to be; if ($result = @mysql_query($sql)) { if (mysql_num_rows($result) > 0) { while($row = mysql_fetch_array($result)) { echo "<div style=\"width:1090px; align-left:auto; margin-right:auto;\">"; echo "<div style=\"float:left; width:30px; border:1px solid #000000; \">" . $row['id'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['date'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['heure'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['lieu'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['assistance'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['adversaire'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['equipe'] . "</div>"; echo "<div style=\"float:left; width:100px; border:1px solid #cccccc;\">" . $row['nom'] . "</div>"; echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['b'] . "</div>"; echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['p'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['pts'] . "</div>"; echo "<div style=\"float:left; width:20px; border:1px solid #cccccc;\">" . $row['l'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['mdp'] . "</div>"; echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['bg'] . "</div>"; echo "<div style=\"float:left; width:30px; border:1px solid #cccccc;\">" . $row['be'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bea'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['bed'] . "</div>"; echo "<div style=\"float:left; width:40px; border:1px solid #cccccc;\">" . $row['PM'] . "</div>"; echo "</div><br>"; } } else { echo 'No records found in db.'; } } else { trigger_error(mysql_error()); //will display any mysql errors; } ?> @andrewgauger - curly braces are perfectly fine in the fashion they were being used, and backticks, while good practice, are not necessary and only become necessary when a column name is a mysql reserved word. Link to comment https://forums.phpfreaks.com/topic/200112-php-variable-in-mysql-query/#findComment-1050305 Share on other sites More sharing options...
vividona Posted April 29, 2010 Share Posted April 29, 2010 $result = mysql_query("SELECT * FROM players ORDER BY {$col}") or die(mysql_error()); this will specify your error Link to comment https://forums.phpfreaks.com/topic/200112-php-variable-in-mysql-query/#findComment-1050309 Share on other sites More sharing options...
Peuplarchie Posted April 29, 2010 Author Share Posted April 29, 2010 how would I also variably give the order asc or desc ? mysql_select_db("peuplarc_sports", $con); $ord = $_GET['ord']; if(empty($_GET['coll'])) { die("No column received"); } $col = mysql_real_escape_string(trim($_GET['coll'])); $query = "SELECT * FROM NHL_GBG_PLAYERS ORDER BY $ord `$col`"; $result = mysql_query($query); if($result == false) { user_error(mysql_error() . "<br />\n$query"); die("SQL error"); } Link to comment https://forums.phpfreaks.com/topic/200112-php-variable-in-mysql-query/#findComment-1050316 Share on other sites More sharing options...
mrMarcus Posted April 29, 2010 Share Posted April 29, 2010 k, you gotta scrub your incoming variables ($ord), 'cause you're wide open to sql injection otherwise. instead of killing script: if(empty($_GET['coll'])) { die("No column received"); } when there is no column selected by user, just have it default to a preset one. less user frustration. and, what do $ord and $col represent in the query? it goes, ORDER BY `column_name` then you can set ASC or DESC and to stay on absolute safe-side, use triple comparison operator === here: if($result === false) Link to comment https://forums.phpfreaks.com/topic/200112-php-variable-in-mysql-query/#findComment-1050317 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.