Need to insert category ID # into database... not category name... how?

[simcoweb]

I saw this somewhere but can't find it for the life of me. Here's what I have. This query works fine now and inserts the proper id's and data into the database. However, the category [b]name[/b] is getting inserted into the 'members_cat' table instead of the [b]categoryid[/b] which, in turn, would enable me to do queries for memberid's that match that categoryid.

Here's the queries:

[code]// Run query
$sql = "INSERT INTO `plateau_pros`(`username`, `password`, `confirmPass`, `firstname`, `lastname`, `email`, `url`, `business`, `title`, `address`, `city`, `zip`, `phone`, `fax`, `mobile`, `category`, `comments`, `specialties`, `photo`) VALUES('".@$_POST['username']."', '".@$_POST['password']."', '".@$_POST['confirmPass']."', '".@$_POST['firstname']."', '".@$_POST['lastname']."', '".@$_POST['email']."', '".@$_POST['url']."', '".@$_POST['business']."', '".@$_POST['title']."', '".@$_POST['address']."', '".@$_POST['city']."', '".@$_POST['zip']."', '".@$_POST['phone']."', '".@$_POST['fax']."', '".@$_POST['mobile']."', '".@$_POST['category']."', '".@$_POST['comments']."', '".@$_POST['specialties']."', '".substr(strrchr($eg_uploadFile1, "/"), 1)."')";
mysql_query($sql) or die(mysql_error());
$newid = mysql_insert_id();


$sql2 = "INSERT INTO members_cat (`memberid`, `categoryid`) VALUES ('$newid', '$catid')";
mysql_query($sql2) or die(mysql_error());
//experimental variable
$specialties = $_POST['specialties'];
//query to insert specialties
$sql3 = "INSERT INTO specialties (id, memberid, specialties) VALUES ('', '$newid', '$specialties')";
mysql_query($sql3) or die(mysql_error());[/code]

Once again, they work fine but I believe I need to somehow pass the categoryid from the form which is using a drop-down selection for the category. The values for each category selection are the names of the category.... not an ID number. This is what I have now:

[code]<td>
<select id="DropDown1" style="WIDTH: 230px" name="category">
<option value="Marketing" selected="selected">Marketing
<option value="Professional Services">Professional Services
<option value="Health and Wellness">Health &amp; Wellness
<option value="Home Design and Improvements">Home Design &amp; Improvements
</select></td>[/code]

As a result, the 2nd query is NOT inserting the category name or categoryid. That table has 3 fields: id , memberid, categoryid of which id is auto-incremented. The id and memberid are being inserted but nothing for the categoryid.

In the 'category' table there's just two fields: categoryid and category of which the latter holds the name of the category and the categoryid is autoincremented. There's currently 4 categories numbered, of course, 1 thru 4. Those are the numbers that need to go into the members_cat table.

I know this is long winded. Hope it makes sense but figured I needed to explain all aspects. Thanks.

[printf]

[code=php:0]
$sql2 = "INSERT INTO members_cat VALUES (" . $newid . ", (SELECT categoryid FROM category WHERE category = '" . mysql_real_escape_string ( $_POST['category'] ) . "'))";[/code]

You should be validating your POST data, instead of using @, it not a very smart thing to do when inserting data, very unsafe!

me!

[simcoweb]

Thanks for the post and the tip. I do plan on cleaning up the code and going to validation once I get all the queries for both insert and the select to work properly.

I inserted your code in place of what I had for sql2 and get this error:

[quote]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 'SELECT categoryid FROM category WHERE category = 'Health and We[/quote]

That's all it showed. Looks like the message was cut off somehow.

[printf]

What is your MySQL version?

You need 4.1.10 or higher to use the sub-query I posted...

me!

[simcoweb]

Ok, i'm using an older version. But, i'm updating right now to 4.1x and we'll see what happens. I have to restart some apache crap first. I'll post in a bit.

[sasa]

try to change your form
[code]
<select id="DropDown1" style="WIDTH: 230px" name="category">
<option value="1" selected="selected">Marketing
<option value="2">Professional Services
<option value="3">Health &amp; Wellness
<option value="4">Home Design &amp; Improvements
</select>
[/code]where is 1 id of category Marketing etc.

[simcoweb]

Well, that was an adventure. During the update of MySQL we had a few issues with rebuilding Apache and all the various supporting modules on the server like GD library and other important items. Anyway, we were finally successful in getting MySQL to 4.1x and the code you posted worked like a charm. All 3 INSERT queries are running perfectly now so I can run my SELECT queries to display the proper data.


[B]ALSO! An FYI for anyone reading this post. [/B]
I was receiving errors when the INSERT queries were being run. After doing some serious research to find the source of this error:
[quote]Column count doesn't match value count at row 1[/quote]

It turns out that IF you have an auto-incremented 'id' field in your tables then you need to include that in your INSERT queries as a field even though you are not inserting data into that field. This is done by using a blank set of single quotes to signify the field. Like this:

$sql3 = "INSERT INTO specialties (id, memberid, specialties) VALUES ([B][color=red]''[/color][/B], '$newid', '$specialties')";

This error kept popping up on 2 of my queries and caused a lot of delays and some grief. There may be another way to deal with this but I thought i'd pass this along to keep people from having the same issue.

Thanks for the help! :)