Filling <select> tags with SQL data for a form

wicked_chicken

I need help figuring out how to take data from a table in MYSQL database and use the items under a field called 'category' to form the <OPTION>for a <SELECT> in a <FORM>.

What I'm trying to do is have a code snippet that I can add to the ADD and EDIT pages that will allow me to designate a particular article's entry into a certain category. Example: table - news, category - local news, other news, etc.

I will also want to add a link at the end of this select box in the form to allow the user to add new categories through a seperate form but I'm not too worried about that.

The variable I use for the table is $tab and I have ID, category, heading, etc. fields.

How can I do this cleanly? Any help is appreciated.

Wicked Chicken

dustbuster

Try something like this. You will want to ensure the quotation marks are done properly as I haven't tested this exact code. I know it works as I have recently done it.

//Open and connect to your database then do the following
$query="select * from '$tab'";
$result=mysql_query($query);

echo "<select name=\"category\">"
while($row = mysql_fetch_array($result)){
        //Should write
        //<option value="Category">Category</option>
             echo "<option value=\"".$row['category']."\">".$row['category']."</option>"."\n";
}
echo "</select>";

Hope it helps
D.

wicked_chicken

Thanks, that worked well after a few modifications.

How would I ensure that double entries do not show up? (e.g. 2 or more articles are categorized as Other News but I only want to list 1 unique category as an option insteaf of duplicates.)

Also, any ideas on what kind of code I would use to create a separate table with categories that my main NEWS table could reference for values instead of relying on each article to have a category? That way I could just have the update link to a simple add.php file for that particular table and the categories of articles in NEWS table would come then from this category table?

Thanks again. The final code with slight modifications is listed below - any advice would be great.

<FORM method="POST" action="<? echo $PHP_SELF; ?>">
<?
$query="select * from $tab";
$result=mysql_query($query);

echo "<select name=\"category\" class=\"input\">";
echo "<option value=\"#\">Choose a category ...</option>";
echo "<option value=\"#\">Add a new category</option>";
while($row = mysql_fetch_array($result))
{
echo "<option value=\"".$row[category]."\">".$row[category]."</option>"."\n";
}

echo "</select>";
?>
<INPUT type="submit" name="mybutton" class="button" value="Submit">
</form>

dustbuster

Create a table categories

create table categories{
id int NOT NULL AUTO_INCREMENT,
PRIMARY KEY id,
category varchar(50) UNIQUE
}

$tab='categories';

This should allow you to have unique categories.

For an article what you currently have in category can be changed to the ID in categories.

Eg:
In Categories table:
1 | Other News

Articles Table:
In the category column use:
1
instead of using:
Other News

Thora_Fan

How would I ensure that double entries do not show up? (e.g. 2 or more articles are categorized as Other News but I only want to list 1 unique category as an option insteaf of duplicates.)

In your SQL, you could try

$query="select * from $tab GROUP BY catagory";

"catagory" being whichever field holds the actual catagory.

wicked_chicken

Ok, I understand, 1 table for categories with id and category fields, 1 table for news with reference to id number in categories table.

How do I select data from 2 tables at once (NEWS and CATEGORIES and corelate or update them simultaneously?

Thanks.

😕

Thora_Fan

Frankly, and this is just my technique, I would only bother with one table. For instance, have something like:

CREATE TABLE myTable (
id NOT NULL AUTO_INCREMENT DEFAULT '0' PRIMARY KEY,
catagory ENUM('Cat1', 'Cat2', 'Cat3') NOT NULL,
...
...
(other fields go here)
...
...
}

Each news post would have it's catagory in the post field.

wicked_chicken

If you use that technique, doesn't that mean that to add a new category without an attached article would mess up the automated display newest scripts on news summary pages?

I want the ADD article form to be able to let the user choose all current categories from those of the already posted articles and have the ability to add a category in that <select> menu, which would likely involve a link to another ADD category page to access the other table. Then, when you return to the ADD article page, you can now select the new category from the form which is filled in by a query of the category table.

How can I do this cleanly?

Thanks.

Wicked Chicken

😕 😕

dustbuster

If you want it to be clean I suggest putting a link that says "Add a Category" and to ADD a new category just redirect the user to an add_category.php script as well.

You mentioned selecting data from 2 tables.
"select * from table1, table2"
Check out http://www.mysql.com/doc/en/JOIN.html for more ways to join tables.

You can put the id from the categories table in the value of the <select> statement if it helps.