如何检查数据库中是否存在值，然后返回数据行

Question

this is for some coursework for my A-level comp sci course. I am currently coding in a language quite unfamiliar and i need some help with an aspect i have been struggling with for a while now. Below shows what I have done so far. What i want to do is output all values in the table that correspond have a certain sport ID this is what I have so far but it doesn't return any values although the table is populated.

<?php
$link = mysqli_connect("localhost", "root", "pizza","fixtures");

if ($_POST['SPORT'] == "Football") {
    $sp = '1';
}
if ($_POST['SPORT'] == "Tennis") {
    $sp = '2';
}
if ($_POST['SPORT'] == "Swimming") {
    $sp = '3';
}

$result = mysql_query("SELECT * FROM fixtureDetails WHERE fixtureDetails.sportID = '$sp'");
if(mysqli_num_rows($result) > 0) {
    echo "yes";
}
mysqli_close($link);
?>

Answer 1

Your code has a few issues:

1. Don't mix more than one library. You are using mysql_ and mysqli_ together.
2. Don't use mysql_* functions. They are deprecated.
3. No need of mysqli_close() function.
4. You don't need to repeat the table.
5. You are not printing anything from the query's result.

The problem with your code is, you need to change this line:

$result = mysqli_query($link, "SELECT * FROM `fixtureDetails` WHERE `sportID`='$sp'");

To populate as a table, use the resultset and loop.

if (mysqli_num_rows($result)) {
  while (false != ($data = mysqli_fetch_assoc($result))) {
    // Do whatever with your data.
    var_dump($data);
  }
} else {
  echo "No records.";
}

Final Code

<?php
  $link = mysqli_connect("localhost", "root", "pizza","fixtures");

  if ($_POST['SPORT'] == "Football") {
    $sp = '1';
  }
  if ($_POST['SPORT'] == "Tennis") {
    $sp = '2';
  }
  if ($_POST['SPORT'] == "Swimming") {
    $sp = '3';
  }

  // Execute the query and save the resultset.
  $result = mysqli_query($link, "SELECT * FROM `fixtureDetails` WHERE `sportID`='$sp'");
  // Check if there are any rows returned.
  if (mysqli_num_rows($result)) {
    // If there are rows returned, save every row to $data.
    while (false != ($data = mysqli_fetch_assoc($result))) {
      // Do whatever with your data.
      var_dump($data);
    }
  } else {
    // If there are no records, display a message.
    echo "No records.";
  }
?>

If you want a function to send the response of the count, you can have something like this:

<?php
  function getCount() {
    $link = mysqli_connect("localhost", "root", "pizza","fixtures");

    if ($_POST['SPORT'] == "Football") {
      $sp = '1';
    }
    if ($_POST['SPORT'] == "Tennis") {
      $sp = '2';
    }
    if ($_POST['SPORT'] == "Swimming") {
      $sp = '3';
    }

    // Execute the query and save the resultset.
    $result = mysqli_query($link, "SELECT * FROM `fixtureDetails` WHERE `sportID`='$sp'");
    // Check if there are any rows returned.
    return mysqli_num_rows($result);
  }
?>

If you want just a true or false, you can do:

<?php
  function getCount() {
    $link = mysqli_connect("localhost", "root", "pizza","fixtures");

    if ($_POST['SPORT'] == "Football") {
      $sp = '1';
    }
    if ($_POST['SPORT'] == "Tennis") {
      $sp = '2';
    }
    if ($_POST['SPORT'] == "Swimming") {
      $sp = '3';
    }

    // Execute the query and save the resultset.
    $result = mysqli_query($link, "SELECT * FROM `fixtureDetails` WHERE `sportID`='$sp'");
    // Check if there are any rows returned.
    return (mysqli_num_rows($result) > 0);
  }
?>