Personally, just run a very simple quick query:
<?php
/*
* This is assuming that you still
* have the variables passed through
* via post method or variables.
*
* If not, then use my comments as a guide
*/
$sql = "SELECT id FROM `crew` WHERE name = '".$name."' LIMIT 1";
// This retrieves ONLY 1 row from the
// database. The variable $name refers
// to some data that has been passed
// through (like a full name, first name,
// last name, nick name, username). You
// can change the code as you see fit.
// So the query takes this form:
// SELECT [column] FROM [table] WHERE
// [column] = [variable/value] LIMIT
// [integer]
$result = mysql_query($sql);
/*
* If the following code does not give you
* the ID, then use the code after my
* comments of "Alternate Way"
*/
$id = $result;
/*
* Alternate Way
*/
$item = mysql_fetch_array($result);
$id = $item['id'];
//=\\=//=\\=//=\\=//=\\=//=\\=//=\\
/*
* Now you will need to execute another
* query on the database to add this
* row, and insert the ID and any other
* basic data that you want.
*
* It is suggested that you allow this
* table to have an auto_increment id #
* and another column named crew_id.
* This way, you could check if crew_id is
* equal to the id field in the crew table.
*/
$selsql = "INSERT INTO `fields` (id) VALUES ('".$id."')";
$selres = mysql_query($selsql);
/*
* From this point on, you will only be
* UPDATE(ing) the row. You will not
* INSERT another row for this person.
*/
?>
So say you have your table in the following setup:
crew
id, name, username, password, email
fields
id, crew_id, date, field, extra info
Well, now you want to display a list of where user Joe (crew ID of 1) has worked. You want this in order from most recent to oldest. You also do not want to show his ID number, but rather his name. You would create a join on the tables, and display the information:
<?php
/*
* Basic Left Join query
*/
$sql = "SELECT * FROM `crew`, `fields` LEFT JOIN `crew` ON crew.id = fields.crew_id WHERE fields.crew_id = '".$id.'" ORDER BY fields.id DESC";
$result = mysql_query($sql);
/*
* This selects all the fields from
* both tables, then joins them on the
* different ID fields (id and crew_id).
* It then pulls the records WHERE the
* crew_id equals the ID number you
* want to view (retrieved through a
* posted variable), and orders in
* descending value by the value of the
* ID in the fields table.
*/
echo '<table border="1">';
while($disp = mysql_fetch_array($result)){
/*
* While there are values in the array
* This will loop through until the
* array becomes empty
*/
echo '<tr>'.
'<td>'.$disp['name'].'</td>'.
'<td>'.$disp['date'].'</td>'.
'<td>'.$disp['field'].'</td>'.
'<td>'.$disp['extra'].'</td>'.
</tr>';
}
?>
Or, you could always just run two separate queries:
1) Retrieve the value of the name:
SELECT name FROM crew WHERE id = '$id' LIMIT 1;
2) Retrieve all the values from the fields table and display it. And where you want to display the name, just call the value/variable from the first query.
I hope I helped you out.
~Brett
