[SOLVED] Simple MYSQL query works in phpmyadmin but NOT on webpage??

[yandoo]

Hi there,

 

Please help im flumuxed!

 

select * from guest_book order by date desc 

 

It works fine when i tried the code in phpmyadmin but dosnt on the page!!!!!

 

When i say it dosnt work what i mean is it doesnt order the records by date either ASC or DESC on the webpage....

 

What the hells going on????

 

Please help

 

 

Heres the code for my code for guestbook page

connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword)
{
global $link;
$link=mysql_connect ("$servername","$dbuser","$dbpassword");
if(!$link){die("Could not connect to MySQL");}
mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
}



					  
					  error_reporting(E_ALL);
ini_set('display_errors', '1'); 

    $limit          = 4;               
    $query_count    = "SELECT count(*) FROM guest_book";    
    $result_count   = mysql_query($query_count);    
    $totalrows      = mysql_result($result_count, 0, 0); 

    if(empty($page)){
        $page = 1;
    }
        

    $limitvalue = $page * $limit - ($limit); 
    $query  = "SELECT * FROM guest_book LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error()); 
?>
   

   <p align="center"><span class="style6">Welcome to the Lopes Arms guestbook</span> </p>
   <p align="center">We invite you to comment on your experiences and hope to see you soon. </p>
   <p align="center"> Please click the link to post your comment: <a href='entry.html'>Post Comment</a>    </p>
   <p align="center">
     <?php					
$query=mysql_query("select * from guest_book order by date asc");
echo "<table border='0' class=\"bot\" width='630' cellspacing='0' cellpadding='0' align=center >";
?>
               </p>
		   <span class="style1">
   <?php
//while($nt = mysql_fetch_array($result)){

while($nt=mysql_fetch_array($result)){ $dt=date("m/d/y",$nt['date']); // formating the date 
$message=nl2br($nt['message']); // this will change  the line breaks to html line breaks
echo "<tr bgcolor= #F8F8F8><td width='20'> <font face=\"Verdana, arial\" size=\"1\" color=\"#505050\"><b>Name:</b></font></td><td width='280'class=\"style1\"><font face=\"Verdana, arial\" size=\"2\">$nt[name]</font></td></tr>";
echo "<tr bgcolor= #F8F8F8><td width='30'class=\"style1\"><font face=\"Verdana, arial\" size=\"1\" color=\"#505050\"><b>Email:</b></font></td><td width='150' class=\"style1\"> <font face=\"Verdana, arial\" size=\"2\"> $nt[email]</font></td></tr>";
echo "<tr bgcolor= #F8F8F8><td width='20' class=\"style1\"><font face=\"Verdana, arial\" size=\"1\" color=\"#505050\"><b>Web:</b></font></td><td width='70'class=\"style1\"><font face=\"Verdana, arial\" size=\"2\"> $nt[url]</font></td></tr>";
echo "<tr bgcolor= #F8F8F8><td width='30'class=\"style1\"><font face=\"Verdana, arial\" size=\"1\" color=\"#505050\"><b>Date:</b></font></td><td width='150'class=\"style1\"><font face=\"Verdana, arial\" size=\"2\">$dt</font></td></tr>";
echo "<br>";
echo"<tr><td colspan=4</td></tr>";
echo "<tr><td colspan=4></td></tr>";
echo "<tr bgcolor= #eeeeee><td colspan=4 class=\"style1\"> <font face=\"Verdana, arial\" size=\"2\"> $message </font></td></tr>";
echo "<tr><td colspan=4></td></tr>";
echo "</tr> \n";
echo "<table border='0' class=\"bot\" width='630' cellspacing='0' cellpadding='0' align=center >";


}

echo "</table>";
echo "<br>";
echo"<center>";
echo"<font size=2>";
$pageprev = $page-1;

    if($page != 1){ 
        echo("<a href=\"$PHP_SELF?page=$pageprev\">PREV</a> "); 
    }else{
        echo("PREV"." ");
    }

    //$numofpages =  $limit / $totalrows;          <-- change this line
    $numofpages =  ceil($totalrows /$limit);

    for($i = 1; $i <= $numofpages; $i++){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
        }
    }


/* remove this part
    if(( $limit % $totalrows) != 0){
        if($i == $page){
            echo($i." ");
        }else{
            echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
        }
    }
*/
$pagenext = $page+1;

//$new = ($limit * $page) - $totalrows; <-- and remove this part
//echo"$new";

    if($page < $numofpages) {
        echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>");
    }else{
        echo("NEXT"); 
    }
    echo"</font>";
    mysql_free_result($result);
?>

 

 

Any help would be gr8ly appreciated

 

Thank you

 

[trq]

date is a reserevd word, try...

 

select * from guest_book order by `date` desc

[yandoo]

Hi

 

firstly thank you for the reply its appreciated!

 

ive tried `date` and it has no effect to the records...they still are displayed with no order????

 

What else can i try??

 

Thanks

[yandoo]

Does it have anything to do with the date format being : m/d/y???

 

I tried changing to d/m/y and still no change!

 

thanks

[AndyB]

ive tried `date` and it has no effect to the records...they still are displayed with no order????

 

I'd bet they are displayed in order, but not the order you expected.  Using any date format other than the ISO standard (yyyy-mm-dd) is NOT going to produce what you think of as ordered dates, i.e from earliest to latest or v/v.  Is March 1, 2007 before or after January 3, 2007? Is 2007-03-01 earlier or later than 2007-01-03 (yes). Is 3/1/2007 more or less than 1/3/2007?

[yandoo]

Hi there,

 

but when i use query in phpmyadmin

 

 

select * from guest_book order by date asc

 

the results are ordered as expected

 

thanks

[PFMaBiSmAd]

BTW: date is a data type, not a reserved keyword. It only has special meaning in a create or alter table statement. It is permitted to be used as a table or column name without any special treatment.

[yandoo]

Hi,

 

I tried ordering thre recods by the entry_id....again it works fine in phpmyadmin but not on the webpage itself??????

 

thanks

[~n[EO]n~]

I am not too sure about this but I see you selecting from guestbook. First is this

$query  = "SELECT * FROM guest_book LIMIT $limitvalue, $limit";  
$result = mysql_query($query) or die("Error: " . mysql_error());

and second is this

$query=mysql_query("select * from guest_book order by date asc");

and you have stored the output in $result variable below your first query. I am not too sure but there maybe a conflict...

 

[yandoo]

Hi,

 

the first line is used to limit the number of records of per page and use pagntation with it.

 

The second is just to display and order the records

 

thanks

[yandoo]

Could it have something to do with the date field being a Varchar?????

 

 

but again cant seehow it works on phphmyadmin and not on webpage!

 

thanks

[yandoo]

Hi there,

 

you are right they are conflicting!!!!

 

i have removed the line of code:

 

$query=mysql_query("select * from guest_book order by date asc");

 

And changed this 1 one above too:

 

$query  = "SELECT * FROM guest_book ORDER BY date desc LIMIT $limitvalue, $limit"; 

$result = mysql_query($query) or die("Error: " . mysql_error());

 

thank you