(sorry for misspelled words) Hi everyone hope you have a nice time. I am using ajax for the first time and trying to add some elements (sent and received messages) to html through ajax but only thing i get is an undefined text in html.
messageInsert and first ajax work ok but from get_msg I get an sql syntax error:
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND incoming_id = ) OR (outgoing_id = AND incoming_id = ) ORDER BY msg_id DESC' at line 1 in C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php:9 Stack trace: #0 C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php(9): mysqli->query('SELECT * FROM m...') #1 {main} thrown in C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php on line 9
messageInsert.php :
<?php
session_start();
require_once 'connection.php';
if (isset($_SESSION['userID'])) {
$outgoing_id = mysqli_real_escape_string(connection() , $_POST['outgoing_id']);
$incoming_id = mysqli_real_escape_string(connection() , $_POST['incoming_id']);
$message = mysqli_real_escape_string(connection() , $_POST['message']);
if (!empty($message)) {
$query = "INSERT INTO messages (outgoing_id, incoming_id, msg) VALUES ($outgoing_id , $incoming_id , '$message')";
$result =connection()->query($query);
}
}
get_msg.php :
<?php
session_start();
require_once 'connection.php';
if (isset($_SESSION['userID'])) {
$outgoing_id = mysqli_real_escape_string(connection() , $_POST['outgoing_id']);
$incoming_id = mysqli_real_escape_string(connection() , $_POST['incoming_id']);
$output = "";
$query = "SELECT * FROM messages WHERE (outgoing_id = {$outgoing_id} AND incoming_id = {$incoming_id}) OR (outgoing_id = {$incoming_id} AND incoming_id = {$outgoing_id}) ORDER BY msg_id DESC";
$result = connection()->query($query);
while ($row = mysqli_fetch_assoc($result)) {
if ($outgoing_id === $row['outgoing_id']) {
$output ='<div class="message-send my-4">
<p>'.$row['msg'].'</p>
</div>';
} else {
$output = '<div class="message-receive">
<img class="message-receive-pic mb-3 me-2" src="style/ProfilePic/'.$_SESSION['userPic'].'" alt="">
<div class="message-receive-text-box my-4">
<p> '. $row['msg'] .'</p>
</div>
</div>';
}
echo $output;
}
}
my ajax :
let form = document.querySelector(".mySend"),
sendInput = form.querySelector(".message"),
sendBtn = form.querySelector(".sendBtn"),
chatBox = document.querySelector(".chat-box");
form.onsubmit = (e)=> {
e.preventDefault();
}
sendBtn.onclick = ()=> {
let xhr = new XMLHttpRequest();
xhr.open("POST" , "http://localhost/project/myProjects/Chatbot/php/messageInsert.php" , true);
xhr.onreadystatechange = ()=> {
if (xhr.readyState === XMLHttpRequest.DONE) {
if (xhr.status === 200){
sendInput.value = "";
}
}
}
let formData = new FormData(form);
xhr.send(formData);
}
setInterval(()=> {
let xhr = new XMLHttpRequest();
let data = "";
xhr.open("POST" , "php/get_msg.php" , true);
xhr.onload = ()=> {
if (xhr.readyState === XMLHttpRequest.DONE) {
if (xhr.status === 200) {
data = this.response;
chatBox.innerHTML = data;
}
}
}
let formData = new FormData(form);
xhr.send(formData);
}, 500);
my html :
<div class="chat-box">
</div>
<div class="type-box">
<form class="mySend" method="post" autocomplete="off">
<input type="hidden" value="<?php echo $userProfile['id'] ?>" name="incoming_id">
<input type="hidden" value="<?php echo $_SESSION["profileID"] ?>" name="outgoing_id">
<input class="message form-control" type="text" placeholder="Type your message here..." name="message">
<button type="submit" class=" sendBtn btn btn-dark" name="sendBtn"><i class="fab fa-telegram-plane"></i></button>
</form>
</div>
I am sure about table and columns names.
I appreciate your help so much:)
I have tried everything in ajax and php files , nothing worked.
Your error message of
Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND incoming_id = ) OR (outgoing_id = AND incoming_id = ) ORDER BY msg_id DESC' at line 1 in C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php:9 Stack trace: #0 C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php(9): mysqli->query('SELECT * FROM m...') #1 {main} thrown in C:\xampp\htdocs\project\myProjects\Chatbot\php\get_msg.php on line 9
clearly points you to the direction that this line is where you are getting into trouble (the error is thrown at the next line where you attempt to execute it):
$query = "SELECT * FROM messages WHERE (outgoing_id = {$outgoing_id} AND incoming_id = {$incoming_id}) OR (outgoing_id = {$incoming_id} AND incoming_id = {$outgoing_id}) ORDER BY msg_id DESC";
Look at the query part of your error message carefully:
AND incoming_id = ) OR (outgoing_id = AND incoming_id = ) ORDER BY msg_id DESC
You have no values set for incoming_id or outgoing_id
This is how you attempt to be loading these values:
$outgoing_id = mysqli_real_escape_string(connection() , $_POST['outgoing_id']);
$incoming_id = mysqli_real_escape_string(connection() , $_POST['incoming_id']);
which clearly suggests that you get empty strings as values for them (since $_POST['outgoing_id'] and $_POST['incoming_id'] does not throw errors, you have those keys inside your POST request, albeit their values are empty.)
These values are defined in your HTML, namely at
<input type="hidden" value="<?php echo $userProfile['id'] ?>" name="incoming_id">
<input type="hidden" value="<?php echo $_SESSION["profileID"] ?>" name="outgoing_id">
given the fact that they end up being empty, we know that $userProfile['id'] and $_SESSION["profileID"] are empty. You will need to look into how these are being defined and possibly wrap intval around them, like
$intOutgoing_id = intval($outgoing_id);
$intIncoming_id = intval($incoming_id);
$query = "SELECT * FROM messages WHERE (outgoing_id = {$intOutgoing_id} AND incoming_id = {$intIncoming_id}) OR (outgoing_id = {$intIncoming_id} AND incoming_id = {$intOutgoing_id}) ORDER BY msg_id DESC";
so they will be converted to 0 even if they are empty strings, so the behavior will be that if there are invalid values passed for them, then there will be 0 records in the result set rather than a page crash.
EDIT
Constructed this example:
<div class="type-box">
<form class="mySend" method="post" autocomplete="off">
<input type="hidden" value="3" name="incoming_id">
<input type="hidden" value="2" name="outgoing_id">
<input class="message form-control" type="text" placeholder="Type your message here..." name="message">
<button type="submit" class=" sendBtn btn btn-dark" name="sendBtn"><i class="fab fa-telegram-plane"></i></button>
</form>
</div>
<script>
let form = document.querySelector(".mySend"),
sendInput = form.querySelector(".message"),
sendBtn = form.querySelector(".sendBtn"),
chatBox = document.querySelector(".chat-box");
let xhr = new XMLHttpRequest();
let data = "";
xhr.open("POST" , "bar.html" , true);
xhr.onload = ()=> {
if (xhr.readyState === XMLHttpRequest.DONE) {
if (xhr.status === 200) {
data = xhr.responseText;
}
}
}
let formData = new FormData(form);
xhr.send(formData);
</script>
and it properly passed the parameters in question. Since something prevents your script from doing so, I recommend debugging and logging your code to see the exact issue. At this point it very much seems that your AJAX request should work, it succeeded in my test.