2

I am updating a mysqli database query to use prepared statements. This is the current PHP code:

$randomQQuery = "SELECT question FROM question WHERE tagOne='".$sub."' ORDER BY RAND() LIMIT 1";
$randomQ = mysqli_query($dbc,$randomQQuery);
$qRow = mysqli_fetch_array($randomQ);
$question = $qRow['question'];

This is my initial prepared statement attempt:

$randomQQuery = $dbc->prepare("SELECT question FROM question WHERE tagOne=? ORDER BY RAND() LIMIT 1");
$randomQQuery->bind_param('s',$sub);
$randomQQuery->execute();
$qRow = $randomQQuery->fetch_array();
$question = $qRow['question'];
  1. Do I need to name my variable $stmt? All examples i've looked at use that name.
  2. If so, how do I distinguish between numerous SQL queries and the use of a second different $stmt?
  3. Am i fetching the array and obtaining the $question correctly? I understand that 'bound results' are an option; are these best practice from a security perspective or just more efficient?
Improve this question
8
  • 2
    It is a variable, and you may therefore name it whatever you like. $stmt is common just be cause it makes it clear that it contains a mysqli_stmt object as opposed to a mysqli resource. Commented Jan 13, 2014 at 19:15
  • 1
    To use fetch_array() as you are, you must call get_result(), but that isn't available on every platform. bind_result() is, but is generally more tedious to use. Commented Jan 13, 2014 at 19:16
  • Much appreciated. The initial PHP code worked fine (aside from being exposed to SQL injection), can you see where my prepared statement version is wrong? Do fetch_array() and mysqli_fetch_array() have different availability? How would the get_result statement look? Commented Jan 13, 2014 at 19:23
  • 2
    Your prepared statement is all ok, but you need to call $result = $randomQQuery->get_result(); and then call $qRow = $result->fetch_array() to convert the prepared statement resource's result into a result resource. Per the get_result() docs I linked earlier, you can only do that if you are using the mysqlnd driver, which not every system has. If you get errors about get_result() not existing, you'll have to use bind_result() instead -- the principle annoyance of mysqli vs PDO. Commented Jan 13, 2014 at 19:25
  • Thanks again. Didnt initially see the links in your comment! Commented Jan 13, 2014 at 19:30

3 Answers 3

Reset to default
2

1 - Not not really, in fact you didn't even named it $stmt?

2 - No need for this since #1 ;)

3 - It's ok... not great, all depends of your needs

What you're doing is only valid for 1 row, otherwise you need at least a while

while($row = $result->fetch_array())
{
    $rows[] = $row;
}

Best pratices would at least validate that execute didn't return false..

Also by default, it's using MYSQL_BOTH which gives you array and numbering array. You can save a bit of memory if you don't need both by using either one:

/* numeric array */
$row = $result->fetch_array(MYSQLI_NUM);
printf ("%s (%s)\n", $row[0], $row[1]);

/* associative array */
$row = $result->fetch_array(MYSQLI_ASSOC);
printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);

Reference: https://www.php.net/mysqli_fetch_array

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

1+ for the tip about MYSQLI_NUM. But where is $result coming from?
$result is the return of the $query() call. That was taken from the php page example directly: $result = $mysqli->query($query);
@ROunofF if you use $mysqli->query($query) , will bind_param() be effective?
1

1: No.

3: Fetching arrays is usually done by while($qRow = $randomQQuery->fetch_array()) { }. I'd go with $row = $randomQQuery->fetch_row();.

Improve this answer

2 Comments

There's a LIMIT 1 on the query, no loop is needed. Further, fetch_array() and its cousins don't necessarily always work with MySQLi prepared statements as they do with plain query result resources. get_result() is needed to be able to use fetch_array() in that context in the first place.
I did mention 'usually done', right? Seeing as it is just one row by the use of LIMIT 1, fetch_row() does the trick.
-1

This is some example code that is pretty simple to follow if it helps. I use $stmt (it's just the way I learned how to do it) and I've used this countless times. I understand it may not be exactly what you needed, but this works and may help or help anyone else having a similar issue.

include "../dbConnFile.php";

if($conn){

    if($_POST['example1']!='' && $_POST['example2']!=''){//checks input

        $stmt = $conn->prepare("INSERT INTO database (example1, example2) VALUES (?,?)");

        $stmt->bind_param("ss", $_POST['example1'], $_POST['example2']);

        $stmt->execute();
        $stmt->close();
    }

    $result=$conn->query("SELECT example1, example2 FROM datasbase ORDER BY ____ DESC");

    if($result){
        while($tempRowHolder = mysqli_fetch_array($result,MYSQL_ASSOC)){
            $records[] = $tempRowHolder;
        }
    }
}
Improve this answer

1 Comment

<h3> Example Table </h3> <table> <tr> <th>Example</th> <th>Example</th> </tr> <?php foreach<$records as $this_row){ echo '<tr>'; echo '<td>'.$this_row['example1'].'</td>'; echo '<td>'.$this_row['example2'].'<td>'; echo '<tr>'; } ?> </table> <hr/> <form action="filename.php" method="post"> Example: <input type="text" id="name" name="name"/> Example: <textarea cols="60" rows="5" name="comment"></textarea> <input type="submit" value="Submit"/> </form>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.