Jump to content

justinbriggs1

Members
  • Content Count

    173
  • Joined

  • Last visited

Community Reputation

0 Neutral

About justinbriggs1

  • Rank
    Member
  • Birthday 09/12/1979

Profile Information

  • Location
    Richmond, VA
  1. OK, here is one answer. I take no credit for figuring this out by the way:SELECT main.*, MAX(CASE WHEN node_name = 'urgency' THEN node_value END) AS urgency, MAX(CASE WHEN node_name = 'name' THEN node_value END) AS name, MAX(CASE WHEN node_name = 'phone' THEN node_value END) AS phone, MAX(CASE WHEN node_name = 'location' THEN node_value END) AS location, MAX(CASE WHEN node_name = 'department' THEN node_value END) AS department, MAX(CASE WHEN node_name = 'cc' THEN node_value END) AS cc, MAX(CASE WHEN node_name = 'status' THEN node_value END) AS status, MAX(CASE WHEN node_name = 'case_manager' THEN node_value END) AS case_manager, MAX(CASE WHEN node_name = 'ip' THEN node_value END) AS ip, MAX(CASE WHEN node_name = 'case_manager_eid' THEN node_value END) AS case_manager_eid FROM problems AS main INNER JOIN problem_nodes pn ON pn.id_problem = main.id_problem WHERE node_name IN ('urgency','name','phone','location','department','cc','status','case_manager','ip','case_manager_eid') GROUP BY main.id_problem HAVING id_problem_type = '1' AND status != 'complete' AND status NOT LIKE '%project%' ORDER BY date_created DESC LIMIT 20 The query time went from 3.7 seconds to about .1 seconds.
  2. That's a good question. I don't see any reason why I couldn't get the results in a different format and then modify the data as needed with PHP. That might actually be optimal since I'd rather not rewrite any major data structures. I am still, however, at a loss for ideas at this time. Do you have any suggestions? And also, have you seen anything like this before? I'm not sure if the last developer was doing some "experimenting", but this is not really the way I would normally do things.
  3. Hey yall, I got a horrible mess of a query, was wondering if anyone could take a look at it and let me know of any suggestions of how to clean it up. It works, but it runs slowly after too many records are in there. It only involves two tables, but the query basically takes values from rows in the problem_nodes table and turns them into columns for the final result.SELECT * FROM (SELECT urgency,name,phone,location,department,cc,status,case_manager,ip,case_manager_eid,id_problem,id_problem_type,eid_author,title,body,date_created,date_modified FROM problems AS main INNER JOIN ( SELECT id_problem as t_urgency_id_problem, node_value AS urgency FROM problem_nodes WHERE node_name = "urgency" )t_urgency ON t_urgency.t_urgency_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_name_id_problem, node_value AS name FROM problem_nodes WHERE node_name = "name" )t_name ON t_name.t_name_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_phone_id_problem, node_value AS phone FROM problem_nodes WHERE node_name = "phone" )t_phone ON t_phone.t_phone_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_location_id_problem, node_value AS location FROM problem_nodes WHERE node_name = "location" )t_location ON t_location.t_location_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_department_id_problem, node_value AS department FROM problem_nodes WHERE node_name = "department" )t_department ON t_department.t_department_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_cc_id_problem, node_value AS cc FROM problem_nodes WHERE node_name = "cc" )t_cc ON t_cc.t_cc_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_status_id_problem, node_value AS status FROM problem_nodes WHERE node_name = "status" )t_status ON t_status.t_status_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_case_manager_id_problem, node_value AS case_manager FROM problem_nodes WHERE node_name = "case_manager" )t_case_manager ON t_case_manager.t_case_manager_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_ip_id_problem, node_value AS ip FROM problem_nodes WHERE node_name = "ip" )t_ip ON t_ip.t_ip_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_case_manager_eid_id_problem, node_value AS case_manager_eid FROM problem_nodes WHERE node_name = "case_manager_eid" )t_case_manager_eid ON t_case_manager_eid.t_case_manager_eid_id_problem = main.id_problem)t This pattern of retrieving data is a bit foreign to me, so any help would be appreciated.Justin
  4. Hey all, I've been tasked with cleaning up a query due to the delay we are getting with the results. My question is, how do I create a select statement which will return row values as column names?For example:Here is how the table is currently configured. Sorry it looks so jacked up, the column names are in brackets, with the corresponding rows below.[id_problem] [node_name] [node_value]2----------------urgency--------Whenever2------------------name-------A. Aaronson 2-----------------phone---------555-5555I would my select statement to return something like this:[id_problem]------[urgency]-------[name]-----------[phone]2------------------Whenever------A.Aaronson--------555-5555Any help would be appreciated, thanks.JW
  5. I'm not sure I understand, which table are you referring to in your answer? Also, it seems like if your solution would produce redundant data, which is what I was trying to avoid. Could you elaborate?
  6. Hey everyone, I am trying to delete across 3 tables without affecting the reliability of the data, but am having a tough time figuring out how. I'm sure there is a standard way of doing this with SQL or the application code (PHP in my case). Here is a short description of my tables:Candidate_table - id_candidate - namePosition_table - id_position - titleRelationship_table - id_position - id_candidate I want to delete the position, and all of the candidates associated with that position. Deleting the Position rows and the Relationship rows are easy:DELETE FROM Position_table WHERE id_position = $id_positionDELETE FROM Relationship_table WHERE id_position = $id_positionBut what is the easiest way to delete the correct candidate from the Candidate_table with only the id_position? For instance, I don't want to delete the candidate if they are assigned to more than one position. Having a hard time wrapping my head around it. Any help would be appreciated.
  7. Great, thanks for your help again that worked perfectly.
  8. Thanks for the reply. I'm not really worried about any HTML formatting besides line breaks. It can be very rudimentary, for instance this would certainly suffice: ["logintimeremaining"]=> "2"["account"]=> "left/2008-05-10"["department"]=> "Specialty Pool" etc.I just can't seem to figure out how to arrange my loop to only print out the relevant key/value pairs. Also, I didn't mentioned before but the previous example was formatted with <pre> tags; otherwise it just outputs as a normal array dump style.
  9. Hey everyone, I have an array result that I am having a difficult time formatting to a more human-readable format.Here is the an example, it came from an LDAP query: array(2) { ["uid=tthompson,ou=People,dc=abc,dc=com"]=> array(7) { ["logintimeremaining"]=> string(1) "2" ["account"]=> string(15) "left/2008-05-10" ["department"]=> string(22) "Specialty Pool" ["affiliation"]=> string(15) "former-it" ["displayname"]=> string(13) "Tom Thompson" ["mail"]=> string(16) "tthompson@abc.com" ["uid"]=> string(8) "tthompson" } ["uid=ttomlin,ou=People,dc=abc,dc=com"]=> array(9) { ["pwdchanged"]=> string(15) "20101015153716Z" ["logintimeremaining"]=> string(1) "2" ["passwordexpiration"]=> string(15) "20111015153716Z" ["locked"]=> string(5) "FALSE" ["department"]=> string(7) "Requisitions" ["affiliation"]=> string(7) "faculty" ["displayname"]=> string(20) "Mr. Tom Tomlin" ["mail"]=> string(15) "ttomlin@abc.com" ["uid"]=> string(7) "ttomlin" }} The end user won't care for the length of the strings, or the top level key/values. Can any array wizards give this a look? Thanks.
  10. This is what I was looking for. just didn't set the second parameter correctly:UPDATE student_apps_copy SET submitdate = STR_TO_DATE(submitdate,'%m/%d/%Y')
  11. Hey everyone, just a quick question. I am attempting to convert some string values intoa date format, but I can't seem to figure out how to do it with one statement. Here is what I have:UPDATE student_apps_copy SET submitdate = STR_TO_DATE(submitdate,'%d,%m,%Y')This doesn't work obviously, but maybe you can see what I am trying to do.Any help would be appreciated.
  12. Hey everyone, I am messing around with the arrayList class, and I am having a bit of trouble.arrayList utilizes an object buffer to keep track of the class elements. How do I extract the last variable from the buffer? This is obviously pretty easy if it is cast to an array, but I can't figure it out with an object.Also, is there a way to extract an object variable at a specified index?Thanks, really confused, any help would be appreciated.
  13. Hey everyone, I just installed xampp on my windows 7 machine, but I forgot that I had previously installed another web server when I was experimenting with the Zend framework. I deleted all of the Zend files since I didn't need them, but now I forgot how to configure localhost to point to my new xampp server (I'm getting an unable to connect error). Any help would be appreciated.UPDATE:OK, I tried restarting the new apache server and then used http://127.0.0.1/ instead of localhost, and it works now. But still doesn't work for locahost. Anyone know how I update this accordingly? Thanks.
  14. Hey everyone, I've been having an issue sending an email in a PHP script using the regular PHP mail function, and the wpmail Wordpress function. I can send e-mails to almost every e-mail address I can think of, and it works fine, but when I try to send it to my clients e-mail, nothing happens, doesn't even get sent to the junk folder. The e-mail in question is one on their own server, like john.smith@mywebsite.com.I could usually just test my way through this, but the client does not want to provide me with a test e-mail account. What is the best way to verify that each e-mail is going to be sent? I don't have a lot of experience with this in general, so any help would be appreciated.
  15. Looks like you didn't use a closing div tag around your images. Are you trying to do this?: <div style="position: absolute; width: 516px; height: 29px; z-index: 1; left: 280px; top: 560px;" id="specials"> <img alt="" src="images/daily_specials.jpg" width="516" border="0" height="29"></div><div style="left: 0px; top: 0px; width: 161px; height: 41px; z-index: 2; position: absolute;" id="rueben"> <img alt="" src="images/grilled_rueben_btn.jpg" width="161" border="0" height="41"></div>
×
×
  • Create New...