I can’t execute SQL sentence twice

I can’t execute SQL twice somehow.
In the case of below code, 1st SQL sentence【SELECT h.title, h.place, h.introduction ~】works. And 2nd one【SELECT name ~】 doesn’t work.

If I change 1st SQL sentence to comment out, 2nd SQL work correctly.

Do you notice how to work both of SQL.

<?php
function dbconnect(){
  $db = new mysqli('localhost:8889', 'root', 'root', 'airbnb');
  // if (! $db){
  //   die($db->error);
  // }
  if ($db->connect_errno){
    throw new RuntimeException('mysqli connection error: ' . $db->connect_error);
  }
  return $db;
}

function h($value){
  return htmlspecialchars($value, ENT_QUOTES);
}
session_start();
$db = dbconnect();

if ( isset( $_SESSION['id']) && $_SESSION['name']){
  $id = $_SESSION['id'];
  $name = $_SESSION['name'];
  $image = $_SESSION['image'];
}else{
  header('Location: login_admin.php');
  exit();
}

$hotel_id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

$error = [];

if ( $_SERVER['REQUEST_METHOD'] == 'POST'){

  $title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);
  if ( $title == ''){
    $error['title'] = 'blank';
  }

  $place = filter_input(INPUT_POST, 'place', FILTER_SANITIZE_STRING);
  if ( $place == ''){
    $error['place'] = 'blank';
  }

  $introduction = filter_input(INPUT_POST, 'introduction', FILTER_SANITIZE_STRING);
  if ( $introduction == ''){
    $error['introduction'] = 'blank';
  }


  if (empty($error)){
    $stmt = $db->prepare('UPDATE hotels SET title=?, place=?, introduction=? WHERE id=? ');
    if (! $stmt){
      die($db->error);
    }
    $stmt->bind_param('sssi', $title, $place, $introduction, $hotel_id);
    $success = $stmt->execute();
    if (! $success){
      die($db->error);
    }
    header('Location: view_admin.php?id=' . $hotel_id);
    exit();
  }


}

?>

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link href="../style.css" rel="stylesheet">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
  <script src="https://kit.fontawesome.com/08e90ee946.js" crossorigin="anonymous"></script>
  <title>Document</title>
</head>
<body>

<?php include 'header_admin.php' ?>

<div class="main-wrapper">
  <div class="container">
    <h3 class="heading">宿泊施設の情報を変更する</h3>
    <?php
    $stmt = $db->prepare('SELECT h.title, h.place, h.introduction FROM hotels h WHERE h.id=? LIMIT 1');
    if (! $stmt){
      die($db->error);
    }
    $stmt->bind_param('i', $hotel_id);
    $success = $stmt->execute();
    if (! $success){
      die($db->error);
    }
    $stmt->bind_result($title, $place, $introduction);
    if ( $stmt->fetch()) :
    ?>
    <form action="" method="post" enctype="multipart/form-data">
    <div class="mb-3">
      <label for="exampleFormControlInput1" class="form-label">タイトル</label>
      <input type="text" class="form-control" id="exampleFormControlInput1" name="title" value="<?php echo h($title) ?>" placeholder="">
      <?php if ( isset($error['title']) && $error['title'] == 'blank') :?>
      <p class="error">タイトルを入力してください</p>
      <?php endif ?>
    </div> 
    <div class="mb-3">
      <label for="exampleFormControlInput1" class="form-label">エリア</label>
      <input type="text" class="form-control" id="exampleFormControlInput1" name="place" value="<?php echo h($place) ?>" placeholder="">
      <?php if ( isset($error['place']) && $error['place'] == 'blank') :?>
      <p class="error">エリアを入力してください</p>
      <?php endif ?>
    </div>     
    <div class="mb-3">
      <label for="exampleFormControlTextarea1" class="form-label">紹介文</label>
      <textarea class="form-control" id="exampleFormControlTextarea1" name="introduction" rows="3"><?php echo h($introduction) ?></textarea>
      <?php if ( isset($error['introduction']) && $error['introduction'] == 'blank') :?>
      <p class="error">紹介文を入力してください</p>
      <?php endif ?>
    </div> 
    <?php endif ?>
    <div class="amenities-area">
      <label for="exampleFormControlTextarea1" class="form-label">アメニティ</label>
      <div class="amenities">    
        <?php
        $stmt = $db->prepare('SELECT name FROM amenities');
        if (! $stmt){
          die($db->error);
        }
        $success = $stmt->execute();
        if (! $success){
          die($db->error);
        }
        $stmt->bind_result($a_name);
        while ( $stmt->fetch()) :
        ?>
        <div class="amenity">
          <input type="checkbox" class="" id="cb1" name="cb1">
          <label class="cb1" for="cb1"><?php echo h($a_name) ?></label>
        </div>
        <?php endwhile ?>
      </div>
    </div>
    <button type="submit" class="btn btn-primary">変更</button>
    </form>
    
  </div>
</div>

</body>
</html>

JavaScript
​x
 
