After breakfast I checked my email, then did some work on my website. I'd written a mysql function to check if a record exists, and if not insert it and return the last_insert_id(), otherwise if the record already exists just return a 0 (equates to false in PHP). But it wasn't working, it wouldn't insert any records. So I started writing the function again, bit by bit and making it just return a value if the record existed or didn't exist. After quite a bit of testing, I found the problem was with my checking whether the record already exists.
My code looked like this:
IF (SELECT 1 FROM percountryinfo WHERE (
percountryinfo.Country = countryKey AND
IF(Releasedvar IS NULL, percountryinfo.Released IS NULL, percountryinfo.Released = STR_TO_DATE(Releasedvar, '%d/%m/%Y')) AND
IF(Producervar IS NULL, Producervar IS NULL, percountryinfo.Producer = Producervar) AND
percountryinfo.PageKey = PageKeyvar)) != 1
THEN do stuff;
But if the SELECT statement doesn't select anything (i.e. the record doesn't already exist), then it equates to NULL. And if you compare a NULL value against anything it will always return NULL. So I just changed my code from != 1 to IS NULL and it worked.
Only problem is, I've only got it working through phpMyAdmin at the moment.
After doing some testing, I stopped using a multi_query to run the function and just ran it in the loop, which gave me an error message, even though I hadn't asked it to pint the error, and the multi_query, where I had put to print the error wasn't giving an error. Before I fixed the error I wanted to find out why the multi_query wasn't printing the error. It turns out that the error code (I use if($mysqli->errno){echo $mysqli->error;}) needs to be inside the do loop of the multi_query, underneath the section where you deal with the results of the query, like this:
if($conn->multi_query($sql))
{
do{
if($result = $conn->store_result())
{
$row = $result->fetch_row();
if($row[1])
{$insertedCountries[] = $row;}
$result->close();
}
if($conn->errno){echo "\n$conn->error\n";}
}
while($conn->next_result());
}
if($conn->errno){echo '
'.$sql.'
'.$conn->error;}
After figuring that out I had to try and fix the error. The error was that the user I was connecting to the database as didn't have execute permission. Should be easy to fix, right?
Well I tried adding execute to the user privileges in phpMyAdmin, but it didn't modify the current permissions, but instead created a second lot something like:
User: myuser
my\db | SELECT, INSERT, UPDATE, DELETE
wildcard my_db | SELECT, INSERT, UPDATE, DELETE, EXECUTE
And the script still gave the same error about not having execute privilege.
So I tried again, but this time from the main page of phpMyAdmin, rather than from the database I was working on page.
Again it created a 2nd lot of permissions, but this time like:
User: myuser
my\db | SELECT, INSERT, UPDATE, DELETE
my_db | SELECT, INSERT, UPDATE, DELETE, EXECUTE
And the script still gave the same error about not having execute privilege.
After trying various different things and still no luck I eventually found out how to do it - I had to open the mysql.db table and change Execute_priv to Y for the my\db entry.
Next I tried to change the function so that I could inject sql into it. Again, I played around with this for quite a while but didn't have any luck, so I ended up posting to the websqueeze to see if anyone knows whether it's possible or not, but I think not.
After lunch I tried changing the function so it doesn't return LAST_INSERT_ID(), but just 0 for no insert and 1 for row inserted, then use $mysqli->insert_id to get the last insert id in PHP. Unfortunately this didn't work either. Although the mysql website doesn't say that insert_id doesn't work for functions, it does say that it doesn't work for stored procedures, so I guess it doesn't work for functions either:
mysql_insert_id() returns 0 following a CALL statement for a stored procedure that generates an AUTO_INCREMENT value because in this case mysql_insert_id() applies to CALL and not the statement within the procedure. Within the procedure, you can use LAST_INSERT_ID() at the SQL level to obtain the AUTO_INCREMENT value.
Next I tried writing the function as a stored procedure. I did have a bit of trouble here, but not too much, the main problem was it didn't like me having a DEFAULT value for an OUT parameter
Then I did some more testing and found there were a few problems with the mysql function and procedure - first I hadn't put the tinyints and ints as UNSIGNED, so for the tinyints when a value was above 127 it would just insert 127 into the database. Also dates were being inserted as 0000-00-00, this was a syntax error on my part, and there was another error where I was checking whether a variable was null, rather than a field. After fixing them I found it all worked okay.
I added a table to print the methods and time they took for all the methods I was using to check whether a record exists, and if it doesn't insert it and return the inserted record's key/id. I ran this 10 times (each time checked/inserted 5 records) and found that making a long query in the loop and running it after the loop had finished was fastest. So then I copied that method, and changed it so the insert query and select inserted ids query are run in a multiquery, to see if that was any faster.
Then I ran it again, once with 5 records for it to check/insert and once with 1 record for it to check/insert. I haven't had time to look properly at the results yet.
I backed up all my stuff, then played on Animal Xing for a bit until dinner time.
After dinner I watched Lost with Mac & Ben, then checked my email, which took ages as the internet kept breaking. I copied some pics to my comp from my camera memory cards and then processed a few to JPEGs.
The weather today was a mixture of sun and cloud, but mostly overcast.
Food
Breakfast: Lemon marmalade toast sandwich; cup o' tea.
Lunch: Sicilian style ham sandwich; clementine; 1 white grape; Mr Kipling's Exceedingly Merry Mince Pie; cup o' tea.
Dinner: slice of home-made cheese and tomato pizza; jacket potato; mixed veg. Pudding was neapolitan ice cream. Coffee; Sainsbury's caramel chocolate.
No comments:
Post a Comment