PHP / SQL - 使用Select元素显示/链接到数据库中的行

Question

I'm building a CRUD app with PHP, and I need some help trying to simplify the user experience. Basically, the user creates a new "Project" with a pre-assigned ID number. This ID number acts as the primary key. Upon submission of the form, a row is created in a "Projects" table in my database.

After the project is created, I want to use another form to add storage transactions to the project (for contextual purposes, I'm tracking warehouse storage). Initially, was going to have the user re-add the ID number (the primary key entered when the project was created), but there are lots of opportunities for human error if the ID number is entered incorrectly.

Current Task: I want to create a "Select" element on my form that lists all ID numbers that have been entered. Then, I can just select one of the projects from that drop down and have the transaction automatically applied. How can I do this?

Thank you in advance for your help!

Answer 1

First, you'll have to do a query (using PHP) to pull a list of all the project records in the project table from that database. When you do the query for that, you'll have a result set as an array of each project in the projects table that you can iterate through using a loop to populate a drop-down box.

First get the projects (assuming a column name of id that you're using for the drop-down selection):

<php $projects = mysqli_query($db, "SELECT id FROM PROJECTS_TABLE"); ?>

Then, to display the drop down selection box:

<form name="formWhatever">
...
<select name="project_id">
<?php foreach ($projects as $project) { 
      $result = mysqli_fetch_array($projects, MYSQLI_ASSOC)?>
  <option value="<?php echo $result[id]; ?>">Project #<?php echo $result[id]; ?></option>
<?php } ?>
</select>
...
</form

I think I also like @pajaja's idea for using an index page to eliminate a long drop-down box as well.