shibbir ahmed shibbir ahmed - 4 days ago 5
PHP Question

How to get data from another table using sql query?

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 x@y.com 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] => azad@gmail.com
[st_id] => 5
[st_name] => Rajshahi
[lg_name] => D4L08841
)

A J A J
Answer

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>'; 
}
Comments