Search name and pull information - MySQL

[dmcdaniel]

I don't know that the subject describes exactly what I want to do so I will explain it as best as I can.

 

I have a form that uses a select box to pull all the names of our employees from the database. I then have a submit button that opens up a page. The code from my view.php is as follows:

 

<form onsubmit="return validateForm()" name="frmRequest" action="attendance.php" method="post" encytype="multipart/form-data">
Which employee would you like? 
<select>
<?
@ $db = mysql_connect("localhost", "db_user", "db_pass");
mysql_select_db("EmployeeInfo");
$strSQL = "SELECT Name FROM EmployeeInfo ORDER BY Name";
$rs = mysql_query($strSQL);
$nr = mysql_num_rows($rs);
for ($i=0; $i<$nr; $i++) {
$r = mysql_fetch_array($rs);
echo "<OPTION VALUE=\"".$r["Name"]."\">".$r["Name"]."</OPTION>";
}
?>
</select>
<input type="submit" class="submit" value="Submit" />
</form>

 

On the attendance.php form, I need it to display that user that was pulled from the form on the page before. What would be the best way to do this? I am fairly new to PHP and i'm at a loss right now.

 

Thanks,

dmcdaniel

 

[trink]

That select would have to have a name, IE

<select name="blah">

Then on the page that it posts to, just use $_POST['blah'] to get the value of the selected option.

[dmcdaniel]

That select would have to have a name, IE

<select name="blah">

Then on the page that it posts to, just use $_POST['blah'] to get the value of the selected option.

 

Thanks. I am using the W3schools.com tutorial for learning php and I think I just found that spot. Thanks again.

[dmcdaniel]

So I can't quite get it to work for me. This is the code I have in my two files.

 

view.php (This is used to select the employee)

<center>
<form onsubmit="return validateForm()" name="frmRequest" action="attendance.php" method="post" encytype="multipart/form-data">
Which employee would you like? <select name="NameSelect">
<?
@ $db = mysql_connect("localhost", "dbuser", "dbpass");
mysql_select_db("EmployeeInfo");
$strSQL = "SELECT Name FROM EmployeeInfo ORDER BY Name";
$rs = mysql_query($strSQL);
$nr = mysql_num_rows($rs);
for ($i=0; $i<$nr; $i++) {
$r = mysql_fetch_array($rs);
echo "<OPTION VALUE=\"".$r["Name"]."\">".$r["Name"]."</OPTION>";
}
?>
</select>
<input type="submit" class="submit" value="Submit" />
</form>
</center>

 

This is my attendance.php file (used to display the employee name only for the time being

<?php 

@ $db = mysql_connect("localhost", "dbuser", "dbpass");
mysql_select_db("EmployeeInfo");

$strSQL = "SELECT Name FROM EmployeeInfo ORDER BY Name";

?>

Welcome <?php echo $_POST["NameSelect"]; ?>!<br />

 

What am I doing wrong?

 

[PFMaBiSmAd]

Your query needs a WHERE clause so that it only matches the name that was submitted in $_POST["NameSelect"] - Ref: http://w3schools.com/php/php_mysql_where.asp

 

[dmcdaniel]

Thanks. That worked for me. This is my new coding:

<?php 

@ $db = mysql_connect("localhost", "root", "markscott");
mysql_select_db("EmployeeInfo");

$strSQL = "SELECT * FROM EmployeeInfo, Absence ORDER BY Name WHERE Name='NameSelect'";

?>

Your are now viewing the Attendance sheet for <strong><?php echo $_POST["NameSelect"]; ?></strong>!<br /><br /><br />

<?php

$result = mysql_query("SELECT AbsDate FROM 'EmployeeInfo', Absence WHERE Absence.Account = EmployeeInfo.Account");

echo "<table border='1'>
<tr>
<th>Absence Date < 1 Year</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['AbsDate'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

 

I am now having problems with this part of the code here:

<?php

$result = mysql_query("SELECT AbsDate FROM 'EmployeeInfo', Absence WHERE Absence.Account = EmployeeInfo.Account");

echo "<table border='1'>
<tr>
<th>Absence Date < 1 Year</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['AbsDate'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

 

If I take away the link, it works properly, however, I need to have the link in there so I make sure I only get the correct absences for the correct person. Without the link, it just displays them all. I have checked my syntax, it looks correct. I have checked my tables, they are correct. Not exactly sure what I am doing wrong.

 

[PFMaBiSmAd]

I'm not sure what link you are referring to but your 'EmployeeInfo' table name is enclosed in single-quote, making it a string, instead of a table name.

[dmcdaniel]

Yeah, I noticed that so I removed it. That didn't fix it either. Maybe if I explain further.

 

I have two tables, EmployeeInfo and Abscence.

 

Inside of both of those tables, I have a field called Account (This is a Primary key).

 

I need to link both of the Account fields inside EmployeeInfo and Absence so I only pull the correct absence dates for that employee and not everyones.

 

Hope that gives enough information

[trink]

$strSQL = "SELECT * FROM EmployeeInfo, Absence ORDER BY Name WHERE Name='NameSelect'";

should be

$strSQL = "SELECT * FROM EmployeeInfo, Absence ORDER BY Name WHERE Name='{$_POST['NameSelect']}'";

And your last query still has nothing that will tell it which account to access, it's just telling it this:

Select the absence date from the tables where the account is equal in both tables, so it'll select essentially everything that has matching rows in each table.

Try something like this

$result = mysql_query("SELECT AbsDate FROM EmployeeInfo, Absence WHERE Absence.Account = EmployeeInfo.Account AND Absence.Account = (SELECT Account FROM EmployeeInfo WHERE Name = '{$_POST['NameSelect']}')";