How to select row by row and set to array in MYSQL?

Question

I want to select row by row and set to array in MYSQL

I have this code:

  public function getWinnerBonus($email){
    $result = mysql_query("SELECT * FROM winers WHERE `email` = '$email'") or die(mysql_error());
    // return user details
    if ($result){
        $respons               = mysql_fetch_array($result);
        $response["error"] = FALSE;
        $response["WinnerInfo"]["price"] = $respons["price"];
        $response["WinnerInfo"]["level"] = $respons["level"];
        $response["WinnerInfo"]["win_date"] = $respons["win_date"];
        $response["WinnerInfo"]["payed"] = $respons["payed"];
        $response["WinnerInfo"]["pay_date"] = $respons["pay_date"];

    }else{
        $response["error"] = TRUE;
    }
    return $response;
}

but this code return just first founded row

How to select row by row and set to array in MYSQL?

Sidenote: GROUP BY comes after a WHERE clause in a SELECT. dev.mysql.com/doc/refman/5.0/en/select.html - Your mysql_error() should have thrown you a syntax error, but you didn't share that with us. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 30, 2015 at 12:02
"but this code return just first founded row" - I sincerely doubt that. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 30, 2015 at 12:07

Community · Accepted Answer · 2017-05-23 12:23:00Z

1

You need to loop over the result set not just one time.

$respons = array();
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
  $respons[$i]["error"] = FALSE;
  $respons[$i]["WinnerInfo"]["price"] = $row["price"];
  $respons[$i]["WinnerInfo"]["level"] = $row["level"];
  $respons[$i]["WinnerInfo"]["win_date"] = $row["win_date"];
  $respons[$i]["WinnerInfo"]["payed"] = $row["payed"];
  $respons[$i]["WinnerInfo"]["pay_date"] = $row["pay_date"];
  ++$i;
}

Note: Never use mysql_ functions, they are deprecated. Use mysqli_ or PDO instead, along with a prepared statement.

References:

Also make sure that you have successfully connected to your database using the same API as your query.

Different MySQL APIs do not intermix.

Consult the following:

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Oct 30, 2015 at 8:00

Pupil

24k6 gold badges50 silver badges71 bronze badges

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

3 Comments

Funk Forty Niner Over a year ago

while ($row = mysql_fetch_assoc($result)) I think that should read as while ($respons= mysql_fetch_assoc($result))

Funk Forty Niner Over a year ago

GROUP BY comes after a WHERE clause. That I know for sure. dev.mysql.com/doc/refman/5.0/en/select.html

shirin Over a year ago

Tank you @Fred-ii- , but I got error: Notice: Undefined index: win_date in

Basti909851 · Accepted Answer · 2015-10-30 08:03:47Z

0

Yes, because you have to loop over the result set. Here is how you could do it:

$resultArray = array();
while($response = mysql_fetch_array($result, MYSQL_NUM) {
    $resultArray[] = $response; //This adds one result set to the array
}

I hope I could help you.

answered Oct 30, 2015 at 8:03

Basti909851

1278 bronze badges

1 Comment

shirin Over a year ago

Undefined variable 'response'

Collectives™ on Stack Overflow

How to select row by row and set to array in MYSQL?

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related