How do I use PHP and MYSQL to fill fields with in a form when the data comes from multiple tables?

Question

Im working on a project where i want the user to enter his/her username and password and if it is correct have a php script fill in fields of a table below the small login section. Is this possible? This is what i have so far and im kinda stuck any suggestions are welcome. Keep in mind i need to combine data from 2 different tables so i used the LEFT JOIN in the query.

<?php
$connect = mysql_connect("localhost","*********","******") or die ("Couldn't Connect"); //host,username,password
mysql_select_db("virtua15_j*1**") or die ("Could not find database");

$query = mysql_query("SELECT * FROM jos_users LEFT JOIN jos_comprofiler ON jos_users.id=jos_comprofiler.id WHERE jos_users.id = $id");


?>
<html>
<form action="populate.php" method='post'>
 <table>
<tr>
        <td>VAE&nbsp;Username:</td>

        <td><input type='text' name='username' value=''></td>
      </tr>
      <tr>
        <td>VAE&nbsp;Password:</td>

        <td><input type='password' name='password' value=''></td>
      </tr>
 </table>
  <p><input type='submit' name='submit' value='Search & Populate!'></p>
</form>
<hr>

<form action="dafreg.php" method='post'>
<table>
<tr>
<td>Fristname:</td>
<td><input type='text' name='firstname' value='<?php echo $firstname; ?>'></td>
</tr>
<tr><td>Lastname:</td>
<td><input type='text' name='lastname' value='<?php echo $lastname; ?>'></td>
</tr>
<tr>
<td>Login:</td>
<td><input type='text' name='login' value='<?php echo $username; ?>'></td>
</tr>
<tr><td>Password:</td>
<td><input type='text' name='pass' value=''></td>
</tr>
<tr><td>Country:</td>
<td><input type='text' name='country' value=''></td>
</tr>
<tr><td>Pilot:</td>
<td><input type='checkbox' name='pilot' value=''></td>
</tr>
<tr><td>ATC:</td>
<td><input type='checkbox' name='atc' value=''></td>
</tr>
<tr><td>Email:</td>
<td><input type='text' name='email' value=''></td>
</tr>
 </table>
 <p><input type='submit' name='submit' value='Register'></p>
 </form>





</form>


</html>

Luke Shaheen · Answer 1 · 2012-03-14T03:02:01.247

You have your query correct, although I would SELECT only the fields you need - try to avoid * (Why pull all the fields and their data when perhaps you only need 2 or 3 fields). Also, be sure to always escape your variables when doing a query - you can use mysql_real_escape_string() for this.

There are several ways you can loop through the results from your query, but I prefer mysql_fetch_assoc().

So your code would look something like this to get the first and last name (because you are joining multiple tables, you should specify the table name with each field):

$query = mysql_query("SELECT jos_users.FirstName, jos_users.LastName FROM jos_users LEFT JOIN jos_comprofiler ON jos_users.id=jos_comprofiler.id WHERE jos_users.id = " . mysql_real_escape_string($id));

if ($row = mysql_fetch_assoc($query))
{
    $firstname = $row['FirstName']; //This is whatever the field is called in your table
    $lastname = $row['LastName'];
}

Now i would enter all the fields i want from both tables after the SELECT? — David, Mar 14 '12 at 02:52
@David correct - and you may need to add the table name to each field, if that field exists in both the jos_users and jos_comprofiler. Probably should do it just to be safe. So, for example, `SELECT jos_users.FirstName, jos_users.LastName` — Luke Shaheen, Mar 14 '12 at 03:01

score 0 · Answer 2 · edited May 23 '17 at 11:48

Use POST to retrieve the user-generated values, e.g.

$username = $_POST["username"];
$password = $_POST["password"];

Then run whatever appropriate SQL query with that information. Since you're storing login information, you should read up on best practices for storing sensitive information in a database. This thread is an example of a good place to start.

Your query would look something like

$query = mysql_query("SELECT firstname, lastname, ... FROM jos_users LEFT JOIN jos_comprofiler ON jos_users.id=jos_comprofiler.id WHERE jos_users.username = " . $username);

You would then process $query to copy the appropriate values into $firstname, $lastname, etc and echo them as you did.

How do I use PHP and MYSQL to fill fields with in a form when the data comes from multiple tables?

2 Answers2