<?phpfunction dbconnect(){  $db = new mysqli('localhost:8889', 'root', 'root', 'airbnb');  // if (! $db){  //   die($db->error);  // }  if ($db->connect_errno){    throw new RuntimeException('mysqli connection error: ' . $db->connect_error);  }  return $db;}​function h($value){  return htmlspecialchars($value, ENT_QUOTES);}session_start();$db = dbconnect();​if ( isset( $_SESSION['id']) && $_SESSION['name']){  $id = $_SESSION['id'];  $name = $_SESSION['name'];  $image = $_SESSION['image'];}else{  header('Location: login_admin.php');  exit();}​$hotel_id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);​$error = [];​if ( $_SERVER['REQUEST_METHOD'] == 'POST'){​  $title = filter_input(INPUT_POST, 'title', FILTER_SANITIZE_STRING);  if ( $title == ''){    $error['title'] = 'blank';  }​  $place = filter_input(INPUT_POST, 'place', FILTER_SANITIZE_STRING);  if ( $place == ''){    $error['place'] = 'blank';  }​  $introduction = filter_input(INPUT_POST, 'introduction', FILTER_SANITIZE_STRING);  if ( $introduction == ''){    $error['introduction'] = 'blank';  }​​  if (empty($error)){    $stmt = $db->prepare('UPDATE hotels SET title=?, place=?, introduction=? WHERE id=? ');    if (! $stmt){      die($db->error);    }    $stmt->bind_param('sssi', $title, $place, $introduction, $hotel_id);    $success = $stmt->execute();    if (! $success){      die($db->error);    }    header('Location: view_admin.php?id=' . $hotel_id);    exit();  }​​}​?>​<!DOCTYPE html><html lang="ja"><head>  <meta charset="UTF-8">  <meta http-equiv="X-UA-Compatible" content="IE=edge">  <meta name="viewport" content="width=device-width, initial-scale=1.0">  <link href="../style.css" rel="stylesheet">  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">  <script src="https://kit.fontawesome.com/08e90ee946.js" crossorigin="anonymous"></script>  <title>Document</title></head><body>​<?php include 'header_admin.php' ?>​<div class="main-wrapper">  <div class="container">    <h3 class="heading">宿泊施設の情報を変更する</h3>    <?php    $stmt = $db->prepare('SELECT h.title, h.place, h.introduction FROM hotels h WHERE h.id=? LIMIT 1');    if (! $stmt){      die($db->error);    }    $stmt->bind_param('i', $hotel_id);    $success = $stmt->execute();    if (! $success){      die($db->error);    }    $stmt->bind_result($title, $place, $introduction);    if ( $stmt->fetch()) :    ?>    <form action="" method="post" enctype="multipart/form-data">    <div class="mb-3">      <label for="exampleFormControlInput1" class="form-label">タイトル</label>      <input type="text" class="form-control" id="exampleFormControlInput1" name="title" value="<?php echo h($title) ?>" placeholder="">      <?php if ( isset($error['title']) && $error['title'] == 'blank') :?>      <p class="error">タイトルを入力してください</p>      <?php endif ?>    </div>     <div class="mb-3">      <label for="exampleFormControlInput1" class="form-label">エリア</label>      <input type="text" class="form-control" id="exampleFormControlInput1" name="place" value="<?php echo h($place) ?>" placeholder="">      <?php if ( isset($error['place']) && $error['place'] == 'blank') :?>      <p class="error">エリアを入力してください</p>      <?php endif ?>    </div>         <div class="mb-3">      <label for="exampleFormControlTextarea1" class="form-label">紹介文</label>      <textarea class="form-control" id="exampleFormControlTextarea1" name="introduction" rows="3"><?php echo h($introduction) ?></textarea>      <?php if ( isset($error['introduction']) && $error['introduction'] == 'blank') :?>      <p class="error">紹介文を入力してください</p>      <?php endif ?>    </div>     <?php endif ?>    <div class="amenities-area">      <label for="exampleFormControlTextarea1" class="form-label">アメニティ</label>      <div class="amenities">            <?php        $stmt = $db->prepare('SELECT name FROM amenities');        if (! $stmt){          die($db->error);        }        $success = $stmt->execute();        if (! $success){          die($db->error);        }        $stmt->bind_result($a_name);        while ( $stmt->fetch()) :        ?>        <div class="amenity">          <input type="checkbox" class="" id="cb1" name="cb1">          <label class="cb1" for="cb1"><?php echo h($a_name) ?></label>        </div>        <?php endwhile ?>      </div>    </div>    <button type="submit" class="btn btn-primary">変更</button>    </form>      </div></div>​</body></html> ​

As you can see, there are 3 SQL sentence in the code. I have no idea that the problem is in the SQL, HTML sentence or Datebase(phpMyAdmin).

Answer

You can’t use the same name for multiple queries.

Every name should be unique for multiple queries on the same page.

For example: $stmt for the first as it is, and for the second try a new name Like $stmt1 or $stmt_second, then it works perfectly.

For first query leave it as it is:

$stmt = $db->prepare('SELECT h.title, h.place, h.introduction FROM hotels h WHERE h.id=? LIMIT 1');

JavaScript
 
$stmt = $db->prepare('SELECT h.title, h.place, h.introduction FROM hotels h WHERE h.id=? LIMIT 1');​

And for the second query change it to:

$stmt_second = $db->prepare('SELECT name FROM amenities');

JavaScript
 
$stmt_second = $db->prepare('SELECT name FROM amenities');​

Advertisement

Answer