Join two tables with PHP

Question

Join two tables with PHP

Navigation

#1 by (6 votes)
#2 by (3 votes)

7

I have two tables in the database, one listing the posts made and the other one of the registered users.

Columns that I have in the posts table:

ID || TEXTO || ID_USER ||

Columns that I have in the user table:

ID || NOME_USER || SENHA_USER||

When you make a post, the system takes the ID of the user who is posting and registers the data.

Only having problem to work better with both tables, I actually want to make an association between both tables. Because I want to associate the ID_USER registered in the post table and associate ID with the user name.

I tried to do this:

("SELECT * FROM posts WHERE ID_USER in (SELECT ID FROM users)")

But it did not work.

At the moment the posts displayed look like this:

3 said :: Text that ID 3 user posted

But I want them to look like this:

Carlos said :: Text that ID 3 user posted

This is all PHP that I'm using to display posts:

<?php
$rs = $pdo->query("SELECT * FROM posts ORDER BY ID DESC ")->fetchAll();

$count = count($rs);
if($count == 0){ echo "Nada foi encontrado"; }else{

if(!$rs){
print_r($pdo->errorInfo());
}foreach ($rs as $res)
{   
?>

<?php echo $res['ID_USER'] ?> disse :: <?php echo $res['TEXTO'] ?>

<?php } ?>

php mysql

asked by anonymous 31.08.2014 / 07:31

2 answers

3

INNER JOIN EXAMPLE

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Based on your tables

SELECT * FROM posts
INNER JOIN users ON posts.id = users.id WHERE users.id = 1

QUERY result

array(
    'id'    => '1'
    'nome'  => 'Eu'
    'texto' => 'Meu texto digitado'
)

Avoid using (SELECT *), define the fields you are going to use by declaring: (SELECT ID, NAME ... FROM)

31.08.2014 / 08:18

Transform jQuery code into JavaScript Finding Unknown Heroes in Stack Overflow using Data Explorer

score 6 · Accepted Answer

Make a INNER JOIN of the tables just below:

INNER JOIN definition : when table A has the same code in its relation in a table B. In the tables below the field ID of users is related with field ID_USER of the posts table, where they form a relationship 1 for Many (1 users can have multiple posts or none)

Users Table ( users )

ID || NAME_USER || SENHA_USER ||

Posting Table ( posts )

ID || TEXT || ID_USER ||

SELECT a.ID as USERID, a.NOME_USER, 
       b.ID as POSTID, b.TEXTO
FROM users a INNER JOIN posts b on
      (a.ID = b.ID_USER)

Example ONLINE: SQLFiddle

PHP

<?php
    $pdo = new PDO('mysql:dbname=genericdb;host=localhost', 'root', 'senha', 
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

    $rs = $pdo->query("SELECT a.ID as USERID, a.NOME_USER, b.ID as POSTID, b.TEXTO FROM users a INNER JOIN posts b on
                     (a.ID = b.ID_USER)");
    if ($rs) {                      
        foreach($rs->fetchAll() as $row):
            printf('<p>%s disse :: %s que o usuario de ID %s postou</p>', $row['NOME_USER'], $row['TEXTO'], $row['USERID']);
        endforeach;
    } else {
        var_dump($pdo->errorInfo());
    }