Fetch Data With Prepared Statements In Multidimensional Array

[masterlayouts]

What is the fastest and easiest way to move the fetched data into an array using prepared statements?

Everything I do seems ad-hoc.

 

... we bind results in two variable $col1 and $col2 ...

 

$x = 0; $myarray = array();

while(mysqli_stmt_fetch($stmt)){

$myarray[$x]['col1'] = $col1;

$myarray[$x]['col2'] = $col2;

$x++;

}

 

...

 

Now we have a multidimensional array $myarray (what I want).

 

However, if I have more variables seems a little inconvenient to repeat over and over again... I wonder if there is any way faster or better.

[HartleySan]

You could run a for loop inside the while loop. For example:

 

while (mysqli_stmt_fetch($stmt)){

 

for ($i = 1; $i <= number-of-variables-here; $i++) {

 

$myarray[$x]['col' . $i] = someOtherArray['col' . $i];

 

}

 

$x++;

 

}

 

Of course, this method would require that all your $col values be stored in the array someOtherArray beforehand, but if you're going to have a big list of variables that might change a lot over time, it would probably be best to declare such an array once at the time of your code to make editing easier.

[rob]

while(mysqli_stmt_fetch($stmt)) {
	$myarray[] = array($col1, $col2));
}

 

$myarray will automatically assign the index numbers, you don't need to.

 

However, if I have more variables seems a little inconvenient to repeat over and over again... I wonder if there is any way faster or better.

 

Is there a reason you're using bound results? If not, simply assign returned $rows to $myarray.

 

while($row = mysqli_fetch_row($result)) {
	$myarray[] = $row;
}

[HartleySan]

Good advice, rob. Thanks.

[masterlayouts]

1.

it is correct, but i will end up with a numeric array. i want to get an associative array as being consistent with names in forms, variables and datbase help me a lot. i will re-format the question: is there a better way to end up with an associative array from fetched data with prepared statements?

 

2.

the second part of your answer is intringing.

 

When should someone bind results and when not?

 

Is this a matter of preference or there is a golden rule or standard?

 

What is the best practice?

 

How this will measure with buffering results with mysqli_stmt_store_result? I have thousands of records... what should work better?

 

(Obviously the second part do not address the question as it has nothing to do with prepared statements... but it got interesting...)

[rob]

Ok, it's been a while since I used mysqli, unless I have to work with legacy code, I pretty much stick to PDO these days.

 

Re-reading the php manual, if you use prepared statements for SELECT queries you need to bind the results, so my question was nonsensical; apologies for misleading.

 

If I had to achieve what you're doing and I had the option, I would probably avoid a prepared statement and go with:

 

$query = "SELECT… ";

if ($result = mysqli_query($mysqli, $query)) {

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
	$myarray[] = $row;
}

mysqli_free_result($result);
}

 

Just remember to filter and sanitize your search terms if you go down that route.

 

Might be worth reading through the user comments on the php manual for a possible solution using prepared statements other than the one you already have.

[masterlayouts]

In fact prepare statements work in PDO as well. I wouldn't mind to understand how and why biding results in prepared statements and how to fetch the result in an associative array using prepared statements PDO style.

Is not the object oriented syntax the issue, but prepared statements.

I appreciate your time and effort, but your code does not make use of prepared statements. I know how to fetch data in an array OOP or procedural. What I would like to understand is when and why it makes sense to bind the results (I guess when the result is re-used in another query, but as I do not have enough experience I do not know if this is the right answer, just an assumtion I make).

 

Also the PHP manual says:

"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."

 

This is what makes me make the assumtion above, however I would like to know what other peoples think and maybe some veterans could help me understand better the issue.

 

I had a post regarding the quotetd statement above... I am very interested to know when prepared statements will fail.

 

If prepared statements are so wonderful and there is no way of SQL injection and no need to sanitize the data at least for SQL statements becase the prepared statements take care themselves of the matter... I am wondering why would somebody, even a beginner, want to learn and use something else?

 

There should be a reason why NOT to use prepared statements?

 

Thanks again for your help.

[Larry]

I've addressed these other questions in a separate thread. Please, per the forum guidelines, do not post the same thing multiple times. It ends up being too confusing and inevitably wastes someone's time when they're trying to help.

[masterlayouts]

I believe the thread in question is ths one:

http://www.larryullman.com/forum/read.php?19,38874

 

I found it through Google search. It was not my intention to duplicate posts. Thank you for your help.