Display selected information from field

[busnut]

I'm trying to display only selected information from a field according to the date range inputed by the user.

 

At the moment, the fields display the bus, the busmake and the history of that bus which shows all the history for that bus of which below is the history field of a particular given bus:

Jan 1988: Delivered to Light St \n??? 1988: Transferred from Light St to Toowong \n??? 1996: Rego changed - was 169AAW \nFeb 2007: Transferred from Toowong to Carina \nFeb 2009: Withdrawn from Carina \n

 

What I am trying to achieve is that when the user wants to view changes made in Feb 2007, it will just display only the text relevant to their search, ie:

history: Feb 2007: Transferred from Toowong to Carina

 

So as you can see, it displays only information of that date range (and note the history file incorporate the date (mmm yyyy) and the change from the concat function when added).

 

Is this possible, and if so, how? So far, I can either get it to display all the history or none Help will be greatly appreciated...

[Mchl]

Where does this data come from?

Storing the dates together with event descriptions is basically shooting yourself into a foot. It makes simple tasks tricky.

[busnut]

The data comes from a mysql database, although another member pointed out having another table which i've also been experimenting with, and whilst it works, I am having difficulty in getting it to input multiple changes for the one bus:

 

This is what i've got so far and if there is only 1 field being changed, it does what it suppose to do, but any more, than it displays all in one line:

 

