Results 1 to 5 of 5

Thread: MySQL Left Join

  1. #1
    Join Date
    Jan 2005
    UK, London

    Question MySQL Left Join


    I have two tables on my database:

    - id
    - product_name
    - supplier_dropship_cost_price
    - dropship_addon_percent
    - supplier_bulkbuy_cost_price
    - bulkbuy_addon_percent
    - min_bulkbuy_quantity

    - id
    - account_id
    - product_id
    - type
    - pack_of
    - amount

    I want to show a list of items in the logged in user's cart (if any). I tried to retrieve the desired data by using the following query:

    PHP Code:
    $query  "";
    $query .= "SELECT ";
    $query .= "`my_cart`.`id`, `my_cart`.`account_id`, `my_cart`.`product_id`, `my_cart`.`type`, `my_cart`.`pack_of`, `my_cart`.`amount` ";
    $query .= "`product_list`.`supplier_dropship_cost_price`, `product_list`.`dropship_addon_percent`, `product_list`.`supplier_bulkbuy_cost_price`, `product_list`.`bulkbuy_addon_percent`, `product_list`.`min_bulkbuy_quantity`, `product_list`.`product_name` ";
    $query .= "FROM `my_cart` ";
    $query .= "LEFT JOIN `product_list` ON `product_list`.`id` = `my_cart`.`product_id` ";
    $query .= "WHERE ( ";
    $query .= "`my_cart`.`account_id` = %d";
    $query .= ")"
    When i ran the query in phpMyAdmin to test if it was correct and i got this error:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`supplier_dropship_cost_price`, `product_list`.`dropship_addon_percent`,' at line 1
    What im i doing wrong here? Thanks in advance for any help/tip.

  2. #2
    Join Date
    Oct 2002
    State of Disbelief

    That looks like a field in a table? You're concatenating it so there's no space and no ".". It's being seen as "`product_list``supplier_dropship_cost_price`"

    Or maybe not. Not my strong point, mySQL.
    Last edited by bear; 10-05-2009 at 02:24 PM.
    Having problems, or maybe questions about WHT? Head over to the help desk!

  3. #3
    Join Date
    May 2008
    Rutherford, NJ
    there is no comma after mycart.amount
    you should not use appends to build this, it leaves room for error and makes the sql debugger see it all as one line, so it will always say line 1. in php, queries should look like:

    $query="SELECT blah, blah,
    FROM table
    JOIN other_table ON blah
    WHERE condition=blah
    GROUP BY stuff, ORDER BY stuff

    rather than using all of those .= statements.
    Database design and performance optimization, custom PHP scripts, and publicly available resources for developers!

  4. #4
    Join Date
    Jan 2005
    UK, London
    thanks allot aradapilot, i forgot a comma lol.

  5. #5
    Join Date
    Mar 2009
    As you're coding PHP anyway, you might as well get the rows from the shopping cart, and then for each row get the relevant fields from the product table.

    Using joins for this kind of thing seems horrendous.

    Am I missing something?
    Last edited by tim2718281; 10-06-2009 at 03:05 AM.

Similar Threads

  1. Left and right join?
    By Julian Jorgensen in forum Programming Discussion
    Replies: 1
    Last Post: 10-02-2008, 04:30 PM
  2. LEFT JOIN & SUM Help Needed
    By JonathanSA in forum Programming Discussion
    Replies: 2
    Last Post: 04-22-2007, 05:46 PM
  3. SQL LEFT JOIN problem
    By blunder in forum Programming Discussion
    Replies: 9
    Last Post: 04-16-2007, 04:11 PM
  4. LEFT JOIN question...
    By seodevhead in forum Programming Discussion
    Replies: 2
    Last Post: 10-18-2006, 07:41 PM
  5. mysql, left join
    By getbusy in forum Programming Discussion
    Replies: 4
    Last Post: 07-23-2004, 01:38 PM

Posting Permissions

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