#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    3

    PHP Join Statement Issue


    When I use the following PHP/SQL statement, I am returning only the data from the services table, while I'm wanting to return the user data, and for the service value it will be the name of the service. Hope that wasn't too confusing.
    (I'm also using Laravel for those areas that look funky or different)

    PHP Code:
    DB::raw("SELECT services.* FROM user_services
    JOIN services ON user_services.service_id = services.id
    WHERE user_services.user_id = '1') 
    Here is the users_services table:


    Here is the services table:


    Thanks for any help!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    58
    Rep Power
    2
    Try this instead:
    PHP Code:
    DB::raw("SELECT services.id, services.type, user_services.id, 
    user_services.user_id, user_services.service_id, user_services.active 
    FROM user_services
    JOIN services ON user_services.service_id = services.id
    WHERE user_services.user_id = '1'"

    Before, you did services.*, which will get all fields from the services table.

    Alternatively, you can do
    PHP Code:
    DB::raw("SELECT * 
    FROM user_services
    JOIN services ON user_services.service_id = services.id
    WHERE user_services.user_id = '1'"

    Comments on this post

    • Jacques1 disagrees : Please no stupid "*" wildcard. It's inefficient, unclean and potentially dangerous.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    this is a pure (My)SQL question.

    Anyway, you seem to be confused about the SELECT part. That's where you need to specify output columns. And you're missing the second JOIN for the user table.

    Since you didn't even mention the user table, I'll assume it's called "users" with a column "name". So you have three relevant tables: users for holding the user data, user_services for assigning users to services and services for holding the, well, service data. That's two joins:

    sql Code:
    SELECT
    	users.id AS user_id
    	, users.name AS user_name
    	, services.TYPE AS service_type
    FROM
    	users
    	JOIN user_services
    		ON users.id = user_services.user_id
    	JOIN services
    		ON user_services.service_id = services.id
    WHERE
    	users.id = 1
    ;

    Note that an inner join (like above) will kick out all users that don't have an associated service. If you don't want that, you need a left join, and you have to deal with NULL values.

    By the way, the id column in your user_services table is useless as (user_id, service_id) is already a perfect primary key. Consider removing the synthetic primary key.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    I had feared that somebody would bring up "SELECT *"...

    Do not use this.

    This will dump all columns into memory, leading to all kinds of issues. First of all, it's a waste of memory, because you probably only need certain columns. Secondly, it's potentially dangerous, because you may unwillingly fetch sensitive data (password hashes, email addresses etc.). Third, it leads to duplicate column names, which causes trouble in PHP. In your case, you have three columns named id. How is PHP supposed to know which one you want when you say $row['id']? And last but not least, it's simply unclean, because nobody knows what columns this query actually selects. This makes the code horrible to work with.

    Always specify the columns explicitly. It may be a bit more to write, but it will save you a lot of trouble. When you specify the columns, you can also alias them if necessary (see above).
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    3
    Hello! Thank you all for your (fast) responses.

    -> Jacques1
    My bad for the question type. I forgot to mention I was going to have to put it into a foreach. I don't exactly understand these joins. Also I attached the image for the users table, as it may help me understand it if you can change those so they would work, but I still don't know if they would work with the foreach. Also those two fields are there (and also the id) because the first main 'id' is the numbered value that will be used for editing. The second id is for the user that it corresponds too, while the last one is the one that corresponds to the service that they have. Hope that makes sense, and I'm doing it the *right* way.


    -> aysiu
    The first option did not work with the foreach statement (that I forgot to mention), while the second (dangerous) option works, but the original id of the user_service is now the id of the services table (which I would need the user_service one).



    Thanks again!
    Last edited by tjswebdev; November 29th, 2013 at 11:36 PM.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    I hope that's not your real password you gave us?

    Please wipe the "SELECT *" from your memory. This is never an option (except maybe for a quick debugging session). For example, you've just loaded the password hash into memory, even if it has absolutely nothing to do with your task. The ID issue is also a symptom of the "*" (as explained above).

    As to the query: If you want me to write down the exact columns, I need to know what columns you want. In your original post, you said you want the user data. But now it looks like you only want the data from services and user_services. So what are the exact columns?

    There's also an issue with your foreach loop. Obviously, you try to access $row['id'] at some point. This is ambiguous (as you just saw). Use an unambigious name like "user_id" instead. If the original column doesn't have that name, you can specify an alias in the query.

    Either way, you definitely need to learn SQL. This is crucial for writing (proper) web applications, and we can't always do it for you. Be careful with online tutorials, though. A lot of them are awful. I'd rather buy a book.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    3
    Thanks. I went ahead and changed my query to:
    PHP Code:
    SELECT serv_idtypedomainactive  
    FROM user_services 
    JOIN services ON user_services
    .service_id services.id 
    WHERE user_services
    .user_id '$client_ID' 
    It works correctly now. As for the renaming of the id, what was the point of doing that? Why wouldn't it just work with 'id'?

    Thanks!
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    I have no idea where the "serv_id" and the "domain" columns come from (they were in none of your pictures), but if you say it's correct, I guess it is.

    Next time, however, you should be more specific and give us all information. We can't read minds, so all we know about your system and your task is what you tell us.



    Originally Posted by tjswebdev
    It works correctly now. As for the renaming of the id, what was the point of doing that? Why wouldn't it just work with 'id'?
    As you saw in #5, it does not work to well.

    Apart from that, the point is to make the code human-readable. Programming is not only about making the code "work" somehow. That's actually the easiest part. The code also needs to be easy to understand. If it's not, it will be error-prone, inflexible, and it will waste many working hours during its lifetime.

    Sure, the key "id" will "work" in the sense that PHP doesn't complain about it. But how is a human supposed to know which ID you mean? The user ID? The service ID? The ID of the association? So everytime somebody has to work with your code, they have to look up the query to see what you mean. This is also the perfect source for difficult bugs.

    So unless you wanna p*ss off your fellow programmers and your future self, you shouldn't do it. Always choose meaningful names. Don't forget: Code gets written once, but it's read many times.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0
    Originally Posted by Jacques1
    I hope that's not your real password you gave us?

    Please wipe the "SELECT *" from your memory. This is never an option (except maybe for a quick debugging session). For example, you've just loaded the password hash into memory, even if it has absolutely nothing to do with your task. The ID issue is also a symptom of the "*" (as explained above).

    As to the query: If you want me to write down the exact columns, I need to know what columns you want. In your original post, you said you want the user data. But now it looks like you only want the data from services and user_services. So what are the exact columns?

    There's also an issue with your foreach loop. Obviously, you try to access $row['id'] at some point. This is ambiguous (as you just saw). Use an unambigious name like "user_id" instead. If the original column doesn't have that name, you can specify an alias in the query.

    Either way, you definitely need to learn SQL. This is crucial for writing (proper) web applications, and we can't always do it for you. Be careful with online tutorials, though. A lot of them are awful. I'd rather buy a book.
    Thanks for giving nice solution.

IMN logo majestic logo threadwatch logo seochat tools logo