PHP MySQL Select script

Question

I am working on an app that needs to select data from a MySQL database. I am currently testing the PHP script via my browser to make sure that it is returning the correct data. The issue is currently it returns the exception "Database Error!". I have included my PHP script.

get_agencies_by_city.php

<?php

/*
* Following code will get all agencies matching the query
* Returns essential details
* An agency is identified by agency id
*/

require("DB_Link.php");

$city =  ($_GET['City']);

//query database for matching agency
$query = "SELECT * FROM agency WHERE City = $city";

//Execute query
try {
    $stmt   = $db->prepare($query);
    $result = $stmt->execute();
}
catch (PDOException $ex)    {
    $response["success"] = 0;
    $response["message"] = "Database Error!";
    die(json_encode($response));
}

//Retrieve all found rows and add to array
$rows = $stmt->FETCHALL();


if($rows)   {
    $response["success"] = 1;
    $response["message"] = "Results Available!";
    $response["agencys"] = array();

    foreach ($rows as $row) {
        $agency         = array();
        $agency["AgencyID"] = $row["AgencyID"];
        $agency["AgencyName"]   = $row["AgencyName"];
        $agency["Address1"] = $row["Address1"];
        $agency["City"]     = $row["City"];
        $agency["State"]    = $row["State"];
        $agency["Zip"]      = $row["Zip"];
        $agency["Lat"]      = $row["Lat"];
        $agency["Lon"]      = $row["Lon"];

        //update response JSON data
        array_push($response["agencys"], $agency);
    }

    //Echo JSON response
    echo json_encode($response);

} else  {
    $response["success"] = 0;
    $response["message"] = "No Agency found!";
    die(json_encode($response));
}

?>

Here is the DB_Link.php

<?php 

// These variables define the connection information the MySQL database 
// set connection...


$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 


try 
{ 

        $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
} 
catch(PDOException $ex) 
{ 

        die("Failed to connect to the database: " . $ex->getMessage()); 
} 


$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 


$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 


if(function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc()) 
{ 
        function undo_magic_quotes_gpc(&$array) 
        { 
            foreach($array as &$value) 
            { 
                if(is_array($value)) 
                { 
                    undo_magic_quotes_gpc($value); 
                } 
                else 
                { 
                    $value = stripslashes($value); 
                } 
            } 
        } 

        undo_magic_quotes_gpc($_POST); 
        undo_magic_quotes_gpc($_GET); 
        undo_magic_quotes_gpc($_COOKIE); 
} 


header('Content-Type: text/html; charset=utf-8'); 


session_start(); 


?>

Answer 1

You should rewrite your query to this, as it is a prepared statement and your query will be much safer (and working)!

 //your code

try { 
    $statement = $dbh->prepare("SELECT * FROM agency WHERE city = :city");
    $statement->execute(array('city' => $city));

    // rest of your code
 }

   // and the exception

 catch (PDOException $ex) {

       //or include your error statement - but echo $ex->getMessage()
        die('Error!: ' . json_encode($ex->getMessage()));

 }

also you should check if $_GET really is set!

LIKE THIS:

try { 
        $stmt = $dbh->prepare("SELECT * FROM agency WHERE city = :city");
        $stmt->execute(array('city' => $city));
        $rows = $stmt->FETCHALL();


if($rows)   {
    $response["success"] = 1;
    $response["message"] = "Results Available!";
    $response["agencys"] = array();

    foreach ($rows as $row) {
        $agency         = array();
        $agency["AgencyID"] = $row["AgencyID"];
        $agency["AgencyName"]   = $row["AgencyName"];
        $agency["Address1"] = $row["Address1"];
        $agency["City"]     = $row["City"];
        $agency["State"]    = $row["State"];
        $agency["Zip"]      = $row["Zip"];
        $agency["Lat"]      = $row["Lat"];
        $agency["Lon"]      = $row["Lon"];

        //update response JSON data
        array_push($response["agencys"], $agency);
    }

    //Echo JSON response
    echo json_encode($response);

} }

 catch (PDOException $ex) {

           //or include your error statement - but echo $ex->getMessage()
            die('Error!: ' . json_encode($ex->getMessage()));

     }

Answer 2

The variable $city needs to be in your query. Do something like this:

$query = "SELECT * FROM Agency WHERE City = " . $city;