SQL JOIN does not return any results

Question

if(isset($_GET['id']) && $_GET['id'] != null) {

    $id = $_GET['id'];

    $sql = "SELECT 
    `maps.name`,
    `maps.description`,
    `maps.date`,
    `maps.mcversion`,
    `maps.mapid`,
    `maps.category`,
    `maps.format`,
    `users.username`,
    `users.rank`,
    `users.verified`,
    `users.mcusername`,
    COUNT(`views.mapid`) AS `views`,
    COUNT(`likes.mapid`) AS `likes`,
    COUNT(`downloads.mapid`) AS `downloads`,
    COUNT(`subscribes.channelid`) AS `subscribers`
    FROM  `maps` INNER JOIN `users` ON `maps.userid` = `users.id` 
        INNER JOIN `views` ON `maps.mapid` = `views.mapid`
        INNER JOIN `likes` ON `maps.mapid` = `likes.mapid`
        INNER JOIN `downloads` ON `maps.mapid` = `downloads.mapid`
        INNER JOIN `subscribe` ON `mapid.userid` = `subscribe.channelid`
    WHERE `maps.mapid` = '$id'";

    $result = mysqli_query($con,$sql);

    if (mysqli_num_rows($result) > 0)   {
        echo “success”;
    } else {
        header("LOCATION: index.php");
    }

    $sql = "SELECT * FROM `maps` WHERE `id`=$id";
    $result = mysqli_query($con,$sql);

    if (mysqli_num_rows($result) > 0)   {
        viewer($id);
    } else {
        header("LOCATION: index.php");
    }

This worked, but I need data from more tables.

$sql = "SELECT 
    `maps.name`,
    `maps.description`,
    `maps.date`,
    `maps.mcversion`,
    `maps.mapid`,
    `maps.category`,
    `maps.format`,
    `users.username`,
    `users.rank`,
    `users.verified`,
    `users.mcusername`,
    COUNT(`views.mapid`) AS `views`,
    COUNT(`likes.mapid`) AS `likes`,
    COUNT(`downloads.mapid`) AS `downloads`,
    COUNT(`subscribes.channelid`) AS `subscribers`
    FROM  `maps`
        INNER JOIN `users`     ON `maps.userid` = `users.id` 
        INNER JOIN `views`     ON `maps.mapid` = `views.mapid`
        INNER JOIN `likes`     ON `maps.mapid` = `likes.mapid`
        INNER JOIN `downloads` ON `maps.mapid` = `downloads.mapid`
        INNER JOIN `subscribe` ON `mapid.userid` = `subscribe.channelid`
    WHERE `maps.mapid` = '$id'";

Is this sql join good? Why it does not return any results?

with the normal $sql = "SELECT * FROM maps WHERE id=$id"; everything works, but i need data from the other tables too.

Remove one join at a time until you get results. Then figure out why the one you just removed isn't finding the expected match. — Patrick Q, Jul 08 '19 at 18:19
Also it might be the case that what you really want is a `LEFT OUTER JOIN` instead of an inner join. — Sam Chats, Jul 08 '19 at 18:22
You need to either remove your backticks (and make sure that you aren't using any reserved words), or you need to use them properly. You need one set around a table name, and _another_ set around a column name. — Patrick Q, Jul 08 '19 at 18:38
`\`maps.name\`` is not correct. It should be `\`maps\`.\`name\``. Same thing for all of the other similar instances. — Patrick Q, Jul 08 '19 at 18:41
So i found the problemS the ` simbol is required to everyhere like this `maps`.`name` and the other big problem was that i accedently typed mapid everywhere instead of id :D Thank you very much for everyones help — Dominik Balogh, Jul 08 '19 at 19:16
**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). — Dharman, Jul 08 '19 at 19:30
**Warning**: Your query does not comply with ANSI SQL as `GROUP BY` clause is omitted for non-aggregated columns. This will not return consistent results and should fail in a compliant SQL database if you ever migrate to another RDBMS. MySQL allows this because you have [**ONLY_FULL_GROUP_BY**](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) mode turned off. Turn it on or run in ANSI mode to ensure valid, consistent results. — Parfait, Jul 08 '19 at 20:57

Dominik Balogh · Answer 1 · 2019-07-08T20:32:42.227

0

The solution:

$sql = "SELECT 
    maps.name,
    maps.description,
    maps.date,
    maps.mcversion,
    maps.mapid,
    maps.category,
    maps.format,
    users.username,
    users.rank,
    users.verified,
    users.mc_username,
    (SELECT COUNT(*) FROM likes WHERE likes.mapid = maps.id) AS likes,
    (SELECT COUNT(*) FROM downloads WHERE downloads.mapid = maps.id) AS downloads,
    (SELECT COUNT(*) FROM subscribe WHERE subscribe.channelid = maps.userid) AS subscribers,
    (SELECT COUNT(*) FROM views WHERE views.mapid = maps.id) AS viewers
    FROM  maps
    INNER JOIN users 
        ON maps.userid = users.id
    WHERE maps.id = '$id'";

Thanks for the help!

edited Jul 08 '19 at 20:32

answered Jul 08 '19 at 19:17

Dominik Balogh

305
1
3
12

**Warning:** You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](http://php.net/manual/en/pdo.prepared-statements.php) or by [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). – Dharman Jul 08 '19 at 19:30
You say that this select query can be un-secure? – Dominik Balogh Jul 08 '19 at 20:35
Could you please provide me a similar sql select query that does the same, but in secure mode? I really want to learn! :) – Dominik Balogh Jul 08 '19 at 20:39
Replace `'$id'` with placeholder and then bind the data. Never put any variables inside SQL. [How can I prevent SQL injection in PHP?](https://stackoverflow.com/q/60174/1839439) – Dharman Jul 08 '19 at 20:41
Oh i am starting to understood, Until yet I just used a simple function which removes the specialcharacters from a string that is inserted into the table. Can you show me how to a bind it? – Dominik Balogh Jul 08 '19 at 20:44
I have provided all useful links. Please read through them in detail. Also take a look at the great tutorial on https://phpdelusions.net/ – Dharman Jul 08 '19 at 20:45

Dominik Balogh · Answer 2 · 2019-07-08T22:43:59.803

IF you would like to secure a complex sql statment, how would you do it? Is it an ok version?:

if(isset($_GET['id']) && $_GET['id'] != null) {
  $id = $_GET['id'];

  $stmt = $mysqli->prepare('SELECT id FROM maps WHERE id = ?');
  $stmt->bind_param('i', $id);

  $stmt->execute();

  $result = $stmt->get_result();

  if (mysqli_num_rows($result) == 1)    {
    $row = $result->fetch_assoc();
      $secid = $row["id"];
  } else {
      echo "error2";
  }

  $sql = "SELECT 
  maps.name,
  maps.description,
  maps.date,
  maps.mcversion,
  maps.mapid,
  maps.category,
  maps.format,
  users.username,
  users.rank,
  users.verified,
  users.mc_username,
  (SELECT COUNT(*) FROM likes WHERE likes.mapid = maps.id) AS likes,
  (SELECT COUNT(*) FROM downloads WHERE downloads.mapid = maps.id) AS downloads,
  (SELECT COUNT(*) FROM subscribe WHERE subscribe.channelid = maps.userid) AS subscribers,
  (SELECT COUNT(*) FROM views WHERE views.mapid = maps.id) AS viewers
  FROM maps
  INNER JOIN users 
      ON maps.userid = users.id
  WHERE maps.id = '$secid'";

  $result = mysqli_query($con,$sql);

  if (mysqli_num_rows($result) > 0) {
      $row = mysqli_fetch_assoc($result);
      echo $row["name"];
  } else {
      echo "error3";
  }

} else {
    echo "error1";
}

database connection:

$mysqli = new mysqli('127.0.0.1', 'root', 'pass’, 'db’);

SQL JOIN does not return any results

2 Answers2