Page 1 of 1
minus
Posted: Wed Nov 23, 2011 6:46 am
by jauson
is it possible to include two tables in 1 query?
because I have this query
Code: Select all
$query = mysql_query("UPDATE `$Table1` SET `textTotalDays`='$textTotalDays' -$Days FROM $Table2 WHERE `employeeID`='$employeeID'"); but its not working.
I wanted to update my table 1 everytime I have transaction in table 2. but before that the data in table 2 will minus the data in table 1.
thank you.
Re: minus
Posted: Wed Nov 23, 2011 6:53 am
by mikeashfield
Looking at what little explanation you've given, would I be right in thinking that you're trying to write a script to automate employee holidays? Take a quick look at this link:
http://allenbrowne.com/casu-14.html and if you still think that it's definitely what you want to do, then come back with a more detailed explanation of what you're trying to accomplish. Post the table structure and the code that $query comes from (in full, minus DB connect credentials).
Re: minus
Posted: Wed Nov 23, 2011 7:44 am
by jauson
yes I want to automate my employee holidays table. when a user apply for a leave she/he will declare how many $days is that. second that $days will minus to holidays table.
Re: minus
Posted: Wed Nov 23, 2011 9:14 am
by mikeashfield
I don't think the best way to do this is to store the calculated value. It is to query the database when a holiday request is taken.
You would:
1 - Query the holidaysTaken table for the total number of holidays taken.
2 - You have a value in the employees table for the total number of allowed holidays.
3 - If the employees total number of holidays allowed minus the holidays taken is more than the requested number of days then you allow the holiday and add it to the holidaysTaken table.
4 - If the employees total number of holidays allowed minus the holidays taken is less than the requested number of days then you can allow the holiday, but only pay the number of available holidays left.
Re: minus
Posted: Wed Nov 23, 2011 9:38 am
by jauson
this is what i wanted. but i dont know how to query this thing.
I have tabletotalnumbersofholidays
and I have tableofholidays
if I apply for a leave i will use tableofholidays to update the data in tabletotalnumbersofholidays.
if so. update tabletotalnumbersofholidays(5) SET days=day -daysrequestforholiday(1) FROM tableofholidays; print = 4
then everything comes up. when a user apply for a holiday and his/her tabletotalnumbersofholidays is 0 then it much easier for to hold them to apply. using IF/ELSE.
Re: minus
Posted: Thu Nov 24, 2011 2:33 am
by jauson
SELECT `textTotalDays` FROM `annual leave` INTERSECT SELECT `textTotalDays` FROM `leaveapproved` is this correcT?
Re: minus
Posted: Thu Nov 24, 2011 3:22 am
by mikeashfield
index.php
Code: Select all
<?php
mysql_connect("", "", "") or die(mysql_error());
//Now connected to DB Server.
mysql_select_db("db_name") or die(mysql_error());
//Now connected to Database.
$query=mysql_real_escape_string('SELECT ID, fname, sname FROM employees');
$result = mysql_query($query);
//We now have an array of all employees in the $result variable.
?>
<form action="/selectEmployee.php" method="POST">
<select name="employeeID">
<?php
foreach ($result as $option) {
echo "<option value='".$option['ID']."'>".$option['fname']." ".$option['sname'];
}
?>
</select>
<input type="submit" value="View Employee">
</form>
selectEmployee.php
Code: Select all
<?php
if (isset($_POST['employeeID'])) {
$ID=$_POST['employeeID'];
mysql_connect("", "", "") or die(mysql_error());
//Now connected to DB Server.
mysql_select_db("db_name") or die(mysql_error());
//Now connected to Database.
$query=mysql_real_escape_string("SELECT fname, sname, holsAllowance FROM employees WHERE ID = '$ID'");
$queryAvail=mysql_real_escape_string("SELECT daysHoliday FROM approvedHolidays WHERE employeeID = '$ID'")
$result = mysql_query($query);
//We now have our employees details in the result array.
$resultAvail = mysql_query($queryAvail);
//This variable holds all taken holidays for the selected employee.
$totalHols;
foreach ($resultAvail as $hols) {
$totalHols = $totalHols + $hols;
}
//Adds all the holidays already taken together and stores it as $totalHols.
$holsAvailable=$result['holsAllowance']-$totalHols;
echo "Employee ".$result['fname']." ".$result['sname']." has ".$holsAvailable."holidays that they can still take.<br><br>";
//Prints the number of holidays the employee has available.
echo " <form action='/addHoliday.php' method='POST'>
<input type='text' name='daysHoliday'><br>
<input type='submit' value='Add Holiday'>
<input type='hidden' name='employeeID' value='".$ID."'>
</form>
";
else {
echo "An error occured. Please try again.";
}
}
?>
addHoliday.php
Code: Select all
<?php
if (isset($_POST['daysHoliday'])) {
$daysHoliday=$_POST['daysHoliday'];
$employeeID=$_POST['employeeID'];
mysql_connect("", "", "") or die(mysql_error());
//Now connected to DB Server.
mysql_select_db("db_name") or die(mysql_error());
//Now connected to Database.
$query=mysql_real_escape_string("INSERT INTO approvedHolidays (employeeID, daysHoliday) VALUES ('$employeeID','$daysHoliday')");
mysql_query($query);
//Execute the Query.
echo "Holiday added sucessfully.";
else {
echo "Adding holiday failed. Please try again.";
}
}
?>
I haven't tested this code, but it should work with the following schema:
Code: Select all
-- ---
-- Globals
-- ---
-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;
-- ---
-- Table 'employees'
--
-- ---
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`ID` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
`fname` VARCHAR(40) NOT NULL,
`sname` VARCHAR(40) NOT NULL,
`holsAllowance` DECIMAL NOT NULL,
PRIMARY KEY (`ID`)
);
-- ---
-- Table 'approvedHolidays'
--
-- ---
DROP TABLE IF EXISTS `approvedHolidays`;
CREATE TABLE `approvedHolidays` (
`ID` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
`employeeID` INTEGER NOT NULL,
`daysHoliday` DECIMAL NOT NULL,
PRIMARY KEY (`ID`)
);
-- ---
-- Foreign Keys
-- ---
ALTER TABLE `approvedHolidays` ADD FOREIGN KEY (employeeID) REFERENCES `employees` (`ID`);
-- ---
-- Table Properties
-- ---
-- ALTER TABLE `employees` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `approvedHolidays` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;