SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL SELECT query not returning wanted result

    Hey, everyone!

    I ran into a strange issue. I have a mysql price table. I have several products available in several quantities. There are also different price levels that are assigned to different customers for the same product. The customer table has a price level assigned to each customer. This is what the price table looks like:

    pricelevel..productID....250.....500....1000
    .....1..........AAAAA.....10.00...20.00..30.00
    .....1..........XXXXX.....15.00...30.00..45.00
    .....1..........ZZZZZ.....20.00...40.00..60.00
    .....2..........AAAAA.....15.00...30.00..45.00
    .....2..........XXXXX.....20.00...40.00..60.00
    .....2..........ZZZZZ.....30.00...60.00..90.00

    Often I need to fill in the order manually when clients order via email/phone/in person. On the order form I fill in the product code, quantity, and the customer who orders it. When I submit the form through PHP, I find out the price level of the customer who I selected. Based on their $pricelevel, $productID, and $quantity, I am attempting to get the price from the above table with the following code:

    $pricequery1 = "SELECT ".$quantity." FROM pricetable WHERE pricelevel='$pricelevel' AND productID='$productID'";
    $pricequery2 = mysql_query($pricequery1) or die(mysql_error());
    $pricequery3 = mysql_fetch_array($pricequery2);
    $price = $pricequery3['$quantity'];

    This returns blank. At first I thought it was syntax, but nothing I changed helped. I even went as far as to say:

    $pricequery1 = "SELECT '500' FROM pricetable WHERE pricelevel='2' AND productID='AAAAA'";
    $pricequery2 = mysql_query($pricequery1) or die(mysql_error());
    $pricequery3 = mysql_fetch_array($pricequery2);
    $price = $pricequery3['500'];

    Depending on whether I put in quotes or not in the last line (around 500) it either returns a blank or the number 500. The query '$pricequery1' on pricetable through phpMyAdmin returns the appropriate price. I checked and all the values ($quantity, $pricelevel, and $productID) get passed on properly from the order form.

    Any suggestions?

    Thank you!

    Victor

  2. #2
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is 500?

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    '500' is the name of the column that corresponds to the quantity of the product being ordered.

    Victor

  4. #4
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The use backticks around it e.g.

    PHP Code:
    $pricequery1 "SELECT `".$quantity."` FROM pricetable WHERE pricelevel='$pricelevel' AND productID='$productID'"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you should normalize your table:
    Code:
    pricelevel  productID  maxvalue  price
     1          AAAAA      250       10.00
     1          AAAAA      500       20.00
     1          AAAAA      1000      30.00
     1          XXXXX      250       15.00
     1          XXXXX      500       30.00
     1          XXXXX      1000      45.00
     1          ZZZZZ      250       20.00
     1          ZZZZZ      500       40.00
     1          ZZZZZ      1000      60.00
     2          AAAAA      250       15.00
     2          AAAAA      500       30.00
     2          AAAAA      1000      45.00
     2          XXXXX      250       20.00
     2          XXXXX      500       40.00
     2          XXXXX      1000      60.00
     2          ZZZZZ      250       30.00
     2          ZZZZZ      500       60.00
     2          ZZZZZ      1000      90.00

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2007
    Location
    Kelowna, BC
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for advice!

    I tried mwasif's advice first and it worked. .

    longneck, are there other advantages to normalizing the table?

    For anyone else having the same/similar issue, the solution did not work right away, since there are several other lines of code. The query was perfect:

    PHP Code:
    $pricequery1 "SELECT `".$quantity."` FROM pricetable WHERE pricelevel='$pricelevel' AND productID='$productID'"
    then to actually get the price:

    PHP Code:
    $pricequery2 mysql_query($pricequery1) or die(mysql_error());
    $pricequery3 mysql_fetch_array($pricequery2);
    $price $pricequery3[$quantity]; 
    note no backticks/quotes around $quantity in last line.

    Maybe it is obvious to some, but I had them there and the result was once again a blank. Removing them made it all work well.

    Again, thank you all for the help!

    Victor


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
  •