Data in the database is not fetched from MySQL in PHP code

Question

I have implemented my database and then I am retrieving data from database via PHP.

In my code I am supposed to filter data by name and if the name exists, I want to print the data. Although the wanted item is in the table, nothing is displayed. I could not understand where the error is. There is only information about the locations in the database , I am not keeping user's locations in my database.

My code is:

<?php
    $username="myusername";
    $password="mypasswd";
    $database="mydatabase";
    $host="mysqlmyhost.com";
    mysql_connect($host,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");

    // check for post data
    if (isset($_GET["locationName"])) {
        $locationName = $_GET['locationName'];

        // get a product from products table
        $result = mysql_query("SELECT *FROM Location WHERE locationName =   $locationName");

        if (!empty($result)) {
            // check for empty result
            if (mysql_num_rows($result) > 0) {
                $result = mysql_fetch_array($result);

                $product = array();
                $product["locationName"] = $row["locationName"];
                $product["locationInfo"] = $row["locationInfo"];
                $product["locationLatitude"] = $row["locationLatitude"];
                $product["locationLongitude"] = $row["locationLongitude"];
                $product["locationPic"] = $row["locationPic"];
                $product["city"] = $row["city"];
                // success
                $response["success"] = 1;

                // user node
                $response["Location"] = array();

                array_push($response["Location"], $product);

                // echoing JSON response
                echo json_encode($response);
            } else {
                // no product found
                $response["success"] = 0;
                $response["message"] = "No product found";

                // echo no users JSON
                echo json_encode($response);
            }
        } else {
            // no product found
            $response["success"] = 0;
            $response["message"] = "No product found";

            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // required field is missing
        $response["success"] = 0;
        $response["message"] = "Required field(s) is missing";

        // echoing JSON response
        echo json_encode($response);
    }
?>

Edited :

Now,I would like to show the nearest locations to the user . I have searched and changed the code . I have an error on line beginning with $userLatitude=$_GET['userLatitude']; . It says there's an unexpected t_string error in here . I could not understand what the error is . Could you help again ?

   if (isset($_GET["userLatitude"]) && isset($_GET["userLongitude"])) {
$userLatitude=$_GET['userLatitude'];
$userLongitude=$_GET['userLongitude'];
$result = mysql_query("SELECT locationName, ( 6371 * acos( cos( radians(      $userLatitude) ) * cos( radians( locationLatitude ) ) * cos( radians( locationLongitude ) - radians( $userLatitude) ) + sin( radians($userLongitude) ) * sin( radians( locationLatitude) ) ) ) AS distance 
FROM Location HAVING distance < 2 ORDER BY distance LIMIT 0 ,20") or die(mysql_error());
echo $result;


  // check for empty result
  if (mysql_num_rows($result) > 0) {
    // looping through all results
    // products node
    $response["Location"] = array();

    while ($row = mysql_fetch_array($result)) {
    // temp user array
    $product = array();
    $product["locationName"] = $row["locationName"];
    $product["locationInfo"] = $row["locationInfo"];
    $product["locationLatitude"] = $row["locationLatitude"];
    $product["locationLongitude"] = $row["locationLongitude"];
    $product["locationPic"] = $row["locationPic"];
    $product["city"] = $row["city"];



    // push single product into final response array
    array_push($response["Location"], $product);
}
// success
$response["success"] = 1;

// echoing JSON response
echo json_encode($response);
   } else {
// no products found
$response["success"] = 0;
$response["message"] = "No products found";

// echo no users JSON
echo json_encode($response);
 }
 mysql_close();

$locationName seems to be a string so it need quotes : '$locationName'. Note that you should use mysqli or pdo or at least sanitize your input before using it in you query. — RafH, Apr 20 '13 at 21:10
You are vulnerable to [SQL injection attacks](http://bobby-tables.com). Fix that before you even THINK about fixing the rest of your code. — Marc B, Apr 20 '13 at 21:12
I have got {"success":0,"message":"No product found"} message . mysql_num_rows($result) is 0 — user2086258, Apr 20 '13 at 21:12
Now,I would like to show the nearest locations to the user . I have searched and changed the code . I have an error on line beginning with $userLatitude=$_GET['userLatitude']; . It says there's an error in here . I could not understand what the error is . Could you help again please ? — user2086258, Apr 22 '13 at 08:09
I think I understand my error , because the php is getting string values , I should convert string values to numbers in php code — user2086258, Apr 22 '13 at 08:34
how can i convert the string values to decimal(10,6) geopoint format ? — user2086258, Apr 22 '13 at 10:08
I could convert the string to the format thanks to http://stackoverflow.com/questions/2434374/converting-a-string-to-a-mysql-decimal-type . — user2086258, Apr 23 '13 at 18:35

Silvertiger · Answer 1 · 2013-04-20T21:40:02.227

A few thoughts:

You may want to add single quotes inside the query as some text could have spaces or special characters and SQL loves boundaries. If you don't like the double quotes around it, it still should work fine, but it's become a habit for me.

Possible code:

// get a product from products table
$result = mysql_query("SELECT *FROM Location WHERE locationName = '".$locationName."'");

When you stored your data in the database, did you account for escaped characters (i.e. did you use mysql_real_escape_string() to add slashes to the data as it was inserted to prevent injection attacks or faulty data.). If so you may want to use the same function to verify the data and they use stripslashes() function to return "normalized" data to insert into your response.

Data example within the database: that/'s what i/'m talkin/' abuot /"fella/'/"!

Possible code:

$locationName = mysql_real_escape_String($_GET['locationName']);

and

$product["locationName"] = stripslashes($row["locationName"]);

You can add error checking and/or display messages to help troubleshoot

INline errors display for mysql queries: (This will stop the page on an error and display the MySQL returned message)

$result = mysql_query("SELECT *FROM Location WHERE locationName = $locationName") or die(mysql_error());

general error reporting in PHP: (this one is a bit verbose and displays warnings too)

// Same as error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);

Verbose output Sometimes I add verbose messages within a page so i can validate the queries i submit and either remove or comment them out after the script is tested.

$locationQuery= mysql_query("SELECT *FROM Location WHERE locationName = '".$locationName."'");
echo "Current Query: ".$locationQuery."<br>\n";
$result = mysql_query($locationQuery) or die(mysql_error());

Ozerich · Answer 2 · 2013-04-20T21:20:33.477

0

1.

SELECT *FROM

You forget space after *

2.

WHERE locationName = $locationName"

you should to wrap variable in quotes

WHERE locationName = \"$locationName\""

3.

delete this condition if (!empty($result)) {, because $result is resource and it is not empty

4.

you use $row variable, change line to $row = mysql_fetch_array($result);

write always or die(mysql_error())

mysql_query('....') or die(mysql_error());

edited Apr 20 '13 at 21:20

answered Apr 20 '13 at 21:10

Ozerich

2,000
1
18
25

oddly enough, `select *from` does work in mysql. looks weird, but it's valid. – Marc B Apr 20 '13 at 21:12
@MarcB, thanks. I dont prefer use weird constructions, I am for clean code – Ozerich Apr 20 '13 at 21:26
Thank you ! I have considered yours adivces and it's working right now ! – user2086258 Apr 20 '13 at 21:36
I would like to show the nearest locations to the user . I have searched and changed the code . I have an error on line beginning with – user2086258 Apr 22 '13 at 07:57

score 0 · Answer 3 · answered Apr 20 '13 at 21:13

In the sample code, you are storing the returned data in the $result variable but trying to access it from the $row variable

$result = mysql_fetch_array($result);

$product = array();
$product["locationName"] = $row["locationName"];

Try

$row = mysql_fetch_array($result);

$product = array();
$product["locationName"] = $row["locationName"];

Data in the database is not fetched from MySQL in PHP code

3 Answers3