Auto fill fields in dependence of selection

Scriptcase 8 Discussion
#1

Hi,

again a problem that was discussed many times in the forum but I can’t make it work.

I have a table users and a table reports. In a form (where I fill out the report) I’d like to show some basic data in the report which is in the users table e.g. name, address of the report writer. I made a selection field and lookup to select the name of the report writer. After selecting the name I’d like to auto fill the basic data concerning to the name from the users table.

I made an ajax event onchange that belongs to the name selection field in the form and put in something like this

sc_lookup(rs, "SELECT user_city FROM user WHERE id = {user_city}");

    {user_city} = {rs[0][0]};

In the form field i put the initial value {user_city}

When generating the form I get the “error mysql_fetch_array() expects parameter 1 to be resource, boolean given” and no auto fill works.

Any help is appreciated.

BTW: Can someone explain the “Fields to be passed as parameters” when do I need this?

Joe

#2

No one any idea?

#3

try:
sc_lookup(rs, “SELECT user_city FROM user WHERE id = ‘{user_city}’”);

#4

Hi,

no more error message but the destination field does not auto fill.

Is it correct to put in the fields initial value {user_city} or ‘{user_city}’ ?
If I put nothing in the form field I get no error.

SQL output says (pdo-mysql): SELECT user_city FROM user WHERE user_id = ‘’ (if I try only WHERE id= … sql says unknown column id)

Joe

#5

sc_lookup is a scriptcase function. As in it is not really a function as with php. You should view the source code and see what scriptcase makes of it and you will notice thet {user_city} is in the source code replace by some this->… value.
From the help file:
Ex. 3: The SQL command also can be composed of application fields (local variables) or of global variables:
sc_lookup(dataset, “select order_value from orders where clienteid = '{customer_id} ’ and salesman_id = [var_glo_salesman]”);
Your field is probably some text field and thus it should be surrounded with quotes.
As experiment do the following:
$s="SELECT user_city FROM user WHERE id = ‘{user_city}’ ";
echo $s;

You’ll see why the quotes are needed (be aware of double and single quotes!!).
The field has a ajax processing somewhere, you need to set the destination field there so that it gets updated when you change the value of the field.

#6

[QUOTE=rr;35716]sc_lookup is a scriptcase function. As in it is not really a function as with php. You should view the source code and see what scriptcase makes of it and you will notice thet {user_city} is in the source code replace by some this->… value.
From the help file:
Ex. 3: The SQL command also can be composed of application fields (local variables) or of global variables:
sc_lookup(dataset, “select order_value from orders where clienteid = '{customer_id} ’ and salesman_id = [var_glo_salesman]”);
Your field is probably some text field and thus it should be surrounded with quotes.
As experiment do the following:
$s="SELECT user_city FROM user WHERE id = ‘{user_city}’ ";
echo $s;

You’ll see why the quotes are needed (be aware of double and single quotes!!).
The field has a ajax processing somewhere, you need to set the destination field there so that it gets updated when you change the value of the field.[/QUOTE]
Added to this

What value has usercity? Do. Manual query on you database and look if return results

#7

Hi friends,

I’m trying for days now to get that work correct. The auto fill ajax function works now but whatever user name I select, the auto fill brings the same data for every user.

sc_lookup(rs, "SELECT user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =".'user_id');

      {user_plz} = {rs[0][0]};
      {user_ort} = {rs[0][1]};
{user_strasse} = {rs[0][2]};
   {user_email} = {rs[0][3]};

How can I do it to show the correct data for the selected user? Thanks for your kind help:o

Joe

EDIT:

Also tried this code - user_id is always ‘0’

 sc_lookup(rs, "SELECT user_id, user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =".'user_id');

    {user_id} = {rs[0][0]};
    {user_plz} = {rs[0][2]};
    {user_ort} = {rs[0][1]};
{user_strasse} = {rs[0][3]};
  {user_email} = {rs[0][4]};

… and still getting the error message
“mysql_fetch_array() expects parameter 1 to be resource, boolean given | Script: /var/www/scriptcase80/prod/third/adodb/drivers/adodb-mysql.inc.php linha: 701”

#8

OK - I got it now.

$userid = {user_id};

sc_lookup(rs, "SELECT user_id, user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =". $userid);

    {user_plz} = {rs[0][2]};
    {user_ort} = {rs[0][1]};
{user_strasse} = {rs[0][3]};
  {user_email} = {rs[0][4]};

but still getting the error message. What’s wrong?

#9

sc_lookup(rs, “SELECT user_id, user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =’”. $userid . ‘’");
Be aware that it says user_id = ’ (so a single quote) " (double quote) . space $userid space " (double quote) ’ (single quote) : double quote

Anyway let me make it clear:
$userid is a php variable
[userid] is a global variable
{userid} is a field on your form

sc_lookup works kind of like a macro so:
“SELECT user_id, user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =”. $userid gets written out in the source code
say for example the php variable $userid has the string value “John Doe”
then your code gets written out as:
“SELECT user_id, user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =”. John Doe
Which thus is wrong.
Surrounding it with quotes:
“SELECT user_id, user_ort, user_plz, user_strasse, user_email FROM tkapp_user WHERE user_id =”. ‘John Doe’

And to make it more confusing read this:
https://php.net/language.types.string
That is the whole php string thingy with the quotes explanations.

#10

Thank you rr for your help.

The last code I posted works fine. The error message came from the lookup field and had nothing to do with the ajax auto fill.

Can be marked as SOLVED

Joe

#11

A new problem regarding the ajax auto fill:

In my user table (which fields I want to auto fill) I have some data fields with lookup data to a third table.

Has someone a code snippet with sc_lookup to make that work?

Example: In form my selction is a user name from user table -> that should auto fill the state field where the user is registered -> the state data is stored in a seperate table (with state_id where I do a lookup) as the user table.

I hope my explanation is clear enough.

Joe