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

PHP
41

Well good/bad/ugly this is what I came up with…
Note: I am showing $row FOR loop on my sample. You would use yours.
I also did not include the id or hash field in my $fields array.
ALSO as I did not see a sample of $rowData or $headings arrays I added the extra key [0] that I am expecting… Adjust as needed.

	$highestRowIndex = $worksheet->getHighestRow();
	$highestColumnIndex = $worksheet->getHighestColumn();
	
	for ($row = 1; $row <= $highestRowIndex; $row++) { 
	
		$headings = $worksheet->rangeToArray('A1:' . $highestColumnIndex . 1,null, true, false);
		
		$rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumnIndex . $row, null, true, false);

		$fields = array(
		  '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'
		); 
				
		$matches = array_intersect($headings[0],$fields);	

		$values = array();
		foreach($fields as $k => $v):
			if($v == "occupation"){
				$values[] = $textfile_occupation;
			}elseif($v == "company_name"){
				$values[] = $institution;
			}elseif($v == "registered_by"){
				$values[] = $UserLogin;
			}else{
				$values[] = (in_array($v,$matches) ? $rowData[0][array_search($v,$matches)] : '');
			}		
		endforeach;
		
		$row_values = implode(',',$values);	
		
		$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("sssssssssssssssssssssssssss",$row_values);
		$stmt->execute();
	}

EDITED: NOTE the $fields array could be written to hold xls column headings as long as they still match the order of table fields, e.g. ‘first_name’ changed to ‘First Name’

42

thanks let me try it out. will get back as soon as am done

43

@Drummin, how can match
‘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’
as there are coming from the xls file.
the sample you use are static variable i declear

44

The $fields array can be edited.

45

Good Morning, pls how can i archieve that here is my sample code i wrote early which is not working

$gender = $columnNames = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(15, $row)->getValue());  
$designation = $columnNames = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(16, $row)->getValue());
$pension = $columnNames = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(17, $row)->getValue()); 
$gratuity = $columnNames = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(18, $row)->getValue());

and this does not read dynamically.

pls help with a sample code, am really stock here

46

As you are using bind_param you no longer need to use mysqli_real_escape_string();
Also redefining column names variable is not needed…

$gender = $worksheet->getCellByColumnAndRow(15, $row)->getValue();
$designation = $worksheet->getCellByColumnAndRow(16, $row)->getValue();
$pension = $worksheet->getCellByColumnAndRow(17, $row)->getValue();
$gratuity = $worksheet->getCellByColumnAndRow(18, $row)->getValue();

QUESTION:
Is there a reason why you cannot define xls column headings to the $fields array?
I mean is a heading called “gender” on one page then “Gender” on another or “sex” on another???
It seems to me that if you have these files you would know what the heading are…
Am I missing something?

47

this xls header can come in any format but the point here is that when i match it it should be able to pick the record and send to the right table column,

like.
the first xls file come in this formate
surname : firstname : lastname : gender : nhf…
another come
like.
Fullname : Gross : Penssion
and another one come like.
surname : firstname : lastname : pension : gross : nhf : pension.

i want once i match it with the heading it beable to pick the data and send to the right column in my database table.
that’s reading it dynamically.
note: people who send these data can give the heading any name of there choice and send to you and you now the one to match on your system and extract the record and send to the database.

48

Well FIRST lets get all headings used (from xls) to lower case and remove any space. Then it doesn’t matter if it is for example “firstname”, “FIRSTNAME”, “First Name” etc.

		$headings = $worksheet->rangeToArray('A1:' . $highestColumnIndex . 1,null, true, false);
		array_walk($headings[0], function(&$value)
		{
		  $value = strtolower($value);
		});

Then make sure your fields array uses ‘firstname’

49

pls i don’t get you here, explian in detail pls.
thanks

50

