SQL/PHP insert data null error

[Flanelman]

Hey guys,

So I'm trying to populate a few tables using MYSQL and PHP but I'm getting this error:
"SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'teamName' cannot be null"

I don't really understand why It thinks teamName is null tho? Here is the code:

 

	$createQuery ="CREATE TABLE TeamTable
	(
		teamID	INT(6) NOT NULL AUTO_INCREMENT,
		teamName	VARCHAR(60) NOT NULL,
		Flag 		BLOB,
		Country	int(6) NOT NULL,
		PRIMARY KEY(teamID)
	)";
	$pdo->exec($createQuery);


$filet = fopen("team.csv", "r");

while(!feof($filet))
{
	$myarray = fgetcsv($filet);

	//inserting TEAMS filet into the database
	$insertTeamQuery ="INSERT into TeamTable(teamID, teamName, Country, Flag) VALUES(:val0,:val1,:val2,:val3)";
	$stmt = $pdo->prepare($insertTeamQuery);
	$stmt->bindParam(':val0', $myarray[0]);
	$stmt->bindParam(':val1', $myarray[1]);
	$stmt->bindParam(':val2', $myarray[2]);
	$stmt->bindParam(':val3', $myarray[3]);
	$stmt->execute();
}
fclose($filet);

So the team name is contained inside myArray[1], I even echoed it out to make sure it was actually containing the values and it was so I don't understand why it is saying it's null when I'm trying to put these values into the DB? I've even tried passing it [2], [3] etc and the same result every time.

Any help would be greatly appreciated, I'm so lost. Thanks in advance!

[79wjd]

Try doing the while loop on fgetcsv != FALSE && fgetcsv != NULL intead of EOF. I'd also try getting rid of the SQL Injection protection just for testing to see if that's causing the problem.

[Cruorzy]

Somewhere in your data there must be a empty string of some sort, try @djdwosk97 his solution.

[Flanelman]

11 minutes ago, djdwosk97 said:

Try doing the while loop on fgetcsv != FALSE && fgetcsv != NULL intead of EOF.

Nope same issue but I may have done it wrong? Is this how you meant:
 

$filet = fopen("team.csv", "r");

while(fgetcsv($filet) != FALSE && fgetcsv($filet) != NULL)
{
//code same as before
}

It's definitely going through the file and getting the fields because I was able to Echo them with my original code but apparently they're still null, even tho the echo output Team Names? 

[79wjd]

30 minutes ago, Flanelman said:

Nope same issue but I may have done it wrong? Is this how you meant:
 

$filet = fopen("team.csv", "r");

while(fgetcsv($filet) != FALSE && fgetcsv($filet) != NULL)
{
//code same as before
}

It's definitely going through the file and getting the fields because I was able to Echo them with my original code but apparently they're still null, even tho the echo output Team Names? 

Try this:

$stmt = $pdo->prepare($insertTeamQuery);
$stmt->execute(array(':val0' => $myarray[0], ':val1' => $myarray[1], ':val2' => $myarray[2], ':val3' => $myarray[3]));
	

 

I think you might need to specify a data_type when you use bindParam.

[Flanelman]

1 hour ago, djdwosk97 said:

Try this:

$stmt = $pdo->prepare($insertTeamQuery);
$stmt->execute(array(':val0' => $myarray[0], ':val1' => $myarray[1], ':val2' => $myarray[2], ':val3' => $myarray[3]));
	

 

I think you might need to specify a data_type when you use bindParam.

Okay well that code stopped one error but now I have this one, any ideas on how to fix this?

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'

I don't understand how there's a duplicate when It's reading from the file correctly (since I checked it by Echoing the output and there is no duplicates?) I read somewhere that auto incrementing fields shouldn't be put into the table and will be done by themselves, so I removed the ":val0" (teamID/$myarray[0]) from the insert and stuff and then I get the same error as before saying that teamName can't be null 

I'm so confused how all the data looks correct within the array if I echo it out yet as soon as I try and add it there is duplicates and issues
 

[79wjd]

5 hours ago, Flanelman said:

Okay well that code stopped one error but now I have this one, any ideas on how to fix this?

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'

I don't understand how there's a duplicate when It's reading from the file correctly (since I checked it by Echoing the output and there is no duplicates?) I read somewhere that auto incrementing fields shouldn't be put into the table and will be done by themselves, so I removed the ":val0" (teamID/$myarray[0]) from the insert and stuff and then I get the same error as before saying that teamName can't be null 

I'm so confused how all the data looks correct within the array if I echo it out yet as soon as I try and add it there is duplicates and issues

Are you echoing right before you try inserting or are you echoing instead of inserting?

[Lumi]

Why are you inserting data into an auto incremented column? pls stop

 

Also

$stmt->execute([

':ya' => $momma,

]);

is less messy

 

and

Alternate syntax for insert

INSERT INTO `table` SET var=:ayy,yolo=:swag