SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Understanding this MySQL Query

    I've been going through all my code adding comments, when all of sudden I came across a MySQL query which I didn't quite understand (and it's in my code). I know what it does, but I don't know how it works. The actual query was partly written by someone here at sitepoint a couple of months back.

    Basically, I've got a query which reads data from the 'cart' table to get a list of all the items in the users shopping cart. After that, I've got a query which gets additional information about the product. The products in this shop I'm developing have multiple weights to choose from, and so I need to store the selected weight in the database. But what happens if the weights of the product changes after a user had already added a product with the old weight to their cart. This is the part of the query I don't quite understand. The query goes something like this: If the product weights have been changed since the user added the product, then the query returns the closest matching 'new weight' to the top of the results. The result at the top of the results list is the only row which is used so it's important the ordering is correct.

    If you're lost, then don't worry, here's the code

    Code PHP:
    $product_info = mysqli_fetch_array(mysqli_query($mysql, "SELECT products.product_id, products.title, products.active, product_attributes.price, product_attributes.weight, categories.active as cat_active FROM products, product_attributes, categories WHERE products.product_id = '".$cart_contents['product_id']."' AND product_attributes.product_id = products.product_id AND categories.cat_id = products.cat_id ORDER BY ABS(product_attributes.weight - ".$cart_contents['weight'].") ASC LIMIT 1"));

    The ABS function is the magic part of the query. It uses the weight stored in the cart database ($cart_contents['weight']) and does some funky stuff with the weights stored in the product database. For a better understanding of what it does, if the product in the cart has a weight of 600 grams, but that product no longer has that weight available and the nearest weight to the old 600 gram weight is the new 500 gram weight, then the query will order the results so that new 500 gram weight is at the top, and therefore, the weight returned from the cart table (the old 600 gram weight) is changed to 500 grams (the actual data in the cart table isn't changed, only the result is).

    I just don't get how this query does that , hence the point of this topic which for some reason has taken up a good 4 paragraphs already . I know that ABS(product_attributes.weight - ".$cart_contents['weight'].") is responsible, but I still don't understand how it works. What does product_attributes.weight - ".$cart_contents['weight']." equate to anyway? how can you order products using an integer? (if that's what it is doing).

    Can anyone here enlighten me on how this works

    Cheers!, and sorry for the long post.

  2. #2
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ABS() returns the absolute value. That is, if the value is negative, it returns the value * -1, otherwise just the value. In layman terms, it strips off the negative sign (If any). It's used to determine the difference between two values.

    The query selects a number of rows, equal to product * product_attributes. It is then ordered by the difference to $cart_contents['weight'] and only the first result is returned. In other words, you get the row, which is closest to the $cart_contents['weight'] (Equal to being the closest of course).

    Hope that helped.
    Since the query is ordered by

  3. #3
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did help, but I'm still wondering about a few things. Does the ABS() function (including the subtract calculation inside it) get processed as the result (as an integer). So if the result of ABS(product_attributes.weight - ".$cart_contents['weight'].") came to 150, would I get the same result if I just replaced the whole ABS function with the integer '150'.

    The thing that's really confusing me here is, how does MySQL know that I'm wanting to apply that ABS() function to the product_attributes.weight?

  4. #4
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's calculated for each result.

    If you removed the limit 1 part of your query, you would get a number of results. These results are ordered by the expression in the order by clause -- Eg. the expression ABS(product_attributes.weight - ".$cart_contents['weight']."). At the time, where the query is sent to MySql, $cart_contents['weight'] has been replaced with a constant. You can try echo'ing the query out, to see what is sent to MySql.

  5. #5
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I echo out a query?


Bookmarks

Posting Permissions

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