php & mysql updating help

[busnut]

G'day guys/girls

 

I currently have an enthusiast website on buses, which has plenty of information currently stored as flatfile, and as the person who had been helping me develop it over the years has now retired, I think now its time to convert the database to mysql.

 

Now i've got no probs with getting most of the info I want displayed from a database, except for the following 2 items that come to hand.

 

Firstly, when I currently update the database, I use a form on my website that if a bus is transferred to another depot, or is delivered or for any other reason, it puts in the history database what the change was, for example if bus 100 is transferred from Adepot to Bdepot, it will change the database depot field to its new location, and in the history database, will display "100|m01|Jan 2009|Transferred from Adepot to Bdepot".

Now this is where i have no idea what i'm doing, and that is when a change is made, I want it to make a note on the history file of its change as I have another page on my website where users can view the changes for each month.

 

Secondly, on the fleet allocation page, what this page does is displays all the currently active buses, but by numerical bus order, but also by depot, for example:

Adepot: 100, 101, 105 (3 buses)

Bdepot: 102, 106, 108, 109 (4 buses)

Cdepot: 103, 104, 107 (3 buses)

etc etc and gives a total count of the buses allocated to each depot and then a total count of all the buses in all the depots.

There are 2 forms of fleet allocations on my site, one is the short version which is above, the second version is a longer display which then does the above, but also sorts them out to each bus type like this:

Adepot:

bustypea: 100, 101 (2 buses)

bustypeb: 105 (1 bus)

Total 3 buses

Bdepot:

bustypea: 102 (1 bus)

bustypeb: 106 (1 bus)

bustypec: 108, 109 (2 buses)

Total 4 buses

Cdepot:

bustypea: 103, 104 (2 buses)

bustypec: 107 (1 bus)

Total 3 buses

 

So stage one of this project is I need to get the project to record history information, but bare in mind that not everything is recorded, so I have a button that I click if I want the change recorded, but not the note added to the history file, and then after that, to work on the fleet allocation script so it will view the information as described.

 

In total, I currently have a database for the fleet, the depots, the busmake, the fueltype, the history, so when a page is displayed or more so when somebody does a search, the final result is that the page can look at up to all the databases to get the information.

 

So any ideas, or help, or even suggestions?

 

Currently the database is setup for one bus company only, although there may be plans in the long term to have it so other companies could also be included (which really means just an extra field for that company that the results are then searched by). But thats down the track!

 

[fenway]

TLDR... what's the gist of the question?

[busnut]

TLDR... what's the gist of the question?

Because of my lack of experience with mysql and limited knowledge in php, its more so a question of how can this be done and more so does anyone know how to do it.

[xtopolis]

If you're asking about how to make the database, read up on normalization:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

If you asking how to convert flat file -> mysql, you can probably use Mysql's LOAD INFILE thing or PHP to parse it out and into the database.

 

I read as much of it as I could stand and it seems like you already have ideas going on -- why not give them a test on a smaller scale and come back with specific questions?

 

My suggestion is that you start small and get a feel for it.  You have something that works already, so you don't need to rush in creating the new thing until you've got it working the way you want it.

[busnut]

The converting from flatfile -> mysql is not a problem.

Yes, I do have a system that does currently work, but am looking that in the near future as the flatfile database grows even more, it needs to be converted, and plus will hopefully have somewhat a better security measures than the old flatfile system does.

It really boils down to, is I have no idea how to make a php script that when a field is changed, that it will record the changed information onto another database whilst the actual information is changed on the fleet database.

This by itself is the most important part as I want the process automated without the need to retype the information into another database that I had already changed. And once the 'update' script works, then the rest can be done in drips & drabs as time permits.

So really, im asking for anybodies help that can put a script together (as it certainly is beyond my capabilities and knowledge) that will work in the same principle as the current php/flatfile system does.

[xtopolis]

If you know how to enter data into a database with PHP, you already know how to create your history / changelog thing.

 

