Trying to check if block_lot_no is already existing using prepared statements

[qwerty]

$select = mysqli_query($db, "SELECT * FROM property WHERE block_lot_no = '".$_POST['block_lot_no']."' LIMIT 1" );
	if(mysqli_num_rows($select)) {
	    array_push($errors,"This Block / Lot No already exists");
	}

	$imgData 		 = file_get_contents($filename);
	$imageProperties = getimageSize($filename);
	$status 		 = 'unverified';
	$status1		 = 'unpaid';
		
	$sql ="INSERT INTO property (full_name,property_type, location, block_lot_no, imageType, imageData, status,status1,tax_payer_id) 
		VALUES (?,?,?,?,?,?,?,?,?)";
	
	$query = $db->prepare($sql);		
	$query->bind_param("ssssssssi",$_REQUEST['full_name'] ,$_REQUEST['property_type'], $_REQUEST['location'], $_REQUEST['block_lot_no'], $imageProperties['mime'], $imgData, $status,$status1, $_SESSION['id']); 		
	$query->execute();
	$current_id = $query->insert_id;

I’m trying to check if the block_lot_no is already existing in my database but my code can only display the error message and still create a new data in mydatabase.

[Thallius]

Maybe you should use if/else then?

There are so many problems with your code just a short list

• never use SELECT *. Always select only the columns you need.
• never use a post or get parameter directly in a sql query. I can call your script with postman setting a post variable block_lot_no = “1‘;DROP TABLE property;‘” And your database table with all its data is deleted.
• if block_lot_no must be unique in the table, why don’t you use it as primary key? Then you do not need to check if it already exists but can use INSERT IGNORE or INSERT ON DUPLICATE KEY UPDATE

[qwerty]

I already have a primary key in my database and I don’t want my block_lot_no with same value.

[mabismad]

The current method, of executing a SELECT query followed by an INSERT query, even if you fix the conditional logic, is not concurrent-safe. If two ore more instances of your script are ever running at the same time, they can all find that the block_lot_no value doesn’t exist and try to insert the same value. This will either produce multiple rows with the same value or produce a duplicate error, depending on how block_lot_no is defined in your table.

To do this, simply, and to prevent duplicates, you would define block_lot_no as a unique index in the table, just attempt to insert the data, then detect if a duplicate index error occurred. If the block_lot_no does not already exist, the row will be inserted. If the block_lot_no is already in the table, you will get a duplicate index error (error number 1062) that you would use the setup the “This Block / Lot No already exists” message for the user.

[qwerty]

so how can I do it if the block_lot_no is already in the table?

[mabismad]

In the error handling for the INSERT query, which you currently don’t have, if there is an error, you would test if it is the duplicate index error number.

You ALWAYS need error handling for statements that can fail. For database statements that can fail - connection, query, prepare, and execute, the simplest way of adding error handling is to use exceptions for errors and in most cases just let php catch and handle the error, where php will use its error related settings to control what happens with the actual error information, via an uncaught exception error (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) The exception to this rule are for user recoverable errors, such as when inserting/updating duplicate or out of range data. In this case, your code would catch the exception, test if the error number is for something your code is designed to handle, then setup a message for the user telling them what exactly was wrong with the submitted data. For all other error numbers, just re-throw the exception and let php handle it.

[qwerty]

anyway thanks.