Jump to content


Photo

Combining fields into one


  • Please log in to reply
5 replies to this topic

#1 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 27 September 2006 - 08:25 PM

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

#2 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 27 September 2006 - 08:31 PM

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
Tell me the problem, I will try tell you the solution

#3 Fehnris

Fehnris
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 27 September 2006 - 10:00 PM

convert all to seperate variables then use mktime to create new timestamp

//$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);



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.

#4 NFD

NFD
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 29 September 2006 - 08:31 PM

Thats Fehnris :)

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



#5 Fehnris

Fehnris
  • Members
  • PipPipPip
  • Advanced Member
  • 48 posts

Posted 01 October 2006 - 06:17 AM

Sure shouldn't be to difficult.  Something like the following should suffice. (Im assuming your database is a mysql database).


$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());

}


Hope that helps



#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 01 October 2006 - 10:17 AM

Add newtime column to your table then

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

where adate, atime are your column names
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users