Insert multiple rows from array

Question

I need help to insert data into mysql. I am unable to find the problem in the code below why data is not inserted into database. When I am submitting the form this message comes : Notice: Undefined variable: asd in...

<?php
$q = mysql_query("select `cus_id`, `date`, `mobile`, 
    least(`t1`, `t2`, `t3`, `t4`) as min 
    from `table1`");

while($r = mysql_fetch_assoc($q)){

$asd = array(`cus_id` =>$r['cus_id'], 
             `mobile` =>$r['mobile'], 
             `date`   =>$r['date'], 
             `credit` =>$r['min']);
}

$sql = array();

foreach((array)$asd as $row){

$sql[] = '("'.mysql_real_escape_string($row['cus_id']).'", 
                                     '.$row['mobile'].', 
                                     '.$row['date'].', 
                                     '.$row['min_total'].'
           )';
}

$stmt = mysql_query('INSERT INTO `single` (`cus_id`, `mobile`, `date`, `credit`) 
                     VALUES '.implode(',', $sql));                                    

if(!$stmt){ 
echo "error". mysql_error();
}else{
$_SESSION['s']="Payment successfully saved";
header('location:final.php');
}
?>

Thanks for any help.

1. You are not quoting your strings, 2. You are not escaping your strings, 3. You should not be using `mysql_*`. — jeroen, Oct 20 '17 at 07:31
What datatype of `date`? Why don't you use parentheses to wrap inserting variables? — shukshin.ivan, Oct 20 '17 at 07:32
`datatype of date` is varchar(255). I don't know how to use parentheses to wrap inserting variables. Plz help. — rocky a, Oct 20 '17 at 08:01
@jeroen will you plz help me how will I convert this code with `mysqli` — rocky a, Oct 20 '17 at 08:40

rocky a · Accepted Answer · 2017-10-20T17:48:19.387

If I convert the above query into mysqli, then will the code be below:

<?php

$link = mysqli_connect('localhost', 'root', '', 'mumbai');
$q = "select `cus_id`, `date`, `mobile`, 
      least(`t1`, `t2`, `t3`, `t4`) as min 
      from `table1`";

$asd = array(); 
$result = mysqli_query($link, $q);
while($r = mysqli_fetch_assoc($result)){

$asd = array(`cus_id` =>$r['cus_id'], 
             `mobile` =>$r['mobile'], 
             `date`   =>$r['date'], 
             `credit` =>$r['min']);
}

$sql = array();

foreach((array)$asd as $row){

$sql[] = '("'.mysqli_real_escape_string($row['cus_id']).'", 
                                 '.$row['mobile'].', 
                                 '.$row['date'].', 
                                 '.$row['min_total'].'
       )';
}
$stmt = mysqli_prepare($link, "INSERT INTO single 
               (`cus_id`, `mobile`, `date`, `time`, `credit`) VALUES (?,?,?,?,?)");

mysqli_stmt_bind_param($stmt, 'isssi', implode(',', $sql));                  

mysqli_stmt_execute($stmt);

if(!$stmt){ 
echo "error". mysqli_error($link);
}else{
$_SESSION['s']="Payment successfully saved";
header('location:final.php');
}
?>

Using `mysqli_real_escape_string()` with a prepared statement demonstrates a misunderstanding about how to use prepared statements properly. — mickmackusa, Nov 22 '22 at 22:22

score 0 · Answer 2 · answered Oct 20 '17 at 09:14

0

before your while loop add an $asd variable.

$asd = array();

while($r = mysql_fetch_assoc($q)){
....

then in your foreach you can remove the (array)

foreach($asd as $row){
....

no more Notice: Undefined variable: asd in...

answered Oct 20 '17 at 09:14

Miggy

816
7
16

I have added `$asd = array();` Now the message comes : `You have an error in your SQL syntax........` I am unable to insert all data rows at once. Also a similar line here `$sql = array();` Will I keep the line or remove ? – rocky a Oct 20 '17 at 10:29
can you show the complete error? Not just `You have an error in your SQL syntax` followed by `....` – Miggy Oct 20 '17 at 10:32
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 – rocky a Oct 20 '17 at 10:45
What is the difference between `$sql = array();` and `$asd = array();`? Will I have to keep both line in the code? – rocky a Oct 20 '17 at 17:54
you can delete $sql = array() if you don't use it @rockya – Miggy Oct 21 '17 at 11:18

Insert multiple rows from array

2 Answers2