?
Solved

MySQL -  Using PHP to determine if record exists

Posted on 2008-06-19
12
Medium Priority
?
6,214 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 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

719 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