If you have a form that allows you to edit something about your busses, when you submit it you could:

a)have the original values stored in a session so that you could compare them to the updated ones

or

b)copy the old row to another table when you make any changes.  actually you could just use a DATETIME column on the row as like "updated" and insert all rows as new and only display the most recent one.

 

The actual code is as easy as "copy this" "update this other" or "insert this as new" right after another.

 

So then later in another part of your page you could get the history by selecting all of the rows for that bus thing and order by Descending.  Does that make any sense?

(the reason I think adding new rows to the same table might work is because if you have to change a column it will be easier to manage the changes if things are all together)( but fenway may come by this thread and tell you a better way )

[busnut]

G'day, i've read up on normalization, although either im not understanding it or it might not be applicable to what i'm wanting to do (unsure really...)

 

I think I might try to explain this abit further (not sure if i've misunderstood what others have said or others don't quite understand what the current flatfile does).

 

What it boils down to is an audit trail of changes. It automatically does this depending on the field that is changed.

 

For instance, I have the following fields in the bus fleet database, although some fields do refer to other databases, of which i'm not sure if going to a mysql db would mean I wouldn't need them.

 

So for example, current flatfile is setout like this:

bus|chassisbody|vin|bodyno|depot|rego|active|ac|wc|fuel|notes|updated

100|m01|xxxxxx|xxxxx|A|100ABC|Y|Y|N|D||31/01/2008

 

but what it means above is bus 100, then m01 represent Mercedes Benz O500, then vin & body no fields, then depot of A represent Abbortsford, then rego then active Y is Yes, air-conditioning Y is Yes, wheelchair N is No, no notes recorded and when this record was last updated.

So say i'm to change the depot from A (abbortsford) to C (City), it changes the database and then in the history file it automatically adds notes that when it was changed and what was changed without me having to do it manually.

 

I can probably do away with the other databases which are depot' fuel, chassisbody & fleethistory and expand the info from a code to the full name, but as for the history, if I was to add another field, as it records and keeps records of all changes, it also needs to keep track of when, but in my case its month & year of change, as there is another page that views all changes per particular month & year selected.

 

Ok, i'm hoping the above makes sense, if it doesn't, feel free to PM me as I really do want to navigate away from the old flatfile db's to mysql.

[xtopolis]

I think you're getting lost on implementation.

 

What you have:

-Data, about buses.

 

What you want to do:

-Change data

-Store previous version as history for that bus

(probably in reverse order of steps)

 

Current Process:

-Update flatfile

-"notes are automatically added without you having to do it manually"

 

 

Whatever the code is for "automatically add notes when changed" should be copied (in concept) and applied to your database design / update page.  There must be something in a page that checks the current data with that history file and adds itself as the most current one for reference or something.. it's impossible to know without seeing code.

 

Regardless, things (like that) don't automatically happen unless someone makes something that makes them happen... it's just a matter of copying the code for your new design, or accomplishing the same effect with new code.

 

 

As I stated above, there are many ways to approach this, two being:

1) A single table storing bus information with a "created" timestamp that simply creates a newer entry for the same bus (whatever you use as the unique identifier for a bus, such as vin)

[Looking back on this approach, it would be inefficient since it scans data that shouldn't be  view most of the time.]

 

2) Have at least a bus table and history table.  The bus table holds 1 entry per bus, has a unique identifier (such as vin).  Whenever that record is update/changed in any way, you add the current record to the history table and update the bus record in the bus table.  Later when you want to see changes you can select records with a matching (unique identifier of bus table) and order by date.  If you just want to see what changed last and highlight it, you could select the most recent record and color the piece of data differently if 1 rows value didn't == the other rows value exactly.  (You could use whatever method you want).

 

If you feel that I am still not understanding you correctly, or you don't understand how what I say applies to you, I will be happy to code a small example of what I mean.  Something that might help you think; an OOP motto: "Code to an interface, not an implementation."  Meaning don't worry about how you're going to do it so much as what needs to be done; you can make it all come together later on.