Home>

Thank you for browsing.

● Overview
Creating something like a simple blog using a database,
I am currently thinking of implementing an editing function.
When you edit the contents on the edit screen (editshow.php)
Each column information is passed to (edit.php) along with the primary key (id) of the page being edited
I am wondering if I can update the contents of the database with the UPDATE command in the same file.
An error has occurred.

● editshow.php

//Excerpt
<form action = "edit.php" method = "post" enctype = "multipart/form-data"><p>TITLE:<input type ='text' name ='title' rows = '1' value =<? php echo $row ['title']? >></p><p>LANGUAGE:
  <select name = "language"> <option value = "php"<? php if ($row ['language'] =='php') {echo'checked';}? >>PHP</option> <option value = "javasctipt"<? php if ($row ['language'] =='javascript') {echo'checked';}? >>JavaScript</option> <option value = "htmlcss"<? php if ($row ['language'] =='htmlcss') {echo'checked';}? >>HTML/CSS</option> <option value = "other"<? php if ($row ['language'] =='other') {echo'checked';}? >>Other</option></select><!-<p>DATE:<input type = date name ='date'></p>-><p>TAG:<textarea name = "tag1" name = "tag1" rows = '1'><? php echo $row ['tag1']?></Textarea><textarea name = "tag2" rows = '1'>#</textarea><textarea name = "tag3" rows = '1'>#</textarea></p><p>REFERENCE:<input type = "file" name = "img"></p><p>MEMO:<textarea name = "memo" cols = "90" rows = "15" maxlength = "500" wrap = "hard"><? Php echo $row ['memo']?></Textarea></p><p>TYPE:
   <input type = "radio" name = "type" value = "question"<? php if ($row ['type'] =='question') {echo'checked';}? >>Question
   <input type = "radio" name = "type" value = "progress"<? php if ($row ['type'] =='progress') {echo'checked';}? >>Progress
   <input type = "radio" name = "type" value = "test"<? php if ($row ['type'] =='test') {echo'checked';}? >>Test</p><input type = "submit" value = "POST"><input type = "hidden" name = "id" value =<? php echo $id?>/></form><br>


● edit.php

<? php
  $language = $_POST ['language'];
  $title = $_POST ['title'];
  $tag1 = $_POST ['tag1'];
  $date = $_POST ['date'];
  $memo = $_POST ['memo'];
  $type = $_POST ['type'];
  $id = $_POST ['id'];
  if ($language =='' || $title =='') {
      header ('Location: toppage.php');
      exit ();
  }
  $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
  $user ='**';$password ='*';
  try {
      $db = new PDO ($dsn, $user, $password);
      $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
// Error location (command description location)
      $stmt = $db->prepare (""
        UPDATE pile SET title = $title, language = $language, date = $date, tag1 = $tag1, memo = $memo, type = $type WHERE id = $id "
     );
     $stmt->execute ();
    header ('Location: toppage.php');
    exit ();
  } catch (PDOException $e) {
      exit ('Could not connect to database'. $E->getMessage ());
  }
  ?>


● Error message
Fatal error: Uncaught Error: Call to a member function execute () on bool
in C: \ xampp \ htdocs \ uskprogram \ edit.php: (command description line)
Stack trace: # 0 {main} thrown
in C: \ xampp \ htdocs \ uskprogram \ edit.php on line (command description line)

● Question content
From the error message, I think there is an error in the command (edit.php).
I want to UPDATE based on the primary key passed from (editshow.php), but I am using a variable in the command
Is it impossible to use variables in commands in the first place?
If the corrections to use or the underlying idea is wrong
How to implement editing functionality on a database-based site
Could you give me a hint?

If i am inexperienced in programming and have a mistake in the wording, please let us know.
In addition, if you have any other necessary information, please let us know and we will add it.
Thanks for your cooperation.
Thank you for browsing.

● Overview
Creating something like a simple blog using a database,
I am currently thinking of implementing an editing function.
When you edit the contents on the edit screen (editshow.php)
Each column information is passed to (edit.php) along with the primary key (id) of the page being edited
I am wondering if I can update the contents of the database with the UPDATE command in the same file.
An error has occurred.

● editshow.php

