# Encoding multiple rows from prepared statement in php [closed]

I have this code that hypothetically returns multiple rows from a database and encodes them in a JSON. I am wondering if this is the best and most presentable way to do it.

Note: I am using msqli not PDO, and procedural php not OOP.

<?php
$query =$_REQUEST["query"];
require(connect.php);

$response = array();$response["success"] = false;

if ($con) {$statement = mysqli_prepare($con, "SELECT * FROM lecturers_general WHERE ? IN (lecturer_id, title, first_name, middle_name, last_name, course1, course2, course3, course4, course5, course6, office_number, office_building, department)"); mysqli_stmt_bind_param($statement, "s", $query); mysqli_stmt_execute($statement);
$lecturer_id = null;$title = null; $first_name = null;$middle_name = null; $last_name = null;$course1 = null; $course2 = null;$course3 = null; $course4 = null;$course5 = null; $course6 = null;$office_number = null; $office_building = null;$department = $null; mysqli_stmt_bind_result($lecturer_id, $title,$first_name, $middle_name,$last_name, $course1,$course2, $course3,$course4, $course5,$course6, $office_number,$office_building, $department); //$rows = mysqli_fetch_assoc($statement);$num_rows = mysqli_num_rows($statement); if ($num_rows >= 1) {
do {
$lecturer_id =$rows["lecturer_id"];
$title =$rows["title"];
$first_name =$rows["first_name"];
$middle_name =$rows["middle_name"];
$last_name =$rows["last_name"];
$course1 =$rows["course1"];
$course2 =$rows["course2"];
$course3 =$rows["course3"];
$course4 =$rows["course4"];
$course5 =$rows["course5"];
$course6 =$rows["course6"];
$office_number =$rows["office_number"];
$office_building =$rows["office_building"];
$department =$rows["department"];

$result['lecturers: '][] = array('lecturer_id' =>$lecturer_id,'title' => $title,'first_name' =>$first_name,'middle_name' => $middle_name,'last_name' =>$last_name,'course1' => $course1,'course2' =>$course2,'course3' => $course3,'course4' =>$course4,'course5' => $course5,'course6' =>$course6,'office_number' => $office_number,'office_building' =>$office_building,'department' => $department); } while($rows = mysqli_fetch_assoc($statement)); echo json_encode($result);
} else {
}
}

?>

• are there any columns in the lecturers_general table that should not go into JSON data? – Sᴀᴍ Onᴇᴌᴀ Aug 10 '18 at 21:24
• Nope... all columns are to be returned. – Beulah Akindele Aug 10 '18 at 21:31
• this code has several errors that would prevent it from working. require(connect.php); is a syntax error for example. this question is off topic as it is asking how to do a certain task (whith dozens answers on Stack Overflow) not to review a working code – Your Common Sense Nov 9 '18 at 7:12

I am using msqli not PDO, and procedural php not OOP.

While you should.

Because it will make the whole mess into three blimmin' lines

<?php
require 'connect.php';

$statement =$con->prepare("SELECT * FROM lecturers_general WHERE ? IN (lecturer_id, title, first_name, middle_name, last_name, course1, course2, course3, course4, course5, course6, office_number, office_building, department)");
$statement->execute([$_REQUEST["query"]]);
echo json_encode(['lecturers: ' => $statement->fetchAll()]);  Because you replied to my question: are there any columns in the lecturers_general table that should not go into JSON data? With the answer: Nope... all columns are to be returned. Initially I thought one could utilize mysqli_query() and mysqli_fetch_all() but that apparently didn't work unless the native MySQL driver is installed. But to borrow from a technique outlined in the User Contributed note by anonunfinder at gmail dot com, the field names can be fetched via mysqli_stmt_result_metadata() //check connection, prepare statement, execute$statement
mysqli_stmt_execute($statement);$data = array();
$variables = array($statement);
$meta_statement = mysqli_stmt_result_metadata($statement);
while($field = mysqli_fetch_field($meta_statement)) {
$variables[] = &$data[$field->name]; }  Reference Assignment is used within the while loop: &$data[$field->name] to push the value of each field's value from $data into the array $variables. This SO answer gives more explanation about that concept. Then utilize call_user_fun_array() to bind the result variables from $variables:

//dynamically pull the variables from the bound statement
call_user_func_array('mysqli_stmt_bind_result', $variables);$lecturers = array();
for($i=0;$row = mysqli_stmt_fetch($statement);$i++) {
$lecturers[$i] = array();
foreach($data as$key => $value) {$lecturers[$i][$key] = $value; } }  Then check the count of $lecturers - if it is greater than zero, encode the results - otherwise return the no results string literal.

if (count($lecturers) >= 1) { echo json_encode(array('lecturers: ' =>$lecturers));
} else {

That way there is no need to add extra variables (e.g. $lecturer_id, $title, etc.).
• I am a bit confused at the while statement. I haven't come across &$data. – Beulah Akindele Aug 11 '18 at 19:25 • I am not sure of how to use it. This is my attempt: $variables[] = &$result[$lecturer_id -> 'lecturer_id']; – Beulah Akindele Aug 11 '18 at 19:42