I would like to select the last 4 rows of my table and then put them into a JSON array with a number attached to each piece of data so I can access them later, I would think a loop but don't know how to do it with the counter being the number
The part that gets the last four rows
SELECT title FROM questions ORDER BY id DESC LIMIT 4
And the array be like 1: data one, 2: data two...
Aron you can use the following code:-
$mysqli = new mysqli("localhost", "dbusername", "dbpassword", "sakila");
$x = 1;
$return_arr = array();
$result = $mysqli->query("SELECT title from questions ORDER By id DESC LIMIT 4");
while( $obj = $result->fetch_object() ) {
$row_array[$x] = $obj->title;
$x++;
}
$result->close();
array_push($return_arr,$row_array);
echo json_encode($return_arr);`//result [{"1":"title1","2":"title2","3":"title3}]
Here in the loop, I am storing the value inside an array($row_array). Here $x will contain the number(key). Then the complete result is pushed into a new array and the result is converted into a json array.
Here's what the code would look like:
$mysqli = new mysqli('localhost', 'user', 'pass', 'database');
$stmt = $mysqli->query('SELECT title FROM questions ORDER BY id DESC LIMIT 4');
echo json_encode($stmt->fetchAll(MYSQLI_NUM));
Check the below code using PDO, you can change to mysqli also
$dbh = new PDO('mysql:host=localhost:33060;dbname=demo', 'root', '');
$sth = $dbh->prepare("SELECT title FROM questions ORDER BY id DESC LIMIT 4");
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_COLUMN);
//Key should be string, otherwise, we can not encode as json
foreach($result as $key => $value) {
$newkey = sprintf('%s',$key);
$newArray["k$newkey"] = $value;
}
echo json_encode($newArray); //result: {"k0":"title 4","k1":"title 3","k2":"title 2","k3":"title 1"}
