Link to sort table column in PHP MYSQL

Question

<?php
$con = mysql_connect ("localhost", "user", "pass") 
  or die ('Error: ' . mysql_error());
mysql_select_db ("members");

if(isset($_GET['orderby'])){ 
$order = $_GET['orderby']; 
$result = "SELECT * FROM persons ORDER BY ".mysql_real_escape_string($order)." DESC"; 
}
else{
$result = mysql_query("select * from persons");
}

$num_rows = mysql_num_rows($result);
$row_counter = 0; 

echo "<table width=600 border=0 cellspacing=0>\n";
echo "<tr>\n
      <th>&nbsp;</th>\n
      <th>First Name</th>\n
      <th>Last Name</th>\n
      <th>Email Address</th>\n
      <th>City</th>\n
      <th>State</th>\n
      <th><a href='index.php?orderby=submitDate'>Date</a></th>\n
      </tr>";

while($row = mysql_fetch_array($result)){
  if($row_counter % 2){
    $row_color="bgcolor='#FFFFFF'";
  }
  else{
    $row_color="bgcolor='#F3F6F8'";
  }
  echo "<tr ".$row_color.">";
  echo "<td class='id'>" . $row['id'] . "</td>\n";
  echo "<td>" . $row['firstName'] . "</td>\n";
  echo "<td>" . $row['lastName'] . "</td>\n";
  echo "<td>" . $row['email'] . "</td>\n";
  echo "<td>" . $row['city'] . "</td>\n";
  echo "<td>" . $row['state'] . "</td>\n";
  echo "<td>" . $row['submitDate'] . "</td>\n";
  echo "</tr>";
  $row_counter++;
  }
echo "</table>";

mysql_close($con);
?

>

I just cannot figure out why my link to sort my query is not working. any ideas? Pre-Thanks!

Please note that this code suffering from SQL injection badly. — Your Common Sense
– Your Common Sense, Commented Feb 27, 2011 at 8:40

amitchd · Accepted Answer · 2011-02-27 07:43:40Z

2

Your if else block was wrong. In one case $result is a query in another it is a result-set. Check code below.. fixed.

<?php
$con = mysql_connect ("localhost", "user", "pass") 
  or die ('Error: ' . mysql_error());
mysql_select_db ("members");

if(isset($_GET['orderby'])){ 
$order = $_GET['orderby']; 
$sql = "SELECT * FROM persons ORDER BY ".mysql_real_escape_string($order)." DESC"; 
}
else{
$sql = "select * from persons";
}
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
$row_counter = 0; 

echo "<table width=600 border=0 cellspacing=0>\n";
echo "<tr>\n
      <th>&nbsp;</th>\n
      <th>First Name</th>\n
      <th>Last Name</th>\n
      <th>Email Address</th>\n
      <th>City</th>\n
      <th>State</th>\n
      <th><a href='index.php?orderby=submitDate'>Date</a></th>\n
      </tr>";

while($row = mysql_fetch_array($result)){
  if($row_counter % 2){
    $row_color="bgcolor='#FFFFFF'";
  }
  else{
    $row_color="bgcolor='#F3F6F8'";
  }
  echo "<tr ".$row_color.">";
  echo "<td class='id'>" . $row['id'] . "</td>\n";
  echo "<td>" . $row['firstName'] . "</td>\n";
  echo "<td>" . $row['lastName'] . "</td>\n";
  echo "<td>" . $row['email'] . "</td>\n";
  echo "<td>" . $row['city'] . "</td>\n";
  echo "<td>" . $row['state'] . "</td>\n";
  echo "<td>" . $row['submitDate'] . "</td>\n";
  echo "</tr>";
  $row_counter++;
  }
echo "</table>";

mysql_close($con);
?>

answered Feb 27, 2011 at 7:43

amitchd

9755 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Over a year ago

!! Thank you !! I am a beginner as you can tell :p I appreciate it much!

Collectives™ on Stack Overflow

Link to sort table column in PHP MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related