help with a small functionality

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

help with a small functionality

Post by mikosiko »

Hi y'all
This is my very first visit here, so i just want to say hi to everyone, and thanks to anyone who could help me with an issue.

Let me say that I'm really new to PHP/MYSQL so please forgive me if my questions are too basic... but everyone started in some place right? :)

I do have a table in Mysql, with several records and each record with some fields that need to be calculated... the tricky part (at least for my current knowledge) is that each calculated field depend of some values in the next record.

I know how to get the records with a select and return a recordset and access it row by row... but while I'm working in a row... how I can get the values from the next row to be able to make the calculations for the current row?..... sorry... maybe is too basic... but as I said... I'm just learning....
could someone give me an example showing how to do this?

thank you...
Miko
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: help with a small functionality

Post by requinix »

My first instinct is that your table needs to be redesigned a little: data in one row should not depend upon data in another row.

Can you be more specific about what you're doing? Some example data and what you want done with it?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: help with a small functionality

Post by mikosiko »

tasairis wrote:My first instinct is that your table needs to be redesigned a little: data in one row should not depend upon data in another row.

Can you be more specific about what you're doing? Some example data and what you want done with it?
Below is a very simplified explanation about what need to be done...
Problem (Simplified):
- Truck company use driver's team (3 or more) for each trip.
- Each Trip's route is composed by 2 or more "legs", being a "leg" a city pair. p.e
* Trip 1 route is LAX-ATL-LAX (round trip)... this trip has 2 "legs,
- Leg 1 LAX-ATL
- Leg 2 ATL-LAX
- For each leg an unique record is recorded in a master table (Trips) ... p.e
Trips (
TrKey, (AUTO)
TrId,
TrDate,
TrOrigin,
TrDestin,
TrTimeOut,
TrTimeIn,
<Other Fields...>
)

Detail Table:... relation 1,n

Driver_Trip (
DtKey, (AUTO)
TrKey,
DrKey, (Driver Id)
<Several calculated fields>
)

Tricky part (simplified also):
- Drivers who start in a trip no always finish the trip and return to base in the same truck, and several of his trip's time payments are based on semi-complicated calculations (business rules) between his associated trip's times to return to base + dead times between trips.

Objective: Be able to calculate monthly payments (several items associated to different concepts and conditions) for each driver depending on his trip's history..

At this point seems to me that is not possible normalize or de-normalize the Data Model any more to simplify the problem and be able to apply the business rules in the calculation process... that is why I need to be able to access the next record for a driver while I'm calculating some fields in the current record... is that functionality possible?

thank you
Miko
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: help with a small functionality

Post by califdon »

No, it is not functionally possible as a relational database, with the data schema that you describe. As tasairis said, calculated and dependent data cannot be stored that way in a relational database. Consult any basic tutorial on relational database theory. Can it be done? Absolutely, but not with that data schema.

This may strike you as theoretical, but your case is a perfect example of why the design of a relational database should always start with identifying the entities and their dependent attributes, meaning that every non-key field in a record must be entirely dependent on the primary key value and on nothing else. You may protest that it simply doesn't, for your application. That's because you haven't isolated the real entities. I would have to know a lot more about the details of your operation, and spend much more time than I can spare, to determine exactly how to reorganize your data, but that is precisely what must be done to design a data schema that conforms to the Codd Relational Model, upon which SQL is based.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: help with a small functionality

Post by mikosiko »

califdon wrote:No, it is not functionally possible as a relational database, with the data schema that you describe. As tasairis said, calculated and dependent data cannot be stored that way in a relational database. Consult any basic tutorial on relational database theory. Can it be done? Absolutely, but not with that data schema.

This may strike you as theoretical, but your case is a perfect example of why the design of a relational database should always start with identifying the entities and their dependent attributes, meaning that every non-key field in a record must be entirely dependent on the primary key value and on nothing else. You may protest that it simply doesn't, for your application. That's because you haven't isolated the real entities. I would have to know a lot more about the details of your operation, and spend much more time than I can spare, to determine exactly how to reorganize your data, but that is precisely what must be done to design a data schema that conforms to the Codd Relational Model, upon which SQL is based.
... Well... seems that I didn't explain everything properly and I gave wrong information... hence your conclusions... appreciate your comments anyway ... after many years working with Databases as a Data Modeller I think that I know well CRM.. but always I can learn more for sure... :D

I did solve the problem... only thing necessary to do was process my recordset backwards or populate it selecting my records in reverse order... after that calculations were easy.
Thank you
Miko
Post Reply