RE: [PHP-DB] Help with MySQL Logic

Thanks to everyone for your help. I was able to work out the logic last night based on your fine suggestions and all is well! Thanks again. Randy > -----Original Message----- > From: "1LT John W. Holmes" <holmes072000@charter.net> > Sent: Monday, March 03, 2003 10:30 AM > To: Rankin, Randy; php-db@lists.php.net > Subject: Re: [PHP-DB] Help with MySQL Logic > > > A client of mine, a rail car storage company, has asked that I create a > > PHP/MySQL application in which they will maintain and track rail cars. I > am > > having a bit of trouble however working through one of thier > requirements. > > They need to know in what position the rail car is on each track. For > > example, they might have a track called X which will hold 30 rail cars. > They > > would enter the car information for 30 cars and associate each of them > with > > track X. If one of the car owners decides to move car 3 out of storage, > then > > the remaining 29 cars must be re-numbered ( ie; car 1 and 2 would remain > and > > car 4-30 would become car 3-29 ). In the same manner, I need to be able > to > > add a car to the track once an empty slot is available. For example, If > the > > new car goes at the front of the track then it would become car 1 and > the > > remaining cars, originally numbered 1-29 would become 2-30. > > Not sure if this helps or if you already realize this... > > Say you have a table that identifies the track_id and car_id. Now, you > delete the car_id for car #3, like you've said. So, that row is deleted > and > you've got a hole now. You can run a query such as: > > UPDATE table SET car_id = car_id - 1 WHERE car_id BETWEEN 4 AND 30 AND > track_id = XX > > to adjust all of the other car_id numbers. > > Now, say you want to add a new car to the beginning. > > UPDATE table SET car_id = car_id + 1 WHERE track_id = XX > > and then insert your new car at position #1. > > Throw in some checks to make sure you don't go over 30 cars and you should > have it. You can get a count of how many cars are on a certain track with: > > SELECT COUNT(*) AS c FROM table WHERE track_id = XX > > Hope that helps. It sounds like a fun project. > > ---John Holmes... >