Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    Jan 2016
    Posts
    13
    Member #
    52917

    Problem joining tables

    I am having trouble joining tables. I am using codeigniter.
    I am just using small example as my table is big.
    I have...
    table 1: author with fields(id, name, image, address, status)
    table 2: books(id, author_id, book_name, image, status)

    I joined using this way.
    $q = $this->db->select('*')->join('books','books.author_id=author.id')->get_where('author',array('status'=>1))->result_array();

    Join is working. But the problem is this is taking the "image" field from "books" table instead of "author".
    I think it is over-writing the image from author and using books image field. And when I change image field name from books table, it works.
    But there will have to lots of changes if I change field name.
    Is there any correct way to do this? I tried left, inner joins but didn't work. I think I am doing it the wrong way.

    Any suggestions is welcome.
    Thank you.

  2.  

  3. #2
    Senior Member sarahswansea's Avatar
    Join Date
    Oct 2015
    Location
    Swansea, UK
    Posts
    152
    Member #
    52184
    Liked
    64 times
    I don't know the exact syntax with PHP but rather than "select *", you can specify the exact tables and columns:

    e.g.

    select field1, field2, author.image

    or

    select field1, field2, books.image AS book_image, author.image AS author_image

  4. #3
    Junior Member
    Join Date
    Jan 2016
    Posts
    13
    Member #
    52917
    Thank you, Sarah for those suggestion.
    Currently, I solved the problem by interchanging books table with author table.
    I am still confused and have been studying on table join. And yeah, specifying required columns is the efficient way of doing it. I will try that.

  5. #4
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,686
    Member #
    5580
    Liked
    716 times
    MySQL queries, especially table joining is something I don't understand very well either. And now, with PDO/MySQLi (data objects), it becomes even trickier.

    If Sarah has a good grasp of MySQLi, then maybe she can be our "go-to" person for MySQLi questions? Sarah?


  6. #5
    Senior Member sarahswansea's Avatar
    Join Date
    Oct 2015
    Location
    Swansea, UK
    Posts
    152
    Member #
    52184
    Liked
    64 times
    Oooh I'll do my best of course, but I'm no means an expert! I only know some of the theory, I haven't used MySQL itself in years and years! But if you have a specific question I'll be happy to try and explain


Remove Ads

Posting Permissions

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

Tags for this Thread

All times are GMT -6. The time now is 05:04 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com