How to get data from another table using sql query?

Question

I have 2 MySQL tables which are below :

users table (st_id is station table primary key)

uid  fname  lname company_name  email    phone  st_id
=====================================================
9    xxx    yyyy  zzzz          [email protected]  xxx    5,6

station table

st_id  uid   st_name  st_lat  st_long  lg_id
============================================
5      9     xxx      24.25   24.95    8,9,10
6      9     yyy      23.25   23.95    11,12,12

Now using one SQL query I want to get all data from users table and all st_name from station table which st_id is match with uid

That's mean it's should return all data from users table and all st_name from station table which uid = 9

My current SQL query :

$getData = mysqli_query($conn, "SELECT users.uid, users.company_name, users.fname, users.lname, users.phone, users.email, station.st_id, station.st_name, logger.lg_name FROM users 
LEFT JOIN station ON station.st_id = users.st_id 
LEFT JOIN logger ON logger.lg_id = station.lg_id 
LEFT JOIN channel ON channel.ch_id = logger.ch_id  
WHERE users.power != 'admin' ");

Note: In station table I stored st_id values as array. Like :

$st_id_value =  implode(',', $st_id_value);

Update Code :

 $getData = mysqli_query($conn, "SELECT users.uid, users.company_name, users.fname, users.lname, users.phone, users.email, station.st_id, station.st_name, logger.lg_name 
FROM users LEFT JOIN station
ON FIND_IN_SET(station.st_id, users.st_id)
LEFT JOIN logger ON logger.lg_id = station.lg_id 
LEFT JOIN channel ON channel.ch_id = logger.ch_id
WHERE users.power != 'admin' AND station.uid=$uid");

$fetchData = mysqli_fetch_assoc($getData) ;

echo '<pre>';
    print_r($fetchData);
echo '</pre>';

Return :

Array
(
    [uid] => 9
    [company_name] => Source and Services
    [fname] => azad
    [lname] => ahmed
    [phone] => 01671133639
    [email] => [email protected]
    [st_id] => 5
    [st_name] => Rajshahi
    [lg_name] => D4L08841
)

It's not recommended to store multiple values in a single field, better to use a link-table, where each row has a reference to a station and a st_id. en.wikipedia.org/wiki/First_normal_form — Puggan Se
– Puggan Se, Commented Nov 30, 2016 at 11:07

A J · Accepted Answer · 2016-11-30 10:59:21Z

0

You can try this query.

SELECT users.uid, users.company_name, users.fname, users.lname, users.phone, users.email, station.st_id, station.st_name, logger.lg_name 
FROM users INNER JOIN station
ON FIND_IN_SET(station.st_id, users.st_id)
LEFT JOIN logger ON logger.lg_id = station.lg_id 
LEFT JOIN channel ON channel.ch_id = logger.ch_id
WHERE users.power != 'admin' AND users.uid=9

Here you can replace 9 with uid you want.

Update

$getData = mysqli_query($conn, "SELECT users.uid, users.company_name, users.fname, users.lname, users.phone, users.email, station.st_id, station.st_name, logger.lg_name 
FROM users LEFT JOIN station
ON FIND_IN_SET(station.st_id, users.st_id)
LEFT JOIN logger ON logger.lg_id = station.lg_id 
LEFT JOIN channel ON channel.ch_id = logger.ch_id
WHERE users.power != 'admin' AND station.uid=$uid");

while($fetchData = mysqli_fetch_assoc($getData))
{
    echo '<pre>';
    print_r($fetchData);
    echo '</pre>'; 
}

edited Nov 30, 2016 at 10:59

answered Nov 30, 2016 at 7:34

A J

4,01214 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5421791 Over a year ago

Sure, and I have another question to you that is. Is that database design is right for this purpose ? what do you think ?

A J Over a year ago

Generally, it is not advised to design in this way. If you have small chunk of data, it is not a big thing. But for large database, it is not good to store multiple values in a single field

user5421791 Over a year ago

I need to store tons of data :(

Collectives™ on Stack Overflow

How to get data from another table using sql query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related