1

I have this basic script checking to make sure a conversation doesn't already take place in the database, if it does, it should get that conversations ID number and send the user to that conversation, otherwise process the info. I'm halfway through and totally stuck as no matter what it doesn't seem to be checking correctly as it just keeps adding the same conversations...

edited to include further information:

Upon script initiate it will work and add a conversation as it should to the two databases I have:

ap_conversations:
conversation_id | user_one | user_two | time | delete

ap_messages: 
message_id | message | sender_id | time_sent | time_read | conversation_id

if there is already a conversation in ap_conversations with the two users, I would like to send the user to THAT conversation rather than create a whole new one. Although the script won't do that and is just creating multiple conversations. 

/////////// CREATE NEW CONVERSATION //////////////////////
if(isset($_POST['newmessage'])){
    $to = $_POST['to'];
    $text = $_POST['message'];
    $message = str_replace("'","\\'",$text); 
    $userid = $_SESSION['userid'];

    /// GET OTHER USER ID
$c_id = rand();
$getID = mysqli_fetch_assoc(mysqli_query($conn, "SELECT user_id FROM ap_users 
WHERE '$to' LIKE CONCAT(first_name, '%', last_name)"));
$touserID = $getID['user_id'];
if(isset($touserID)){


   /// CHECK CONVO DOESNT EXIST

    $sql = "SELECT * from ap_conversations WHERE user_one = '$userid' AND user_two = '$touserid' OR user_two = '$userid' AND user_one = '$touserid' LIMIT 1";
    $result = $conn->query($sql);
    if($result->num_rows != 1){ 


        mysqli_query($conn,"INSERT INTO `ap_conversations` (`conversation_id`, `user_one`, `user_two`, `time`, `delete`) 
VALUES ('$c_id', '$userid', '$touserID', NOW(), '0');");    

        mysqli_query($conn,"INSERT INTO ap_messages (message_id, message, sender_id, time_sent, time_read, conversation_id) 
VALUES ('','$message','$userid', NOW(), '', '$c_id')"); 

        header('Location: messages.php?convoid='.$c_id.'');


      } else {
          $getconid = mysqli_fetch_assoc(mysqli_query($conn, "SELECT conversation_id FROM ap_conversations 
            WHERE user_one = '$userid' AND user_two = '$touserid' OR user_two = '$userid' AND user_one = '$touserid' LIMIT 1"));
          $conid = $getconid['conversation_id'];
        header('Location: messages.php?convo='.$conid.'');
   }
} else {
    header('Location: messages.php?error=2');
   }
}
Snappysites
  • 804
  • 1
  • 10
  • 41
  • First of all, you're using a very unsafe SQL queries. Please read this question and asnwers http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php . – ksimka Nov 27 '15 at 13:49
  • @ksimka this isn't for a public site so shouldn't have much to worry about although once I have it working I will be cleaning it up a little. I will have a read of that article, thanks! – Snappysites Nov 27 '15 at 13:51
  • 1) please show full code; 2) what do you mean by "keeps adding the same conversations..."? I mean, what's the problem? Can you provide an actual and expected results to show the difference? – ksimka Nov 27 '15 at 13:51
  • Will edit the question... – Snappysites Nov 27 '15 at 13:52
  • Updated the question with more information and details. – Snappysites Nov 27 '15 at 13:57
  • 1
    @Snappysites Debug your code first. `if(isset($touserID) && !empty($touserID)){ // echo all your data here }`, and see if there's anything missing. – Rajdeep Paul Nov 27 '15 at 14:07
  • 1
    Can you manually execute your query "SELECT * from ap_conversations WHERE user_one = ? AND user_two = ?" with known user ids that still get new conversations? What's there? – ksimka Nov 27 '15 at 14:09
  • Yes, but if I make those columns unique so that it won't allow it then it won't give the user an error or send them to the conversation already in place will it? – Snappysites Nov 27 '15 at 14:12

1 Answers1

1

Try this:

// your code

if($result->num_rows != 1){
    // ...
}else{
    // ...
}

// your code

Edited:

Run your program with the following code snippet, and see what you're getting.

/////////// CREATE NEW CONVERSATION //////////////////////
if(isset($_POST['newmessage'])){
    $to = $_POST['to'];
    $text = $_POST['message'];
    $message = str_replace("'","\\'",$text); 
    $userid = $_SESSION['userid'];

    /// GET OTHER USER ID
    $c_id = rand();
    $result = $conn->query("SELECT user_id FROM ap_users WHERE '$to' LIKE CONCAT(first_name, '%', last_name)");
    $getID = $result->fetch_assoc();
    $touserID = $getID['user_id'];
    if(isset($touserID) && !empty($touserID)){

        $sql = "SELECT * from ap_conversations WHERE (user_one = '$userid' AND user_two = '$touserid') OR (user_two = '$userid' AND user_one = '$touserid') LIMIT 1";
        $result = $conn->query($sql);
        if ($result->num_rows) {
            // conversation exists
            $result = $conn->query("SELECT conversation_id FROM ap_conversations WHERE (user_one = '$userid' AND user_two = '$touserid') OR (user_two = '$userid' AND user_one = '$touserid') LIMIT 1");
            $getconid = $result->fetch_assoc();
            $conid = $getconid['conversation_id'];
            header('Location: messages.php?convoid=' . $conid);
            exit();
        }else{
            // conversation does not exist
            $conn->query("INSERT INTO `ap_conversations` (`conversation_id`, `user_one`, `user_two`, `time`, `delete`) VALUES ('$c_id', '$userid', '$touserID', NOW(), '0');");
            $conn->query("INSERT INTO ap_messages (message_id, message, sender_id, time_sent, time_read, conversation_id)  VALUES ('','$message','$userid', NOW(), '', '$c_id')");

            header('Location: messages.php?convoid=' . $c_id);
            exit();
        }

    }else{
        header('Location: messages.php?error=2');
        exit();
    }
}

Sidenote: You mixed the procedural and object oriented style of mysqli.

Rajdeep Paul
  • 16,887
  • 3
  • 18
  • 37