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

Recommended Answers

All 3 Replies

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.

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.