ORDER by DESC question

[All4172]

When I use:

[code]
mysql_query("SELECT * from $db_table ORDER by ID"); }
[/code]

It acts in the normal way and displays the data by ID in ASCENDING order by default like it should.  However I'd like it in descending order.  But when I try:

[code]
mysql_query("SELECT * from $db_table ORDER by ID DESC"); }
[/code]

It still displays it in the ASC order, when I change it to ASC it stays the default way.  Is there something I"m missing or doing wrong here?

[Barand]

Should work.

[code]<?php
$res = mysql_query("SELECT * from $db_table ORDER by ID DESC");
while ($row = mysql_fetch_assoc($res)) {
    echo $row['ID'], '<br/>';
}
?>[/code]

[All4172]

For some reason when I convert that over it gives an error.  Here's my code block but curious as to why my DESC doesn't work?  Everything else displays as normal but the ORDER in DESC mode doesn't seem to happen.

[code]
$result = mysql_query("SELECT * from $db_table ORDER by ID DESC"); }


while($row = mysql_fetch_object($result)) {


        $tmp .= "<tr><td bgcolor=#EEEEFF> $row->date</td><td bgcolor=#EEEEFF> $row->keyword $row->ID</td>";   
        $tmp .= "<td bgcolor=#EEEEFF>Statistics</td>";
        $tmp .= "<td bgcolor=#EEEEFF>Modify</td><td bgcolor=#EEEEFF><a href='/mysql/delete_data.php?&ID={$row->ID}&del=deleted'>Erase</a></td></tr>";   

[/code]

[Barand]

Is ID a simple, auto_incremented integer column?

[All4172]

Yes it is.

[Barand]

Curiouser and curiouser ???

[.josh]

change this:
[code]
$result = mysql_query("SELECT * from $db_table ORDER by ID DESC"); }
[/code]
to this:
[code]
$sql = "SELECT * from $db_table ORDER by ID DESC";
echo $sql;
$result = mysql_query($sql) or die(mysql_error());
[/code]
i doubt you'll get a mysql_error message since you say you are getting results, but hey this already doesn't make sense, so may as well illiminate it for sure.

also, copy/paste the echo'd $sql and try using it directly in phpmyadmin see if it returns the rows in the right order.

[All4172]

Looks like that last example will work if I pull it totally out of the IF statement.  Also the others will work if their condition is met.  I'm now attempting to have a default display of ID in DESC order.

[quote]
if($_GET["sort"]=="A-Z") {

$result = mysql_query("SELECT * from $db_table ORDER BY keyword");}

else if($_GET["sort"]=="Z-A") {

$result = mysql_query("SELECT * from $db_table ORDER BY keyword DESC");}

else if($_GET["sort"]=="DESC") {

$result = mysql_query("SELECT * from $db_table ORDER BY ID DESC");}

else if($_GET["sort"]=="DESC") {

$result = mysql_query("SELECT * from $db_table ORDER BY ID ASC");}

else if ($_GET["keyword"]==$_GET["keyword"]) {

$result = mysql_query("SELECT * FROM joa WHERE keyword LIKE '%$keyword%'");}

else {
$sql = "SELECT * from $db_table ORDER by ID DESC";
$result = mysql_query($sql) or die(mysql_error());}



while($row = mysql_fetch_object($result)) {


        $tmp .= "<tr><td bgcolor=#EEEEFF> $row->date</td><td bgcolor=#EEEEFF> $row->keyword </td>";   
        $tmp .= "<td bgcolor=#EEEEFF>Statistics</td>";
        $tmp .= "<td bgcolor=#EEEEFF>Modify</td><td bgcolor=#EEEEFF><a href='/mysql/delete_data.php?&ID={$row->ID}&del=deleted'>Erase</a></td></tr>";   

    }

} else {
    echo 'could not connect to database : '. mysql_error();
}

print $tmp;
[/quote]

But it appears even though if the link is something like database.php it still goes to ASC mode instead of the default I've set in ELSE. 

[.josh]

okay, if nothing else, your "default" of order by desc will never execute because of your last elseif:

[b]else if ($_GET["keyword"]==$_GET["keyword"]) {[/b]

this will always be true.

2nd, you have 2 seperate elseif's that check for the exact same thing:

[b]else if($_GET["sort"]=="DESC") {[/b]

but each one does something different.  therefore, the second one will never be executed, because if it is true, then the first one would be true, and the first one would be executed instead of any of the other ones.

post your form that you are getting these variables from and explain the options the user has for searching, that is, what you want them to be able to search for, and we can help you build a more efficient structure for building your query.

[All4172]

Thanks for that, let me do some corrections and I'll post the other data if I'm still lost.

[All4172]

I got everything sorted, just one question now though.  What's the best way for me to do the search.  Basically I'm trying to word it so in the address box, it looks like script.php?keyword=$keyword with the $keyword being what the user input into the text box.  Is the following the best way to do that or is there a better way?

[code]

echo '<input type="text" value="" name="keyword" size=25>';
$keyword = $_GET["keyword"];
if (keyword==$keyword) {
$result = mysql_query("SELECT * FROM joa WHERE keyword LIKE '%$keyword%'");}

[/code]

[.josh]

[code]
echo '<input type="text" value="" name="keyword" size=25>';
$keyword = $_GET["keyword"];
if (keyword==$keyword) {
$result = mysql_query("SELECT * FROM joa WHERE keyword LIKE '%$keyword%'");}
[/code]
is this your exact code? cuz if it is, a couple of things to note:

a) i don't understand why you would be putting these two things together. that is, your form and your query.  your form should be in one place and your query stuff should be in some other place, inside some if statement that checks to see if stuff was submitted from the form.

b) in your if statement, you have a typo. you forgot the $. But that doesn't really matter, because there's no reason for your query to be inside that if statement.  if($keyword == $keyword) will always be true, and therefore it will always execute your query. Therefore, there's no reason for it.  I suspect what you really meant to do was this:
[code]
$keyword = $_GET["keyword"];
if (isset($keyword) and trim($keyword != '')) {
  $keyword = mysql_real_escape_string($keyword);
  $result = mysql_query("SELECT * FROM joa WHERE keyword LIKE '%$keyword%'");
}
[/code]
okay i took the liberty to add in some security and error checking here, but the main difference between my if and your if, is that your if checked to see if $keyword equals itself, which it would, obviously.  My if statement checks to see if it is set and also if the user didn't enter in a blank space or something.  The mysql_real_escape_string is a security measure to prevent sql injection from bad people.  you should always sanitize your variables before using them in queries.

as far as your actual query, it looks fine to me, at face value. However, you may want to give an example of what's in your keyword column vs. an example search keyword being used, if you are not getting your desired results.

[All4172]

[quote]i don't understand why you would be putting these two things together.[/quote]

Well what I'm attempting to do is to have various sort options in the same file.  So far I've stored 4.  So basically depending on what the user has after the page (say...database.php) it will do various things.  For instance on the A-Z button, if clicked it'll go to database.php?sort=A-Z and so forth with the other sort options.  Also on the same page i'm listing the entire DB pull.  At the top I put in a search form to search entries.  That's how that keyword=$keyword thing came into play. 

With your example code I was able to figure out what I needed to do.  Here's what I currently have and thus far everything acts how I need it:

[code]
$keyword = $_GET["keyword"];

if($_GET["sort"]=="A-Z") {

$result = mysql_query("SELECT * from $db_table ORDER BY keyword");}

else if($_GET["sort"]=="Z-A") {

$result = mysql_query("SELECT * from $db_table ORDER BY keyword DESC");}

else if($_GET["sort"]=="DESC") {

$result = mysql_query("SELECT * from $db_table ORDER BY ID DESC");}

else if($_GET["sort"]=="ASC") {

$result = mysql_query("SELECT * from $db_table ORDER BY ID ASC");}

else if($_GET["keyword"] != '') {

$result = mysql_query("SELECT * FROM $db_table WHERE keyword LIKE '%$keyword%'");}

else {
$sql = "SELECT * from $db_table ORDER by ID DESC";
$result = mysql_query($sql) or die(mysql_error());}

while($row = mysql_fetch_object($result)) {


        $tmp .= "<tr><td bgcolor=#EEEEFF> $row->date</td><td bgcolor=#EEEEFF> $row->keyword </td>";   
        $tmp .= "<td bgcolor=#EEEEFF>Statistics</td>";
        $tmp .= "<td bgcolor=#EEEEFF>Modify</td><td bgcolor=#EEEEFF><a href='/mysql/delete_data.php?&ID={$row->ID}&del=deleted'>Erase</a></td></tr>";   

    }

} else {
    echo 'could not connect to database : '. mysql_error();
}

print $tmp;

[/code]