MySQL - Using PHP to determine if record exists

I am a semi-newbie to PHP and MySQL.

I need to search a table to see if a certain record exists.  I do not need to know the data in the record.  I am trying to figure out if I need to collect the data to insert into the table.  After researching this I concluded the best approach is to use the code show below.  Problem seems to be when no records exist I get the warning shown below.

Question 1:
Am I using the best method to figure out whether a record exists?  
If not, what is a  better method?

Question 2:
How do I fix the warning message?  I need to see the actual PHP code that will work.


Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource


$result=mysql_query("SELECT COUNT(*) FROM tableA WHERE (column1 = $num) AND (column2 = $uid)");
$count = mysql_fetch_row($result);
echo "Num of rows = $count[0]";
LVL 1
dalvaAsked:
Who is Participating?
 
RurneConnect With a Mentor Commented:
You can use built-in functions to check the result type.  The advantage of this is that, should you find what you need, you don't have to requery for it and open up a new database resource.  mysql_num_rows is a little slower than the SQL 'COUNT()' function, but one query is vastly superior to two queries.  It really depends on your usage, though... I think the below is more maintainable.

As for the error you're getting, it sounds like either:
1. $num and/or $uid is empty.
2. $num and/or $uid contains bad data that is breaking the query.

For 1., you'll need to check empty($uid) and empty($num) to make sure you've got something to run the query.  For 2., I've used mysql_real_escape_string below to help prevent that.
$result = mysql_query('SELECT * FROM tableA WHERE column1 = '
    .mysql_real_escape_string($num).' AND column2 = '.
    .mysql_real_escape_string($uid)) or throw new Exception(mysql_error(), mysql_errno());
 
echo 'Num or rows = '.mysql_num_rows($result);

Open in new window

0
 
Ali KayahanFull Stack DeveloperCommented:
$result=mysql_query("SELECT * FROM tableA WHERE (column1 = $num) AND (column2 = $uid)");
$count = mysql_num_rows($result);
echo "Num of rows = $count";
0
 
RoonaanCommented:
Your query probably has an error. Use

$result=mysql_query("SELECT COUNT(*) FROM tableA WHERE (column1 = $num) AND (column2 = $uid)") or print("SQL Error: " + mysql_error());
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dalvaAuthor Commented:
Experts,
Thanks for the quick response.  I'll test out tonight and get back to you in the AM.
0
 
spoxoxCommented:
1) You do have stuff like this preceding your illustrated code:

  $username = "eeuser";
  $password = "eepw";
  $hostname = "localhost";    
  $dbconn = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
  $database = mysql_select_db("eedb",$dbconn);

$result=mysql_query("SELECT COUNT(*) FROM tableA WHERE (column1 = $num) AND (column2 = $uid)");
$count = mysql_fetch_row($result);
echo "Num of rows = $count[0]";


2) Make sure the SQL is correct: in particular tableA, column1, column2 are all spelled correctly.


3) NOTE: adding "or die..." will produce more descriptive error messages. (as Roonaan suggests, but in a different place....)

$result=mysql_query("SELECT COUNT(*) FROM tableA WHERE (column1 = $num) AND (column2 = $uid)") or die(mysql_error());
0
 
spoxoxConnect With a Mentor Commented:
Also, if column1 is numeric, OK;

if column2 is alphabetic, try:
$result=mysql_query("SELECT COUNT(*) FROM tableA WHERE (column1 = $num) AND (column2 = \"$uid\")") or die(mysql_error());
0
 
dalvaAuthor Commented:
spoxox, I do have the required preceeding code.  Just left off for sake of clearity.
0
 
dalvaAuthor Commented:
I tried all the suggestions but it always gave the warning on the line with mysql_fetch_row($result) or mysql_num_rows($result).

I researched more and learned the return value is either a number corresponding to the number of fetched rows or FALSE if there are no fetched rows.  I thought it would return a number or zero for no fetched rows.  This explains the warning message since FALSE is not a valid argument when it is expecting a numeric.

The fix is to add the "if (!$result)" logic code as shown below from an example I came across.  In my case a FALSE means there are no records in the database but I guess it could also mean something blew up during the query.  If I can distinguish between a FALSE which means no records and a FALSE which means query blew up then it would make better logic code.

Is my thinking correct?  Any ideas on how to distinguish between the two FALSE types would be appreciated?


The below text and example came from the site:
http://us2.php.net/manual/en/function.mysql-fetch-row.php

Return Values
Returns an numerical array of strings that corresponds to the fetched row, or FALSE if there are no more rows.

mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

Example #1 Fetching one row with mysql_fetch_row()

<?php
$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$row = mysql_fetch_row($result);

echo $row[0]; // 42
echo $row[1]; // the email value
?>
0
 
RurneCommented:
From http://us3.php.net/mysql-query:

"Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error."



The fact that you are getting an error when trying to run mysql_num_rows() or mysql_fetch_row() means that you are not using the "or" statements to throw an Exception or die instantly.  If you were, then you would be receiving error messages to pinpoint the issue.  The "if (!$result) { " syntax you are using is simply a longer way of expressing the "or" syntax provided in the above examples. mysql_num_rows() _will_ return 0 if no records are found; it only returns FALSE on failure.


Please try the offered suggestions without modification and provide the exact error message you are obtaining from mysql_error().
0
 
dalvaAuthor Commented:
Rurne,
I'll follow the offered suggestions this weekend with more diligence and let you know on Monday.
Thanks
0
 
dalvaAuthor Commented:
After careful debugging using suggested methods and echo statements this is the working code which works as expected.   If no rows are found count equals zero otherwise count equals number of rows found.

The fix was to place the $num and $uid in single quotes and include the or die portion.  $uid is an alphanumeric value.

$result=mysql_query("SELECT COUNT(*) FROM tableA WHERE (column1 = $num) AND (column2 = $uid)") or die(mysql_error());
$count = mysql_fetch_row($result);
if ($count[0]==0) {
bla bla&
}

I have split the points half to Rurne for being on track as to where the problem might exist and being adamant that my original conclusion was incorrect and I should follow the experts suggestions more closely.

Spoxox receives the other half points for his suggestion to try this \"$uid\" which lead me to use single quotes.

It is interesting to learn how much difference quotes make.  When to use single quotes versus double quotes is something I have not yet mastered.

Thanks to all who contributed.  As usual experts-exchange kicks butt.

0
 
RurneCommented:
As with my initial suggestion, I highly recommend you not to count on backslashes to escape your quotes.  You really should look into mysql_real_escape_string() for better quoting and escaping of any user-submitted content.

In any case, I am glad you have come to a workable solution.  Please let us know if you have any other questions or issues with this solution that we can assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.