Well my modified sample would look like this.

	$highestRowIndex = $worksheet->getHighestRow();
	$highestColumnIndex = $worksheet->getHighestColumn();
	
	for ($row = 1; $row <= $highestRowIndex; $row++) { 
	
		$headings = $worksheet->rangeToArray('A1:' . $highestColumnIndex . 1,null, true, false);
		array_walk($headings[0], function(&$value)
		{
		  $value = str_replace(" ", "" ,strtolower($value));
		});
			
		
		
		$rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumnIndex . $row, null, true, false);

		$fields = array(
		  'title'
		, 'surname'
		, 'firstname'
		, 'middlename'
		, 'gender'
		, 'dob'
		, 'maritalstatus'
		, 'occupation'
		, 'phonenumber'
		, 'companyname'
		, 'registrationtype'
		, 'registeredby'
		, 'taxid'
		, 'officeaddress'
		, 'officecity'
		, 'tempreg'
		, 'workplacecategory'
		, 'active'
		, 'monthlygross'
		, 'NHF'
		, 'NHIS'
		, 'NSITF'
		, 'basicsalary'
		, 'grade'
		, 'designation'
		, 'pension'
		, 'gratuity'
		); 
		$first_names = array();		
		$matches = array_intersect($headings[0],$fields);	

		$values = array();
		foreach($fields as $k => $v):
			if($v == "occupation"){
				$values[] = $textfile_occupation;
			}elseif($v == "companyname"){
				$values[] = $institution;
			}elseif($v == "registeredby"){
				$values[] = $UserLogin;
			}else{
				$values[] = (in_array($v,$matches) ? $rowData[0][array_search($v,$matches)] : '');
			}		
		endforeach;
		
		$row_values = implode(',',$values);	
		
		echo "<pre>";
		print_r($values);	
		echo "</pre>";
		//$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("sssssssssssssssssssssssssss",$row_values);
		//$stmt->execute();
	}

NOTE that all values in the fields array are also lower case, without a space or underscore so they should match xls headings that have been changed to lowercase and spaces removed.

This SHOULD place any matching values from xls in the correct order of the values array for insert.
(Don’t really have and DB tables or xls for testing but seems logical to me)

51

here is the db table

idtreg int(10) unsigned NO PRI auto_increment
title varchar(45) YES
surname varchar(45) NO
first_name varchar(45) NO
middle_name varchar(45) YES
gender varchar(45) YES
dob date YES
marital_status varchar(45) YES
occupation varchar(45) NO
phone_number varchar(45) YES
company_name varchar(200) YES
registration_type tinyint(1) unsigned NO 1
registered_by varchar(45) NO taxo_import
tax_id varchar(45) NO
office_address varchar(45) YES
office_city varchar(45) YES
temp_reg tinyint(1) unsigned NO 1
workplace_category varchar(45) YES
active tinyint(1) unsigned NO 0
monthly_gross decimal(50,2) NO
NHF decimal(50,2) NO 0.00
NHIS decimal(50,2) NO 0.00
NSITF decimal(50,2) NO 0.00
basic_salary decimal(50,2) NO
grade varchar(45) YES
designation varchar(45) YES
pension decimal(50,2) NO 0.00
locked tinyint(1) unsigned NO 0
gratuity decimal(50,2) NO 0.00
hash varchar(244) YES

xls sample files:

52

ok pls

53

hope these sample files will help

54

By “sample files”, you mean the data is just made up, don’t you? You’re not posting anybody’s actual information?

1 Like
56

@technobear. those are the sample files, and i have also given my table structure. what next should provid?

57

You just need to confirm that this isn’t the data of real people working for the organisations mentioned at the top of the file.

1 Like
58

What is concerning me is that those “sample files” seem to contain real data relating to real people. These are public forums, and they are crawled by search engines (and probably also less ethical bots). You really don’t want to post any private information here.

59

ok. not reall data. am just trying to provide some idea that will enable the room help solve a problem

60

OK, then, thank you. I’ll make the topic publicly visible again.