Retrieve Multiple Rows from mysql [duplicate]

Question

I am working on an android app project which uses this php script to retrieve the data from MySql database:

<?php
$con=mysqli_connect("localhost","root","","webservice");

$item_name = "Navnita";


$statement = mysqli_prepare($con, "SELECT * FROM reviews WHERE item_name = ?");
mysqli_stmt_bind_param($statement, "s", $item_name);
mysqli_stmt_execute($statement);

mysqli_stmt_store_result($statement);
mysqli_stmt_bind_result($statement, $item_name, $username, $review);

$revs = array();

while(mysqli_stmt_fetch($statement)){
    $revs["item_name"] = $item_name;
    $revs["username"] = $username;
    $revs["review"] = $review;              
}

echo json_encode($revs);
mysqli_close($con);

?> I wanted to retrieve all the rows having item_name = "Navnita" but it only retrieves one row:

{"item_name":"Navnita","username":"Jenny","review":"Food is just great!"}

The database contains 3 rows having item_name = "Navnita". Please help me out guys! I am not that good at php and database as well :|

$revs["item_name"][] = $item_name; all your data add []

aldrin27
– aldrin27

2015-09-19 07:45:50 +00:00
Commented Sep 19, 2015 at 7:45 — aldrin27
– aldrin27, Commented Sep 19, 2015 at 7:45

Niki van Stein · Accepted Answer · 2015-09-19 07:48:48Z

0

For that you need two arrays. Most logical is to store each row in one array and than store this array into another like:

$revs = array();
$allrevs = array();

while(mysqli_stmt_fetch($statement)){
    $revs["item_name"] = $item_name;
    $revs["username"] = $username;
    $revs["review"] = $review;       
    $allrevs[] = $revs;       
}

echo json_encode($allrevs);

answered Sep 19, 2015 at 7:48

Niki van Stein

10.8k3 gold badges33 silver badges63 bronze badges

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

1 Comment

Jayant Usrete Over a year ago

Thanks @Bas van Stein, that's what i was looking for.

Rohit Kumar · Accepted Answer · 2015-09-19 07:52:39Z

-1

You are assigning values on same variable , as they are not appended they are overwriting them . Do as below

$revs = array();

while(mysqli_stmt_fetch($statement)){
   array_push( $revs,array("item_name"=>$item_name,"username"=>$username,"review"=>$review));   

}

It will give your desired result.

answered Sep 19, 2015 at 7:52

Rohit Kumar

1,9581 gold badge12 silver badges17 bronze badges

Collectives™ on Stack Overflow

Retrieve Multiple Rows from mysql [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related