Page 1 of 1

Posted: Tue Jul 31, 2007 4:28 am
by w33nie
I doubted I should post a new topic about this, well I've had a shot at doing this for my own mySQL table, and have failed badly somehow. So if someone could help, it would be greatly appreciated.

I have no doubt gone wrong somewhere in my attempt to have a sortable mySQL table within html tables. Everything is displayed perfectly, and sorted by the default, which is Name, but it just doesnt sort when I click on one of the links.

Note, I'm extremely new to both php and mysql. So I won't be able to spot the error in my code below. (sorry if its annoying to read due to its length)
yes I do need it to be sortable by 12 variables :D

Code: Select all

<tr>
<?php
***mysql connection info here***

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database"); 
?>
<?php
echo '<td><a href="?orderby=name&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Name</a></td>';

echo '<td><a href="?orderby=team&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Team</a></td>';

echo '<td><a href="?orderby=matches_pl&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Matches Played</a></td>';

echo '<td><a href="?orderby=periods_pl&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Periods Played</a></td>';

echo '<td><a href="?orderby=goals&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Goals</a></td>';

echo '<td><a href="?orderby=assists&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Assists</a></td>';

echo '<td>&nbsp;</td>';

echo '<td><a href="?orderby=votes&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Total Votes</a></td>';

echo '<td><a href="?orderby=votes_apr&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Apr</a></td>';

echo '<td><a href="?orderby=votes_may&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">May</a></td>';

echo '<td><a href="?orderby=votes_june&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Jun</a></td>';

echo '<td><a href="?orderby=votes_july&'; 
echo isset($_GET['ASC']) ? 'DESC' : 'ASC'; 
echo '">Jul</a></td>';

$orderby = 'name';
$direction = 'ASC';


if (isset($_REQUEST['order_by']))
{
    switch(trim($_REQUEST['order_by']))
    {
        case 'team':
            $order_by = 'team';
            break;
        case 'matches_pl':
            $order_by = 'matches_pl';
            break;
        case 'periods_pl':
            $order_by = 'periods_pl';
            break;
        case 'goals':
            $order_by = 'goals';
            break;
        case 'assists':
            $order_by = 'assists';
            break;
        case 'votes':
            $order_by = 'votes';
            break;
        case 'votes_apr':
            $order_by = 'votes_apr';
            break;
        case 'votes_may':
            $order_by = 'votes_may';
            break;
        case 'votes_jun':
            $order_by = 'votes_jun';
            break;
        case 'votes_jul':
            $order_by = 'votes_jul';
            break;
        default:
            $order_by = 'name';
    }

} else {
    $order_by = 'name';
}
?>
  </tr>

<?php
//error message (not found message)begins 
$XX = "Table error. Refresh this page or contact the site admin"; 
//query details table begins
$query = mysql_query("SELECT *, (votes_apr+votes_may+votes_june+votes_july) as votes FROM `stats-s1` ORDER BY $order_by $direction LIMIT 0, 200 ");
while ($row = @mysql_fetch_array($query))
{
//add this line:
  extract($row); 
$variable1=$row["name"];
$variable2=$row["team"];
$variable3=$row["matches_pl"];
$variable4=$row["periods_pl"];
$variable5=$row["goals"]; 
$variable6=$row["assists"];
$variable7=$row["votes"];
$variable8=$row["votes_apr"];
$variable9=$row["votes_may"];
$variable10=$row["votes_june"];
$variable11=$row["votes_july"];
//table layout for results 

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("<td>$variable5</td>");
print ("<td>$variable6</td>");
print ("<td>&nbsp;</td>");
print ("<td>$variable7</td>");
print ("<td>$variable8</td>");
print ("<td>$variable9</td>");
print ("<td>$variable10</td>");
print ("<td>$variable11</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{ 
print ("$XX");
} 
//end 
?>
[/i]

Posted: Tue Jul 31, 2007 6:17 am
by feyd
This thread has been split from here.

Posted: Tue Jul 31, 2007 6:45 am
by VladSun
The name of the variable in your URL-s is "orderby", while you are trying to read $_REQUEST['order_by']

Posted: Tue Jul 31, 2007 7:42 am
by w33nie
I'm sorry, but how do I fix it if that's the case?

Posted: Tue Jul 31, 2007 7:44 am
by John Cartwright
change

$_REQUEST['order_by'] to $_REQUEST['orderby']

:?

Posted: Tue Jul 31, 2007 7:47 am
by VladSun
Well - it is *your* choice how to name the variables ... Feel free to name'em equally in the URL's ( i.e. index.php?your_choice_of_name_here=your_choice_of_value_here ) and in the $_REQUEST['your_choice_of_name_here']

Posted: Tue Jul 31, 2007 8:53 am
by w33nie
Jcart wrote:change

$_REQUEST['order_by'] to $_REQUEST['orderby']

:?
haha told you i was bad, i didnt even make that connection

but thanks for the help, that worked.

although, now it only sorts in Ascending fashion. how do i get it to switch between Ascending and Descending when clicking the hyperlink

Posted: Tue Jul 31, 2007 10:43 am
by superdezign
w33nie wrote:although, now it only sorts in Ascending fashion. how do i get it to switch between Ascending and Descending when clicking the hyperlink
You have to make use of the 'ASC' or 'DESC' in the URL.

Posted: Tue Jul 31, 2007 1:15 pm
by w33nie
well when i click one of the sorting links, it does switch between ?order_by=team&ASC and ?order_by=team&DESC

Posted: Tue Jul 31, 2007 1:17 pm
by John Cartwright
because you have $direction hardcoded as 'ASC'.. you need the capture the value from the url

Posted: Tue Jul 31, 2007 2:47 pm
by w33nie
okay, thanks, all working perfectly now.

you can close this if you want