0

I have below select statement which is returning Nearby Available user_id's with some other column value

$sql="(SELECT
    user_location.*,
    driver_location.user_id as user_id,
    ( 3959 * acos( cos( radians(37.4219983) ) * cos( radians( driver_location.latitude ) ) * cos( radians( driver_location.longitude ) - radians(-122.0844) ) + sin( radians(37.4219983) ) * sin(radians(driver_location.latitude)) ) ) AS distance 
FROM user_location, driver_location
HAVING distance >= 0)";

Now For each user_id from above query I need to Insert data to request table with some other details.For each user_id there will be a separate row with other details.I have tried below as an array but it't not saving any data

foreach ($sql as $key => $value) {

        $data[] = [
        'pickup_latitude'     => $pickup_lat,
        'pickup_longitude'   =>   $pickup_lan,
        'car_id'   =>   $car_id,
        'user_id'     =>   $value->user_id,
        'user_mobile'   =>$umobile         
    ];
} //for loop

$columns = implode(", ",array_keys($data));
$escaped_values = array_map('mysql_real_escape_string', array_values($data));
$values  = implode(", ", $escaped_values);
$stmt = $con->prepare("INSERT INTO `request`($columns) VALUES ($values)");
$stmt->execute();
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
Mithu
  • 665
  • 1
  • 8
  • 38
  • 2
    Why don't you save inside the loop while using prepared statements? – Rotimi Mar 28 '20 at 10:28
  • can you show us what is your `$columns` et `$values` ? – Pierre Mar 28 '20 at 10:28
  • 2
    I think you should dump the use of `mysql_real_escape_string` (Don't use the deprecated `mysql_*` functions at all) and start using prepared statements ASAP – Alon Eitan Mar 28 '20 at 10:37
  • 1
    https://phpdelusions.net/pdo (for PDO) or https://phpdelusions.net/mysqli (for mysqli) – Alon Eitan Mar 28 '20 at 10:38
  • See this answer might help https://stackoverflow.com/a/60018884/12232340 atleast you can see how to get array out of for each. –  Mar 28 '20 at 11:37
  • First off, `$data` is a multidimensional array so calling `implode(", ",array_keys($data))` will give you a string like: `0,1,2,3,4,...`, not the column names. And the `array_values($data)` will give you an array with arrays, identical to `$data`, so you're passing an array to `mysql_real_escape_string()`, not the values. This is simply the wrong way of using the wrong tools. – M. Eriksson Mar 28 '20 at 12:15
  • how to resolve this – Mithu Mar 28 '20 at 12:20
  • **Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. – Dharman Mar 28 '20 at 18:39
  • Does this answer your question? [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – Dharman Mar 28 '20 at 18:40

0 Answers0