Picking up an array of values from php request and running a mysql query

Question

I have this table which contains the following columns.

Id
place_id
name
latitude
longitude
offers

I have to get the offers given an array of place IDs. But the way I have implemented this, it doesn't return any results. Below is my request.

http://localhost:8001/googleplaces/getOffers.php?place_id[]=%22epc888%22&place_id[]=%22epc999%22

Below is the PHP script I wrote.

<?php
    $response = array();
    $pid =array();
    require_once __DIR__ . '/db_connect.php';
    $db = new DB_CONNECT();
    if (isset($_GET["place_id"])) {
       $pid = $_GET['place_id'];

       $result = mysql_query("SELECT * FROM places WHERE place_id IN ($pid)");

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

        $result = mysql_fetch_array($result);

        $place = array();
        $place["place_id"] = $result["place_id"];
        $place["offers"] = $result["offers"];
        // success
        $response["success"] = 1;

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

        array_push($response["place"], $place);

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

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

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

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

Though there are couple of records satisfying the above request, this is the response I get.

{"success":0,"message":"No offers found"}

What am I doing wrong here? Please advice. I am new to PHP.

Why did you use mysql_ -- these functions are deprecated? Also your code is vulnerable to SQL injection. — trincot
– trincot, Commented Jan 5, 2016 at 13:55
Yes this is a small code piece called through an android application of mine. So I don't mind those :) Could you please tell me where I have gone wrong here? :( — AnOldSoul
– AnOldSoul, Commented Jan 5, 2016 at 13:57
@trincot I did as you said. Please refer the edited question. But I still am getting only one output. Please help :( — AnOldSoul
– AnOldSoul, Commented Jan 5, 2016 at 14:30

trincot · Accepted Answer · 2016-01-05 15:06:37Z

1

As PHP detects the URL parameter place_id[] is of array type, it will present the values as an array. However, you inject the $pid into your SQL. PHP will raise a warning about this, but will still do an implicit conversion and continue. If for instance, if the array was (1, 2, 3), then it would be converted to the string "Array.1,2,3", which leads to invalid SQL syntax.

The very quick solution would be to turn that array into a comma-separated string, like this:

$pid = implode(",", $_GET['place_id']);

However, and this is important: your code is vulnerable to SQL injection. If someone knows the URL to send to your PHP code, they can quite a lot of damage! You should use prepared statements (and move to mysqli or PDO!) with arguments instead.

You should also turn your results processing into a loop to treat all records, and collect them in an array. So replace the if with a while, and directly populate $response["place"]. Note that your if was overwriting the original $results result-set with the array of the first record, which makes you lose the original $result object. But you'll need it for looping over it. So I introduce the variable $row:

// check for empty result
if (mysql_num_rows($result) > 0) {
    // user node
    $response["place"] = array(); // array of places
    while ($row = mysql_fetch_array($result)) {
        $response["place"][] = array(
            "place_id" => $row["place_id"],
            "offers" => $row["offers"]
        );
    }
    // success
    $response["success"] = 1;
    // echoing JSON response
    echo json_encode($response);
}

edited Jan 5, 2016 at 15:06

answered Jan 5, 2016 at 14:09

trincot

357k38 gold badges282 silver badges338 bronze badges

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

3 Comments

AnOldSoul Over a year ago

After adding the line, I am getting only one record. The other one is not returned. Is the way I'm returning the records correct? Does the $place array hold all the output?

trincot Over a year ago

Use while loop, see updated answer. also note you were assigning to $result while getting from $result ---> really bad code!

AnOldSoul Over a year ago

Thanks a lot mate! nailed it.

segFault · Accepted Answer · 2016-01-07 13:01:46Z

1

This: $_GET['place_id'] is an array in your example, so you need to treat it like one in your query.

So you could do this:

$pid = implode(",", $_GET['place_id']);

Not the best way, but it should get it working. You probably want to escape and sanitize that though.

To get all results you need to iterate through them like:

    // success
    $response["success"] = 1;
    // user node
    $response["place"] = array();

    while ($result = mysql_fetch_array($result)) {
        $response["place"][] = array( 
            "place_id" => $result["place_id"],
            "offers" => $result["offers"],
        );
    }

edited Jan 7, 2016 at 13:01

answered Jan 5, 2016 at 14:09

segFault

4,0541 gold badge21 silver badges34 bronze badges

4 Comments

AnOldSoul Over a year ago

After adding the line, I am getting only one record. The other one is not returned. Is the way I'm returning the records correct? Does the $place array hold all the output?

segFault Over a year ago

Yes, I thought that was how you wanted it, if you need to return all results, then you need to iterate through them. I can update my answer.

AnOldSoul Over a year ago

Im getting this error after placing your code :( Parse error: syntax error, unexpected ';', expecting ')' in C:\wamp\www\googleplaces\getOffers.php on line 26

AnOldSoul Over a year ago

Thanks mate I found the solution :)

Collectives™ on Stack Overflow

Picking up an array of values from php request and running a mysql query

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related