Check if a row exists using old mysql_* API
This ought to do the trick: just limit the result to 1 row; if a row comes back the $lectureName
is Assigned, otherwise it's Available.
function checkLectureStatus($lectureName)
{
$con = connectvar();
mysql_select_db("mydatabase", $con);
$result = mysql_query(
"SELECT * FROM preditors_assigned WHERE lecture_name='$lectureName' LIMIT 1");
if(mysql_fetch_array($result) !== false)
return 'Assigned';
return 'Available';
}
Best way to test if a row exists in a MySQL table
You could also try EXISTS
:
SELECT EXISTS(SELECT * FROM table1 WHERE ...)
and per the documentation, you can SELECT
anything.
Traditionally, an EXISTS subquery starts with SELECT *, but it could
begin with SELECT 5 or SELECT column1 or anything at all. MySQL
ignores the SELECT list in such a subquery, so it makes no difference.
How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
The following are tried, tested and proven methods to check if a row exists.
(Some of which I use myself, or have used in the past).
Edit: I made an previous error in my syntax where I used mysqli_query()
twice. Please consult the revision(s).
I.e.:
if (!mysqli_query($con,$query))
which should have simply read as if (!$query)
.
- I apologize for overlooking that mistake.
Side note: Both '".$var."'
and '$var'
do the same thing. You can use either one, both are valid syntax.
Here are the two edited queries:
$query = mysqli_query($con, "SELECT * FROM emails WHERE email='".$email."'");
if (!$query)
{
die('Error: ' . mysqli_error($con));
}
if(mysqli_num_rows($query) > 0){
echo "email already exists";
}else{
// do something
}
and in your case:
$query = mysqli_query($dbl, "SELECT * FROM `tblUser` WHERE email='".$email."'");
if (!$query)
{
die('Error: ' . mysqli_error($dbl));
}
if(mysqli_num_rows($query) > 0){
echo "email already exists";
}else{
// do something
}
You can also use mysqli_
with a prepared statement method:
$query = "SELECT `email` FROM `tblUser` WHERE email=?";
if ($stmt = $dbl->prepare($query)){
$stmt->bind_param("s", $email);
if($stmt->execute()){
$stmt->store_result();
$email_check= "";
$stmt->bind_result($email_check);
$stmt->fetch();
if ($stmt->num_rows == 1){
echo "That Email already exists.";
exit;
}
}
}
Or a PDO method with a prepared statement:
<?php
$email = $_POST['email'];
$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_password = 'xxx';
$mysql_dbname = 'xxx';
try {
$conn= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
exit( $e->getMessage() );
}
// assuming a named submit button
if(isset($_POST['submit']))
{
try {
$stmt = $conn->prepare('SELECT `email` FROM `tblUser` WHERE email = ?');
$stmt->bindParam(1, $_POST['email']);
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
}
}
catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
if($stmt->rowCount() > 0){
echo "The record exists!";
} else {
echo "The record is non-existant.";
}
}
?>
- Prepared statements are best to be used to help protect against an SQL injection.
N.B.:
When dealing with forms and POST arrays as used/outlined above, make sure that the POST arrays contain values, that a POST method is used for the form and matching named attributes for the inputs.
- FYI: Forms default to a GET method if not explicity instructed.
Note: <input type = "text" name = "var">
- $_POST['var']
match. $_POST['Var']
no match.
- POST arrays are case-sensitive.
Consult:
- http://php.net/manual/en/tutorial.forms.php
Error checking references:
- http://php.net/manual/en/function.error-reporting.php
- http://php.net/manual/en/mysqli.error.php
- http://php.net/manual/en/pdo.error-handling.php
Please note that MySQL APIs do not intermix, in case you may be visiting this Q&A and you're using mysql_
to connect with (and querying with).
- You must use the same one from connecting to querying.
Consult the following about this:
- Can I mix MySQL APIs in PHP?
If you are using the mysql_
API and have no choice to work with it, then consult the following Q&A on Stack:
- MySql php: check if Row exists
The mysql_*
functions are deprecated and will be removed from future PHP releases.
- It's time to step into the 21st century.
You can also add a UNIQUE constraint to (a) row(s).
References:
- http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
- http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
- How to check if a value already exists to avoid duplicates?
- How add unique key to existing table (with non uniques rows)
How to compare MySql data exist in php
Try this way:-
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if (isset($_POST['btnReg'])) {
$user2=mysqli_real_escape_string($conn, $_POST["reg_user"]);
// mysqli_real_escape_string is used instead of mysql_real_escape_string
$sql = "SELECT * FROM accounts WHERE Username = '$user2' LIMIT 1";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo '<div class="alert alert-danger"><strong>Username already exists</strong></div>';
}
else {
echo '<div class="alert alert-success"><strong>Sign Up Success .</strong></div>';
}
}
$conn->close();
Check if MySQL table exists or not
Updated mysqli version:
if ($result = $mysqli->query("SHOW TABLES LIKE '".$table."'")) {
if($result->num_rows == 1) {
echo "Table exists";
}
}
else {
echo "Table does not exist";
}
Original mysql version:
if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$table."'"))==1)
echo "Table exists";
else echo "Table does not exist";
Referenced from the PHP docs.
Differentiate between 'no rows were affected' and rows succesfully UPDATEd--to same value (MySQL and PHP)
A simple solution would be two queries.
First, run a SELECT query to check if the row exists using mysqli_num_rows().
Then, if the row exists, you can run the UPDATE query and use mysqli_affected_rows().
[EDIT]
...I'll suggest a potential alternative for anyone seeking out a single call. I don't know if you are interested in doing any INSERTs, or purely UPDATEs. Below is some food for thought:
From the top comment @ http://php.net/manual/en/mysqli.affected-rows.php :
On "INSERT INTO ON DUPLICATE KEY UPDATE" queries, though one may expect affected_rows to return only 0 or 1 per row on successful queries, it may in fact return 2.
From Mysql manual: "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated."
See: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
Here's the sum breakdown per row:
+0: a row wasn't updated or inserted (likely because the row already existed, but no field values were actually changed during the UPDATE)
+1: a row was inserted
+2: a row was updated
Can you make that suit your needs?
Related Topics
Efficiently Pick N Random Elements from PHP Array (Without Shuffle)
Decrementing Alphabetical Values
Unit Testing and Static Methods
PHP What Is the Best Approach to Using Xml? Need to Create and Parse Xml Responses
Could Not Open Input File: Artisan
How Follow the Don't Repeat Yourself Principle When Consuming My Own Laravel API
PHP Array Syntax Parse Error Left Square Bracket "["
Can't Return a Result Set in the Given Context
PHP Share Variable Among Different Users/Sessions
How to Decode This JSON String
Cakephp-3.X: How to Change the Data Type of a Selected Alias
How to Write a Stored Procedure in PHPmyadmin
What Does Using a Single Pipe '|' in a Function Argument Do
Continue Processing After Closing Connection