Not working, but not getting an error

[alexruimy]

I'm working with a long list of phone extensions, among other things. The page can be viewed [a href=\"http://artelcommunications.net/test/view.php\" target=\"_blank\"]here[/a]. It's fully functional except for the "sort" feature (when you click a column title). It was working two hours ago, but now isn't. I tried echoing my SQL query then pasting it into PHPMyAdmin and I still get no errors. The relevant code is below:

[code]<?
if (!$sort){
$sort = "ASC";
}

if ($sort == "ASC"){
$sort2 = "DESC";
}

if ($sort == "DESC"){
$sort2 = "ASC";
}

$username="admin";
$password="pw";
$database="db";
$heh="site.site.net";

mysql_connect($heh,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

if ($orderby){
$result = mysql_query("SELECT * FROM extensions ORDER BY '$orderby'");
}

if (!$orderby){
$orderby= "ext";
$result = mysql_query("SELECT * FROM extensions ORDER BY '$orderby'");
}

$num=mysql_numrows($result);


?>[/code]
...
[code]
<td class="1"><b><a href="view.php?orderby=ext">Ext</a></b></td>
<td class="1"><b><a href="view.php?orderby=building">Building</a></b></td>
<td class="1"><b><a href="view.php?orderby=type">Phone</a></a></b></td>
<td class="1"><b><a href="view.php?orderby=room">Room</b></td>
<td class="1"><b><a href="view.php?orderby=floor">Floor</a></b></td>
<td class="1"><b><a href="view.php?orderby=elevation">Elevation</a></b></td>
</tr>

<?
$i=0;
$i2 = 1;
while ($i < $num) {

if ($i2 % 2 == 0 ) {
$incl = "class=\"td1\"";
}
else {
$incl = "class=\"td2\"";
}

$ext=mysql_result($result,$i,"ext");
$building=mysql_result($result,$i,"building");
$type=mysql_result($result,$i,"type");
$room=mysql_result($result,$i,"room");
$floor=mysql_result($result,$i,"floor");
$el=mysql_result($result,$i,"elevation");

echo "<tr>
<td $incl>$ext</td>
<td $incl>$building</td>
<td $incl>$type</td>
<td $incl>$room</td>
<td $incl>$floor</td>
<td $incl>$el</td>
</tr>
";

$i++;
$i2++;
}

mysql_close();

?>[/code]

The weird thing is, the sort feature works on the export page. I copied the code verbatim from there and it still won't go. Any help would be greatly appreciated. Thanks.

[ober]

I don't see where you're extracting the "orderby" part from the URL.

You should be using $_GET['orderby'] or $_REQUEST['orderby'] instead of $orderby.

[joquius]

order by `field` what? desc, asc?

[alexruimy]

Yeah, I should probably use a GET but it still works nonetheless; Is something like "view.php?orderby=room" - as I have now - not sufficient or reliable? I'm fairly certain that's not what's causing the problem, though...

[ober]

If the server has been changed to disallow global variables (like if they upgraded PHP, for example) then you can't get the variables you're talking about without using the method I mentioned. And I'm fairly certain that WOULD be the problem.

[alexruimy]

[!--quoteo(post=383780:date=Jun 14 2006, 09:59 AM:name=joquius)--][div class=\'quotetop\']QUOTE(joquius @ Jun 14 2006, 09:59 AM) [snapback]383780[/snapback][/div][div class=\'quotemain\'][!--quotec--]
order by `field` what? desc, asc?
[/quote]

I just changed the query line to
[code]$result = mysql_query("SELECT * FROM extensions ORDER BY '$orderby' $sort");[/code]
and although $sort does exist, it did nothing.


Thanks for the suggestions all. Keep 'em coming.

[ober]

[code]$result = mysql_query("SELECT * FROM extensions ORDER BY '" . $_GET['orderby'] . "' $sort");[/code]

[alexruimy]

Just threw that code in, thanks Ober. No change though.

I feel like I'm missing something really stupid.

[AndyB]

As a debugging aid, echo the query ahead of the table display. At least then you will know for certain just what query is being run - and it may not be the query that you expect.

[alexruimy]

Just for reference, here's my export.php file, where the sort currently is working.

[code]<?
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=extensions.xls");
header("Pragma: no-cache");
header("Expires: 0");

$tab = "\t";
$cr = "\n";

$username="asdf";
$password="sdfsdf";
$database="asdfsdfcom";
$host="ssdf.net";

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$data = "Extension" . $tab . "Building" . $tab . "Line" . $tab . "Room" . $tab . "Floor" . $tab . "Elevation" . $cr;

   $result = mysql_query("SELECT * FROM extensions ORDER BY '$orderby'");

   while($row = mysql_fetch_array($result)) {
   $data .= $row[ext] . $tab . $row[building] . $tab .  $row[type] . $tab . $row[room] . $tab .  $row[floor] . $tab . $row[elevation] .$cr;
             }
mysql_close();
echo $data;

     ?>[/code]

[!--quoteo(post=383797:date=Jun 14 2006, 10:11 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jun 14 2006, 10:11 AM) [snapback]383797[/snapback][/div][div class=\'quotemain\'][!--quotec--]
As a debugging aid, echo the query ahead of the table display. At least then you will know for certain just what query is being run - and it may not be the query that you expect.1
[/quote]
Good idea. I had done that initially, and it looked right, but now $query no longer exists, because I'm using
$result = mysql_query("SELECT * FROM extensions ORDER BY '" . $_GET['orderby'] . "' $sort");
in lieu of it.

[!--quoteo(post=383797:date=Jun 14 2006, 10:15 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jun 14 2006, 10:15 AM) [snapback]383797[/snapback][/div][div class=\'quotemain\'][!--quotec--]
$num=mysql_numrows($result);

change to

$num=mysql_num_rows($result);
[/quote]

Done.

[joquius]

sorry there were some multiple posts of mine I deleted every post though hmm. anyway
in the first piece of code

$num=mysql_numrows($result);

change to

$num=mysql_num_rows($result);

never neglect the little things

[ober]

I'd suggest splitting it out so you can echo the query. Rule #1 of debugging, always keep your query outside of the mysql_query function so you can echo it.

[alexruimy]

[!--quoteo(post=383797:date=Jun 14 2006, 10:11 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jun 14 2006, 10:11 AM) [snapback]383797[/snapback][/div][div class=\'quotemain\'][!--quotec--]
As a debugging aid, echo the query ahead of the table display. At least then you will know for certain just what query is being run - and it may not be the query that you expect.
[/quote]
Good idea. I had done that initially, and it looked right, but now $query no longer exists, because I'm using
$result = mysql_query("SELECT * FROM extensions ORDER BY '" . $_GET['orderby'] . "' $sort");
in lieu of it.

[joquius]

ehhhhh....ORDER BY `$field` DESC not ORDER BY '$field' DESC it's a field not a value

[alexruimy]

Okay, if you go back to the page, I set the sqlquery to call the $query variable :

[code]if (!$orderby){
$query = "SELECT * FROM extensions ORDER BY 'ext' $sort";
$result = mysql_query($query);
echo "!, $query";
}

if ($orderby){
$orderby= "ext";
$query = "SELECT * FROM extensions ORDER BY '" . $_GET['orderby']. "' $sort";
$result = mysql_query($query);
echo "$query";
}[/code]

[joquius]

"SELECT * FROM extensions ORDER BY '" . $_GET['orderby']. "' $sort";

should be

"SELECT * FROM extensions ORDER BY `" . $_GET['orderby']. "` $sort";

[alexruimy]

Done. And did nothing. :(

I appreciate everyone's help on this. I wish this stupid junk was easier.

[joquius]

where are you getting $orderby from in export.php?

[alexruimy]

The URL. I haven't cleaned that page up yet. I suppose it is prone to errors..

[AndyB]

[!--quoteo(post=383828:date=Jun 14 2006, 10:40 AM:name=alexruimy)--][div class=\'quotetop\']QUOTE(alexruimy @ Jun 14 2006, 10:40 AM) [snapback]383828[/snapback][/div][div class=\'quotemain\'][!--quotec--]
The URL. I haven't cleaned that page up yet. I suppose it is prone to errors..
[/quote]
Why should it be? Since the parameter you want is passed by URL, retrieve it (properly) by using syntax like:
[code]$orderby = $_GET['orderby'];[/code]

Unless and until you actually echo the query along with the results table, you'll never be sure that you really have the query you expect. "Did nothing" suggests to me that there's an error in your code logic and/or what's online is different from the version you're working with.

Sort out retrieving $orderby, echo the query, add sql error trapping, upload to the site and let's see what's really happening.

[alexruimy]

Right, I am echoing the query on the page. [a href=\"http://artelcommunications.net/test/view.php\" target=\"_blank\"]http://artelcommunications.net/test/view.php[/a]

What's SQL Error Trapping?

Thanks again.

[AndyB]

error trapping example...

[code]$result = mysql_query($query) or die("error: ". mysql_error(). " with query ". $query); // that helps[/code]

OK, I see that the echo'd query changes properly and that the displayed data never changes regardless of what 'order' I wanted. That looks as though the data are being displayed without regard to the echo'd query - which makes me wonder if there's another query (without any sort instruction) being used to actually retrieve the data for display.

I'm not sure you've posted the relevant part of your code - all the way from the query to actually displaying the tabular data. If you have, aplogies; if you haven't can we see?

[alexruimy]

[!--quoteo(post=383845:date=Jun 14 2006, 11:05 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jun 14 2006, 11:05 AM) [snapback]383845[/snapback][/div][div class=\'quotemain\'][!--quotec--]
error trapping example...

[code]$result = mysql_query($query) or die("error: ". mysql_error(). " with query ". $query); // that helps[/code]

OK, I see that the echo'd query changes properly and that the displayed data never changes regardless of what 'order' I wanted. That looks as though the data are being displayed without regard to the echo'd query - which makes me wonder if there's another query (without any sort instruction) being used to actually retrieve the data for display.

I'm not sure you've posted the relevant part of your code - all the way from the query to actually displaying the tabular data. If you have, aplogies; if you haven't can we see?
[/quote]
If I didn't post the relevant part, I'm gonna feel like an idiot :-P.

Anyway, here's the entire page:

[code]<?
if (!$sort){
$sort = "ASC";
}

if ($sort == "ASC"){
$sort2 = "DESC";
}

if ($sort == "DESC"){
$sort2 = "ASC";
}

$username="sdf";
$password="sdfdfsd";
$database="sdfsdf";
$heh="sdfsdf";

mysql_connect($heh,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


if (!$orderby){
$query = "SELECT * FROM extensions ORDER BY `ext` $sort";
$result = mysql_query($query);
}

if ($orderby){
$orderby= "ext";
$query = "SELECT * FROM extensions ORDER BY `" . $_GET['orderby']. "` $sort";
$result = mysql_query($query);

}

$num=mysql_num_rows($result);
echo "$query";

?>
<html>
<head>
<title>NYC DEP Extensions Chart</title>
<style>
body {background-color:black;}
* {font-family: verdana, arial, sans-serif; font-size:12px; color:white;}
.hover {font-weight:bold; font-size:14px;}
a:hover {background-color:9999ff; color:black;}
.heading {font-size:16px; font-weight:bold; text-align:center;}
.td1 {background-color:#808080;}
.td2 {background-color:#636363;}
.hidden {display:none;}
</style>
<script type="text/javascript">
<!--
function hide( id, id2 )
{
document.getElementById(id).style.display = 'block';
document.getElementById(id2).style.display = 'none';
}
//-->
</script>
</head>
<body>

<table width="80%" align="center" cellspacing="0">
<tr>
<td class="heading" colspan="6">NYC DEP Phone Extensions</td>
</tr>
<tr>
<td colspan="6">
<div class="hidden" id="stats"><? include("stats.php"); ?>
<div onclick="hide('show','stats');"><a>(Hide Stats)</div></a></div>
<div onclick="hide('stats','show');" id="show"><a>(Show Stats)</a></div></td>
</tr>
<tr>
<td colspan="6" align="center"><br>
<a href="export.php?orderby=<? echo $orderby; ?>">Export to Excel</a></td>
</tr>
<tr>
<td class="1"><b><a href="view.php?orderby=ext">Ext</a></b></td>
<td class="1"><b><a href="view.php?orderby=building">Building</a></b></td>
<td class="1"><b><a href="view.php?orderby=type">Phone</a></a></b></td>
<td class="1"><b><a href="view.php?orderby=room">Room</b></td>
<td class="1"><b><a href="view.php?orderby=floor">Floor</a></b></td>
<td class="1"><b><a href="view.php?orderby=elevation">Elevation</a></b></td>
</tr>

<?
$i=0;
$i2 = 1;
while ($i < $num) {

if ($i2 % 2 == 0 ) {
$incl = "class=\"td1\"";
}
else {
$incl = "class=\"td2\"";
}

$ext=mysql_result($result,$i,"ext");
$building=mysql_result($result,$i,"building");
$type=mysql_result($result,$i,"type");
$room=mysql_result($result,$i,"room");
$floor=mysql_result($result,$i,"floor");
$el=mysql_result($result,$i,"elevation");

echo "<tr>
<td $incl>$ext</td>
<td $incl>$building</td>
<td $incl>$type</td>
<td $incl>$room</td>
<td $incl>$floor</td>
<td $incl>$el</td>
</tr>
";

$i++;
$i2++;
}

mysql_close();

?>
</table>
</body>
</html>[/code]

[joquius]

if (!$orderby){
$query = "SELECT * FROM extensions ORDER BY `ext` $sort";
$result = mysql_query($query);
}

if ($orderby){
$orderby= "ext";
$query = "SELECT * FROM extensions ORDER BY `" . $_GET['orderby']. "` $sort";
$result = mysql_query($query);

}

change this to

if (!isset ($_GET['orderby'])){
$query = "SELECT * FROM extensions ORDER BY `ext` $sort";
$result = mysql_query($query);
}

if (isset ($_GET['orderby'])){
$query = "SELECT * FROM extensions ORDER BY `" . $_GET['orderby']. "` $sort";
$result = mysql_query($query);

}

what am I saying

$orderby = (isset ($_GET['orderby'])) ? $_GET['orderby'] : "ext";
$query = "SELECT * FROM extensions ORDER BY `$orderby` $sort";
$result = mysql_query($query);

[alexruimy]

Okay, I was playing around with it and I got it. It had to do, I believe, with my including the "stats.php" file. Here's the corrected code, also available at [a href=\"http://artelcommunications.net/test/view2.php\" target=\"_blank\"]http://artelcommunications.net/test/view2.php[/a]


[code]<html>
<head>
<title>NYC DEP Extensions Chart</title>
<style>
body {background-color:black;}
* {font-family: verdana, arial, sans-serif; font-size:12px; color:white;}
.hover {font-weight:bold; font-size:14px;}
a:hover {background-color:9999ff; color:black;}
.heading {font-size:16px; font-weight:bold; text-align:center;}
.td1 {background-color:#808080;}
.td2 {background-color:#636363;}
.hidden {display:none;}
</style>
<script type="text/javascript">
<!--
function hide( id, id2 )
{
document.getElementById(id).style.display = 'block';
document.getElementById(id2).style.display = 'none';
}
//-->
</script>
</head>
<body>

<table width="80%" align="center" cellspacing="0">
<tr>
<td class="heading" colspan="6">NYC DEP Phone Extensions</td>
</tr>
<tr>
<td colspan="6">
<div class="hidden" id="stats"><? include("stats2.php"); ?>
<div onclick="hide('show','stats');"><a>(Hide Stats)</div></a></div>
<div onclick="hide('stats','show');" id="show"><a>(Show Stats)</a></div></td>
</tr>
<tr>
<td colspan="6" align="center"><br>
<a href="export.php?orderby=<? echo $orderby; ?>">Export to Excel</a></td>
</tr>
<tr>
<td class="1"><b><a href="view2.php?orderby=ext">Ext</a></b></td>
<td class="1"><b><a href="view2.php?orderby=building">Building</a></b></td>
<td class="1"><b><a href="view2.php?orderby=type">Phone</a></a></b></td>
<td class="1"><b><a href="view2.php?orderby=room">Room</b></td>
<td class="1"><b><a href="view2.php?orderby=floor">Floor</a></b></td>
<td class="1"><b><a href="view2.php?orderby=elevation">Elevation</a></b></td>
</tr>
<?
if (!$sort){
$sort = "ASC";
}

if ($sort == "ASC"){
$sort2 = "DESC";
}

if ($sort == "DESC"){
$sort2 = "ASC";
}

$username="52542m";
$password="atsfsdfm";
$database="sdfsdfdf";
$heh="asdfsdfict";

mysql_connect($heh,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


if (!$orderby){
$query = "SELECT * FROM extensions ORDER BY `ext` $sort";
$result = mysql_query($query);
}

if ($orderby){
$orderby= "ext";
$query = "SELECT * FROM extensions ORDER BY `" . $_GET['orderby']. "` $sort";
$result = mysql_query($query);

}

$num=mysql_num_rows($result);
echo "$query";

?>
<?
$i=0;
$i2 = 1;
while ($i < $num) {

if ($i2 % 2 == 0 ) {
$incl = "class=\"td1\"";
}
else {
$incl = "class=\"td2\"";
}

$ext=mysql_result($result,$i,"ext");
$building=mysql_result($result,$i,"building");
$type=mysql_result($result,$i,"type");
$room=mysql_result($result,$i,"room");
$floor=mysql_result($result,$i,"floor");
$el=mysql_result($result,$i,"elevation");

echo "<tr>
<td $incl>$ext</td>
<td $incl>$building</td>
<td $incl>$type</td>
<td $incl>$room</td>
<td $incl>$floor</td>
<td $incl>$el</td>
</tr>
";

$i++;
$i2++;
}

mysql_close();

?>
</table>
</body>
</html>[/code]

Thanks all for your help.