dd_gamer

OK... I guess I'm "NOT" smarter than a 5th grader... but thanks for the help...

I have an issue that needs to be corrected by can't figure the math part. The current code charges 50% but I need to change it to charge 60%... the issue is that the math is using "/2" to get the 50% so how do I change to 60%? The current code : // If shipping method is ground we only want to change half. if ($this->shipn[$num]=="Ground") $return[$num][rate]=$return[$num][rate]/2; return $return; }

Yes, thank you! I'm sorry but I don't understand how to use this method. Could you show an example with my code?

I made the changes to my code using the "Group by" with the student_id and that makes the query run "all" students (600 plus). The server timed out after 2 minutes (see above)... So, back to my original question about table reduction methods. Does anyone have any idea's? I'm stuck on this at this time !

I changed the schedules.gf to SUM(schedules.gf) but the aggregate counts every value within the date range and doesn't seem to recognize the "DISTINCT" in my code DISTINCT DATE(studentrecord_daily.record_date) so the count is too high (not correct). Also when I removed the student_id and use it in the Group by the query "error" time -out"

Just to clear up any query issues ... here is the query: SELECT DISTINCT DATE(studentrecord_daily.record_date), studentrecord_daily.student_id, schedule_room.scheduledate, schedule_room.scheduleid, schedules.id, schedules.gf, schedules.schedule_desc FROM schedule_room JOIN schedules ON schedules.id = schedule_room.scheduleid JOIN studentrecord_daily ON DATE(studentrecord_daily.record_date ) = schedule_room.scheduledate WHERE studentrecord_daily.student_id = '3' AND studentrecord_daily.record_date >= '2011-09-06' AND studentrecord_daily.record_date <= '2011-11-04' And here is the loop: do { $factorSum =$row_rs_factor['schedules.gf']; $factorTotal = $factorTotal + $factorSum ; } while ($row_rs_factor = mysql_fetch_assoc($rs_factor)); Then upload: mysql_query("UPDATE tcstudent SET data = '$factorTotal' WHERE id = $student_id ");

I don't have the code to get all the students yet! The code will look for "all students" that are active and run... If you look at the link you will see the code for one student only. If you do the math (600 x 2 seconds) the query will take to long and as time goes on the query will take longer and longer as the table keeps growing. That's why I'm asking for other methods , idea's and the possibilities.

Maybe I confused the issue... here is the query from another post http://www.phpfreaks.com/forums/index.php?topic=352184.msg1662997#msg1662997 The above query is for "1" person but when I do the query for "all" students it will run 600 times...

The query only takes approx. 2 seconds and was improved from 90 seconds on another post a few weeks ago. I have to run that query over 600 times (one per person) during the running of the report. I don't need the other (800,000) records during the report running and the table will be growing at a rate of 250,000 every 3 months or so. I hope this helps to explain my issue.

Can you give an example of php code? I've never used cron and not sure if I have access...

I'm hoping someone can help me or at least put me in the right direction. I have a Mysql database and one table with over 800,000 records. Most of the other tables are manageable. What I need to do is move a selected number of records (date range) from that one database table and make another table so I can improved my search capabilities within a report query. The majority of the 800,000 records are for reference only and only the active records need to be searched (approx. 20,000). I need all the records (800,000) for report history but not for the active query and the table will keep growing. Not sure if I should move table to another database! It would be best if I could automate a the process....

GOT IT! Thank you all... just used the mysql_real_escape_string($stud_book) Before the insert code...

When I used the above code this error message : With error: FUNCTION mydatabase.mysql_real_escape_string does not exist

Thank you... can you give an example using my code if possible. I'm a little confused from the example you gave as to the correct syntax for my use.

