Mysql - SELECT columns from 2 tables using INNER JOIN error

Question

This should be a basic question, but I haven't used Mysql for a very long time and forgot all the basic stuff. So SO programmers please bear with me.

I have 2 tables like this:

Table 1 (events): here

Table 2 (users): here

I would like to select all rows in the events table where event_invitees contains a username. I was able to do this using:

SELECT * FROM meetmeup_events WHERE event_invitees LIKE '%$username%'

Now I'd like to also select the event_invitees's photo from the users table (column called user_userphoto). My attempt to this was this:

$result = mysql_query("SELECT meetmeup_events.*, meetmeup_user.user_photo 
                       FROM meetmeup_events 
                       WHERE event_invitees LIKE '%$username%' 
                       INNER JOIN meetmeup_user 
                          ON meetmeup_user.user_username = meetmeup_events.event_inviter");

$rows = array();
   while($r = mysql_fetch_assoc($result)) {
     $rows['meetmeup_user'][] = $r;
   }

 echo json_encode($rows);

This gave me an error: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

How can I do this? What am I missing? Can you give me some examples?

Thanks in advance! I'll be sure to accept the working answer!

Your query syntax is out of order. The `WHERE` clause must come after the `FROM` clause and all `JOIN`s. Be sure to check errors: `if (!$result) echo mysql_error();` after the `mysql_query()` call. — Michael Berkowski, Sep 03 '14 at 16:07
Side note: See also [Why shouldn't I use mysql_ functions in PHP](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Michael Berkowski, Sep 03 '14 at 16:09
@MichaelBerkowski By doing what you said. The error disappeared but the json is empty. Are there more errors in mysql query ? — BuddyBud, Sep 03 '14 at 16:10
If the JSON is empty, that would imply no rows were returned. Test out the query in a MySQL client, separated from your PHP application code to make sure you can get it to return results. I don't see anything else faulty in it. — Michael Berkowski, Sep 03 '14 at 16:13
Debug by removing the `WHERE` clause entirely, to get the join to work, then test with a known good username. Perhaps the variable `$username` doesn't contain the expected value. — Michael Berkowski, Sep 03 '14 at 16:14
If there were more errors in your query, you would get the same error you were previously getting. Remember that, because you are using an inner join, if there are no records that match the join condition, no records will be returned at all. — Don't Panic, Sep 03 '14 at 16:14
@MichaelBerkowski Thankyou for the help. I tried doing what you said. When I remove the WHERE, it works. The $username does contain the value because I tried using the same username before without using INNER JOIN and it worked. What is this missing? — BuddyBud, Sep 03 '14 at 16:29
@BuddyBud There must be no match. But looking at the one sample row in your image, it is really not recommended to store JSON as a string then try to `LIKE` against it. Similar usernames, like `john` and `johnsmith` would mean a search for `john` matches both. It is necessary to create a third joining table which just matches users to events — Michael Berkowski, Sep 03 '14 at 16:33
@MichaelBerkowski Right... With your example I now understand why I'm not suppose to use LIKE. Thankyou for your help, I'll try figure out why there is no match. God bless you Mike. Thanks so much! — BuddyBud, Sep 03 '14 at 16:38

RiggsFolly · Answer 1 · 2014-09-03T21:29:42.643

Its just a matter of getting the query coded in the correct order, and you might like to make it a little more managable by using alias's for the table names

Try this :-

SELECT me.*, 
       mu.user_photo 
FROM meetmeup_events me
   INNER JOIN meetmeup_user mu ON mu.user_username = me.event_inviter
WHERE me.event_invitees LIKE '%$username%'

This of course assumes that all the column names are correct and the mu.user_username = me.event_inviter does in fact make sence because those fields are in fact equal

Additional Suggestion You are not actually issuing the query for execution by mysql.

You have to do this :-

$sql = "SELECT me.*, 
           mu.user_photo 
        FROM meetmeup_events me
           INNER JOIN meetmeup_user mu ON mu.user_username = me.event_inviter
        WHERE me.event_invitees LIKE '%$username%'";

$result = mysql_query($sql);

$rows = array('mysql_count' => mysql_num_rows($result) );

while($r = mysql_fetch_assoc($result)) {
    $rows['meetmeup_user'][] = $r;
}

echo json_encode($rows);

Now in your browser using the javascript debugger look at the data that is returned. There should at least be a mysql_count field in it even if there is no 'meetmeup_user' array, and if it is zero you know it found nothing using your criteria.

Thanks for the answer. I tried you code and the error went away. But I still could not understand why the JSON returns empty? Can you please help me further ? — BuddyBud, Sep 03 '14 at 16:30

lpg · Answer 2 · 2014-09-03T16:27:23.497

0

You should change your mysql functions to either mysqli / PDO, although the problem seems to be the query itsef. Should be:

SELECT meetmeup_events.*, meetmeup_user.user_photo 
FROM meetmeup_events 
INNER JOIN meetmeup_user 
ON meetmeup_user.user_username = meetmeup_events.event_inviter
WHERE event_invitees LIKE '%$username%'

(the WHERE clause at the end)

Sql fiddle demo: http://sqlfiddle.com/#!2/852a2/1

edited Sep 03 '14 at 16:27

answered Sep 03 '14 at 16:21

lpg

4,897
1
16
16

Thanks for the answer. The error went away, but somehow the JSON returns empty. What am I missing? – BuddyBud Sep 03 '14 at 16:30

Mysql - SELECT columns from 2 tables using INNER JOIN error

2 Answers2