salary. what phpmyadmin type - i used float

[Jonathan_Fortis]

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

thanks

jon

[cragthehack]

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.

[Jonathan_Fortis]

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?

[cragthehack]

I don't understand your question. But if you need, you ahve have more then one decimal field.

[Jonathan_Fortis]

what i am asking is decimal the best if people are inputting not just numbers but other characters aswell?

[bregent]

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.

[Jonathan_Fortis]

i was thinking of making a menu to suggest amounts, that would make it easier

[bregent]

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.

[Jonathan_Fortis]

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?

[bregent]

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?

[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);

[bregent]

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?

[Jonathan_Fortis]

good question, what i have seen through testing is

25.000 + per annum

+ 25000 - 30000

25,000

[bregent]

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?

[Jonathan_Fortis]

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

[bregent]

>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.

[Jonathan_Fortis]

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

[Jonathan_Fortis]

we are not inputting any of the salaries, these are required salary for the candidate so we have no control.

[cragthehack]

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.

[bregent]

>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.

[Jonathan_Fortis]

ok i will look into the spry

[Jonathan_Fortis]

what spry widget is this you are talking about, is it in the standard spry toolbar?

[cragthehack]

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.

[Jonathan_Fortis]

ok looks heavy so will look in dreamweaver help