[thelist] ASP & HTML: Pass double data from a Select

Casey Crookston casey at thecrookstons.com
Wed Jul 16 07:35:36 CDT 2003


Rudy said:

> don't pass the number, just pass the value
>
>       <option value="apples">apples</option>
>       <option value="peaches">peaches</option>
>
> and depending on how you do your insert into the target table, you may
> not even need a separate lookup, you might be able to do it with a simple
> join

Okay, this makes a lot of sense.  But I may need some help building an
INSERT and UPDATE statement with a join.  Let's pretend the tables look like
this

table users:
+--------+------+-------------+
 | user_id | name | favorite_fruit |
+--------+------+-------------+
 |   1        | Jane  |        2           |
+--------+------+-------------+

table fruit:
+--------+-----------+
 | fruit_id |  fruit_name |
+--------+---==-----+
 |   1        |  Apple       |
+--------+-----------+
 |   2        |  Peach       |
+--------+-----------+

Now let's say we want to update Jane because she has decided that she likes
apples better then peaches. In the HTML forms we have not passed "1" along,
but rather "Apple".

"UPDATE users SET favorite_fruit = ???? WHERE user_id = 1"

I assume that the ???? will be the join?  I'm no SQL jockey.... I could
easily do this by first doing a seperate select:

fruitPK = "SELECT fruit_id FROM fruit WHERE fruit_name = "Apple""

And then make the Update look like this:

"UPDATE users SET favorite_fruit = "&fruitPK&" WHERE user_id = 1"

But the entire purpose here is to do this w/o two trips to the db, right?
So it boils down to this: what is the syntax to build the join?

TIA,

Casey




More information about the thelist mailing list