-2

I use PHP to parse big CSV file and generate a SQL file that contains INSERT requests. But at the beginning of the file, I also put a DELETE statement to clear the database before.

My file looks like something like this : 

DELETE FROM `my_table` WHERE id IN (<list>) ;
INSERT INTO `my_table` .... (lot of values);

The lines are correctly inserted but the old ones are not deleted. I tried the delete request in PHPMyAdmin : it works. So the issue comes from the way I run the sql file.

I use the exec method in PHP : 

$command = "mysql.exe -u user -pPassword my_database < sqlfile.sql";

It seems that this line, using the left chevron, works fine for INSERT statements, but not for DELETE ones.

Any idea to solve that ?

Thanks a lot :)

Pete_Gore
  • 594
  • 1
  • 5
  • 20

1 Answers1

0

Code isn't entirely clear, but judging by your WHERE ... IN clause, you're either manually generating an ID via SELECT and manually incrementing (Usually bad), or you're simply doing it wrong. I see 2 options based on these scenarios:

TRUNCATE TABLE `my_table`

This empties all previous values from said table.

Second, if you were auto-generating ID's, set up the ID as auto-increment and try

# Null corresponds to auto-increment/primary key ID field in MySQL table
INSERT INTO `my_table` ... VALUES (null, value, value)
iamgory
  • 862
  • 1
  • 6
  • 10
  • Finaly it works. I was doing "DELETE...; SET NAMES UTF-8; INSERT". I put the SET NAMES statement at the beginning and now it's ok? – Pete_Gore Oct 16 '14 at 13:13
  • Yes, that would break it since SET NAMES would need to be at the start of the script. But do you need SET NAMES? Are you sending special characters? – iamgory Oct 16 '14 at 13:16