how to check if a record exist in a database before inserting with php

Question

I am creating a form that collects data and sends to a database using php, with a code snippet i got online.

$con=mysqli_connect("localhost","root","famakin","k");
if(mysqli_connect_errno())
{
   echo"FailedtoconnecttoMySQL:".mysqli_connect_error();
}
$sql="INSERT INTO transactions(Username,Insured_Name,combined,Residential_Address,Telephone,Email,Make_Of_Car,Model,Engine_Number,Year_Of_Manufacture,Chassis_Number,Vehicle_Class,Colour,Registeration_Number,Product_Type,Premium,Policy_Number,Start_Date,Expiry_Date,Date_Begin,Type_Of_Insurance,Status,  Transaction_id)VALUES('$_POST[Username]','$_POST[Insured_Name]','$_POST[combined]','$_POST[Residential_Address]','$_POST[Telephone]','$_POST[Email]','$_POST[Make_Of_Car]','$_POST[Model]','$_POST[Engine_Number]','$_POST[Year_Of_Manufacture]','$_POST[Chassis_Number]','$_POST[Vehicle_Class]','$_POST[Colour]','$_POST[Registeration_Number]','$_POST[Product_Type]','$_POST[Premium]','$_POST[Policy_Number]','$_POST[Date]','$_POST[Date_Expiry]','$_POST[Date_Begin]','$_POST[Type_Of_Insurance]','$_POST[Status]','$_POST[Transaction_id]')";
if(!mysqli_query($con,$sql))
{
   die('Error:'.mysqli_error($con));
}
mysqli_close($con);

This works for inserting details into the database,but i want to check if for example the username in which i am inserting into the database exists,please how do i go about this with what i have already?

regards

You should read up on slq injection and prepared statements. — jeroen, Oct 13 '14 at 14:01
If you make the username in the table unique.. Then the value won't get inserted.. — Naruto, Oct 13 '14 at 14:02
There are many ways to do this. What did Google say when you put in "check if user exists in database"? — Funk Forty Niner, Oct 13 '14 at 14:05
possible duplicate of [How to check if user already exists in MySQL with PHP](http://stackoverflow.com/questions/17465468/how-to-check-if-user-already-exists-in-mysql-with-php) — showdev, Oct 13 '14 at 16:49

score 4 · Answer 1 · answered Oct 13 '14 at 14:01

There are two main approaches, essentially...

SELECT from the database before trying to INSERT. If the record is found by the SELECT, don't perform the INSERT and instead respond to the user accordingly.
Place a UNIQUE constraint on the column (or set of columns) which needs to be unique in the table. This would cause the INSERT to fail, and the code would have to catch and handle that failure and respond to the user accordingly.

The second option puts the responsibility squarely on the database itself, which is important if anything else if ever going to use that database and needs to maintain that same responsibility.

Also, and this is important, please note that your code is open to SQL injection attacks, which allows users to execute their own code on your server. You'll want to read up on that so you can protect your application.

score 4 · Answer 2 · edited May 23 '17 at 10:33

Here, you can do it via mysqli_num_rows():

$username = mysqli_real_escape_string($con, $_POST['Username']);
$check_select = mysqli_query("SELECT * FROM `transactions` WHERE Username = '$username'"); 

$numrows=mysqli_num_rows($check_select);

if($numrows > 0){

// do something

}

else{

// do something else

}

Although there are other ways to do this, it is but one example.

You can avoid this by also setting your column(s) as UNIQUE.

By the way, your present code is open to SQL injection.
Use prepared statements, or PDO with prepared statements, they much safer.

score 2 · Answer 3 · answered Oct 13 '14 at 14:00

2

Just do a SELECT query before the INSERT. If a record with that username exists then don't insert the record.

answered Oct 13 '14 at 14:00

danmullen

2,556
3
20
28

how do i go abt that, am a newbie please – stack stack Oct 13 '14 at 14:02
@stackstack `select count(*) from Transactions.... where Username =..`. – AStopher Oct 13 '14 at 14:44
can u be so kind to do an example with the code i have provided – stack stack Oct 13 '14 at 14:51

score 1 · Answer 4 · edited Oct 13 '14 at 15:03

Well before you insert one you want to query for it's existence (please refer to Google on how to "Select data from Database PHP").

If that select count(*) from Transactions.... where Username =.. returns something other than 0 the username is already taken.

Note: I have bigger concerns about the fact you include POST-Parameters directly into your SQL-Query string and recommend you read something about "SQL Injection PHP".

how to check if a record exist in a database before inserting with php

4 Answers4