Form Problem: Handling both INSERTs and UPDATEs

PHP
1

I need help with my “Thoughts” form… :frowning:

(What once seemed like a piece of cake is turning out to be extremely complicated in my mind…)

When a User registers on my website (i.e. Creates a Member Account), I just ask for basic information like…

  • username
  • email
  • password
  • first name

However, after their Member Account has been created, they can log back in and build a “Member Profile” which includes everything from Biographical Info, to uploading a Photo, to answering several open-ended Questions and thus publishing their “Thoughts” (on small-business).

If a Member clicks on the “change_details.php” script, it is simple, because all of the “Details” will be added to an existing record in the “member” table, so I am always doing an UPDATE.

But here comes the tricky part that I need help with…

If Member wants to deal with “Thoughts” then things are not as simple, because “Thoughts” reside in the “answer” table which is part of a many-to-many between the “member” and “question” tables.

I was going to build a “create_thoughts.php” script and then a “change_thoughts.php” script, but what is to stop a Member from trying to “change” Thoughts (records) that do not already exist, or what is to stop a Member from trying to “create” Thoughts (records) that already exist and thus creating an INSERT error?!

If a Member wants to answer Questions 1, 4, 5, and 9, then I would have to have a script intelligent enough to INSERT records for those Answers.

If a Member comes back and wants to change his/her “Thought” to Question 5, then my script has t be smart enough to do an UPDATE on that one record.

If a Member comes back and decides to answer Question 2, then my script needs to know that another INSERT is in order.

This is complicated as heck if you ask me?!

I need some help either coming up with a better workflow OR figuring out how to code this… :frowning:

Thanks,

Debbie

2

This may be the COMPLETELY wrong method to handle this, and I’d love to hear that if so, but what would the problems incurred be if your initial INSERT created blank values (or some default value that you could test for later?). Then all tables would be complete and simple UPDATE statements would be required after that. Or, when they launch the form, it could do a quick query against the database, IF a value already exists in that table for that user then an UPDATE is performed, if not an INSERT is given. Then it’s simply a toggle between whether the SQL should use UPDATE or INSERT (and the appropriate WHERE clause at the end there too of course).

Like I said, I’m such a rookie in this higher end coding, but figure the best place to learn is to offer my suggestions and then be fed the logic as to why I am wrong. (-;

Greg

3

I was thinking something similar, but my gut tells me that is wrong.

The problem is that instead of dealing with One Record with 10 Answers in it, I am stuck with 10 Records each representing One Answer?!

I think my lack of knowing how to take my limited repertoire of mysqli Prepared Statement syntax and do things like working with Loops and Array is part of the problem…

For example, how would I convert this code so that when 2 records are returned, I can place each field value into an Array Variable…


		// ******************
		// Populate Form.		*
		// ******************

		// Build query.
		$q2 = "SELECT response
				FROM bio_answer
				WHERE member_id=?";

		// Prepare statement.
		$stmt2 = mysqli_prepare($dbc, $q2);

		// Bind variable to query.
		mysqli_stmt_bind_param($stmt2, 'i', $memberID);

		// Execute query.
		mysqli_stmt_execute($stmt2);

		// Store results.
		mysqli_stmt_store_result($stmt2);

		// Check # of Records Returned.
		if (mysqli_stmt_num_rows($stmt2)>0){
			// Details Found.

			// Bind result-set to variable.
			mysqli_stmt_bind_result($stmt2, $response);

			// Fetch record.
			mysqli_stmt_fetch($stmt2);

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

		}else{
			// Details Not Found.
			$_SESSION['resultsCode'] = 'DETAILS_NOT_FOUND_2133';

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

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

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

			// End script.
			exit();
		}//End of POPULATE FORM

**I do NOT know how to do PDO or OOP, but I am sure there is a way to work with the mysqli prepared statements above and maybe use a Loop and an Array to capture values so I get something like this…


$answerArray[0] = 'I want to start my own business because I want to be my own boss!'

$answerArray[1] = 'My advice is to hire a good accountant...'

This entire undertaking is actually a very complicated, but if I can at least learn how to add Looping and Arrays into my Prepared Statements, then I will have a slightly better chance of figuring things out…

Can anyone help me out here?

Thanks,

Debbie