check two tables to see if an IP exists in either

Question

I'm trying to check two tables to see if an IP exists in either. The code I am using works but the number of rows being returned shows the full tables are being searched. The tables can be huge so I would like to limit the checking. Below shows what I am trying to do. When I run it on the actual database, the result of the echo shows 61,012, which is the number of rows in table b. Would someone please explain how I can limit the result so each table has a limit of 1?

    table a {
    status tinyint DEFAULT '0' NOT NULL, 
    ip INT UNSIGNED NOT NULL
    }

    table b {
    ip INT UNSIGNED NOT NULL
    }
    INSERT INTO b VALUES ('1.129.107.125'); 

    $ip = '1.129.107.125';
    $ck_query = mysqli_query("SELECT 1 FROM a WHERE status = 0 and ip = INET_ATON('" . $ip . "')
     or (SELECT 1 FROM b WHERE ip = INET_ATON('" . $ip . "') limit 1)");

    echo 'row '.mysqli_num_rows($ck_query);

By the way, when I searched for ways to check the two tables I found a number of examples that said to use select exists. The problem is that that always returns true, unless I implemented it incorrectly, so I can't use it.

select exists(select * from ...) + exists(select * from ...) — Your Common Sense, Aug 07 '23 at 05:54
As mentioned, that won't work. In php the function that creates the mysql call returns success because it didn't fail. On success of the call, one would either check the rows returned or fetch the returned results. So I need to be able to limit the rows or know the actual results. — user3052443, Aug 07 '23 at 15:11
"or fetch the returned results". What prevents you from doing so? — Your Common Sense, Aug 07 '23 at 15:39
When I fetch and display the results using the code from the other post, it just shows the two select statements - everything except the select exists. — user3052443, Aug 07 '23 at 16:02
Not sure what you mean. all you need is to run this query, fetch the result and then use it — Your Common Sense, Aug 07 '23 at 16:45
I was referring to the code in the other thread you posted. When I fetch the result for that call, it just shows the select statements. For example, if the code was select exist (select 1 from table where 1), fetching it should show 1 but it shows (select 1 from table where 1). I take it since no one has answered my original question that it is not possible to limit the results. Do you know if that is that correct? — user3052443, Aug 07 '23 at 18:36
did you notice the very first comment I made? What's wrong with SQL provided there? — Your Common Sense, Aug 07 '23 at 18:51
No, I didn't notice that. I tried it but it didn't work. Although it caused me to search for why the code wasn't returning the result and there are many posts on this forum for it and you mentioned too. When I was checking the result, I just noticed did a dump of the array. The key is the mysql statement and the value is either 0 or 1. I didn't notice the value since the key took up most of the page. Checking the value works correctly. What I thought was the number of rows is something else, maybe the size of the return but it is not an issue. Thank you for sticking with me to resolve this. — user3052443, Aug 08 '23 at 22:53

check two tables to see if an IP exists in either

0 Answers0