Updating a database using values from another.

[hilltopper06]

HalfDay is a radio button with two options Label Yes with value .5 and Label No with value 1.  I have a sum counter going at the bottom of the column on my attendance tracker front end and it shows the correct value there, so I am assuming it is numeric and has no problems doing calculations on it.

[kickstart]

Hi

 

What is its value on the database though?

 

All the best

 

Keith

[hilltopper06]

Here is a dump of the attn_track and tea_tbl files.  They just contain test entries now, so nothing I am worried about anyone seeing.  I am not sure I follow you on the HalfDay thing, as far as I know it is numeric.

 

[attachment deleted by admin]

[hilltopper06]

Ok, I noticed that HalfDay was set to text so I changed it to decimal (VacationDays, SickDays, PersonalDays are decimal as well).  I still am not having any luck.  It processes but nothing changes, I will play around with it some more.

[hilltopper06]

Here is the result I get from a console query

 

mysql> connect attn;

Connection id:    1843

Current database: attn

 

mysql> UPDATE tea_tbl a SET SickDays = SickDays - (SELECT IFNULL(SUM(HalfDay),0)

FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Sick' AND b.TeacherSelec

t = CONCAT_WS( ', ',a.LastName,a.FirstName) ) WHERE CONCAT_WS( ', ',a.LastName,a

.FirstName) IN (SELECT TeacherSelect FROM attn_track WHERE proc = 'no' AND Absen

ceType = 'Sick' );

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 0

 

mysql>

 

So it isn't finding any matching records to update it seems.  I will run some test queries to see if I can get it to match up.

[kickstart]

Hi

 

SELECT * FROM tea_tbl a  WHERE CONCAT_WS( ', ',a.LastName,a
.FirstName) IN (SELECT TeacherSelect FROM attn_track);

 

Try that and see if it finds any matches at all.

 

All the best

 

Keith

 

[hilltopper06]

No results.  That is weird.  I guess we have nailed down the problem.

[kickstart]

Hi

 

Yep. My guess would be there is an extra space somewhere in the full name field.

 

All the best

 

Keith

[hilltopper06]

I found the problem, now I need your help to fix it (sound familiar  )

 

Ok, I did a query on tea_tbl from the mysql console.  I did

 

SELECT CONCAT_WS( ', ',LastName,FirstName)  FROM tea_tbl;

 

And it returned my teacher names properly formated.  ie. Smith, John

 

Then I tried a query on TeacherSelect using the following

 

SELECT TeacherSelect  FROM attn_track;

 

And it only returned the last names.  ie.  Smith.

 

The form that feeds tea_tbl allows an end-user to input a First Name, and a Last Name.

 

The form that feeds attn_track uses a database join and a concat function to join the two.  They show up properly in all of my tables and forms, but apparently the query doesn't like the comma inbetween the two.  Any ideas on how to fix this?

[kickstart]

Hi

 

Have you the code that is used to join the names to populate attn_track? Could you post them.

 

All the best

 

Keith

[hilltopper06]

I do not have the code as I am using a built in feature in the joomla component fabrik.  Attached is a screenshot of the page where I set the database join.  It doesn't use normal CONCAT syntax (from what I can tell from the popup window).

 

 

 

[attachment deleted by admin]

[kickstart]

Hi

 

To be honest I know next to nothing about Joomla.

 

I think that you might be best starting a new topic on this issue with Joomla in the title and hopefully someone with experience of that framework can help identify the problem

 

All the best

 

Keith

[hilltopper06]

Thanks for your help, I am having some trouble registering over at the fabrik forums, so I will probably make a new topic over here shortly.  I installed firebug and found something interesting.

 

<option value="Abernathy" >Abernathy, Dave</option>

 

That is how my TeacherSelect is being displayed when I look at it in Firebug.  So I take it that my value is not being set the right way somewhere.  I will have to play around with it a bit.  Thanks for all your help.

[hilltopper06]

