How do I use a Function to assign values to an Array from an SQL Query

Question

I'm having some issue creating a PHP Function that can assign values to a Multi-dimensional Associative Array based on the results of an SQL query.

I've searched through some of the other questions on this topic, but I couldn't tell if/how the answers applied to me. My question seemed more simple than what the other questions were getting at. I'm not sure how to explain it with words, so I'm going to try and explain through code.

Say I have this database with several tables that contain all sorts of information about different breeds of dogs. Height, weight, color, etc. 50+ columns worth of information. I want to pull 5 specific breeds out of this database based on different search criteria, but I want to assign all the same columns to array variables. So rather than repeat these long while loops over and over, I wanted to create a function that would assign these variables for me.

Example

$sql1 -> //Query for German Shepherd info
$sql2 -> // Query for Golden Retriever info
$sql3 -> // Query for Poodle info
$sql4 -> //Query for Pit Bull info
$sql5 -> // Query for Bulldog info

$dogs = array(array()); // set up 2 level multi-array

The long way (that does work, though it's redundant) is to assign the values through different while loops and repeat ALL the columns over and over.

while ($rows = $sql1->fetch(PDO::FETCH_ASSOC)){
$dogs[1]['breed_name'] = $rows['breed_name'];
$dogs[1]['height'] = $rows['height'];
$dogs[1]['weight'] = $rows['weight'];
$dogs[1]['country'] = $rows['country'];
$dogs[1]['function'] = $rows['function'];
etc .. etc .. etc..
}
while ($rows = $sql2->fetch(PDO::FETCH_ASSOC)){
$dogs[2]['breed_name'] = $rows['breed_name'];
$dogs[2]['height'] = $rows['height'];
$dogs[2]['weight'] = $rows['weight'];
$dogs[2]['country'] = $rows['country'];
$dogs[2]['function'] = $rows['function'];
etc .. etc .. etc..
}
while ($rows = $sql3->fetch(PDO::FETCH_ASSOC)){
$dogs[3]['breed_name'] = $rows['breed_name'];
$dogs[3]['height'] = $rows['height'];
$dogs[3]['weight'] = $rows['weight'];
$dogs[3]['country'] = $rows['country'];
$dogs[3]['function'] = $rows['function'];
etc .. etc .. etc..
}

I would much rather just write a function that I could call for each breed as needed. Then I could store the function on an external function page that I include to whatever page I'm working on. It would save space and effort. But I can't figure out HOW to do this. It seems it should be simple, but I seem to be missing something.

I've tried 2 different ways so far, neither have produced any results.

Attempt 1

//set up the function
function setDog($dogs, $d) {
$dogs[$d]['breed_name'] = $rows['breed_name'];
$dogs[$d]['height'] = $rows['height'];
$dogs[$d]['weight'] = $rows['weight'];
$dogs[$d]['country'] = $rows['country'];
$dogs[$d]['function'] = $rows['function'];
etc .. etc .. etc..
} 

//call the function
while ($rows = $sql1->fetch(PDO::FETCH_ASSOC){setDog($dogs, 1);}
while ($rows = $sql2->fetch(PDO::FETCH_ASSOC){setDog($dogs, 2);}
while ($rows = $sql3->fetch(PDO::FETCH_ASSOC){setDog($dogs, 3);}
while ($rows = $sql4->fetch(PDO::FETCH_ASSOC){setDog($dogs, 4);}
while ($rows = $sql5->fetch(PDO::FETCH_ASSOC){setDog($dogs, 5);}

But that didn't work (no values were assigned to the array), so I tried switching it up

Attempt 2

//set the function
function setDog($dogs, $d, $sql){

while ($rows = $sql->fetch(PDO::FETCH_ASSOC)){
$dogs[$d]['breed_name'] = $rows['breed_name'];
$dogs[$d]['height'] = $rows['height'];
$dogs[$d]['weight'] = $rows['weight'];
$dogs[$d]['country'] = $rows['country'];
$dogs[$d]['function'] = $rows['function'];
etc .. etc .. etc..
}

}

//call the function
setDog($dogs, 1, $sql1);
setDog($dogs, 2, $sql2);
setDog($dogs, 3, $sql3);
setDog($dogs, 4, $sql4);
setDog($dogs, 5, $sql5);

This 2nd one seemed the most likely to work, but it still didn't set any values in my array. I check by calling the random array variables, (height, country, breed_name) and they all come up empty.

echo $dogs[1]['breed_name'];

I feel like this, or something very much like this, should be possible. The concept seems so simple, but I must be missing something. Can anyone help me figure this out?

EDIT!!

I messed up my example the first time. It was supposed to include the $d argument, but I just copied and pasted a bunch of wrong code. It should be fixed now.

To modify original $dogs array you have to pass it by reference. But your entire code seems a bit confuse. I.e., do you realize that you override same array key? And why you pass $d to function if you don't use it? — fusion3k
– fusion3k, Commented May 2, 2016 at 22:57
using just one index isn't enough, there are a lot of types of poodles, you can't just use 1, it'll just overwrite them and get the last type in the end, i'd suggest just use an in clause, feed the custom function with poodle, bulldog array, then just use the unique id and assign it as key in your array — Kevin
– Kevin, Commented May 2, 2016 at 22:58
DOH! I messed up my example. In the function, where it says 1 it SHOULD say $d. I'll go change it. — Shirley Dodson
– Shirley Dodson, Commented May 2, 2016 at 23:01
Can you also show your mysql queries and table structure? Probably you can perform a single query. — fusion3k
– fusion3k, Commented May 2, 2016 at 23:03
On this particular page, I probably COULD do a single query, but that isn't the issue here. I'm going to have other pages that call for the same information I'm trying to set with this function. Those pages will be based on 1 query. — Shirley Dodson
– Shirley Dodson, Commented May 2, 2016 at 23:09

fusion3k · Accepted Answer · 2016-05-02 23:29:36Z

1

To change original $dogs array you have to pass it to function by reference:

#                ↓
function setDog( &$dogs, $d, $sql  )
{
    (...)
}

setDog( $dogs, 1, $sql1 );

Without using references, your function create a copy of passed arguments and it works on that copy.

Also note that your function performs a while loop but it assigns rows values to same array:

while ($rows = $sql->fetch(PDO::FETCH_ASSOC)){
    $dogs[$d]['breed_name'] = $rows['breed_name'];

If your queries return only one row, your function will work as expected, otherwise only last row (for each call) will be added.

End note: you assign to array keys same db field names, so you can simply do in this way:

while ($rows = $sql->fetch(PDO::FETCH_ASSOC)){
    $dogs[$d] = $rows;
}

Or — if the query return only one row:

function setDog( &$dogs, $d, $sql  )
{
    $dogs[$d] = $sql->fetch( PDO::FETCH_ASSOC );
}

As you can see, if the query returns only a single row, you can completely remove function call and do it directly in main code:

$dogs[1] = $sql1->fetch( PDO::FETCH_ASSOC );
$dogs[2] = $sql2->fetch( PDO::FETCH_ASSOC );
(...)

answered May 2, 2016 at 23:29

fusion3k

11.7k4 gold badges27 silver badges47 bronze badges

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

1 Comment

Shirley Dodson Over a year ago

THANK YOU! That worked perfectly. I was unaware that I needed to pass the array by reference. I'd been running functions with arrays before, but I'd been returning values, not setting them. So I wasn't aware there was a difference in how I passed the array. Thank you so much!

Collectives™ on Stack Overflow

How do I use a Function to assign values to an Array from an SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related