How do I retrieve multiple rows of data?

Question

How do I retrieve multiple rows of data? Currently I am only able to retrieve one row of data but I want to retrieve multiple rows of data.

I want to retrieve the vouchers a user have in the database. E.g 'name' of user is mark , I want to retrieve every row that the column 'name' is mark.

// check for post data
 if (isset($_GET["name"])) {
 $name = $_GET['name'];

$result = mysql_query("SELECT * FROM coupons WHERE name = '$name'");

if (!empty($result)) {
    // check for empty result
    if (mysql_num_rows($result) > 0) {

        $result = mysql_fetch_array($result);

        $coupons = array();
        $coupons["couponcreated"] = $result["couponcreated"];
        $coupons["couponexpires"] = $result["couponexpires"];
        $coupons["coupondetails"] = $result["coupondetails"];
        // success
        $response["success"] = 1;

        // user node
        $response["coupons"] = array();

        array_push($response["coupons"], $coupons);

        // echoing JSON response
        echo json_encode($response);

First, you should be using prepared statements in order to reduce the risk of [SQL injection](http://bobby-tables.com/). Second, [mysql_query is obsolete](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). Third, you can loop over your results `while ($row = mysql_fetch_array($result)) { //$row['couponcreated'] contains this row's value for the couponcreated column } — devlin carnate, Dec 16 '15 at 03:44

Jake Psimos · Accepted Answer · 2015-12-16T02:59:14.617

// check for post data
 if (isset($_GET["name"])) {
 $name = $_GET['name'];

$queryResult = mysql_query("SELECT * FROM coupons WHERE name = '$name'");

if (!empty($result)) {
    // check for empty result
    if (mysql_num_rows($result) > 0) {

        $result = array();
        while($row = mysql_fetch_array($queryResult)){
           array_push($row, $result);
        }


        //Now result array has all the rows!

        $coupons = array();
        $coupons["couponcreated"] = $result["couponcreated"];
        $coupons["couponexpires"] = $result["couponexpires"];
        $coupons["coupondetails"] = $result["coupondetails"];
        // success
        $response["success"] = 1;

        // user node
        $response["coupons"] = array();

        array_push($response["coupons"], $coupons);

        // echoing JSON response
        echo json_encode($response);

Also, you must protect your $name variable and sanitize any naughty strings from it so nobody can steal your SQL data or inject data by means of SQL Injection attack.

This is an example although it is not perfect:

$name = htmlspecialchars($name);

Filter any SQL commands from $name by using str_replace or whatever string manipulation function you know of.

actually you can change $result = mysql_fetch_array($result) to the loop and once the loop is done then your array will be fully populated — Jake Psimos, Dec 16 '15 at 02:55
I modded the answer accordingly. Result will now contain an array of arrays so you will need to mod aswell — Jake Psimos, Dec 16 '15 at 02:59

How do I retrieve multiple rows of data?

1 Answers1