Page 1 of 1

Sorting Table Results JQuery Tablesorter issue

Posted: Sun Dec 27, 2009 8:42 am
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

Re: Sorting Table Results JQuery Tablesorter issue

Posted: Sun Dec 27, 2009 7:03 pm
by jayshields
Take your <tbody> tag outside of your loop.

Re: Sorting Table Results JQuery Tablesorter issue

Posted: Sun Dec 27, 2009 10:38 pm
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?

Re: Sorting Table Results JQuery Tablesorter issue

Posted: Mon Dec 28, 2009 4:33 am
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!

Re: Sorting Table Results JQuery Tablesorter issue

Posted: Mon Dec 28, 2009 4:36 am
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)

Re: Sorting Table Results JQuery Tablesorter issue

Posted: Tue Dec 29, 2009 7:06 am
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.