How to sort rows of HTML table that are called from MySQL

Question 1

I know it's such a basic thing, but a Google search hasn't shown me how to re-sort the rows after clicking the th links.

I've got this:

<table border="1">
  <tr>
    <th>Type:</th>
    <th>Description:</th>
    <th>Recorded Date:</th>
    <th>Added Date:</th>
  </tr>

<?php 
while($row = mysql_fetch_array($result)){
    ?>
    <tr>
        <td><?php echo $row['type'] ?></td>
        <td><?php echo $row['description'] ?></td>
        <td><?php echo $row['recorded_date'] ?></td>
        <td><?php echo $row['added_date'] ?></td>
    </tr>
    <br /> 


  <?php 
}
mysql_close();
?>
</table>

I need to be able to click type and sort alphabetically, and click on either Recorded Date or Added Date and sort by date. I see that I need to have the MySQL queries do this, but do I set them up as conditionals with a href tags?

Question 2

The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.

The HTML would look like this:

<th><a href="mypage.php?sort=type">Type:</a></th>
<th><a href="mypage.php?sort=desc">Description:</a></th>
<th><a href="mypage.php?sort=recorded">Recorded Date:</a></th>
<th><a href="mypage.php?sort=added">Added Date:</a></th>

And in the php code, do something like this:

<?php

$sql = "SELECT * FROM MyTable";

if ($_GET['sort'] == 'type')
{
    $sql .= " ORDER BY type";
}
elseif ($_GET['sort'] == 'desc')
{
    $sql .= " ORDER BY Description";
}
elseif ($_GET['sort'] == 'recorded')
{
    $sql .= " ORDER BY DateRecorded";
}
elseif($_GET['sort'] == 'added')
{
    $sql .= " ORDER BY DateAdded";
}

$>

Notice that you shouldn't take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;

Question 3

Remove single quote & * from the query so that query should look like below. I hope it will work.

SELECT Acronym, Full Name, What FROM jargon1 ORDER BY $col $dir

If it won't work,

Can you print

echo "SELECT Acronym, Full Name, What FROM jargon1 ORDER BY $col $dir";

and let me know what you get?

Note: I think you wanted to use backticks(`), however mistakenly used single quote.

Edit 1

As you see

echo "SELECT Acronym, Full Name, What FROM jargon1 ORDER BY $col $dir";

prints

SELECT Acronym, Full Name, What FROM jargon1 ORDER BY ASC`

That means $col is not printing.. please check what is going wrong with $col

SELECT Acronym, Full Name, What FROM jargon1 ORDER BY columnNameMissing ASC`
                                                      ^^^^^^^^^^^^^^^^^

Question 4

The best way to do get your table displaying "row-data in columns" is to use a table in a table, since you have to build a table in HTML row by row.

Then, to get all scores of a player, you can either request the data for each player by a seperate request or use a GROUP_CONCAT.

$result = mysqli_query($conn,"SELECT p.*, (SELECT GROUP_CONCAT(s.score) FROM score s WHERE s.playerid = p.playerid) AS scorearray FROM player p");

Now your result will contain all colums of your player table and a column "scorearray", which contains a comma-separated list of all scores from your player. See GROUP_CONCAT example.

To generate the first row you can reuse your given code:

$result = mysqli_query($conn, "SELECT p.*, (SELECT GROUP_CONCAT(s.score) FROM score s WHERE s.playerid = p.playerid) AS scorearray FROM player p");


$playercols = array(); 
$scores = array(); 

while($row = mysqli_fetch_array($result)) 
{ 
    $playercols[] =  "<th>" . $row['name'] . "</th>"; 
    $currentscore = explode(",", $row['scorearray']); 
    // Only doing a line break, you can build a one-columned table out of this data aswell 
    $scores[] = "<td valign=top>" . implode("<br />", $currentscore) . "</td>"; 
} 
echo "<table>"; 
echo "<tr>"; 
echo implode("", $playercols); 
echo "</tr>"; 
echo "<tr>"; 
echo implode("", $scores); 
echo "</tr>"; 
echo "</table>";

See the result here: Result

And full source here: Source

Question 5

You need to use Style Sheet for this purpose.

<td style="display:none;">

Question 6

Pass your event handler a reference to the row that is clicked using this:

<td><a href="#" onclick="toggleRow(this);"><img alt="Expand row" height="20px;" src="expand.png"></a></td>

Then update your toggleRow function as follows:

function toggleRow(e){
    var subRow = e.parentNode.parentNode.nextElementSibling;
    subRow.style.display = subRow.style.display === 'none' ? 'table-row' : 'none';    
}

You may want to consider creating a general-purpose function to navigate up the DOM tree (so that this function won't break when/if you change your HTML).

How to sort rows of HTML table that are called from MySQL

Answers

Edit 1

php,mysql,html-table