PHP/MYSQL Query as a Function for Multi-Use

Question

I am trying to clean a ton of select queries inside about 15 pages of a website and I thought using a function that then I can use to call all the different queries and tables, would reduce the clutter a bunch.

I am running into a problem and can't seem to figure out a way to get it right.

Here is the function:

function dbGet($conn, $table, $select, $where, $cond) {
   require($conn);
   if ( $stmt = $db->prepare(" SELECT $select FROM `" . $table . "` WHERE $where=? ")) {
      $stmt->bind_param("i", $cond);
      $stmt->execute();
      $result = $stmt->get_result();
      $rowNum = $result->num_rows;

      if ( $rowNum > 0 ) {
         while( $data = $result->fetch_assoc() ){
            return $data;
         }
      }
      $stmt->close();
   } else {
      die($db->error);
   }
   $db->close();
}

And here is how I call the function in the various different pages:

$settings = dbGet('php/connect.php', 'settings', '*', 'userId', $sessionId);
echo $settings->toName;

The problem I am running into, is that it is only displaying the first record from the settings table. I thought using the while loop in the function would return all the records. As a matter of fact, if I do a print_r for $data instead of return, I get all the records. How can I pass along all the records into the return $data to be access in any other page?

OR If you guys happen to have any suggestions of an existing function that is highly regarded and true-and-tested that I can implement instead of writing my own, please let me know.

Thanks.

When you return, you are effectively ending that function at the first pass of that loop. That's why you only see the first record. — cteski
– cteski, Commented Jul 24, 2017 at 14:14
@cteski how can I achieve such functionality, as the one I am after? I would like to have the function flexible enough that I can use it is in many different situations. — Sergio
– Sergio, Commented Jul 24, 2017 at 14:18
Instead of returning rows, maybe you want to yield them? See php.net/manual/en/language.generators.syntax.php. — localheinz
– localheinz, Commented Jul 24, 2017 at 14:18

smcjones · Accepted Answer · 2017-07-24 14:23:30Z

1

When return is triggered, the function ends and every other line of code after it is not executed. If you want to delay the return of your data until later in the function, then create a variable, assign value(s), and return the variable when you're ready.

For example, you can adapt your code to create an array for $data, like so:

function dbGet($conn, $table, $select, $where, $cond) {
    $data = array();
    ...
    while ( $row = $result->fetch_assoc() ) {
        $data[] = $row;
    }
    $stmt->close();
    ...

    $db->close();

    return $data;
}

edited Jul 24, 2017 at 14:23

answered Jul 24, 2017 at 14:19

smcjones

5,6411 gold badge25 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

PHP/MYSQL Query as a Function for Multi-Use

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related