Updating a database using values from another.

hilltopper06 · March 16, 2009

I am trying to run a php script after a user submits a form. It is an attendance tracker of sorts and I am wanting to take the value of the day they missed (1 for full day .5 for half) and subtract it from their total days (which are split into 3 categories Sick, Vacation, Personal). I have two tables. One that shows a history of absences for all teachers and another that contains a list of all teachers and their total days remaining. Here is my code (which does not work, it is more of pseudo code that I need your help with).

<?php

$host = "myhost";

$user = "myuser";

$pass = "password";

$dbname = "database";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()." ");

mysql_select_db($dbname);

$sql = SELECT halfday, absencetype FROM attn_track;

$sql2 = SELECT sick, personal, vacation FROM tea_tbl;

$query = mysql_query($sql);

$temp = $halfday;

if $absencetype = "sick"

{

$query = mysql_query($sql2);

$sick = $sick - $halfday;

$query1="update " . $tea_tbl . " set dbflag=" . $sick . " where id = " . $absencetype;

}

if $absencetype = "personal"

{

$query = mysql_query($sql2);

$personal = $personal - $halfday;

$query1="update " . $tea_tbl . " set dbflag=" . $personal . " where id = " . $absencetype;

}

if $absencetype = "vacation"

{

$query = mysql_query($sql2);

$vacation = $vacation - $halfday;

$query1="update " . $tea_tbl . " set dbflag=" . $vacation . " where id = " . $absencetype;

}

mysql_close($attn);

?>

Please help! tea_tbl is the table that has all my teachers and their days remaining. attn_track has all the absences.

JonnoTheDev · March 16, 2009

One problem you are having is that you are setting values in your conditional statements instead of comparing them

Operator

= Sets a value

== Compares a value

if $absencetype = "sick"

Should be

if($absencetype == "sick")

Ammend all your operators

http://uk2.php.net/manual/en/language.operators.comparison.php

JonnoTheDev · March 16, 2009

Also your varable $absencetype will contain no value as you are not returning any data from your query. If you are expecting more than 1 record to be returned from your query you must loop through the results:

$sql = SELECT halfday, absencetype FROM attn_track;
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
  $temp = $row['halfday'];
  
  if($row['absencetype'] == "sick") {

  }
}

hilltopper06 · March 17, 2009

I am not returning more than one value, maybe I need to better explain myself.

Please go forward assuming I know nothing about php or mysql (not too far from the truth).

I am using a Joomla component called Fabrik. It allows you to construct and tie in forms to tables. There is the option to execute custom php code at the end of a form, that is what I want to do. I have two main tables. One lists all of my teachers and how many sick, vacation, and personal days they have. The second is used by a form to record absences. It pulls the teacher list from my first table (the component does this on its own thankfully) and allows the end user to specify what type of absence and whether or not it was a half day (half-day yes = .5 and half-day no = 1).

I need the form used to record absences to take the half-day value and subtract it from the appropriate filed (vacation, sick, personal) in the first table. I understand that this needs to be done by updating the appropriate field. I am just not sure how to do this. Since it will be run each time a new record is input, I don't think I need a loop, but I am not sure. I am really needing help with the syntax of accessing two tables and then updating the information in one. If there is a tutorial that applies to my situation, a link would be appreciated, thanks for all the replies.

JonnoTheDev · March 17, 2009

http://www.tizag.com/mysqlTutorial/

hilltopper06 · March 17, 2009

Thanks for the replies, here is where I am now:

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()." ");
mysql_select_db($dbname);

$sql = SELECT halfday, absencetype, LastName FROM attn_track;

$query = mysql_query($sql);

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

$temp = $row['halfday'];

if($row['absencetype'] == "sick") {

$sick = $sick - $temp;

$result="UPDATE tea_tbl SET sick=" . $sick . " WHERE LastName = " . $lastname " AND proc = 'no' ";

$result="UPDATE tea_tbl SET proc = 'yes' ";

}

if($row['absencetype'] == "personal") {

$persoanl = $personal - $temp;

$result="UPDATE tea_tbl SET personal=" . $personal . " WHERE LastName = " . $lastname " AND proc = 'no' ";

$result="UPDATE tea_tbl SET proc = 'yes' ";

}

