[SOLVED] Help Me With These

june_c21 · May 4, 2008

why my code can't execute? when i execute, it output all the staff_no, name instead of specific staff_no only

<?php
//$id=$_GET["id"];
$staff_no = $_POST['staff_no'];

$host = 'localhost';
$user = 'root';
$password = '';
$dbase = 'claim';

$dblink = mysql_connect($host,$user,$password);
mysql_select_db($dbase,$dblink);

$sql="SELECT report1.staff_no, user.name FROM report1,user WHERE report1.staff_no = '".$staff_no."'";
$result = mysql_query($sql);

echo "<table border='1'>
<tr>

<th>Staff No</th>
<th>Name</th>

</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";

echo "<td>" . $row[0] . "</td>";
echo "<td>" . $row[1] . "</td>";

echo "</tr>";
}
echo "</table>";

?>

tronicsmasta · May 4, 2008

You are using an array here... you need to specify what part of the array with a variable.

while($row = mysql_fetch_array($result))
{
echo "<tr>";

echo "<td>" . $row[0] . "</td>";
echo "<td>" . $row[1] . "</td>";

echo "</tr>";
}
echo "</table>";

should be:

while($row = mysql_fetch_array($result))
{
     $report1_staffno = $row['report1.staffno'];
     $username = $row['user.name'];
     echo "<tr>";
     echo "<td>$report1_staffno</td>";
     echo "<td>$username</td>";
     echo "</tr>";
}
echo "</table>";

june_c21 · May 4, 2008

sorry .. is still doesn't work. it came out blank only

sasa · May 4, 2008

you don't set relatin on your tablese

tronicsmasta · May 4, 2008

There isn't any syntax errors...

the only think i can think of is the . in the table

eg

report1.staffno

user.name

june_c21 · May 4, 2008

ok, i test and this code got error

SELECT report1.staff_no, user.name FROM report1,user WHERE report1.staff_no = '$staff_no'

when i replace sk200023 into $staff_no

all the name in the database will came out and they auto change their staff_no into sk200023. why?

tronicsmasta · May 4, 2008

report1,user

has a coma.... is that suppose to be a . ? if its a coma, put report1,user into single quotes 'report1,user'

june_c21 · May 4, 2008

report1 is a table name

user is another table name

i need to match report1.staff_no (databse) with user input staff_no

and then based on both table got staff_no, i will take the name from user table that match with the user input staff_no

Barand · May 4, 2008

As sasa said, you haven't set the join relationship in the query

$sql="SELECT report1.staff_no, user.name 
       FROM report1 
           JOIN user ON report1.staff_no = user.staff_no
       WHERE report1.staff_no = '".$staff_no."'";

june_c21 · May 4, 2008

barand,

why it came out blank instead of showing the staff_no and name? anything i miss out?

june_c21 · May 4, 2008

<?php
//$id=$_GET["id"];
$staff_no = $_POST['staff_no'];

$host = 'localhost';
$user = 'root';
$password = '';
$dbase = 'claim';

$dblink = mysql_connect($host,$user,$password);
mysql_select_db($dbase,$dblink);

$sql="SELECT report1.staff_no, user.name 
       FROM report1 
           JOIN user ON report1.staff_no = user.staff_no
       WHERE report1.staff_no = '.$staff_no.'";

$result = mysql_query($sql);

echo "<table border='1'>
<tr>

<th>Staff No</th>
<th>Name</th>

</tr>";

while($row = mysql_fetch_array($result))
{
     $report1_staffno = $row['report1.staff_no'];
     $username = $row['user.name'];
     echo "<tr>";
     echo "<td>$report1_staffno</td>";
     echo "<td>$username</td>";
     echo "</tr>";
}
echo "</table>";
?>

this is the user input code

<html>
<head>
<script src="selectuser.js"></script>
</head>
<body><form id="form1" name="form1" method="post" action="checking.php">
  <p><strong>KEV Tool Store - Loan Transaction Page</strong></p>
  <table width="497" border="0">
    <tr>
      <td width="284">Choose</td>
      <td width="203"><select name="staff_no" onChange="showUser(this.value)">
        	  <?php
 $host     = 'localhost';
$user     = 'root';
$password = '';
$dbase    = 'claim';

$dblink = mysql_connect($host, $user, $password);
mysql_select_db($dbase, $dblink);

