Data not inserting into the database table. but when var_dump it show all record to be inserted

PHP
1

Cant, insert into the database table…

$institution=$_SESSION['Company'];
$UserLogin=$_SESSION['UserLogin'];
$textfile_surname = $_POST['surname'];
$textfile_occupation = $_POST['occupation2'];
$textfile_occupation1 = $_POST['occupation'];
$textfile__monthlygross = $_POST['monthly_gross'];
  
  for ($col = 0; $col <= $highestColumnIndex; $col++) { 
    //assigning all data to one variable 
    $allcolrowdata =$columnNames = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow($col, $row)->getValue()); 
       //echo $allcolrowdata.'<br>';
       $allcolrowdataexplode=explode(",",$allcolrowdata);
       var_dump( $allcolrowdataexplode).'<br>';
      foreach($allcolrowdataexplode as $key => $inserted){
    //echo $inserted;
    $query_insert ="INSERT INTO tem_treg2 (title,surname,first_name,middle_name,gender,dob,marital_status,occupation,phone_number,company_name,registration_type,registered_by,tax_id,office_address,office_city,temp_reg,workplace_category,active,monthly_gross,nhf,nhis,nsitf,basic_salary,grade,designation,pension,gratuity) 
    VALUES ('','$inserted','','','','','','$textfile_occupation','','$institution','','$UserLogin','','','','','','','','','','','','','','','')
    ";
    //var_dump($query_insert);
    //('','$values1','$inserted','','','','','$textfile_occupation','','$institution','','$UserLogin','','','','','','','','','','','','','','','')
    }
                    }

 mysqli_query($connect, $query_insert);  echo mysqli_error($connect);
2

if the DB doesn’t insert then there is an error that would print in your script.

3

it print data have been inserted but when i check my database it shows empty rows.
also when i var_dump the var $inserted it shows all data to be inserted but why is not inserting, i dont know

4

If the data don’t insert, then the DB issues an error (given that you actually sent the data over).

5

what am trying to say is that, it insert empty string into the database table not the record needed.
and i have check over and over to know why but i can’t.

6

Is it possible that the data you retrieve from the spreadsheet contains characters such as ' that might be confusing the query string? Using prepared statements might help there. Failing that, show some of the data (sanitised if you need to) for more information.

7

yes, it contain it, pls can you provide some code sample using the code above to solve this problem pls. i have been here for three to four days now

8

Well, you either need to read up on escaping strings before storage to deal with special characters, or read up on prepared statements. My preference would be the latter for several reasons.

Have a look around the forums, there is plenty of sample code on here, and links to sites that people recommend. I use PDO rather than mysqli, so I can’t really point you to anything specific.

1 Like
9

thanks very much. i will do as you said .once am done i will let you know am going for the prepared statements because i have escaping strings still things are not working out.

1 Like
10

@droopsnoot.

still not inserting after using the prepared statements

11

Perhaps you can show your updated code?

12

here is my update query code

$stmt =$connect->prepare("INSERT INTO tem_treg2 (title,surname,first_name,middle_name,gender,dob,marital_status,occupation,phone_number,company_name,registration_type,registered_by,tax_id,office_address,office_city,temp_reg,workplace_category,active,monthly_gross,nhf,nhis,nsitf,basic_salary,grade,designation,pension,gratuity) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$stmt->bind_param("work", $inserted,'','','','','',$textfile_occupation,'',$institution,'',$UserLogin,'','','','','','','','','','','','','','','');
$stmt->execute();

echo "New records created successfully";
13

Read up a bit more on bind_param() : http://php.net/manual/en/mysqli-stmt.bind-param.php

You missed a significant part out.

And, for all those columns where there will be no data, why put them in the query? Just leave them out of the columns list, and leave out all the blank values. It’ll make the query look much clearer.

14

ok thanks for your quick respond

15

@droopsnoot here is the error i got
Only variables can be passed by reference in C:\xampp\htdocs\excellimportwork\company_temp_reg_excel_import_mapping.php on line 144

remember that $inserted is an array of datas

and here is my code samples

 $stmt =mysqli_prepare($connect,"INSERT INTO tem_treg2 (title,surname,first_name,middle_name,gender,dob,marital_status,occupation,phone_number,company_name,registration_type,registered_by,tax_id,office_address,office_city,temp_reg,workplace_category,active,monthly_gross,nhf,nhis,nsitf,basic_salary,grade,designation,pension,gratuity) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
mysqli_stmt_bind_param($stmt,"work", $inserted,'','','','','',$textfile_occupation,'',$institution,'',$UserLogin,'','','','','','','','','','','','','','','');
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt);
echo "New records created successfully";
16

Did you read the link I posted about bind_param()? You still have the first parameter missing. You’ve changed from OOP to procedural for some reason, too.

If $inserted is an array, that might not be a problem, but it will be more work to extract from the table later on, I think you should probably not store a complete array in a single database column. This line in the original code

foreach($allcolrowdataexplode as $key => $inserted){

suggested it was just a string, I hadn’t realised that $allcolrowdataexplode was an array of arrays.

If it’s anything like PDO, I don’t think you can specify a string constant as one of the parameters - assign it to a variable and reference that instead.

17

$inserted is AN ARRAY WHICH HAS BEEN EXPLODE TO ENABLE IT BE INSERTED IN A DIFFERENT COLUMN ONCE THE QUERY RUN. THIS ARRAY CONTAIN ALL ROWS NEEDED TO BE INSERTED

18

I couldn’t see that in your code. The only explode I see is here:

$allcolrowdataexplode=explode(",",$allcolrowdata);
19

<off-topic>
@chibuzorchinaecherem when you post code in the forum, you need to format it. To do so you can either select all the code and click the </> button, or type 3 backticks ``` on a separate line both before and after the code block.
</off-topic>

20

@Gandalf NO PROBLEM