My program isn't working and I'm unsure which point exactly I've gone wrong
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>
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
}