Jump to content


Photo

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


  • Please log in to reply
6 replies to this topic

#1 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 29 September 2006 - 08:36 PM

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 name is getting inserted into the 'members_cat' table instead of the categoryid which, in turn, would enable me to do queries for memberid's that match that categoryid.

Here's the queries:

// 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());

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:

<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>

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.

#2 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 29 September 2006 - 09:20 PM

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

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

me!

#3 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 29 September 2006 - 09:34 PM

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:

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


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

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 29 September 2006 - 09:41 PM

What is your MySQL version?

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

me!

#5 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 29 September 2006 - 09:59 PM

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.

#6 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 29 September 2006 - 10:23 PM

try to change your form
<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>
where is 1 id of category Marketing etc.

#7 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 30 September 2006 - 04:55 PM

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.


ALSO! An FYI for anyone reading this post.
I was receiving errors when the INSERT queries were being run. After doing some serious research to find the source of this error:

Column count doesn't match value count at row 1


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 ('', '$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! :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users