I'm using MySQL with PHP and I need to do something like this (pseudocode):
if (sql row exists where username='bob')
{
// do this stuff
}
-
1What database driver are you using?alex– alex2011-07-08 04:36:28 +00:00Commented Jul 8, 2011 at 4:36
-
See PHP Manual Example #1 of mysql_fetch_assocbrady.vitrano– brady.vitrano2011-07-08 04:39:49 +00:00Commented Jul 8, 2011 at 4:39
-
Does this answer your question? How to check if a row exists in MySQL?Dharman– Dharman ♦2019-11-26 18:36:49 +00:00Commented Nov 26, 2019 at 18:36
Add a comment
|
3 Answers
If you would like to use PDO (PHP Data Object), then use the following code:
$dbh = new PDO("mysql:host=your_host_name;dbname=your_db_name", $user, $pass);
$stmt = $dbh->prepare("SELECT username from my_table where username = ':name'");
$stmt->bindParam(":name", "bob");
$stmt->execute();
if($stmt->rowCount() > 0)
{
// row exists. do whatever you want to do.
}
1 Comment
PhoneixS
Have in mind what the PHP documentation says about
rowCount(): "If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.". So check first if your database returns the correct number or not.Sayem's answer has the most upvotes, but I believe it is incorrect regarding PDO.
From the PHP docs:
For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned.
$sql = "SELECT COUNT(*) FROM fruit WHERE calories > 100";
$res = $conn->query($sql);
/* Check the number of rows that match the SELECT statement */
if ($res->fetchColumn() > 0) {
/* Issue the real SELECT statement and work with the results */
$sql = "SELECT name FROM fruit WHERE calories > 100";
foreach ($conn->query($sql) as $row) {
print "Name: " . $row['NAME'] . "\n";
}
}
/* No rows matched -- do something else */
else {
print "No rows matched the query.";
}
Comments
another approach
$user = "bob";
$user = mysql_real_escape_string($user);
$result = mysql_query("SELECT COUNT(*) AS num_rows FROM my_table WHERE username='{$user}' LIMIT 1;");
$row = mysql_fetch_array($result);
if($row["num_rows"] > 0){
//user exists
}
