MYSQL tables with same column name

Question

Fetching and print results from multiple tables that have same column name. Here's my query: I am trying to fetch the column salt from login and cookie table.

SELECT login.salt
     , cookie.salt 
  FROM login
     , cookie 
 WHERE login.user_id 
   AND cookie.user_id = :user_id`

login
+---------------------------+
| user_id |       salt      |
+---------+-----------------+
| 1       |    fromLogin    |
| 2       |    fromLogin    |

cookie
+---------------------------+
| user_id |       salt      |
+---------+-----------------+
| 1       |    fromCookie   |
| 2       |    fromCookie   |

The result was:

Array ( [salt] => fromCookie )

How can i print both result out?

PHP

$user_id = 1;

$sql = "SELECT login.salt, cookie.salt FROM login, cookie WHERE login.user_id AND cookie.user_id = :user_id";

$params = array(
        'user_id' => $user_id
        );

$stmt = $db->justConnect()->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

print_r($result);

If i have to fetch salt from maybe 100 tables, so I just do like `s1`, `s2`, `s3` ... — Jimmy, Apr 28 '16 at 13:02
Start with a good (and fairly current) book or tutorial on PHP and MySQL. This is basic stuff. And if you have 100 tables, something's gone very, very wrong. — Strawberry, Apr 28 '16 at 13:03
@Jimmy "WHERE login.user_id AND cookie.user_id = :user_id" just curious if its correct syntax? — Atif, Apr 28 '16 at 13:05
Yes, It turn out just fine. If i have more tables, i wouldn't do this. — Jimmy, Apr 28 '16 at 13:05

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

Use alias in mysql

 $sql = "SELECT login.salt as s1, cookie.salt as s2 
         FROM login, cookie 
         WHERE login.user_id 
           AND cookie.user_id = :user_id";

For more info refer to MySQL alias for SELECT * columns

edited May 23 '17 at 11:52

Community

1
1

answered Apr 28 '16 at 13:01

Web Artisan

1,870
3
23
33

3

Its a bad idea around here to quote w3schools – RiggsFolly Apr 28 '16 at 13:04
Thanks to let me know. – Web Artisan Apr 28 '16 at 13:06

score 0 · Answer 2 · answered Apr 28 '16 at 12:58

0

use alias SELECT login.salt as login_salt

answered Apr 28 '16 at 12:58

Moauya Meghari

471
2
6
23

score 0 · Answer 3 · answered Apr 28 '16 at 12:58

0

Try alias, An alias can be used in a query select list to give a column a different name.

  $sql = "SELECT login.salt as s1, cookie.salt as s2 FROM login, cookie WHERE login.user_id AND cookie.user_id = :user_id";

Reference 1

Reference 2

answered Apr 28 '16 at 12:58

AkshayP

2,141
2
18
27

score 0 · Accepted Answer · answered Apr 28 '16 at 12:59

Use alias with query.

$user_id = 1;

$sql = "SELECT login.salt as loginSalt, cookie.salt as cookieSalt FROM login, cookie WHERE login.user_id AND cookie.user_id = :user_id";

$params = array(
        'user_id' => $user_id
        );

$stmt = $db->justConnect()->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

print_r($result);

score 0 · Answer 5 · edited Apr 28 '16 at 13:04

0

set alias for your columns:

SELECT login.salt as login_salt, cookie.salt as cookie_salt FROM login, cookie WHERE login.user_id AND cookie.user_id = :user_id

edited Apr 28 '16 at 13:04

Brijal Savaliya

1,101
9
19

answered Apr 28 '16 at 13:00

Ali Farhoudi

5,350
7
26
44

MYSQL tables with same column name

5 Answers5