Rewrite MySQL SELECT statement so error isn't generated

Posted on 2011-05-05
Last Modified: 2012-05-11
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?

Question by:Jonathan Greenberg
    LVL 51

    Accepted Solution

    I don't use php, but it sounds like that's where your problem is. Are you checking the row count 1st?,142673,200941#msg-200941
    to prevent this, check the result first using mysql_num_rows() before using mysql_result()
    LVL 3

    Assisted Solution

    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.
    LVL 51

    Expert Comment

    Yep, looks like wuyinzhi's suggestion would do the trick.

    Author Comment

    by:Jonathan Greenberg
    Actually, these are both really helpful solutions. Thank you both!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    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…
    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 a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now