Add days to a varchar date?

[Designer_101]

Hi

I'm doing a project for express and star delivery. I have a varchar field for a week in my database

The name is of the field is 28-03-09
It then lists how much each customer owes

ID ---- Address ----- 28-03-09 ------ will
1 29 Broomy 1.20 1.2

I need my users to be able to click a button and the database adds a field 7 days from the previous (28-03-09).
Like this:

ID ---- Address ----- 28-03-09 ------- 04-04-09 ------ will
1 29 Broomy 1.20 1.20 1.20

There are many reasons for me wanting to do this however it would take ages to explain and is un-needed. :)

Any help would be greatly appreciated.
Thanks

[martin5211]

Basically, you can do:

$query = "SHOW COLUMNS FROM customer"; 
	 
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$fieldnames[] = $row[0];
}

list($d, $m, $y) = explode('-', $fieldnames[2]);

$timestamp = mktime(0,0,0,$m,$d,$y);

$newts = strtotime("-7 days", $timestamp);

$newdate = strftime("%d-%m-%y", $newts);

$query = "ALTER TABLE customer ADD `".$newdate."` VARCHAR(60) AFTER `".$fieldnames[2]."`";

$result = mysql_query($query) or die(mysql_error());

MySQL SHOW COLUMNS will display the table structure. After that, we populate an array with field names and we use the third field to create a new timestamp. strtotime() is our best simple chance to discount days, we format the timestamp to present a new date. Finally, MySQL ALTER TABLE will add the field after the other one.

Well, I subtracted days instead add... you can change this on strtotime() as well.

[Designer_101]

Hi

Will this still work as I have other content in my table?

$query = "SHOW COLUMNS FROM customers"; 
	 
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
	$fieldnames[] = $row[0];
}

list($d, $m, $y) = explode('-', $fieldnames[2]);

Isnt that assuming that all columns are dates?

Thanks for the reply

[martin5211]

It will work with minimal changes, if your table comply with the structure mentioned previously. Modify 'customers' table name using your own. Make a DB connection too if doesn't exist. If you do echo fieldnames[2]; after the loop you can see if that is the suitable field. strtotime() should be '+7 days'.

Some conditionals will be almost mandatory for more control. This code will always add a field using only the third column as reference. You want only two fields, use this

while ($row = mysql_fetch_array($result)) {
	$fieldnames[] = $row[0];

        if(strpos($row[0], '-')){
             $date_fields[] = $row[0];
        }
}

We create another array or use a variable incremented ($a++) to count the instances found and if that number is > 2, perform an update instead of create.

If you want to use the last date field as reference, you will need to apply this code and make an addition on fieldnames[] index.