INSERT INTO...

[dd_gamer]

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?

 

[DavidAM]

Try replacing that last mysql_query() call with something like this:

 

$sql = "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' )"; 

if (! mysql_query($sql)) die('INSERT failed: ' . $sql . ' With error: ' . mysql_error());

 

Of course, I would not leave the die() message in the production version, but I think this will give an idea of why the INSERT is not working. There is probably a single-quote mark in one of the values, or a duplicate key error or something.

 

[dd_gamer]

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?

[DavidAM]

Yes, the apostrophe (A.K.A. single-quote mark) is causing the query string to end prematurely. In order to resolve this problem, you have to use the mysql_real_escape_string function when placing your values into the query string. For example:

 

$sql = "SELECT col1, col2 FROM table4 WHERE col3 = '" . mysql_real_escape_string($col3Value) . "'";

 

You should apply this function to all character fields in the query to protect yourself from SQL injections, and to allow the "special" characters.

[dd_gamer]

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.

[thomasw_lrd]

$sql = "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', mysql_real_escape_string($stud_book), '$studGL','$today' )";

[dd_gamer]

$sql = "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', mysql_real_escape_string($stud_book), '$studGL','$today' )";

 

When I used the above code this error message :

 

With error: FUNCTION mydatabase.mysql_real_escape_string does not exist

[dd_gamer]

GOT IT! Thank you all... just used the

 

mysql_real_escape_string($stud_book)

 

Before the insert code...