MySQL have this load data function, where we can insert data to a table from external files like csv, txt etc.
The process is quite simple, 1st lets login to mysql via terminal/console.

mysql -u root -p

Enter your MySQL password. Change root to any username(MySQL users) you would wish to login to MySQL.

mysql>

Now lets run the command.

mysql> LOAD DATA LOCAL INFILE ‘/var/www/site/site_users.csv’
INTO TABLE site_users FIELDS
TERMINATED BY ‘;’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES;

You will see this if command ran successfully:

Query OK, 370 rows affected (0.00 sec)
Records: 370  Deleted: 0  Skipped: 0  Warnings: 0

In the 1st line, we load a file from local dir and that is the reason we used ‘LOCAL’.
2nd line will insert the data into site_users table.
In the 3rd line we tell the command to break the fields by ; . You can check your csv file in txt format that whether its ; or comma(,).
4th line means that data is enclosed with the double quotes.
And lines are terminated by \n.
Last line will ignore the 1st line, which usually represents the data headers.

One thing i would like to mention here, make sure that the table fields and csv columns sequence matches.
Like name, address, phone are in the table, csv will also be name, address, phone. Which means data will be inserted into the right field.
And yeh column name can be changed, that does not matter like in table first_name and in csv name. What i mean to say here is that the sequence of fields need to match that right data goes to the right field.

That’s it.

We can connect to MySQL the easy way or the other way by writing a MySQL wrapper, that’s upto the programmer. In this post, i will not go for any wrapper or class but will share my approach with PDO, which itself is OO.

In the code, i declare a variable this is to switch for local and live server easily, if you don’t want it just remove that piece of code.

<?php
$live = 0 ; // 1=live, 0=local

if($live === 0) :
$hostname = 'localhost';
$username = 'admin';
$password = 'passlocal';
$database = 'local_db';

elseif($live === 1) :

$hostname = 'localhost';
$username = 'admin';
$password = 'passlive';
$database = 'live_db';
endif;

if (defined('PDO::ATTR_DRIVER_NAME')):
 if(extension_loaded ('PDO')):
 if(extension_loaded ('pdo_mysql')):
 try {
 $dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 //echo 'Connected to database<br />';

 }
 catch(PDOException $e)
 {
 echo $e->getMessage();
 }
 else:
 echo "PDO support is available and is loaded, but it seems that pdo mysql extension is not loaded.";
 endif;
 else:
 echo "PDO support is available, but it seems that it is not loaded.";
 endif;
else:
 echo "Sorry, but it seems that PDO support is not available.";
endif;
?>

In the 1st few lines of if statement we check that whether POD driver is available if yes, is it loaded?. By default PHP already load this module and it is installed by default. but this is in case some thing is wrong. You can uncomment the echo line to make sure you are connected with the database.

And that’s it..

SMF forum is quit popular open source forum application used by many websites as their main bulletin board. Some time we need to grab posts,topics from the board and show them on our home/index page or anywhere on our site. To make the things much easier, SMF provides all this in one script where we call a function with arguments and dress it up according to our needs. File is SSI.php and can be found in the root folder of the forum. You can also add extra parameters/piece of code to your SSI.php. In this post i will show you how to retrieve posts from SMF board with additional information like board, post author, date & time plus top poster and board statistics.

$PostList = ssi_recentPosts(8, null, null, 'array');

The above piece of code will pull the last 8 posts from the forum db, as you can see i used array this is because i want to dress up my code like open links in new tab/window etc.

foreach ($PostList as $Post) {
//echo '<pre>', print_r($Post), '</pre>';
echo "<tr>";
echo "<div class='forum_posts'>";
echo '<td width="350px"><a target="_blank" href=', $Post['href'], '>', $Post['subject'], '</a></td>',
'<td width="250px"><a target="_blank" href=', $Post['board']['href'], '>',$Post['board']['name'],'</a></td>',
'<td width="100px"><a target="_blank" href=', $Post['poster']['href'], '>', $Post['poster']['name'], '</a></td>',
'<td width="150px">',$Post['time'],'</td>';
echo "</div>";
echo "</tr>";
}
unset($PostList);

The above code will loop the 8 results and print it on the page with post subject, board, poster name and date.

Now the additional stuff like top poster and board statistics:

