JOIN table MySQL not joining in PHP

Question

I am trying to learn how to use JOIN, INNER JOIN, LEFT JOIN and RIGHT JOIN. I have the following users and generator tables

users
  uid
  username
  password
  email

and

creator
  id
  uid
  g_name
  g_bio

But I cannot join them. I try to JOIN like this :

public function Generator($uid) {
            $g_name=mysql_real_escape_string($uid);
            $query= mysql_query ("SELECT username,g_name,g_bio FROM users JOIN creator ON users.uid = creator.uid");
            $data=mysql_fetch_array($query);

            return $data;
            }

But print_r does not show anything? What is wrong here?

What are your desired results? Are you trying to return all records from one table or just those that match? — user1628449, Jun 06 '14 at 19:50
Error checking is essential. `if (!$query) echo mysql_error();` Otherwise, nothing returned from `mysql_fetch_array()` means no rows returned from the query. I note also that you aren't actually using `$g_name` since you have no `WHERE` clause. — Michael Berkowski, Jun 06 '14 at 19:51
One issue I can see is the missing connection string inside the function so the mysql_query() will not work, your connection string scope is outside the function not inside it. — Abhik Chakraborty, Jun 06 '14 at 19:51
Use users.username, users.g_name, users.g_bio FROM ...YOUR REST QUERY... And also use mysql_query ("...Your query..") or die(mysql_error()); — Subhojit Mukherjee, Jun 06 '14 at 19:52
Make sure user.uid and creator.uid are both integer, i mean on mysql table architecture — Subhojit Mukherjee, Jun 06 '14 at 19:56
@AbhikChakraborty `mysql_query()` will use the last open connection, regardless of scope (which is one of its chief failings, and the biggest problem for new users of the other APIs) — Michael Berkowski, Jun 06 '14 at 19:59
@SubhojitMukherjee If i use only username like `("SELECT username FROM users JOIN creator ON users.uid = creator.uid");` this print_r(); shows me username. But if i add another columnt `g_name` it said error `unknown columnt Generator` — MltHty, Jun 06 '14 at 19:59
Use a loop to get the users, while($res=mysql_fetch_array($query)){print_r($res)} — Subhojit Mukherjee, Jun 06 '14 at 19:59
What you used? Did you used users.username, creator.g_name, creator.g_bio? — Subhojit Mukherjee, Jun 06 '14 at 20:03

score 0 · Accepted Answer · edited May 23 '17 at 12:05

First thing, when you are referencing two tables, you need to specify from which table you are selecting each column from:

SELECT 
    users.username,
    creator.g_name,
    creator.g_bio 
FROM users 
JOIN creator ON users.uid = creator.uid

Second, in your function, you are passing in $uid, and then converting it to $g_name, but not using it anywhere in your query. Assuming you want rows matching this value, you need to add an additional where condition:

WHERE creator.g_name = '$g_name'

Finally, don't use the mysql_*() functions, instead use mysqli or pdo and utilize prepared statements.

score 0 · Answer 2 · answered Jun 06 '14 at 20:01

You escaped function argument $uid to $g_name but you don't use it. May be it the reason of "nothing happened" ? You should rewrite your query that way :

"SELECT username,g_name,g_bio FROM users
    JOIN creator ON users.uid = creator.uid
    WHERE g_name = '$g_name'"

Beyond that I think you better should use PDO and a parameterized queries

JOIN table MySQL not joining in PHP

2 Answers2