Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

Results 1 to 13 of 13

Thread: [PHP/MySQL] Concept Help

  1. #1

    [PHP/MySQL] Concept Help

    So I'm working on a project and I'm thoroughly stuck. I'm not sure if I need code help or maybe just concept help.

    Essentially what's going on is I have a database full of events which are pulled on to a calendar. The events are structured by employee ID (referencing the employee table) and a start date and end date in the form of unix timestamps.

    The way I pull out data right now is I search for all events under a certain project between the start date and the end date and order by employee id. So I get an array that might look something like this:
    employee 1: project a, date x1 through date y1
    employee 2: project a, date x2 through date y2
    employee 3: project b, date x3 through date y3.

    This all works hunky dory, my issue arises when I pull out data for the same employee with multiple events in a time frame. So I'll get.
    employee 1: project a, date x1 through date y1
    employee 1: project a, date x2 through date y2
    employee 2: project b, date x3 through date y3

    I'm using the mysql_get_array function so the array structure creates a key for each event, rather than each employee, so when I do a while loop in the PHP code I end up getting a new line for each event rather than employee. This leads to a table row for one employee followed by a table row for the same employee with a different event. Obviously it would make more sense for this to be all on one line.

    I know this is all over the place, and I'll probably have to post some code at some point, but for now I'm just conceptually looking for ideas on how to tackle this. It's not imminent, I'm pushing this feature (or bug fix, depending on view) to the second beta which will probably be a week or two from now.

    But any help would be awesome. Writing calendaring apps is a behotch.

  2. #2
    Could you perhaps, on the second loop (the one employee 1 is accessed a second time), ask your DB if there already is a record for employee 1, and if there is, just put employee 1's second event in that row?

    Probably not the best way, but it should work I guess.
    ----- Blargh!!!

  3. #3
    I'd just use a multidimensional array. When I'm going through the loop to get all the results, i'd just make something like $values[$row['employeeid']][$row['eventid']][$row[(key)] = $row[(value];

    Then it'd just take a few foreaches to get to it, but other than that it'd be golden.
    got pwnt?

  4. #4
    Yeah I may have to go that direction. This is such a mess

  5. #5
    I'm about to take a stab at what's been mentioned but if anyone has any other ideas or examples of code I'd love to see it.

  6. #6
    The only economic way of doing this to my knowledge is to use a multidimensional array as hl has mentioned. May be a pain to parse but it's the way to go.

  7. #7
    I'm currently creating a giant bastard of an array.

    For some reason nesting a while loop inside my foreach is screwing up the foreach array as soon as the while loops more than once.

    Wow that's hard to explain. This is such a stupid problem.

  8. #8
    encountered this often. my way of solving this is as follows:

    lets sy you have this result set:
    employee 1: project a, date x1 through date y1
    employee 1: project a, date x2 through date y2
    employee 2: project b, date x3 through date y3

    Code:
    $last_employeeid = -1;
    $last_project = -1;
    while( $result )
    {
          if( $last_employeeid != $result['employeeid'] )
          {
                $last_employeeid = $result['employeeid'];
               // add a row or something extra here
          }
          if( $last_project != $result['project'] )
          {
               $last_project = $result['project'];
               // add a row or something extra here
          }
          // do the normal thing 
          $result = get_next_result(); // not real code
    }
    the problem that you have with your resultset in the last comment could be caused by you overwriting the first resultset when getting a new one.... in the nested loop
    Last edited by borrob; January 25th, 2008 at 04:40 AM.

  9. #9
    That's essentially what I did. I'm now at the point of just trying to figure out a few more things and I think I'll be all set.

    Thanks for the help

  10. #10
    icio's Avatar
    3,811
    posts
    looks better in lowercase
    I've not tested any of this, just thinking aloud
    Also, I've commented the code but I changed a couple of bits after pasting here so the comments may or may not make sense (haha, sorry.) I think this is most likely evident in the final state of the array where the key of each event won't simply be the next empty key, but the ID of the event


    With respect to creating that bad-assed multi-dimensional array I'd have thought of something a little easier going whereby you get each employee with an event occuring within your current calendar month (or week, whatever..) and then for each employee get each event.

    This would easily allow you to create an AllEmpoyees => CertainEmployee => AllEvents => CertainEvent heirarchy. And then you might wish to do something like this:
    PHP Code:
    <?
        
    /**
         * Assumed Database Structure:
         *
         * + Events [Table]
         *   - EmployeeID
         *   - Project
         *   - StartTime
         *   - EndTime
         * + Employees [Table]
         *   - EmployeeID
         *   ...
         */
         
        /**
         * Variables
         */
        
    $employees = array();
        
        
    // Get all unique employees with events (we don't need the others; perhaps in your situation it's best just to get all of the employees from the employees table)
        
    $result_employees mysql_query("SELECT DISTINCT `EmployeeID` FROM `Events`;");
        
        while (
    $employee mysql_fetch_assoc($result_employees))
        {
            
    $employees[$employee $employee['EmployeeID']] = array();
            
            
    /**
             * Now we have
             * $employee as the current employee ID to look for
             * $emplyees[$employee] as the array to hold the events for employee $employee
             */
             
            
    $result_events mysql_query("SELECT * FROM `Events` WHERE `EmployeeID`=$employee;");
            while (
    $event mysql_fetch_assoc($result_events))
            {
                
    $employees[$employee][$event['EventID']] = array(
                    
    "project" => $event['Project'],
                    
    "start"   => $event['StartTime'],
                    
    "end"     => $event['EndTime']
                    );
            }
            
        }
        
        
    mysql_free_result($result_employees);
        
    mysql_free_result($result_events);
        
        
    /**
         * So now effectively what you've got is
         * $employees = array(
         *    0 => array(
         *        // Events employee with corresponding EmployeeID '0'
         *        array(
         *            // Employee 0's first listed event
         *            "project" => 'project',
         *            "start"   => 'starttime',
         *            "end"     => 'endtime'
         *        ),
         *        // etc ...
         *    ),
         *    1 => array(
         *        // etc ...
         *    ),
         *    // etc...
         * );         
         */




    // *** Which will tidy up to look like the following:

        
    $employees = array();
        
    $result_employees mysql_query("SELECT DISTINCT `EmployeeID` FROM `Events`;");
        
        while (
    $employee mysql_fetch_assoc($result_employees))
        {
            
    $employees[$employee $employee['EmployeeID']] = array();
            
            
    $result_events mysql_query("SELECT * FROM `Events` WHERE `EmployeeID`=$employee;");
            while (
    $event mysql_fetch_assoc($result_events))
            {
                
    $employees[$employee][] = array(
                    
    "project" => $event['Project'],
                    
    "start"   => $event['StartTime'],
                    
    "end"     => $event['EndTime']
                );
            }
        }
        
        
    mysql_free_result($result_employees);
        
    mysql_free_result($result_events);
    Or (perhaps more easily) like this:
    PHP Code:
    $employees = array();
    $result mysql_query("SELECT * FROM `Events`;");
    while (
    $event mysql_fetch_assoc($result))
    {
        if(!isset(
    $employees[$event['employee']]))
        { 
    // Create the space for the employee
            
    $employees[$event['EmployeeID']] = array();
        }
        
        
    // Add the event to the employee's space
        
    $employees[$event['EmployeeID']][$event['EventID']] = array(
            
    "project" => $event['Project'],
            
    "start"   => $event['StartTime'],
            
    "end"     => $event['EndTime']
            );
    }
    mysql_free_result($result); 


    With all of that said, I don't think this is the approach that I'd go when the main thing I should be considering is the dates. I'm not exactly sure what you're representing in your calendar (or how) but my main focus would be on the dates at which things happen. And I would likely create an array where each key represented a day of the month (so array[0..30], for example) and in each key I would have an array of what's going on on that day.

    Things that are going on might include a new event starting, a started event ending or an event continuing from a previous day until another day. I think the best thing I can advise from this is to first take into consideration how you're outputing the data into the calendar and let that be the deciding factor in how you store your data in your script. Think about how you're going to access it when you're building the HTML.

    I hope that helps... it's kind of a complex question. Perhaps I'm misunderstanding or over-complicating matters and sorry if that's the case.

    Best of luck!

    Edit: reading back on that my english has been mashed by my thinking at the same time.
    Last edited by icio; January 27th, 2008 at 09:24 PM.
    "60% of the time it works... every time." -- Paul Rudd as Brian Fantana.

  11. #11
    That's a really helpful post, I very much appreciate the work you put in to that.

    I like what you're doing and I think I'm going to kind of blend what I did with your concepts. I think I'll pull out all the unique employees like you are doing (I pulled out everything from MySQL and then modified the array in PHP, I'm a moron - didn't even think to just toss a DISTINCT in there). After that I'll cycle through each day in the calendar for each person and depending on what's available for the particular the day the code will do what it needs to do.

    Turns out it really doesn't need to be as complicated as I'm making it. I have a nice function that takes into account the start and end date of a table and truncates a colspan value which is returned based on these things. So I think I'll just loop through with an index variable that goes up by 1 if nothing happened or whatever the colspan is and just spit out tds based on that.

    Phew - this stuff is rough and I'm just doing this as a favor to my bosses

  12. #12
    icio's Avatar
    3,811
    posts
    looks better in lowercase
    Well it sounds like you know where you're going with it at least. Glad I could help even if it was only a little.
    "60% of the time it works... every time." -- Paul Rudd as Brian Fantana.

  13. #13
    It helped a lot, it caused me to think of different ways to work it out which is what I generally get stuck on.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012