php - 无法从MySQL数据库的其他表中选择ID

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

我正在尝试从artistId中选择artists，并将其与用户尝试输入的专辑相关联。但是，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>

albumredir.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');

 ?>

最佳答案

$artistId = $conn->query返回一个结果集，因此以后尝试时不能直接绑定到它：

$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']
);

关于php - 无法从MySQL数据库的其他表中选择ID，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37017265/