$query = "SELECT staff_no
FROM user
ORDER BY staff_no
";
      		
		$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
$staffno = $row['staff_no'];
echo "<option value=\"$staffno\">$staffno</option>";
     }
?>
</select></td>
    </tr>
    <tr>
      <td colspan="2"><p>
<div id="txtHint"><b>info will be listed here.</b></div>
</p> </td>
    </tr>
  </table>
  <p> </p>
  <p>
    <input type="submit" name="Submit" value="Submit">
  </p>
</form></body>
</html>

this whole code just can't get me the name and staff no when user input their staff_no . why? i been trying for 3 days....

Barand · May 4, 2008

Check for error

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

june_c21 · May 4, 2008

try already. no error

Barand · May 4, 2008

Then it could be your data.

It will only return rows where there are records on both tables which match on staff_no.

You can see which have matches and which dont by running this query. (if there is a match it will show a name)

$sql="SELECT report1.staff_no, user.name 
       FROM report1 
           LEFT JOIN user ON report1.staff_no = user.staff_no";

june_c21 · May 4, 2008

now i redo the html file again

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="checking2.php">
  <label>Name 
  <input name="staff_no" type="text" id="staff_no" />
  </label>
  <p>
    <label>
    <input type="submit" name="Submit" value="Submit" />
    </label>
  </p>
</form>
</body>
</html>

php file

<?php
// echo " New Entry <a href=add.html>click here</a><br></br>";
$host = "localhost";
$user = "root";
$password = "";
$dbase = "claim";

$dblink = mysql_connect($host,$user,$password);
mysql_select_db($dbase,$dblink);
$staff_no = $_POST['staff_no'];

echo "<table border=3 align=center width=90%>";
echo "<tr><td width=12% align=center><span class=style5>ID</td></span>";
echo "<td width=12% align=center><span class=style5>Month</td></span>";
echo "<td width=12% align=center><span class=style5>Start Date </td></span>";
echo "<td width=12% align=center><span class=style5>End Date</td></span>";
echo "<td align=center><span class=style5>Name</td></span>";
echo "<td align=center><span class=style5>Staff No</td></span>";
echo "<td align=center><span class=style5>Details</td></span>";
echo "<td align=center><span class=style5>Amount</td></span>";
echo "<td align=center><span class=style5>GL code</td></span>";
  echo "<td width=12% align=center><span class=style5>Bank </td></span>";
echo "<td align=center ><span class=style5>Account No. </td></span>";

echo "<td align=center ><span class=style5>Total </td></span>";
echo "<td align=center colspan=3><span class=style5>Action </td></span></tr>";


$query = "SELECT report1.id, month, start_date, end_date, user.name, report1.staff_no,details,amount,gl_code,user.bank,user.acc_no FROM user,report1 WHERE report1.staff_no = '$staff_no'";
$result = mysql_query($query, $dblink);
while($myrow = mysql_fetch_row($result))
{
echo "<tr><td><span class=style2>" . $myrow[0] . "</span</td>";
    echo "<td><span class=style2>" . $myrow[1] . "</span</td>";
    echo "<td><span class=style2>" . $myrow[2] . "</span</td>";
    echo "<td><span class=style2>" . $myrow[3] . "</td></span>";
    echo "<td><span class=style2>" . $myrow[4] . "</span></td>";
    echo "<td><span class=style2>" . $myrow[5] . "</span></td>";
    echo "<td><span class=style2>" . $myrow[6] . "</span></td>";
    echo "<td><span class=style2>" . $myrow[7] . "</span></td>";
echo "<td><span class=style2>" . $myrow[8] . "</span></td>";
echo "<td><span class=style2>" . $myrow[9] . "</span></td>";
echo "<td><span class=style2>" . $myrow[10] . "</span></td>";


$query1 = "SELECT SUM(amount) FROM report1 where staff_no = '$myrow[5]'";
$result1 = mysql_query($query1, $dblink);
while ($myrow1 = mysql_fetch_row($result1))
{
echo "<td><span class=style2>" .$myrow1[0]. "</span></td>";

  }
}
echo "</table>";
?>

why when i input one particular staff_no, it display blank when i click on submit button

june_c21 · May 4, 2008

thanks barand. topic solved!!!!!

Sign In

[SOLVED] Help Me With These

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

Join the conversation

Important Information