-1

I have a PHP file where I am populating a table from MySQL data, using array format to gather the information. I have headers on my table, and I would like to sort the table after clicking a specific header. How can I do that?

This is my running the query and getting the data into an array.

$results=$db_link->query($query);

 while($row = $results->fetch_assoc()) {
    $project_data[$row[id]][id] = $row[id];
    $project_data[$row[id]][billable] = $row[billable];
    $project_data[$row[id]][pugh_project] = $row[pugh_project];
    $project_data[$row[id]][name] = $row[address2]." ".$row[name];
    $project_data[$row[id]][project_num] = $row[project_num];
    $project_data[$row[id]][budget] = $row[budget_hours_pm] + $row[budget_hours_eng] + $row[budget_hours_des] + $row[budget_hours_cad] + $row[budget_hours_tech];
 }

This is the way I have my headers using HTML.

 <div class="grid_12">

 <table class="contact_list_table">

    <tr class="contact_list_top_table">
        <td width="100px" style="color:white">Project #</a></td>
        <td style="color:white">Project Name</td>
        <td style="color:white">Budget Hours</td>
        <td style="color:white">Actual Hours</td>
        <td style="color:white">Estimate to Complete</td>
        <td style="color:white">Budget at Completion</td>
        <td style="color:white">Performance (%)</td>    
    </tr>

How can I make it so that I can click on a header (eg. Project Name), and refresh the page with the proper sorting??

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
Lakito
  • 13
  • 6
  • 1
    please post your code as text and not pictures. – Lelio Faieta Mar 29 '18 at 17:44
  • Also did you try something to solve this already? – ChristianM Mar 29 '18 at 17:45
  • btw if you want you can use a library designed for that. I am thinking about things like datatable for example – Lelio Faieta Mar 29 '18 at 17:46
  • thanks for that, I'm new here, just did it @LelioFaieta – Lakito Mar 29 '18 at 17:49
  • I have a basic idea of how to do this, but I'm getting a bit confused doing it....I thought about do some $_GET method, where if a variable isset, then I can do a switch case or something to sort the array....but the array_sorting part is what I can't figure out..... @ChristianM – Lakito Mar 29 '18 at 17:50
  • don't do this kind of things on the server side. Use javascript for that. No page reload, and faster times with no data exchange between server and client – Lelio Faieta Mar 29 '18 at 17:54
  • @LelioFaieta I was trying to stay away from JavaScript, I thought it would be easier with my idea, but you make a good point about the faster times...is there a reference I can use for integrating JavaScript?? – Lakito Mar 29 '18 at 17:57
  • I usually just use [datatables](https://datatables.net/) for this type of thing. I've done it myself in plain PHP too, and it's usually just so much more trouble. – Don't Panic Mar 29 '18 at 17:59
  • I already told you and I agree with @Don'tPanic : datatable are an easy and effective solution – Lelio Faieta Mar 29 '18 at 18:05
  • thanks a lot, I will definitely try that method then! – Lakito Mar 29 '18 at 18:10
  • That depends a bit on the size of hte data set. But yes datatables can be a good idea. As oyu are pulling the data out of a database you definitely don't want to array sort but let the DB sort it fi you don't do datatables. – ChristianM Mar 29 '18 at 18:10
  • The minimal effort of typing "php mysql sortable table" into Google would have gotten you https://stackoverflow.com/questions/3489783/how-to-sort-rows-of-html-table-that-are-called-from-mysql immediately. – CBroe Mar 29 '18 at 18:27
  • thanks @CBroe, that is a helpful link. I did do my research prior to this, but I am fairly new at this. I didn't find the 'array' related answers before, but I'll take a look at that, thanks for the nice answer! – Lakito Mar 29 '18 at 19:21

1 Answers1

0

If you want to do this all in PHP, the first step would be to convert the text in the column headers to links, specifying the column in a query string.

<td style="color:white">
    <a href="thispage.php?sort=projectname">Project Name</a>
</td>

Then in your controller, (or whatever script you have that generates the table), you'll have access to the column you clicked in $_GET['sort'], and you can use that in an ORDER BY clause in your query.

You won't be able to pass a column name as a parameter to a prepared statement, so you'll have to add that dynamically to your SQL. It's a potential SQL injection vulnerability, so be sure you verify that $_GET['sort'] contains a valid column name before just using it in the query.

Don't Panic
  • 41,125
  • 10
  • 61
  • 80
  • thanks for this! This is what I had in mind, and I think I was going the wrong way, but if I use the ORDER BY clause, depending on the href clicked, I can make it happen....I'll give it a go....!!! – Lakito Mar 29 '18 at 18:22