![](https://forums.phpfreaks.com/uploads/set_resources_1/84c1e40ea0e759e3f1505eb1788ddf3c_pattern.png)
bubblegum.anarchy
-
Posts
526 -
Joined
-
Last visited
Never
Posts posted by bubblegum.anarchy
-
-
-
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
-
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
-
$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);
-
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()
-
$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}";
-
Joins
in MySQL Help
Thanks, that code generates a query error.
Well, that is fantastic.
-
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.
-
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.
-
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
-
Joins
in MySQL Help
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
-
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.
-
-
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.
-
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); }
-
ini_set working outside of the if condition and not inside is what does not make sense.
-
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.
-
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>";
-
no it works since I switched the and that you had with where. Seems to work pretty good now that I optimized the tables a little bit more. Thank you very much for the help.
heh - that sure is interesting.
-
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.
-
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.
-
no no... DISTINCT is also useful in a GROUP_CONCAT
-
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.
-
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.
Pulling previous and next record so I can reorder items.
in MySQL Help
Posted
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:
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.