Dec 08

cutenews export to mysql - php tutorial

PHP

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.

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

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

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

Related Posts

  1. Miller Said,

    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?

  2. admin Said,

    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:

    SELECT * FROM the_export_table ORDER BY date;

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

  3. Nicole Rork Said,

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

  4. Miller Said,

    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?

  5. admin Said,

    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:

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

    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

  6. Miller Said,

    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?

  7. admin Said,

    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:

    $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`)
            );"    ;

    Now you can do:

    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>
    "    ;
    }

    Hopes this fixes it?

  8. Miller Said,

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

  9. admin Said,

    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

  10. Miller Said,

    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

  11. admin Said,

    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.

  12. nicole rork Said,

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

  13. krissy Said,

    DtyFKH gfb07yvt9d6t94wbtx63bgq7d

  14. Zuppwvzf Said,

    Very Good Site coral colored teen dress lzl

  15. Jdwxvadb Said,

    Wonderfull great site the best gay tests 8-[[

  16. Qktxqnnr Said,

    It’s serious hot wet cunt gallery wevuu

  17. Dglhhhkm Said,

    Best Site Good Work gay biker leather video rudev

  18. Pxyjxbhw Said,

    magic story very thanks free 65 old porn 8P

  19. Gwtrjuou Said,

    I’m happy very good site north carolina porn stars :[[[

  20. Ljdfckyk Said,

    pics of pornstar leena 8864

  21. Bakrmzzj Said,

    good material thanks xxx g booty porn %-OO

  22. Glvjsvti Said,

    this is be cool 8) palm beach ebony escort abex

  23. Uvzluxnk Said,

    very best job candice michelle sex pic xxx 317247

  24. Gutceeyp Said,

    this is be cool 8) suck my own penis jxc

  25. Zqzpwvek Said,

    Very Good Site teens in the beach pictures 014441

  26. Bzsjxwry Said,

    magic story very thanks please give me a blowjob >:)

  27. Cuqjghrb Said,

    this post is fantastic black teen in tampa 171417

Add A Comment