Jump to content

Archived

This topic is now archived and is closed to further replies.

NFD

Combining fields into one

Recommended Posts

Hi everyone,

What I am looking at doing is combining two database fields into one.

To explain that better....

I currently have two fields:
- date ( int(16) ) e.g. 20050425
- time ( varchar(10) ) e.g. 20:57

What I want is one field:
- newtime ( int(10) ) e.g. 1114376220 (unix time stamp)

I have around 200+ of these to do and would like to automate the process.
Is there a way of reading the two existing fields and converting them into the one field?


Thankyou in advance :)

Share this post


Link to post
Share on other sites
If the vars are simular name

then you could run a loop
then do
$field1 .= $field2;

I dont know how much php u know
OR how the file is set out

Share this post


Link to post
Share on other sites
convert all to seperate variables then use mktime to create new timestamp

[code]
//$date = <your date database field>
//$time = <your time database field>

$year = substr(strval($date), 0, 4);
$month = substr(strval($date), 4, 2);
$day = substr(strval($date), 6, 2);
$hour = (int) substr($time, 0, 2);
$minute = substr($time, 3, 2);

$timestamp = mktime($hour, $minute, 0, $month, $day, $year);

[/code]


put that in a loop to check through all your database records and add an update query to update the new timestamp field for each record.

Share this post


Link to post
Share on other sites
Thats Fehnris :)

Can you please explain how to put it into a loop that will also update the table?

Share this post


Link to post
Share on other sites
Sure shouldn't be to difficult.  Something like the following should suffice. (Im assuming your database is a mysql database).

[code]

$con = mysql_connect ("$HOST", "$USERNAME", "$PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$DATABASENAME");

$sqlquerystring = "SELECT * From 'insert your table that has the date fields here'";
$sqlresult = mysql_query("$sqlquerystring") or die ('sqlquerystring error: ' . mysql_error());

while($tablerow = mysql_fetch_array($sqlresult))
{

  $date = $tablerow['insert your date database field here'];
  $time = $tablerow['insert your time database field here'];

  $year = substr(strval($date), 0, 4);
  $month = substr(strval($date), 4, 2);
  $day = substr(strval($date), 6, 2);
  $hour = (int) substr($time, 0, 2);
  $minute = substr($time, 3, 2);

  $timestamp = mktime($hour, $minute, 0, $month, $day, $year);

  //Hopefully you have an 'id' field in your database table that is set to auto increment.  The record update string below relies on the
  //fact that an id field is present to determine which record to update in the database.

  $sqlupdatestring = "Update 'insert your database table name here' Set 'name of new timestamp field'=$timestamp where id=".$tablerow[id];

  $result = mysql_query("$sqlupdatestring") or die ('sqlupdatestring error: ' . mysql_error());

}

[/code]

Hope that helps

Share this post


Link to post
Share on other sites
Add newtime column to your table then

[code]
UPDATE tablename
SET newtime = UNIX_TIMESTAMP(CONCAT(SUBSTRING(adate,1,4),'-',
SUBSTRING(adate, 5,2),'-',SUBSTRING(adate,7,2),' ', atime))
[/code]

where adate, atime are your column names

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.