php variable in MYSQL query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Peuplarchie
Forum Contributor
Posts: 148
Joined: Sat Feb 04, 2006 10:49 pm

php variable in MYSQL query

Post by Peuplarchie »

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 (

Code: Select all

$result = mysql_query("SELECT * FROM NHL_GBG_PLAYERS ORDER BY {$col}");
)

Here is my code :

Code: Select all

<?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!
dmsundar
Forum Newbie
Posts: 5
Joined: Thu Apr 22, 2010 7:01 am

Re: php variable in MYSQL query

Post by dmsundar »

hey...

$result=@mysql_query("Select * FROM NHL_GBG_PLAYERS ORDER BY " . $col ) ;




_____________________________________________________________________________
The only way out is always through :)
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: php variable in MYSQL query

Post by flying_circus »

This is a terrible suggestion. Just becuse you supress the error message does not mean you fixed the error.
dmsundar wrote:$result=@mysql_query("Select * FROM NHL_GBG_PLAYERS ORDER BY " . $col ) ;


Try something like this:

Code: Select all

<?php
  # Declare Variables
    $valid_columns = array();
    
  # Connect to MySql Server
    $con = mysql_connect("localhost", "sports", "sports");
    
    if(!$con)
      die('Could not connect: ' . mysql_error());
      
  # Select Database
    mysql_select_db("sports", $con);
    
  # Fetch List of Column Names
    $querystring = "SHOW COLUMNS FROM `players` FROM `sports`;";
    $columns = mysql_query($querystring, $con);
    
    if(mysql_num_rows($columns)) {
      while($column = mysql_fetch_assoc($columns)) {
        $valid_columns[] = $column['Field'];
      }
    } else {
      exit("Could not fetch column names of table 'players'.");
    }
    
  # Fetch Navigation Variables
    $col = isset($_POST['col']) ? $_POST['col'] : '';
    
    if(!empty($col)) {
      if(!in_array($col, $valid_columns)) {
      # User Supplied Column Name is invalid.  Set Default
        $col = $valid_columns[0];
      }
    } else {
    # User Supplied Column Name is empty.  Set Default
      $col = $valid_columns[0];
    }
    
  # Prepare $col for use within a database query
    $col = mysql_real_escape_string($col, $con);
    
  # Fetch Player Data sorted by User Supplied $col
    $result = mysql_query("SELECT * FROM `players` ORDER BY `{$col}`;");
    
  # Verify Results
    if(mysql_num_rows($result)) {
      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 Players Found in the Database";
    }
    
  # Close MySql Connection
    mysql_close($con);
?>
Post Reply