mysql query showing wrong result












0















I have a database table look like this



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+


When i run this query which should show me no rows because there is no row with 101foo2 value :




SELECT * FROM tableName WHERE ID = '101foo2'




I am getting a result with same ID without the foo2 word



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+


how it is showing the row with ID 101 if my query is ID = '101foo2'










share|improve this question


















  • 1





    Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting '101foo2' into 101 if the column datatype is a INT.. That why your query results into getting that record.

    – Raymond Nijland
    Jan 3 at 17:13


















0















I have a database table look like this



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+


When i run this query which should show me no rows because there is no row with 101foo2 value :




SELECT * FROM tableName WHERE ID = '101foo2'




I am getting a result with same ID without the foo2 word



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+


how it is showing the row with ID 101 if my query is ID = '101foo2'










share|improve this question


















  • 1





    Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting '101foo2' into 101 if the column datatype is a INT.. That why your query results into getting that record.

    – Raymond Nijland
    Jan 3 at 17:13
















0












0








0








I have a database table look like this



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+


When i run this query which should show me no rows because there is no row with 101foo2 value :




SELECT * FROM tableName WHERE ID = '101foo2'




I am getting a result with same ID without the foo2 word



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+


how it is showing the row with ID 101 if my query is ID = '101foo2'










share|improve this question














I have a database table look like this



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+


When i run this query which should show me no rows because there is no row with 101foo2 value :




SELECT * FROM tableName WHERE ID = '101foo2'




I am getting a result with same ID without the foo2 word



+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+


how it is showing the row with ID 101 if my query is ID = '101foo2'







mysql sql phpmyadmin






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 3 at 17:10









NasserNasser

74




74








  • 1





    Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting '101foo2' into 101 if the column datatype is a INT.. That why your query results into getting that record.

    – Raymond Nijland
    Jan 3 at 17:13
















  • 1





    Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting '101foo2' into 101 if the column datatype is a INT.. That why your query results into getting that record.

    – Raymond Nijland
    Jan 3 at 17:13










1




1





Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting '101foo2' into 101 if the column datatype is a INT.. That why your query results into getting that record.

– Raymond Nijland
Jan 3 at 17:13







Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting '101foo2' into 101 if the column datatype is a INT.. That why your query results into getting that record.

– Raymond Nijland
Jan 3 at 17:13














1 Answer
1






active

oldest

votes


















2














You are mixing types. ID is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101 and matches.



You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:



SELECT * FROM tableName WHERE ID = 101foo2


However, you would get an error. Another possibility is to force the conversion to a string:



SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'





share|improve this answer
























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026797%2fmysql-query-showing-wrong-result%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    You are mixing types. ID is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101 and matches.



    You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:



    SELECT * FROM tableName WHERE ID = 101foo2


    However, you would get an error. Another possibility is to force the conversion to a string:



    SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'





    share|improve this answer




























      2














      You are mixing types. ID is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101 and matches.



      You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:



      SELECT * FROM tableName WHERE ID = 101foo2


      However, you would get an error. Another possibility is to force the conversion to a string:



      SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'





      share|improve this answer


























        2












        2








        2







        You are mixing types. ID is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101 and matches.



        You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:



        SELECT * FROM tableName WHERE ID = 101foo2


        However, you would get an error. Another possibility is to force the conversion to a string:



        SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'





        share|improve this answer













        You are mixing types. ID is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101 and matches.



        You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:



        SELECT * FROM tableName WHERE ID = 101foo2


        However, you would get an error. Another possibility is to force the conversion to a string:



        SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 3 at 17:13









        Gordon LinoffGordon Linoff

        793k36316419




        793k36316419
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026797%2fmysql-query-showing-wrong-result%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas