Resolved ORDER BY not working

[~Kira]

I have a function and I need to sort by a new field sort_by because it's being changed all over the place and I just need that. so I added some code to the function but it doesn't work.

function getProductsByCategory($categoryID) {
global $dbConnection;

$dbQuery = 'SELECT * FROM 00_product_categories WHERE category_id=' . $categoryID;
$sqlResult = $dbConnection->query($dbQuery);
$itemCount = $sqlResult->size();

$products = array();
$insertedProducts = 0;
for ($i = 0; $i < $itemCount; $i++) {
$itemFetch = $sqlResult->fetch();
$dbQuery2 = 'SELECT * FROM 00_products WHERE id=' . $itemFetch['product_id'] . ' ORDER BY sort_order ASC';
$sqlResult2 = $dbConnection->query($dbQuery2);
if ($sqlResult->size() > 0) {
$products[$insertedProducts] = $sqlResult2->fetch();
$insertedProducts++;
}
}

return $products;
}

What am I doing wrong?

[tangoforce]

What am I doing wrong?

Why don't you ask mysql_error() ?

[skywalker2208]

$dbQuery2 = 'SELECT * FROM 00_products WHERE id=' . $itemFetch['product_id'] . ' ORDER BY sort_order ASC'; 

I assume the query above returns only one result because you have "WHERE id = ..." so there is nothing for it to sort by.

If I am incorrect about it returning one result then please give some examples of the data being stored.

[~Kira]

Yes that's got to be a problem but if so, where do I put the order?

Here's the whole file so you can see what's going on.

<?php

// Turn off all error reporting
error_reporting(0);

function getAllCategories() {
    global $dbConnection;
    
    $dbQuery = "SELECT * FROM 00_categories ORDER BY sort_order ASC";
    $sqlResult = $dbConnection->query($dbQuery);
    $catCount = $sqlResult->size();

    $categories = array();
    for ($i = 0; $i < $catCount; $i++) {
        $categories[$i] = $sqlResult->fetch();    
    }
    
    return $categories;
}

function getProductsByCategory($categoryID) {
    global $dbConnection;
    
    $dbQuery = 'SELECT * FROM 00_product_categories WHERE category_id=' . $categoryID;
    $sqlResult = $dbConnection->query($dbQuery);
    $itemCount = $sqlResult->size();

    $products = array();
    $insertedProducts = 0;
    for ($i = 0; $i < $itemCount; $i++) {
        $itemFetch = $sqlResult->fetch();
        $dbQuery2 = 'SELECT * FROM 00_products WHERE id=' . $itemFetch['product_id'] . ' ORDER BY sort_order ASC';
        $sqlResult2 = $dbConnection->query($dbQuery2);
        if ($sqlResult->size() > 0) {
            $products[$insertedProducts] = $sqlResult2->fetch();
            $insertedProducts++;
        }
    }
    
    return $products;
}

function getProductSizes($productID){
    global $dbConnection;
    
    $dbQuery = 'SELECT * FROM 00_product_sizes WHERE product_id=' . $productID;
    $sqlResult = $dbConnection->query($dbQuery);
    $itemCount = $sqlResult->size();

    $sizes = array();
    $insertedSizes = 0;
    for ($i = 0; $i < $itemCount; $i++) {
        $itemFetch = $sqlResult->fetch();
        $dbQuery2 = 'SELECT * FROM 00_sizes WHERE id=' . $itemFetch['size_id'];
        $sqlResult2 = $dbConnection->query($dbQuery2);
        if ($sqlResult->size() > 0) {
            $sizes[$insertedSizes] = $sqlResult2->fetch();
            $insertedSizes++;
        }
    }
    
    return $sizes;
}

