CODEWITHSUNDEEP
phpmysqlforms
WillHerndon
WillHerndon

Reputation: 23

in PHP, how do I get info from one table to pull up in a form for another

This is probably pretty basic, but I'm a beginner in PHP - I have one table for categories (cat_id is the primary key & cat_name is the only other field), and another table for posts which uses the cat_id as a foreign key.

My question is when creating a form for a user to submit a new post, how do I get the list of already created categories to appear in the form (by their category name) by way of a drop down box?

I'm assuming I'll have to select the table, assign values/variables to the data, or make it an array of some sort and then print the data in a table for the user to select?

Upvotes: 0

Views: 219

Answers (3)

theazureshadow
theazureshadow

Reputation: 10059

There is no restriction on running queries for one table when you've already run a query for another. Use a simple SELECT * FROM category, perhaps with an ORDER BY clause. I'm assuming you're using mysql or mysqli, so use a while loop to go through the results:

// run the query
$result = $mysqli->query('SELECT * FROM category ORDER BY cat_name ASC');
// initialize the html string
$select = '';
// loop through all results
while ($row = $result->fetch_array()) {
  // escape any characters that would break the html
  $row['cat_id'] = htmlspecialchars($row['cat_id']);
  $row['cat_name'] = htmlspecialchars($row['cat_name']);
  // create this item's option element and append it to the html string
  $select .= "<option value=\"$row[cat_id]\">$row[cat_name]</option>";
}
// wrap the options in a select
$select = "<select name=\"category\">$select</select>";

You'll end up with a html string $select that you can echo wherever you like, as many times as you like.

Upvotes: 2

Phil
Phil

Reputation: 164832

// PHP
$db = new PDO(...);
$stmt = $db->prepare('SELECT cat_id, cat_name FROM categories ORDER BY cat_name');
$stmt->execute();
$categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

// HTML / PHP
<select name="category">
    <? foreach ($categories as $category) : ?>
        <option value="<?= htmlspecialchars($category['cat_id']) ?>">
            <?= htmlspecialchars($category['cat_name']) ?>
        </option>
    <? endforeach ?>
</select>

Upvotes: 0

deceze
deceze

Reputation: 522210

Dropdown boxes, or <select> elements, have the following syntax:

<select name="fieldname">
    <option value="val">Name</option>
    ....
</select>

To generate this using PHP, you simply select all categories from your table, then loop through the results, printing them in the above format.

Simple example:

<?php $results = mysql_query(/* select all categories */); ?>

<select name="category_id">
    <?php while ($row = mysql_fetch_assoc($results)) : ?>
        <option value="<?php echo $row['cat_id']; ?>">
            <?php echo htmlspecialchars($row['cat_name']); ?>
        </option>
    <?php endwhile; ?>
</select>

Upvotes: 2

Related Questions