Combining fields into one

[NFD]

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

[onlyican]

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

[Fehnris]

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.

[NFD]

Thats Fehnris :)

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

[Fehnris]

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

[Barand]

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