Merge two tables with PHP

Asked

Viewed 2,817 times

7

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

Columns I have in the table of posts:

ID || TEXTO || ID_USER ||

Columns I have in the user table:

ID || NOME_USER || SENHA_USER||

When making a post any system picks up the ID of the user who is posting and logging the data.

Only that I have trouble working better with both tables, actually I want to make an association between both tables. Because I want to associate the ID_USER registered in the table of posts and associate the ID to the user’s name.

I tried to do that:

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

But it didn’t work.

At the moment the posts displayed are like this:

3 said :: Text ID 3 user posted

But I want them to stay that way:

Carlos said :: Text ID 3 user posted

This is all PHP 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

  • inner join wouldn’t it be better? Put the PHP you have

  • I read about Ner, but I can’t apply it correctly. php is very simple. I’ll edit the question and put the php that I’m using to display

  • 1

    [code review] Ivan, code indentation is essential to make logic clear and easy to understand. The functions printf and sprintf are perfect to output small chunks of HTML and not have to keep opening and closing PHP. I would rewrite the code like this: http://pastebin.com/kYjPq3iV

2 answers

6


Make a INNER JOIN of the tables just below:

Definition of INNER JOIN: when table A has in a table B the same code of its relation. In the tables below the field ID of users if relates field ID_USER from the posts table, where they form a relationship 1 for Many (1 users may have multiple posts or none)

Table of Users (users)

ID || NOME_USER || SENHA_USER||

Table of Posts (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)

ONLINE EXAMPLE: 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());
    }

inserir a descrição da imagem aqui

  • ++excellent answer++ . . . . To be perfect, for an ignorant SQL like me, a small explanation of what is happening here INNER JOIN posts b on (a.ID = b.ID_USER) would be great

  • @brasofilo I’ll put the explanation already ... is that I found another question that the answers are not very favorable.

  • I put an excellent reference @brasofilo I think it helps a lot the understanding.

  • The idea is that an answer in [if] is self-sufficient and that I don’t need to go elsewhere to understand. Of course links to documentation also increase, and much, the quality of the post. Anyway, I find your answer a (almost) perfect example of a Canonical Answer [Edit: updated this link].

3

INNER JOIN EXAMPLE

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

Based on their tables

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

Result of QUERY

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


Avoid using (SELECT *), set the fields you will use by declaring: (SELECT ID , NAME ... FROM)

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.