ebay is annoying

Got up this morning about 6.30am. Had breakfast, then finished running the mysql SELECT IN test that I was doing on Friday. It is much faster to build up the query inside the loop and then execute the query in one go when the loop's finished.

	Loop 5 times
	OR	IN	IN2
	0.00798606872559	0.00504803657532	0.00319099426270
	0.00821709632874	0.00626087188721	0.00343608856201
	0.00728511810303	0.00468301773071	0.00320792198181
	0.00834417343140	0.00567698478699	0.00334596633911
	0.00859093666077	0.00527000427246	0.00325489044189
	0.01122093200680	0.00462007522583	0.00328207015991
	0.00714206695557	0.00437498092651	0.00323915481567
	0.00991106033325	0.00634288787842	0.00347900390625
	0.00950288772583	0.00515413284302	0.00312805175781
	0.00950288772583	0.00515413284302	0.00312805175781
Avg	0.00877032279968	0.00525851249695	0.00326921939850

And the test script I used:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css">
div{
border: 1px solid #000;
}
.left{ float: left;}
.right{float: right;}
</style>
<title>Untitled Document</title>
</head>

<body>
<?php
include('globalFuncs.inc.php');

$words = array('a', 'ac', 'accumsan', 'ad', 'adipiscing', 'Aenean', 'Aliquam', 'amet', 'ante', 'aptent', 'arcu', 'at', 'auctor', 'augue', 'bibendum', 'Class', 'commodo', 'condimentum', 'consectetuer', 'consequat', 'conubia', 'convallis', 'Cras', 'cubilia', 'Cum', 'Curabitur', 'Curae;', 'cursus', 'dapibus', 'diam', 'dictum', 'dignissim', 'dis', 'dolor', 'Donec', 'dui', 'Duis', 'egestas', 'eget', 'eleifend', 'elementum', 'elit', 'enim', 'erat', 'eros', 'est', 'et', 'Etiam', 'eu', 'euismod', 'facilisi', 'facilisis', 'fames', 'faucibus', 'felis', 'fermentum', 'feugiat', 'fringilla', 'Fusce', 'gravida', 'habitant', 'himenaeos', 'iaculis', 'id', 'imperdiet', 'in', 'inceptos', 'Integer', 'interdum', 'ipsum', 'justo', 'laoreet', 'lectus', 'leo', 'libero', 'litora', 'Lorem', 'luctus', 'Maecenas', 'magna', 'magnis', 'malesuada', 'massa', 'mattis', 'Mauris', 'metus', 'mi', 'molestie', 'montes', 'Morbi', 'mus', 'Nam', 'nascetur', 'natoque', 'nec', 'neque', 'netus', 'nibh', 'nisi', 'nisl', 'non', 'nostra', 'Nulla', 'Nullam', 'nunc', 'orci', 'ornare', 'parturient', 'pellentesque', 'penatibus', 'per', 'Phasellus', 'placerat', 'porttitor', 'posuere', 'Praesent', 'primis', 'Proin', 'purus', 'quam', 'quis', 'rhoncus', 'ridiculus', 'risus', 'rutrum', 'sagittis', 'sapien', 'Sed', 'sem', 'semper', 'senectus', 'sit', 'sociis', 'sociosqu', 'sollicitudin', 'suscipit', 'Suspendisse', 'taciti', 'tellus', 'tempor', 'tempus', 'tincidunt', 'torquent', 'tortor', 'tristique', 'turpis', 'ultrices', 'ultricies', 'urna', 'ut', 'varius', 'vehicula', 'vel', 'velit', 'venenatis', 'Vestibulum', 'vitae', 'Vivamus', 'volutpat', 'vulputate');
/*
$sql = "INSERT IGNORE INTO tagsTest (Tag) VALUES ('";
$sql .= implode("'),('",$words)."')";
$conn->query($sql);
if($conn->errno){echo '<br>'.$sql.'<br><br>'.$conn->error;}
*/
echo '<div class="left"><p>OR</p>';
$start = microtime(true);
for($i=0; $i<5; $i++)
{
$tags = array();
for($j=0; $j<20; $j++)
{
$tags[] = $words[rand(0, (count($words)-1))];
}

$sql = "SELECT `Key`, Tag FROM tagsTest WHERE Tag = '";
$sql .= implode("' OR Tag = '", $tags)."'";
$tags = array();
//if($conn){echo $sql;}
if($result = $conn->query($sql))
{
while($row = $result->fetch_row())
{$tags[] = $row;}
}
if($conn->errno){echo '<br>'.$sql.'<br><br>'.$conn->error;}
echo '<div>';
foreach($tags as $tmp)
{echo "{$tmp[0]} =&gt; {$tmp[1]}<br>";}
echo '</div>';
}
$end = microtime(true) - $start;
echo "<p>$end seconds</p>";?>
</div>
<div class="left"><p>IN</p>
<?php
$start = microtime(true);
for($i=0; $i<5; $i++)
{
$tags = array();
for($j=0; $j<20; $j++)
{
$tags[] = $words[rand(0, (count($words)-1))];
}

$sql = "SELECT `Key`, Tag FROM tagsTest WHERE Tag IN ('";
$sql .= implode("', '", $tags)."')";
$tags = array();
//if($conn){echo $sql;}
if($result = $conn->query($sql))
{
while($row = $result->fetch_row())
{$tags[] = $row;}
}
if($conn->errno){echo '<br>'.$sql.'<br><br>'.$conn->error;}
echo '<div>';
foreach($tags as $tmp)
{echo "{$tmp[0]} =&gt; {$tmp[1]}<br>";}
echo '</div>';
}
$end = microtime(true) - $start;
echo "<p>$end seconds</p>";?>
</div>
<div class="right"><p>IN2</p>
<?php
$start = microtime(true);
$sql = array();
for($i=1; $i<6; $i++)
{
$tags = array();
for($j=0; $j<20; $j++)
{
$tags[] = $words[rand(0, (count($words)-1))];
}

$sql[] = implode("', '", $tags)."') AND pages.Key = $i)";
$tags = array();
}
$sql = implode(" OR (tagsTest.Tag IN ('", $sql);
$sql = "SELECT tagsTest.Key, tagsTest.Tag, pages.Key FROM tagsTest, pages WHERE (tagsTest.Tag IN ('".$sql;
if($result = $conn->query($sql))
{
while($row = $result->fetch_row())
{$tags[$row[2]][] = $row;}
}
if($conn->errno){echo '<br>'.$sql.'<br><br>'.$conn->error;}
for($i=1; $i<6; $i++)
{
echo '<div>';
foreach($tags[$i] as $tmp)
{echo "{$tmp[0]} =&gt; {$tmp[1]}<br>";}
echo '</div>';
}
$end = microtime(true) - $start;
echo "<p>$end seconds</p>";?>
</div>
</body>
</html>

