CODEWITHSUNDEEP
phpmysqlselect
Cubi73
Cubi73

Reputation: 1951

Trouble with query with two tables

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.

Upvotes: 0

Views: 44

Answers (3)

scrowler
scrowler

Reputation: 24405

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:

Upvotes: 2

Bit_hunter
Bit_hunter

Reputation: 809

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

Upvotes: 0

Bartosz Polak
Bartosz Polak

Reputation: 136

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.

Upvotes: 1

Related Questions