Zergman Posted October 6, 2008 Share Posted October 6, 2008 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? Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted October 6, 2008 Share Posted October 6, 2008 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. Quote Link to comment Share on other sites More sharing options...
revraz Posted October 6, 2008 Share Posted October 6, 2008 Should work, echo the query to make sure the variable is working. Quote Link to comment Share on other sites More sharing options...
trecool999 Posted October 6, 2008 Share Posted October 6, 2008 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. Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 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 Quote Link to comment Share on other sites More sharing options...
trecool999 Posted October 6, 2008 Share Posted October 6, 2008 That makes sense. Replace the 's around $orderby with `s. If that doesn't work either, just take the quotes around $orderby out completely. Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 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. Quote Link to comment Share on other sites More sharing options...
trecool999 Posted October 6, 2008 Share Posted October 6, 2008 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"; Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 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. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted October 6, 2008 Share Posted October 6, 2008 try this "SELECT * FROM `data` WHERE tdate = '2008-10-06' ORDER BY '". $orderby ."'"; Quote Link to comment Share on other sites More sharing options...
trecool999 Posted October 6, 2008 Share Posted October 6, 2008 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? Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted October 6, 2008 Share Posted October 6, 2008 You would think so but if it works for him then who cares. Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 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 Quote Link to comment Share on other sites More sharing options...
revraz Posted October 6, 2008 Share Posted October 6, 2008 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. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted October 6, 2008 Share Posted October 6, 2008 If the variable is getting set then then it is not the url because it is getting the correct information from the url. Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 its a varchar column with plain text stored in it Quote Link to comment Share on other sites More sharing options...
revraz Posted October 6, 2008 Share Posted October 6, 2008 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. Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 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 Quote Link to comment Share on other sites More sharing options...
revraz Posted October 6, 2008 Share Posted October 6, 2008 Is the fieldname Level1 or level1? Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 6, 2008 Author Share Posted October 6, 2008 level1 is the column name. Level1 is just whats shows on the page as the visual header. Everything behind the scenes is level1 Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 10, 2008 Author Share Posted October 10, 2008 What should the url look like when sorting a specific column? Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 11, 2008 Author Share Posted October 11, 2008 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 12, 2008 Share Posted October 12, 2008 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] Quote Link to comment Share on other sites More sharing options...
Zergman Posted October 12, 2008 Author Share Posted October 12, 2008 Wicked, thanks for the example! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.