Lost in translation: Something changing a value mid-query! 1

[LTeeple]

Hi all,
I've encountered a problem I've never seen before. I am stumped! Any advice is appreciated. First off, here is my table description:

+-----------------------+--------------+------+-----+
| Field                 | Type         | Null | Key |
+-----------------------+--------------+------+-----+
| invoice_id            | int(10)      |      | PRI |
| fname                 | varchar(32)  |      |     |
| mname                 | varchar(32)  | YES  |     |
| lname                 | varchar(32)  |      |     |
| email                 | varchar(64)  |      |     |
| ip_address            | varchar(16)  |      |     |
| transaction_date_time | datetime     |      |     |
| original_ticket_qty   | int(2)       |      |     |
| current_ticket_qty    | int(2)       |      |     |
| original_total        | int(7)       |      |     |
| current_total         | int(7)       |      |     |
| paid                  | tinyint(4)   | YES  |     |
| company               | tinytext     | YES  |     |
| other_queens_program  | tinytext     | YES  |     |
| program               | tinytext     | YES  |     |
| year                  | int(4)       | YES  |     |
| special               | text         | YES  |     |
| phone                 | varchar(32)  | YES  |     |
| method                | tinyint(4)   | YES  |     |
| notes                 | varchar(255) | YES  |     |
+-----------------------+--------------+------+-----+

Next, here is my insert sql code:

$query = "insert into invoices values (
  $this->invoice_id,
  '$inv_fname',
  '$inv_mname',
  '$inv_lname',
  '$inv_email',
  '$this->ip_address',
  '$this->transaction_date',
  $this->ticket_qty,
  $this->ticket_qty,
  $this->total,
  $this->total,
  0,
  '$inv_company',
  '$inv_other',
  '$inv_program',
  $inv_year,
  '$inv_special',
  '$inv_phone',
  $inv_method,
  '$inv_notes'
)";

Next, as I execute the query, for debugging, I echo out the sql statement to see all the values. This is the result of echoing the above sql statement:

insert into invoices values insert into invoices values ( 9999483648, 'john',blah','doe','johndoe@whatever.ca','111.111.111.111','',1,1,1000,1000,0,'','','MBA',2004,'','',0, '3b03f02c7fc02047942ce0247ef5a571')

The first number, the invoice number is what I want it to be. Its good. But when I check my table, there is an anomaly. Everything appears perfectly except for the invoice number. See what it's giving me below:

select invoice_id from invoices;
+------------+
| invoice_id |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

Could someone please help? I'd like to know where my error is, and what caused it, if possible. I've never seen this before!

Thanks for all your help folks!


Cheers!
Laura

 

[sleipnir214]

It's probably not in your PHP code. It's the limit of your column type.

Your invoice_id column is defined as INT, which according to the MySQL online manual has a range of -2147483648 to 2147483647. Your value 9999483648 exceeds that limit.

One workaround is to go with BIGINT UNSIGNED for that column. That'll give you up to 18446744073709551615.

However, I think you should rethink your table. Is this invoice ID an actual number, or is it a numeric string? The test I always use is to ask the question, "Will you be performing mathematical operations on this value?" If the answer is "No", you could store the value as a string.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LTeeple]

As always sleipnir214, your reply is quick and most informative!

I do use mathematical operations on the value, so I will follow your advice and alter my table.

That test you use - "Will you be performing mathematical operations on this value?" I will always keep in mind.

All the best!


Cheers!
Laura

 

[LTeeple]

I just altered my table as sleipnir214 suggested, and everything is working smoothly now.

Thanks again! This forum is the best resource I know, thanks to people like sleipnir214.


Cheers!
Laura

 

[dkdude]

Laura,

As sleipnir214 points out -the INT type limit the number (32 bit signed) where as BIGINT UNSIGNED (64 bit) will hold you invoice number with all the digits in your example.

Here's my two cents:

I too would consider make it a string. However, if you have a huge number of inivoices (or a large number of users simultaneously working on your database) BIGINT UNSIGNED may (?) speed up things compared to a string type var. Not sure though -just a thought...

Best Regards


Jakob

 

[sleipnir214]

I developed that question after running into problems in a database.

A place where I once worked used an in-house-developed database to track project billable time, and every entry required status code of the form "digit decimal digit" (7.1, 4.3, etc). The problem was that the person who developed the database was using a floating-point column to store those values, and some of the status codes, because of inaccuracies involving IEEE floating-point numbers, would not be reported correctly when later recalled.

Want the best answers? Ask the best questions!

TANSTAAFL!!