I think I figured it out.  It is a bug in fabrik I have decided.  On the screenshot I posted notice where it allows me to select a field and key from tea_tbl OR the concat string.  Well the value still goes to the field you select and the concat string is just the label.  To work around this I tried setting the value to fabrik_internal_id (which it wouldn't let me do earler when the concat string was empty).  Now I can change my code to read IF TeacherSelect = a.fabrik_internal_id and everything will be peachy.  I will respond again once I have it working.

[hilltopper06]

Maybe I spoke too soon.  When I run a console query it works:

mysql> SELECT * FROM tea_tbl WHERE fabrik_internal_id IN (SELECT TeacherSelect F

ROM attn_track);

+--------------------+---------------------+-----------+----------+--------+----

----------+----------+--------------+

| fabrik_internal_id | time_date          | FirstName | LastName | School | Per

sonalDays | SickDays | VacationDays |

+--------------------+---------------------+-----------+----------+--------+----

----------+----------+--------------+

|                26 | 2009-03-20 00:00:00 | Collin    | Nelson  | Bremen |

      3.0 |    10.0 |        10.0 |

|                27 | 2009-03-20 00:00:00 | Ted      | Dude    | Bremen |

      3.0 |    10.0 |        10.0 |

+--------------------+---------------------+-----------+----------+--------+----

----------+----------+--------------+

2 rows in set (0.00 sec)

 

mysql>

 

Here is my most recent code as well.

<?

$connection info

$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 = 'processing' AND AbsenceType = 'Sick' AND b.TeacherSelect = a.fabrik_internal_id 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 = 'processing' AND AbsenceType = 'Personal' AND b.TeacherSelect = a.fabrik_internal_id 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 = 'processing' AND AbsenceType = 'Vacation' AND b.TeacherSelect = a.fabrik_internal_id 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);

?>

 

 

So now we are really, really, really, close.  Maybe a typo on my part?

[kickstart]

Hi

 

Few errors in the SQL. The bit in red doesn't make sense $sql = "update tea_tbl a SET VacationDays = VacationDays - (SELECT IFNULL(SUM(HalfDay),0) FROM attn_track b WHERE proc = 'processing' AND AbsenceType = 'Vacation' AND b.TeacherSelect = a.fabrik_internal_id IN (SELECT TeacherSelect FROM attn_track WHERE proc = 'processing' AND AbsenceType = 'Vacation' )  ";

 

However give this a try. I did spot an error in what I gave you last time, and this assumes that TeacherSelect on attn_track contains fabrik_internal_id:-

 

<?
$connection info
$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 = 'processing' AND AbsenceType = 'Sick' AND b.TeacherSelect = a.fabrik_internal_id 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 = 'processing' AND AbsenceType = 'Personal' AND b.TeacherSelect = a.fabrik_internal_id 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 = 'processing' AND AbsenceType = 'Vacation' AND b.TeacherSelect = a.fabrik_internal_id 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]

Something still isn't right.  I tried running one of the queries through mysql console and it spit back an error:

 

mysql> update tea_tbl a SET SickDays = SickDays - (SELECT IFNULL(SUM(HalfDay),0)

FROM attn_track b WHERE proc = 'no' AND AbsenceType = 'Sick' AND b.TeacherSelec

t = a.fabrik_internal_id IN (SELECT TeacherSelect FROM attn_track WHERE proc = '

no' AND AbsenceType = 'Sick' );

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '' at

line 1

mysql>

 

So it is not liking the apostrophies?  Or does that only apply to the console query?

[kickstart]

Hi

 

No, it isn't liking me making a total mess of editing it! My fault totally.

 

<?
$connection info
$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 = 'processing' AND AbsenceType = 'Sick' AND b.TeacherSelect = a.fabrik_internal_id) WHERE a.fabrik_internal_id 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 = 'processing' AND AbsenceType = 'Personal' AND b.TeacherSelect = a.fabrik_internal_id) WHERE a.fabrik_internal_id 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 = 'processing' AND AbsenceType = 'Vacation' AND b.TeacherSelect = a.fabrik_internal_id) WHERE a.fabrik_internal_id 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]

My setup is at work so I will try that out on Monday as soon as I get it, thanks so much for all your help.

[hilltopper06]

That worked!  Thanks again for all your help, this puppy is solved!

[kickstart]

Hi

 

Glad I could help.

 

All the best

 

Keith

[hilltopper06]

Guess who is back? 

 

I just need a little more help, and then I will really be done.

 

Right now my teacher name has a CONCAT label of LastName, FirstName , but a key value that is tied to an ID.  When I lookup teachers for reports, it only shows the key value (id numbers and not names).  Is there a way to change the key value of TeacherSelect to be LastName, FirstName other than ID?  Like I mentioned, this is functionality I need for reports.  Thanks.

[hilltopper06]

Just to give a better idea of what I am trying to do, and where I am at with it.  I am using the same code the kickstart helped me with before and it works beautifully (thanks kickstart).  Now, I am wanting to alter it a little to perform one more mundane function for me.  I would like for it to store a teachers full name into a hidden field for me.  This way I can pull that field later for reports, instead of having to use the Teacher ID.  Here is the code I have.

 

update attn_track a SET fullname = CONCAT_WS( ', ',b.LastName,b

.FirstName) FROM tea_tbl b WHERE proc = 'processing' AND a.TeacherSelect = b.fabrik_internal_id) WHERE b.fabrik_internal_id IN (SELECT TeacherSelect FROM attn_track WHERE proc = 'processing' )  ";

 

It isn't working for me (that is to say nothing gets changed or updated in fullname but the proc flag does get set to 'yes' so I know it was finished.  Any ideas?  Thanks.