使用SELECT查询从数据库中的两个不同表中获取数据 [英] Using a SELECT query to get data from two different tables in database

问题描述

我真的很难理解我的查询出了什么问题.当我使用var_dump($ sqli);它只是回显整个查询，而没有任何数据.

I am really struggling to understand what is wrong with my query. When I use var_dump($sqli); it just echos out the entire query without any data in it.

我想创建一个查询，以便当用户搜索城市时，返回的搜索结果将是与该城市相关的景点.我意识到我的代码存在一些错误，但是，如果我完成了查询，那么我将在那里调试其余的代码.如果有人对我的查询为什么不起作用有任何想法，我将不胜感激.

I want to create a query so when a user searches for a city the returned search result would be the attractions associated with that city. I realise that there is a few errors with my code, however if I get the query completed I will go from there and debug the rest of the code. If anyone has any ideas as to why my query isn't working I would greatly appreciate them.

require_once('config1.php');
error_reporting(E_ALL);

$output = '';
if(isset($_POST['search'])){
$searchq = $_POST['search'];

$sqli = 'SELECT attraction_name, lat, long, cost FROM zz_attractions WHERE city_id IN SELECT city_id FROM zz_city WHERE city_name LIKE %searchq%' or die("could not search");
var_dump($sqli);

$result = mysqli_query($conn, $sqli);
$count = mysqli_num_rows($result);
if ($count == 0) { 
    $output = 'there was no search results';
} else {
    while ($row = mysql_fetch_array($sqli)) { 
    $attraction_name = $row['attractionname'];
    $lat = $row['latitude'];
    $long = $row['longitude'];
    $cost = $row['cost'];

    $output .= '<div>'.$attraction_name.' '.$lat.' '.$long.' '.$cost.'</div>';

    }
}
}

推荐答案

您当前的查询失败，因为您需要在此处显示括号:

Your current query fails because you need the parentheses shown here:

  SELECT attraction_name, lat, long, cost 
  FROM zz_attractions 
  WHERE city_id IN (SELECT city_id 
                    FROM zz_city 
                    WHERE city_name 
                    LIKE '%searchq%')

但是更好的方法是内部连接:

But a better way to do it would be an inner join:

  SELECT a.attraction_name, a.lat, a.long, a.cost 
  FROM zz_attractions a
  INNER JOIN zz_city c ON a.city_id = c.city_id
  WHERE c.city_name LIKE '%searchq%'

----

仅是观察，仅此而已...

----

just an observation, nothing more...

从来没有完全理解为什么PHP编码人员倾向于将每个SQL查询都放在一行中

Never quite understood why PHP coders tend to have every SQL query as a single row

$query = '
  SELECT a.attraction_name, a.lat, a.long, a.cost 
  FROM zz_attractions a
  INNER JOIN zz_city c ON a.city_id = c.city_id
  WHERE c.city_name LIKE %searchq%
';

echo $query; 

这篇关于使用SELECT查询从数据库中的两个不同表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！