//Excerpt
<form action = "edit.php" method = "post" enctype = "multipart/form-data"><p>TITLE:<input type ='text' name ='title' rows = '1' value =<? php echo $row ['title']? >></p><p>LANGUAGE:
  <select name = "language"> <option value = "php"<? php if ($row ['language'] =='php') {echo'checked';}? >>PHP</option> <option value = "javasctipt"<? php if ($row ['language'] =='javascript') {echo'checked';}? >>JavaScript</option> <option value = "htmlcss"<? php if ($row ['language'] =='htmlcss') {echo'checked';}? >>HTML/CSS</option> <option value = "other"<? php if ($row ['language'] =='other') {echo'checked';}? >>Other</option></select><!-<p>DATE:<input type = date name ='date'></p>-><p>TAG:<textarea name = "tag1" name = "tag1" rows = '1'><? php echo $row ['tag1']?></Textarea><textarea name = "tag2" rows = '1'>#</textarea><textarea name = "tag3" rows = '1'>#</textarea></p><p>REFERENCE:<input type = "file" name = "img"></p><p>MEMO:<textarea name = "memo" cols = "90" rows = "15" maxlength = "500" wrap = "hard"><? Php echo $row ['memo']?></Textarea></p><p>TYPE:
   <input type = "radio" name = "type" value = "question"<? php if ($row ['type'] =='question') {echo'checked';}? >>Question
   <input type = "radio" name = "type" value = "progress"<? php if ($row ['type'] =='progress') {echo'checked';}? >>Progress
   <input type = "radio" name = "type" value = "test"<? php if ($row ['type'] =='test') {echo'checked';}? >>Test</p><input type = "submit" value = "POST"><input type = "hidden" name = "id" value =<? php echo $id?>/></form><br>


● edit.php

<? php
  $language = $_POST ['language'];
  $title = $_POST ['title'];
  $tag1 = $_POST ['tag1'];
  $date = $_POST ['date'];
  $memo = $_POST ['memo'];
  $type = $_POST ['type'];
  $id = $_POST ['id'];
  if ($language =='' || $title =='') {
      header ('Location: toppage.php');
      exit ();
  }
  $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
  $user ='**';
  $password ='*';
  try {
      $db = new PDO ($dsn, $user, $password);
      $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
// Error location (command description location)
      $stmt = $db->prepare (""
        UPDATE pile SET title = $title, language = $language, date = $date, tag1 = $tag1, memo = $memo, type = $type WHERE id = $id "
     );
     $stmt->execute ();
    header ('Location: toppage.php');
    exit ();
  } catch (PDOException $e) {
      exit ('Could not connect to database'. $E->getMessage ());
  }
  ?>


● Error message
Fatal error: Uncaught Error: Call to a member function execute () on bool
in C: \ xampp \ htdocs \ uskprogram \ edit.php: (command description line)
Stack trace: # 0 {main} thrown
in C: \ xampp \ htdocs \ uskprogram \ edit.php on line (command description line)

● Question content
From the error message, I think there is an error in the command (edit.php).
I want to UPDATE based on the primary key passed from (editshow.php), but I am using a variable in the command
Is it impossible to use variables in commands in the first place?
If the corrections to use or the underlying idea is wrong
How to implement editing functionality on a database-based site
Could you give me a hint?

If i am inexperienced in programming and have a mistake in the wording, please let us know.
In addition, if you have any other necessary information, please let us know and we will add it.
Thanks for your cooperation.

~~ 10/14 postscript ~~
In order to identify the error location with the guidance of tanat
(1) Validate the SQL statement with (1) myadmin → (2) Return to the PHP text and replace the SQL statement with a placeholder → (3) Bind the value with a fixed value → (4) Bind the value with a variable
When I edited edit.php to check ② ~ ③ in the flow of, the error statement no longer occurs, but
There is an event that the page returns to the top page without being reflected.

