leading zeros and MySQL

frog

I want to save a php variable recording the month and year in the format mmyy in a field on a mysql database. However, when I use the data type varchar mysql removes leading zeros so that 0103 becomes 103. What data type do I need to use in order to keep the leading zeros?

Good morning to every one from the sunny(?) coast of southern England

laserlight

hmm... VARCHAR should be fine.

Maybe you are entering it as an integer instead, either from PHP or in the query?

Enter it as a string.

frog

I know this lazy and I should probably read the manual but how do I specify the php variable as a string?

frog

I still can't get my head round this.

function create_table($mmyy) {

			 // create a new archive table with all the entries from blog_entry for a
			 //certain month
			 $table_name = "blog_entry".$mmyy;

			 $query = "CREATE  TABLE  `imhotep`.`$table_name` (  `blog_entry_key` mediumint( 8  ) unsigned zerofill NOT  NULL auto_increment,  `blog_scribble` longtext NOT  NULL ,  `blog_timestamp` timestamp( 14  )  NOT  NULL ,  `mmyy` varchar( 4  )  NOT  NULL default  '',  PRIMARY  KEY (  `blog_entry_key`  ) ,  KEY  `blog_timestamp` (  `blog_timestamp`  )  ) TYPE  =  MYISAM ";   

			 $result = (mysql_query($query));

			 if(!$result) error_message(sql_error());

			 $query ="INSERT  INTO  `imhotep`.`$table_name`
			 					SELECT  *  FROM  `imhotep`.`blog_entry`
								WHERE mmyy = $mmyy";

			 $result = (mysql_query($query));

			 if(!$result) error_message(sql_error());

			 //create entry for the month in table blog_archive_id

			 $query = "INSERT into imhotep.blog_archive_id
			 					 VALUES (NULL,
								 				 $mmyy)";

			 $result = (mysql_query($query));

			 if(!$result) error_message(sql_error());

}

As you can see I format a table name "blog_entry".$mmyy At this stage $mmyy retains the leading zero e.g 0503. However, by the time I use it to insert into the table blog_archive_id it has lost the leading zero and becomes 503. Is the previous query doing this?
Sorry that my php code is not highlighted but I only seem to be able to paste about 30 characters at a time into the code highlighter alert box - another problem - sigh.

frog

Fixed it. This is the culprit.

$query = "INSERT into imhotep.blog_archive_id
VALUES (NULL,
$mmyy)";

I need single quotes around $mmyy

I.E.

$query = "INSERT into imhotep.blog_archive_id
VALUES (NULL,
' $mmyy')";

Smiling again

🙂

Sxooter

To answer your previous question of how to force a var to be a certain type, we present, the manual:

http://www.php.net/manual/en/function.settype.php

frog

Thanks dude, the manual is a wonderful thing and to be recommended for bedtime reading.

laserlight

well, good for you 🙂

in this case it was the database that was treating the variable as an integer, not PHP.

Norman_Graham

Originally posted by Sxooter
To answer your previous question of how to force a var to be a certain type, we present, the manual:

http://www.php.net/manual/en/function.settype.php

Now that's a really elegant and polite way of saying RTFM!!!

Norm 😃

Sxooter

Well, ya know, if the PHP manual sucked horribly, I could understand not wanting to read it.

But that page is 3862 bytes long. How long could it possibly take to read and comprehend it?

I don't mind teaching people to fish, hell, I'll even put a worm on your hook for ya, but I'm not gonna sit there all day holding your fishing pole while you go lay in the sun.

frog

Just to put the record straight, I refer to the manual regularly and have the chm version on my kit at home. In this case I was being a bit lazy and admitted that in my post. I don't really think I deserved RTFM

Sxooter

but it wasn't really an rtfm. just a reference to the manual. I DID look it up for ya and post a link, so I wasn't really trying to be a dick or anything. Heck, I had a hard time finding stuff in that part of the docs when I started. Someone else said it was an elegant way of saying RTFM, but it really wasn't just that. If it had been that, there would have been no link.

No hard feelings...

P.s. if you're as lazy as you say you are (which is likely to still be less lazy than me, engineers are lazy by nature) then you might wanna grab ahold of the latest htdig 3.2 beta snapshot and setup all your docs on a local apache server and use htdig to index them. It really makes lookups fast. I just looked it up on my box, then use the ending part of the URL to post the link to www.php.net.

frog

Yeah no hard feelings mate, I overreacted a bit...end of a long day. Thanks also for advice re htdig 3.2 beta snapshot, I may well do that.🙂