After doing that test I started writing a test to compare running an INSERT query followed by a SELECT LAST_INSERT_ID() in a loop with making a long INSERT and SELECT query and then running them when the loop finished, and also with writing the queries in the loop but then executing them with multi_query() outside the loop. However, after writing this I came across a few problems.

First off, I have been SELECT LAST_INSERT_ID() rather than $mysqli->insert_id() because my understanding was that LAST_INSERT_ID() was connection specific whilst insert_id() probably wasn't. This was due to the following statement in the 'Friends of ED PHP Solutions - Dynamic Web Design Made Easy' book, which says:

The SELECT query highlighted in step 6 uses the filename and caption of the record just
entered as search criteria. This is a more accurate way of finding the primary key than a
technique that you often see recommended. By calling the mysql_insert_id() function,
you can get the primary key of the most recently inserted record (as long as it uses
auto_increment). MySQL Improved and PDO both offer equivalents with the insert_id
and lastInsertId properties. respectively. Most of the time, this will give you the information
that you want, but on a busy server, someone else might insert another record at
the same time. To be sure that you get the correct primary key, it’s best to be specific in
your request.

However, it seems that insert_id() is connection specific, and actually is the exact same thing as LAST_INSERT_ID(), except of course you can just run it from your mysql object in PHP rather than having to perform another query to get the ID.

The following sites/posters all say that insert_id() is reliable so long as you don't use a shared connection or close and open a connection between the insertion and calling insert_id():
Not getting last insert id
PHP and MySQL Insert ID
[PHP-DB] Is persistent connection + MySQL + Apache + insert_id === safe?
And even the mysql website says it's connection specific:

You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.

(I think I must have read this before but not understood that the mysql_insert_id() C API function is what PHP uses when using insert_id()).

So I will actually need(?) to rewrite my test to use insert_id().

The other thing was that with the long built up query, because we are inserting all the rows in one go, we can't get the last_insert_id, so instead I have to build up a long SELECT statement that selects the key based on all the data that's just been inserted. This is the same way the author suggests you get the last_insert_id in the PHP Solutions book I mentioned earlier.

So my code to pull the rows look like this:

if($result = $conn->query($sqlSELECT))
{
while($row = $result->fetch_row())
{$insertedCountries[] = $row;}
$result->close();
}

However, there is a problem with this, since your data might exist in the table more than once. If you are just selecting one id, then this okay, you can just loop through the rows retrieved until you get to the last one, which should be the last inserted row. But I can't see how to get it to work with selecting the id/keys of multiple records without selecting duplicate records.

