SQL query

taz321 · January 29, 2008

Hi

I have two tables

Employee

Form

The foreign key of the form is EmployeeID.

Now heres the problem.

I want to display the EmployeeID in my form but as you know if i did then the ID number of the Employee would display instead of the Full Name (which is what i want).

e.g

The employeeID of Tom is 2.

In my form i want to display the name Tom by using an SQL query which recognises the ID and returns the name of that ID.

I have had a start but its wrong.

<?php

require "connect.php";

$query1 = "select * from form WHERE empName = surname";

$result1 = mysql_query($query1, $connection)

or die ("MySQL Error: ".mysql_error());

?>

empName (displays the primary key of the employee table)

surname (i want it to display the surname of the employee by recognising the empName)

Any help would be appreciated.

Thanks

p2grace · January 29, 2008

Basically you'd have to use a simple join.

$query = "SELECT e.surName FROM `Employee` e, `Form` f WHERE e.empName = f.empName";
$run = mysql_query($query);

This is assuming that surName is the actual name of the employee, and empName is the id of the employee.

craygo · January 29, 2008

Try this I am not sure what the key field is in the employee table, but in this example I will assume it is the same as in the form table, employeeID

$query1 = "SELECT * FROM form JOIN employee ON form.employeeID=employee.employeeID WHERE empName = 'surname'";

if you give me the structure of both tables I could help out a little more.

Ray

taz321 · January 29, 2008

This is the 'employee' Table

employeeID

title

firstname

surname

username

password

active

This is the 'form' Table

formID

issuetitle

datesubmitted

timesubmitted

systemaffected

prioritylevel

issuedetails

supportcomments

clientFname

clientSurname

teamname

empName

clientID

In the form table, the empName displays the employeeID from the employee table, hope this helps.

Thanks

craygo · January 29, 2008

what field in the form table references the employeeid in the employee table??

craygo · January 29, 2008

Sorry I was editing the last post when time ran out so disregard.

I replaced the surname with xxx, because empname is a reference to the employee id which is an interger. the empname should be, in the case above, 2. The xxx should be some value passed from the web page.

<?php
$query1 = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'xxxx'";
$res = mysql_query($query1) or die(mysql_error());
while($rows = mysql_fetch_assoc($res)){
echo $rows['firstname']." ".$rows['lastname']."<br>";
}
?>

That should give you your first and last name based off toms employeeid of 2

Ray

taz321 · January 29, 2008

So this is the statement im now using

<?php

$query1 = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'surname'";

$result = mysql_query($query1) or die(mysql_error());

while($rows = mysql_fetch_assoc($result)){;

echo $rows['surname'];

}

?>

And then i echo it

<?php echo $row['surname'];?>

but it doesnt seem to work, any reason why ?

taz321 · January 29, 2008

can anyone help me with this please.

themistral · January 29, 2008

Try changing this line

