consult don't work

[magcr23]

Hi guys, 

 

I have this code: 

<nav class="navbar navbar-inverse navbar-fixed-top" role="navigation">
<?php
include("menuLateral.php");
include("menuTop.php");
include("conexao.php");
					
$utilizador = $_SESSION["user"];
$mensagem=$_GET['mensagem'];
$result = mysqli_query($con, "SELECT * FROM mensagens WHERE id= $mensagem");
$ln = mysqli_fetch_assoc($result);
$raiz1 = 1;
					
$teste =  mysqli_query($con, "SELECT * FROM `mensagens` WHERE `id`= '$mensagem' AND raiz = '$raiz1' ");
$row = mysqli_fetch_assoc($teste);

?>
</nav>

 echo '<font size="5"><b>   Mensagem:</b></font> <font size="4"> ' . $row['mensagem'] . '</font><br />';

Why my eco is not working? If i use 

$ln['mensagem']

 it returns the the value where raiz=0, if i use 

$teste = mysqli_query($con, "SELECT * FROM `mensagens` WHERE raiz = '$raiz1' ");

$row['mensagem']

it returns what i want.

 

What am i doing wrong to 

$teste =  mysqli_query($con, "SELECT * FROM `mensagens` WHERE `id`= '$mensagem' AND raiz = '$raiz1' ");
$row = mysqli_fetch_assoc($teste);

don't work?

[cyberRobot]

I'm not sure what you are asking. For what it's worth, both queries are pretty much the same. The only thing that would make a difference is the WHERE clause. In the second query, the WHERE clause is set to only pull records that have "raiz" set to 1 (using your $raiz1 variable).

[magcr23]

I'm not sure what you are asking. For what it's worth, both queries are pretty much the same. The only thing that would make a difference is the WHERE clause. In the second query, the WHERE clause is set to only pull records that have "raiz" set to 1 (using your $raiz1 variable).

The first query is what i had before, i want trade the first for the second one.

 

I have 1 page where people can send messages, and when the query insert that message in the database it get's raiz=0. Then the guy who received the message can reply, and the reply will get raiz = 1. I want a page where i can show the first message and all the reply's to that message.

 

 

I already find why is not working, 

$teste =  mysqli_query($con, "SELECT * FROM `mensagens` WHERE `id`= '$mensagem' AND raiz = '$raiz1' ");

when i ask for the message with  `id`= '$mensagem' AND raiz = '$raiz1'   it returns 0, because the id is the ID from the main message (raiz=0). So i will need to use:

$teste =  mysqli_query($con, "SELECT * FROM `mensagens` WHERE raiz = '$raiz1' ");

But when i do that, it returns all the reply's, and i just want the reply's to 1 of the messages. How can i do that?

[CroNiX]

Not sure if this is what you want, but you can try:

$teste = mysqli_query($con, "SELECT * FROM `mensagens` WHERE `id`= '$mensagem' ORDER BY raiz ASC");

If I'm reading your scenario and data right, using the order by will return all results with "raiz = 0" (original message) being the very first result, and the rest of the results would be "raiz = 1" which are the replies. However I really don't think you DB Schema is set up the best way for this sort of thing. I'd add a parent ID column. Original messages would have parent_id of 0, and replies would have parent_id of the original question. I'd also have a datetime column for when the original message and the reply was posted then you could properly order them.

[magcr23]

Not sure if this is what you want, but you can try:

$teste = mysqli_query($con, "SELECT * FROM `mensagens` WHERE `id`= '$mensagem' ORDER BY raiz ASC");

If I'm reading your scenario and data right, using the order by will return all results with "raiz = 0" (original message) being the very first result, and the rest of the results would be "raiz = 1" which are the replies. However I really don't think you DB Schema is set up the best way for this sort of thing. I'd add a parent ID column. Original messages would have parent_id of 0, and replies would have parent_id of the original question. I'd also have a datetime column for when the original message and the reply was posted then you could properly order them.

Yes, i was thinking in a wrong way, instead of raiz=1 it have to be raiz = (id from original message). I have a datetime column in all tables.

 

But i'm not doing the add reply right, i'm doing that:

 

Form:

input type="hidden" id="IDmsg" name="IDmsg">
<tr><td>From:<input id="remetenteMSG" name="remetenteMSG" type="text" value="<?php echo $_SESSION["user"]; ?>" readonly="readonly" required ></td></tr>

<tr><td>To:<input value="<?php echo $ln['emissor']; ?>" id="destinatarioMSG" name="destinatarioMSG" type="text" readonly="readonly" required ></td></tr>

<tr><td>Subject:<input value ="Re:<?php echo $ln['assunto'];?>" id="assuntoMSG" name="assuntoMSG" type="text" readonly="readonly" required ></td></tr>
								
<tr><td>Message: <textarea id="corpoMSG" name="corpoMSG" cols="21" rows="1" required ></textarea></td></tr>

<tr><td><input type="submit" id="enviarMSG" name="enviarMSG" value="Enviar"></td></tr>

Page to insert:

$de = limpa($_POST["remetenteMSG"]);
$para = limpa($_POST["destinatarioMSG"]);
$assunto = limpa($_POST["assuntoMSG"]);
$mensagem = limpa($_POST["corpoMSG"]);
@$data = date('Y-m-d');
$raiz = $_POST["IDmsg"];
	
$msg = "INSERT INTO mensagens(id, emissor, destinatario, mensagem, assunto, data, raiz) VALUES (DEFAULT, '$de', '$para', '$mensagem', '$assunto', '$data', '$raiz' )";

mysqli_query($con, $msg);

Can you give me an idea of what am i doing wrong?

 

BTW: To a better understanding of the code there's some tips:

remetente / emissor / de = From

destinatario / para = To

assunto = Subject

[magcr23]

Forget my question before, it was stupid xD I forgot the value of the hidden field so it wasn't working.

 

My problem now is that when i do 

while($row = mysqli_fetch_assoc($teste)){	
echo '<font size="5"><b>Resposta: </b></font><br />';
echo '<font size="5"><b>   Mensagem:</b></font> <font size="4"> ' . $row['mensagem'] . '</font><br />';
}

it will repeat the original message, like that:

 

From: miguel
   Subject: raiz
   Message: is it working?

Reply: 
   Message: is it working?
Reply: 
   Mensagem: I think so...

 

what can i do to don't repeat that?

 

(query)

$abc = "SELECT * FROM `mensagens` WHERE `id` = '$mensagem' OR `raiz` = '$mensagem' ";
$teste =  mysqli_query($con, $abc);

[magcr23]

Problem solved, i used 2 querys, one to the original message, and another to the reply's