cutenews export to mysql – php tutorial

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 <?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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
 <?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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 <?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

15 Comments
  1. Miller says:

    This website is just brilliant! so many useful scripts. how ever i would like to know how to add the news in the reverse sequence so i am able to display the news correctly. also how do i display the news on pages in certain catagories?

    Reply
  2. admin says:

    You don’t need to add the news in a reversed order, you can just build your SQL query so that it’s ordered by date instead of id:

    [cc lang='sql']
    SELECT * FROM the_export_table order by date;
    [/cc]

    ** Tutorial has been updated, for your request to display news per category on a page.

    Reply
  3. Is there a way to store the comments in the database as well?

    Reply
  4. Miller says:

    when the date is added, is it possible to add the date as date(“l jS M Y @ g:ia”, $value_arr[0]) instead of date(“Y-m-d h:i:s”, $value_arr[0]) because i like to show a full date and in the mysql it saying that it has to be datetime other but wont allow a “full” date and time, if you get me?

    Reply
  5. admin says:

    You can just leave date that’s in the database, and use PHP to extract the correct format in the same way in the display page:

    [cc lang='php']
    date(”l jS M Y @ g:ia”, $row['date'])
    [/cc]

    This should do the trick. Tell me if it doesn’t work i’ll create a workaround.

    @ Nicole Rork

    I’ll check to see if there’s a way to export these aswell. Visit again in a few days and i’ll have more information

    Reply
  6. Miller says:

    unfortunately all that is showing up is date(‘l jS M Y @ g:ia’,2007-11-25 11:48:39), is it not possible to use the unix date format and convert it with displaying the news? but at the same time, would you still be able to display the news in the correct order?

    Reply
  7. admin says:

    try doing this:

    in the Export function try changing the

    date(“Y-m-d h:i:s”, $value_arr[0])
    into
    $value_arr[0]

    this wil make the date enter the database as a unix timestamp

    ** note you have to change the create table command like this aswell:

    [cc lang='php']
    $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,
    `category` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
    );”;
    [/cc]

    Now you can do:

    [cc lang='php']
    while ($row = mysql_fetch_array($result))
    {

    echo ”

    $row[title] @ “.date(“l jS M Y @ g:ia”, $row['date']).”

    $row[short]
    $row[full]

    “;
    }
    [/cc]

    Hopes this fixes it?

    Reply
  8. Miller says:

    Brilliant! :D this has worked! Thank you so much. Did you get my email at all?

    Reply
  9. admin says:

    I’ve recieved your mail, and i will look into it. If i can find the time this week i’ll try to also get the comments working, plus writing some extra functions.

    greetz

    Reply
  10. Miller says:

    is there a way to change it so that each category is in a seperate table? instead of this one because when adding / editing and viewing and only viewing categories its best if its in a diff table if you get me?

    Regards

    Miller

    Reply
  11. admin says:

    I would not do that, the good thing about a database is that you can have them all in a same table, and filter the results per category. Wheter or not it’s in the same table makes no diff.

    ** Note i did change the database so that you can now use category ID’s instead of the category name.

    Reply
  12. nicole rork says:

    do you think there’s any way to convert cutephp news/comments into a wordpress database?

    Reply
  13. Alex says:

    Excelent work..that works great for me.

    Reply
  14. Khua Khai says:

    Fabulous website with lots of information to extract. I love it most… thanks

    Reply
  15. somebody can happen to me where under cutenews with the please modified archives that I cannot make work them

    Reply
Leave a Reply




XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


three + 1 =