This led me on to looking at how to avoid duplicate rows. This post explains how to do this using a Unique key on multiple columns: how to prevent duplicate rows but allow duplicate column entries. However, this doesn't work with columns that allow NULL, e.g. Unique indexes and NULL column values.

Then when I thought about it a bit more, I don't actually want UNIQUE rows in the percountry table (which is the table these tests are inserting/selecting from) as I'm using a lookup table to associate the page id with the percountry record. So in the percountry table I should be able to have 2 rows exactly the same, but then in the lookup table one row might be assigned to page 10, whilst the other row might be for page 12.

Then this got me thinking - why am I using a lookup table for this? Because each percountry record must be associated to one page, and one page only, I might as well just have the page key/id in the percountry table. Then before I insert a record to the percountry table I think I will need to do a check to make sure the record doesn't already exist.

After lunch I did a bit more reading/coding, then played on Animal for about an hour. I didn't do much really, just spoke to Sahara who said she wanted some old carpets, then I spoke to all the animals, and only one had an old carpet. Then I had to run around to try and find Sahara to give her the old carpet.

The rest of the afternoon I did more coding and reading, though I still haven't finished writing my test script yet.

After dinner I bid on a Lowepro Topload Zoom AW bag, I bid £20 with 30 seconds of the auction left, but annoying ebay didn't put the bid through and it sold to someone else for £16.66 + £7 P&P. Annoying for the person selling it as well since now they won't get as much for it as they would have done if ebay hadn't blocked my bid.

Then I watched Lost with Mac & Ben. I did a bit more coding and then watched the end of Shaolin soccer with Mac and Ben. After that Ben went on Animal again since it was his bed time and he wanted to see the meteor shower before he went to bed. Maccy looked it up on the pinternet, but it seems there's not really anything special about the meteor shower except you can see shooting stars (though you can't wish on them) in the sky.

Food
Breakfast: Bowl of fake coco pops; cup o' tea.
Lunch: Cheese straw; chicken tikka sandwich spread sandwich; 1½ bananas; toasted tea cake with butter; cup o' tea.
Dinner: Potato; peas; ham quiche. Pudding was a slice of chocolate swiss roll with tinned mandarin segments and chocolate custard. Coffee.

[innerHTML Vs. DOM] [PHP MySQL extensions]

I woke up this morning about 7am and there was a really nice sunrise outside with the cloudy sky lit up all orange, but I was too sleepy to get up so I went back to sleep and got up about 7.45am.

After breakfast I checked my email, deviantart, hotukdeals and thewebsqueeze. Clare found a small catterpillar in the iceberg lettuce (which was from Spain), so I took some photos of it. It looked like it was dead, but after I took a few photos of it it woke up and started wandering around.

Then I tried to look at some wiki sites to see how they work with editing the data and displaying the data.

After lunch I checked hotukdeals and my email. Then I did some work on my website, making the pogInfo form so it would be generated as javascript rather than coded sraight into the page. Basically I just copied the form as I had previously written it in php/html and made it into a string in php. In php I then escaped the string, which escapes any line breaks/new lines and ' chars, which would break javascript otherwise. Keeping the line breaks in there also means that the code when generated by javascript is easy to read. Then I echoed the string as a javascript variable. I made a new container div (coded into the page) to hold the form, then changed the javascript function so that when the edit button is clicked, the container div has its innerHTML changed to the variable holding the form HTML. I did searched on google, and found that using innerHTML is much faster (as well as loads less code to write) than using DOM methods.

I also changed the sql query used to get the tags. Previously I was getting all the tags row by row and putting them in an array. Then I was using a loop to stick the tags together in a string seperated by a comma and a space. Now the query uses GROUP_CONCAT to stick the tags together instead.

I was waiting for a lowepro lens case to finish on ebay, unfortunately it finished before I thought it would. I think what happened was when I refreshed the page to see how long was left, the page didn't actually refresh. So I thought there was still an hour left on the auction. Then I refreshed the page again about half an hour later and it said the auction had finished. Very annoying. I did bid on another lens pouch that hadn't finished, but got outbid for that. I'm hoping at the moment that the person who won the 5 lowepro lens pouches auction won't actually pay, and then I might get a 2nd chance offer.

I don't think I said in yesterday's blog, but I bid on (and won) a canon 100mm/2.8 Macro lens from the canon refurbished shop on ebay. It ended up about £320, only about £10-20 less than a brand new one, but since you get a year's warranty and it should have been checked by canon and be in working order (which a new lens may not be) I think it's still worth it. I hope I get a box and instructions with it though. The main reason for buying it now is not that I'm going to be doing a lot of macro soon, but that I won't be. So I can test the AF on it with the 450D and see if the AF is working properly. Then if it isn't, I can send it and the camera back to canon to get fixed and won't miss the camera because I don't have anything to shoot with it at the moment anyway.

