Face-smacking errors

Yet again today, I  spent a while trying to figure out an error that had me smacking my forehead at the end.

Here was the problem, I am testing a fairly simple database – adds records, updates, selects, does some error checking as you enter the data, typical stuff. To test it, I have a small table with a few records.

I enter the value for record #1 and it retrieves the data fine and everything looks perfect.

I enter the value for record #2 and it retrieves the data for record #1 ! Obviously,  I have, in my testing, hard-coded the value for record #1, right? Wrong.

I have this:

$select_query = “SELECT * FROM clients WHERE id = ” . $id ;

I used this on the test data set previously and it was perfectly fine. I had used this for a different application and it worked fine. Finally, I tried a third record and found the error.

Here is what happened:

1. The previous application where I had used this had numeric ID values 111, 112 etc.

2. The current client has no specific requirements for an ID except that it must be unique. Some employees enter customers as 111. Others enter AAA or Bob. IDs are permanent and cannot be re-used. (Hey, this was not MY idea!)

3. If a customer is dropped for any reason and becomes inactive, then next year becomes active again, they are counted as a “new” customer, for the purpose of recording how many new customers were added this year. However, we still want to have some way of matching them with their history. So, they get a new record with -01 added to their ID. If they drop out and come back into the system again, the next time would have a -02 and so on.

It just so happened that the first record had an ID of 1123   and the second was 1124-01

Since $id was being read as a numeric variable, 1124-01 resolved to 1123 . If not for the coincidence of these two being entered consecutively, I probably would have spotted the error much quicker. When I got an error again when I entered  AAA for an id, but not when I entered 1111, the problem was obvious. I changed my code as below, and life was good again.

$select_query = “SELECT * FROM clients WHERE id =   ‘$id’ “;

Now I can put this aside for a while and go back to working on the game.