PHP MySQL Query to check if record exists or not

Question

I have following data in the table..

+-------------+---------------+--------------+
| activity_id | activity_name | main_unit_id |
+-------------+---------------+--------------+
|           1 | DEA           |           67 |
|           2 | DEB           |           68 |
|           3 | DEC           |           68 |
|           4 | fasdfsadf     |           74 |
+-------------+---------------+--------------+

i want to add another activity, but before adding i have to make sure that same activity name is not there against an main_unit_id... I write following query,

$SQL_CHECK_ACTIVITY = mysql_query(
    "SELECT count(*) FROM activities " .
    "WHERE main_unit_id = '67' and activity_name = 'DEA'"
);
$RESULT_SQL_CHECK_ACTIVITY = mysql_num_rows($SQL_CHECK_ACTIVITY);
echo $RESULT_SQL_CHECK_ACTIVITY;

then it print 1 which means a activity against this project already exists...

$SQL_CHECK_ACTIVITY = mysql_query(
    "SELECT count(*) FROM activities " .
    "WHERE main_unit_id = '78' and activity_name = 'afsdaf'"
);
$RESULT_SQL_CHECK_ACTIVITY = mysql_num_rows($SQL_CHECK_ACTIVITY);
echo $RESULT_SQL_CHECK_ACTIVITY;

then it print 1 which means a activity against this project already exists... but there is no record in this case....

The reason you are getting 1 in the second case is that you are looking at the number of rows returned and not the value itself. The query returns one row with the value of 0. — Jimmy Bernljung
– Jimmy Bernljung, Commented Feb 14, 2015 at 23:58

Gordon Linoff · Accepted Answer · 2015-02-14 23:58:37Z

2

If you want to add another activity, but do not want duplicates on main_unit_id, then the right approach is to create a unique index (or constraint) on the field:

create unique index idx_activities_mainunitid on activites(main_unit_id);

If you attempt to insert a duplicate, the insert will fail.

answered Feb 14, 2015 at 23:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mhall · Accepted Answer · 2015-02-15 00:15:29Z

2

mysql_num_rows will return the number of rows, not the count(*) result.

In the first test, count(*) will return 1 in one row, so the result will be 1.

In the second test, count(*) will return 0 in one row, so the result would still be 1.

You will need to use a fetch function to get the result of count(*) rather than mysql_num_rows.

Edit

I.e. from:

$RESULT_SQL_CHECK_ACTIVITY = mysql_num_rows($SQL_CHECK_ACTIVITY);

To:

list ($RESULT_SQL_CHECK_ACTIVITY) = mysql_fetch_row($SQL_CHECK_ACTIVITY);

^{Note: The use of the mysql_* functions are considered deprecated and you should instead use something that offers better security and more functionality, such as MySQLi or PDO.}

edited Feb 15, 2015 at 0:15

answered Feb 14, 2015 at 23:58

mhall

3,7113 gold badges26 silver badges35 bronze badges

2 Comments

Muhammad Taqi Over a year ago

i know that, but how will i get status in my case.

mhall Over a year ago

Replace the lines with mysql_num_rows with list ($RESULT_SQL_CHECK_ACTIVITY) = mysql_fetch_row($SQL_CHECK_ACTIVITY);. See edit.

Muhammad Taqi · Accepted Answer · 2015-02-15 07:29:44Z

0

$SQL_CHECK_ACTIVITY = mysql_query("SELECT * FROM activities WHERE main_unit_id = '".$project."' and activity_name = '67' limit 1");
    if(mysql_fetch_row($SQL_CHECK_ACTIVITY)) {
        echo 'a activity against this project already exists...';
      }
      else{
}

answered Feb 15, 2015 at 7:29

Muhammad Taqi

5,4247 gold badges39 silver badges65 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Query to check if record exists or not

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related