Closed

Write PHP script to fix deleted column in MySQL Table

This project received 41 bids from talented freelancers with an average bid price of $125 USD.

Get free quotes for a project like this
Employer working
Skills Required
Project Budget
N/A
Total Bids
41
Project Description

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.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online