if($row['absencetype'] == "vacation") {

$vacation = $vacation - $temp;

$result="UPDATE tea_tbl SET vacation=" . $vacation . " WHERE LastName = " . $lastname " AND proc = 'no' ";

$result="UPDATE tea_tbl SET proc = 'yes' ";

}

}

mysql_close($attn);

?>

I think I am getting close. What I did was loop it as you suggested (as I quickly figured out it was the only way to get this to work). In order to loop it successfully without reading through the same information over and over, I added a flag of sorts with the proc field. It defaults to 'no' in the table and I have the script change it to yes after it is read through. This should work as my script is really only going to be doing one row at a time. The only thing I am not understanding (and that I have not found much help with in the tutorials) is how to access the sick, vacation, or personal fields from tea_tbl and then write them back. I guess I am confuses on how to access information from two tables and then write the finished info back into one of them. I am not real sure about any of the syntax involved there, or the command to actually push out my changes. Any help is much appreciated.

JonnoTheDev · March 17, 2009

if($row['absencetype'] == "sick") {
  $sick = $sick - $temp;
  $result = "UPDATE tea_tbl SET sick='".$sick."' WHERE LastName = '".$row['LastName']."' AND proc = 'no'";
  mysql_query($result);
  $result = "UPDATE tea_tbl SET proc = 'yes'";
  mysql_query($result);
}

kickstart · March 17, 2009

Hi

There are still issues, but think this is the basics of what you want:-

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
$sql = SELECT halfday, absencetype, LastName FROM attn_track;
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) 
{
$temp = $row['halfday'];
$lastname = $row['LastName'];

switch ($row['absencetype'])
{
	case "sick" : 
		$sick = $sick - $temp;
		$result="UPDATE tea_tbl SET sick=$sick WHERE LastName = '$lastname' AND proc = 'no' ";
		mysql_query($result);
		$result="UPDATE tea_tbl SET proc = 'yes' WHERE LastName = '$lastname'";
		mysql_query($result);
		break;
	case "personal":
		$personal = $personal - $temp;
		$result="UPDATE tea_tbl SET personal=$personal WHERE LastName = '$lastname' AND proc = 'no' ";
		mysql_query($result);
		$result="UPDATE tea_tbl SET proc = 'yes' WHERE LastName = '$lastname'";
		mysql_query($result);
		break;
	case "vacation":
		$vacation = $vacation - $temp;
		$result="UPDATE tea_tbl SET vacation=$vacation WHERE LastName = '$lastname' AND proc = 'no' ";
		mysql_query($result);
		$result="UPDATE tea_tbl SET proc = 'yes' WHERE LastName = '$lastname'";
		mysql_query($result);
		break;
	default :;
}
}

mysql_close($attn);

?>

The first noticeable issue being that you are not setting the fields $sick, etc, to have an initial value.

Also not sure why you are doing 2 updates and not just setting proc = 'yes' on the first update (normal reason for doing this would be to batch up a load of changes, do some processing such as send an email to each one and then mark them as processed).

All the best

Keith

JonnoTheDev · March 17, 2009

$result="UPDATE tea_tbl SET sick=$sick WHERE LastName = '$lastname' AND proc = 'no' ";

$lastname is not set. This will not work.

Make sure all your variables have values or you will end up with unexpected results in your database tables

$result="UPDATE tea_tbl SET sick=$sick WHERE LastName = '".$row['LastName']."' AND proc = 'no' ";

hilltopper06 · March 17, 2009

Thanks again for all the help, you guys have been great. Here is what I have now.

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()." ");

mysql_select_db($dbname);

$sql = SELECT halfday, absencetype, LastName FROM attn_track;

$query = mysql_query($sql);

while($row = mysql_fetch_array($query))

