MySQLi - search through an array

Question

I have the following code:

function resultToArray($result) {
    $rows = array();
    while($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    return $rows;
}

// Usage
$query = 'SELECT q17, COUNT(q17) FROM tresults GROUP BY q17';
$result = $mysqli->query($query);
$rows = resultToArray($result);
//print_r($rows); // Array of rows
$result->free();

Brings back the following (only an excerpt):

Array ( [0] => Array ( [q17] => [COUNT(q17)] => 7 ) [1] => Array ( [q17] => Admin & Clerical [COUNT(q17)] => 118 )......etc.

What I am struggling with is how to then return the data, basically, what I need is some code to pull out the data as follows:

WHERE Array = Admin & Clerical BRING BACK THE COUNT(q17) number

How do I search through the array, normally, I'd use something like:

if($rows['q17']==$q[$i]){echo$rows['COUNT(q17)'];}

But this doesn't work - I assume because there are two sets of data within each part of the array? Not sure how to deal with this.

You could just do that as party of the SQL query to save having to scan the array afterwards. — diggersworld
– diggersworld, Commented Nov 4, 2013 at 22:19
@diggersworld - thanks for the reply, much appreciated. How would I achieve that? — Homer_J
– Homer_J, Commented Nov 4, 2013 at 22:22
Use a WHERE clause... something like WHERE q17 = "Admin & Clerical". Don't know your db structure. — diggersworld
– diggersworld, Commented Nov 4, 2013 at 22:26
Thanks and appreciated but I have in the region of 400 rows of data within the array. A WHERE clause isn't what I'm looking for as I am trying to loop through the array within another loop in my PHP code. — Homer_J
– Homer_J, Commented Nov 4, 2013 at 22:29

Meti · Accepted Answer · 2013-11-04 22:51:31Z

1

You can achieve this by using MYSQL itself, by using HAVING clause instead of WHERE. To do this rewrite your query like this.

$query = 'SELECT q17, COUNT(q17) as qcount FROM tresults GROUP BY q17 HAVING q17="Admin & Clerical" ';

echo $row[0]['qcount']; //return  118

if you still want to it with PHP after getting the result from the database, it's how it done:

function get_q17_count($rows, $q17){
    foreach ($rows as $onerow) {
        if($onerow['q17'] == $q17){
            return $onerow['COUNT(q17)'];
        }
    } 
}

answered Nov 4, 2013 at 22:51

Meti

3653 silver badges8 bronze badges

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

2 Comments

Homer_J Over a year ago

Appreciate that Medi, I actually came up with a solution, as detailed below. It had to be via PHP after getting the result.

Meti Over a year ago

I saw your answer after I post mine, it seems like the same solution.

Homer_J · Accepted Answer · 2013-11-04 22:48:51Z

0

function resultToArray($results) {
    $rows = array();
    while($row = $results->fetch_assoc()) {
        $rows[] = $row;
    }
    return $rows;
}

// Usage
$querys = 'SELECT q17, COUNT(q17) FROM tresults GROUP BY q17';
$results = $mysqli->query($querys);
$rows = resultToArray($results);
//print_r($rows); // Array of rows
$results->free();

function searchForId($id, $array) {
   foreach ($array as $key => $val) {
       if ($val['q17'] === $id) {
           return $val['COUNT(q17)'];
       }
   }
   return null;
}

Called the function using:

    $id = searchForId($q[$i], $rows);echo " (".$id.")";

answered Nov 4, 2013 at 22:48

Homer_J

3,32312 gold badges48 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySQLi - search through an array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related