<?php
session_start();
include "inc_db.php";
$query = "SELECT u_d.u_id as uid, concat(u_pname,' ', u_fname) as uname, dog.d_id as did, d_name as dname, d_desc as ddesc FROM user, u_d, dog WHERE dog.d_id = u_d.d_id and user.u_id = u_d.u_id";
// The query results
// uid uname did dname ddesc (JSON)
// 1 gideon koch 1 Teddy {"fur-color":"Gold","collar":"Brown"}
$result = $conn->query($query);
if ($result->num_rows > 0) {
$mystr = '[';
while($row = $result->fetch_array()) {
$mystr .= '{"user_id":"'.$row['uid'].'","user_name":"'.$row['uname'].'","dog_id":"'.$row['did'].'","dog_name":"'.$row['dname'].'","dog_desc":'.$row['ddesc'].'},';
}
$mystr = substr($mystr,0,-1); // delete the last ,
$mystr .= ']';
}
/* This will create a JavaScript structure
[
{ "user_id":"1",
"user_name":"gideon koch",
"dog_id":"1",
"dog_name":"Teddy",
"dog_desc":
{"fur-color":"Gold","collar":"Brown"}
}
]
*/
?>
<!DOCTYPE html>
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<title>ex1</title>
<style>
table {
border-collapse: collapse;
}
td, th {
border:1px #888 solid;
}
th {
cursor:pointer;
}
</style>
<script>
// Assign a PHP string as a JavaScript variable
j1 = <?php echo $mystr ?>;
// sort order by dog name
function sortbydogname(a,b)
{
if (a.dog_name > b.dog_name) return 1
else
if (a.dog_name < b.dog_name) return -1
else return 0;
}
// sort by dog name
function showbydog()
{
j1.sort(sortbydogname);
build_table("main");
}
// sort order by dog name
function sortbyusername(a,b)
{
if (a.user_name > b.user_name) return 1
else
if (a.user_name < b.user_name) return -1
else return 0;
}
// show order by user
function showbyuser()
{
j1.sort(sortbyusername);
build_table("main");
}
function get_desc(js)
{
st = "";
for ( k in js) st += k + " - " +js[k] + "<br>";
return st;
}
// build the table and show it
function build_table(tid)
{
str = '<table style="width: 50%" cellpadding="5" align="center">';
str += `<tr>
<th onclick="showbyuser()" title="Click to sort by user name">User Name</th>
<th onclick="showbydog()" title="Click to sort by dog name">Dog Name</th>
<th>Dog Details</th>
</tr>`;
for (i=0 ; i< j1.length ; i++) {
str += `<tr>
<td>`+j1[i].user_name+`</td>
<td>`+j1[i].dog_name+`</td>
<td>`+get_desc(j1[i].dog_desc)+`</td>
</tr>`;
}
str += '</table>';
document.getElementById(tid).innerHTML = str;
}
</script>
</head>
<body>
<div id="main"></div>
<script>
showbyuser(); // default
</script>
</body>
</html>