{

$temp = $row['halfday'];

$lastname = $row['LastName'];

switch ($row['absencetype'])

{

case "sick" :

$sick = $sick - $temp;

$result="UPDATE tea_tbl SET sick=$sick WHERE WHERE LastName = '".$row['LastName']."' AND proc = 'no' ";

mysql_query($result);

$result="UPDATE tea_tbl SET proc = 'yes' WHERE WHERE LastName = '".$row['LastName']."';

mysql_query($result);

break;

case "personal":

$personal = $personal - $temp;

$result="UPDATE tea_tbl SET personal=$personal WHERE LastName = '".$row['LastName']."' AND proc = 'no' ";

mysql_query($result);

$result="UPDATE tea_tbl SET proc = 'yes' WHERE LastName = '".$row['LastName']."';

mysql_query($result);

break;

case "vacation":

$vacation = $vacation - $temp;

$result="UPDATE tea_tbl SET vacation=$vacation WHERE LastName = '".$row['LastName']."' AND proc = 'no' ";

mysql_query($result);

$result="UPDATE tea_tbl SET proc = 'yes' LastName = '".$row['LastName']."';

mysql_query($result);

break;

default :;

}

}

mysql_close($attn);

?>

I feel like I am almost there, but I am curious about one thing. The initial values of $sick $personal and $vacation need to come from tea_tbl. That is to say, I want to read information from attn_track, and depending on the absence type and whether or not it was a half-day, subtract that value from the appropriate category in tea_tbl. Do I need to do something speical in order to initialize the values from tea_tbl? Can I make two queries at the same time to two different tables? That is the part that I remain confused about. Thanks again for all your help!

hilltopper06 · March 18, 2009

Sorry to double post, but I thought of something that I thought I should ask. How do I nest loops in php/mysql?

I need the first loop to take values from a row in Table A. Then in the second nested loop it should compare them to every row in Table B. If requirements are met, then the current row in table B should be updated. Basically I need to compare values from two tables for every row in each table and update accordingly. That is what I have been trying to do, now I just need some help with the nesting of the loops, any ideas? Thanks again.

hilltopper06 · March 18, 2009

Sorry again, it wouldn't let me edit my last post for some reason. Here is some pseudo code that helps explain what I am wanting to do. Thanks again.

<?

DB Connect Code;

$query1 = SELECT FullName, AbsenceType, HalfDay, Proc FROM attn_tbl;

$query2 = SELECT FirstName, LastName, VacationDays, PersonalDays, SickDays FROM tea_tbl;

while($row = mysql_fetch_array($query1))

{

$temp = $row['halfday'];

$fullname = $row['Fullname'];

$count = 0;

while($count = mysql_fetch_array($query2))

{

if($row['AbsencType'] == 'sick' && $row['FullName'] == $count["'LastName' + ', ' + 'FirstName'"] && $row['proc'] == 'no')

{

$sick = $count['SickDays'] - $temp;

$result="'UPDATE tea_tbl SET SickDays='$sick";

mysql_query($result);

break;

}

if($row['AbsencType'] == 'vacation' && $row['FullName'] == $count["'LastName' + ', ' + 'FirstName'"] && $row['proc'] == 'no')

{

$vacation = $count['VacationDays'] - $temp;

$result="'UPDATE tea_tbl SET VacationDays='$vacation";

mysql_query($result);

break;

}

if($row['AbsencType'] == 'personal' && $row['FullName'] == $count["'LastName' + ', ' + 'FirstName'"] && $row['proc'] == 'no')

{

$vacation = $count['PersonalDays'] - $temp;

$result="'UPDATE tea_tbl SET PersonalDays='$vacation";

mysql_query($result);

break;

}

}

}

mysql_close($attn);

?>

hilltopper06 · March 18, 2009

Sorry again for the bump :'(

Here is my most recent code. I think it all works from a design standpoint, it is the syntax that is killing me. Can anyone please give me a proofread on this? I know that it is not working in my setup as is.

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()." ");

mysql_select_db($dbname);

$sql = SELECT halfday, absencetype, TeacherSelect FROM attn_track;

$sql2 = SELECT SickDays, VacationDays, PersonalDays FROM tea_tbl;

$query1 = SELECT TeacherSelect, AbsenceType, HalfDay, proc FROM attn_track;

$query2 = SELECT FirstName, LastName, VacationDays, PersonalDays, SickDays FROM tea_tbl;

while($row = mysql_fetch_array($query1))

{

$temp = $row['halfday'];

$fullname = $row['TeacherSelect'];

$count = 0;

while($count = mysql_fetch_array($query2))

{

if(($row['AbsencType'] == 'Sick') && ($fullname == $count["'LastName' + ', ' + 'FirstName'"]) && ($row['proc'] == 'no'))

{

$sick = $count['SickDays'] - $temp;

$result="UPDATE tea_tbl SET SickDays=$sick WHERE LastName + ', ' + Firstname = '".$fullname."' AND proc = 'no' ";

mysql_query($result);

$result2="'UPDATE attn_track SET' ".$row['proc']." = 'yes'";

mysql_query($result2);

break;

}

if(($row['AbsencType'] == 'Vacation') && ($fullname == $count["'LastName' + ', ' + 'FirstName'"]) && ($row['proc'] == 'no'))

{

$vacation = $count['VacationDays'] - $temp;

$result="UPDATE tea_tbl SET VacationDays=$vacation WHERE LastName + ', ' + Firstname = '".$fullname."' AND proc = 'no' ";

mysql_query($result);

$result2="'UPDATE attn_track SET' ".$row['proc']." = 'yes'";

mysql_query($result2);

break;

}

if(($row['AbsencType'] == 'Personal') && ($fullName == $count["'LastName' + ', ' + 'FirstName'"]) && ($row['proc'] == 'no'))

{

$vacation = $count['PersonalDays'] - $temp;

$result="UPDATE tea_tbl SET PersonalDays=$personal WHERE LastName + ', ' + Firstname = '".$fullname."' AND proc = 'no' ";

mysql_query($result);

$result2="'UPDATE attn_track SET' ".$row['proc']." = 'yes'";

mysql_query($result2);

break;

}

}

}

mysql_close($attn);

?>

JonnoTheDev · March 19, 2009

This is not valid syntax

$count["'LastName' + ', ' + 'FirstName'"]

Use . for concatonation

$count['LastName'].' '.$count['FirstName']

kickstart · March 19, 2009

Hi

Try this:-

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
$query1 = "SELECT TeacherSelect, AbsenceType, HalfDay, proc FROM attn_track WHERE proc = 'no'";

while($row = mysql_fetch_array($query1))
{
$temp = $row['halfday'];
$fullname = $row['TeacherSelect'];
$count = 0;
$query2 = "SELECT FirstName, LastName, VacationDays, PersonalDays, SickDays FROM tea_tbl WHERE LastName + ', ' + Firstname = '$fullname'" ;
while($count = mysql_fetch_array($query2))
{
	if($row['AbsencType'] == 'Sick')
	{
		$sick = $count['SickDays'] - $temp;
		$result="UPDATE tea_tbl SET SickDays=$sick WHERE CONCAT_WS( ', ',LastName,Firstname) = '$fullname' ";
		mysql_query($result);
		$result2="UPDATE attn_track SET proc = 'yes'";
		mysql_query($result2);
	}
	if($row['AbsencType'] == 'Vacation') 
	{
		$vacation = $count['VacationDays'] - $temp;
		$result="UPDATE tea_tbl SET VacationDays=$vacation WHERE CONCAT_WS( ', ',LastName,Firstname) = '$fullname' ";
		mysql_query($result);
		$result2="UPDATE attn_track SET proc = 'yes'";
		mysql_query($result2);
	}
	if($row['AbsencType'] == 'Personal')
	{
		$vacation = $count['PersonalDays'] - $temp;
		$result="UPDATE tea_tbl SET PersonalDays=$personal WHERE CONCAT_WS( ', ',LastName,Firstname) = '$fullname' ";
		mysql_query($result);
		$result2="UPDATE attn_track SET proc = 'yes'";
		mysql_query($result2);
	}
}
}
mysql_close($attn);
?>

You could also consider doing this without looping round in php, just doing the processing in MySQL. Like this (note - not tested this at all)

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
$sql = "update tea_tbl a SET SickDays = SickDays - (SELECT SUM(HalfDay) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Sick' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";
mysql_query($sql);
$sql = "update tea_tbl a SET PersonalDays = PersonalDays - (SELECT SUM(HalfDay) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Personal' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";
mysql_query($sql);
$sql = "update tea_tbl a SET VacationDays = VacationDays - (SELECT SUM(HalfDay) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Vacation' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";
mysql_query($sql);
$sql = "update attn_track SET proc = 'yes' WHERE AbsenceType IN ('Sick','Personal','Vacation')";
mysql_query($sql);
mysql_close($attn);
?>

