Simple PHP/MYSQL

[Al Neri]

i have a database that I outputted via php and mysql... now is there an easy way to put a link on the top of each column without using a form to have it execute SQL (ie same SQL query but order by column)?

 

[fs5]

just pass in a variable called 'orderBy' and the value equal to the column name. Then just order by that column when a request comes in. When you output the table also set another variable on the column that you just ordered by called 'desc' with value=1 so you know next time you're going to order by ASC if desc=1

 

[Al Neri]

$query = "SELECTblahblahblahblah";
?> <table border=1> <tr><td>Player Name</td><td>Team</td><td>Position</td><td>Option?</td></tr><?
$result = mysql_query($query) or die('Query failed: '.mysql_error());
while ($line = @mysql_fetch_array($result)) {
$fullname=$line['fulln'];
$team=$line['teamname'];
$pos=$line['position'];
$opt=$line['options'];
echo "<TR>";
echo "<TD>";
echo $fullname;
echo "</TD><TD>";
echo $team;
echo "</TD><TD>";
echo $pos;
echo "</TD><TD>";
echo $opt;
echo "</TD></TR>";
//$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}
echo "</TABLE>";

what link can i put on the titles of the columns to make them sortable? i thought of a few ways but theyd be complicated... is there an easy way to do that?

 

[clamum]

Pretty much what fs5 said.

Add a variable called 'orderBy' to your SQL statement when you do the query. The value will be the name of the column you wish to order by.

For example, make the Player Name text in that column a link to http://www.yourdomain.com/pagename.php?orderBy=Player%20Name.

orderBy should be a GET variable, and when you do the SQL query it would be something like:
$orderBy = $_GET['orderBy'];
$query = "SELECT * FROM table ORDER BY $orderBy";

Then when you click that link in the Player Name column, it will refresh the page, send it Player Name as the sort option, and display your table sorted by Player Name.

 

[Al Neri]

right but how do i set the variable when someone clicks on say, name or team?

 

[igowerf]

database.php?orderBy=name&direction=asc

In your code, you would grab those values by doing this:

$orderBy = $_GET["orderBy"];
$direction = $_GET["direction"];

 

[Al Neri]

hmm i tried:

if(isset($_POST['orderBy'])){
$orderby=$_POST['orderBy'];
$addon="ORDER BY $orderby";
}
$query = "SELECTblahblahblahblah".$addon;
?> <table border=1> <tr><td><a href="index.php?orderBy=player.lastn">Player Name</a></td><td>Team</td><td>Position</td><td>Option?</td></tr><?
$result = mysql_query($query) or die('Query failed: '.mysql_error());
while ($line = @mysql_fetch_array($result)) {
$fullname=$line['fulln'];
$team=$line['teamname'];
$pos=$line['position'];
$opt=$line['options'];
echo "<TR>";
echo "<TD>";
echo $fullname;
echo "</TD><TD>";
echo $team;
echo "</TD><TD>";
echo $pos;
echo "</TD><TD>";
echo $opt;
echo "</TD></TR>";
//$result = mysql_query($query) or die('Query failed: ' . mysql_error());
}
echo "</TABLE>";

i tried clicking on it and echoing $addon is empty... what am i dont wrong

 

[clamum]

Try this. Just replace the links with the actual page name.

 

[Al Neri]

EWE=TEHMAN!!!

 

[stndn]

Btw, just a small addition to the code above.
Make sure you do error checking on what value was passed as the value of orderBy
This is to avoid the "Bad Guys" from injecting silly codes to your SQL query.

$allowedList = array ('fulln', 'teamname', 'position'); # Basically the ordering that you want to allow

if (isset ($_GET['orderBy']))
{
...$orderBy = $_GET['orderBy'];

...if (! in_array ($orderBy, $allowedList))
...{
......die ('Bad request - or put something more fancy here');
...}
}

# connect to your database here and do the rest of stuffs as above