PHP - Execute select query and loop through results

Question

I am developing a web service using PHP. I am having some trouble while executing the select query. This is the code I'm using.

DB_Functions.php

public function getCompanies() {

    $result = mysql_query("SELECT * FROM company");
    // check for successful store

    if ($result) {
        return mysql_fetch_array($result,true);
    } else {
        return false;
    }
}

GetCompanies.php

<?php

require_once 'include/DB_Functions.php';
$db = new DB_Functions();

$companies = array();
//$rows = $db->getCompanies();

while ($row = $db->getCompanies()) {
    echo $row['companyName'];
    $rowArr = array();
    $rowArr['CompanyName'] = $row['companyName'];
    $rowArr['CompanyID'] = $row['companyId'];
    //array_push($companies, $rowArr);
    $companies[] = $rowArr;
}
header('Content-Type: application/json');
$response=array("Companies"=>$companies);
$json = json_encode($response);
echo $json
?>

But the problem is in GetCompanies.php file the while loop is runs endless. The code appears to be ok. Any help would be appreciated.

Because you're re-running the query every single time you call getCompaines()... Store the result outside of your loop and iterate over that. — Pavlin
– Pavlin, Commented Sep 1, 2015 at 17:38
Why are you using mysql_query? You shouldn't be. Switch to MySQLi or PDO. mysql_query is going to be removed from PHP very soon. — gen_Eric
– gen_Eric, Commented Sep 1, 2015 at 17:51

gen_Eric · Accepted Answer · 2015-09-01 17:54:32Z

1

When you do while ($row = $db->getCompanies()) { you are running the entire query over again and returning the 1st row each time. mysql_fetch_array returns one row.

What you need to do is have getCompanies() loop over all the rows and return an array.

public function getCompanies() {    
    $result = mysql_query("SELECT * FROM company");
    // check for successful store

    if ($result) {
        $ret = array();

        while($row = mysql_fetch_assoc($result)){
            $ret[] = $row;
        }

        return $ret;
    } else {
        return false;
    }
}

Now, getCompanies() will return you an array that you can just foreach over:

$rows = $db->getCompanies();
foreach($rows as $row){
    // ...
}

answered Sep 1, 2015 at 17:54

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

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

Comments

Divij Bindlish · Accepted Answer · 2015-09-01 17:40:38Z

0

Change your while loop declaration to something like

foreach($rows as $row) {}

And as Pavlin said, move the function call to getCompanies() outside the loop.

Also, how about modifying the query to select a particular set of fields from the database and directly sending them as the response without doing any additional processing?

answered Sep 1, 2015 at 17:40

Divij Bindlish

3351 silver badge8 bronze badges

1 Comment

LIH Over a year ago

Returns the error " Illegal string offset 'companyName'"

Nazim · Accepted Answer · 2015-09-01 17:41:51Z

0

Since you are implementing Select query without any condition(where clause). And since the company table has data it would always return true in the while loop this makes the while loop an infinite loop. For while to work properly the condition should become false to exit the loop. Its not a programming flaw its a logical one.

answered Sep 1, 2015 at 17:41

Nazim

5645 silver badges13 bronze badges

2 Comments

Nazim Over a year ago

You should add where clause and select a specific type of dataset instead of fetching all.

Nazim Over a year ago

You should move while loop inside the function and return data from there if its there else return false.

Pavlin · Accepted Answer · 2015-09-01 17:50:47Z

0

The php docs have all the information you need. You're using the wrong function:

mysqli_fetch_array — Fetch a result row as an associative, a numeric array, or both

vs

mysqli_fetch_all — Fetches all result rows as an associative array, a numeric array, or both

Just change your return statement to return mysqli_fetch_all($result); or return mysqli_fetch_all($result, MYSQLI_ASSOC); to get an associative array.

And of course, you need to move your getCompanies call outside of the loop.

NOTE php mysql_* functions have been depricated since version 5.5.* and are going to be removed from the language soon. You should look into mysqli_* or PDO.

edited Sep 1, 2015 at 17:50

answered Sep 1, 2015 at 17:47

Pavlin

5,5587 gold badges44 silver badges57 bronze badges

4 Comments

gen_Eric Over a year ago

This is only half of the solution. while ($row = $db->getCompanies()) { would still be an infinite loop.

Pavlin Over a year ago

Yes. That would also require change, as I've already pointed out in the comments.

gen_Eric Over a year ago

P.S. He's also using mysql_query in his question, he'd have to change to MySQLi.

Pavlin Over a year ago

So he is. Well, he shouldn't, it's about to be removed from php.

Collectives™ on Stack Overflow

PHP - Execute select query and loop through results

4 Answers 4

Comments

1 Comment

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related