INSERT not working in PHP

[doubledee]

I am trying to do an INSERT on my "bio_answer" table which is a junction table in between the "member" and "bio_question" tables.

 

My PHP just tries to do the INSERT into "bio_answer" and doesn't touch the parent tables, which I assume is okay?!

 

Here is a snippet of my code...

if ($_SERVER['REQUEST_METHOD']=='POST'){
	// Form was Submitted (Post).

	// Initialize Errors Array.
	$errors = array();

	// Trim all form data.
	$trimmed = array_map('trim', $_POST);


	// ************************
	// Validate Form Data.	*
	// ************************

	// Validate Answer1.
	if (strlen($trimmed['answer01']) >= 2 && strlen($trimmed['answer01']) <= 1024){
			// Valid Answer1.
			$answerArray[0] = $trimmed['answer01'];
			$questionID = 1;


echo '<p>$memberID = ' . $memberID .  '</p>';			// Resolves to 19 which exists in the "member" table
echo '<p>$questionID = ' . $questionID .  '</p>';		// Resolves to 1 which exists in the "bio_question" table
echo '<p>$answerArray[0] = ' . $answerArray[0] . '</p>';	// Resolves to whatever I type in my form, e.g. "This is a test..."

	}else{
			// Invalid Answer1.
			$errors['question01'] = 'Answer must be 2-1024 characters.';
	}//End of VALIDATE ANSWER1


	// ******************************
	// Attempt to Create Thoughts.	*
	// ******************************
	if (empty($errors)){
		// Valid form data.

		// Build query.
		$q1 = "INSERT INTO bio_answer(member_id, question_id, response, created_on)
						VALUES(?, ?, ?, NOW())";

		// Prepare statement.
		$stmt1 = mysqli_prepare($dbc, $q1);

		// Bind variables to query.
		mysqli_stmt_bind_param($stmt1, 'iis', $memberID, $questionID, $answerArray[0]);

		// Execute query.
		mysqli_stmt_execute($stmt1);

		// Verify Insert.
		if (mysqli_stmt_affected_rows($stmt1)==1){
			// Insert Succeeded.
			$_SESSION['resultsCode'] = 'THOUGHTS_NEW_THOUGHTS_CREATED_2138';

		}else{
			// Insert Failed.
			$_SESSION['resultsCode'] = 'THOUGHTS_NEW_THOUGHTS_FAILED_2139';
		}//End of UPDATE MEMBER RECORD

		// Close prepared statement.
		mysqli_stmt_close($stmt1);

		// Set Error Source.
		$_SESSION['errorPage'] = $_SERVER['SCRIPT_NAME'];

		// Redirect to Display Outcome.
		header("Location: " . BASE_URL . "/members/results.php");

		// End script.
		exit();
	}//End of ATTEMPT TO CREATE THOUGHTS

 

 

My script keeps failing and errors to 'THOUGHTS_NEW_THOUGHTS_FAILED_2139'

 

What is wrong with my Script/SQL??

 

Thanks,

 

 

Debbie

 

[trq]

Have you looked at mysqli_error so you can get a proper error message?

[doubledee]

Have you looked at mysqli_error so you can get a proper error message?

 

The link you provided says the function doesn't exist.

 

I sorta solved my problem, so if you want to pull this thread go on ahead.

 

The problem was I was trying to INSERT a record that had identical keys to an existing record, which my "Unique Index" doesn't allow.

 

 

Debbie

 

[doubledee]

Have you looked at mysqli_error so you can get a proper error message?

 

The link you provided says the function doesn't exist.

 

I sorta solved my problem, so if you want to pull this thread go on ahead.

 

The problem was I was trying to INSERT a record that had identical keys to an existing record, which my "Unique Index" doesn't allow.

 

 

Debbie

 

That leads me to another question...

 

How do I handle things so I know whether I need to create a new record (INSERT) or just modify the existing record(s) (UPDATE)?

 

 

Debbie

 

[kicken]

For tables like that which pretty much just glue a couple other tables together, it is common to just delete all the old records and then insert the new ones.  If you have other columns you need to preserve then you could test with a SELECT before hand to see if the record exists first, then update if it does.

[Muddy_Funster]

You could have a look at this: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html, although I'm guessing this is an issue with the compound unique index that was brought up on the other thread, rather than on the the primary key, so I don't know that it will be a massive help on this occasion.  Another thing that you could do is expand the index to include a timestamp, that would auto populate, if you are looking to hold historical information about the number of times a question has been answered by a perticular member, and what those answers were, which you could then archive off however you see fit.

[litebearer]

Just a pre-coffee thought

update indicates the existence of a record to which one wants to add/modify.

it is good practice to populate an update form with any preexisting data

perhaps create a hidden field in the form with the id of the existing record

when processing the form, check for the value of the hidden form field, if=0 new record, if >0 update