First off, as a security concern, NEVER EVER store the password in the database unencrypted. What you need to do is use an MD5 or SHA1 hash of the password, and match the two. That's digressing from the point, but it's a very important issue.
If you are storing the password, unencrypted in the database, you add two new columns to the table. password_question and password_answer. If they click a link for 'Retrieve my Password', or something to that extent, it will SELECT the password_question out of the database. They enter their answer, and you compare the password_answer to their inputed data. It will then SELECT their password from the DB, and send it to their screen.
A quick snippet of code:
if ($_POST['passwordretreive]) {
$user = $_POST['user']
$query = "SELECT password_question FROM usertable WHERE user='$user' LIMIT 1";
$result = mysql_query($query);
$question = mysql_result($result, 0);
echo $question;
}
They input their answer into the form below, do a SELECT for the password_answer, compare that to the posted value, SELECT the password, and display it.
But I can't stress enough how insecure the method is. If you choose to do it that way, the users could suffer the consequences.
