Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Charlie Aitkin
Charlie Aitkin
5,721 Points

Writing a mySQL trigger (using PHP) to update an existing table when a batch update is done - Ignoring duplicate values.

Hey everyone!

I've been tasked with writing a script that updates mySQL tables every 24 hours. So far I'm grabbing the .DBF files, converting them to .CSV files and then uploading them as temporary tables in phpMyAdmin. Then I drop the original tables and then rename the temp tables with the original tables names.

The next step I'm a little misty on - Triggers.

I'm trying to change my process, so instead of dropping and renaming tables I would just compare the newly created CSV against the exisitng table and only update the values which are different, leaving data that is the same as it is, untouched.

I've written this scirpt in PHP and I was hoping one of you could help shed some light on mySQL triggers and how I can achieve this functionality.

Thanks!

Charlie

1 Answer

Charlie Aitkin
Charlie Aitkin
5,721 Points
Charlie Aitkin
Charlie Aitkin
5,721 Points

It turns out that using a trigger would've been too inefficient.

Instead I used an UPDATE query, only updating the table columns I knew I wouldn't be updated from the .CSV files, but from user actions.

Feel free to have a look at my code below, it does the job! :)

$con=mysqli_connect($db_host, $db_user, $db_pass, $db_name);

$updateTbl = "UPDATE $tableNameOrig o
                JOIN $tableNameTemp t
                ON o.id = t.id
                SET o.customerCode = t.customerCode,
                o.customerName = t.customerName,
                o.customerEmail = t.customerEmail,
                o.customerAddress = t.customerAddress,
                o.customerAdded = t.customerAdded;";

if (mysqli_query($con,$updateTbl)) {
    echo "$tableNameOrig successfully updated. \n";
} else {
    echo "Error updating $tableNameOrig: ".mysqli_error($con).".\n";
}

Thanks!