Web Analytics Made Easy -
StatCounter ORDER BY not working - CodingForum

Announcement

Collapse
No announcement yet.

ORDER BY not working

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Resolved 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; Aug 26, 2011, 02:13 PM.

  • #2
    Originally posted by ~Kira View Post
    What am I doing wrong?
    Why don't you ask mysql_error() ?
    "Tango says double quotes with a single ( ' ) quote in the middle"
    '$Name says single quotes with a double ( " ) quote in the middle'
    "Tango says double quotes ( \" ) must escape a double quote"
    '$Name single quotes ( \' ) must escape a single quote'

    Comment


    • #3
      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.

      Comment


      • #4
        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;
        }

        ?>

        Comment


        • #5
          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.
          "Tango says double quotes with a single ( ' ) quote in the middle"
          '$Name says single quotes with a double ( " ) quote in the middle'
          "Tango says double quotes ( \" ) must escape a double quote"
          '$Name single quotes ( \' ) must escape a single quote'

          Comment


          • #6
            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?

            Comment


            • #7
              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.
              "Tango says double quotes with a single ( ' ) quote in the middle"
              '$Name says single quotes with a double ( " ) quote in the middle'
              "Tango says double quotes ( \" ) must escape a double quote"
              '$Name single quotes ( \' ) must escape a single quote'

              Comment


              • #8
                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.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    But do it OUTSIDE of the loop that its currently in.
                    "Tango says double quotes with a single ( ' ) quote in the middle"
                    '$Name says single quotes with a double ( " ) quote in the middle'
                    "Tango says double quotes ( \" ) must escape a double quote"
                    '$Name single quotes ( \' ) must escape a single quote'

                    Comment


                    • #11
                      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
                      CodingForum Supreme Overlord
                      All Hail Spookster

                      Comment

                      Working...
                      X