Why would this MySQL work most of the time and not some of the time?

Question

Why would this MySQL work most of the time and not some of the time?
if ($handle = opendir('music')) {
while (false !== ($entry = readdir($handle))) {
if ($entry != "." && $entry != "..") {
echo $i, "
";
echo "$entry\n";


preg_match("/^[A-Za-z0-9 -_ ]*(?= -)/", $entry, $matches);
$artist = $matches[0];
echo $artist;
echo "
";
preg_match("/(?<=- )[A-Za-z0-9().&-', _ ]*(?=.mp3)/", $entry, $matches);
$song = $matches[0];

echo $song;


mysql_query("INSERT INTO songs (song, artist) VALUES ('$song', '$artist')");



$song and $artist always echo but sometimes it does not insert into the database and sometimes it does. Most of the time it does.


I have song and artist set in combination to be a UNIQUE field but even if it is a song or artist that is all unique it still sometimes refuses to write it to my database. It's doing this with about 2 to 4 percent of the songs.



I've taken care of the apostrophes. Even with that thought, it's not the apostrophes that are being skipped. For example, the file "Carpenters - Every Sha La La.mp3" will not be saved in the database even though it will echo the artist "Carpenters" and echo the song "Every Sha La La".

Answer 1

Do you know which combinations of artists and songs that it is not saving? If you do, try to write it directly in SQL database, e.g. mysql_query("INSERT INTO songs (song, artist) VALUES ('Every Sha La La', 'Carpenters')"); to check the data is saved. If it is saved, then your SQL statement is correct.

Answer 2

I normally do all of my SQL stuff directly from the SQL Server Management Console so I may be wrong but it looks like you are using $Song and $Artist as variable names here. If that is true, I don't think you should have them wrapped in single quotes.



Since song and artist combine to make a unique key, you can do this once and it will write the value $Song to the song field in the songs table and $Artist to the artist field of the same row.



The next time it tries to write the same exact values since it sees them as a single quote wrapped string instead of a variable which is basically trying to write a row that is identical to the first one and is a key constraint error.



Remove the single quotes from $Song and $Artist in the insert statement and you should get a little further.



WOW!!! I don't understand why someone would give me a thumbs down when I am giving perfectly good advice.

Answer 3

Jesus christ dude--how many times are you going to ask this question? For the millionth time, use mysql_error() to display the error when the query fails.



EDIT: But you still haven't posted what the output of mysql_error() is. Until you do, you'll NEVER know why your queries are failing. You need to check the output of mysql_query()--if it's false, you need to run mysql_error() and see WHY the query failed. You haven't done that yet. mysql_error() will tell you everything you need to know about why your query isn't working.