I came across a situation where I needed to pull records from 1 db to another on a recurring basis. I read many different ways to do it via searching around the net. Some of the suggestions included creating a temporary table and copying it over. What was lacking was that sometimes the new table also needed to be UPDATED, as the information changed from the original table.
I’ve come up with a dirty little example to show this can be done. There are more keywords that can enhance the ON DUPLICATE KEY UPDATE function even more. The Example is assuming an “employees” table that looks like so:
| employees |
| id (PK) |
| first_name |
For brevity, I’ve left out connection data etc;
$myArray = array(0 => array('id' => '1', 'fname' => 'Steve'),
array('id' => '2', 'fname' => 'sara'),
array('id' => '3', 'fname' => 'Matt')
);
// Don't forget to validate & clean your data
// Connect to db here
foreach($myArray as $key => $value){
$query = "INSERT INTO employees
(id, first_name) VALUES ('$value[id]', '$value[fname]')
ON DUPLICATE KEY UPDATE first_name = '$value[fname]'";
$result = mysql_query($query);
if(!$result){
print("Problem: " . mysql_error() . "");
} else {
print("Success !");
}
}
Read the manual on this for more information. This snippet will INSERT if a UNIQUE (in this case ‘id’) does not exist. It will UPDATE any existing unique. In this case any existing ‘id’s, the ‘first_name’ column will get updated.
One could also use the REPLACE function. As I understand the difference, REPLACE will DELETE any matching uniques, and INSERT a new record in it’s place.
Comments? Better ways? I’m always up for learning something new so please chime in ;-)
The rush is just around the corner. I found a bunch of sites that list places and prices of ‘hot’ Black Friday deals. Most of which include links to many major stores:
How much shopping will you be doing online this year?
One thing I thought I would look forward to as a home owner was the satisfaction of household manual labor. This is our first home and we’ve now been in it for 3 years. I’m not mr. fixit by any means but I’m learning as I go. I’ve repaired toilets, replaced ceiling fans, repaired mailboxes, replaced sump pumps, repaired dishwashers, replaced garbage disposals, re-routed heat placement by adjusting ventilation doors, minor landscaping, and the usual duties like mowing the lawn and shoveling snow. If I’ve got the time to learn I’m willing to take on most things. (Although I try to steer clear of electrical and plumbing duties).
I’ve found the novelty really wears off after about 2 minutes when it comes to shoveling snow and raking leaves (we live on a corner lot). The lot is a size thats small enough that it doesn’t really require a tractor mower. We have 3 silver maples that tend to hang on to their leaves until early December depending on the weather, and that is rarely predictable in Wisconsin. The trees are large and provide wonderful shade in the summer for the entire house, and they are nice to look at. In the spring they litter the yard and the gutters with helicopters. In the fall the leaves cover the land but not all at once. Some are from neighboring parcels.
It’s hard to get a nice weekend to rake where I’m home, and most of the leaves have fallen. Often if the maples haven’t lost their leaves it’s futile to get out there. Not knowing if it will snow soon (never know in WI) this Saturday seemed like the time to get out there. The best thing about the project is the family time.
(The day after raking)
Of course not all the leaves fell. So on Sunday we ended up re-raking a lot of it. If you don’t get the majority of them they get wet in spring and your entire lawn smells of decomposing leaves. I managed to get up on the roof to clean out the gutters too. Not cleaning them out often results in waterfalls over them. Which is neat, but bad for a home. Water is one of a homes worst enemies. It’s best to direct it away from the home.
A few years ago I invested in some ‘lattice’ type covers for about $0.98/foot. I tried it out on one section because if they didn’t work well, I didn’t want to remove the entire length of the house. They worked fine for the first season. But now in the spring the helicopters fall into the slots and stick there from the sap. It looks like I’m growing a garden of helicopters in my gutters. They keep larger leaves out, but twigs and smaller debris gets gummed up in there from the sap and I have to remove them, clean it out and replace them again. My advice: don’t bother. It’s just easier to get up there 3 times a year for about 2 hours cleaning them out.
I’m too cheap to opt for the ‘leaf guard‘ or ‘gutter topper’ type devices. Have any of you had experiences with these? Do they truly work as promised? I’ve heard some pretty pricey quotes on them, and I just don’t trust them.
Fall, leaves, raking
This may seem simple enough to many programmers out there but this threw me for a few days. This is how you install imagick on a windows server. If your site is on a shared host, you need to ask your host to install the extension for you.
For this example I am running php 5.2.4 on Abyss/Apache web server
Download the matching pecl5 Binary from php.net. In this case its pecl5.2-win32-200710121230.zip (matching my current php version).
Unpack the file.
Copy the php_imagick.dll into php/ext/ directory
Open your php.ini file and look for the “Dynamic Extensions” area. In there will be a list of items (some commented out). Add the following line to the bottom of the list: extension=php_imagick.dll
Save the changes you made.
Restart your web server.
If these steps were successful imagick will show up on any php page using the phpinfo() function.
imagick, pecl-extension, php-extension
