1
\$\begingroup\$

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';
    }
}


?>
| improve this question | | | | |
\$\endgroup\$
  • \$\begingroup\$ are there any columns in the lecturers_general table that should not go into JSON data? \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Aug 10 '18 at 21:24
  • \$\begingroup\$ Nope... all columns are to be returned. \$\endgroup\$ – Beulah Akindele Aug 10 '18 at 21:31
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ – Your Common Sense Nov 9 '18 at 7:12
2
\$\begingroup\$

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()]);
| improve this answer | | | | |
\$\endgroup\$
0
\$\begingroup\$

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.).

| improve this answer | | | | |
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Beulah Akindele Aug 10 '18 at 22:10
  • \$\begingroup\$ I am a bit confused at the while statement. I haven't come across &$data. \$\endgroup\$ – Beulah Akindele Aug 11 '18 at 19:25
  • \$\begingroup\$ I am not sure of how to use it. This is my attempt: $variables[] = &$result[$lecturer_id -> 'lecturer_id']; \$\endgroup\$ – Beulah Akindele Aug 11 '18 at 19:42

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