PHP: want to put variable and string in mysql

Question

I want to put variable and string in mysql.

The json code was output by the API.

json view:

Array(
    [body] => Array
        (
            [items] => Array
                (
                    [0] => Array
                        (
                            [bizesId] => 333333
                            [lon] => 00.000000
                            [lat] => 00.000000
                        )

                )

)

PHP code:

    for ($i=0; $i<=1000; $i++) {

  foreach($result_json['body']['items'] as $arr){
      if(mysqli_query($con, "INSERT INTO TEST (`num`,'market',`lon`,`lat`) VALUES ('". $arr['bizesId'] ."','test','". $arr['lon'] ."','". $arr['lat'] ."')")){
      }
  }
 }

Data can not be saved.

Please read about [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). Instead of building queries with string concatenation, use [prepared statements](https://secure.php.net/manual/en/pdo.prepare.php) with [bound parameters](https://secure.php.net/manual/en/pdostatement.bindparam.php). See [this page](https://phptherightway.com/#databases) and [this post](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some good examples. — Rahul, May 10 '19 at 09:20

score 0 · Answer 1 · answered May 10 '19 at 09:24

0

  INSERT INTO TEST (`num`,'market',`lon`,`lat`) VALUES ('$arr['bizesId']','test',' $arr['lon']','$arr['lat']'

Try this. and also change table name if it is in Lowercase write - test

answered May 10 '19 at 09:24

Anjali Rakholiya

15
4

error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in – Hannah Oh May 10 '19 at 09:28
ok then use your query just put $result_json['body']['items'][0] as $arr) – Anjali Rakholiya May 10 '19 at 09:32
Despite the revision, Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) – Hannah Oh May 10 '19 at 09:37
The `'`around market will cause the error. Try ` – ivion May 10 '19 at 09:40

score 0 · Answer 2 · answered May 10 '19 at 09:45

0

Resolved. Thank you.

INSERT INTO TEST (num,market,lon,lat) VALUES ...

answered May 10 '19 at 09:45

Hannah Oh

39
4

score 0 · Answer 3 · answered May 10 '19 at 13:15

replace 'market' with `market`. Foll code works fine

<?php

ini_set('display_errors', 1);
$user = 'root';
$pass = 'xxxx';
$DB = 'test';
$host = 'localhost';


$conn = new mysqli($host, $user, $pass, $DB);
$result_json['body']['items'][0] = ['bizesId'=>34, 'lon'=>34, 'lat'=>23];

foreach($result_json['body']['items'] as $arr){
        $sql = "INSERT INTO TEST_INSERT (`num`,`market`,`lon`,`lat`) VALUES ('". $arr['bizesId'] ."','test','". $arr['lon'] ."','". $arr['lat'] ."')";
      if(mysqli_query($conn, $sql)){
      }
      else
      {
        echo $sql.'<br>';
      }
  }

PHP: want to put variable and string in mysql

3 Answers3