Sorting 2 columns in mysql with php

by 3 replies
4
I'm trying to sort a table by 2 colums, ie: id and date or id and name. and have it work by clickable link/dropdown menu, either is fine. I just need to know what's wrong with this code so I can get it running right. From what I've seen, to get a table to sort from 2 columns you do
$sql = "SELECT * FROM $tbl_name col1 asc, col2";
Thanks in advance!


// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// select record from mysql
$sql = "SELECT * FROM $tbl_name";

if ($_GET['sort'] == 'id')
{
$sql .= " ORDER BY id";
}
elseif ($_GET['sort'] == 'date')
{
$sql .= " ORDER BY id, date";
}
elseif ($_GET['sort'] == 'referrer')
{
$sql .= " ORDER BY id, referrer";
}
elseif($_GET['sort'] == 'first_name')
{
$sql .= " ORDER BY id, first_name";
}
elseif ($_GET['sort'] == 'email')
{
$sql .= " ORDER BY id, email";
}
elseif($_GET['sort'] == 'phone')
{
$sql .= " ORDER BY id, phone";
}
elseif($_GET['sort'] == 'vehicle_type_id1')
{
$sql .= " ORDER BY id, vehicle_type_id1";
}

$result=mysql_query($sql);
?>



<tr>
<td width="60" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=id">ID</a></strong></td>
<td width="174" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=date">Date Entered</a></strong></td>
<td width="190" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=referrer">Referring Site</a></strong></td>
<td width="250" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=first_name">Name</a></strong></td>
<td width="258" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=email">Email Address</a></strong></td>
<td width="150" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=phone">Phone Number</a></strong></td>
<td width="120" align="center" bgcolor="#FFFFFF"><strong><a href="data2.php?sort=vehicle_type_id1">Vehicle Type</a></strong></td>
<td width="130" align="center" bgcolor="#FFFFFF">&nbsp;</td>
</tr>
#programming #columns #mysql #php #sorting
  • ok it appears my issue was having id in front, that should be done as a secondary it looks like. I'll have to play with it more
    • [1] reply
    • Exactly... "order by date" instead of "order by id, date" ... the way you had it, it would order by ID (which I assume are unique and auto incremented anyway) and would have only then sorted by date if the ID was a tie from the two records.
      • [ 1 ] Thanks
  • Banned
    try codeigniter,they have active record
    • [ 1 ] Thanks
Join the discussion

Next Topics on Trending Feed

  • 4

    Sorting 2 columns in mysql with php

    I'm trying to sort a table by 2 colums, ie: id and date or id and name. and have it work by clickable link/dropdown menu, either is fine. I just need to know what's wrong with this code so I can get it running right. From what I've seen, to get a table to sort from 2 columns you do $sql = "SELECT * FROM $tbl_name col1 asc, col2"; Thanks in advance!