Is it possible to delete row using WHERE fields that are all foreign keys?

[sabinmash]

For example:

DELETE FROM user_question_answer WHERE user_id = 1 AND question_id = 7 AND `answer_id` = 3

 

This sql query has no error, but reports deleting 0 rows.

 

user_id and question_id fields are both foreign keys in this table.  Could that be why it's not working, yet not reporting an error?

 

Thank you for your time.

[Pandemikk]

Foreign keys to what? I don't think that's even relevant information.

 

Anyway, what you're asking is to delete rows with a user_id of 1, question_id of 7 and answer_id of 3. If a row(s) with all those values exists, it will be deleted.

[floridaflatlander]

I'm not sure what you're asking, you can use any column for the WHERE clause in the delete.

 

I put my deletes in single quotes like WHERE user_id = '1' or something like WHERE user_id ='$user_id'

 

Are you sure there is  a record WHERE user_id = 1 AND question_id = 7 AND answer_id = 3

 

Why does your " AND `answer_id` " have quotes?

 

Have you tried " or die("Error: ".mysqli_error($dbc)); " to see if there is a specific error.

 

Also use LIMIT 1 if you're deleting just one row.

[Pandemikk]

I don't recommend you wrap integers in single quotes^. If you are unsure if your variable is an integer you should sanitize it.

 

There's no reason to use LIMIT 1. How could he possibly know he's deleting the right row that way?

[sabinmash]

Oh god. I apologize. it seems I asked the wrong (silly) question.  The query works, so i must have made a mistake when testing it.  The code using this DELETE doesn't not work however, and I believe it has something to do with the syntax of the SQL in PHP.

 

function data_retrieve($get_query){
$conn = db_connect();
$query =  $get_query;
$result = $conn->query($query);
return $result;
}

function delete_user_answer_query($table, $field1, $field1_value, $field2, $field2_value) {   
//$sql =  "DELETE FROM user_question_answer WHERE user_id     =           1            AND   question_id   =         7            ";		
$query = "DELETE FROM " . $table . " WHERE  " . $field1 . " = " . $field1_value . "  AND " . $field2 . " = " . $field2_value;
return $query;
}

function get_query_row($delete_user_answer_query) {
$query = $delete_user_answer_query;
$result = data_retrieve($query);
 while ($row = mysqli_fetch_array($result))
{
	echo $row[0];
	return $row[0];
}
}

session_start();

echo delete_user_answer_query('user_question_answer', 'user_id', $_SESSION['user_id'], "question_id", 7);

$delete_user_answer_query = delete_user_answer_query('user_question_answer', 'user_id', $_SESSION['user_id'], "question_id", 7);

get_query_row($delete_user_answer_query);

$question_id = get_question_id(query_question_id(7));

 

This code echoes : DELETE FROM user_question_answer WHERE user_id = 1 AND question_id = 7  .  Which works fine when i tested it in directly in the database.

 

this prints along with the error: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\Alumni Survey Site\survey_queries.php on line 52.  Which is where it reads: " while ($row = mysqli_fetch_array($result))"

[Pandemikk]

That's a big mess. Why are you over complicating it with functions? There's no need for it and it just looks sloppy.

 

And you're getting that error because you're using mysqli_fetch_array on a delete query. Why the #$% are you doing that?

 

 

[sabinmash]

Well the short answer is, cause I don't know any better.  PHP noob, and general programming noob.  Still have yet to take a design methods class. 

 

Thank you about using the using mysqli_fetch_array on a delete query, I completely did not realize.  I was trying to use some previously written code from SELECT query and didn't realize that a DELETE would not return anything at first since its not selecting something.