Jump to content


  • Posts

  • Joined

  • Last visited


Everything 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. SQLyog: http://www.webyog.com/en/
  3. 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
  4. 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
  5. $month = "2007-06-01"; // SQL date format YYYY-MM-DD $result = mysql_query($query = " SELECT * FROM articles WHERE date_created BETWEEN '{$month}' AND last_day('{$month}')") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR);
  6. 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()
  7. $found_rows = mysql_query($query = "SELECT found_rows()") or trigger_error(mysql_error()."<PRE>".$query."</PRE>", E_USER_ERROR); $found_rows = mysql_result($found_rows, 0); print "Found rows: {$found_rows}";
  8. 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%' UNION SELECT 'acceptnums', count(*) AS contains_matches FROM acceptnums WHERE acceptnums.decision LIKE '%sara%' OR acceptnums.nums LIKE '%sara%' UNION etc... ... and then another query based on the results to display the records for matching tables.
  9. Unfortunately I am stuck on 4.1.21 until work updates there's, likely never. Thanks for confirming that the issue is likely version related, binindex.
  10. 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
  11. 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
  12. 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 RIGHT JOIN " . W_PLAYER_TABLE . " p 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 RIGHT JOIN " . W_PLAYER_TABLE . " p 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.
  13. There is an import data from CSV function in SQLyog. http://www.webyog.com/en/
  14. 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. job_association.id job_association.job_id job_association.employee_id job_association.contract_type_id etc.. contract_type.id contract_type.label contract_type.weekly_hours contract_type.overtime_multiplier etc... leave.id leave.employee_id leave.start_date leave.return_date leave.paid_days <- used without a leave_range table leave.status <- (pending, approved, denied) etc... leave_range.leave_id leave_range.start_date leave_range.end_date etc... 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.
  15. mysql_query($SQL); // THE DATA IS INSERTED HERE if (!mysql_query($SQL,$con)){ // THE DATE IS ALSO INSERTED HERE die('Error: ' . mysql_error() . '<br/><br/>' . $SQL); }
  16. ini_set working outside of the if condition and not inside is what does not make sense.
  17. 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.
  18. 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>";
  19. Usually one large, well designed, query is better than four smaller queries.... but I do recall an instance where splitting a small portion of a large query resulted in a decreased page load time - this depends on how connected that query table is to the original source and what information is available to query against.
  20. 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.
  21. no no... DISTINCT is also useful in a GROUP_CONCAT
  22. A simple solution: ALTER TABLE table_name MODIFY column_name FLOAT NOT NULL DEFAULT 0; All the values .1 .2 etc.. will become 0.1 0.2 etc... EDIT: Just be aware that non numeric values will be converted to zero.
  23. 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... Note: 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.