I went on the rowing machine for a few minutes and watched an episode of MacGyver with Ben. After that I googled for 'Mysql mysqli PDO' to see which extension was best. A few threads/blogs said that PDO was slow, but one of the results said that in their initial tests PDO was much slower, but when they re-ran the tests, mysql, mysqli and PDO all worked at similar speeds. Anyway, on the official mysql site it says you should use mysqli php extension, and that you shouldn't use the mysql extension anymore as it's outdated. It suggests only using PDO if want your site to work with database systems other than mysql.

After dinner I watched Lost with Maccy and Ben, then watched the Gadget Show, then checked HotUKdeals. The weather today was grey skies raining slightly all day (apart from at sunrise where I don't think it was raining and the clouds were orange instead of grey).

Food
Breakfast: Strawberry crisp cereal; orange juice.
Lunch: Mature cheddar cheese sandwich; packet of Chilli flavour Doritos; banana; slice of cherry madeira cake; breakaway; cup o' tea.
Dinner: Chicken & mushroom pie; baked beans; poatoes; roast parsnips; cauliflower. Pudding was Bread pudding (made in the miguelwave) with custard.

Frosty morning and getting closer to getting mysqli working

I kept waking up this morning, but then I would go back to sleep because it was still dark. On about the fourth time I woke up I thought I better check the time since I thought it must be time to get up by now, but it was only 5.30am so I went back to sleep again. The next time I woke up it was 6.30am and it was starting to get light outside so I got up. It was frosty but not misty.

By the time I'd had my shower and breakfast it was 7.30am, and the sun had already been up for a while, sunrise was at 6.53am. So it probably would have been better to get up at 5.30am so I could catch the sunrise. Since the sun had already been up for a while I didn't bother going to take photos of the sunrise and frosty fields, but instead went into the back garden and took some macros of frosty leaves and grass.

After that I did a japanese lesson and got granny breakfast. The sky had completely clouded over by about 9.30am, the sky was clearer earlier.

I checked my email and had received some replies from evohosting, who said that the PHP.net mysqli Prepared Statements script works okay on another server so can they move my site there. Before replying to them I did some more checking on the version of MySQL needed to work with the mysqli extension for PHP, since it seems that the MySQL installation must be the thing that isn't working with prepared statements through mysqli.

The MySQL version installed on the server is 4.1.22, and the mysqli extension for PHP should work with MySQL version 4.1 and above. However, it seems that despite all the information given out in the PHP.net manual and the MySQL manual and other sites, the mysqli extension for PHP does not work with mysql version 4.1. It actually needs MySQL version 4.1.3.

After finishing checking my emails it was lunch time. After lunch I checked HotUKdeals, then did another japanese lesson. It rained a bit. I checked the canon lens forum on dpreview and then processed/sorted the frosty macro pics I took this morning and also a few macros I took yesterday.

It's actually snowing now! Though it's not settling :(.

I uploaded some of the frosty macros I took this morning to the weekly dpreview macro thread. One of the images I had resized for web using Juza's technique of oversharpening the fullsize image and then downrezzing. The other images just had the default ACR sharpening applied on RAW conversion, and then downrezzed. The one using Juza's technique looked a lot better to me, so after posting the pics to dpreview, I re-downsized the otherpics using Juza's technique (actually I didn't follow his technique properly and I'm sure even better results could be had if following it properly). The ones using Juza's technique all looked a lot better than the originals to me. Then I uploaded them to Flickr and joined the BBC Autumnwatch group and added the photos to that.

I read a few threads in the Nikkor lens group on Flickr, then it was dinner time. After dinner I watched Battlestar Trashlactica with Mac, it was the last episode of season 3 was and was actually quite good! Then I checked my email and had received a reply from evohosting to say they had moved my site to a new server which the php.net mysqli prepared statement example worked on. After that watched Autumn watch, then checked panoguide, hen went to bed.

Food
Breakfast: Orange marmalade toast sandwich; orange juice.
Lunch: Breaded ham with mustard sandwich; clementine; Gold bar; cup o' tea; Roses.
Afternoon snack: Cappuchino; malted milk; choc chip cookie.
Dinner: Slice of cheese and tomato pizza; slice of pepperoni pizza; peas; sweetcorn; deep fried potato; deep fried mashed potato; lemon flavoured couscous. Pudding was 1 ¼ coconut tarts with evaporated milk. Coffee.