[Pulling previous and next record so I can reorder items.]

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.

[connecting to database]

SQLyog: http://www.webyog.com/en/

[Mysql Server Crash]

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

[Select articles from within a month of todays date?]

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

[Select articles from within a month of todays date?]

$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);

[need a query for removing dupes]

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()

[searching (WHERE) in a UNION query]

$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]

Thanks, that code generates a query error.

 

Well, that is fantastic.

[[SOLVED] full database search]

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.

[Mysql Server Crash]

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.

[Mysql Server Crash]

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]

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

[[SOLVED] Querying multiple tables through one statement.]

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.

[Exporting from Excel to MySQL ?]

There is an import data from CSV function in SQLyog.

 

http://www.webyog.com/en/

[Calculating Days Off and Hours Per day]

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.

[[SOLVED] Double row INSERT issue with PHP and MySQL]

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);
}

[Where Are My Error Messages?]

ini_set working outside of the if condition and not inside is what does not make sense.

[Where Are My Error Messages?]

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.

[Where Are My Error Messages?]

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>";

[[SOLVED] Need help optimizing join]

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.

[[SOLVED] Is it better to have 1 big query or 3-4 smaller ones?]

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.

[Calculating Days Off and Hours Per day]

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.

[Looking for the most efficient way to do this]

no no... DISTINCT is also useful in a GROUP_CONCAT

[Wrong field type, changing entries from ".1" to "0.1" ?]

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.

[Where Are My Error Messages?]

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.