Return multiple rows of an array from a mysqli query

Question

I have this in my functions.php file

function getUserOrders($userId){
    global $conn;
    $query = "SELECT * ";
    $query .= "FROM orders ";
    $query .= "WHERE userid=" . $userId . " ";      
    $odrset = mysqli_query($conn, $query);  

    while ($odr = mysqli_fetch_assoc($odrset)){
        return $odr;
    } 
}

What I neeed to do in my orders.php file is display specific fields and their values from the returned $odr array as this snippet suggests

$userId = sql_prep($_SESSION['userid']) ;
getUserOrders($userId);
echo $odr['title'].$odr['orderid'].'<br>'

I am only able to do it in the functions.php file...

function getUserOrders($userId){
    global $conn;
    $query = "SELECT * ";
    $query .= "FROM orders ";
    $query .= "WHERE userid=" . $userId . " ";
    $odrset = mysqli_query($conn, $query);
    confirm_query($odrset);

    while ($odr = mysqli_fetch_assoc($odrset)){
        echo $odr['title'].$odr['orderid'].'<br>';
    } 
}

..and calling it in my orders.php file like so:

$userId = sql_prep($_SESSION['userid']) ;
getUserOrders();

which is not good since i need to recycle the function somewhere else and display different fields and their values. So I need to have $odr returned as an array in my order.php

Questions or issues with answers?

chris85
– chris85

2016-02-09 05:54:22 +00:00
Commented Feb 9, 2016 at 5:54 — chris85
– chris85, Commented Feb 9, 2016 at 5:54

chris85 · Accepted Answer · 2016-02-08 01:15:17Z

6

Store it as an array and then return the array.

function getUserOrders($userId){
    global $conn;
    $query =
      "SELECT * 
         FROM orders 
        WHERE userid= ?";    
    $odrset = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($odrset, 'i', $userId);
    mysqli_stmt_execute($odrset);

    while ($odr = mysqli_fetch_assoc($odrset)){
        $return[] = $odr;
    }
    return $return;
}

I've updated your mysqli connection to use a parameterized query with prepared statement. You can read more about these here, http://php.net/manual/en/mysqli.quickstart.prepared-statements.php. This is the preferred approach than escaping.

Later usage...

$orders = getUserOrders($_SESSION['userid']);
foreach($orders as $order) {
     echo $order['title'] . $order['orderid'];
}

You may not need the sql_prep function with this approach, I'm not sure what that did. Your questions code didn't pass the userid to the function so I don't think that was your exact usage.

edited Feb 8, 2016 at 1:15

answered Feb 8, 2016 at 1:04

chris85

23.9k7 gold badges36 silver badges51 bronze badges

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

2 Comments

Sean Over a year ago

Just nitpicking, but should probably include the OPs $userId = sql_prep($_SESSION['userid']) ; as it looks like they have some minor sanitation, instead of directly inserting into getUserOrders()

chris85 Over a year ago

@Sean true, I've update to use prepared statements. Thanks.

Zill Christian · Accepted Answer · 2016-02-08 01:11:05Z

0

mysqli_fetch_assoc only returns one record at a time so you need to store the results inside an array and return the array from the function:

// functions.php
function getUserOrders($userId){
    global $conn;
    $query = "SELECT * ";
    $query .= "FROM orders ";
    $query .= "WHERE userid=" . $userId . " ";      
    $odrset = mysqli_query($conn, $query);  

    $results = array();
    while ($odr = mysqli_fetch_assoc($odrset)){
        $results[] = $odr;
    }

    return $results;
}


// in your orders file
$userid = sql_prep($_SESSION['userid']);
$orders = getUserOrders($userid);

foreach ($order as $orders) {
    echo $order['title']. $order['orderid'] . '<br>';
}

answered Feb 8, 2016 at 1:11

Zill Christian

411 bronze badge

Collectives™ on Stack Overflow

Return multiple rows of an array from a mysqli query

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