Why can't I ORDER BY name in this query?

Question

In my PHP function to populate the categories in my database, I have the following query:

$query = 'SELECT cat_id FROM cat WHERE name="'.$_GET['cat'].'"';

Why can I not sort by name when I do the following:

$query = 'SELECT cat_id FROM cat WHERE name="'.$_GET['cat'].'" ORDER BY name';

I do not get an error, but the sorting stays the same on the page, items are not sorted by name as I want them to be. My text editor doesn't highlight ORDER BY as an operation like it does with SELECT, FROM, WHERE

Full function:

function populate_category()
{
  global $link;

  $query = 'SELECT cat_id FROM cat WHERE name="'.$_GET['cat'].'"';
  $result = mysqli_query($link, $query);
  $cat_id = mysqli_fetch_row($result)[0];
  $nbprod = mysqli_query($link, 'SELECT COUNT(*) FROM item_cat WHERE cat_id="'.$cat_id.'"');

  $query =  'SELECT item.* FROM item JOIN item_cat ON item.item_id=item_cat.item_id JOIN cat ON item_cat.cat_id=cat.cat_id WHERE cat.name = "'.$_GET['cat'].'" ORDER BY item.name';
  $result = mysqli_query($link, $query);

  echo "<span style='display: inline; font-size: 24px; font-weight: bold;'>&quot;{$_GET['cat']}&quot; Journals</span>&nbsp&nbsp&nbsp";
  echo '<span style="color: gray; font-size: 14px; font-weight: 500;">'.mysqli_fetch_row($nbprod)[0].' RESULTS FOUND</span><br><hr>';

  while ($cat = mysqli_fetch_row($result))
    echo '
    <div class="itemlist">
        <span><h3 style="display:inline;">'.$cat[1].'</h3><h6 style="display:inline; margin-left: 1%;"><a href="#"><u>View Media Kit</u></a></h6></span>
        <div class="col-lg-12" style="background-color: white;"><br>
            <div class="row">
              <div class="col-lg-2" style="margin-right: 2%;">
              <a href="#" class="thumbnail"><img src="https://via.placeholder.com/160x210"></a>
              </div>
            </div><br>
            </div>
      </div>
      <hr>
    ';
}

Answer 1

You don't need your first query; you can integrate it into the second one by changing your WHERE clause to be on cat.name instead of cat.cat_id. Note you should use a prepared query to avoid SQL injection issues. This code should work:

$query = 'SELECT item.*
          FROM item
          JOIN item_cat ON item.item_id=item_cat.item_id
          JOIN cat ON item_cat.cat_id=cat.cat_id
          WHERE cat.name = ?
          ORDER BY item.name';
$stmt = $link->prepare($query);
$stmt->bind_param('s', $_GET['cat']);
if (!$stmt->execute()) {
    echo "Error: " . $stmt->error;
    exit;
}
$result = $stmt->get_result();
// number of products
$num_prods = $result->num_rows;
while ($cat = $result->fetch_row()) {
    // rest of your code here

Note that you shouldn't need a separate query to fetch the number of products, you can simply use $stmt->num_rows after you have called get_result(). I've included that in the code above.