<strong>Top Poster : </strong><?php ssi_topPoster();?> <hr id='line-hr'>
<strong>Forum Stats : </strong><?php ssi_boardStats(); ?> <hr id='line-hr'>

Above are the main code which will pull the things for you, now lets put the whole code in one place with html and its css.

Full code:

<h2>My Forum Recent Happenings!</h2>
<strong>Recent Posts : </strong>
<table class='main'>
<tr>
<th>Post</th><th>Board</th><th>Posted by</th><th>Time</th>
</tr>

<?php
$PostList = ssi_recentPosts(8, null, null, 'array');
foreach ($PostList as $Post) {

echo "<tr>";
echo "<div class='forum_posts'>";

echo '<td width="350px"><a target="_blank" href=', $Post['href'], '>', $Post['subject'], '</a></td>',
'<td width="250px"><a target="_blank" href=', $Post['board']['href'], '>',$Post['board']['name'],'</a></td>',
'<td width="100px"><a target="_blank" href=', $Post['poster']['href'], '>', $Post['poster']['name'], '</a></td>',
'<td width="150px">',$Post['time'],'</td>';

echo "</div>";
echo "</tr>";
}
unset($PostList);
?>
</table>
<strong>Top Poster : </strong><?php ssi_topPoster();?> <hr id='line-hr'>
<strong>Forum Stats : </strong><?php ssi_boardStats(); ?> <hr id='line-hr'>

You can put this code any where you want in you pages, just to make sure include the css below in the head tag of the header file(home/index etc etc) like:

<link rel="stylesheet" href="style.css" type="text/css" media="screen" />

CSS file code:

/* Table Layout */
table.main {
margin-top: .5em;
margin-bottom: .5em;
margin-left: .0em;
margin-right: .0em;
background: whitesmoke;
border-collapse: collapse;
font-size: 13px;
font-family: Arial;
}
table.main tr:hover {
background: #E6E6E6 !important;
}
table.main td {
border: 0px silver solid;
padding: 0.5em;
text-align:left;
border-bottom:1px silver solid;
}
table.main th {
border: 0px silver solid;
border-bottom:2px silver solid;
padding: 0.5em;
background: gainsboro;
text-align: left;
}
table.main caption {
margin-left: inherit;
margin-right: inherit;
}
/* Table Laout Ends */

/* Hr */
#line-hr {
height:1px;
margin-top:7px;
margin-bottom:7px;
background-color: silver;
border: 0px solid;
}
/* Hr Ends */

/* Forum Posts */
.forum_posts {
margin-top: 5px;
margin-bottom: 5px;
border-bottom: 0px solid #000;
}
.forum_posts a:link {
text-decoration: underline;
}
.forum_posts a:hover {
text-decoration: none;
color: #ea4900;
}
.forum_posts #time {
float: right;
}
/* Forum Posts Ends */

Save this file as style.css and include it in your head tag of the home/index page.

And that’s it, we are done… any question in mind use the comment section or you can ask in the SMF forum.

Personally i think, that Linux terminal is the strongest application/tool whatever you name it. Yesterday i was restoring a MySQL database size of 5MB via MySQL Administrator application, and it crashed several times. Actually i use it because the easy way to have GUI with few clicks, but that doesn’t mean i never used terminal for such things. But this crashing made me think of this powerful tool to use and use it forever..

So lets have backup our database 1st via terminal.

1- The 1st thing you need to do is open terminal(konsole).

2- mysqldump will be used to backup the database we want to.

mysqldump -u Username -p  databasename > backup.sql

Username = this is your database username
databasename = the name of your database which you want to backup
backup.sql = the filename for your database backup

The text in red should be changed according to your specific MySQL/database details. You can also give path to the filename where you want to save the backup file like: /home/mian/backup.sql.

Now lets run it in real form:

mysqldump -u admin -p storedb > /home/mian/storedb.sql

By entering this command and hitting Enter key will ask you MySQL password for user admin. By doing so will backup storedb database to the path given.

Now lets restore storedb database.

1- I assume that terminal is open.

2- Run this command in terminal:

mysql -u admin -p storedb < /home/mian/storedb.sql

This command is not much different except we don’t have mysqldump this time. Which means we are not dumping any database, just to restore one. It will also ask for the password for user admin to proceed.

If you want to run bash script to do the job for you by cron at specific times, i had written this script in past, but didn’t get time to update it regularly, but still is enough to do the backup archives with no manual running from command line.