我的查询或语法有什么问题？ [关闭]

Question

Lines 3 and 8 here are telling me I have an error.

I know the sql syntax is correct because I can enter it directly and it works.

$kidID = $_POST['remkid'];

$kname = mysqli_fetch_assoc($conn, SELECT name FROM kids WHERE id=`$kidID`);

$name = $kname['name'];

if(isset($_POST['remkid'])) {
    mysqli_query($conn, DELETE FROM kids WHERE id=`$kidID`) or die ("Error: " . mysqli_error());
    echo "$name was removed from the system.";
}

Here is an included file that houses $conn which I know also works correctly on the other 5 or 6 pages I have created.

<?php
$servername = "*";
$username = "*";
$password = "*";
$dbname = "*";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Any help is greatly appreciated.

Answer 1

To summarize the comments: You don't have a MySQL syntax error, you have a PHP syntax error. First, mysqli_query() expects the query to be a string value, which means that at some point it must be enclosed in quotes. That is to say,

mysqli_query($conn, DELETE FROM kids WHERE id=`$kidID`)

should be

mysqli_query($conn, "DELETE FROM kids WHERE id = $kidID")

Depending on the type of kids.id you might have to say "...WHERE id = '" . $kidID . "'"); - note that $kidID is enclosed in straight quotes (''), not backticks (``).

Second, mysqli_fetch_assoc() doesn't take a query string at all. You need to pass it a mysqli_result object, as returned by mysqli_query() (which requires a quoted string argument):

$result = mysqli_query($conn, "SELECT name FROM kids WHERE id=$kidID");
$kname = mysqli_fetch_assoc($result);

That said, the other problem is that you are using a dynamic SQL query. something for which there is no excuse when prepared statements are available. Prepared statements close up what in your case is a gaping SQL injection hole (see How can I prevent SQL-injection in PHP?), as well as automagically handling all the necessary quoting, typing and escaping of variables.

For your SELECT the prepared-statement syntax would be

$sql = "SELECT name FROM kids WHERE id = ?";    // define the query
$stmt = mysqli_prepare($conn, $sql);            // compile the query
mysqli_stmt_bind_param($stmt, 's', $kidID);     // fill in the variables
$result = mysqli_stmt_execute($stmt);           // perform the query
$kname = mysqli_fetch_assoc($result);           // retrieve the result
$name = $kname['name'];

I have omitted any treatment of error handling, as I prefer to leave that as an exercise for the reader.