i have used float 6,2 for a salary but it doesnt show the correct figure,
if the applicant inputs 25,000 i need it to come out like that or 120,000
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.
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
so is decimal the best for this because with varchar is was getting a Â symbol
can you suggest anything?
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.
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.
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
$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);
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?
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
so that is what people will be searching for and what will be stored
>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.
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
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.
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.
>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.