0

I have following two tables (here with random data)

 cu                         cgm
+-----+---------+-----+    +---------+--------+
| uid | name    | ... |    | groupid | userid |
+-----+---------+-----+    +---------+--------+
|   1 | manfred | ... |    |       2 |      2 |
|   2 | peter   | ... |    |       2 |      5 |
|   3 | jackson | ... |    |       2 |      7 |
| ... |  ...    | ... |    |       4 |      3 |
+-----+---------+-----+    +---------+--------+
  uid <========= FOREIGN KEY ========== userid

I want to select cu.name and cu.uid / cgm.userid by cgm.groupid and put the values into an associative array where the keys are cu.name and the values cu.uid / cgm.userid. My only solution was to send a query to select cgm.userid by cgm.groupid, send a second query to select cu.name by cu.uid and put the data together. But I was wondering, if there is way to achieve this with only one query and use PHP as less as possible.

Cubi73
  • 1,891
  • 3
  • 31
  • 52

3 Answers3

2

Your SQL with a LEFT JOIN could be:

SELECT cu.name, cu.uid, cgm.userid, cgm.userid, cgm.groupid
FROM cu
LEFT JOIN cgm ON cu.uid = cgm.userid
WHERE cgm.groupid = 2 -- your var there

Although you don't need to select cgm.userid in this case as it should be the same as cu.uid (if it's not, this join isn't correct).

With PHP:

$group_id = 2;
$sql = "SELECT ..... WHERE cgm.groupid = " . $group_id;
$query = $db->query($sql); // your query functions here...

To help you avoid SQL injection, your should bind $group_id as a parameter:

$sth = $db->prepare("SELECT ... WHERE cgm.groupid = :group_id");
$sth->bindParam(':group_id', $group_id, PDO::PARAM_INT); // only accept integers
$sth->execute();

More info:

Community
  • 1
  • 1
scrowler
  • 24,273
  • 9
  • 60
  • 92
1
SELECT cu.name, cu.uid FROM cu LEFT JOIN cgm ON cu.uid = cgm.userid WHERE cgm.groupid = 2

You can adjust WHERE condition to your needs.

Bartosz Polak
  • 136
  • 6
  • Thanks you. But I don't fully understand `ON`. Is it a condition for connecting the datasets from `cu` with `cgm`? – Cubi73 Feb 10 '14 at 21:23
  • it is the equivalent of `WHERE x = y` but for the join - e.g. `join cgm ON cu.uid = cgm.userid -- equiv of WHERE cu.uid = cgm.userid` – scrowler Feb 10 '14 at 21:25
  • By JOIN you tell MySQL to attach another table to the results and by ON you tell exact how to do this, so ON tells how to bind data from first dataset with those in secont one. It's short explanation but you really should go to link that @scrowler provided so you can understand joining tabels better. – Bartosz Polak Feb 10 '14 at 21:31
0

Yes you can do it by using join. Use below query.

select uid,name from cu,cgm where uid=userid and groupid =<GROUP_ID>;

You can use PDO to connect your database.

$conn= new PDO('mysql:host=localhost;dbname=chatting4u','root','');
    $stmt=$conn->prepare('select uid,name from cu,cgm where uid=userid and groupid =<GROUP_ID>');
    $stmt->execute();
    $row=$stmt->fetchall(PDO::FETCH_ASSOC);

Now you will get uid, uname in $row array. Try it,i hope it will work.

References:-

Databse Joins-> http://www.w3schools.com/sql/sql_join.asp

PDO in PHP-> http://www.php.net/manual/en/book.pdo.php

Bit_hunter
  • 789
  • 2
  • 8
  • 25