Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : mySQL - ORDER BY

[NickMahon]

PHP Code:

if($alpha==""){
    mysql_query("SELECT name FROM album_collection ORDER BY name");
} elseif($alpha=="name"){
    mysql_query("SELECT name FROM album_collection ORDER BY name");
} elseif($alpha=="artist"){
    mysql_query("SELECT artist FROM album_collection ORDER BY artist");
} elseif($alpha=="rating"){
    mysql_query("SELECT rating FROM album_collection ORDER BY rating");
} 


Is there something wrong with this? It has no effect whatsoever.

[Alturus]

Add some conditions at the end

SELECT blah FROM blah ORDER BY blah DESC

or

SELECT blah FROM blah ORDER BY blah ASC

[NickMahon]

Still no luck.

Here is the full code:

PHP Code:

<?
include("dbconnect.inc");
$result = mysql_query("SELECT name, artist, comments, rating FROM album_collection", $link_id);
if($alpha==""){
    mysql_query("SELECT name FROM album_collection ORDER BY name ASC");
} elseif($alpha=="name"){
    mysql_query("SELECT name FROM album_collection ORDER BY name ASC");
} elseif($alpha=="artist"){
    mysql_query("SELECT artist FROM album_collection ORDER BY artist ASC");
} elseif($alpha=="rating"){
    mysql_query("SELECT rating FROM album_collection ORDER BY rating ASC");
}
print("<table align=\"center\" border=\"0\" cellspacing=\"1\" cellpadding=\"4\">");
print("<tr align=\"center\">");
print("<td class=\"headerrow\" nowrap><strong><a href=\"?alpha=name\">Album Name</a></strong></td>");
print("<td class=\"headerrow\" nowrap><strong><a href=\"?alpha=artist\">Artist</a></strong></td>");
print("<td class=\"headerrow\" nowrap><strong><a href=\"?alpha=rating\">Rating</a></strong></td>");
print("</tr>");
$counter = 0;
while($query_data = mysql_fetch_row($result)) {
    $counter++;
    if($counter%2==0) {
        $rowclass = row2;
    } else {
        $rowclass = row1;
    }
    print("<tr align=\"center\">");
    print("<td class=\"$rowclass\" nowrap>$query_data[0]</td>");
    print("<td class=\"$rowclass\" nowrap>$query_data[1]</td>");
    print("<td class=\"$rowclass\" nowrap><img src=\"$query_data[3].gif\"></td>");
    print("</tr>");
    if($query_data[2]!="N/A") {
        print("<tr align=\"center\">");
        print("<td class=\"$rowclass\" colspan=\"3\"><b>Comments:</b> $query_data[2]</td>");
        print("</tr>");
    }
}
print("</table>");
?>

[Alturus]

Well, try using SELECT * FROM

Because as it stands you seem to be only selecting one bit of info:

SELECT whatever FROM...

Only selects 'whatever' not all the data.

[Alturus]

Oh oh, try this:

PHP Code:

<?
include("dbconnect.inc");

if($alpha==""){
   $sort = "ORDER BY name ASC";
} elseif($alpha=="name"){
   $sort = "ORDER BY name DESC";
} elseif($alpha=="artist"){
   $sort = "ORDER BY artist ASC";
} elseif($alpha=="rating"){
   $sort = "ORDER BY rating ASC";
}

$result = mysql_query("SELECT name, artist, comments, rating FROM album_collection $sort", $link_id);

