Jump to content

Combining fields into one


NFD

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 :)
Link to comment
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.
Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.