while($rows = mysql_fetch_assoc($result)){;

to

while($rows = mysql_fetch_array($result)){

taz321 · January 29, 2008

i echo the surname like this to display the result that i want.

<?php echo $row['surname'];?>

But it still doesnt work.

themistral · January 29, 2008

Try

<?php echo $rows['surname'];?>

instead of

<?php echo $row['surname'];?>

You have set the variable as $rows in this line

while($rows = mysql_fetch_assoc($res)){

taz321 · January 30, 2008

This is my code now

<?php

$query1 = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'surname'";

$result1 = mysql_query($query1) or die(mysql_error());

while($rows = mysql_fetch_array($result1)){

echo $rows['surname'];

}

?>

and i display it by doing this (but still doesnt work)

<?php echo $rows['surname'];?>

But nothing displays

Is there anything im doing wrong.

taz321 · January 30, 2008

Can anyone help me with this query

Thanks

trq · January 30, 2008

You need to check it actually finds a result.

<?php

  $sql = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'surname'";
  if ($result = mysql_query($sql)) {
    if (mysql_num_rows($result)) { 
      while ($row = mysql_fetch_array($result)) {
        echo $row['surname'];
      }
    } else {
      echo "No results found";
    }
  } else {
    die("Query failed<br />$sql<br />" . mysql_error());
  }

?>

What do you get?

taz321 · January 30, 2008

That still doesnt display the employee surname. :-(

This is the code you gave me

<?php

$sql = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'surname'";

if ($result1 = mysql_query($sql)) {

if (mysql_num_rows($result1)) {

while ($row = mysql_fetch_array($result1)) {

echo $row['surname'];

}

} else {

echo "No results found";

}

} else {

die("Query failed<br />$sql<br />" . mysql_error());

}

?>

And i echo it using this

<?php echo $rows['surname'];?>

taz321 · January 30, 2008

If it helps, this is my whole code for displaying this data.

i have indicated below by ''''''' ''''''''''' where i echoed the code where i want the surname to display.

<?php

session_start();

if (isset($_SESSION['username']) == false){

header("Location:ClientLogin.php");

exit();

}

?>

<?php

require "connect.php";

$query = "select * from form";

$result = mysql_query($query, $connection)

or die ("MySQL Error: ".mysql_error());

?>

<?php

$sql = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'surname'";

if ($result1 = mysql_query($sql)) {

if (mysql_num_rows($result1)) {

while ($row = mysql_fetch_array($result1)) {

echo $row['surname'];

}

} else {

echo "No results found";

}

} else {

die("Query failed<br />$sql<br />" . mysql_error());

}

?>

<head>

<title>Jupiter Development Support</title>

<!--

#Layer2 {position:absolute;

width:200px;

height:52px;

z-index:2;

left: 16px;

top: 143px;

}

#Layer1 {position:absolute;

width:200px;

height:80px;

z-index:1;

left: 254px;

top: 84px;

}

.style3 {font-family: "Copperplate Gothic Bold"; font-size: x-large; }

#Layer5 { position:absolute;

width:441px;

height:36px;

z-index:3;

left: 390px;

top: 207px;

}

.style4 {font-size: x-small}

.style6 {font-family: "Copperplate Gothic Bold"}

#Layer3 {

position:absolute;

width:156px;

height:43px;

z-index:4;

left: 24px;

top: 470px;

}

#Layer6 { position:absolute;

width:126px;

height:118px;

z-index:4;

left: 1079px;

top: 186px;

}

.style7 {font-size: xx-small; }

.style8 {font-size: x-small; font-weight: bold; }

-->

</style>

</head>

<body>

<div id="Layer5"><span class="style3">Analyst Enquiry Screen </span></div>

<tr>

<td width="495"><span class="style4"><?php echo $_SESSION['firstname']?> <?php echo $_SESSION['surname']?> <span class="style6"> LOGGED IN </span></span></td>

</tr>

</table>

<tr>

</tr>

<tr>

</tr>

<tr>

<td><div align="center" class="style8">Enquiry Number </div></td>

<td><div align="center" class="style8">Issue Title </div></td>

<td><div align="center" class="style8">Date Submitted </div></td>

<td><div align="center" class="style8">System Affected </div></td>

<td><div align="center" class="style8">Priority Level </div></td>

</div></td>

<td class="style8"><div align="center">Employee</div></td>

</tr>

<tr>

</tr>

<tr>

<td width="68"><?php

while($row= mysql_fetch_array($result)){?></td>

'''''''''''''<td height="35"><div align="center" class="style7"><?php echo $row['surname'];?></div></td>'''''''''''''''''''''''

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

</table>

</body>

trq · January 30, 2008

Please use

tags when posting code.

Your code is all over the place. Your second while loop tries to loop through a result called $result, this is never defined anywhere.

taz321 · January 30, 2008

<?php
while($row= mysql_fetch_array($result)){?></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['formID'];?></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['issuetitle'];?></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['datesubmitted'];?></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['systemaffected'];?></div></td>
    <td height="35"><div align="center"><span class="style7"><?php echo $row['prioritylevel'];?></span></div></td>
    <td height="35"><div align="center"><span class="style7"><?php echo $row['teamname'];?></span></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['surname'];?></div></td>

Above is the main code which displays everything i want.

And iv realised now wot u mean.

Your second while loop tries to loop through a result called $result, this is never defined anywhere

But if i change it to $result1 then even though it wouldnt display for all the other fields, should it not still display surname ?

trq · January 30, 2008

no, becasue youve already ran that $result1 through a while loop at the top of your script. The would be at the end of its internal pointer.

You basically want to replace the last piece of code you posted with...

<?php

  $sql = "SELECT * FROM form JOIN employee ON form.empname = employee.employeeID WHERE empName = 'surname'";
  if ($result1 = mysql_query($sql)) {
    if (mysql_num_rows($result1)) { 
      while ($row = mysql_fetch_array($result1)) {?>
        <td height="35"><div align="center" class="style7"><?php echo $row['formID'];?></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['issuetitle'];?></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['datesubmitted'];?></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['systemaffected'];?></div></td>
    <td height="35"><div align="center"><span class="style7"><?php echo $row['prioritylevel'];?></span></div></td>
    <td height="35"><div align="center"><span class="style7"><?php echo $row['teamname'];?></span></div></td>
    <td height="35"><div align="center" class="style7"><?php echo $row['surname'];?></div></td>
      <?php}
    } else {
      echo "No results found";
    }
  } else {
    die("Query failed
$sql
" . mysql_error());
  }

?>

And remove the other part from the top.

taz321 · January 30, 2008

Thanks, il have a little play around with it and let you know.

Much appreciated

Sign In

SQL query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information