[Snippet] How to Search and Replace String in MySQL or PHPMyAdmin

Posted on by Bimo Hery Prabowo Leave a comment

Imagine, you have a 10K records in a table in your database. And on each line, there is a character that you want to remove or replaced with other characters. If do it manually, it may take days. Creating a system or script to solve the problem it was still takes a lot of times.

I have even had a case like this and I am looking on the internet. Turns out there and very simple. MySQL has a replace function similar to PHP. Just use this in phpmyadmin  and all your problems will be completed.

UPDATE `table`
SET `field` = replace(field, 'unwanted_char', 'wanted_char')

Problems are solved in seconds… very help me.

[Snippet] PHP Code to Submit data into Database

Posted on by Bimo Hery Prabowo Leave a comment

Although we use the framework to submit data into the database, sometimes we need a pure PHP code to insert data into the database. Honestly, sometimes we forget the command to insert data into the database, because the framework has been easier for us.

And this morning, I get a case where I have to enter the data into the database without using the framework. I usually use cakephp.

Here are a few lines of code that is easy to learn.

?php
function mysql_insert($table, $inserts) {
    $values = array_map('mysql_real_escape_string', array_values($inserts));
    $keys = array_keys($inserts);
        
    return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (''.implode('','', $values).'')');
}
?>

For example:

<?php

mysql_insert('cars', array(
    'make' => 'Aston Martin',
    'model' => 'DB9',
    'year' => '2009',
));
?>

very easy right?