How can I use a for loop in PHP to return mysql data

Question

I am making an app that will list employees by a certain store in a listview. My current function in my DB_Functions.php file is:

public function getEmployeeList($name) {
    $stmt = $this->con->prepare("SELECT employee_name FROM employees WHERE name = ?");

    $stmt->bind_param('s', $name);

    if ($stmt->execute()) {
        $employee_list = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if (empty($employee_list)) {
            return NULL;
        } else {
            return $employee_list;
        }
    }
}

and in my employees.php file I have the following code:

<?php

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

$response = array('error' => FALSE);

if (isset($_POST['name'])) {
    $name = $_POST['name'];

    $employee_list = $db->getEmployeeList($name);

    if ($employee_list != false) {
        $response['error'] = FALSE;
        //EMPLOYEE LIST OBJECT HERE
    } else {
        $response['error'] = TRUE;
        $response['error_msg'] = 'No employees have been added to this profile.';

        echo json_encode($response);
    }
} else {
    $response['error'] = TRUE;
    $response['error_msg'] = 'You have not logged in to your store\'s account, please log in first.';

    echo json_encode($response);
}

?>

I would like to have an employee_list object in the commented space above. Something like:

$response['employee_list']['0'] = $employee_list['0'];
$response['employee_list']['1'] = $employee_list['1'];
$response['employee_list']['2'] = $employee_list['2'];

etc... etc...

After that JSONObject is returned to the android app, the contents will be listed in a listview. I would need a for loop (I think) because the employee number will never be known since each store will be able to add and remove employees as they wish. Can someone point me in the right direction and also advise if I am using the correct approach as far as the rest of the code. Thanks.

awoyotoyin · Accepted Answer · 2016-03-13 11:50:48Z

3

First, in your DB_Functions.php, you should be returning the mysqli_result object. Hence your DB_Functions should be this:

public function getEmployeeList($name) {
    $stmt = $this->con->prepare("SELECT employee_name FROM employees WHERE name = ?");

    $stmt->bind_param('s', $name);

    if ($stmt->execute()) {
        // we get the mysqli_result object without calling the fetch_assoc() on it
        $result = $stmt->get_result();
        $stmt->close();

        // if the count is less than 1, no result found, hence return null
        if ($result->num_rows < 1) {
            return null;
        } else {
            // we return the mysqli_result object without calling the fetch_assoc() on it
            return $result;
        }
    }
}

In your employees.php, what you want is something like this:

<?php

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

$response = array('error' => FALSE);

if (isset($_POST['name'])) {
    $name = $_POST['name'];

    $result = $db->getEmployeeList($name);

    // do an early check for if result returns null or is not set
    if (is_null($result) || !$result) {
        $response['error'] = TRUE;
        $response['error_msg'] = 'No employees have been added to this profile.';
    } else {
        $response['error'] = FALSE;
        //EMPLOYEE LIST OBJECT HERE
        // since $result->fetch_assoc() returns one row at a time, you want to loop through each row and get the appropriate data
        while ($row = $result->fetch_assoc()) {
            // inject the current into the employee_list array
            $response['employee_list'][] = $row;
        }
    }
} else {
    $response['error'] = TRUE;
    $response['error_msg'] = 'You have not logged in to your store\'s account, please log in first.';
}

// echo response gets called no matter what
echo json_encode($response);

Hope it helps

edited Mar 13, 2016 at 11:50

answered Mar 13, 2016 at 11:45

awoyotoyin

7904 silver badges8 bronze badges

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

1 Comment

Alexiz Hernandez Over a year ago

Thank you so much!! +1. I managed to get the results into the app, I can see it in the log like this: {"error":false,"employee_list":[{"employee_name":"Jason Matthews"},{"employee_name":"Tori Wilson"}]} Now, I just have to figure out how to put all of that in a selectable list. Thank you very much for your help.

Collectives™ on Stack Overflow

How can I use a for loop in PHP to return mysql data

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