时间戳

首页 > 解决方案 > Mysql select statement with inner join not working with php but works on mysql terminal

php - Mysql select statement with inner join not working with php but works on mysql terminal

问题描述

I have three tables in mysql

Table 1: projects

+----+-------------+-----------------------------+----------+---------------+
| id | ProjectName | ProjectDescription          | projType | projectStatus |
+----+-------------+-----------------------------+----------+---------------+
|  1 | RAPepsi     | Retail Audit for Pepsi      |        1 |             1 |
|  2 | RACocaco    | Retail Audit for Coke       |        1 |             1 |
+----+-------------+-----------------------------+----------+---------------+

Table2 : outlets

id pid  poid OutletName Add1     Add2       City    Phone  interviewer Status projStat 
    1   1   11  Outlet1 Address1    Address2    City1   12345      1          1      1
    2   1   21  Outlet2 Address1    Address2    City1   12345      1          1      1
    3   2   32  Outlet2 Address1    Address2    City1   12345      3          1      1

Table 3: Users

id  username        email           password
1   test1@gmail.com test1@gmail.com 123
2   test2@gmail.com test2@gmail.com 123
3   test3@gmail.com test3@gmail.com 123

I am trying to get the project name and description from projects table assigned to specfic interviewer from outlets table. I have tried this code in mysql console:

select distinct(p.ProjectName),p.ProjectDescription from outlets as oo inner join projects as p on p.id = oo.pid where oo.interviewer=(select id from users where email='test1@gmail.com');

This fetches the correct results as desired. However, when I use the same code in a php script where the email is dynamic, it fails to return the data:

PHP Script

include "config.php";
$conn = new mysqli($HostName, $HostUser, $HostPass, $DatabaseName);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
} 
$username  = (isset($_GET['userId']))?  $_GET['userId'] : 0;
$sql="select distinct(p.ProjectName),p.ProjectDescription from outlets as oo inner join projects as p on p.id = oo.pid where oo.interviewer=(select id from users where email='".$username."')";
$result = $conn->query($sql);

The above line not working in php. I am getting the following error: [client 103.47.158.210:56346] PHP Parse error: syntax error, unexpected '$result' (T_VARIABLE)

However, when i use the following code: it works:

$sql = "SELECT * FROM projects where projectStatus=1 and username= '".$username."'";

Ofcourse I test this with username column in my projects table.

标签： phpmysql

解决方案

我怀疑这条线是这里的问题： 

$username = (isset($_GET['userId'])) ? $_GET['userId'] : 0;

这一行告诉我您正在尝试获取用户 ID（我猜是一个数字），然后在数据库中查询电子邮件（这是一个字符串）。尝试设置$username为test1@gmail.com，看看是否适合您。如果这行得通，那么您知道您需要获得该用户username而不是他们的id.

您可以这样做的另一种方法是查询数据库中用户的id而不是他们的email. 所以这将是你的新 PHP 行：

$sql="select distinct(p.ProjectName),p.ProjectDescription from outlets as oo inner join projects as p on p.id = oo.pid where oo.interviewer=(select id from users where id='".$username."')";

如果这些方法都不起作用，请仔细检查您是否连接到正确的数据库并重试。

祝你好运！

推荐阅读