Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Oct 2009
    Posts
    31
    Thanks
    6
    Thanked 0 Times in 0 Posts

    ORDER BY not working

    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?
    Last edited by ~Kira; 08-26-2011 at 06:13 PM.

  • #2
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,343
    Thanks
    60
    Thanked 527 Times in 514 Posts
    Blog Entries
    4
    Quote Originally Posted by ~Kira View Post
    What am I doing wrong?
    Why don't you ask mysql_error() ?
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #3
    Regular Coder
    Join Date
    Jan 2009
    Posts
    193
    Thanks
    0
    Thanked 20 Times in 20 Posts
    PHP Code:
    $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.

  • #4
    New Coder
    Join Date
    Oct 2009
    Posts
    31
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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 Code:
    <?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;
    }

    ?>

  • #5
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,343
    Thanks
    60
    Thanked 527 Times in 514 Posts
    Blog Entries
    4
    Quote Originally Posted by ~Kira View Post
    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.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #6
    New Coder
    Join Date
    Oct 2009
    Posts
    31
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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?

  • #7
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,343
    Thanks
    60
    Thanked 527 Times in 514 Posts
    Blog Entries
    4
    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.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #8
    New Coder
    Join Date
    Oct 2009
    Posts
    31
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Senior Coder Rowsdower!'s Avatar
    Join Date
    Oct 2008
    Location
    Some say it's everything.
    Posts
    2,027
    Thanks
    5
    Thanked 397 Times in 390 Posts
    If you're pulling all records and sorting then just remove the entire part about product id and use this:
    PHP Code:
    $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...
    The object of opening the mind, as of opening the mouth, is to shut it again on something solid. –G.K. Chesterton
    See Mediocrity in its Infancy
    It's usually a good idea to start out with this at the VERY TOP of your CSS: * {border:0;margin:0;padding:0;}
    Seek and you shall find... basically:
    validate your markup | view your page cross-browser/cross-platform | free web tutorials | free hosting

  • #10
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,343
    Thanks
    60
    Thanked 527 Times in 514 Posts
    Blog Entries
    4
    But do it OUTSIDE of the loop that its currently in.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #11
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •