Using php array or using mysql query which one is faster

PHP
1

Please house i want to create a function that will query the database to get some sets of values, this means the function have to query the database on each execution and mostly the function will be called inside a loop. now i am thinking of pulling out all the details from the database into a php array and then loop through that array to find values in the loop instead of hitting the database as many times the loop can take

Now please i need to know if mysql query will be better and faster to use or is using array faster and better?

2

I don’t honestly know, but I would imagine the array would be faster.
But the problem with the array is: how often with the data set be updated?

When using the database and the same query is being made in a loop, you can make it more efficient by using prepared statements. Prepare the query only once before the loop, then execute within the loop.
Eg:-

$sql = $db->prepare("SELECT * FROM table WHERE id = ?");

foreach($list as $id){
    $sql->execute([$id]);
    // Fetch Etc...
}
1 Like
3

What’s the most number of sets of variables that will ever be needed? Is it the same fields from the same tables each time?

4

is a three column table, the table holds the name and price.

findprice();

now this findprice() will query the database to get the price of the a given name.
and if i should fo it normally it will look like this

function findprice($value){
$search = $con->prepare("select from table where value = ?");
$search->bindParam (1, $value, PDO::PARAM_STR);
$search-> execute ();
return $search->fetch();
}

$data = array(data);

foreach ($data as $da){
findprice($da);
}

we know the above findprice() will query the database inside a loop.

or is this approach better.

function pullprice() {
    $search = $con->prepare ("SELECT FROM table");
    $search->execute ();
    return $search->fetchAll();
}

function newfindprice($amount, $value) {
    if ($value === $amount) {
        return $amount
    }
}

$pricelist = pullprice();
$toname = 'dessy';

foreach ($pricelist as $name) {
    newfindprice($toname, $name);
}

something like this is what am thinking of.

5

Oh I would definitely query ONE time and put all details in an array with the product id as the primary array key. If the array is built near the top of the page before output, it can be used in many ways such as form/order processing( i.e. comparing form values to this array or grabbing needed product details that are not sent with a form input.) , showing different product views from complete details to a lists of products or quick view popups. All from one array. Just my 2 cents…

2 Likes
6

I would do only one query also, but I would directly request the correct array by using IN statement

$inString = "('".implode($data, "','")."')";
$priceList = $con->prepare("SELECT value FROM table WHERE value IN $inString");

If you value column is of type INT or FLOAT you do not need to escape the values with '

1 Like
7

really, i think using one query, output to an array and then loop through the array in body.

$outputtedarray = results from database

then the below foreach loop will only use that outputtted array more like the database is now brought out and placed above the function.

so only query happens once.
while the looping function plays with the returned array. as many times as it wants without querying the database more than twice.

8

Remember to build the array placing the ID as the key, so any product popup, product detail view can simply use the ID with the array to grab details of that product. Example:

$productlist = array();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
	$productlist[$row['id']] = $row;
}
1 Like
9

This can be done directly by fetchAll using the PDO::FETCH_UNIQUE mode.

$productlist = $query->fetch(PDO::FETCH_UNIQUE);

Assuming the ID is the first column in the table, it will be the array key for its row.
If not, you can explicitly set the order of the columns in the query, where the first mentioned becomes the key, though it must be a unique column.

SELECT id, name, price FROM table

I highly recommend exploring the various PDO fetch modes, there are lots of handy tricks like this that can save you work in PHP processing.
A nice reference: https://phpdelusions.net/pdo/fetch_modes

2 Likes
10

wow thats great, i never knew of this was busy using some complex query method like distinct and all that when i can simply use a fetch module. Thanks alot

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.