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 2 of 2
  1. #1
    New Coder
    Join Date
    Feb 2011
    Posts
    32
    Thanks
    3
    Thanked 0 Times in 0 Posts

    PHP/MySQL Head-Spinning Query - Please Help

    PHP/mysql newb here and I'd appreciate any help.

    I have 3 mysql tables that I'm trying to query via a join query.

    The 3 tables are:

    order

    order_option

    order_product


    Order 4 as seen in shopping cart before going in database looks like this:

    (



    order_id is shared field in all tables
    order_product_id is shared field in order_option and order_product tables.

    In a single query,

    For order_id : 4

    I would like to retrieve invoice_id from table order and,

    I would like to retrieve from tables order_product and order_option the following info in the following manner:

    **************************************************
    PRODUCT: LG 1200 Unlock Code
    Network: U.S.A. - AT&T
    IMEI: 565656565656565
    IMEI: 343434355666666
    Network: U.S.A. - T-Mobile
    IMEI: 767676766766776


    PRODUCT: HTC 2223 Unlock Code
    Network: U.S.A. - SunCom
    IMEI: 383838383839405
    Network: U.S.A. - AT&T
    IMEI: 123456789123456


    ***************************************************

    It's important to note that the value of the field 'name' in table order_option is always either Network or IMEI.


    My current query is shown below:

    SELECT * FROM table.order AS o INNER JOIN table.order_option AS p, table.order_product AS r where o.order_id = p.order_id AND o.order_id = r.order_id";


    With the above query all I get is a list of everything in the tables. That's a lot of unorganized info on a page and not concatenated or bunched together. How do I bunch together and organize the information based on order/product/options as I stated above?

    I'm a newb but I'm thinking concat may be needed as well as possibly distinct in order to organize the options under individual products based on IMEI and Network. (that being said, I'm no specialist and i could be totally off).

    I am really stuck and I'd appreciate help from some of the gurus in here.

    Thanks

  • #2
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    You can't do this
    SELECT * FROM table.order AS o
    and expect to pull things out individually. I would not do use "AS" if not needed.

    Check out this website for learning MySql, they will show you how to "Join" http://sql.learncodethehardway.org/book/

    Here is a Select I did with multiple tables, userID is the "shared field"

    PHP Code:
    $search "SELECT
    user.userID,
    user.first_name,
    user.gender,
    about_me.headline,
    about_me.description,
    pets.dog,
    pets.cat,
    pets.fish

    FROM user
    LEFT JOIN about_me
    ON user.userID = about_me.userID

    LEFT JOIN pets
    ON user.userID = pets.userID

    WHERE user.userID = '$userID'"
    ;
    $result $mysqli->query($search);

    while (
    $r=$result->fetch_assoc()) {
    $userID=$r["userID"];
    $dog=$r["dog"];
    $cat=$r["cat"];

    // put all your code in the 'while' which will make it lope until the array is finished

    echo "$userID has $dog and $cat"// this line will repeat for as many $userID


    Hope this helps


  •  

    Tags for this Thread

    Posting Permissions

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