Solved

MySQL -  Using PHP to determine if record exists

Posted on 2008-06-19
12
6,191 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
  • 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Blog posts not showing up on Blog page 7 50
wamp versus xampp 4 43
php refresh button on the browser 2 35
echo time from sql to input type="time" 9 17
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now