What happens if one of the inserts has an error; like: *
The statement stops executing, and any changes that were underway are undone. (However, there may be some side effects from the failed insert... for example, the AUTO_INCREMENT on the table (if there is one) may be higher than it was when the INSERT statement started. Also, BEFORE INSERT triggers fired for each row may have made DML changes to MyISAM tables, those changes won't be "backed out".
But as far as rows inserted into the target table, none of the rows from the INSERT that failed will be there.
Will it stop the Query there
Yes, the INSERT will raise an error, and any changes made by that statement be reverted.
and leave the other inserts floating in space?
There won't be anything left floating in space. The entire statement will either succeed, or it will fail. Either all the rows will be inserted, or none of the rows will be inserted.
It's up to you how the process proceeds, whether you continue with the next INSERT statement, or whether you stop processing.
(I'm talking here in the context of a single INSERT statement that's inserting multiple rows. Not about what happens with other statements. Statements that previously succeeded remain successful. (In the context of a TRANSACTION, using InnoDB (or some other storage engine that supports transactions) it would be possible to issue a ROLLBACK and "undo" the changes. If you're using the MyISAM storage engine, a ROLLBACK won't have any effect, a successful statement is already committed.
I would have to TRUNCATE the table and start again without knowing who's causing the problem :/
You wouldn't necessarily need to truncate the table. That may be an approach you want to use to restart the load process, that's up to you. As far as MySQL is concerned, all the INSERT statements that completed successfully are stored. You'd only need to restart from the INSERT statement that failed.
Some things to consider:
If you don't need to insert that "invalid" row, but you need the other ones to be inserted, you could use an INSERT IGNORE statement.
The IGNORE tells MySQL not to raise an error when an insert of a particular row fails. MySQL continues to run the statement and allow it complete successfully, with warnings, rather than errors.
If you inserted a thousand rows, and one row had an error, you'd have a warning, and the other 999 rows would be inserted.
For bulk loading a brazilians of rows, nothing beats the LOAD DATA statement.
If you go the route of a brazilian individual inserts statements, that's gonna be a whole lot more work on the MySQL server, parsing statements, generating an execution plan, obtaining locks, returning a status... an INSERT is a fairly efficient process, but all of that overhead for processing each individual statement adds up. So, not only is that going be agonizingly slower, it's also going to be pounding on the MySQL server with the relentless and excruciating row-by-row processing.
mysqli_real_escape_string function. (As another alternative, you could use a prepared statement with bind placeholders, and then you wouldn't need to include the values in the SQL text, and wouldn't need to do the escaping.)