myswl feild type
Moderator: General Moderators
-
chris_s_22
- Forum Commoner
- Posts: 76
- Joined: Wed Dec 31, 2008 2:05 pm
myswl feild type
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
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
- vargadanis
- Forum Contributor
- Posts: 158
- Joined: Sun Jun 01, 2008 3:48 am
- Contact:
Re: myswl feild type
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:
And when you want to operate with it as an integer in PHP you could cast it back to integer:
That is my solution but that doesn't mean it's the only one and the best one.
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."' ) ");Code: Select all
$inv_var = (int)$var;- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: myswl feild type
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:
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);- vargadanis
- Forum Contributor
- Posts: 158
- Joined: Sun Jun 01, 2008 3:48 am
- Contact:
Re: myswl feild type
Hehe, no argument there!If possible, use an int column in MySQL as its (unsurprisingly) better at storing numbers than varchar is
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
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
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
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.
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>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>- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: myswl feild type
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.:
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 
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)-
chris_s_22
- Forum Commoner
- Posts: 76
- Joined: Wed Dec 31, 2008 2:05 pm
Re: myswl feild type
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
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.
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;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.
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: myswl feild type
this should work
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
Code: Select all
$dob = $dobyear . sprintf('[%02d]', $dobmonth) . sprintf('[%02d]', $dobday);
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
thanks i will certainly give it a try thx for your help