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 {
        echo 'No results';
    }
}


?>

put on hold as off-topic by Toby Speight, Zeta, Sᴀᴍ Onᴇᴌᴀ, vnp, 200_success yesterday

This question appears to be off-topic. The users who voted to close gave these specific reasons:

  • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – Sᴀᴍ Onᴇᴌᴀ, vnp, 200_success
  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – Toby Speight, Zeta
If this question can be reworded to fit the rules in the help center, please edit the question.

  • are there any columns in the lecturers_general table that should not go into JSON data? – Sᴀᴍ Onᴇᴌᴀ Aug 10 at 21:24
  • Nope... all columns are to be returned. – Beulah Akindele Aug 10 at 21:31
  • 1
    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 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 {
    echo 'No results';
}

That way there is no need to add extra variables (e.g. $lecturer_id, $title, etc.).

  • Sorry I posted old code... there is a query that supposed to be searched but all the same, all columns are to be returned. Your answer also doesn't secure against SQL injection. – Beulah Akindele Aug 10 at 22:10
  • okay - I updated the code per your update – Sᴀᴍ Onᴇᴌᴀ Aug 10 at 22:22
  • I am a bit confused at the while statement. I haven't come across &$data. – Beulah Akindele Aug 11 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 at 19:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.