Not able to debug the syntax error with my SQL query

Question

My program isn't working and I'm unsure which point exactly I've gone wrong

Basically, I get an artist_id from my first PHP page via get.
I have 2 other tables. 1 Table contains the information I want. However, I need to compare my initial value, with an external table to establish a relationship from the main table where I need the information from.
The logic behind my SQL Query is correct. However, I am unsure whether I made a syntax error which I'm not able to figure out.

Also, I find it very tedious trying to debug these programs. What would be the best way to check whether the syntax for my SQL queries are correct or not?

<!doctype html>
<html>
  <head>
    <meta charset="utf-8">
<style>
table,th,td{
border: 1px solid black;
}
</style>
    <title>My second PHP page</title>
  </head>
  <body>
    <?php
      include 'config.php';
    ?>
    <?php
     $pdo = new PDO("mysql:dbname=${config['dbname']};host=${config['host']};charset=utf8",
                     $config['name'], $config['pass']);
    $my_id= $_GET['val'];
    $sql = ' SELECT name, venue, schedule FROM events'
           .' WHERE event_id IN'
           .' (SELECT event_id FROM performs'
           .' WHERE artist_id = :variable) '
            .' ORDER BY schedule DESC,
                       event_id ASC';

       $stmt->execute([':variable' => $my_id ]);

  $result = $pdo->query($sql);

       echo "<table>";
      echo "<tr><th>Event name</th><th>Venue</th><th>Schedule</th></tr>";
      foreach ($result as $row) {
         echo "<tr>";
         $name = htmlspecialchars($row['name']);
         $venue = htmlspecialchars($row['venue']);
         $schedule = htmlspecialchars($row['schedule'];
         echo "<td>".$name."</td>";
         echo"<td>".$venue."</td>";
         echo"<td>".$schedule."</td>";
         echo "</tr>";

      }

        echo "</table>";


    ?>

</body>

`WHERE event_id IN ( sub-query )`, i.e. missing parentheses. — jarlh, Oct 22 '18 at 10:22
@jarlh Thanks! Stupid of me to miss that. It still doesn't seem to work after though — JasonMetr, Oct 22 '18 at 10:24
`'events'.'WHERE'` will result in `eventsWHERE`, which is probably not what you want. You have to insert a space either at the end of one line or at the start of the next. Ditto for couple of other lines (`INSELECT`, `performsWHERE`). You do it correctly for `:variable ORDER`, though. — Amadan, Oct 22 '18 at 10:26
Order by is missing a comma between the two fields being used in the order. This kind of 'please check my sql' question is ideal for the sql chat room instead of a question, which is inevitably a relatively simple syntax / typo error — Andrew, Oct 22 '18 at 10:29
The best way to debug the SQL is to use a SQL Client, connect directly to the database, and run your SQL there. — MatBailie, Oct 22 '18 at 10:31
To answer a bit more helpfully about "What would be the best way to check whether the syntax for my SQL queries are correct or not?": inspect the SQL itself (not just the code that builds it) to catch silly concatenation errors, and follow the advice in [PDO error message?](https://stackoverflow.com/a/3999871/240443) about how to get feedback from the DB about what's wrong with your SQL. — Amadan, Oct 22 '18 at 10:31
Made all the changes but it still doesn't seem to be working. Also, what would be the best way I go about debugging something like this? Since I'm relatively new to PHP and SQL and I'm unaware of the syntax. — JasonMetr, Oct 22 '18 at 10:32
@JasonMetr so, you've done`WHERE event_id IN ( (SELECT event_id FROM performs WHERE artist_id = :variable) )`? With the missing `()`? *(The outer "extra" `()` are to say "here's a list" and the inner `()` are to say "here's a sub-query")*. As for debugging, connect directly to your database with a SQL Client........ — MatBailie, Oct 22 '18 at 10:33
_“Also, what would be the best way I go about debugging something like this? Since I'm relatively new to PHP and SQL and I'm unaware of the syntax.”_ - a) familiarize yourself better with the syntax (because otherwise debug outputs and error messages will most likely still not tell you much), and b) don’t play multiple fields at once - get the basic query syntax right using a database frontend like phpMyAdmin first, and implement it in your own PHP script afterwards. — misorude, Oct 22 '18 at 10:37
Why are you using `.` strings, PHP allows multi-line strings no problem, and they are easier to read. — ArtisticPhoenix, Oct 22 '18 at 10:38
I tested my query on phpMyAdmin and it seems to be working fine. Maybe there is an error in my php code. — JasonMetr, Oct 22 '18 at 10:56

score 0 · Answer 1 · answered Oct 23 '18 at 06:36

You have not any Syntax errors in your code but, you are using PDO Wrongly, when i have replicated your code in my local machine, i have got below errors:

  Notice: Undefined variable: stmt in your_file.php on line 12

  Fatal error: Call to a member function execute() on null in your_file.php on line 12

You cannot bind variable using PDO::query(), you have to use PDO::prepare().

PDO::prepare( string $statement [, array $driver_options = array() ] ) Prepares an SQL statement to be executed by the PDOStatement::execute() method. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style. Use these parameters to bind any user-input, do not include the user-input directly in the query.

PDO::prepare example from php documentation:

Example #1 Prepare an SQL statement with named parameters

   /* Execute a prepared statement by passing an array of values */
   $sql = 'SELECT name, colour, calories
           FROM fruit
           WHERE calories < :calories AND colour = :colour';
   $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
   $sth->execute(array(':calories' => 150, ':colour' => 'red'));
   $red = $sth->fetchAll();
   $sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
   $yellow = $sth->fetchAll();  
?>

Example #2 Prepare an SQL statement with question mark parameters

<?php  

   /* Execute a prepared statement by passing an array of values */
   $sth = $dbh->prepare('SELECT name, colour, calories
                         FROM fruit
                         WHERE calories < ? AND colour = ?');
   $sth->execute(array(150, 'red'));
   $red = $sth->fetchAll();
   $sth->execute(array(175, 'yellow'));
   $yellow = $sth->fetchAll();
?>

Now, Below i am showing how to use PDO::prepare for your code, i will only show where you have to change your code:

<?php
 $pdo = new 
PDO("mysql:dbname=${config['dbname']};host=${config['host']};charset=utf8",
                 $config['name'], $config['pass']);
$my_id= $_GET['val'];
$stmt = $pdo->prepare('SELECT name, venue, schedule FROM events
                       WHERE event_id IN
                      (SELECT event_id FROM performs WHERE artist_id = :variable)
                       ORDER BY schedule DESC, event_id ASC');

$stmt->execute([':variable' => $my_id ]);

foreach ($stmt as $row) {

//fetch and use you result set as you want here

}

Not able to debug the syntax error with my SQL query

1 Answers1