cutenews export to mysql - php tutorial

Home > PHP > cutenews export to mysql - php tutorial

cutenews export to mysql - php tutorial

December 8th, 2007 in PHP by Gertjan

Want to export your cutenews posts to a mysql database? In this tutorial i will show you how to export your posts from cutenews into a mysql table.

Fist off we start by creating the connection to the database:

$host: this is the location for the Mysql server it can be a hostname or an ip adress. it is usualy localhost.
$db: this is the Mysql user account used to access the database.
$user: this is the password for the Mysql user account
$pass: this is the name of the Mysql database used.

Now we include the export function.

We will need to include the function ‘cuteExport’ in the file ‘cutenewsexport.php’

Now we declare what data files should be exported from cutenews:

$newsfile: Cutenews uses ‘news.txt’ for it’s data. Specify the FULL path to this file.
$categoryfile: Cutenews uses ‘category.db.php’ for it’s categories. Specify the FULL path to this file.

Now we start the export to mysql by calling the function ‘cuteExport’:

This function will export all data in the ‘news.txt’ file and ‘category.db.php’ and import it in a Mysql table.
The function will export all data to a table called ‘cutenews_export_year_month_day_hour_min_sec’ by default.
You can change this in the cutenewsexport file where it says ‘$table = “cutenews_export_” . date(”Y_m_d_h_i_s”);’.
When the function finishes all your data will be inserted into a mysql table.

^?View Code PHP

 <?php
 
$host = 'localhost'; // MYSQL database host adress
$db = ''; // MYSQL database name
$user = ''; // Mysql Datbase user
$pass = ''; // Mysql Datbase password
 
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
 
require 'cutenewsexport.php';
 
$newsfile ='news.txt';
$categoryfile = 'category.db.php';
 
cuteExport($newsfile,$categoryfile);
 
?>

The include file:

Feel free to alter this file to your needs:

To change the default table name change the $table variable to your likings.

You can also export categories to a mysql table and use the ID as a reference in the export table.

file: cutenewsexport.php

^?View Code PHP

 <?php
 
 
############ DO NOT ALTER ############################
function str_replace_all($search, $replace, $subject)
{
    while (strpos($subject, $search) !== false)
    {
        $subject = str_replace($search, $replace, $subject);
    }
    return $subject;
}
 
############ END DO NOT ALTER ############################
 
function cuteExport($newsfile = '', $categoryfile = '')
{
    if ($newsfile == "")
    {
        echo "Please specify a 'news.txt' file to export.";
        exit;
    }
    if ($categoryfile == "")
    {
        echo "Please specify a 'category.db.php' file to export.";
        exit;
    }
 
    $export_count = 0;
    $export_success = 0;
    $export_failed = 0;
    $cat_count = 0;
    $cat_success = 0;
    $cat_failed = 0;
 
    // table name
    $table = "cutenews_export_" . date("Y_m_d_h_i_s");
    $tablecat = "cutenews_export_cat_" . date("Y_m_d_h_i_s");
    // table sql
    $create = "
		CREATE TABLE  $table (
  			`id` int NOT NULL auto_increment,
 			`date` varchar(20) NOT NULL,
 			`author` text NOT NULL,
 		 	`title` text NOT NULL,
  			`short` text NOT NULL,
  			`full` text NOT NULL,
  			`categoryid` int NOT NULL,
  			PRIMARY KEY  (`id`)
		);";
 
    $createcat = "
		CREATE TABLE  $tablecat (
  			`id` int NOT NULL auto_increment,
 			`category` varchar(100) NOT NULL,
  			PRIMARY KEY  (`id`)
		);";
 
    if (!mysql_query($create))
    {
        echo "Could not create export table !";
        exit;
    }
 
    if (!mysql_query($createcat))
    {
        echo "Could not create category table !";
        exit;
    }
    $lines_cat = file($categoryfile);
    foreach ($lines_cat as $single_line_cat)
    {
        $cat_count++;
        $value_arr_cat = explode("|", $single_line_cat);
        $i++;
        $catsql = "insert into $tablecat (id,category) value ('$value_arr_cat[0]','$value_arr_cat[1]')";
        echo $catsql;
        if (mysql_query($catsql))
        {
            $cat_success++;
        } else
        {
            $cat_failed++;
        }
    }
 
 
    // begin export
    $lines = file($newsfile);
    foreach ($lines as $single_line)
    {
        $export_count++;
        $value_arr = explode("|", $single_line);
 
        // insert export row in mysql table
        $query = "INSERT INTO $table (date, author, title, short, full,categoryid) VALUES (
			 '" . $value_arr[0] . "',
			 '" . addslashes($value_arr[1]) . "',
			 '" . str_replace_all("{nl}", "", addslashes($value_arr["2"])) . "' ,
			 '" . str_replace_all("{nl}", "", addslashes($value_arr["3"])) . "' ,
			 '" . str_replace_all("{nl}", "", addslashes($value_arr["4"])) . "',
			 '" . addslashes($value_arr["6"]) . "');";
 
        if (mysql_query($query))
        {
            // line exported
            $export_success++;
        } else
        {
            //line export failed
            $export_failed++;
        }
    }
    //Display export results
    echo "<table border='0' cellpadding='5' cellspacing='1'>
  			<tr>
    			<td colspan='2'><b>Export finished:</b><hr size='1' noshade></td>
  			</tr>
  			<tr>
  			  <td align='center'>$export_count</td>
   			  <td>posts in total.</td>
  			</tr>
  			<tr>
    		  <td align='center'>$export_success</td>
    		  <td>posts exported succesfuly.</td>
  			</tr>
  			<tr>
    		  <td align='center'>$export_failed</td>
    		  <td>posts failed.</td>
  			</tr>
  			<tr>
  			  <td colspan='2'><hr size='1' noshade></td>
   			  </tr>
  				<tr>
  			  <td align='center'>$cat_count</td>
   			  <td>categories in total.</td>
  			</tr>
  			<tr>
    		  <td align='center'>$cat_success</td>
    		  <td>categories exported succesfuly.</td>
  			</tr>
  			<tr>
    		  <td align='center'>$cat_failed</td>
    		  <td>categories failed.</td>
  			</tr>
		  </table>";
}
?>

Displaying news on pages divided in category can be done by filtering your result per category per page:

^?View Code PHP

 <?php 
 
$host = 'localhost'; // MYSQL database host adress
$db = ''; // MYSQL database name
$user = ''; // Mysql Datbase user
$pass = ''; // Mysql Datbase password
 
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);
 
$table = 'cutenews_export_2007_12_13_10_13_15';
$categoryid = (int) htmlentities($_GET['cat']); // You should make this more secure...
 
$sql = "select * from $table where categoryid = '$categoryid' order by date ";
 
$result = mysql_query($sql);
 
// displaying the actual page results:
 
while ($row = mysql_fetch_array($result))
{
 
    echo "
<div id ='news_$row[id]'>
<h1>$row[title] @ ".date("l jS M Y @ g:ia", $row['date'])."</h1>
<div>$row[short]</div>
<div>$row[full]</div>
</div>
";
}
?>

Now you can easily display news per category using a URL like: news.php?category=some_category_id

ineedtutorials.com