Mysql, IF EXIST UPDATE ELSE INSERT not working

General PHP Help
#1

Hello,
i have app, where user login and can check messages… Ive added “user block” but when app send data to srv app get " <br / > " on response…
Here is code:

<?php

if($_SERVER['REQUEST_METHOD']=='POST'){

include 'DatabaseConfig.php';

 $con = mysqli_connect($HostName,$HostUser,$HostPass,$DatabaseName);

 $uniqueid = $_POST['UniqueID'];
 $userid = $_POST['UserID']; 
 $blockstatus = $_POST['BlockStatus'];



$result = Sql_Query("SELECT * FROM blocks WHERE unique_id = '$uniqueid' AND user_id = '$userid'");

if( mysql_num_rows($result) > 0) {
Sql_Query("UPDATE blocks SET unique_id = '$uniqueid', user_id = '$userid', status = '$blockstatus'");
}
else
{
Sql_Query("INSERT INTO blocks SET unique_id = '$uniqueid', user_id = '$userid', status = '$blockstatus'");
}


 if(mysqli_query($con,$Sql_Query))
{
 echo 'Success';
 }
else
{
 echo 'Error';
 }
 }
 mysqli_close($con);
?>
#2

There’s almost nothing about your post above that makes sense (the only useful thing in the code, is it is testing if a post method form was submitted.)

A list -

  1. The title mentions a mysql php connection error. The code is trying to make a mysqli connection, not a mysql connection. What exact error are you getting that leads you to believe there’s a connection error?

  2. There’s no apparent user permission system, so anyone can submit ‘user block’ data. You probably want to restrict this code so that it only processes data if the currently logged in user is a moderator/administrator on your site.

  3. You need to have error handling for all the database statements (connection, query, prepare, execute - see below for prepare/execute.) The easiest way of doing this is to use exceptions and in most cases let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information.

  4. The UniqueID value doesn’t seem to have any purpose in this process, and since there are no comments in the code, what exactly is this there for? Storing the user_id and the block/status value is all you would seem to need.

  5. Don’t copy variables to other variables without a good reason. This just clutters up your code and makes more things you have to keep track of. Either just use the original $_POST variables in the rest of the code, or if you do have a good reason to make a copy of the posted data, such as trimming all the data, operate on the data as a set using php array function(s) and place the copy of the data in an array variable, not discrete variables.

  6. You need to validate the input data before using it. At a minimum, you must test if ‘required’ data is not empty. If you store validation errors in an array, you can use the array as an error flag. If the array is empty, there are no errors. If the array is not empty, there are errors.

  7. You are calling a user written Sql_Query() function that apparently uses mysql_ statements. These are not compatible with the mysqli connection you are making in the code and the mysql_ extension is obsolete and has been removed from the latest php versions. All the database statements must be from the same php extension. If you want help with a user written function in your code, you will need to post the function definition.

  8. In order to INSERT new data or UPDATE existing data, you don’t need to first SELECT the data. There’s a single query - INSERT … ON DUPLICATE KEY UPDATE … you can use.

  9. The UPDATE query you do have in your code will update all the rows in the table because it is lacking a WHERE clause. You would want to update only the row WHERE user_id = the_submitted_userID_value.

  10. You should not put data values directly into the sql query statement. Use a prepared query, with place-holders for the data values, then supply the data when you execute the query.

  11. There is no $sql_query variable being set in the posted code, so calling mysqli_query() using it would be producing an error and won’t execute any sql query.

  12. Php automatically closes the database connection when the script ends, so there is no need for you to have code to do this. Also, you have the close statement after the end of the statement testing if a post method form was submitted, so if this page does get requested when there is no post data, the code will try to close a non-existent connection and a php error will be thrown.

2 Likes
#3

Every user can block another user to not get messages from him.
UniqueID is id of user who is blocking (for example me) another user (UserID), its needed to get response if user is blocked by me.

#4

Ive edited this:

Sql_Query("insert into blocks (unique_id, user_id, status) VALUES ('$uniqueid', '$userid', '$blockstatus') on duplicate key update set status ='$blockstatus'")

But it doesnt working :confused:

#5

And i have to tell that when im inserting data without checking if there is row with these values, its working:

  Sql_Query("INSERT INTO blocks SET unique_id = '$uniqueid', user_id = '$userid', status = '$blockstatus'");

Im just trying to not create new row for every “block operation”, just update if exist…

#6

Then the UniqueID should come from your login code’s session variable, not through the form. By passing the data through the form, anyone can submit data that blocks someone else. A hacker could submit all combinations of UnieuwID and UserID and block everyone against everyone else.

And speaking of blocking anyone. Your logic needs to make sure that the submitted UserID is not the same as the currently logged in user.

#7

That’s because the syntax has an error in it, which you would know if you had error handling (one of the points that was made that this code needs.) It’s also likely that no query is going to work due to the apparent use of mysql_ statements.

If you want to learn the correct syntax for this query, consult the mysql database server documentation.

#8

Prepared statements…

Add a unique composite key to the table.

You shouldn’t need to do an update. You either insert the block, or remove the validity of the record.

#9

If the status value only has two choices - blocked or not-blocked, all you need to do is INSERT a row with the UniqueID and UserID to indicate the blocked state and DELETE the row to un-block a user.

If you set up the unique_id and user_id columns as a unique composite index, when you try to INSERT a row that already exists, the row won’t be inserted and you will get a duplicate key error, which your code can catch and produce a user error message for, if you want. If you want to silently ignore the duplicate key error, add the IGNORE keyword to the query. Your user interface for blocking users should only display non-blocked users. A user interface for managing blocked users would display the currently blocked users.

Sponsor our Newsletter | Privacy Policy | Terms of Service