How to query with 2 fields?

Question

I want to mysql query in a table that has 4 fields: id, user_one, user_two, hash.

   $check_con = mysql_query("SELECT count(hash) FROM messages_group WHERE (user_one=$my_id AND user_two=$other_id) OR (user_one=$other_id AND user_two=$my_id)");

    $amsik = mysql_fetch_array($check_con);
        $currenthash=$amsik['hash'];

           if ($amsik['COUNT(hash)'] > 0){
            mysql_query("INSERT INTO messages VALUES('', '$currenthash', '$my_id', '$message')");
            header('location: m.php?hash='.$currenthash.'');
           } else{
           mysql_query("INSERT INTO messages_group VALUES('$my_id', '$other_id', '$random_number','')");
           mysql_query("INSERT INTO messages VALUES('', '$random_number', '$my_id', '$message')");
           header('location: m.php?hash='.$random_number.'');
           }
       }

This if statement doesn't work. It always goes with the else part. I mean when I try to run it if even there is more than 1 rows, it creates a new hash in messages_group by random number. What should i do?

A few things: Firstly if you're not sure why things are happening, do a `var_dump($amsik);` wrapped in `
` tags. Easier to debug (you can see exactly what's returned by the `mysql_fetch_array` query). Secondly `mysql_` functions are deprecated, use `mysqli` or similar. Thirdly, you're not escaping any of the variables (at least in this code snippet). Ensure all values are escaped. Prepared statements help with this. Lastly name your `COUNT(hash)` column something more specific, just easier to read. — ScottMcGready, Dec 12 '15 at 01:35

score 0 · Accepted Answer · edited May 23 '17 at 11:44

PHP is case sensitive. It should be

if ($amsik['count(hash)'] > 0)

I recommend you always assign aliases to calculated fields, rather than using the formula as the key.

SELECT COUNT(hash) AS hashcount ...

then you can use $amsik['hashcount']

Another problem in your code is:

$currenthash = $amsik['hash'];

The query doesn't return the hash column. You can use MAX(hash) to return the current hash when there is one:

SELECT COUNT(hash) AS hashcount, MAX(hash) AS hash ...

Or you can do it like this:

SELECT hash
FROM messages_group
WHERE (user_one=$my_id AND user_two=$other_id) OR (user_one=$other_id AND user_two=$my_id)
LIMIT 1

Then you can do:

$amsik = mysql_fetch_assoc($check_con);
if ($amsik) {
    $currenthash = $amsik['hash'];
    mysql_query("INSERT INTO messages VALUES('', '$currenthash', '$my_id', '$message')");
header('location: m.php?hash='.$currenthash.'');
} else{
   mysql_query("INSERT INTO messages_group VALUES('$my_id', '$other_id', '$random_number','')");
   mysql_query("INSERT INTO messages VALUES('', '$random_number', '$my_id', '$message')");
   header('location: m.php?hash='.$random_number.'');
}

By the way, unless hash can be NULL and you need to skip those when counting, you should use COUNT(*) instead of COUNT(hash). See count(*) and count(column_name), what's the diff?

Barmar, thanks for your answer. I created a conversation part for users like facebook or any websites. Anyway it works well, except If user1 tries to send message to user2, it's created new conversation everytime. I want it to insert the new messages to first conversation. If this is the first conversation then create a new one. Yes the query returns the count of hashes, not any specific hash but there must be 1 hash max belongs to these specific 2 users (user_one and user two). How can I get that hash as a current hash? — Cuba Libre, Dec 12 '15 at 01:55
I've edited my answer based on there being just one row that matches the criteria. That wasn't obvious from the original question. — Barmar, Dec 12 '15 at 01:59

How to query with 2 fields?

1 Answers1