1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 5:56 pm Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Trying to access a table in my database with a drop-down menu

         

solace

7:18 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



I'm trying to write a form to enter "news" or blog updates. I want to be able to have a drop-down list with all the categories, but for the purposes of keeping things consistent, I was going to have a seperate table for categories (with an id #, cat. name, and cat. description). This way, I can also have an archive page listed by category. How can I write a "new entry" form with a drop-down list that uses the categories from the category table (and, should I ever add a new category, have it automatically update the drop-down list)?

This is the code I have right now: 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Testing Writing to Database Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php
// includes
include("config.php");
// connection to server and selection of database
$connect = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_errno().": ".mysql_error()."<br 
/>Unable to connect to server.");
$db = mysql_select_db($db_name,$connect) or die (mysql_errno().": ".mysql_error()."<br />Unable to 
select database.");
?>
<form action="newentry.php" method="post">
Title: <input type="text" name="entry_title" /><br /><br />
Entry: <br /><textarea rows="30" cols="60"></textarea><br /><br />
Category: <!-- HEREIN LIES THE PROBLEM --><br /><br />
Allow Comments? <input type="checkbox" name="entry_comments" /><br />
</form>
<?php
mysql_close($connect);
?>
</body>
</html>

Any brilliant ideas?

Thanks!

sned

7:33 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



I use something like this quite a bit:

<?
$catQ = "SELECT * FROM Category";
$cat = mysql_query($catQ);

?>

Category:
<select name="category" etc. >
<?
while($row = mysql_fetch_object($cat)){
echo "<option value=\"", $cat->CategoryID,"\">", $cat->CategoryName, "</option>";
}
?>
</select>

solace

7:55 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



Okay. That sort of makes sense. I'm relatively new to this whole thing, but that does make sense. Except this part: 


while($row = mysql_fetch_object($cat)){ 
echo "<option value=\"", $cat->ID,"\">", $cat->Name, "</option>";

Now, I understand while statements. I get that whole thing. But as far as the $cat->ID thing goes...I'm not sure exactly how that works. My main table is called "blog_entries", the column is called "category". The second table is called "blog_categories", and the columns are "category_id", "category", and "description". So what values do I put in that option statement to make the drop down?

On top of all this silliness, I'm not sure how to make the "category" column in the "blog_entries" table reference the "category" column in the "blog_categories" table. Does that make any sense? Do I even need to make them reference each other, if the drop-down box pulls the categories straight out of the "blog_categories" table? I'm such a n00b.

I really appreciate the help.

sned

8:12 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



oops, I messed up in my own code :/ .. How embarrassing .. $cat->ID & $cat->Name should be $row->ID & $row->Name

Anyway -

while($row = mysql_fetch_object($cat)) - This just gets the results from the query, and goes through them row by row.

$row->ID & $row->Name -- get the column value for that particular row

So in your case, that particular line would be:

echo "<option value=\"", $row->category_id,"\">", $row->category, "</option>";

In html code, this would like like:
<option value="1">Cool Stuff</option>
<option value="2">More stuff</option>
...

As far as linking the two together, what does your blog_entries table have for columns?

solace

8:24 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



My blog_entries table has "entry_id" (an auto-increment), "entry_date" (an auto-timestamp), "entry_title" (VARCHAR(255) to be entered in this form I'm working on), "entry_text" (full entry, TEXT type to also be entered in this form), "category", and "entry_comments" (an ENUM('yes','no') field with a checkbox to decide if you want comments to be available for this post or not).

First of all, I haven't even started THINKING about the comments program (*is afraid, is very afraid*), but besides that, the rest of those elements are either auto-entered or should be on this form.

sned

9:23 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



Ok, when you process your form inputs, it sounds like you just need to take the value chosen from your select list, and use that in the insert statement - insert it into the category column.

solace

9:35 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



Right. That makes sense. But does that mean I really need to do anything to actually "link" my tables together? If I'm taking the data from "blog_categories" and literally inserting it into a record in "blog_entries", then I can just search by that column in "blog_entries", and "blog_categories" is just where I keep everything together.

Okay, I think that makes sense (at least to me *lol*).

Thank you very very much for your help. I appreciate it.

solace

9:35 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



Bwahahahaha! And the code you gave me works. So that rules.

sned

9:38 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



No, you don't really need to do anything to "link" the two together, unless for whatever reason, you wanted to do a cascade delete - (delete a category, and have all comments about that category deleted with it) - then you would need foreign keys and relationships defined and such.

Good luck :)

-sned

solace

10:58 pm on Jun 7, 2004 (gmt 0)

10+ Year Member



Eep! Yeah, I'll come back to that when I cross that bridge. *laugh* Thanks very much for all your help, you've been grand.

solace

4:33 am on Jun 16, 2004 (gmt 0)

10+ Year Member



Alright...I don't know what the heck I did...but I screwed it up again somehow... 


<?php
// includes and requires
require('config.php');
// set the globals
$entry_title = $_POST[entry_title];
$entry_text = $_POST[entry_text];
$entry_category = $_POST[entry_category];
$entry_mood = $_POST[entry_mood];
$entry_music = $_POST[entry_music];
$entry_month = $_POST[entry_month];
$entry_date = $_POST[entry_date];
$entry_year = $_POST[entry_year];
$entry_timeofday = $_POST[entry_timeofday];
// set the pre-inserted timestamp parts
$current_month = date("F");
$current_date = date("d");
$current_year = date("Y");
$current_time = date("H:i");
// process the form
if($submit) {
// convert the timestamp
$entry_time = strtotime("$entry_month $entry_date $entry_year $entry_timeofday");
// add line breaks
$entry_text = nl2br($entry_text);
// connect to the database host and select the database
mysql_connect($db_host,$db_user,$db_pass) or die("Unable to connect to the host.<br />" . mysql_errno() . ": " . mysql_error() . "<br /><br />\n");
mysql_select_db($db_name) or die("Unable to select the database.<br />" . mysql_errno() . ": " . mysql_error() . "<br /><br />\n");
// add values
$addentry = "INSERT INTO bloggart_entries (entry_time,entry_title,entry_text,entry_category,entry_mood,entry_music) VALUES ('$entry_time','$entry_title','$entry_text','$entry_category','$entry_mood','$entry_music')";
$result = mysql_query($addentry) or print ("Unable to add entry to the table 'bloggart_entries' in the database.<br />" . mysql_errno() . ": " . mysql_error() . "<br /><br />");
mysql_close();
}
?>
<form method="post" action="<?php echo $PHP_SELF?>">
<p>Month:&nbsp;
<select name="entry_month">
<option value="<?php echo $current_month?>"><?php echo $current_month?></option>
<option value="January">January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="May">May</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>
</select>&nbsp;&nbsp;
Date:&nbsp;<input type="text" name="entry_date" size="2" value="<?php echo $current_date?>" />&nbsp;&nbsp;
Year:&nbsp;<input type="text" name="entry_year" size="4" value="<?php echo $current_year?>" />&nbsp;&nbsp;
Time:&nbsp;<input type="text" name="entry_timeofday" size="5" value="<?php echo $current_time?>" /><br /><br />
Title:&nbsp;<input type="text" name="entry_title" size="40" /><br /><br />
Entry:<br /><textarea cols="80" rows="20" name="entry_text"></textarea><br /><br />
<?php
$catQ = "SELECT * FROM bloggart_categories";
$cat = mysql_query($catQ);
?>
Category:
<select name="entry_category">
<?php
while($row = mysql_fetch_object($cat)){
echo "<option value=\"", $row->category_id,"\">", $row->category_title, "</option>";
}
?>
</select>
<br /><br /> 
Mood:&nbsp;<input type="text" name="entry_mood" size="40" /><br /><br />
Music:&nbsp;<input type="text" name="entry_music" size="40" /><br /><br />
<input type="submit" name="submit" value="Post" />
</form>

The new names for the bloggart_categories table (renamed, obviously) are category_id, category_title, and category_description...I have no idea what I did wrong...it worked before.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 5:56 pm Apr 28, 2026