来自WHERE的PHP SQL SELECT不起作用

Question

I know that this is very thoroughly covered on stack overflow, but I cannot figure it out. I am completely new to PHP and SQL commands so please bear with me.

Here is my code:

    $connection = mysql_connect($serverName, $userName, $password) or die('Unable to connect to Database host' . mysql_error());
    $dbselect = mysql_select_db($dbname, $connection) or die("Unable to select database:$dbname" . mysql_error());

    $studentid = $_POST['student_id'];

    $result = mysql_query($connection,"SELECT `first_name` FROM `students` WHERE student_id = '$studentid'");

    while($row = mysqli_fetch_array($result))
    echo $row['first_name']

I am sure that it is probably something really stupid. I know that i should be using mysqli or something but this is just a test project to teach me some basics.

student_id is from the previous php page, and I want it to lookup student_id and display the first name of the student where I put echo from the table named students, but I get nothing on the page and there is no entry in the error log.

student_id is both the name of the column and the name of the input field on the previous php page.

Also, I don't know if it makes a difference, but the code from $connection to the while statement are in one

Any suggestions?

Thanks.

Answer 1

You're mixing your MySQL APIs, they do "not" mix.

Change mysqli_fetch_array to mysql_fetch_array if you really want to use mysql_*

Plus, put some bracing in:

while($row = mysql_fetch_array($result)) // missing brace
echo $row['first_name'] // <= missing semi-colon

and a semi-colon at the end of echo $row['first_name']

while($row = mysql_fetch_array($result)){
echo $row['first_name'];
}

Also, your DB connection here, goes at the end, not at the beginning: Unlike the mysqli_* method, it goes first. Using mysql_, the connection goes at the end. If you really want to use mysqli_* functions, then you'll need to change all mysql_ to mysqli_ (which follows).

$result = mysql_query($connection,"SELECT `first_name` FROM `students` WHERE student_id = '$studentid'");

which isn't really needed, since a DB connection has been established. (I've placed it at the end though).

$result = mysql_query("SELECT `first_name` FROM `students` WHERE student_id = '$studentid'",$connection);

Plus, use $studentid = mysql_real_escape_string(strip_tags($_POST['student_id']), $connection); for added protection, if you're still keen on using mysql_* based functions.

Add error reporting to the top of your file(s) which will help during production testing.

error_reporting(E_ALL);
ini_set('display_errors', 1);

MySQL (error reporting links)

• http://www.php.net/manual/en/function.mysql-error.php
• http://www.php.net/manual/en/mysqli.error.php
• http://www.php.net/mysqli_error

---

However...

---

Here's a full mysqli_ based method: adding mysqli_real_escape_string() to the POST variable.

error_reporting(E_ALL);
ini_set('display_errors', 1);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$connection = mysqli_connect($serverName, $userName, $password, $dbname) 
or die('Unable to connect to Database host' . mysqli_error());

$studentid = mysqli_real_escape_string($connection,$_POST['student_id']);

$result = mysqli_query($connection,"SELECT `first_name` FROM `students` WHERE student_id = '$studentid'");

while($row = mysqli_fetch_array($result)){
echo $row['first_name'];
}

And technically speaking...

mysql_* functions deprecation notice:

http://www.php.net/manual/en/intro.mysql.php

This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

These functions allow you to access MySQL database servers. More information about MySQL can be found at » http://www.mysql.com/.

Documentation for MySQL can be found at » http://dev.mysql.com/doc/.

---

Regarding SQL injection:

Your present code is open to SQL injection. Use mysqli_* functions. (which I recommend you use and with prepared statements, or PDO)