Reording A Database Output With a Link

[BlaizeDuke]

First hello everyone, this looks like a really good community for PHP. I'm just starting PHP and MySQL. I picked up a PHP and MySQL for dummies, but I think I got an older version because I couldn't get it to work until I came here and used some code in the sticky at the top.

Ok so right now I'm toying around with a database. My eventual plan is to display every NFL Player (25 at a time) and link to each of their profile pages which will have a total of 17 facts of information ranging from name to where the played in high school to their twitter account.

Right now I'm trying to figure out how to allow a user to resort the page just by clicking a link.

I currently have created a basic version just for learning reasons four columns of information, lastname, firstname, position, and college.

I'm using

"SELECT * FROM Players ORDER BY lastname"

Do I have to create an entire new page to reorder it or is there a function that will reorder it for the user?

[laserlight]

BlaizeDuke wrote:

Do I have to create an entire new page to reorder it or is there a function that will reorder it for the user?

You would create an entire new page to show the data in the desired ordering (well, you don't have to load an entirely new page, but let's keep it simple for now). One idea is to provide a name/value pair in the query string of the link, and then based on the corresponding $GET['orderby'] variable you would set the ORDER BY clause in your SQL query. To do this, you would likely check if $GET['orderby'] matches one of the column names that you want to allow ordering on.

[BlaizeDuke]

Do you have a tutorial on this? Like I said I'm pretty new to this...

[laserlight]

No, but it is not very hard. For example:

$orderby = 'lastname';
if (isset($_GET['orderby']) && in_array($_GET['orderby'], array('lastname'))) {
    $orderby = $_GET['orderby'];
}

$sql = "SELECT * FROM Players ORDER BY $orderby";
// ...

For link itself, it could be as simple as:

<a href="?orderby=lastname">Last Name</a>

[BlaizeDuke]

Not working so far... here is what I have.

$orderby = 'lastname';
if (isset($_GET['orderby']) && in_array($_GET['orderby'], array('lastname'))) {
    $orderby = $_GET['orderby'];
}
$sql = "SELECT * FROM Players ORDER BY $orderby";  

$query = mysql_query($sql); 

// Creates an array of items with keys corresponding to table columns... 
echo '<table><tr><td>Last Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>'.
	'<td>First Name&nbsp;&nbsp;</td>'.
	'<td>Position&nbsp;&nbsp;</td>'.
	'<td>College&nbsp;&nbsp;</td></tr>';
while($row = mysql_fetch_array($query))
{ 

//Create the HTML table 
echo "<tr><td>".$row[lastname]."</td><td>".$row[firstname]."</td><td>".$row[position]."</td><td>".$row[college]."</td></tr>";
} 
echo "</table>"; 
?>

when I link to "?orderby=college" nothing happens the order stays the same. Would it matter a lot of it wasn't a .php page. I'm using wordpress and using exec-php to write php in my html pages. Here is the full address:
http://titanstouchdown.com/nflplayers?orderby=college

[laserlight]

BlaizeDuke wrote:

when I link to "?orderby=college" nothing happens the order stays the same.

That is because 'college' is not one of the valid columns to order by. Notice that the in_array check only has 'lastname' at the moment.

[BlaizeDuke]

Thank you so much laserlight. Its working great. One last question for now. If I wanted to do a "search on the database" where it would return only players who played at Alabama. How would I do that? And can I tie it in to the ?orderby so I could search for Alabama players and order it by their position?

[laserlight]

BlaizeDuke wrote:

If I wanted to do a "search on the database" where it would return only players who played at Alabama. How would I do that?

You would need to use an appropriate WHERE clause.

BlaizeDuke wrote:

And can I tie it in to the ?orderby so I could search for Alabama players and order it by their position?

Yes, you can, though you would probably want a different name, e.g., ?orderby=college&state=Alabama. Consider using a form.

[BlaizeDuke]

Tried to do it myself but apparently I'm not that good at this kind of stuff. Its returning an error.

Parse error: syntax error, unexpected $end in D:\Hosting\7277632\html\wp-content\plugins\exec-php\includes\runtime.php(42) : eval()’d code on line 29

