dongpao5658 2018-10-22 10:21
浏览 137

无法使用我的SQL查询调试语法错误

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>
  • 写回答

1条回答 默认 最新

  • doutan1905 2018-10-23 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

}
    评论

报告相同问题？

悬赏问题

  • ¥15 关于#Java#的问题，如何解决？
  • ¥15 加热介质是液体，换热器壳侧导热系数和总的导热系数怎么算
  • ¥15 想问一下树莓派接上显示屏后出现如图所示画面，是什么问题导致的
  • ¥100 嵌入式系统基于PIC16F882和热敏电阻的数字温度计
  • ¥15 cmd cl 0x000007b
  • ¥20 BAPI_PR_CHANGE how to add account assignment information for service line
  • ¥500 火焰左右视图、视差（基于双目相机）
  • ¥100 set_link_state
  • ¥15 虚幻5 UE美术毛发渲染
  • ¥15 CVRP 图论 物流运输优化