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(); 


?>

Have you tried to look at the content of the PDO exception? That can give you info on what's the problem — AlexL
– AlexL, Commented Nov 4, 2014 at 23:18
If you can run the SQL statements directly on the server (say with mysql command line program) that would make it a lot easier to debug and see error messages. — Kim Ryan
– Kim Ryan, Commented Nov 4, 2014 at 23:20
You need to wrap $city in single quotes in your query; you should echo $ex->getMessage() as part of your error message. Even though you're using prepared statements your code is susceptible to SQL injection because of the way you've constructed your query. — user1864610
– user1864610, Commented Nov 4, 2014 at 23:20
Is $_GET['City'] returning a correct value? If so, is it a string? You may need single quotes in your sql. Also it might be helpful to add $ex->getMessage() to your "Database Error!" string. — zack.lore
– zack.lore, Commented Nov 4, 2014 at 23:23

baao · Accepted Answer · 2014-11-05 00:38:57Z

2

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()));

     }

edited Nov 5, 2014 at 0:38

answered Nov 4, 2014 at 23:26

baao

73.5k18 gold badges152 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

IrishCarBomb Over a year ago

Made the suggested changes and now I get "Call to a member function prepare() on a non-object" at line 20

baao Over a year ago

change $dbh->prepare to $db->prepare, sorry, I didn't know what variable you are using therefor

IrishCarBomb Over a year ago

good catch, these are only test creds though. I have removed. Also made the next change and now I get "Call to a member function FETCHALL() on a non-object" at line 30. Thanks so much for the help. This has been eluding me for the last two days.

baao Over a year ago

AHHH, you have to change $statement to $stmt, then it fits the rest of your code again

IrishCarBomb Over a year ago

I have a second script that has two values instead of just the one. So would that make this line "$stmt->execute(array('city' => $city));" Look like this instead "$stmt->execute(array('userid' => $userid)&('agencyid' => $agencyid));

JMc · Accepted Answer · 2014-11-04 23:25:55Z

0

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

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

answered Nov 4, 2014 at 23:25

JMc

3551 silver badge6 bronze badges

2 Comments

user1864610 Over a year ago

The OP's version is equivalent to this. Variable names present in a double-quoted string will have their value substituted when the string is used.

JMc Over a year ago

Good call. I was thinking of what happens when single quotes are used.

Collectives™ on Stack Overflow

PHP MySQL Select script

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related