I have a mysql table, I want to check if a row exists where columnA=$a and $columnB=$b. I dont need to select anything from there. What should be efficient query for that? Currently Im doing like,
if ($stmt = mysqli->prepare("SELECT * FROM TABLE WHERE columnA=? && columnB= ? LIMIT 1")) {
$stmt->bind_param("ss", $a, $b);
$stmt->execute();
$stmt->store_result();
$count=$stmt->num_rows;
$stmt->close();
}
return ($count > 0 ? true : false);
I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.
Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.
<?php
$db = new mysqli('localhost', 'user', 'pass', 'test');
$start = microtime(true);
$a = 'a';
$b = 'b';
$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
$a = chr($i % 1);
$b = chr($i % 2);
$sql->execute();
}
$sql->close();
echo microtime(true) - $start;
$db->close();
?>
This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).
If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.
You can make $argument pass-by-reference:
function if_exist(&$argument, $default="") {
if(!isset($argument)) {
$argument = $default;
return false;
}
return true;
}
DEMO
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).
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.";
}
}
?>
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.
Note: <input type = "text" name = "var"> - $_POST['var'] match. $_POST['Var'] no match.
Consult:
Error checking references:
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).
Consult the following about this:
If you are using the mysql_ API and have no choice to work with it, then consult the following Q&A on Stack:
The mysql_* functions are deprecated and will be removed from future PHP releases.
You can also add a UNIQUE constraint to (a) row(s).
References:
Try with this:
Now you should be able to get it done