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

PHP
62

thank you for that information.

63

So what has been the result when you try the code. Errors?

Remember print_r() is your friend so what do you see when you print the headings array and then also the rowData array?


	echo "<pre>";
	print_r($headings);	
	echo "</pre>";

DO YOU see a ZERO as the primary array key then a secondary array with headings?

You will see in my code samples I am assuming you are seeing the ZERO which is why I use the [0] key when wanting to get or work with the values in the array, e.g. $headings[0].

Assuming you do see the primary ZERO key in those arrays and comparing what you posted it looks like when printing the $values array you should see

‘title’, ‘surname’, ‘firstname’, ‘middlename’, ‘monthlygross’ VALUES and are missing ‘grade’ and ‘step’

Not sure what “step” is in relation to your DB table but does ‘grade’ need to be changed to ‘gradelevel’ in the $fields array?

FYI
IN CASE IT ISN’T CLEAR
The fields array represents the ORDER of the DB table with the corresponding xls names all lowercase and spaces removed. SO if xls uses GRADE LEVEL change or adjust fields array to ‘gradelevel’

OR are you not able to get that far???

Being able to diagnose and understand what is happening and then convey exactly where the problem lies when you ask for help will get you the help you need. Please be more specific with results of testing and issues you are having.

64

@Drummin. this is what i got
Array ( [0] => Array ( [0] => [1] => name’s [2] => grade [3] => level [4] => state [5] => monthlygross [6] => conslugross ) )
when i print_r($headings).
and this is what i got
( [0] => Array ( [0] => [1] => EDOBOR, Mrs. PHILOMENA EBERE [2] => GL07_CONHESS [3] => 4 [4] => EBONYI [5] => 101616.33 [6] => 88141.33 ) )

when i print_r($rowData).
my question now,
how can i loop through the column and get all the data from the xls file.
becouse $rowData only give the first record and we have about 5000 record in one sheet.

thanks
pls dont be angry for my question. your pettern is really working out. pls help me out here am really stuck.

65

OK so you do have primary array key [0] as I suspected and it looks as though the code IS changing headings to lowercase and removing spaces.

IS there any consistency in the headings being tested? I see “name’s” as a heading where we had ‘surnames’ before. Also ‘state’ is a new one that doesn’t match any DB field name. What’s up with the randomness?

66

yes we have consistency at some pint were the heading match but with no [0] array key. it come like
Array ( [0] => Array ( [0] => [1] => name’s [2] => grade [3] => level [4] => state [5] => monthlygross [6] => conslugross ) )
making the [0] like the master key and now have children under it reading from [0] again like
Array ( [0] => [1] => name’s [2] => grade [3] => level [4] => state [5] => monthlygross [6] => conslugross ).

67

This indicates there is a primary key of zero. echo the <pre> tags for a better view.

SO are some headings being matched and passed to $values array?

Let’s also remove everything but letters from headings using preg_replace.

		array_walk($headings[0], function(&$value)
		{
		 // $value = str_replace(" ", "" ,strtolower($value));  
		  $value = preg_replace("/[^a-zA-Z]+/", "",strtolower($value)); 
		});
68

yes there is a primary key of array[0]
here is the result
Array
(
[0] => Array
(
[0] =>
[1] => name’s
[2] => grade
[3] => level
[4] => state
[5] => monthlygross
[6] => conslugross
)

)

Array
(
[0] => Array
(
[0] =>
[1] => name’s
[2] => grade
[3] => level
[4] => state
[5] => monthlygross
[6] => conslugross
)

)

69

that is what am trying to say and for this perticular file
all heading match

70

I just saw your edit in POST #64.

When you print $rowData is the primary key incremented or is it zero? If incremented does it match $row?

IF it does match $row then update both places that use $rowData to use the $row variable as the key.
$rowData[$row]

71

something just happend.
it incremented and it match $row
is like this

Array ( [0] => Array ( [0] => [1] => NAME'S [2] => GRADE [3] => LEVEL [4] => STATE [5] => MONTHLY GROSS [6] => CONSLU GROSS ) ) Array ( [0] => Array ( [0] => [1] => NWEZE, Mrs. AKPURU NGOZI [2] => GL08_CONHESS [3] => 5 [4] => EBONYI [5] => 120901.75 [6] => 105583.75 ) ).

and it just printed all record in the sheet
does that mean that we should use $rowData as our variable in these if condition.

if($v == "title"){
				$values[] = $textfile_occupation;
			}elseif($v == "surname"){
				$values[] = $institution;
			}elseif($v == "first_name"){
				$values[] = $institution;
			}
			elseif($v == "middle_name"){
				$values[] = $institution;
			}
			elseif($v == "gender"){
				$values[] = $institution;
			}
72

Be sure to update this bit of code to handle apostrophes etc.

73

have done that.

74

does that mean that we should use $rowData as our variable in these if condition.

if($v == "title"){
$values[] = $textfile_occupation;
}elseif($v == "surname"){
$values[] = $institution;
}elseif($v == "first_name"){
$values[] = $institution;
}
elseif($v == "middle_name"){
$values[] = $institution;
}
elseif($v == "gender"){
$values[] = $institution;
}
75

FIRST it looks like you still have an old version with underscores… See POST #50 where I updated the array and IF conditions.
This code is looping through the $fields array so these IF conditions should match the fields array.

76

am not getting you here. pls more detail to enable understand

77

that is the sample am using just modified it to accomodate all the column headings waiting for your respond.
that is why am asking.

78
  • So we are trying to match the defined fields in the INSERT query.
  • The order of the fields array should be the same as the order of the fields in the INSERT query.
  • The names used in the fields array should match expected xls headings (letters only. all lowercase)
  • We are using array_intersect looking for matches to the headings array and the fields array.
  • WE then are using foreach to loop through the fields array so we can define a value for each field
  • Because you have a few hard coded inputs, i.e. occupation, companyname etc we add IF conditions in the foreach loop so when these fields are found POST values are used.
  • Then If a match is found ( with array_intersect() and placed in the $matches array) we grab the data key to get the value or we set the value as empty.
79

ok. pls code sample that is what am trying to ask for…
pls help with code sample that with all the xls column heading

80

You have the xls files and should know the headings. Just modify fields array “(letters only. all lowercase)” and no spaces.
Be sure any IF conditions within foreach statement match.