Thank you....the error message is below: INSERT failed: INSERT INTO rc_subjects (report_card_id ,student_id, subject_id, report_card_column_1, report_card_column_2 , created_on ) VALUES ('6', '21', '11', 'America's Story 1998', '1','February 28, 2012, 8:23 am' ) With error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's 1998', '1','February 28, 2012, 8:23 am' )' at line 1 Does this mean the 's in America's is causing a problem? If so how do I get it?

I give up... I've been racking my brain on this and can't see the problem. My query works in phpMyAdmin and display's the correct results. My code in my webpage using the 'echo' statement shows the correct results. When I look into the database one subject is missing... but it's showing before the insert statement. So this tell's me the issue must be my "INSERT INTO" statement. But it seems to work with everything but one type of subject_id... Here is my query and insert statement : $result2 = mysql_query("SELECT MIN(student_tract.book_lesson_id) as book_lesson_id, student_tract.bookid, student_tract.subjectId, student_tract.studentId, student_tract.book_desc, student_tract.subject_desc, book.gradelevel FROM student_tract, book WHERE student_tract.studentId = ' 21' AND student_tract.lessonComplete = '0' AND student_tract.start_date IS NULL AND book.bookid = student_tract.bookId GROUP BY subjectId ")or die(mysql_error()); while($row2 = mysql_fetch_array( $result2 )) { $stu_Id = $row2['studentId'].'<br>'; $stu_subject = $row2['subjectId'].'<br>'; $stud_book = $row2['book_desc'].'<br>'; $studGL = $row2['gradelevel']; $today = date("F j, Y, g:i a"); mysql_query("INSERT INTO rc_subjects (report_card_id ,student_id, subject_id, report_card_column_1, report_card_column_2 , created_on )VALUES ('$theRecordPeriod', '$stu_Id', '$stu_subject', '$stud_book', '$studGL','$today' )"); ///////////////////// Results before INSERT: subjectId studentId book_desc 1 21 Math 3 21 Art 6 21 Language 8 21 Science 9 21 Writing 10 21 Geography 11 21 History 25 21 Reading DATABASE Results: subjectId studentId book_desc 1 21 Math 3 21 Art 6 21 Language 8 21 Science 9 21 Writing 10 21 Geography 25 21 Reading Anyone have this issue before?

THANK you again fenway!

I added a second "Left Join" on "JOIN s_name" but it didn't change anything. I took it out because it wasn't needed for this example anyway and added to the confusion. I'm missing something here... So, here is the Query without the second JOIN...: SELECT ss.rc_id, ss.s_id, ss.sub_id, gsj.g_id, gsj.gla, gsj.sub_id, gsj.rc_id FROM ss LEFT JOIN gsj ON ss.rc_id = gsj.rc_id AND ss.sub_id = gsj.sub_id WHERE ss.rc_id = '71' AND gsj.g_id = '1' AND ss.s_id = '2250'

Perhaps a little more detail of what results I'm looking for... I want to retrieve all the records from the "ss" table and only "gla" fields that match from the "gsj" table. So, the results I'm looking for would look like this (if it works): Rows: 6 rc_id s_id sub_id g_id gla 71 2250 1 1 71 2250 2 1 71 2250 3 1 1.8 71 2250 6 1 2.45 71 2250 25 1 1.8 71 2250 5 1 2.51

I'm having a problem with a query using the 'Left Join". My results are still not showing all the records which is why I'm using the left join. Can someone show me what I'm doing wrong? My Query: SELECT ss.rc_id, ss.s_id, ss.sub_id, gsj.g_id, gsj.gla, gsj.sub_id, gsj.rc_id, s_name.id FROM ss LEFT JOIN gsj ON ss.rc_id = gsj.rc_id AND ss.sub_id = gsj.sub_id JOIN s_name ON ss.sub_id = s_name.id WHERE ss.rc_id = '71' AND gsj.g_id = '1' AND ss.s_id = '2250' Tables and output: TABLE = gsj rc_id g_id sub_id gla 71 1 3 1.8 71 1 5 2.51 71 1 6 2.45 71 1 24 1.04 71 1 25 1.8 71 1 9 NULL ____________________________________ TABLE = ss rc_id s_id sub_id 71 2250 1 71 2250 3 71 2250 6 71 2250 25 71 2250 5 71 2250 2 ____________________________________ OUTPUT Rows: 4 rc_id s_id sub_id g_id gla sub_id 71 2250 3 1 1.8 3 71 2250 6 1 2.45 6 71 2250 25 1 1.8 25 71 2250 5 1 2.51 5 As you can see from the output I'm only getting 4 rows with results and I want to get all 6 rows from ss table ... I'm missing sub_id = 1 & 2 .

Nope!

Thanks Psycho and fenway! Changed the schedule_room.scheduledate field from Varchar to DATE CAST(schedule_room.scheduledate as Date) AS schedule_date, Query took 2.6 seconds... Live and learn!

Thank you for the index info... I was reading that it could increase speed to change the type from the EXPLAINED report because "ALL" is the worst case. The number of record's in the studentrecord_daily is 179,812 rows currently. I just checked the datatypes (schedule_room.scheduledate, studentrecord_daily.record_date) and the fields I'm working with are varchar(255)... I'm assuming that's not good!!

| TABLE | type | possible_keys | key |key_len | ref | rows | Extra ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | schedule_room | ALL | NULL | NULL | NULL | NULL | 39168 | Using temporary | schedules | eq_ref | PRIMARY | PRIMARY | 4 |schedule_room.scheduleid | 1 | | studentrecord_daily | ALL | NULL | NULL | NULL | NULL | 179812 | Using where; Using join buffer I'm re-posting the EXPLAINED so that its readable. My question is how do the Index say the studentrecord_daily table for this query when it already has an "id" that is auto_increment? Or does that matter?

I did try the "BETWEEN" and using phpMyadmin interface and only gained "1" second. I did read about the Explained and the follow report shows my issues from the query. | TABLE | type | possible_keys | key |key_len | ref | rows | Extra ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | schedule_room | ALL | NULL | NULL | NULL | NULL | 39168 | Using temporary | schedules | eq_ref | PRIMARY | PRIMARY | 4 |schedule_room.scheduleid | 1 | | studentrecord_daily | ALL | NULL | NULL | NULL | NULL | 179812 | Using where; Using join buffer I'm not sure how to "Index" a table that has another field indexed...