All the best

Keith

hilltopper06 · March 19, 2009

This kinda works

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()." ");

mysql_select_db($dbname);

$sql = "update tea_tbl a SET SickDays = SickDays - (SELECT SUM(HalfDay) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Sick' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";

mysql_query($sql);

$sql = "update tea_tbl a SET PersonalDays = PersonalDays - (SELECT SUM(HalfDay) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Personal' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";

mysql_query($sql);

$sql = "update tea_tbl a SET VacationDays = VacationDays - (SELECT SUM(HalfDay) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Vacation' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";

mysql_query($sql);

$sql = "update attn_track SET proc = 'yes' WHERE AbsenceType IN ('Sick','Personal','Vacation')";

mysql_query($sql);

mysql_close($attn);

?>

It processes the entries but sets the values of SickDays, PersonalDays, and VacationDays to nothing (not 0, the field becomes empty). Any ideas what could be causing that? Thanks for all your help.

kickstart · March 19, 2009

Hi

Suspect the SUMs might be returning NULL. You could try forcing the SUM to 0 if it is NULL.

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
$sql = "update tea_tbl a SET SickDays = SickDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Sick' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";
mysql_query($sql);
$sql = "update tea_tbl a SET PersonalDays = PersonalDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Personal' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";
mysql_query($sql);
$sql = "update tea_tbl a SET VacationDays = VacationDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Vacation' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) )";
mysql_query($sql);
$sql = "update attn_track SET proc = 'yes' WHERE AbsenceType IN ('Sick','Personal','Vacation')";
mysql_query($sql);
mysql_close($attn);
?>

All the best

Keith

hilltopper06 · March 19, 2009

That worked, somewhat. Now it keeps values in the VacationDays, SickDays, PersonalDays fileds, but it is the start value. Nothing gets changed. What is the purpose of the SUM(HalfDay) vs. just HalfDay? I think there is just one small detail I am missing somewhere that is messing everything up. Thanks for your help.

kickstart · March 19, 2009

Hi

The subselect should be coming up with a total all the HalfDay for a particular name. So if there are 5 unprocessed rows for a particular teacher containing (say) 1,2,3,2,4 in HalfDay it would bring back 12 (ie, 1+2+3+2+4). The update can then use this value to subtract away from Sickdays / etc.

Entirely possible the issue is that it never finds a match on the name. Always a risk when combining 2 columns. If you can I would recommend having a table of teachers with a numeric autonumber key and then in every other table rather than using the teachers name you just refer to the numeric ID field from the teachers table.

Also note that these updates are not going to be 100% reliable. For example if someone managed to put an entry on attn_track for a sick day after the sick days have been updated but before the processed flag has been set then this would be marked as processed without ever being processed. However I suspect for your needs it should be fine. It not there is a way round this by updating the processed marker to some temporary value at the start of processing (for example maybe set it to "being processed"), process those that have that processed market and then update those ones to mark them as processing complete

Also note it you could probably improve the efficiency with:-

<?php
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
$sql = "update attn_track SET proc = 'processing' WHERE AbsenceType IN ('Sick','Personal','Vacation') AND proc = 'no'";
mysql_query($sql);
$sql = "update tea_tbl a SET SickDays = SickDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Sick' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) ) WHERE CONCAT_WS( ', ',a.LastName,a.Firstname) IN (SELECT TeacherSelect FROM attn_track WHERE proc = 'processing' AND AbsenceType = 'Sick' )  ";
mysql_query($sql);
$sql = "update tea_tbl a SET PersonalDays = PersonalDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Personal' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) ) WHERE CONCAT_WS( ', ',a.LastName,a.Firstname) IN (SELECT TeacherSelect FROM attn_track WHERE proc = 'processing' AND AbsenceType = 'Personal' )  ";
mysql_query($sql);
$sql = "update tea_tbl a SET VacationDays = VacationDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Vacation' AND b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname) ) WHERE CONCAT_WS( ', ',a.LastName,a.Firstname) IN (SELECT TeacherSelect FROM attn_track WHERE proc = 'processing' AND AbsenceType = 'Vacation' )  ";
mysql_query($sql);
$sql = "update attn_track SET proc = 'yes' WHERE AbsenceType IN ('Sick','Personal','Vacation') AND proc = 'processing'";
mysql_query($sql);
mysql_close($attn);
?>

