myswl feild type

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
chris_s_22
Forum Commoner
Posts: 76
Joined: Wed Dec 31, 2008 2:05 pm

myswl feild type

Post by chris_s_22 »

im trying to put numbers 01 02 03 04 05 06 07 08 09 into mysql

my feild type currently is INT but when the data is passed from form to database it is entered has 1 2 3 4 5 6 7 8 9

my question is what is the correct feild type i should be using to keep the numbers in their current form 01 02 etc etc

i must add that the feild will only ever contain numbers
User avatar
vargadanis
Forum Contributor
Posts: 158
Joined: Sun Jun 01, 2008 3:48 am
Contact:

Re: myswl feild type

Post by vargadanis »

Well an integer 01 == 1 so mysql will just store 1.
What you could do is use it as a varchar type. In that case you will have to send the query as fallows:

Code: Select all

mysql_query("INERT INTO `table` VALUES ( NULL, '".$num."' ) ");
And when you want to operate with it as an integer in PHP you could cast it back to integer:

Code: Select all

$inv_var = (int)$var;
That is my solution but that doesn't mean it's the only one and the best one.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: myswl feild type

Post by iankent »

Do you need it to be stored as '01', '02' etc or do you just need is displayed like that?

If possible, use an int column in MySQL as its (unsurprisingly) better at storing numbers than varchar is. You should instead use PHP's printf/sprintf functions to add leading 0's if necessary, e.g. to get 1 displayed as 01:

Code: Select all

printf("[%02d]", 1);
User avatar
vargadanis
Forum Contributor
Posts: 158
Joined: Sun Jun 01, 2008 3:48 am
Contact:

Re: myswl feild type

Post by vargadanis »

If possible, use an int column in MySQL as its (unsurprisingly) better at storing numbers than varchar is
Hehe, no argument there! :)
I didn't think of that solution but I am guessing that iankent's solution is a lot better than mine.
chris_s_22
Forum Commoner
Posts: 76
Joined: Wed Dec 31, 2008 2:05 pm

Re: myswl feild type

Post by chris_s_22 »

just to give you a bit more idea of what im doing

heres where i originally put data into database i put day/month/year in seperate feilds and also combine and put date of birth has a date

Code: Select all

<select name="dobday">
    <option value="01">1</option>
    <option value="31">31</option>
    </select>
    <select name="dobmonth">
    <option value="01">January</option>
    <option value="12">December</option>
    </select> 
    <select name="dobyear">
    <option value="2009">2009</option>
    <option value="1900">1900</option>
    </select>
here another form i use if i want to update data, i you can see i call for the orginal data to be stored in the value so if no change the data in database would be same

Code: Select all

<select name="dobday">
<OPTION selected value="<?php echo $dobday1?>"><?php echo $dobday1?></OPTION>
    <option value=""></option>
    <option value="01">1</option>
    <option value="31">31</option>
  </select>
    <select name="dobmonth">
<OPTION selected value="<?php echo $dobmonth2?>"><?php echo $dobmonth2?></OPTION>
    <option value=""></option>
    <option value="01">January</option>
    <option value="12">December</option>
    </select> 
    <select name="dobyear">
<OPTION selected value="<?php echo $dobyear3?>"><?php echo $dobyear3?></OPTION>
    <option value=""></option>
    <option value="2009">2009</option>
    <option value="1900">1900</option>
    </select>
However when i try the form to edit it works if i completly change the day and month but if i make no changes. the data if 01-09 being sent isnt right.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: myswl feild type

Post by iankent »

I think its because your original options are 01, 02 etc. and mysql will be outputting 1, 2, etc. so when you do make a change its submitting 01 or 02 and if you don't make a change its just submitting 1 or 2

You can either just change all of <option> values to single digits so everything (mysql and html) are working in the same format, or you can use the printf command I gave you earlier around your echo to give it the correct format, e.g.:

Code: Select all

echo printf('[%02d]', $dobmonth2)
as a side-note, you don't need to do <? php echo $abc ?> to output a variable, you can use the shorthand which is <?=$abc?> - a lot neater and a lot less typing :)
chris_s_22
Forum Commoner
Posts: 76
Joined: Wed Dec 31, 2008 2:05 pm

Re: myswl feild type

Post by chris_s_22 »

think i use <?php out of habbit but thx for the tip

anyways ive tried changing all forms so number values are same 1 2 etc

the problem im having when i use this method

Code: Select all

$dob = $dobyear.$dobmonth.$dobday;
then store data as a date i get the value in mysql as 0000-00-00

it needs the value 0 on the single numbers for it to enter properly.

has for your other option
echo printf('[%02d]', $dobmonth2)

can you explain exactly what this is doing just i like to understand code if im going to try it.
User avatar
iankent
Forum Contributor
Posts: 333
Joined: Mon Nov 16, 2009 4:23 pm
Location: Wales, United Kingdom

Re: myswl feild type

Post by iankent »

this should work

Code: Select all

 
$dob = $dobyear . sprintf('[%02d]', $dobmonth) . sprintf('[%02d]', $dobday);
 
assuming $dobyear is always going to be 4 digits

printf is a function that prints a formatted string, i.e. printf('format', 'input text'). there's no need to use echo with it as thats what it does by itself.

sprintf does exactly the same, but instead of echoing it, the result is returned, e.g. $text = sprintf('format', 'input text').

the format '[%02d]' is (hopefully) telling sprintf/printf to make sure the string is at least 2 characters, and if its shorter, add leading 0's. that's not a great explanation and PHP's manual does a better job :)
chris_s_22
Forum Commoner
Posts: 76
Joined: Wed Dec 31, 2008 2:05 pm

Re: myswl feild type

Post by chris_s_22 »

thanks i will certainly give it a try thx for your help
Post Reply