Query MySQL with PHP

Question

I am trying to query a MySQL database with PHP and return the results as JSON. I'm new to PHP and web development so I'm not sure what I'm doing wrong. I've set up the database using MAMP. My parameters are being printed but I'm not getting the JSON. I've gotten this far with the help of a tutorial.

EDIT: I just went into phpMyAdmin to make sure it was working and when I click on Server:localhost:8889, a window pops up that says Error in processing request. Error code 404.

I'm thinking this is the problem, I'm just not sure why it isn't working. I may reinstall MAMP.

    <?php

$user = 'root';
$password = 'root';
$db = 'TestDB';
$host = '127.0.0.1';
$port = '8889';

$first_name = filter_input(INPUT_GET, 'first_name');
$last_name = filter_input(INPUT_GET, 'last_name');
$membership_number = filter_input(INPUT_GET, 'membership_number');

echo $first_name;
echo $last_name;
echo $membership_number;

// Create connection
    // $con = mysqli_connect("localhost", "root", "root", "TestDB");
    // $con = mysqli_connect("localhost", "root", "root", "TestDB", "8889", $socket);
    $link = mysqli_init();
    $con = mysqli_real_connect($link, $host, $user, $password, $db, $port);

    // Check connection
    if(mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $sql = "SELECT * FROM NAME WHERE FIRST_NAME = \'$first_name\' and LAST_NAME = \'$last_name\' and MEMBERSHIP_NUMBER = \'$membership_number\'";

    $result = mysqli_query($con, $sql);

    if(!$result) {
        die('Query failed: ' . mysqli_error());
    }

    // Check for results
    // if ($result = mysqli_query($con, $sql)) {
    if($result) {
        // If there are results, create results array and a temporary one to hold the data
        $resultArray = array();
        $tempArray = array();

        // Loop through each row in the result set
        // while($row = $result->fetch_object()) {
        while($row = mysqli_fetch_object($result)) {
            // Add each row to the results array
            $tempArray = $row;
            array_push($resultArray, $tempArray);
        }

        echo $tempArray;
        echo $resultArray;
        echo $result;

        echo json_encode($resultArray);
    }

    // Close connections
    mysqli_close($con);

?>

Answer 1

You need to change you $sql variable to remove the escapes on the single quotes. They register as part of the string because you are using double-quotes to wrap it. Basically, you're telling the database to run the query "SELECT * FROM NAME WHERE FIRST_NAME = \'John\' and LAST_NAME = \'Smith\' and MEMBERSHIP_NUMBER = \'VRX78435\'". This will error if you run it directly because the escape characters are not escaping.

$sql = "SELECT * FROM NAME WHERE FIRST_NAME = '$first_name' and LAST_NAME = '$last_name' and MEMBERSHIP_NUMBER = '$membership_number'";

That should fix it for you.

There may also be an issue with your connection to the server. mysqli_query() uses the results of mysqli_connect() to run the query. mysqli_real_connect() only returns a boolean value, so it is invalid for this particular use (at least it failed to work on my server).

This would be a simple matter of replacing the $con and then you can drop the $link variable.

$con = mysqli_connect($host, $user, $password, $db, $port);

These changes, and assuming the $first_name, $last_name, and $membership_number are all valid, allowed your script to run for me, so I hope this helps.