Re: Odd MySQL Problem...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jason Pruim wrote:

> Hey everyone,
> 
> Have a weird issue that I can't seem to figure out...
> 
> Using PHP to insert phone numbers into the site... I have the Area Code, &
> Exchange, and then dynamically create the last 4 digits... Once it's
> inserted it's dropping the leading zero's... Here's some samples:
> 
> Copied from phpMySQL:
> 
> 
> Edit   Inline Edit     Copy    Delete 212     200     0       6800001
> Edit   Inline Edit     Copy    Delete 212     200     1       6800002
> Edit   Inline Edit     Copy    Delete 212     200     2       6800003
> Edit   Inline Edit     Copy    Delete 212     200     3       6800004
> Edit   Inline Edit     Copy    Delete 212     200     4
> 
> 
> echoed output from PHP:
> 
> SQL: INSERT INTO phonesite (areacode, exchange, subscriber) VALUES(212,
> 200, 0001) SQL: INSERT INTO phonesite (areacode, exchange, subscriber)
> VALUES(212, 200, 0002)
> 
> 
> Actual PHP code:
> 
> <?PHP
> //    ini_set('display_errors', 1);
> //error_log(-1);
> set_time_limit(0);
> 
> //set_time_limit("120");
> include "includes.php";
> include "databaseabstraction.php";
> include "authentication.php";
> 
> dbconnect("localhost", "XXXXXX", "XXXXXX", "XXXXXX")or die("Unable to
> connect: " . mysql_error());
> 
> 
> function number_pad($number,$n) {
> return str_pad((int) $number,$n,"0",STR_PAD_LEFT);
> }
> 
> if (($handle = fopen("newyorktest.csv", "r")) !== FALSE) {
>     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
> print_r($data);
> 
>         foreach( range(0, 9999) AS $n) {
>         //echo "<BR>DATA: " . $data[2] . "<BR>";
>              $padded_number = number_pad($n, "4");
>              $sql = "INSERT INTO phonesite (areacode, exchange,
>              subscriber) "; $sql .= "VALUES({$data[1]}, {$data[2]},
>              {$padded_number}) "; //mysql_query($sql) or die("Didn't
>              insert you dumb ass.... FIX IT NOW CLOWN!!!! " .
>              mysql_error());
> echo "SQL: " . $sql . "<BR>";
> 
>              }
> 
>     }
>     echo "File Bitches!";
>     fclose($handle);
> }
> 
> 
> 
> ?>
> 
> 
> Any ideas on what I'm missing?
> 
> Thanks everyone!
> 
> Jason Pruim
> lists@xxxxxxxxxxxxxxxxxxxx
SQL: INSERT INTO phonesite (areacode, exchange, subscriber) VALUES(212, 200,
0001) 
SQL: INSERT INTO phonesite (areacode, exchange, subscriber) VALUES(212, 200,
0002) 

Note the lack of single quotes on the third value; mysql will treat those as
integers - especially if subscriber is an integer type :-)

If you absolutely have to store the leading zeros you'll need to store them
in a char type and quote them when INSERTing or UPDATEing 



Cheers
-- 
David Robley

<-------- The information went data way --------
Today is Boomtime, the 68th day of Bureaucracy in the YOLD 3177. 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux