Sorting Table Results JQuery Tablesorter issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Sorting Table Results JQuery Tablesorter issue

Post by Flashart »

Hello all!

Yes I have returned again with yet another query!

I am looking to sort my results from a php/mysql query by clicking on the column headers a 'la jquery/tablesorter.

I can get this to work on a static HTML page sweet as but can I replicate this within a php page? No. I must be missing something quite simple?

OK so I have the javascript code in the <HEAD>

Code: Select all

<script type="text/javascript" src="tablesorter/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="tablesorter/jquery.tablesorter.min.js"></script>
<script type="text/javascript">$(document).ready(function() 
    {$("#table").tablesorter(); }); </script>


further down the page I have my sql query:

Code: Select all

$query = "SELECT date,account,campaign,adgroup, sum(impressions)impressions, sum(clicks)clicks,sum(clicks/impressions)ctr,sum(cost)cost,sum(cost/clicks)cpc,avg_position,sum(conversions)conversions,sum(conversions_mpc)conversions_mpc FROM adgroup_data WHERE account LIKE '".$account."' AND date>= '".$from_date."' AND date <='".$to_date."' group by adgroup";
Moving on down I have the code to populate the table:

Code: Select all

//Table to hold the data
    echo "<table id='table'>";
    echo "<thead>";
    echo "<th>Account</th>";
    echo "<th>Campaign</th>";
    echo "<th>Adgroup</th>";
    echo "<th>Impressions</th>";
    echo "<th>Clicks</th>";
    echo "<th>CTR</th>";
    echo "<th>CPC</th>";
    echo "<th>Cost</th>";
    echo "<th>Avg_Position</th>";
    echo "<th>Conversions</th>";    
    echo "<th>Conversions_mpc</th>";
    echo "</thead>";
 
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 
 
{
    echo "<tbody>";
//provides a highlight when moving over a row
    echo "<tr onmouseover=\"mouse_event(this, 'hlt');\" onmouseout=\"mouse_event(this, '');\">";
    echo "<td>{$row['account']}</td>";
    echo "<td>{$row['campaign']}</td>";
    echo "<td>{$row['adgroup']}</td>";
    echo "<td>{$row['impressions']}</td>";
    echo "<td>{$row['clicks']}</td>";
    echo "<td>{$row['ctr']}%</td>";
    echo "<td>£{$row['cpc']}</td>";
    echo "<td>£{$row['cost']}</td>";
    echo "<td>{$row['avg_position']}</td>";
    echo "<td>{$row['conversions']}</td>";
    echo"<td>{$row['conversions_mpc']}</td>
    </tr>";
    
}
 
echo "</tbody>";
echo "</table>";
 
I have searched the forums (and google) for an answer but my lack of knowledge makes it a little harder to amend what I see in other peoples queries to fit my own needs. Like i say, I can get this to work on a static HTML page but not php.

It seemed so simple when I started out!

I would be very grateful for a pointer.

Many thanks
Peter
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Sorting Table Results JQuery Tablesorter issue

Post by jayshields »

Take your <tbody> tag outside of your loop.
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Sorting Table Results JQuery Tablesorter issue

Post by Flashart »

OK done that, but it's made no difference.

Code: Select all

 
//Table to hold the data
    echo "<table class='sortable' id='results_table'>";
    echo "<thead>";
    //  echo "<th>Date</th>";
    echo "<th>Account</th>";
    echo "<th>Campaign</th>";
    echo "<th>Adgroup</th>";
    echo "<th>Impressions</th>";
    echo "<th>Clicks</th>";
    echo "<th>CTR</th>";
    echo "<th>CPC</th>";
    echo "<th>Cost</th>";
    echo "<th>Avg_Position</th>";
    echo "<th>Conversions</th>";    
    echo "<th>Conversions_mpc</th>";
    echo "</thead>";
    echo "<tbody>";
 
    
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 
 
{
    
    echo "<tr onmouseover=\"mouse_event(this, 'hlt');\" onmouseout=\"mouse_event(this, '');\">";
    //echo "<td>{$row['date']}</td>";
    echo "<td>{$row['account']}</td>";
    echo "<td>{$row['campaign']}</td>";
    echo "<td>{$row['adgroup']}</td>";
    echo "<td>{$row['impressions']}</td>";
    echo "<td>{$row['clicks']}</td>";
    echo "<td>{$row['ctr']}%</td>";
    echo "<td>£{$row['cpc']}</td>";
    echo "<td>£{$row['cost']}</td>";
    echo "<td>{$row['avg_position']}</td>";
    echo "<td>{$row['conversions']}</td>";
    echo"<td>{$row['conversions_mpc']}</td>
    </tr>";
    
    
}
 
echo "</tbody>";
Perhaps I should rebuild the table as html code with php tags within it? I don't initially see why this should affect it but it's the only thing I can think of doing?
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Sorting Table Results JQuery Tablesorter issue

Post by Flashart »

FIXED!

Took out the <tbody> as suggested AND the <thead> element.

Even though the instructions say you need them..

Onto my next issue.. I will be back!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Sorting Table Results JQuery Tablesorter issue

Post by Eran »

<tbody> is not required, but if you are using it you need to close it inside the loop as well (it's being closed outside the loop just once)
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Sorting Table Results JQuery Tablesorter issue

Post by jayshields »

pytrin wrote:<tbody> is not required, but if you are using it you need to close it inside the loop as well (it's being closed outside the loop just once)
I don't know what you mean (you only need a closing <tbody> once - after the table body), but the reason why it didn't work was probably because you didn't use a </table> at the end.

Make sure your HTML is valid when you are viewing the generated page, and the table sorter will probably work fine.
Post Reply