Jump to content


  • Posts

  • Joined

  • Last visited


Posts posted by bubblegum.anarchy

  1. hmm... what about a sort order based on linked lists.


    using a < prev_book_id > instead of a sort order value


    book.id = 1

    book.name = 'First Book'

    book.prev_book_id = 0


    book.id = 2

    book.name = 'Second Book'

    book.prev_book_id = 1


    book.id = 3

    book.name = 'Third Book'

    book.prev_book_id = 2


    grab the ordered list with:


    SELECT * FROM book ORDER BY prev_book_id


    moving the book either way would require:

        the selected books prev_book_id to be set to the prev_book_id of the shifted book


    and the next requirements depend on the direction of the shift.. as is to shift up and switched to shift down


        the shifted books prev_book_id to be set to the id of the selected book

        and the prev_book_id of the book that references the selected book to be set to the id of shifted book


    well something like that anyway.

  2. What if you rewrite that to move the interval part to the between?


    First of all, allow me to confirm that the original line of code still crashes mysql.


    ... and the INTERVAL beside any constant date value crashes mysql and so does the following:


    SELECT subdate('2007-06-02', 1) BETWEEN '2007-06-02' AND '2007-06-03';
    SELECT adddate('2007-06-02', 1) BETWEEN '2007-06-02' AND '2007-06-03';


    version = 4.1.21-community-nt

  3. This is a little silly and pedantic but anyways:


    SET @date_created = '2007-06-01';
    SELECT benchmark(10000000, month(@date_created) = month(CURRENT_DATE));
    # ~ 531ms
    SELECT benchmark(10000000, @date_created BETWEEN CURRENT_DATE AND last_day(CURRENT_DATE));
    # ~ 219ms


    The month function appears to take some time.


    version = 4.1.21-community-nt

  4. That is oddly stored data, consider grabing a count of each group that has duplicate records, like this:


    SELECT count(*) - 1 as delete_number, concat(id, rating, ip) as duplicate_row 
    FROM ips
    GROUP BY concat(id, rating, ip)
    HAVING count(*) > 1;


    ... and then deleting each duplicate ip record separately with a limit


    mysql_query($query = "DELETE FROM ips WHERE concat(id, rating, ip) = '{$record['duplicate_row']}' LIMIT {$record['delete_number']}") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);


    EDIT: probably best to add an appropriate separator for the concat()

  5. Either connect all the tables with indexes (foreign keys) or use UNION


         SELECT 'scholarship', count(*) AS matches FROM scholarship 
         WHERE scholarship.description LIKE '%sara%' 
              OR scholarship.decision LIKE '%sara%' 
              OR scholarship.name LIKE '%sara%' 
              OR acceptnums.description LIKE '%sara%'
         SELECT 'acceptnums', count(*) AS contains_matches FROM acceptnums 
         WHERE acceptnums.decision LIKE '%sara%' OR acceptnums.nums LIKE '%sara%'


    ... and then another query based on the results to display the records for matching tables.

  6. Anyone know why the following crashes mysql server version 4.1.21-community-nt


    SELECT '2007-06-02' - INTERVAL 1 DAY BETWEEN '2007-06-02' AND '2007-06-03';


    WARNING: the above line will most likey crash mysql server

  7. Can anyone confirm this wacky notion:


    SELECT headings.headingid
        , headings.heading
        , topics.topic
        , posts.userid
        , posts.added
        , posts.post
    FROM headings
        LEFT JOIN topics ON headings.headingid = topics.headingid
        LEFT JOIN posts ON topics.topicid = posts.topicid AND concat(posts.topicid, posts.added) IN (
             SELECT concat(topicid, max(added)) AS most_recent
             FROM posts GROUP BY topicid 
    GROUP BY headings.headingid

  8. Change this:


    $reqres= mysql_query("SELECT m.member_name, m.member_earned, m.member_spent, m.member_adjustment
    (m.member_earned-m.member_spent+m.member_adjustment) AS m.member_current
    m.member_lastraid, p.pid
    FROM " . MEMBERS_TABLE . " m, " . W_REQUEST_TABLE . " w
    ON m.member_name = p.pname
    WHERE (w.iid = '$iid') AND (w.pid = p.pid)");


    To this:


    $reqres= mysql_query($query = "SELECT m.member_name, m.member_earned, m.member_spent, m.member_adjustment
    (m.member_earned-m.member_spent+m.member_adjustment) AS m.member_current
    m.member_lastraid, p.pid
    FROM " . MEMBERS_TABLE . " m, " . W_REQUEST_TABLE . " w
    ON m.member_name = p.pname
    WHERE (w.iid = '$iid') AND (w.pid = p.pid)") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);


    to see what the error is.

  9. The number of hours per day can be worked out using the `contract_type.weekly_hours`.

    There is no way to tell what days an employee is asking for off using `leave.paid_days`, just calculate hours - otherwise include a date range to track the actual dates.


















    leave.paid_days <- used without a leave_range table

    leave.status <- (pending, approved, denied)








    An employee submits a leave form starting on 11-JUN-2007 and returning to work on 25-JUN-2007 and also submits the paid date ranges (calendar controls would be handy) 11-JUN-2007 to 15-JUN-2007 and 18-JUN-2007 to 22-JUN-2007 - the resulting record would be:


    leave.id = 6448

    leave.employee_id = 487

    leave.start_date = 11-JUN-2007

    leave.return_date = 25-JUN-2007

    leave.status = pending


    leave_range.leave_id = 6448

    leave_range.start_date = 11-JUN-2007

    leave_range.end_date = 15-JUN-2007


    leave_range.leave_id = 6448

    leave_range.start_date = 18-JUN-2007

    leave_range.end_date = 22-JUN-2007


    Either way, with variable rosters, the onus is on the employee to provide accurate information and an additional approval system should be used for data integrity.


    EDIT: use datetime fields in the date ranges to track leave in hours format... You are likely going to really need to have the additional leave_range table if something like leave hours needs to be tracked... either way, add an approval system also.

  10. What is the error_reporting and display_errors directive set to in php.ini and are they commented out?


    Your results with the additional calls to error_reporting and ini_set make no sense, pleaes post the exact file contents wrapped in code tags.

  11. But there is no error with that above code... I even tested the code... something should be printed.


    Do you have access to the php.ini file?


    what about this:

    if ($original_value = @ini_set('display_errors', 'On'))
         print "<P>New display_errors value set</P>";
         print "<P>Original value: {$original_value}</P>";
    else print "<P>Failed to set display_errors</P>";

  12. Consider adding a column to the job table to identify the number of hours per week the employee works.  If the number of hours per week an employee works is not fixed then the onus must be on the employee to provide the amount of paid leave days and a managers approval of the request.  The use the number hours per week stored in the job table or the employee provided paid days in leave to calculate the total amount paid to the employee.

  13. Please try the following:


    if ($original_value = ini_set('display_errors', 'On'))
         print "<P>New display_errors value set</P>";
         print "<P>Original value: {$original_value}</P>";
    else print "<P>Failed to set display_errors</P>";


    The following note is regarding the behaviour of ini_set(display_errors...



    Although display_errors may be set at runtime (with ini_set()), it won't have any affect if the script has fatal errors. This is because the desired runtime action does not get executed.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.