Joining two tables and check if record exist

Question

I'm trying show some records from table and if doesn't exist to show button create new. If exist to show record.

There are two tables - users

user_id
username
...

And restaurants

rest_id
name
menu

So after user is created and he log into his account must have condition if user_id has restaurant user_id = menu (menu from restaurants). If doesn't exist in restaurant show button create. This is the query with which I trying

$q = $pdo->prepare("SELECT * FROM restaurants m                 
          LEFT JOIN users ON users.user_id = m.menu WHERE rest_id = :user_id");

                $q->bindParam(':user_id', $_SESSION['user_id']);
                $q->execute();
                // fetch the results
                $results = $q->fetchAll(PDO::FETCH_ASSOC);
                if(count($results) > 0) {
                    foreach($results as $res) {
                        echo '<a href="users/restaurant.php?rest_id='. $res['rest_id'] .'"> '.$res['name'].' </a>';
                    }
                } else {
                        echo '<a href="users/restAdd.php?rest_id='.$_SESSION['user_id'].'">Create New</a>';
                }

In this way when user login he see button Create New because he doesn't have one. The problem is when he log again after he is created record already ... button Create New is visible again.

I thing the problem is in the query or no?

UPDATE: menu row hold user_id from session when he create new one. This is in restAdd.php

    $sql = "INSERT INTO restaurants ( name, menu, image) VALUE ( :name, :menu, :image)";
                    $q = $pdo->prepare($sql);
                $q->execute(array(
                ':name'     => $name,
                ':menu'     => $_SESSION['user_id'],
                ':image'    => $forDB
                ));

Check again your query, "users.user_id = m.menu" ??? Is this correct? If so, explain please — javier_domenech, Jan 13 '15 at 14:09
Well don't I need to check join user_id from `users` and menu from `restaurants`? — Jason Paddle, Jan 13 '15 at 14:11
Pro tip: Don't use `SELECT *` especially in `JOIN` queries. Instead, enumerate the columns you need returned. Also, we can't tell from your question how exactly each row in your `restaurants` table relates to a row in your `users` table. Can you explain? — O. Jones, Jan 13 '15 at 14:12
You want a relation 1user:Nrests, so you need a field in the table Restaurants pointing to table User. This field is what you need for that join, I doubt menu contains user id, what does contain menu field? — javier_domenech, Jan 13 '15 at 14:15

javier_domenech · Accepted Answer · 2015-01-13T14:35:32.713

1

Try with this query:

SELECT m.rest_id, m.name FROM restaurants m                 
  INNER JOIN users ON users.user_id = m.menu
  WHERE menu = :user_id

1) INNER JOIN instead of LEFT, you want to check if there are restaurants related with the user.

2)I think you did a little mistake, the menu field is wich contains the user id

edited Jan 13 '15 at 14:35

answered Jan 13 '15 at 14:24

javier_domenech

5,995
6
37
59

Thank's for your help. I guess I need to learn differences between LEFT, RIGHT, INNER and OUTER joins. – Jason Paddle Jan 13 '15 at 14:48
1

You're welcome. Here is one of my favourite answers of SO, check it. http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins – javier_domenech Jan 13 '15 at 14:52

score -1 · Answer 2 · answered Jan 13 '15 at 14:24

-1

In this case you need in your where clause menu = :user_id not rest_id = :user_id Try with this:

SELECT * FROM restaurants m                 
    LEFT JOIN users ON users.user_id = m.menu WHERE menu = :user_id"

answered Jan 13 '15 at 14:24

Goro

499
1
13
31

Joining two tables and check if record exist

2 Answers2