[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?

Easy Access MySQL Database via NodeJS

Posted on by Bimo Hery Prabowo 1 Comment
nodejs access database mysql

NodeJs is very powerful javascript, nodejs powered many app in the world. Because it uses javascript, then nodejs can not connect to MySQL server or MySQL database. Because of these limitations, we can not directly access the database through nodejs.
Fortunately, nodejs have a complete library, one of which is the http request, such as curl in PHP or as a browser that we use.
With the http request, we can access the database. We will be using 2 pieces of files

  1. submit.php where we can access database via php file
  2. send.js where javascript file to access nodejs

Submit.php

<?php
 $key1 = mysql_escape_string($_POST['key1']);
 $key2 = mysql_escape_string($_POST['key2']);
 
 
 mysql_connect('localhost','root','');
 mysql_select_db('test');
 mysql_query("INSERT INTO `test`.`send` (`judul`, `isi`) VALUES ('$key1', '$key2');");
?>

Send.js

var request = require('request');
 
// Set the headers
var headers = {
    'User-Agent':       'Super Agent/0.0.1',
    'Content-Type':     'application/x-www-form-urlencoded'
}
var key1="Title";
var key2 ="This is content";
 
// Configure the request
var options = {
    url: 'http://localhost/submit.php',
    method: 'POST',
    headers: headers,
    form: {'key1': key1, 'key2': key2}
}
 
// Start the request
request(options, function (error, response, body) {
    if (!error && response.statusCode == 200) {
        // Print out the response body
        console.log(body)
    }
})

Then you can running nodejs app via command line, just type

node send.js

Resource

You can follow these resource to improve this trick

  1. http://unirest.io/nodejs.html (easy way to request post and get via nodejs)
  2. http://davidwalsh.name/nodejs-http-request (basic http request via nodejs)
  3. Request json https://www.npmjs.com/package/request-json

What Next ?

If you can access the MySQL database via nodejs, then you should be able to develop this concept as read records from the database, delete records, edit, and so on. There are still many things that can be developed.

Thank you for reading this article

Complete Guide : How to Setup Web Server on Linux and Install WordPress

Posted on by Bimo Hery Prabowo Leave a comment
Eye Drawings

Using Linux and the web server sometimes have to read a lot of tutorials and step-by-step. I can not memorize one by one. So, I wanted to make this post useful for the newbie who wants to create a server with linux.

What is needed is

  1. Ubuntu 14.04 or above
  2. Internet connection

This tutorial will discuss

  1. How to install PHP, MySQLand Apache2 and seting it up
  2. How to change webroot location to your home directory  or where you want
  3. Install phpmyadmin
  4. Install wordpress

Ok, lets begin. Open your terminal and get into step one

Install Apache2

Use this command and enter

sudo apt-get update
sudo apt-get install apache2

Install MySQL

sudo apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql

You will get prompt to setting up database. Fill password and enter. Its simple

Install PHP

sudo apt-get install php5 libapache2-mod-php5 php5-mcrypt

After this step your server is ready to battle. Your webroot directory in /var/www/html/

Try to open localhost in your browser. Ok, let go to step two.

Change Webroot Directory

Maybe some of you want to change the webroot directory so as not to touch your linux system. The most convenient way is to move the webroot to your home directory, eg /home/dono/www

We will try to make this easier. There are 2 files that you must change.

  1. file apache2.conf dengan alamat /etc/apache2/apache2.conf
  2. file ooo-default.conf dengan alamat /etc/apache2/sites-available/000-default.conf

Edit Apache2.conf

sudo gedit /etc/apache2/apache2.conf

Search this code lines

<Directory /var/www/>
Options Indexes FollowSymLinks
AllowOverride None
Require all granted
</Directory>

And change as look like this

<Directory /home/dono/www/>
Options Indexes FollowSymLinks
AllowOverride None
Require all granted
</Directory>

Save and done

Edit ooo-default.conf

sudo gedit /etc/apache2/sites-available/000-default.conf

And search this line

DocumentRoot /var/www/ and change to DocumentRoot /home/dono/www

Then save.

Fix permission

sudo chmod -R 777 www-data:www-data /home/dono/www

Dont forget to restart your server with this command

sudo service apache2 restart

Install PHPMyAdmin

Ok, just use this command

sudo apt-get install phpmyadmin apache2-utils

You will get prompt to setting your PHPMYadmin configuration. Its simple. Dont forget to include PHPMyAdmin config file to apache config file, in order you can access phpmyadmin with simple URL like this

http://localhost/phpmyadmin

sudo gedit /etc/apache2/apache2.conf

then add this code in apache2.conf file (in below)

Include /etc/phpmyadmin/apache.conf

Then save. Done

Install WordPress

Install wordpress is very simple step. We use wget to downlaod wordpress file

cd /home/dono/www
wget http://wordpress.org/latest.zip

unzip latest.zip

Then install wordpress by http://localhost/wordpress.

Bonus : Fix WordPress Ask FTP account

Sometimes when you want to install a plugin or theme, wordpress ask FTP username and password. Actually, this is not necessary. All you have to do is with a few lines of code to fix this.

sudo chown -hR www-data:www-data /home/dono/www/wordpress

Thanks to narga.net for provide this tricks

That is a short tutorial for setting up a linux server and installing wordpress. I wrote this tutorial to remind myself and help friends all