Check if a row exists using old mysql_* API

Question

I just want to check and see if a row exists where the $lectureName shows. If a row does exist with the $lectureName somewhere in it, I want the function to return "assigned" if not then it should return "available". Here's what I have. I'm fairly sure its a mess. Please help.

function checkLectureStatus($lectureName)
{
 $con = connectvar();
 mysql_select_db("mydatabase", $con);
 $result = mysql_query("SELECT * FROM preditors_assigned WHERE lecture_name='$lectureName'");
  while($row = mysql_fetch_array($result));
  {
     if (!$row[$lectureName] == $lectureName)
     {
         mysql_close($con);
         return "Available";
     }
      else
     {
        mysql_close($con);
        return "Assigned";
    }
}

When I do this everything return available, even when it should return assigned.

Please don't type queries like that because you have the problem of SQL injection. This means that an attacker can easily manipulate your database (http://php.net/manual/de/security.database.sql-injection.php). To avoid it see http://at2.php.net/manual/de/function.mysql-real-escape-string.php for some examples. — Johannes Egger, Mar 31 '12 at 05:23
Ahh sorry, seems like I copied the wrong links, just replace 'de' with 'en' within the URL. — Johannes Egger, Feb 23 '14 at 19:24
possible duplicate of [Best way to test if a row exists in a MySQL table](http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table) — atiquratik, Mar 16 '15 at 06:07
Just a small update for the post and the answers: mysqli_query should be used, not mysql_query. [mysqli_query](http://php.net/manual/en/mysqli.query.php) — Spas, Dec 10 '12 at 10:36
Does this answer your question? [How to check if a row exists in MySQL?](https://stackoverflow.com/questions/22252904/how-to-check-if-a-row-exists-in-mysql) — Dharman, Nov 26 '19 at 18:29

kijin · Answer 1 · 2012-03-31T05:31:52.040

33

Easiest way to check if a row exists:

$lectureName = mysql_real_escape_string($lectureName);  // SECURITY!
$result = mysql_query("SELECT 1 FROM preditors_assigned WHERE lecture_name='$lectureName' LIMIT 1");
if (mysql_fetch_row($result)) {
    return 'Assigned';
} else {
    return 'Available';
}

No need to mess with arrays and field names.

edited Mar 31 '12 at 05:31

answered Mar 31 '12 at 05:12

kijin

8,702
2
26
32

2

mysql_real_escape_string has nothing to do with security. Nevertheless it has to be always used to escape every string put into query. But it is usage is limited to quoted strings only – Your Common Sense Mar 31 '12 at 05:40
Just FYI, mysql_fetch_row returns a single row, thus no need for LIMIT 1. – Vincent May 06 '16 at 16:41

score 25 · Accepted Answer · answered Mar 31 '12 at 05:13

25

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';
}

answered Mar 31 '12 at 05:13

quickshiftin

66,362
10
68
89

Another thing that occurs to me is it would be a good idea to mark the `lecture_name` column as `UNIQUE` if there is only supposed to be one entry per lecture name (which seems to be the intent). This will enforce the idea of the `checkLectureStatus` function at the database level, rather than just components of the application that use this method. – quickshiftin Mar 31 '12 at 05:27

Starx · Answer 3 · 2012-03-31T05:47:47.937

19

Use mysql_num_rows(), to check if rows are available or not

$result = mysql_query("SELECT * FROM preditors_assigned WHERE lecture_name='$lectureName' LIMIT 1");
$num_rows = mysql_num_rows($result);

if ($num_rows > 0) {
  // do something
}
else {
  // do something else
}

edited Mar 31 '12 at 05:47

answered Mar 31 '12 at 05:19

Starx

77,474
47
185
261

@AbijithAjayan, Not sure (You can probably test that quickly). If you only want the count then I would probably do `COUNT()` rather than the full query. – Starx Aug 21 '19 at 15:41
@Starx I want to check actually, is that particular id is exists or not, for now it is working perfectly, am thinking about after few months, defenitly database is going bigger and bigger, for that time, may be it will come as slow or not..??? – Abijith Ajayan Aug 22 '19 at 07:54

score 2 · Answer 4 · answered Mar 31 '12 at 05:16

2

$result = mysql_query("select if(exists (SELECT * FROM  preditors_assigned WHERE lecture_name='$lectureName'),'Assigned', 'Available')");

answered Mar 31 '12 at 05:16

juergen d

201,996
37
293
362

1

This is a good approach if you're an advocate of implementing application logic at the database level; one step away from stored procedures if you ask me, but a valid solution nonetheless. – quickshiftin Mar 31 '12 at 05:45

Hardeep Pandya · Answer 5 · 2012-03-31T05:34:33.110

If you just want to compare only one row with $lactureName then use following

function checkLectureStatus($lectureName)
{
 $con = connectvar();
 mysql_select_db("mydatabase", $con);
 $result = mysql_query("SELECT * FROM preditors_assigned WHERE lecture_name='$lectureName'");
  if(mysql_num_rows($result) > 0)
  {
         mysql_close($con);
         return "Assigned";
  }
  else
  {
        mysql_close($con);
        return "Available";
  }
}

score -2 · Answer 6 · answered Mar 31 '12 at 05:29

-2

function checkLectureStatus($lectureName) {
  global $con;
  $lectureName = mysql_real_escape_string($lectureName);
  $sql = "SELECT 1 FROM preditors_assigned WHERE lecture_name='$lectureName'";
  $result = mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
  if (mysql_fetch_row($result)) {
    return 'Assigned';
  }
  return 'Available';
}

however you have to use some abstraction library for the database access.
the code would become

function checkLectureStatus($lectureName) {
  $res = db::getOne("SELECT 1 FROM preditors_assigned WHERE lecture_name=?",$lectureName);
  if($res) {
    return 'Assigned';
  }
  return 'Available';
}

answered Mar 31 '12 at 05:29

Your Common Sense

156,878
40
214
345

1

That's some gratuitous use of curly braces in those if statements., and is that a global variable I see... Major insight on the db abstraction layer.. – quickshiftin Mar 31 '12 at 05:47
Nothing wrong with global variables if them used on purpose. A developer should be guided by knowledge, not superstitions. – Your Common Sense Mar 31 '12 at 05:57
That's just as debatable as your opinion on 'bad formatting'. And the whole 'the code would become' bit, what is that your home brewed db library, I don't have a copy, or a reference, neither does anyone else reading this thread... – quickshiftin Mar 31 '12 at 05:59

Check if a row exists using old mysql_* API

6 Answers6

Linked

Related