Solved

MySQL -  Using PHP to determine if record exists

Posted on 2008-06-19
12
6,208 Views
Last Modified: 2013-12-12
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]";
0
Comment
Question by:dalva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 9

Accepted Solution

by:
Rurne earned 125 total points
ID: 21824536
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
 
LVL 14

Expert Comment

by:Ali Kayahan
ID: 21824538
$result=mysql_query("SELECT * FROM tableA WHERE (column1 = $num) AND (column2 = $uid)");
$count = mysql_num_rows($result);
echo "Num of rows = $count";
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 21824552
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:dalva
ID: 21824863
Experts,
Thanks for the quick response.  I'll test out tonight and get back to you in the AM.
0
 
LVL 11

Expert Comment

by:spoxox
ID: 21824871
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
 
LVL 11

Assisted Solution

by:spoxox
spoxox earned 125 total points
ID: 21824905
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
 
LVL 1

Author Comment

by:dalva
ID: 21824913
spoxox, I do have the required preceeding code.  Just left off for sake of clearity.
0
 
LVL 1

Author Comment

by:dalva
ID: 21833319
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
 
LVL 9

Expert Comment

by:Rurne
ID: 21833456
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
 
LVL 1

Author Comment

by:dalva
ID: 21833574
Rurne,
I'll follow the offered suggestions this weekend with more diligence and let you know on Monday.
Thanks
0
 
LVL 1

Author Closing Comment

by:dalva
ID: 31468904
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
 
LVL 9

Expert Comment

by:Rurne
ID: 21848123
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

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question