[SOLVED] Table Sorting

[Zergman]

Trying to allow my users to sort the data in a table based on what column they click.  Im not good with coding so please bare with me.

 

I created a variable to get the column name from a link tied to the column header.

$orderedby = $_GET['sortby'];

 

The table column headers look like this

<a href="http://www.mysite.com/admin/main.php?sortby=level1">Level 1</a>

 

Then my query to show the data

"SELECT * FROM `data` WHERE tdate = '$date' ORDER BY '$orderedby'";

 

Only thing is it doesn't work.  Can't figure out what im doing wrong.

 

Suggestions?

[ngreenwood6]

the sortby would have to be a value in the database. for example ("SELECT * FROM 'data' WHERE tdate = '$date' ORDER BY 'name'"). This would order all the results by the name. the name value would have to be in the database though.

[revraz]

Should work, echo the query to make sure the variable is working.

[trecool999]

Try this and tell use what you get:

$orderedby = $_GET['sortby'];
echo $orderedby;

Then write:

$query = mysql_query("SELECT * FROM `data` WHERE tdate = '$date' ORDER BY '$orderedby'") or die("Error was: " . mysql_error());

If it comes up with "Error was: blah blah blah", then your problem should be explained in that. If not, tell use what showed up.

[Zergman]

tried what you said trecool999, no errors.

 

If I do this, it doesn't work

$query_rstodayincidents = "SELECT * FROM `data` WHERE tdate = '$date' ORDER BY '$orderedby'";

 

If I do this, it works

$query_rstodayincidents = "SELECT * FROM `data` WHERE tdate = '$date' ORDER BY level1";

 

So im assuming its my variable that isn't working properly

[trecool999]

That makes sense.

Replace the 's around $orderby with `s.

If that doesn't work either, just take the quotes around $orderby out completely.

[Zergman]

That makes sense.

Replace the 's around $orderby with `s.

If that doesn't work either, just take the quotes around $orderby out completely.

 

I get a unknown column error when I use `s.  If I remove the quotes completely, I get syntax errors.

[trecool999]

That makes sense.

Replace the 's around $orderby with `s.

If that doesn't work either, just take the quotes around $orderby out completely.

 

I get a unknown column error when I use `s.  If I remove the quotes completely, I get syntax errors.

:S

Try a query using non-variables and just strings.

Like just trying "SELECT * FROM `data` WHERE `tdate` = '06/10/08' ORDER BY 'level1";

[Zergman]

Doing this works perfectly

$query_rstodayincidents = "SELECT * FROM `data` WHERE tdate = '2008-10-06' ORDER BY level1";

 

I echo'd $orderedby at the bottom of the page, and it does show level1

 

If I put the 's around level1, it doesn't sort.

[ngreenwood6]

try this

 

"SELECT * FROM `data` WHERE tdate = '2008-10-06' ORDER BY '". $orderby ."'";

[trecool999]

try this

 

"SELECT * FROM `data` WHERE tdate = '2008-10-06' ORDER BY '". $orderby ."'";

Good thinking Batman, but wouldn't they return the same thing even still?

[ngreenwood6]

You would think so but if it works for him then who cares.

[Zergman]

try this

 

"SELECT * FROM `data` WHERE tdate = '2008-10-06' ORDER BY '". $orderby ."'";

Good thinking Batman, but wouldn't they return the same thing even still?

 

sigh, still won't sort but the query executes.

 

im noticing that the variable is working.

 

Perhaps its how its being put in the url?

This is how it shows in firefox

http://www.mywebsite.com/admin/main.php?sortby=level1

 

 

[revraz]

What kind of infomation is in that field?  Is it both alpha and numeric or a single type?  What is the Fieldtype set to?

 

Numbers in a Text fieldtype will sort as Alpha and not Numeric.

[ngreenwood6]

If the variable is getting set then then it is not the url because it is getting the correct information from the url.

[Zergman]

its a varchar column with plain text stored in it

[revraz]

Give us an example of how it's not sorting right, because by default it sorts ASCending, since you don't specify how you want it sorted.

[Zergman]

I put in 3 entries to test.

 

This is how the level1 column looks like with no sorting.  Also what it looks like when I use the $orderedby

 

Level1

Found Working

Valid

Previously Working

 

 

 

When I use "ORDER BY level1" in my sql query, it sorts properly like this

 

Level1

Found Working

Previously Working

Valid

[revraz]

Is the fieldname Level1 or level1?

[Zergman]

level1 is the column name.

 

Level1 is just whats shows on the page as the visual header.  Everything behind the scenes is level1

[Zergman]

What should the url look like when sorting a specific column?

 

 

[Zergman]

LOL, my god why won't this work  ???

 

So I know the variable works when I click the column header, I have it echo on the page and it does change depending on what header I click.

 

I don't have any other sorting in the sql statement

 

I think its boiling down to the sql statement and syntax.

When I use this, it does sort

"SELECT * FROM `data` WHERE tdate = '$date' ORDER BY level1"

 

When I use this, it won't sort

"SELECT * FROM `data` WHERE tdate = '$date' ORDER BY '$orderedby'"

or this

"SELECT * FROM `data` WHERE tdate = '$date' ORDER BY 'level1'"

 

Any other suggestions?

[Barand]

Table

[pre]

+--------+------------------+------+-----+---------+----------------+

| Field  | Type            | Null | Key | Default | Extra          |

+--------+------------------+------+-----+---------+----------------+

| iddata | int(10) unsigned | NO  | PRI | NULL    | auto_increment |

| level1 | varchar(15)      | NO  |    | NULL    |                |

| tdate  | date            | NO  |    | NULL    |                |

+--------+------------------+------+-----+---------+----------------+

[/pre]

 

Data

[pre]

+--------+--------+------------+

| iddata | level1 | tdate      |

+--------+--------+------------+

|      1 | aa    | 2008-10-01 |

|      2 | bb    | 2008-10-01 |

|      3 | dd    | 2008-10-01 |

|      4 | gg    | 2008-10-02 |

|      5 | ee    | 2008-10-01 |

|      6 | cc    | 2008-10-01 |

+--------+--------+------------+

[/pre]

 

Code - works

<?php
include 'db.inc.php';
if (isset($_GET['sortby']))
{
    $orderby = $_GET['sortby'];
    $sql = "SELECT level1 FROM data WHERE tdate = '2008-10-01' ORDER BY $orderby";
    $res = mysql_query($sql);
    while ($row = mysql_fetch_row($res))
    {
        echo $row[0], '<br/>';
    }
}
?>
<form>
Sort by <input type="text" name="sortby" value="level1" size="12">
<br/>
<input type="submit" name="btnSubmit" value="Sort">
</form>

 

Sorted results

[pre]

aa

bb

cc

dd

ee

[/pre]

 

[Zergman]

Wicked, thanks for the example!