如何用MYSQLI检查数据库中是否存在学校[重复]

Question

This question already has an answer here:

• check if row exists with mysql 3 answers

I have a question on how to check if a school exists within the database i have made? I have provided the MYSQLI code below. If you see any syntax errors can you please help me fix them. Thank you for your help i really appreciate it.

$connection = mysqli_connect("localhost", "root", "root", "babble");
    $check ="SELECT COUNT(1) FROM schools WHERE schoolname = '$_POST[schoolname]'";
    $result = mysqli_query($connection,$check);
    $data = mysqli_fetch_array($result, MYSQLI_NUM);
    if ($data[0] > 1) {
        echo "<center><div class='alert alert-danger'><p>Sorry, but that school already exists in the database</p></div></center>";
    }else{
        $insert = "INSERT INTO schools";        
    }

</div>

Answer 1

You forgot the quotes around the post variable name. Also, you should sanitize to prevent injection. I created a function you may use for future reference. It will trim everything but letters and numbers. (because i assume your school name will not contain symbols)

Also, you should check out PDO. I wrote up an example for you.

function protect($str)
{
    $str = stripslashes($str);
    $str = strip_tags($str);
    $str = preg_replace("[^A-Za-z0-9]", "", $str);

    return $str;
}

$schoolToCheck = protect($_POST['schoolname']);

PDO

$pdo = new PDO('mysql:host=localhost;dbname=root', 'root', 'babble');

$stmt = $pdo->prepare("SELECT COUNT(*) FROM schools WHERE schoolname = '$schoolToCheck'");
$stmt->execute();
if($stmt->rowCount() > 0)
{
    echo "<center><div class='alert alert-danger'><p>Sorry, but that school already exists in the database</p></div></center>";
}
else
{
    $insert = "INSERT INTO schools";   
}

MYSQLI

$connection = mysqli_connect("localhost", "root", "root", "babble");

$check = "SELECT COUNT(*) FROM schools WHERE schoolname = '$schoolToCheck'";
$result = mysqli_query($connection,$check);
$data = mysqli_fetch_array($result, MYSQLI_NUM);
if ($data[0] > 0)
{
    echo "<center><div class='alert alert-danger'><p>Sorry, but that school already exists in the database</p></div></center>";
}
else
{
    $insert = "INSERT INTO schools";        
}

EDIT: Short and sweet

$count = $pdo->query("SELECT COUNT(*) FROM schools WHERE schoolname = '$schoolToCheck'")->fetchColumn();
if($count > 0)
{
    echo "already exists";
}
else
{
    echo "";
}