Retrieve specific data from mysql based on HTML value

[FireGeek21]

I am new to php and am working on a web page of employee pictures. I would like to protect our employee information so would like to retrieve data based on a specific number from a mysql database. I would like to return only a single field (Position) based on a value in HTML code. Here is some php code I wrote that returns all the data from my database and puts it into a table on my website. This php works. Looking to see how I can modify it because I want only the Position returned based on a Nbr in HTML. Below is an HTML sample.

<?php
$con = mysql_connect(localhost,userid,password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db(database, $con);

$result = mysql_query("SELECT * FROM email_address ORDER BY Nbr");

echo "<table border='1'>
<tr>
<th>Nbr</th>
<th>LName</th>
<th>FName</th>
<th>Email</th>
<th>Position</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Nbr'] . "</td>";
  echo "<td>" . $row['LName'] . "</td>";
  echo "<td>" . $row['FName'] . "</td>";
  echo "<td>" . $row['Email'] . "</td>";
  echo "<td>" . $row['Position'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
mysql_close($con);
?>

Here is a sample of HTML I am working with that retrieves the employee picture. Each employee picture is stored according to a number. This number correlates to the Nbr field in the mysql database. The bottom section (Position) is what I want pulled in from the mysql database. It will be easier to update the mysql database than the HTML code.

<tr>
	<td align="center"><img class="normal" src="\includes\mfd_nbrs\64.png" /><td>
	<td align="center"><img class="normal" src="\includes\mfd_nbrs\25.png" /><td>
	<td align="center"><img class="normal" src="\includes\mfd_nbrs\7.png" /><td>
	<td align="center"><img class="normal" src="\includes\mfd_nbrs\37.png" /><td>
</tr>
<tr>
	<td align="center">FT Firefighter/Paramedic<td>
	<td align="center">Firefighter/Paramedic<td>
	<td align="center">Firefighter/Paramedic<td>
	<td align="center">FT Firefighter/Paramedic<td>
</tr>

Looking for ideas. THANK YOU!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[ClulessChris]

I'm a newbie to PHP too but I'll have a go:

<?php
Function GetPosition($Nbr) {
    $con = mysql_connect(localhost,userid,password);
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
mysql_select_db(database, $con);
$result = mysql_query("SELECT * FROM email_address WHERE Nbr = '".$Nbr.'");
echo $result['Position'];
}
?>

Just added a WHERE Clause to your SQL and echo'd the required field. Hopefully If this isn't correct or can be done better somebody will point that out and I can learn too.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[jpadie]

The mysql_query function returns (if successful) a resource. Not an array or object.

Once you have the resource you must iterate over it to get the data.

A typical iterative function would be mysql_fetch_assoc(). Which takes the resource as an argument and returns an associative array.

 

[vacunita]

If you just want a single column if you will, then build the mysql to be that specific.

SELECT columnName from mail_address WHERE Nbr = '".$Nbr.'"

Assuming Nbr can uniquely identify a row in your table, you should be able to pick a single field out of a single returned row.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[FireGeek21]

So, how can I correspond the Nbr with a specific Nbr in the HTML?

Technically this will be a two column table. Column one has the employee picture (employee name embedded in the picture) and column 2 will be their position.

Thoughts on a 3rd "hidden" column that holds the employee Nbr? But how can I connect that to the Nbr in the php code?

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[vacunita]

You would need yes a 3rd column in your table that holds the position i.e Firefighter/Paramedic.

That way you can build your query so it returns both things from the row for the person.









----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[FireGeek21]

I was refering to a 3rd column (hidden) for the Nbr. My second column is intending to hold the Position I am attempting to acquire from the mysql database by way of the Nbr in the HTML code.

I just don't know how to go about conneting the HTML and the PHP by way of the Nbr variable/field/hidden column.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[vacunita]

I'm confused now. the Nbr should be a unique identifier in your table. When you retrieve a row it contains the Nbr already.

What exactly are you wanting to do?

Have links that when clicked show the picture? If you explain what you want the end result to be it may be easier to understand, as we don't know exactly what your Nbr means to you, or what you mean by

go about conneting the HTML and the PHP by way of the Nbr

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[FireGeek21]

I will have to respond later to this. I can supply a picture of what I am looking for. Thanks!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[vacunita]

Sure. A Picture is worth 1000 words.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[jpadie]

php and html are stateless.
you need to provide php with all the information the script needs, every time a script is called.
leaving aside sessions, the way this is done is through url parameters and form submission.
url key=value pairs are available in php in the $_GET superglobal. so mydomain.com?field=value&field1=value1 will be available in $_GET['field'] and $_GET['field1']
forms submit with a particular method, which is prescribed as an attribute of the form tag. if you use the GET method then the form field names and values can be treated identically to url parameters. if you use the POST method then the variables are available in the $_POST superglobal.
thus

<form method="post">
 <input type="text" name="field" value="value" />
 <input type="submit" name="xsubmit" value="Submit" />
</form>

this form will, when submitted result in the following key value pairs being available in the $_POST super global
$_POST['field'] = 'value'
$_POST['xsubmit'] = 'Submit' //assuming the form is submitted by button press and not javascript.