$changed=date(M) ." " . date(Y);
if($history<>"") {
$query = "INSERT INTO bushistory (id, busno, chassisbody, type, changed, history)
VALUES ('$id', '$busno', '$chassisbody', '$type', '$changed', '$history')"; }

the $history refers to several fields that record comments (whilst several others dont record comments). The below code refers to what will get recorded:

if ($active1=="N" && $active=="Y") { $history1 = date(M) ." " . date(Y) .": Delivered to $depot\n"; $type="Deliveries"; }
if ($active1=="Y" && $active=="N") { $history1 = date(M) ." " . date(Y) .": Withdrawn from $depot\n"; $depot="Withdrawn"; $withdrawn=date(M)." ".date(Y); $type="Withdrawals"; }
if ($active1=="Y" && $active=="Y" && $depot1 <> $depot) { $history1 = date(M) ." " . date(Y) .": Transferred from $depot1 to $depot\n"; $type="Transfers"; }
if ($active1=="Y" && $rego1 <> $rego && $rego1<>"") { $history3 = date(M) ." " . date(Y) .": Rego changed from $rego1 to $rego\n"; $type="Registration Changes"; }
if ($active1=="Y" && strlen($aoa1)>2 && $aoa=="") { $history4 = date(M) ." " . date(Y) .": AOA removed\n"; $type="Advertising Changes"; }
if ($active1=="Y" && $aoa1<>$aoa && $aoa<>"") { $history4 = date(M) ." " . date(Y) .": AOA for $aoa\n"; $type="Advertising Changes"; }
if ($active1=="Y" && $ac1=="N" && $ac=="Y") { $history5 = date(M) ." " . date(Y) .": Air-Conditioning Installed\n"; $type="Other Changes"; }
if ($active1=="Y" && $ac1=="Y" && $ac=="N") { $history5 = date(M) ." " . date(Y) .": Air-Conditioning Removed\n"; $type="Other Changes"; }
if ($busno1 <> $busno) { $history6 = date(M) ." " . date(Y) .": Bus renumbered renumbered from $busno1 to $busno\n"; $type="Other Changes"; }
if ($notes<>"") { $noted = date(M) ." ". date(Y) .": $notes\n"; $type="Other"; }

 

Sorry if the above code is abit messy. I realise the above code refers to the first database where it records the history using the concat feature to keep recording the history (explans the \n on the lines), so how then can I get the first code to post each change as a seperate record. I've tried the foreach loop, and failed as it would show the change type, but not the information of why it was changed.

 

If you require the full code, i'm happy to post it. Once i've got this completed, then I can go to work on authorisation and then finally the end user pages.

[Mchl]

Perhaps you should post the table structure for both tables, then we could analyze it and see if it could be improved, so that it's easier to use.

[busnut]

when you say table structure, you mean the code or the phpmyadmin bit of what each field is? If it is the code, then attached is the code to update a bus:

 

$id = $_POST['id'];
$busno = $_POST['busno'];
$chassisbody = $_POST['chassisbody'];
$vin = $_POST['vin'];
$bodyno = $_POST['bodyno'];
$delivered = $_POST['delivered'];
$withdrawn = $_POST['withdrawn'];
$depot = $_POST['depot'];
$rego = $_POST['rego'];
$active = $_POST['active'];
$ac = $_POST['ac'];
$wc = $_POST['wc'];
$aoa = $_POST['aoa'];
$history = $_POST['history'];
$notes = $_POST['notes'];

if ($active1=="N" && $active=="Y") { $history1 = date(M) ." " . date(Y) .": Delivered to $depot\n"; $type="Deliveries"; }
if ($active1=="Y" && $active=="N") { $history1 = date(M) ." " . date(Y) .": Withdrawn from $depot\n"; $depot="Withdrawn"; $withdrawn=date(M)." ".date(Y); $type="Withdrawals"; }
if ($active1=="Y" && $active=="Y" && $depot1 <> $depot) { $history1 = date(M) ." " . date(Y) .": Transferred from $depot1 to $depot\n"; $type="Transfers"; }
if ($active1=="Y" && $rego1 <> $rego && $rego1<>"") { $history3 = date(M) ." " . date(Y) .": Rego changed from $rego1 to $rego\n"; $type="Registration Changes"; }
if ($active1=="Y" && strlen($aoa1)>2 && $aoa=="") { $history4 = date(M) ." " . date(Y) .": AOA removed\n"; $type="Advertising Changes"; }
if ($active1=="Y" && $aoa1<>$aoa && $aoa<>"") { $history4 = date(M) ." " . date(Y) .": AOA for $aoa\n"; $type="Advertising Changes"; }
if ($active1=="Y" && $ac1=="N" && $ac=="Y") { $history5 = date(M) ." " . date(Y) .": Air-Conditioning Installed\n"; $type="Other Changes"; }
if ($active1=="Y" && $ac1=="Y" && $ac=="N") { $history5 = date(M) ." " . date(Y) .": Air-Conditioning Removed\n"; $type="Other Changes"; }
if ($busno1 <> $busno) { $history6 = date(M) ." " . date(Y) .": Bus renumbered renumbered from $busno1 to $busno\n"; $type="Other Changes"; }
if ($notes<>"") { $noted = date(M) ." ". date(Y) .": $notes\n"; $type="Other"; }

$changed=date(M) ." " . date(Y);

$history = $history1 ."". $history2 ."". $history3 ."". $history4 ."". $history5 . "" . $history6 . "" . $noted;

$update = "UPDATE busfleet SET id = '$id', busno = '$busno', chassisbody = '$chassisbody', vin = '$vin', bodyno = '$bodyno', delivered = '$delivered', withdrawn = '$withdrawn', depot = '$depot', rego = '$rego', active = '$active', ac = '$ac', wc = '$wc', aoa = '$aoa', history = CONCAT(history,'$history') WHERE id='$id' ";

if($history<>"") {
$query = "INSERT INTO bushistory (id, busno, chassisbody, type, changed, history)
VALUES ('$id', '$busno', '$chassisbody', '$type', '$changed', '$history')"; }


$rsUpdate = mysql_query($update);
if ($rsUpdate)

{
echo "Bus $busno updated successfully in fleet database";
echo "<body onload='top.searching.document.location.reload();'>";
} 

$results = mysql_query($query); 
if ($results)

{
echo "<p>Bus $busno updated successfully in history database";
echo "<body onload='top.searching.document.location.reload();'>";
} 

mysql_close();

 

ok, just to give some idea as I know my coding isn't that neat, the body onload refers to refreshing another frame once the bus has been edited.

Currently the code above still does show where it records history to the busfleet table which works brilliantly, but as you and another board member mentioned, it will be hard to displayed searched difinitive history results (and also slows the system down), but also is the code to add to the bushistory table.

 

in plain english minus the code, this is how the system works out if it is to add a history note.

if the bus is made active (N to Y) and no matter what other fields are changed, then it will put onto the history file 'delivered to bla'

if the bus is made inactive (Y to N) and no matter what other fields are changed, then it will put onto the history file 'withdrawn from bla' and also make the 'withdrawn' field the date (mmm yyyy) on the busfleet table

if the bus has a rego change whilst being active, then it will put onto the history file 'rego chagned from bla to bla'

if the bus is transferred from one depot to another whilst being active, then it will put onto the history file 'transferred from bla to bla'

if notes are added (notes isn't a field in the tables, but is a field on the edit page to add a history note), then it will put onto the history file what the user types

if the bus gets advertising applied whilst the bus is active, then it will put onto the history file 'AOA for bla', but if the info in the field is deleted, then it will put onto the history file 'AOA removed'

 

Sorry for the long winded post, just thought I would try to explain how it works... (and yes, I still have alot to learn)

[Mchl]

I meant MySQL's CREATE TABLE statement, that is used to create the table. That's usually most meaningful way of showing how table look like You can get this from PhpMyAdmin using 'Export' feature, and selecting 'Structure' only (uncheck 'Data' checkbox)

[busnut]

I meant MySQL's CREATE TABLE statement, that is used to create the table. That's usually most meaningful way of showing how table look like You can get this from PhpMyAdmin using 'Export' feature, and selecting 'Structure' only (uncheck 'Data' checkbox)

Yep, no probs, no the export feature all too well for when i've made a blunder in scripting and its changed every record by accident:

 

CREATE TABLE IF NOT EXISTS `busfleet` (
  `id` int(5) NOT NULL auto_increment,
  `busno` varchar(5) NOT NULL default '',
  `chassisbody` varchar(255) NOT NULL default '',
  `vin` varchar(100) NOT NULL default '',
  `bodyno` varchar(100) NOT NULL default '',
  `delivered` text NOT NULL,
  `withdrawn` varchar( NOT NULL default '',
  `depot` varchar(100) NOT NULL default '',
  `rego` varchar(50) NOT NULL default '',
  `active` char(3) NOT NULL default '',
  `ac` char(3) NOT NULL default '',
  `wc` char(3) NOT NULL default '',
  `fuel` varchar(50) NOT NULL default '',
  `aoa` varchar(255) NOT NULL default '',
  `history` text NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1966 ;

-- --------------------------------------------------------

--
-- Table structure for table `bushistory`
--

CREATE TABLE IF NOT EXISTS `bushistory` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `busno` varchar(5) NOT NULL default '',
  `chassisbody` varchar(255) NOT NULL default '',
  `changed` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `history` text NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1965 ;

 

also to note, there are 1964 different buses (same have the same number, but different make as they were in service many years ago), and also the existing bus history database which is still in flatfile, has some 5500 records which is why I was being put off in the first place of using a second table, but if it makes things easier in the long run, then no problemo

[Mchl]

Would you mind explaining what data is stored in the fields? chassisbody, vin, bodyno are self explanatory OC, but columns like 'delivered', 'withdrawn', 'changed' etc... are not.

[busnut]

Would you mind explaining what data is stored in the fields? chassisbody, vin, bodyno are self explanatory OC, but columns like 'delivered', 'withdrawn', 'changed' etc... are not.

ah sorry, forgot that bit, here it is (all of it with example):

 

busfleet table:

 

busno: the number identified, although the same number may have been used in the past, no other active bus will have this same number

chassisbody: MAN SL200 [Denning] (make, model and body supplier)

vin: vehicle identification number

bodyno: body number from teh body supplier, but not all buses have one

delivered: australian date format of dd/mm/yyyy

withdrawn: month & year the bus was withdrawn mmm yyyy

depot: where the bus is allocated - but if the bus is not active, it will either be 'to be built' or 'withdrawn'

rego: registration plate number

active: either Y or N

ac: airconditioned either Y or N

wc: wheelchair access either Y or N

aoa: all over advertising - if the bus has a full body wrap, then it will be listed otherwise the field is left blank

history: this is where i was putting the info of certain changes

 

in the bushistory table:

busno: as above

chassisbody: as above

type: the type of change, ie Delivery, Withdrawal, Rego Change, Transfer, Other, Advertising

changed: when the change occurred, ie mmm yyyy

history: each instance of a change (in the busfleet table, it all was recorded, in this table, each instance must be in its own record for easier sorting/finding)

 

hope the info helps. Thanks so far, most appreciated.

[Mchl]

Well... first thing: never (I mean NEVER) store dates as strings. Use DATE datatype. This makes all date based queries a lot easier. The only drawback is that you have to learn how to change date formats between mysql's format and your preffered one. Given it's not difficult at all, I would strongly recommend that.

 

The 'two-table' approach is correct, but you have to do some redesigning still.

 

Consider this:

each table should store data about one class of 'object'

'bus' is one type of object

'event from bus' history' is another type of object

 

So far so good: we have two tables

'busfleet' for buses

'bushistory' for events

 

Another thing: each table shoul have a way to unambiguously identify each record - so called 'primary key'. In MySQL most common practice is to create an 'ID' INT AUTO_INCREMENT column. This key is only used by application. Users should not see it or care about it.

 

Our tables

busfleet
ID, busno, vin, ....

bushistory
ID, changed, history

 

Now, the final thing. Relating data from one table to another. This is done using so called 'foreign keys'. Foreign key, is a column that indicates what data from another table is associated with data in this table. In our case every 'event' fro 'bushistory' is associated with one (and only one) 'bus' from 'busfleet'. How do we identify a 'bus'? Using its ID!

 

 

Our tables (with some data)

busfleet
ID, busno, vin, ....
1, '123', '123456'
2, '123', '653277'

bushistory
ID, busID, changed, history
1, 1, '1950-03-23', 'delivered'
2, 1, '1975-02-21', 'withdrawn'
3, 2, '1975-03-01, 'delivered'
4, 2, '1985-12-24, 'crashed'

 

How do you like this?

[busnut]

Ah foriegn key, that had been mentioned to me before, but didn't quite understand it, but your detailed information outlines it a fair bit more. Somehow I presume in the writing to the bushistory table, we make the busid field = the id field of the busfleet table.

As for the date, I have came across that issue before, and thought (which obviously was wrong) was to make it simplier and use it as a string so I can manipulate it in my own preferred format.

 

But with using the correct date format, and this is more so for the 'delivered' date, in another script once completed, I plan to display the average age of vehicles, so will having the date in the sql format still work to get the average age of a bus fleet?

 

So I presume before going much further with getting the scripts to do what I prefer, is to change (or more so make new tables) with the correct format. But what about the withdrawn date as there is no one particular day, just a month and year (as the precise date is very rarely known)?

[Mchl]

Yes, you should do your best to design the database properly, before you get into scripting the application that will use it. Will save you a lot of time later on.

 

Using dedicated DATE format for storing dates allows you for easy calculation of ages (and their averages). Much easier than if they were stored in strings.

There is no problem with day being unknown, you can use ALLOW_INVALID_DATES mode in SQL to be able to insert dates like 1982-04-00 when the day is unknown.

 

Read more here about dates in MySQL

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

 

Somehow I presume in the writing to the bushistory table, we make the busid field = the id field of the busfleet table.

 

That's it!