[Resolved] How to convert a database in a txt file into mysql?

[sapoxgn]

hi there.. my new news system is done and working perfectly.. but now i have a problem 🙁

My old system didnt used mysql to store the news data... it used a txt file istead.. the txt file looks like this?

id|cid|time|user|title|news|fnews|data:vars

8f7ebedfdd|principal|1096051802|sapo|Finalmente estamos com site novo no ar!|[align=left][b][size=3]Agora é definitivo![/size][/b][/align]\n[align=left] [/align]\n[align=left]Eh nóis de site chique agora![/align]\n[align=left] [/align]\n[align=left]beijem o symn pelo Layout ok?[/align]||nm:c;fm:c;o:0;cx:;b:1;c:0;s:1;f:0;t:0;nc:0;q:0

anybody knows a simple way to put this data in a sql database
😕

Thanx for any help 🙂

[Roger_Ramjet]

I take it ths is a tab-delimited text file. Use phpmyadmin and load text file is the simple way. Or use load data infile if phpmyadmin is not available (and why not?)

[sapoxgn]

thanx a lot 😃

it worked perfectly on cpanel, but i m having a problem with the time field, what kind of data is it? 😕

[sapoxgn]

I ve found the function used to convert the time() to this stupid number.. take a look:

$nl['time'] = $s->cfg['time']; if 
($m->perms("cgdate", $cat) && 
$s->req['c']['date']['day'] != "" && 
$s->req['c']['date']['month'] != "" && 
$s->req['c']['date']['year'] != "" && 
$s->req['c']['date']['hour'] != "" && 
$s->req['c']['date']['minute'] != "" && 
$s->req['c']['date']['second'] != "") 
{$nl['time'] = 
mktime($s->req['c']['date']['hour'], 
$s->req['c']['date']['minute'], 
$s->req['c']['date']['second'], 
$s->req['c']['date']['month'], 
$s->req['c']['date']['day'], 
$s->req['c']['date']['year']); }

Btw.. i have to idea how to convert it to timestampo 🙁

[tsinka]

Hi,

do you mean that you need to convert it to the MySQL TIMESTAMP type (YYYYMMDDmmhhss) ?

You can do that with the date function. You already converted the value to a unix timestamp with mktime. The following code will convert it to a MySQL TIMESTAMP value:

....
$nl['time'] = $s->cfg['time']; 
if ($m->perms("cgdate", $cat) && 
    $s->req['c']['date']['day'] != "" && 
    $s->req['c']['date']['month'] != "" && 
    $s->req['c']['date']['year'] != "" && 
    $s->req['c']['date']['hour'] != "" && 
    $s->req['c']['date']['minute'] != "" && 
    $s->req['c']['date']['second'] != "") {
    $nl['time'] = date('YmdHis',mktime($s->req['c']['date']['hour'], 
     $s->req['c']['date']['minute'], 
     $s->req['c']['date']['second'], 
     $s->req['c']['date']['month'], 
     $s->req['c']['date']['day'], 
     $s->req['c']['date']['year'])); 
}
....

Thomas

[sapoxgn]

thanx.. i ll try it out 😃

I didnt understand this code.. btw...

i have just a number like:

8191228

how should i use this code? 😕

[sapoxgn]

I used this:

<?php

$s = 1096051802;

$s = date('YmdHis',$s);
 echo $s;

?>

and it returned:

20040924155002

This date is correct, but i stupid question now..

my date field in my mysql table is in timestamp format.. is it going to be a valid date?

[tsinka]

So the column type is MySQL TIMESTAMP ?
If yes, then the format is correct. The format of the TIMESTAMP type is:

YYYYMMDDhhmmss

YYYY: year (4 digits)
MM: month (2 digits)
DD: day (2 digits)
hh: hour (2 digits)
mm: minutes (2 digits)
ss: seconds (2 digits)

If you don't like that then rather use date, time or datetime as column type and store the data in that way.

You can fetch the value of a MySQL TIMESTAMP field as e.g. a unix timestamp by using the MySQL unix_timestamp function:

SELECT UNIX_TIMESTAMP(fieldnam) AS fieldname, ...

Or use the MySQL date_format function to fetch the data in a user specific format.

Thomas

[sapoxgn]

Thanx.. that worked.. but i still got a problem, take a look at my code:

<?php
  mysql_select_db($database_php, $php);
  $query_php = sprintf("SELECT * FROM mxnews");
  $not = mysql_query($query_php, $php) or die(mysql_error());



   while($row_not = mysql_fetch_assoc($not))
   {
//	id|cid|time|user|title|news|fnews|data:vars

$s = $row_not['time'];
$data = date('YmdHis',$s);
echo $data;
$autor = $row_not['user'];
$titulo = $row_not['title'];
$texto_c = $row_not['news'];
$texto_l = $row_not['fnews'];

  $submit_query = mysql_query("INSERT INTO noticia (titulo, autor, data, texto_c, texto_l) 
  VALUES ('$titulo', '$autor', '$data', '$texto_c', '$texto_l')") or die(mysql_error());
}

but i m getting this:

2004092415500220040924185919200409241948352004092420194620040924202940200409242036282004092420550420040925104637You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's novos, além de novos veículos, armas e ainda por cima deixa o

Wtf is wrong with it?

I ve commented the die(mysql_error()); and it worked.. but there was more than 10 lines missing in the table :queasy:

[sapoxgn]

I ve found this in the php manual:

<?php
function mysql_format($strTemp){
   //Format sql sentence for insert
   $bad_chars= array("\\", "'", "\"");
   $good_chars = array("\\\\", "''", "\"\"");
   return str_replace($bad_chars, $good_chars, $strTemp);
}

And it solved my problem, thanx for helping guys 🙂

[tsinka]

Alternatively, have a look at the mysql_real_escape_string function:

http://www.php.net/mysql_real_escape_string

Thomas