function showCart() {
    global $dbConnection;
    
    $cart = $_SESSION['cart'];
    if ($cart) {
        $items = explode(',', $cart);
        $productsData = array();
        $currentPos = 0;
        foreach ($items as $item) {
            $productsData[$currentPos] = explode(':', $item);
            $currentPos++;
        }
        
        $output = '<form method="post" action="index.php#cart">';
        $output .= '<h4>Please check quantities and update if necessary.</h4>
<h4>Click "Remove" to remove all of a particular item.</h4>
<h4>When ready, enter your name and email address to send.</h4><table>';
        foreach ($productsData as $data) {
            $dbQuery = 'SELECT * FROM 00_products WHERE id=' . $data[0];
            $dbResult = $dbConnection->query($dbQuery);
            $product = $dbResult->fetch();
            $dbQuery2 = 'SELECT * FROM 00_sizes WHERE id=' . $data[1];
            $dbResult2 = $dbConnection->query($dbQuery2);
            $size = $dbResult2->fetch();
            $output .= '<tr>';
            $output .= '<td><a href="index.php?action=delete&product_id=' . $product['id'] . '&size_id=' . $size['id'] . '#cart" class="r">Remove</a></td>';
            $output .= '<td>' . $product['name'] . ' by ' . $product['model'] . '; Size: ' . $size['name'] . '</td>';
            $output .= '<td>$' . $product['price'] . '</td>';
            $output .= '<td><input type="text" name="size[' . $product['id'] . '][' . $size['id'] . ']' . '" value="' . $data[2] . '" size="3" maxlength="3" /></td>';
            $output .= '<td>$' . ($product['price'] * $data[2]) . '</td>';
            $total += ($product['price'] * $data[2]);
            $output .= '</tr>';
        }
        $output .= '</table>';
        $output .= '<p>Grand total: <strong>$' . $total . '</strong></p>';
        $output .= '<div><input type="hidden" name="action" value="update" />';
        $output .= '<div><button type="submit">Update My Order Request</button></div>';
        $output .= '</form>';
        $output .= '<BR><BR><form method="post" action="index.php#cart">
                    <table width="350">
                    <tr>
                    <td align="right">Name: </td><td><input type="text" name="name" value="' . $_POST['name'] . '" /></td>
                    </tr>
                    <tr>
                    <td align="right">Email: </td><td><input type="text" name="email" value="' . $_POST['email'] . '" /></td>
                    </tr>
                    <tr>
                    <td align="right">&nbsp;</td><td><div>
                    <input type="hidden" name="action" value="mail" />
                    <input type="submit" value="Submit My Order Request" /></div></td>
                    </tr>
                    </table>
                    </form>';
        
    } else {
        $output .= '<p>Your request form is empty.</p>';
    }
    return $output;
}

function showCartForEmail() {
    global $dbConnection;
    
    $cart = $_SESSION['cart'];
    if ($cart) {
        $items = explode(',', $cart);
        $productsData = array();
        $currentPos = 0;
        foreach ($items as $item) {
            $productsData[$currentPos] = explode(':', $item);
            $currentPos++;
        }
        
        $output = 'Customer: ' . $_POST['name'] . '<br />';
        $output .= 'Contact Email: ' . $_POST['email'] . '<br />';
        $output .= '<table>';
        foreach ($productsData as $data) {
            $dbQuery = 'SELECT * FROM 00_products WHERE id=' . $data[0];
            $dbResult = $dbConnection->query($dbQuery);
            $product = $dbResult->fetch();
            $dbQuery2 = 'SELECT * FROM 00_sizes WHERE id=' . $data[1];
            $dbResult2 = $dbConnection->query($dbQuery2);
            $size = $dbResult2->fetch();
            $output .= '<tr>';
            $output .= '<td>' . $product['name'] . ' by ' . $product['model'] . '; Size: ' . $size['name'] . '</td>';
            $output .= '<td>$' . $product['price'] . '</td>';
            $output .= '<td>Quantity:' . $data[2] . '</td>';
            $output .= '<td>$' . ($product['price'] * $data[2]) . '</td>';
            $total += ($product['price'] * $data[2]);
            $output .= '</tr>';
        }
        $output .= '</table>';
        $output .= '<p>Grand total: <strong>$' . $total . '</strong></p>';
    } else {
        $output .= '<p>Your shopping cart is empty.</p>';
    }
    return $output;
}

?>

[tangoforce]

Yes that's got to be a problem but if so, where do I put the order?

Here's the whole file so you can see what's going on.

You're missing the point of what skywalker has said.

If there is only one result being returned (which in this case there will be because you're selecting one item by its ID) then mysql will not have any other records to sort by.

If you had say three records returned then it would sort them by your order by column asc. Because you have no other records it won't.

Just delete the SORT BY.. part of your clause if you're only going to return one result.

[~Kira]

I understood what he said but thought maybe there was another place I could sort the products. How would I go about fixing this so I can pull all products but still keep the code to get the sizes and all that?

[tangoforce]

Well pulling by one record you can't. You need to rethink the criteria that you're searching your DB by. If you always go for one unique ID then you'll only ever return one item and that doesn't sound like what you're trying to do so you need to have a rethink.

[~Kira]

Yes, but I didn't write this so if I could just pull all the products, then sort, then let the function run I'd be doing well. I guess that's not possible from what you're saying.

[Rowsdower!]

If you're pulling all records and sorting then just remove the entire part about product id and use this:

$dbQuery2 = 'SELECT * FROM 00_products ORDER BY '.$sort_order.' ASC'; 

That is, of course, assuming you have a PHP variable named $sort_order the specifies which field you wish to sort by...

[tangoforce]

But do it OUTSIDE of the loop that its currently in.

[Spookster]

Also it's never easy to debug SQL statements when you try to do it within your code. If you are using MySQL and you have phpMyAdmin available run your query in there first to make sure the query is correct before you mix it into your code so you at least know the query is correct.

So do this <?php echo $dbQuery; ?> to your page and then copy and pasted it into phpMyAdmin and run the query with the values you are using as the criteria. If there is an error with your SQL statement it will tell you. If it does not return what you expected it will be obvious and much easier to tweak the SQL there then in your code.