John Davidson

Message:

I'm dynamically generating a html table with php and mysql, I'm able to generate it alright but I want to order the table by values from another table, the ordering never works, what am I doing wrong?

Here's my code:

$kids_list = array();

$kids_list[] = '<table border="2"><tr><th>Name</th><th>Group</th></tr>';

$db = JFactory::getDbo();

$query = $db->getQuery(true);

$query

->select(array('t1.id', 'name', 'lastname', 'group'))->from('kids t1')

->where('year = ' . date("Y"))

->join('LEFT', 'inscriptions t2 ON t1.id = t2.kid')

->order('t2.group ASC');

$db->setQuery($query);

$l_kids= $db->loadObjectList();

foreach ($l_kids as $l_kid) {

  $kids_list[] = '<tr>';

  $kids_list[] = '<td>';

  $kids_list[] = $l_kid->name . ' ' . $l_kid->lastname;

  $kids_list[] = '</td>';

  $kids_list[] = '<td>';

  $kids_list[] = $l_kid->group;

  $kids_list[] = '</td>';

  $kids_list[] = '</tr>';

}

$kids_list[] = '</table>';

return implode($kids_list);

I also tried using innerjoin to the same result, it doesn't get ordered at all.

It gets ordered by id from first table, completely ignoring the join.

---

My Answer:
SELECT * FROM kids ORDER BY age';foreach ($kids_list as $kid) { $result = mysqli_query($conn, $kid); if (mysqli_num_rows($result) > 0) { echo ""; echo ""; while ($row = mysqli_fetch_assoc($result)) { echo ""; } echo "

Name	Age
" . $row['name'] . "	" . $row['age'] . "

"; } else { echo "No kids found"; }}?>

The issue in your code is that you are trying to order the table by values from another table using a simple array. You need to modify your SQL query to join the two tables and order by the desired column from the other table.

Here's an example of how you can modify your code to achieve this:

php
$query = "SELECT kids.name, kids.age FROM kids JOIN other_table ON kids.id = other_table.kid_id ORDER BY other_table.column_name";
$result = mysqli_query($conn, $query);

if (mysqli_num_rows($result) > 0) {
echo "";
echo "";
while ($row = mysqli_fetch_assoc($result)) {
echo "";
}
echo "

Name	Age
" . $row['name'] . "	" . $row['age'] . "

";
} else {
echo "No kids found";
}


Replace `other_table` with the name of the table you want to order by, `kid_id` with the column that links the two tables, and `column_name` with the column you want to order by. This should order the table by values from another table as desired.