print("<table align=\"center\" border=\"0\" cellspacing=\"1\" cellpadding=\"4\">");
print("<tr align=\"center\">");
print("<td class=\"headerrow\" nowrap><strong><a href=\"?alpha=name\">Album Name</a></strong></td>");
print("<td class=\"headerrow\" nowrap><strong><a href=\"?alpha=artist\">Artist</a></strong></td>");
print("<td class=\"headerrow\" nowrap><strong><a href=\"?alpha=rating\">Rating</a></strong></td>");
print("</tr>");
$counter = 0;
while($query_data = mysql_fetch_row($result)) {
    $counter++;
    if($counter%2==0) {
        $rowclass = row2;
    } else {
        $rowclass = row1;
    }
    print("<tr align=\"center\">");
    print("<td class=\"$rowclass\" nowrap>$query_data[0]</td>");
    print("<td class=\"$rowclass\" nowrap>$query_data[1]</td>");
    print("<td class=\"$rowclass\" nowrap><img src=\"$query_data[3].gif\"></td>");
    print("</tr>");
    if($query_data[2]!="N/A") {
        print("<tr align=\"center\">");
        print("<td class=\"$rowclass\" colspan=\"3\"><b>Comments:</b> $query_data[2]</td>");
        print("</tr>");
    }
}
print("</table>");
?>

I use basically the same sort process at http://supratest.ue3.net

PHP Code:

if($sort == 'user_desc'){
$sql_sort = 'ORDER BY username DESC';
}
elseif($sort == 'user_asc'){
$sql_sort = 'ORDER BY username ASC';
}
elseif($sort == 'date_asc'){
$sql_sort = 'ORDER BY id ASC';
}
elseif($sort == 'date_desc'){
$sql_sort = 'ORDER BY id DESC';
}
elseif($sort == 'car_asc'){
$sql_sort = 'ORDER BY supra ASC';
}
elseif($sort == 'car_desc'){
$sql_sort = 'ORDER BY supra DESC';
}
elseif($sort == 'votes_asc'){
$sql_sort = 'ORDER BY votes ASC';
}
elseif($sort == 'votes_desc'){
$sql_sort = 'ORDER BY votes DESC';
}
else{
$sql_sort = 'ORDER BY supra DESC';
}

then further past a bunch of crap:

$sql = "SELECT * FROM accounts $sql_sort LIMIT 100";
$result = mysql_query($sql) or die ("Invalid Query");
while($myrow = mysql_fetch_array($result)){


etc etc etc 


[NickMahon]

Well, I want the user to be able to click on a link on view.php such as "Album Name" - this will take them to view.php?alpha=name, thus executing

PHP Code:

} elseif($alpha=="name"){
    mysql_query("SELECT name FROM album_collection ORDER BY name ASC");
} 


and ordering the field alphabetically.

[NickMahon]

Thanks

[RackNine]

mysql_query("SELECT name FROM album_collection ORDER BY name ASC");

Shouldn't the above be stored into a result object? ie:

$result = mysql_query("SELECT name FROM album_collection ORDER BY name ASC");

[Alturus]

PHP Code:

$result = mysql_query("SELECT name, artist, comments, rating FROM album_collection", $link_id); 


You've already selected your info here, no need to do it again, it won't work. What you need to do is append a sorting condition on this statement, like:

PHP Code:

$result = mysql_query("SELECT name, artist, comments, rating FROM album_collection ORDER BY name DESC", $link_id); 


[NickMahon]

Yup, worked great.

[drsenay]

Originally posted by NickMahon

PHP Code:

if($alpha==""){
    mysql_query("SELECT name FROM album_collection ORDER BY name");
} elseif($alpha=="name"){
    mysql_query("SELECT name FROM album_collection ORDER BY name");
} elseif($alpha=="artist"){
    mysql_query("SELECT artist FROM album_collection ORDER BY artist");
} elseif($alpha=="rating"){
    mysql_query("SELECT rating FROM album_collection ORDER BY rating");
} 


Is there something wrong with this? It has no effect whatsoever.

It will work faster and simpler if you do this:

PHP Code:

switch ($alpha){
    case "":
    case "name":
        $sql = "SELECT name FROM album_collection ORDER BY name";
    break;
    case "artist":
        $sql = "SELECT artist FROM album_collection ORDER BY artist";
    break;
    case "rating":
        $sql = "SELECT artist FROM album_collection ORDER BY rating";
    break;
}

mysql_query($sql); 


and so on... I think you'll find this works better....

[Chr1s]

If your running PHP4 you also might have to declare your $alpha variable:

PHP Code:

$alpha = $HTTP_GET_VARS['alpha']; 


[NickMahon]

Thanks everyone, but this is already solved