Update Table Problem using Web form!!

obrienkev

Hi all,

I have a mysql table called subfund. I have another one called client.

client has a primary key (client_ID) which is a foreign key in the subfund table.

So the user updates the client table and the subfund table using a form - enters name of client in the form.

The client_ID in client table will automatically increment if its a new client.

My problem is how do I update the client_ID foreign key in the subfund table???

Code so far is:

$query="insert into subfund values(NULL, $subfund, $manager, ??client_ID)";

How do I get the relevant client_ID entered here without the user having to manually enter it??

The client_ID in subfund table is equal to client_ID in client table where client='$client'

Thanks guys!!

saraadmin

This may be a bit of a long shot, but I suspect you want an HTML select element containing a list of all the available clients along with their ids in the 'subfund' form:

<select name="client_id" size="1">
<?
$clients = mysql_query("select client_id, name from clients order by name");

while ($row = mysql_fetch_assoc($result))
{
    echo("<option value=\"" . $row['client_id'] . "\">" . $row['name'] . "</option>\n");
}
?>
</select>

If you insert this code into your 'subfund' form, you'll have a list of clients for the user to select and, when the form is submitted, the value $_GET['client_id'] will be the id of the client he selected.

You can then construct your query like this:

$query="insert into subfund values(NULL, $subfund, $manager, " . $_GET['client_id'] . ")";

But I may have got the wrong end of the stick!

obrienkev

Hi sara,

The client will be inserted via text field not a select menu.

If the client is not already in the client table then that table is updated.
Also in the subfund table this new client_ID is added.

Hope this makes sense.

How is this done??

thanks.

Kevin.

saraadmin

The client will be inserted via text field not a select menu.

In what format? Just name, I assume?

If the client is not already in the client table then that table is updated.

Assuming the given client value is just a name and is in a text field called 'client_name':

$find_client = mysql_query("select client_id from clients where name like \"%" . $_GET['client_name'] . "%\" LIMIT 1");
if (mysql_num_rows($find_client) > 0)
{
    $client_id = mysql_result($find_client, 0);
}
else
{
    mysql_query("insert clients (name) values (\"" . $_GET['client_name'] . "\")");
    $client_id = mysql_result(mysql_query("select max(client_id) from clients"), 0);
}

Also in the subfund table this new client_ID is added.

$query="insert into subfund values(NULL, $subfund, $manager, $client_id)";

obrienkev

That seems to do the job!!!

Thanks ever so much sara!!

A very happy guy I am now!
🙂 😃 😃 😃 😃 😃 😃 😃 😃 😃

obrienkev

Sara,

I want to do the same thing for a number of other fields.

Do I just follow the above??