Restricting access to table data from a control application

Applications Control
#1

I am currently evaluating scriptcase and I need to build a time stamp system so our employees could enter their working time every day.
So far, I built a control application which verifies both the name and password in the appropriate table. I also managed to direct one user to the form where his time is recorded.
My problem is that I need to restrict access for each employee to only have access to his own data.

So far I wrote an onValidate envent :
$usr = {nom};
$pwd = {nip};
$sql = “SELECT
nom,
nip
FROM
personnes
WHERE
(nom = '”.$usr."’) AND
(nip = ‘".$pwd."’)";
sc_lookup(ds, $sql);
if (isset({ds[0][0]}))
{
sc_redir(form_temps);
}
else
{
echo “erreur nom ou nip”;
}

Then I added a Where condition : no_employe in the form_temps, but that does not restrict access so I don’t think this is the right way to do it.
I am hoping someone could help me with this. Also, since we have 65 employees, I’d rather not have to do a table for each employee…
As I specified, I am new to scriptcase and still fairly new to MySQL. Also, as you could guess, I am not a native english speaker, so do not hesitate to ask if anything is unclear.

#2

You need a WHERE-clause (and table) with Employe_ID. That’s all … just standard sql. You find a fine MySQL tutorial here: Click!

#3

Thank you for your answer, but I don’t see where to use the WHERE clause.
The way I see it, I would need the WHERE clause to be in the form_temps (form sc_redir(form_temps) ) but is there a way to have it to refer to the usr entered in the control app?
Something like creating a link between the data entered from the control app and the table it redirect to…
I know I can filter the data of the table with a WHERE but do I need to enter 65 different WHERE clauses, one for each employee?
Being new to the databases world, I’m kinda lost here…

#4

Just retrieve the user_id (employee id) along with user_name and user_password and send it as a global variable to your form.
If you don’t have an id field, create one on all tables involved. You’ll need it, more than once.

SELECT nom, nip, user_id
FROM personnes
WHERE (nom = ‘".$usr."’) AND (nip = ‘".$pwd."’)";

In your redirect:
sc_redir(form_temps,v_user={user_id},"_self");

In from_temps (WHERE section)
user_id = [v_user]

jsb

#5

Thank you, your help is greatly appreciated.

I tried it and I think it is really close this time.

$usr = {nom};
$pwd = {nip};
$sql = “SELECT no, nom, nip
FROM
personnes
WHERE
(nom = '”.$usr."’) AND
(nip = ‘".$pwd."’)";
sc_lookup(ds, $sql);
if (isset({ds[0][0]}))
{
sc_redir(form_temps_1,v_user={no},"_self");
}
else
{
echo “erreur nom ou nip”;
}
When I log on via the control app, it returns this message:

Error
Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1

Since I don’t see " at line 1, and am not yet familiar with MySQL script, I just can’t figure what the syntax error is?

The column named no is the employee id, which is primary key and auto increment. But the employee id column in the table referred to in form_temps is named no_employe so I think it might be causing a problem…
I tried sc_redir(form_temps_1,v_user={no_employe},"_self"); but it returned the same error.

#6

Make yourself familiar with the use of variables in SC and MySQL.
You don’t have to get fancy here.

Put the field {no} on your control form (you can mark it as a hidden field in the Field Attributes if you want).

Change your SQL command to:
$sql = “SELECT no, nom, nip
FROM personnes
WHERE nom = ‘{nom}’ AND nip = ‘{nip}’”; // I suppose nom and nip are of type string

Your redirection should look like:
sc_redir(form_temps_1,v_user={no},"_self")

In your temps form, your WHERE needs to be:
no_employe = [v_user]

That’s it

jsb

#7

I tried what is mentioned above but I still have a little problem with this application.
I started again from scratch and the problem I am experiencing now is that the application does not return a ‘no’ from the select request.
I tried with sc_alert({no}) and it always return no 0, no matter which nom and nip are used… the no 0 does not even exist in the database.
Since it returns 0 as no, the redir does not work as expected.
What I am trying to achieve is to redirect to a form where the no (employee id) is predefined by the control app and where it cannot be modified by the end user and everything else in the form is blank. So I typed sc_apl_conf(“form_temps”, “start”, “new”); in onApplicationInit in the form. I hope it is not interfering with my goal?
Everything else seems to be working perfectly.
Could someone help me, please?