SQL update/insert result keeps returning false

[sabinmash]

Does anyone see anything wrong with the SQL?  This function keeps returning false.

 

Thank you for your time.

 

function insert_survey1($survey1_anwers) {
  // extract order_details out as variables
  extract($survey1_anwers);
  
  $username = $_SESSION['valid_user'];
  $conn = db_connect();

  // select user_id based on the entered data			
  $query = "SELECT user_id FROM user WHERE
gender 		= '".$gender."',
birth_range = '".$birth_range."',
degree_year = '".$degree_year."'
username 	= '".$username."'";

  $result = $conn->query($query);

  //if the resulting user_id exists (AND has the POST data just entered, since the query must match those to return a row) , 
  if($result->num_rows>0) {
    //return the current row of the result set as an object and place it into user variable
    $user = $result->fetch_object();
//set variable for user_id to the fetched user object data representing the user_id int he db( because it was fetched from the sql select result)
    $user_id = $user->user_id; 
//update the fields since the user exists already.
$query = "UPDATE user
			SET gender 			= '".$gender."',
				birth_range 	= '".$birth_range."',
				degree_year 	= '".$degree_year."'
			WHERE user_id = '".$user_id."'";

// $query = "UPDATE `alumni_survey`.`user` 
	// SET `gender` 		= '".$gender."', 
		// `birth_range` 	= '".$birth_range."', 
		// `degree_year` 	= '".$degree_year."' 
	// WHERE `user`.`user_id` = '".$user_id"'";
  } else {
//otherwise, if the user doesn't exist already insert this new data.
    $query = "INSERT INTO user (gender, birth_range, degree_year)
		VALUES('".$gender."','".$birth_range."','".$degree_year."') 
		WHERE user_id = '".$user_id."'";
    $result = $conn->query($query);

    if (!$result) {
       return false;
    }
  }
  
  return $user_id;
}

[ManiacDan]

if you're getting a mysql error, printing mysql_error() might help, since that's how you find the error.

 

You cannot have a WHERE in an INSERT query.

[sabinmash]

Thank fyou for the sql WHERE advice.  It didn't fix the problem however.  Well I am still getting "Could not story data" from this code here:

 

  if (($gender) && ($birth_range) && ($degree_year)) {
    // able to insert into database
    if(insert_survey1($_POST) != false ) { 
      header("Location: survey2.php");
  
    } else {
      echo "<p>Could not store data, please try again.</p>";
     
    }
  } else {
    echo "<p>You did not fill in all the fields, please try again.</p><hr />";
  }

[sabinmash]

Come to think of it, i don't really know how i can properly troubleshoot this situation myself.  Does a "die(mysql_error()"  or something similar, belong after where the insert_survey1() function is used?  Or inside that function itself? 

[ManiacDan]

There's a number of problems with this code, I'll try to tackle what I see:

 

- don't use extract(), it's poor practice and leads to confusion.  Access the variable keys directly in the array.

 

- Good lord fix the spelling of your variables.  One day you'll spell "answer" right for the first time in your life and the code won't work and you'll yank your hair out trying to figure out why.  If someone ELSE tries to work on this code, they might never find your body.

 

-  if (($gender) && ($birth_range) && ($degree_year)) {  <-- You might want to be using !empty($gender) (etc)

 

- $result in this code will be false if your query failed.  If it's false, echo mysql_error() and die.  There may be an error function for your db object, especially if you're using the built in one.

 

- You must die directly after a header command.

 

- You must use mysql_real_escape_string on any string variables passed from the user that will be used in a query.

 

- Your code structure and indentation are good, this code is easy to read.  For even better readability, use [ PHP ] instead of [ CODE ] tags, that will color your code.

 

-Dan

[Pikachu2000]

I'll add one thing that stands out as a problem:

 

// Commas aren't valid in the where clause. Should be using AND or OR.
$query = "SELECT user_id FROM user WHERE
gender 		= '".$gender."',
birth_range = '".$birth_range."',
degree_year = '".$degree_year."'
username 	= '".$username."'"; 

[sabinmash]

Thanks for the advice, and double thanks for making me laugh out loud while learning (and not being too harsh either lol)!

 

OK I reworked this whole thing.  Turns out no user would be using this survey unless they were already given a username and password, so there is only a need for updating, not inserting.  And it works fine. 

 

This code below also commented out the extract function and accesses the $_POST variables directly.  But i don't think this is what you mean by "Access the variable keys directly in the array".  I tried using this for example: $survey_answers[$gender], but blank data was updated into the db.

 

At the same time, I am trying to push this further and make this query modular for other survey pages.  I don't really know if that's possible here though.  Google examples  I have found are a bit over my head at this time, so maybe I will just pre-make the other page's query's and ad them into this function with a 2nd parameter that define's the update query.

 

function insert_survey($survey_answers) {
  // extract order_details out as variables
  //extract($survey_answers);
  
  $username = $_SESSION['valid_user'];
  
  $conn = db_connect();

  // select user_id based on the entered data				
  $query = "SELECT user_id FROM `user` WHERE 
  `username` = '".$username."'";
  
  $result = $conn->query($query);

  //if the resulting user_id exists (AND has the POST data just entered, since the query must match those to return a row) , 
  if($result->num_rows>0) {
    //return the current row of the result set as an object and place it into user variable
    $user = $result->fetch_object();
//set variable for user_id to the fetched user object data representing the user_id int he db( because it was fetched from the sql select result)
    $user_id = $user->user_id; 
//update the fields since the user exists already.


$query = "UPDATE `alumni_survey`.`user` 
SET `gender` = '".$_POST['gender']."',
	`birth_range` = '".$_POST['birth_range']."',
	`degree_year` = '".$_POST['degree_year']."'
WHERE `user`.`user_id` = '".$user_id."'"; 

// $query = "UPDATE `alumni_survey`.`user` 
	// SET `gender` = '".$gender."',
		// `birth_range` = '".$birth_range."',
		// `degree_year` = '".$degree_year."'
	// WHERE `user`.`user_id` = '".$user_id."'"; 

$result = $conn->query($query);

if (!$result) {
       return false;
    }
  } 
  
  return $user_id;
}

[sabinmash]

I just tried this, but unfortunately nothing is updated:

 

The query is put into insert_survey() as a parameter.

 
  $survey1_query = get_survey_query1();
  
  // Check that the user filled out the form and inserted details into the database 
  if (($gender) && ($birth_range) && ($degree_year)) {
    // able to insert into database
    if(insert_survey($_POST, $survey1_query) != false ) { 
// echo $survey1_query;
// exit;
     header("Location: survey2.php");
  
    } else {
      echo "<p>Could not store data, please try again.</p>";

 

The insert inside insert_survey($survey_answers, $sql)  :

$query = $sql;

$result = $conn->query($query);

 

And here is the function I am getting it from.  I dont get any "Could not store data" error, the query seems to go through.  I'm not sure what's wrong.  Thanks again for you help.

function get_survey_query1() {
$survey1_query = "UPDATE `alumni_survey`.`user` 
	SET `gender` = '".$_POST['gender']."',
		`birth_range` = '".$_POST['birth_range']."',
		`degree_year` = '".$_POST['degree_year']."' 
		WHERE `user`.`user_id` = '".$user_id."'"; 

	return $survey1_query;
}

 

[Pikachu2000]

I'm betting $user_id isn't available within the function, since you haven't explicitly passed it as an argument. Echo the query string and make sure it contains the values you'd expect it to contain. If it does, execute it in phpMyAdmin and see if it succeeds or not.