php - 如何使用sql查询从另一个表中获取数据？

Question

我有 2 个 MySQL 表，如下:

users表(st_id是station表主键)

uid  fname  lname company_name  email    phone  st_id
=====================================================
9    xxx    yyyy  zzzz          x@y.com  xxx    5,6

车站表

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

现在使用一个 SQL 查询，我想从users 表获取所有数据以及从station 获取所有st_name st_id 与 uid 匹配的表

这意味着它应该返回users表中的所有数据以及station表中的所有st_name，其中uid = 9

我当前的 SQL 查询:

$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' ");

注意:在 station 表中，我将 st_id 值存储为数组。喜欢:

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

更新代码:

 $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>'; 

返回:

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
)

Answer 1

您可以尝试此查询。

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

这里您可以将 9 替换为您想要的 uid。

更新

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