All the best

Keith

hilltopper06 · March 19, 2009

I understand what you are saying about setting a processing flag, I may do that once I get everything else ironed out. I was going to try using user ids instead of names but was having some issue passing the values from one table to the next. I may have to give that another go. I am thinking that my problem lies in the

SickDays = SickDays - (SELECT IFNULL(SUM(HalfDay),0)

part. Because before you added the IFNULL it would return nothing and after it always returns 0. I am thinking that since HalfDay comes from the attn_track table then it does not know where it is coming from or what value it has since it is not initialized until later on in the query statement. I may be way off on that one, but I am hoping I am hitting close to home. Also, I noticed on the name join that you have

CONCAT_WS( ', ',a.LastName,a.Firstname)

which I would think would return ,LastNameFirstName

where I am needing LastName, FirstName or something like this

CONCAT_WS(a.LastName, ', ',a.Firstname)

I may be off here as well, I am pretty new to all of this.

Thanks again, you have been more than helpful.

kickstart · March 19, 2009

I was going to try using user ids instead of names but was having some issue passing the values from one table to the next. I may have to give that another go. I am thinking that my problem lies in the

It should be fairly easy, and it does make other things easier (ie, removes the need to play around concatenating first and last names). But maybe best to deal with one problem at a time.

I am thinking that since HalfDay comes from the attn_track table then it does not know where it is coming from or what value it has since it is not initialized until later on in the query statement. I may be way off on that one, but I am hoping I am hitting close to home. Also, I noticed on the name join that you have

It is a returned value from the subselect. If it finds no matching name then it should bring back 0 (and so it will update the table by subtraction 0, which is not going to change the value).

CONCAT_WS( ', ',a.LastName,a.Firstname)

which I would think would return ,LastNameFirstName

where I am needing LastName, FirstName or something like this

CONCAT_WS will join a load of string together using the first parameter as a separator. That should give you what you want, although slightly concerned that TeacherSelect might not have the space in it.

I have realised I made a very silly error in the last bit of code I posted to you. I added an update to set "proc" to processing, but still checked for no in the subselects. Sorry

If you want post (or PM me) a bit of the data (export the tables and some of the data from phpmyadmin) and I will have a play.

All the best

Keith

hilltopper06 · March 19, 2009

I'd be more than happy to send you any and all of it, but the forms and everything else I am using is a part of Joomla component called Fabrik. The code I have been needing help with is a script that I have set to run after an attendance form is submitting so that it will update that teacher's record to reflect their new total number of days remaining. It is fabrik that is giving me grief as far as the ID thing is concerned. It assigns each table entry an internal id and does not like for a table to have any other id assigned to it. The other problem is that my site is not live (nor will it be anytime soon). Anything else you know of that I could try?

kickstart · March 19, 2009

Hi

Don't really need the forms to try debugging this, but a data extract would help (just a export half a dozen lines of the 2 tables).

Beyond that try the following SQL just to check the name is obvious:-

SELECT CONCAT_WS( ', ',a.LastName,a.Firstname) FROM attn_track b

Could also try:-

SELECT * FROM tea_tbl a INNER JOIN attn_track b ON b.TeacherSelect = CONCAT_WS( ', ',a.LastName,a.Firstname)

and see whether that does match the records up.

All the best

Keith

hilltopper06 · March 19, 2009

I think what you gave me before matches the TeacherSelect to the LastName, FirstName as it did update some of my records, it just updated them with NULL. I did many various data inputs and it came back the same every time. All 3 records (VacationDays, SickDays, PersonalDays) would go from their start value (10, 10, 3) to Blank, Blank, and Blank. Once you added the IFNULL the records stay 10, 10, 3 regardless of how the data is input. The record is processed as the proc flag is updated. There is something quirky there in Subtracting VacationDays = VacationDays - Halfday. I will check and make sure I am not doing something on my end to mess it up and I will try and dump the two tables for you so you can take a look.

kickstart · March 19, 2009

Hi

Halfday is a numeric field isn't it?

All the best

Keith

Sign In

Updating a database using values from another.

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

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