[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Rewrite MySQL SELECT statement so error isn't generated

I need to query a table such that if the content of a column equals my value, then it returns the value of another column; but if not, then return nothing and generate no warning. (Returning "false" would also be fine, or any other legal value to tell me my value doesn't exist.)

For example, let's say I submit this query:

"SELECT firstname FROM table WHERE lastname='Jones'"

I want to write this so it means: If there is a row with a lastname value of "Jones", then return the firstname value. If not, return nothing.

But if I use this statement as is and there is no row where the lastname is "Jones", MySQL generates an "Unable to jump to row 0" warning.

How can this statement be rewritten so that if my value is not found, a warning will not be generated?

Thanks.
0
Jonathan Greenberg
Asked:
Jonathan Greenberg
  • 2
2 Solutions
 
_agx_Commented:
I don't use php, but it sounds like that's where your problem is. Are you checking the row count 1st?

http://forums.mysql.com/read.php?52,142673,200941#msg-200941
to prevent this, check the result first using mysql_num_rows() before using mysql_result()
0
 
wuyinzhiCommented:
usually i just write something like this in PHP

$query = "SELECT firstname FROM table WHERE lastname='Jones'";
$result = mysql_query($query);      
      if ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                // do something
      }      

so i just do something if the data exists.
0
 
_agx_Commented:
Yep, looks like wuyinzhi's suggestion would do the trick.
0
 
Jonathan GreenbergAuthor Commented:
Actually, these are both really helpful solutions. Thank you both!
0

Featured Post

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now