mysql query returning false even when values DO exist in table? Trying to find if not in table?

Question

Ok, so I am building on my first question here https://stackoverflow.com/questions/38102208/php-mysql-how-to-only-echo-links-with-search-bar-post-that-arent-already-echo

trying to only echo only usernames of people whose id is NOT in a mysql table called conversation along with a set id (the person who is signed in).

I echo the people who their id is user_two in a table conversation REGARDLESS if a search bar is posted here:

//$num = mysqli_query($con, "SELECT * FROM `pm_messages` WHERE user_from=".$account['id']."");
$numCon = mysqli_query($con, "SELECT * FROM `conversation` WHERE user_one=".$account['id']."");
$numrows = mysqli_num_rows($numCon);

while ($u = mysqli_fetch_assoc($numCon)) {
    //get other users usernames to echo link
    $getUserTwo = mysqli_query($con, "SELECT * FROM `accounts` WHERE id=".$u['user_two']."");
    $s = mysqli_fetch_assoc($getUserTwo);

    //echo $s['username'];
    echo "<a href='message.php?id={$s['id']}'><li><img class = 'dmCircle' src = '../images/chatCircle.png'/>{$s['username']} </li></a>";
}

This works well, meaning only individuals who a conversation has been started with (a row exists for this user and the one signed in conversation table) are echoed in a link.

Problem comes here with the search bar because it echoes all individuals even if a conversation has been started, resulting in duplicates:

(notice the 2 khusteds)

This does not make sense because here I select the row in conversation where user_one is the signed in user and user_two is the second user and only echo a link if the result is FALSE (meaning there's no conversation):

if (isset($_POST['searchbarpm'])) {
    //$sess->getUsers();
    $dbh = mysqli_connect("localhost","username","password","sqlserver");
    $query = $_POST['searchbarpm'];
    $q = mysqli_query($dbh, "SELECT * FROM sqlserver.accounts WHERE username LIKE '%".$query."%'");

    //display all the results
    while($row = mysqli_fetch_assoc($q)) {

        $checkConvo = mysql_query("SELECT 'id' FROM sqlserver.conversation WHERE user_one=".$user_id." AND user_two=".$row['id']."");

        //only output users they dont have convo going with because theyre already printed!!!
        if ($checkConvo==false && $row['id']!= $user_id) { 
            echo "<a href='message.php?id={$row['id']}'><li><img class = 'dmCircle' src = '../images/noChatCircle.png'/> {$row['username']}</li></a>";
        }
    }
}

But it looks like the query is always false because again, all users are echoed. Why is this happening? How can I only echo users not in conversation table with the signed in user (user_one)? EDIT:

new code (sorry for screenshot); :

@IanH -

$con = mysqli_connect("localhost","username","password","sqlserver");

                    //$num = mysqli_query($con, "SELECT * FROM `pm_messages` WHERE user_from=".$account['id']."");
                $numCon = mysqli_query($con, "SELECT * FROM `conversation` WHERE user_one=".$account['id']."");
                    $numrows = mysqli_num_rows($numCon);
                while ($u = mysqli_fetch_assoc($numCon))
                    {
                    //get other users usernames to echo link
    $getUserTwo = mysqli_query($con, "SELECT * FROM `accounts` WHERE id=".$u['user_two']."");
        $s = mysqli_fetch_assoc($getUserTwo);
                    //echo $s['username'];

                    if(isset($_POST['searchbarpm'])){
//$sess->getUsers();
    $dbh = mysqli_connect("localhost","username","password","sqlserver");
                    $query = $_POST['searchbarpm'];
                    $q = mysqli_query($dbh, "SELECT * FROM sqlserver.accounts WHERE username LIKE '%".$query."%'");
                    //display all the results
                    while($row = mysqli_fetch_assoc($q)){


                        if($row['id']!= $user_id && $row['id']!=$s['id']) { //only output users they dont have convo going with because theyre already printed!!!
                        echo "<a href='message.php?id={$row['id']}'><li><img class = 'dmCircle' src = '../images/noChatCircle.png'/> {$row['username']}</li></a>";
                        }
                    }
}
                    else {


                    echo "<a href='message.php?id={$s['id']}'><li><img class = 'dmCircle' src = '../images/chatCircle.png'/>{$s['username']} </li></a>";
                    }
                }//

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 29 '16 at 19:12
Is there a reason why you indent your code so chaotically? Could you redo that? — trincot, Jun 29 '16 at 19:13
`$checkConvo = mysql_query("SELECT 'id'` you're mixing APIs here with your `mysqli_`. So that query is failing you right there. And using regular quotes `'` which either need to be removed or use ticks `\``. — Funk Forty Niner, Jun 29 '16 at 19:15

Ian H · Answer 1 · 2016-06-29T19:20:27.620

0

First of all, you need change the mysql_query( ... ) on line 9 of your second posted code block to mysqli_query( ... ), for API consistency and compatibility.

Also, you could have duplicate results if you are allowing multiple entries in the conversation table where users A and B can be entered as user1 = A, user2 = B in one conversation, and user1 = B, user2 = A in a different conversation.

Lastly, as Jay Blanchard said, you should use prepared statements to avoid SQL injection.

edited Jun 29 '16 at 19:20

answered Jun 29 '16 at 19:19

Ian H

105
1
9

Please do not use answers to ask clarification questions. – Jay Blanchard Jun 29 '16 at 19:20
@JayBlanchard Fixed – Ian H Jun 29 '16 at 19:21
That's true, I didn't think about the ordering of the users. I updated the code I have now in the question, now its not returning any users – blue Jun 29 '16 at 19:22
In the code you use to print the users you do have conversations with, couldn't you just use the logic used to just print those users to print all users, but only add an additional image to the set with/without a conversation? It would be useful to see the code where you print those as well. – Ian H Jun 29 '16 at 19:33
Id like to do that but again the query is returning false when it shouldn't. I posted all the code - the first block prints users with conversations the second is the search bar for (right now) all users – blue Jun 29 '16 at 19:41
@IanH Just tried to integrate it but it didn't work. Can you please help me? – blue Jun 29 '16 at 19:56
Try changing that select up earlier to "SELECT * FROM accounts" and moving the equivalent of a "WHERE id=$u['user_two']" to an if-else statement condition, which prints the complete, dotted username if $s['id] == $u['user_two'], otherwise printing the other ones only if the search is enabled. I guess after that you'd want to add some additional filtering logic for the other ones, but try something like this for now? – Ian H Jun 29 '16 at 20:05
@IanH Im only partially understanding what you mean here.. would you post some code in your answer? A little confused as to how an if else statement would work here – blue Jun 29 '16 at 21:33
I updated the question with what Ive tried- it isnt working at all. – blue Jun 29 '16 at 21:38

mysql query returning false even when values DO exist in table? Trying to find if not in table?

1 Answers1

Linked