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

Question

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.

I can't reproduce that error, there's nothing wrong with the code you have posted. Are you sure it's on this line: `$result = $conn->query($sql);`? — Nick, Dec 28 '19 at 04:27
Normally that error would indicate a missing `;` at the end of the previous line but that is there. the code has no syntax errors on 3v4l: https://3v4l.org/QZ1pG — Nick, Dec 28 '19 at 04:29
Side notes: `DISTINCT` is not a function, you can remove the parenthesis around `p.ProjectName`, they have no effect. And your code is vulnerable to SQL injection. You should use parameterized queries. See: ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — sticky bit, Dec 28 '19 at 05:23

score 0 · Answer 1 · answered Dec 28 '19 at 04:50

Im suspecting that this line is the issue here:

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

This line is telling me that you are trying to get the user id (which I'm guessing is a number) and then query the database for an email (which is a string). Try setting $username to test1@gmail.com and see if that works for you. If that works, then you know you need to be getting that users username rather than their id.

The other way you could do it would be to query the database for the user's id instead of their email. So this would be your new PHP line:

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

If neither of those ways work, then double check that you are connecting to the correct database and try again.

Good luck!

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

1 Answers1