Solved

MySQL -  Using PHP to determine if record exists

Posted on 2008-06-19
12
6,195 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…

856 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