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 [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:30 AM To: Rankin, Randy; [EMAIL PROTECTED] 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...
Re: [PHP-DB] Help with MySQL Logic
Heh. Sounds like a programming class homework project. I would say through the clever use of where clauses's, like: UPDATE position SET posistion = (position-1) WHERE position $idremoved; Would work, assuming $idremoved containted the position of the car removed. On Mon, 2003-03-03 at 10:59, Rankin, Randy wrote: Hello All, This may be a bit off topic. If so, my apololgies. 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. I hope I explained thourougly. Any suggestions would be appreciated. Randy -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
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... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with MySQL Logic
What about two tables, one would have this: carID carCompany carPayload etc... the second this: position carID This table would be static and if a car was not present in certain conditions they would contain (-1) for the carID. Then finding an empty slot to place an incoming car should be doable (stepping through to find (-1), and when a car is removed from a slot, then you could update the list, just dump out the 'current' order into an array, and then you would be able to sort, order, etc... Hope this makes sense. -Brad Heh. Sounds like a programming class homework project. I would say through the clever use of where clauses's, like: UPDATE position SET posistion = (position-1) WHERE position $idremoved; Would work, assuming $idremoved containted the position of the car removed. On Mon, 2003-03-03 at 10:59, Rankin, Randy wrote: Hello All, This may be a bit off topic. If so, my apololgies. 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. I hope I explained thourougly. Any suggestions would be appreciated. Randy -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Help with MySQL Logic
Coming from a non-programmer... If you were to take the cars' IDs and put them in an array named for the track a specific group of cars is on, that would give you indexes, thus positions, from 0-N depending on how many cars are there. Any time you have to add or delete cars, you should be able to delete or add the ID from or to the array and the indexing would automatically tell you what position all of the cars are in. You should be able to move cars (IDs) around in the array with PHP too. So you'd have: $track1 = array(2,4,6,8); //where 2,4,6,8 are the IDs of rail cars And you'd use the serialize and unserialize functions to get the data into and out of MySQL and PHP to manipulate the order of the IDs inside the array. Like I said, I'm a non-programmer, but a track with cars on it sounds pretty similar to a real-world representation of an array structure to me. I'm sure there will be myriad other ways to get at this problem with their own good and bad points. I agree with John though, sounds like a fun project. Rich -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 11:25 AM To: Rankin, Randy Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Help with MySQL Logic Heh. Sounds like a programming class homework project. I would say through the clever use of where clauses's, like: UPDATE position SET posistion = (position-1) WHERE position $idremoved; Would work, assuming $idremoved containted the position of the car removed. On Mon, 2003-03-03 at 10:59, Rankin, Randy wrote: Hello All, This may be a bit off topic. If so, my apololgies. 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. I hope I explained thourougly. Any suggestions would be appreciated. Randy -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc
