checking to see if data exist with in a table

Question

How do i go about looking into a table and searching to see if a row exist. the back gorund behind it is the table is called enemies. Every row has a unique id and is set to auto_increment. Each row also has a unique value called monsterid. the monster id isn't auto_increment.

when a monster dies the row is deleted and replaced by a new row. so the id is always changing. as well the monsterid is changed too.

I am using in php the $_GET method and the monsterid is passing through it, basically i am trying to do this

$monsterID = 334322 //this is the id passed through the $_GET

checkMonsterId = "check to see if the monster id exist within the enemies table"

if monsterid exist then
{RUN PHP}

else
{RUN PHP}

If you need anymore clarity please ask. and thanks for the help in advance.

Answer 1

Use count! If it returns > 0, it exists, else, it doesn't.

select count(*) from enemies where monsterid = 334322

You would use it in PHP thusly (after connecting to the database):

$monsterID = mysql_real_escape_string($monsterID);
$res = mysql_query('select count(*) from enemies where monsterid = ' . $monsterid) or die();
$row = mysql_fetch_row($res);
if ($row[0] > 0)
{
    //Monster exists
}
else
{
    //It doesn't
}

Answer 2

Use count, like

select count(*) from enemies where monsterid = 334322

However be sure to make certain you've added an index on monsterid to the table. Reason being that if you don't, and this isn't the primary key, then the rdbms will be forced to issue a full table scan - read every row - to give you the value back. On small datasets this doesn't matter as the table will probably sit in core anyway, but once the number of rows becomes significant and you're hitting the disk to do the scan the speed difference can easily be two orders of magnitude or more.

If the number of rows is very small then not indexing is rational as using an non-primary key index requires additional overhead when inserting data, however this should be a definite decision (I regularly impress clients who've used a programmer who doesn't understand databases by adding indexes to tables which were fine when the coder created them but subsequently slow to a crawl when loaded with real volumes of data - quite amazing how one line of sql to add an index will buy you guru status in your clients eyes cause you made his system usable again).

If you're doing more complex queries against the database using subselect, something like finding all locations where there is no monster, then look up the use of the sql EXISTS clause. This is often overlooked by programmers (the temptation is to return a count of actual values) and using it is generally faster than the alternatives.

Answer 3

Simpler :

select 1 from enemies where monsterid = 334322

If it returns a row, you have a row, if not, you don't.