CODEWITHSUNDEEP
phpmysqlweb-applications
Corey
Corey

Reputation: 826

How would I generate an HTML form based on table data from two different tables using PHP?

I am trying to write a PHP script that will create an HTML form using the “field_definitions” table as a source for input fields and set the default values of each field based on previously stored results from the “user_data” table. (based on the currently logged in user… I have an auth system setup and running using session variables.)

My tables are like so...

users TABLE
user_id user_email       user_firstname user_lastname user_password
1       [email protected] John           Doe           password

field_definitions TABLE
field_id field_type field_length field_name field_desc                   field_section
1        text       40           color      What is your favorite color? 1

user_data TABLE
response_id user_id field_id user_response
1           1       1        Blue

I’ve created a block of code that returns the rows from my “field_definitions” table and stores them in an array. I’m able to use a foreach loop to generate the form based on each row in the array, but I can’t figure out how to pull-in the second set of information from the other table for the default values.

$dbc = mysqli_connect(sqlhost, sqluser, sqlsecret, sqldb);

$field_definitions_query = "SELECT * FROM field_definitions WHERE field_section = '1'";

$field_definitions_data = mysqli_query($dbc, $field_definitions_query);

$field_definitions = array();

while ($row = mysqli_fetch_array($field_definitions_data)) {
  array_push($field_definitions, $row);
}
echo '<form enctype="multipart/form-data" method="post" action="' . $_SERVER['PHP_SELF'] . '">';

foreach ($field_definitions as $row) {
 echo '<label for="' . $row[field_name] . '">' . $row[field_desc] . ':</label>';
 echo '<input type="' . $row[field_type] . '" id="' . $row[field_name] . '" name="' . $row[field_name]. '" value="temp" /><br />';
}

echo '<input type="submit" value="Save" name="submit" /></form>';

Do I need to create a second array of the other table data, then merge the two in someway?

Is my general approach feasible, or is there a better way?

I am a beginner in PHP and programming in general and would rather not try and conquer any of the frameworks out there; I’m trying to pull this off with my own code for the sake of learning.

Upvotes: 1

Views: 1479

Answers (1)

well-wisher
well-wisher

Reputation: 74

Consider using SQL JOIN construction. In case of MySQL it would be something like this: select t1.*, t2.user_response from field_definitions t1 left join user_data t2 using (field_id) where t2.user_id = %USER_ID% or t2.user_id is null. Then you can fetch data row-by-row to a single array/hash/whatever. See your SQL manual for more details on JOINs.

In short. We are joining tables t1 and t2 by field x. N is NULL (non-existent) record. Ljn is LEFT JOIN, Rjn is RIGHT JOIN, Ijn is INNER JOIN (INNER keyword is frequently ommited) and Fjn is FULL JOIN

t1.x: 1 2 3 4 5 N N

t2.x: N N N 4 5 6 7

Ljn : ^_______^----

Rjn : -----^______^

Ijn : -----^__^----

Fjn : ^___________^

You can check if record is non-existing one by t1.x IS NULL or t1.x IS NOT NULL in where clause.

Hope it will help.

Upvotes: 1

Related Questions