<?php
$orderby = 'lastname';
if (isset($_GET['orderby']) && in_array($_GET['orderby'], array('lastname','firstname','positon','college'))) {
    $orderby = $_GET['orderby'];
}
$whereby = 'alabama';
if (isset($_GET['whereby']) && in_array($_GET['whereby'], array('alabama','clemson'))) {
    $whereby = $_GET['whereby'];
$sql = "SELECT * FROM Players WHERE college=’$whereby’ ORDER BY $orderby";  

$query = mysql_query($sql); 

// Creates an array of items with keys corresponding to table columns... 
echo '<table><tr><td><a href="?orderby=lastname">Last Name</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=firstname">First Name</a>&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=position">Position</a>&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=college">College&nbsp;&nbsp;</a></td></tr>';
while($row = mysql_fetch_array($query))
{ 

//Create the HTML table 
echo "<tr><td>".$row[lastname]."</td><td>".$row[firstname]."</td><td>".$row[position]."</td><td>".$row[college]."</td></tr>";
} 
echo "</table>"; 
?>

[BlaizeDuke]

Found some of the problems on my own... still not working but here is new code.

if (isset($_GET['orderby']) && in_array($_GET['orderby'], array('lastname','firstname','positon','college'))) {
    $orderby = $_GET['orderby'];
}
$whereby = 'alabama';
if (isset($_GET['whereby']) && in_array($_GET['whereby'], array('alabama','clemson'))) {
    $whereby = $_GET['whereby'];
}
$sql = "SELECT * FROM Players WHERE $whereby ORDER BY $orderby";  

$query = mysql_query($sql); 

// Creates an array of items with keys corresponding to table columns... 
echo '<table><tr><td><a href="?orderby=lastname">Last Name</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=firstname">First Name</a>&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=position">Position</a>&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=college">College&nbsp;&nbsp;</a></td></tr>';
while($row = mysql_fetch_array($query))
{ 

//Create the HTML table 
echo "<tr><td>".$row[lastname]."</td><td>".$row[firstname]."</td><td>".$row[position]."</td><td>".$row[college]."</td></tr>";
} 
echo "</table>"; 
?>

[laserlight]

You missed a closing brace. Strings in SQL should be enclosed in single quotes, not any other kind of quotes.

[BlaizeDuke]

Is this better?

<?php 
$orderby = 'lastname';
if (isset($_GET['orderby']) && in_array($_GET['orderby'], array('lastname','firstname','positon','college'))) {
    $orderby = $_GET['orderby'];
}
$whereby = 'alabama';
if (isset($_GET['whereby']) && in_array($_GET['whereby'], array('alabama','clemson'))) {
    $whereby = $_GET['whereby'];
}
$sql = 'SELECT * FROM Players WHERE $whereby ORDER BY $orderby';  

$query = mysql_query($sql); 

// Creates an array of items with keys corresponding to table columns... 
echo '<table><tr><td><a href="?orderby=lastname">Last Name</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=firstname">First Name</a>&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=position">Position</a>&nbsp;&nbsp;</td>'.
	'<td><a href="?orderby=college">College&nbsp;&nbsp;</a></td></tr>';
while($row = mysql_fetch_array($query))
{ 

//Create the HTML table 
echo '<tr><td>".$row[lastname]."</td><td>".$row[firstname]."</td><td>".$row[position]."</td><td>".$row[college]."</td></tr>';
} 
echo '</table>'; 
?>

Because I'm still having no luck

[laserlight]

No, it should be:

$sql = "SELECT * FROM Players WHERE college='$whereby' ORDER BY $orderby";

[BlaizeDuke]

OK so its working but its only returning alabama players, which is what I want... now I need a link to have it return clemson players

[BlaizeDuke]

Nevermind figured it out, but I can't get it to show all... how do I do that?

[laserlight]

BlaizeDuke wrote:

but I can't get it to show all... how do I do that?

You need to write the logic such that if there is no college provided, then you just leave out the where clause in the SQL statement.