Member Avatar for mcastu

hi all
i spent 2 days to find out my mistake.... but failed
i m completely stuck out from my code plz help me out
:'(
I am trying to update one field(ecode) of my at0310 table from emp_company table's ecode within same database(master)...
i tried update query with diff styles but noting happend.......
my at0310 table having ecardno,attime,atdate,IO and ecode..
ecode field is empty inside at0310 table........ and i want dat value from emp_company table dat matches with ecardno of both tables.
here is code-:

<head>
<title></title>
</head>
<body>
<?php

$con = mysql_connect("localhost","root","root");
 if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("master", $con);
$query= "UPDATE at0310 SET ecode = ( SELECT ecode
FROM emp_company WHERE at0310.ecardno = emp_company.ecardno)
WHERE EXISTS ( SELECT ecode
 FROM emp_company WHERE at0310.ecardno = emp_company.ecardno)";
//$query="SELECT emp_company.ecode
//FROM emp_company
//LEFT JOIN at0310
//ON emp_company.ecardno = at0310.ecardno";
   if (mysql_query($query, $con))
{
echo "record updated !";
}
else
{
echo "something went wrong";
}

mysql_close($con)
?>
</BODY>
</HTML>
  • 2 Contributors
  • 7 Replies
  • 93 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by mcastu
Member Avatar for CFROG

It doesn't look like you are executing your query. Tacking an or die(mysql_error()); on the end of the query will give you an immediate heads up if there is an error in your query. There are a few other possibilities why it's not working but I would start here.
Try this:

$query=mysql_query( "UPDATE at0310 SET ecode = ( SELECT ecode
FROM emp_company WHERE at0310.ecardno = emp_company.ecardno)
WHERE EXISTS ( SELECT ecode
 FROM emp_company WHERE at0310.ecardno = emp_company.ecardno)") or die(mysql_error());
Member Avatar for mcastu

hey...................i got dis error
You have an error in your SQL syntax near 'SELECT ecode FROM emp_company WHERE at0310.ecardno = emp_company.ecardno) ' at line 1

Member Avatar for CFROG

There would be a few different ways to write this query and a JOIN comes immediately to mind.

EXAMPLE: <-------

$query=mysql_query("SELECT emp.ecode FROM emp_company AS emp INNER JOIN at0310 AS at ON at.ecardno=emp.ecardno")or die(mysql_error());
while($row=mysql_fetch_array($query)){
     $ecode=$row['ecode'];

mysql_query("UPDATE at0310 SET ecode='$ecode'")or die(mysql_error());
}

The above query IS JUST AN EXAMPLE of how you could use a JOIN. You may not get the desired results if you run this query. If your table is backed up then you could try it if you want. Don't say I didn't warn you ;) Without the WHERE clause it will update every instance of ecode ... this may or may not be what you want.

Member Avatar for mcastu

still itz not working..........query is running but the condition applied is not working...the same ecode is applied on all the records.................dnt knw whatz the problem.

Edited by mcastu because: n/a
Member Avatar for CFROG

Like I said, that query was just an example of how you could use JOIN to do what you want. You say the "same" ecode is used on all records. So it is the same code for every record ... or does it differ for each entry in the db?

Member Avatar for mcastu

hi ...............
i want ecode from emp_company table so in emp_company tb there are diff ecode for every employee
cardno in at0310 can be repeated but corresponding dat cardno ecode should come from emp_company table
.. and emp_compnay having ecardno field dat is unique for all ecode
i feel i m near to solve my prob............ i need little improvement in this code dat update ecode into this table...

Member Avatar for mcastu

reply for this thread ........................waiting for reply

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.