<? php
  $language = $_POST ['language'];
  $title = $_POST ['title'];
  $tag1 = $_POST ['tag1'];
  $memo = $_POST ['memo'];
  $type = $_POST ['type'];
  $id = $_POST ['id'];
 /* $answer = $_POST ['answer'];* /
  if ($language =='' || $title =='') {
      header ('Location: toppage.php');
      exit ();
  }
  $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
  $user ='**';
  $password ='*';

  try {
      $db = new PDO ($dsn, $user, $password);
      $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
      $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
      $stmt = $db->prepare (""
        UPDATE pile SET title =': title', language =': language', tag1 =': tag1', memo =': memo', type =': type' WHERE id =': id'");
  $stmt->execute (array (': title' =>'test',': language' =>'test',': tag1' =>'test',': memo'=>'test',': type'=>'test',': id' =>1));
    header ('Location: toppage.php');
    exit ();
  } catch (PDOException $e) {
      exit ('Could not connect to database'. $E->getMessage ());
  }
  ?>
  • Answer # 1

    Answer

    First

        $db = new PDO ($dsn, $user, $password);
          $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);


    To

        $db = new PDO ($dsn, $user, $password);
          $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
          $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);


    As a matter of fact, let's make an exception when there is a SQL syntax mistake.

    Then you will see the error message that MySQL returns.
    Try debugging based on that.

    Often

    Among the variables directly linked to SQL, there is an empty variable and the SQL has the wrong syntax.

    There is a mistake in the SQL syntax in the first place (in this case, the handling of strings is suspicious)
    It is around.

    Development flow

    The flow of development

    1. The expected SQL can be phpMyAdmin, MySQL Workbench, or PHP for testing, so first execute it with a fixed value to create the correct (assumed) SQL.

    For example, like this. (Please make the actual UPDATE statement by yourself&it is unchecked, so an error may occur, but if you can understand the flow.)

    Issue a SELECT statement to check if the result is as expected when accessed

    // PHP that only updates the title of pile ID = 1 when accessed
      $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
      $user ='**';
      $password ='*';
      try {
          $db = new PDO ($dsn, $user, $password);
          $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
          $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
         // Redundant sample because no placeholders are used
          $stmt = $db->prepare ("UPDATE` pile` SET title ='test update' WHERE id = 1 ");
           $stmt->execute ();
        exit ();
      } catch (PDOException $e) {
          exit ('Could not connect to database'. $E->getMessage ());
      }

    2. Refer to the PHP manual and replace the value part with a placeholderprepare ()To do.

    // PHP that only updates the title of pile ID = 1 when accessed
      $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
      $user ='**';
      $password ='*';
      try {
          $db = new PDO ($dsn, $user, $password);
          $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
          $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
         // Redundant sample because no placeholders are used
          $stmt = $db->prepare ("UPDATE` pile` SET title =': title' WHERE id =: id ");
    // At this point, there is an exception that there are not enough variables bound, but no SQL syntax error occurs.
           $stmt->execute ();
        exit ();
      } catch (PDOException $e) {
          exit ('Could not connect to database'. $E->getMessage ());
      }

    3. Refer to the sample PHP manual and set the valueexecute ()OrbindParam ()Bind with

    // PHP that only updates the title of pile ID = 1 when accessed
      $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
      $user ='**';
      $password ='*';
      try {
          $db = new PDO ($dsn, $user, $password);
          $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
          $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
         // Redundant sample because no placeholders are used
          $stmt = $db->prepare ("UPDATE` pile` SET title =': title' WHERE id =: id ");
          $stmt->execute (array (': calories' =>"test title",': color' =>1));
        exit ();
      } catch (PDOException $e) {
          exit ('Could not connect to database'. $E->getMessage ());
      }

    4. Change the binding part to a variable and incorporate it into the process

    // PHP that only updates the title of pile ID = 1 when accessed
      $dsn ='mysql: host = localhost;dbname = ***;charset = utf8';
      $user ='**';
      $password ='*';
      try {
          $db = new PDO ($dsn, $user, $password);
          $db->setAttribute (PDO :: ATTR_EMULATE_PREPARES, false);
          $db->setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
         // Redundant sample because no placeholders are used
          $stmt = $db->prepare ("UPDATE` pile` SET title =': title' WHERE id =: id ");
          $stmt->execute (array (': calories' =>$title,': color' =>$id));
        exit ();
      } catch (PDOException $e) {
          exit ('Could not connect to database'. $E->getMessage ());
      }

    It is a flow like that.
    Executing from PHP without confidence in the SQL you want to execute is a difficult method (as it is now) because it creates a problem isolation.

    Other

    prepare ()Do not include variables directly in the SQL statement. (Prepare () has no meaning and is a hotbed for SQL injection vulnerabilities.)

        $stmt = $db->prepare (""
            UPDATE pile SET title = $title, language = $language, date = $date, tag1 = $tag1, memo = $memo, type = $type WHERE id = $id "
         );

    Please refer to page 2 of Development Flow and use the correct placeholders and bindings.