hilltopper06 Posted March 19, 2009 Author Share Posted March 19, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788736 Share on other sites More sharing options...
kickstart Posted March 19, 2009 Share Posted March 19, 2009 Hi What is its value on the database though? All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788740 Share on other sites More sharing options...
hilltopper06 Posted March 19, 2009 Author Share Posted March 19, 2009 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] Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788753 Share on other sites More sharing options...
hilltopper06 Posted March 19, 2009 Author Share Posted March 19, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788774 Share on other sites More sharing options...
hilltopper06 Posted March 19, 2009 Author Share Posted March 19, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788834 Share on other sites More sharing options...
kickstart Posted March 19, 2009 Share Posted March 19, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788860 Share on other sites More sharing options...
hilltopper06 Posted March 19, 2009 Author Share Posted March 19, 2009 No results. That is weird. I guess we have nailed down the problem. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788863 Share on other sites More sharing options...
kickstart Posted March 19, 2009 Share Posted March 19, 2009 Hi Yep. My guess would be there is an extra space somewhere in the full name field. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-788874 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789454 Share on other sites More sharing options...
kickstart Posted March 20, 2009 Share Posted March 20, 2009 Hi Have you the code that is used to join the names to populate attn_track? Could you post them. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789476 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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] Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789483 Share on other sites More sharing options...
kickstart Posted March 20, 2009 Share Posted March 20, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789504 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789524 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789530 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789537 Share on other sites More sharing options...
kickstart Posted March 20, 2009 Share Posted March 20, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789579 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789585 Share on other sites More sharing options...
kickstart Posted March 20, 2009 Share Posted March 20, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789596 Share on other sites More sharing options...
hilltopper06 Posted March 20, 2009 Author Share Posted March 20, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-789844 Share on other sites More sharing options...
hilltopper06 Posted March 24, 2009 Author Share Posted March 24, 2009 That worked! Thanks again for all your help, this puppy is solved! Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-792562 Share on other sites More sharing options...
kickstart Posted March 24, 2009 Share Posted March 24, 2009 Hi Glad I could help. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-792600 Share on other sites More sharing options...
hilltopper06 Posted April 8, 2009 Author Share Posted April 8, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-804750 Share on other sites More sharing options...
hilltopper06 Posted April 8, 2009 Author Share Posted April 8, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149711-updating-a-database-using-values-from-another/page/2/#findComment-804902 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.