Closed

Write PHP script to fix deleted column in MySQL Table

A column in a Mysql table was accidently deleted today... and the only backup was 1 year old... A PHP and MySQL script should be written to fix the missing data which can be calculated from the remaining data as described here..

The columns are:

ID, CustID, PackagID, StartDate, EndDate, Paid, Qty, ExtraInfo, Cost, Invoiceno, nextno

The field called NextNo was accidently deleted for all rows in the database.

A PHP script should scan through the table and work out the ID of the next billed entry and then update the table to repopulate the field called 'nextno'

E.G.

| 9960 | 712 | 49 | 2011-11-09 23:00:00 | 2011-12-08 23:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4622 | 10054 |

| 10054 | 712 | 49 | 2011-12-09 23:00:00 | 2012-01-07 23:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4680 | 10115 |

| 10115 | 712 | 49 | 2012-01-08 23:00:00 | 2012-02-06 23:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4714 | 10335 |

| 10335 | 712 | 49 | 2012-02-07 23:00:00 | 2012-03-07 23:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4787 | |

| 10403 | 712 | 49 | 2012-03-08 23:00:00 | 2012-04-08 00:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4813 | |

| 10525 | 712 | 49 | 2012-04-09 15:39:41 | 2012-05-09 15:39:41 | pending | 1 | Bespoke Hosting | [url removed, login to view] | 4835 | |

| 10555 | 712 | 49 | 2012-04-08 00:00:00 | 2012-05-08 00:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4842 | |

| 10638 | 712 | 49 | 2012-05-09 00:00:00 | 2012-06-09 00:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 4948 | |

| 10807 | 712 | 49 | 2012-05-10 15:39:41 | 2012-06-09 15:39:41 | pending | 1 | Bespoke Hosting | [url removed, login to view] | 4985 | |

| 10894 | 712 | 49 | 2012-06-10 00:00:00 | 2012-07-10 00:00:00 | pending | 1 | Bespoke hosting | [url removed, login to view] | 5014 | |

| 10957 | 712 | 49 | 2012-06-10 15:39:41 | 2012-07-10 15:39:41 | pending | 1 | Bespoke Hosting | [url removed, login to view] | 5053 | |

| 11082 | 712 | 49 | 2012-07-10 15:39:41 | 2012-08-10 15:39:41 | pending | 1 | Bespoke Hosting | [url removed, login to view] | 5152 | |

| 11499 | 712 | 49 | 2012-08-11 15:39:41 | 2012-09-11 15:39:41 | pending | 1 | Bespoke Hosting | [url removed, login to view] | 5405 | |

| 11629 | 712 | 49 | 2012-09-12 15:39:41 | 2012-10-12 15:39:41 | pending | 1 | Bespoke Hosting | [url removed, login to view] | 5548 | |

E.G. in the above 10403 is clearly the 'nextno' for 10335

Not all the rows are that simple because Sometimes the purchasedate is the same date as the expires date, sometimes it is the day after and sometimes it is a day before.. So the script will need some kind of tolerance to work this out. Some examples of this in PHP are:

foreach ($list as $r) {

$current = $r;

foreach ($list as $a) {

if ($a[id] != $r[id]) {

if ($r[expiresdate] <= $a[purchasedate]) {

if ($a[extrainfo] == $r[extrainfo]) {

if ($a[packagid] == $r[packagid]) {

if ($a[cost] == $r[cost]) {

echo "Possible Match: $a[id] nextno for $r[id]\n";

$s="UPDATE purchased_packages set nextno = '$a[id]' where id = $r[id]";

//$g=mysql_query($s);

echo $s."\n";

break;

}

} } }

}

}

}

I will provide a full copy of the MySQL Table to the person who takes this on.

Skills: MySQL, PHP

See more: to fix it, foreach in php and mysql, bespoke data entry, write php and mysql, write a simple php script, mysql backup, column, database copy script, php backup mysql, mysql update field, missing columns data, mysql backup table, mysql date field, mysql copy database, missing table, year date column, missing database table, php mysql field list, backup mysql php, php script echo, write script data entry, fix match, mysql update table, script entry database entry, echo script php

About the Employer:
( 11 reviews ) London, United Kingdom

Project ID: #4313674

40 freelancers are bidding on average $127 for this job

toinnisfree

quick pro help. ftp and milestone required.

$200 USD in 3 days
(520 Reviews)
7.8
cevouk

Hi there Please find my bid, I've allowed for several hours encase there is any difficulties but we should be fine. Thanks James

$135 USD in 4 days
(172 Reviews)
7.3
rajeshsonisl

Quality work assured. Ready to get started. Thanks.

$300 USD in 1 day
(368 Reviews)
6.9
zeke

Expert PHP/MySQL developer. Available to start immediately and finish today.

$100 USD in 1 day
(145 Reviews)
6.8
rinsadsl

please refer to my private message

$150 USD in 4 days
(187 Reviews)
6.7
mitrasubir

Hi, Please check PMB for details. Thanks KD

$250 USD in 7 days
(38 Reviews)
6.6
DrTech76

Hi! Actually a walk-through algorithm won't be that much difficult to implement. I have some questions though. Sending you message in a sec. Kind Regards

$50 USD in 0 days
(240 Reviews)
6.5
linuxfreak1985

Hi there, We are experts in PHP Open Source (any kind of PHP/MySQL work),wordpress and Ajax/Web 2.0 technology. Some of the projects we completed for php are mentioned below https://www.freelancer.com/projects/ More

$250 USD in 15 days
(62 Reviews)
5.7
diegou

hi i can do this

$90 USD in 1 day
(35 Reviews)
5.7
weonlydo

Hi, I am interested in this project and would like to discuss further.

$150 USD in 3 days
(33 Reviews)
5.4
seekdeveloper

Hi, Consider its done! I would love to get this opportunity of working with you. I can guarantee complete satisfaction if you select me to execute this project. Please check your private message board. Thanks

$230 USD in 2 days
(13 Reviews)
5.3
D3SolutionsIndia

please check private message

$130 USD in 3 days
(18 Reviews)
5.2
Eliteprog

Hi, First of all thanks for posting this project, I am available to provide you economical services for bug fixing on web model, I have read and understood all of project details. I am expert in opensource developm More

$131 USD in 6 days
(22 Reviews)
5.0
abanipatra

Hi, I would like to work on your project.

$60 USD in 2 days
(43 Reviews)
4.7
pixecs

Dear sir, i can help you with this project. I have over 7 years of experience in mysql database programming, and over 3 years of experience in Php development.

$40 USD in 3 days
(25 Reviews)
4.7
pollux1er

------- Hi, this is a 01 hour Job! I can do this for you!!! -----------

$50 USD in 0 days
(26 Reviews)
4.5
svipl7

Hello we are 8+ years old web-development company. please check our portfolio for more details. thanks

$160 USD in 2 days
(9 Reviews)
4.5
trongtd1988

I can do it for you, I'm professional in PHP, MySql. You can give me this project. i will do it for you. Thank you.

$50 USD in 2 days
(1 Review)
4.5
freelancerbuzzo

Check PM, please.

$90 USD in 1 day
(12 Reviews)
4.4
apbsystems

Hi, I can help you to fix the deleted column. Thanks for considering my bid. Patrik.

$100 USD in 2 days
(8 Reviews)
4.3