-
1. Re: salary. what phpmyadmin type - i used float
Stephen Cox May 2, 2012 10:34 AM (in response to Jonathan Fortis)Use decimal. See http://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html for more info.
But basically, 5,2 would get you 999.99, 5 being the total size of the field. So for salary I use 9,2. Also the presentation is not in the database but in your code. Assuming you are using PHP take a look at the number_format() function: http://php.net/manual/en/function.number-format.php.
And if you want to point and click, search the exchange for "number formats". There are probably a few extensons available.
-
2. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 2, 2012 11:57 AM (in response to Stephen Cox)thanks the the problem i have is public are added a salary required and are inputting all sorted of this like
25.000 + per annum
+ 25000 - 30000
25,000
so is decimal the best for this because with varchar is was getting a  symbol
can you suggest anything?
-
3. Re: salary. what phpmyadmin type - i used float
Stephen Cox May 2, 2012 1:36 PM (in response to Jonathan Fortis)I don't understand your question. But if you need, you ahve have more then one decimal field.
-
4. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 2, 2012 2:17 PM (in response to Stephen Cox)what i am asking is decimal the best if people are inputting not just numbers but other characters aswell?
-
5. Re: salary. what phpmyadmin type - i used float
bregent May 2, 2012 2:28 PM (in response to Jonathan Fortis)No, of course not. You can't input characters into a numeric field. If you allow users to type in freeform, it will make it very difficult to create a meaningful query. If this is for salary range, I'd go what I think Stephen was suggesting; create two columns - one for min salary and one for max.
-
6. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 2, 2012 3:09 PM (in response to Jonathan Fortis)i was thinking of making a menu to suggest amounts, that would make it easier
-
7. Re: salary. what phpmyadmin type - i used float
bregent May 2, 2012 4:28 PM (in response to Jonathan Fortis)Sure, using a menu/dropdown to standardize the input is a good idea. But I still suggest two decimal fields for min and max if you are going to have users entering search criteria to find salaries within a certain range.
-
8. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 3, 2012 2:40 AM (in response to bregent)so if i have two decimal feilds will this complicate the search and i will have to add more to my sql seach that is already built? as there will be two fields that it is searching?
-
9. Re: salary. what phpmyadmin type - i used float
bregent May 3, 2012 9:42 AM (in response to Jonathan Fortis)In my opinion this would simplify a search. How were to planning to allow a user to search for a salary range that was stored in a single column?
-
10. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 3, 2012 11:51 AM (in response to Jonathan Fortis)the search for salary was from its only field in the search page
<input name="tk_job_salary" type="text" class="textfeilds" value="Salary" size="34" />
with two other search feilds for other results
<input name="tk_job_location" type="text" class="textfeilds" value="Location" size="34" />
<input name="tk_job_title" type="text" class="textfeilds" value="Job Title" size="34" />
and the results are
mysql_select_db($database_hostprop, $hostprop);
$query_Recordset1 = sprintf("SELECT tk_job_title, tk_job_location, tk_job_salary, LEFT(tk_job_desc,200) as truncated_job_desc FROM think_jobsearch WHERE tk_job_title LIKE %s OR tk_job_location LIKE %s OR tk_job_salary LIKE %s", GetSQLValueString("%" . $var_tk_job_title_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_tk_job_location_Recordset1 . "%", "text"),GetSQLValueString("%" . $var_tk_job_salary_Recordset1 . "%", "text"));
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $hostprop) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
-
11. Re: salary. what phpmyadmin type - i used float
bregent May 3, 2012 12:12 PM (in response to Jonathan Fortis)No, I mean what type of values are you expecting to be stored in the 'salary' database column, and what type of expression are you expected a user to enter when searching?
-
12. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 3, 2012 12:43 PM (in response to bregent)good question, what i have seen through testing is
25.000 + per annum
+ 25000 - 30000
25,000
-
13. Re: salary. what phpmyadmin type - i used float
bregent May 3, 2012 1:46 PM (in response to Jonathan Fortis)Let me try again. Please answer both questions:
1) What type of values will be stored in the 'salary' database column(s)?
2) What type of expression are you expected a user to enter when searching?
-
14. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 3, 2012 2:08 PM (in response to bregent)sorry i thought i had answered that
we ask what salary they want and currently they can input anything example
25.000 + per annum
+ 25000 - 30000
25,000
so that is what people will be searching for and what will be stored
-
15. Re: salary. what phpmyadmin type - i used float
bregent May 3, 2012 3:01 PM (in response to Jonathan Fortis)>so that is what people will be searching for and what will be stored
What will be stored? You are showing 3 different examples. You would store all 3 for each job?
Forget it - That concept simply won't work. First of all you can't store "+ 25000 - 30000" in a numeric database column; It's not numeric data. If you want to store salary ranges, then you need to create two columns - one for upper and one for lower.
-
16. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 3, 2012 3:05 PM (in response to bregent)thats what im getting at, the inputs are completly random so i dont think i can use decimals because they will input anything. so thats why i suggested a dropdown to linit what goes in to the database
-
17. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 3, 2012 3:07 PM (in response to Jonathan Fortis)we are not inputting any of the salaries, these are required salary for the candidate so we have no control.
-
18. Re: salary. what phpmyadmin type - i used float
Stephen Cox May 3, 2012 7:26 PM (in response to Jonathan Fortis)If you want to do any type of calculation they'll have to be numbers. Actually you can calculate alpha numeric fields, but you won't get the results you expect.
So control this in your form. You could let your users enter what they want, and strip any non-numeric chars from the fields before processing to your table. But this is the hard way.
A better approach is to mask what they are entering. You can use Spry (javascript) within Dreamweaver for this. if you don't know anything about Javascript, this is the easy way. It's all point and click.
Take a look at Insert > Spry. Also the help in DW is pretty good. Search on Spry and read up on it.
So if you using a Spry "field" in your form (once you set the form up in the Properties Panel) your users would enter just a number; "205699" would appear (masked) as $2056.99. And Spry would only allow numbers in the $_POST[] var. Easy peay for processing.
-
19. Re: salary. what phpmyadmin type - i used float
bregent May 3, 2012 4:19 PM (in response to Jonathan Fortis)>so thats why i suggested a dropdown to linit what goes in to the database
You can use dropdowns to control the input. But most job postings list salary ranges. You can't have a dropdown for every possible salary range combination. So if you want the job listing to include salary ranges, you must use 2 columns.
When a user is searching for a job, they would enter a desired salary and your SQL query would find any job within a matching salary range.
You can also use Spry that Stephen is suggestion to validate the data and ensure it is numeric.
-
20. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 4, 2012 5:25 AM (in response to bregent)ok i will look into the spry
-
21. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 4, 2012 9:38 AM (in response to Jonathan Fortis)what spry widget is this you are talking about, is it in the standard spry toolbar?
-
22. Re: salary. what phpmyadmin type - i used float
Stephen Cox May 4, 2012 2:33 PM (in response to Jonathan Fortis)Dreamwever > Help > Spry Framework.
What i would do is create a form, with two "Spry" text fields. Using the properties panel you can set a mask and validation.
-
23. Re: salary. what phpmyadmin type - i used float
Jonathan Fortis May 4, 2012 4:19 PM (in response to Stephen Cox)ok looks heavy so will look in dreamweaver help