php - 无法从 MySQL 数据库中的不同表中选择 id

Question

我正在构建一个网站，用户应该可以在其中将音乐艺术家和专辑输入到数据库中。我在 musique 数据库中有表 artists 和 albums。

我正在尝试从 artists 中选择 artistId 并将其与用户尝试输入的任何专辑相关联。不过，artistId 一直返回为 0。我认为我的 SELECT 语句有问题，但我不完全确定。

有人知道发生这种情况的原因吗？

inputalbum.php:

    <?php
    include "session.php";
    include "db.php";
    SessionClient::checkIfLoggedIn();

    // Get list of artists to suggest
    $conn = DB::connect();
    $results = $conn->query("SELECT artistName FROM artists");

    $artists = [];

    while ($row = $results->fetch_assoc()) {
      $artists[] = $row;
    }
    ?>

    <?php include "header.php"; ?>

    <div class="container">
      <h1>INSERT ALBUM</h1>
        <form class="form" enctype="multipart/form-data" action="albumredir.php" method="POST">
          <fieldset>
            <label for ="artistName">Artist</label>
            <input type="text" name="artistName">
            <br>
            <!-- <div>
              Artists already in the database: <span>?</span>
            </div> -->
            <script>
              // Transfer php array to js to use on the browser
              var artists = <?php echo json_encode($artists) ?>;
              // Grab the artist input field
              var artistInput = document.querySelector('input[name="artists"]');
              // Set an event for when they change to suggest artists
              artistInput.oninput = function () {
                var currentValue = artistInput.value;
                var suggestedArtists = [];
                artists.forEach(function (artist) {
                  var enteredArtists = currentValue.split(',');
                  if (artist.label.match(enteredTags[enteredArtists.length - 1].trim())) {
                    suggestedArtists.push(artist);
                  }
                });
                var suggestionString = suggestedArtists.map(t => t.label).join(',');
                document.querySelector('div span').innerHTML = suggestionString;
              }
            </script>
            <label for="albumName">Album Name:</label>
            <input type="text" name="albumName" placeholder="Album One">
            <br>
            <label for="relDate">Release Date:</label>
            <input type="date" name="relDate">
            <br>
          </fieldset>
          <fieldset>
            <input type="submit" name="submit" value="Submit">
          </fieldset>
        </form>
      </div>

相册目录.php:

<?php
session_start();

$artistName = $_POST['artistName'];
$albumName = $_POST['albumName'];
$relDate = $_POST['relDate'];
$submit = $_POST['submit'];

include "db.php";
$conn = DB::connect();

$artistId = $conn->query("SELECT artistId FROM artists WHERE artistName = $artistName");

$stmt = $conn->prepare("INSERT INTO albums (artistId, userId, albumName, relDate) VALUES (?, ?, ?, ?)");

$stmt->bind_param(
  "iiss",
  $artistId,
  $_SESSION['currentUser']['userId'],
  $_POST['albumName'],
  $_POST['relDate']
);

if(isset($_SESSION['currentUser']['userId']))
{
    $currentUser = $_SESSION['currentUser']['userId'];
}
else
{
    $currentUser = NULL;
}

if(isset($_POST['albumName']))
{
  $albumName = $_POST['albumName'];
}
else
{
  $albumName = NULL;
}

if(isset($_POST['relDate']))
{
  $relDate = $_POST['relDate'];
}
else {
  $relDate = NULL;
}

$stmt->execute();

// Close the connection
$conn->close();
// header('Location: index.php');

 ?>

Answer 1

$artistId = $conn->query 正在返回一个结果集，因此您稍后尝试时不能直接绑定(bind)到它:

$stmt->bind_param(
  "iiss",
  $artistId,

您需要先从结果集中获取 artistId。

在此示例中，为了清楚起见，我将结果集的名称从 $artistId 更改为 $result。

$result = $conn->query("SELECT artistId FROM artists WHERE artistName = $artistName");

// get row from result
$row = $result->fetch_assoc();

// get artistID from row
$artistId = $row["artistId"];

$stmt = $conn->prepare("INSERT INTO albums (artistId, userId, albumName, relDate) VALUES (?, ?, ?, ?)");

$stmt->bind_param(
  "iiss",
  $artistId,
  $_SESSION['currentUser']['userId'],
  $_POST['albumName'],
  $_POST['relDate']
);