通过PHP从MySQL获取数据 - 我做得对吗？

Question

Simple question I guess, but a fundamental one and I'm not sure of the best practice.

So let's say that I have a database with some IP addresses that I want to display to the user.

Is this a good/secure way/practice?

//--> CONNECT TO DB, etc

$db_query = 'SELECT ip,'
        ."FROM table "
        ."GROUP BY ip ";

$result = $db_conn->query($db_query);
echo 'Found '.$result->num_rows.' records';

if($result->num_rows > 0) {

    while($row = $result->fetch_array(MYSQLI_BOTH))
    {
        //POPULATE A HTML TABLE/WHATEVER WITH THE INFO
    }
}

I'm mostly concerned about this: $result->num_rows > 0 and this: fetch_array(MYSQLI_BOTH)

I'm asking because I read somewhere that num_rows > 0 can usually mean trouble depending on the situation, for example a user login. In that case I suppose it would num_rows == 1 right?

And also, I haven't fully understood the difference between MYSQLI_BOTH and other forms of fetching.. If you could simple explain them to me and when to use them I would be grateful.

What do you think?

Answer 1

I would add a check to ensure your query was executed OK - and if not output the error :

$result = $db_conn->query($db_query);
// check for error - output the error
if (!$result) {
    $message  = 'Invalid query: ' . mysqli_error() . "
";
    $message .= 'Whole query: ' . $db_query;
    die($message);
}
echo 'Found '.$result->num_rows.' records';

Other than that ... looks OK

EDIT:

To explain MYSQLI_BOTH, the options are MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH ->

• MYSQLI_ASSOC = Associative array so the value of the rows can be accessed using $row['column']
• MYSQLI_NUM = Numeric array so the values of the rows are accessed using a number $row[n] where n is the number of the column (0 based)
• MYSQLI_BOTH = can use both to access values of row either $row[n] or $row['column']

EDIT2:

There is also a function for checking the number of returned rows :

if(mysqli_num_rows($result) == 0){ 
   echo "Sorry. No records found in the database";                        
} 
else { 
  // loop you results or whatever you want to do
}

EDIT3:

php.net has some excellent docs for the MY_SQLI extension