update database

[magcr23]

Hi guys, i'm doing a private message system, but it's not working very well.

there's one of the problems:

I want to update read=0 to read=1 only when it's the receiver who reads the message, but it update's even if the guy who reads is the one who sen'ts.

 

that's what i'm doing:

$utilizador = $_SESSION["user"];
$message=$_GET['msg'];
$result = mysqli_query($con, "SELECT * FROM message WHERE id= $message");
$ln = mysqli_fetch_assoc($result);
					
$abc = "SELECT * FROM `message` WHERE `raiz` = '$message' ";
$teste =  mysqli_query($con, $abc);			

if($utilizador = $ln['to']){
  $update= "UPDATE `messages` SET read= '1' WHERE id = '$message' OR raiz = '$message' ";
  mysqli_query($con, $update);
}

and that's my database:

id, from, to, subject, message, date, raiz, read

 

what am i doing wrong?

[Ch0cu3r]

You need to use the comparison operator ( == ) not the assignment operator ( = )  here

if($utilizador = $ln['to']){
               ^
               |
         this should be ==

[magcr23]

 

You need to use the comparison operator ( == ) not the assignment operator ( = )  here

if($utilizador = $ln['to']){
               ^
               |
         this should be ==

that problem is fixes, but there's another:

 

i have 2 things, the original message, and the reply message(there's the errors we are talking about in this topic)

if is an original message raiz=0,

if is an reply message raiz = ID from original message

 

there's the problem:

when i do that:

$utilizador = $_SESSION["user"];
$message=$_GET['msg'];
$result = mysqli_query($con, "SELECT * FROM message WHERE id= $message");
$ln = mysqli_fetch_assoc($result);
                    
$abc = "SELECT * FROM `message` WHERE `raiz` = '$message' ";
$teste = mysqli_query($con, $abc);            

if($utilizador = $ln['to']){
  $update= "UPDATE `messages` SET read= '1' WHERE id = '$message' OR raiz = '$message' ";
  mysqli_query($con, $update);
}

i'm using the $ln['to'] from the original message, and i need to use it from the last reply

[magcr23]

I think the problem is in $result, but i've alraedy tried to change it, but all i can do is make it worst...

Edited June 25, 2015 by magcr23

[mac_gyver]

your raiz/root column only has to do with displaying a list of the original message and the replies that belong with that message. it doesn't have anything to do with displaying a specific message or marking a message as being read. displaying a specific message or marking a message as being read only involves the id of that specific message.

 

the only people who can view any specific message are the sender or the recipient. your first query should enforce that condition -

$result = mysqli_query($con, "SELECT * FROM message WHERE id = $message AND (`to` = $utilizador OR `from` = $utilizador)");

the update query, which in your last post above didn't correct the php logic to use a == comparison, should be -

if($utilizador == $ln['to']){
  $update= "UPDATE messages SET `read` = 1 WHERE id = $message";
  mysqli_query($con, $update);
}

note: read is a reserved mysql keyword (along with from and to in the first query) and must be enclosed in back-ticks `` to prevent mysql errors. you would be getting a mysql error from your update query. your code needs to ALWAYS have error checking logic in it, to get your code to tell you when, where, and why something is failing.

 

lastly, the $_GET['msg']/$message are external data and can be anything. you MUST validate that they are of the expected format and you must protect against sql injection. the best way of protecting against sql injection is to use prepared queries. for what you are using the value for in your code, someone could inject sql that allows them to display ANY message that belongs to anyone. which defeats the point of a Private Message system.
 

Edited June 25, 2015 by mac_gyver

[magcr23]

your raiz/root column only has to do with displaying a list of the original message and the replies that belong with that message. it doesn't have anything to do with displaying a specific message or marking a message as being read. displaying a specific message or marking a message as being read only involves the id of that specific message.

 

the only people who can view any specific message are the sender or the recipient. your first query should enforce that condition -

$result = mysqli_query($con, "SELECT * FROM message WHERE id = $message AND (`to` = $utilizador OR `from` = $utilizador)");

the update query, which in your last post above didn't correct the php logic to use a == comparison, should be -

if($utilizador == $ln['to']){
  $update= "UPDATE messages SET `read` = 1 WHERE id = $message";
  mysqli_query($con, $update);
}

note: read is a reserved mysql keyword (along with from and to in the first query) and must be enclosed in back-ticks `` to prevent mysql errors. you would be getting a mysql error from your update query. your code needs to ALWAYS have error checking logic in it, to get your code to tell you when, where, and why something is failing.

 

lastly, the $_GET['msg']/$message are external data and can be anything. you MUST validate that they are of the expected format and you must protect against sql injection. the best way of protecting against sql injection is to use prepared queries. for what you are using the value for in your code, someone could inject sql that allows them to display ANY message that belongs to anyone. which defeats the point of a Private Message system.

 

 

I did what you said, but it still doing the same, it only updates the original message, the reply's he keep's ignoring. I think i know where the error is, 

if($utilizador == $ln['to']){
  $update= "UPDATE messages SET `read` = 1 WHERE id = $message";
  mysqli_query($con, $update);
}

that's only updating where id=$message, and that's only true to the original message. To the reply's it have to be:

if($utilizador == $ln['to']){
  $update= "UPDATE messages SET `read` = 1 WHERE raiz= $message";
  mysqli_query($con, $update);
}

how can i do that?

 

if i place that in an else it won't work, because the first clause will be always true (page always show the original message).

Edited June 25, 2015 by magcr23

[mac_gyver]

the problem seems to be because the ?msg=x that you are putting into your links are only putting an x value from the original message, not the replies.

[magcr23]

the problem seems to be because the ?msg=x that you are putting into your links are only putting an x value from the original message, not the replies.

i'm sending  ?reply=x from this page

 

that's what i'm doing:

echo '<font size="5"><b>message: </b></font>';
echo'<br />';
echo '<font size="5"><b>   from:</b></font> <font size="4"> ' . $ln['from'] . '</font><br/>';
echo '<font size="5"><b>   subject:</b></font> <font size="4"> ' . $ln['subject'] . '</font><br />';
echo '<font size="5"><b>   message:</b></font> <font size="4"> ' . $ln['message'] . '</font><br />';
echo '<br />';

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

i'm showing the original message and then the reply's. To reply i click in an button above.

 

resume: i have a page where it show's all the subjects of original messages, then the user choses wich one he want's to open, and it opens a page where it updates the database to read=1, show's the original message and the reply's.

 

if you want i can show all my file

Edited June 25, 2015 by magcr23