Fun one! last person to text number

Databases
1

I have two tables

userpie_history which is set out

to,user,message,date,ip,useragent

userpie_incoming which is set out
from,mesage,date,user_id

what i want to do is grab the number that has come in and find the last person that sent a message to them and then take the user and insert it in to the user_id field on userpie_incoming.

by default when a new incoming message comes in it sets the user_id as null.

hopefully i have explained enough for some one to understand all date’s are set via CURRENT_TIMESTAMP

2

i got the following for grabbing the last message sent to a number


SELECT * 
FROM  `userpie_history` 
WHERE  `to` =  '077123456789'
ORDER BY  `userpie_history`.`date` DESC 
LIMIT 0 , 1
3

Solved in the end


$findlast = $db2->prepare("SELECT * FROM  `userpie_history` WHERE  `to` = :from ORDER BY  `userpie_history`.`date` DESC LIMIT 0 , 1");
$findlast->bindParam(':from', $from3);
$findlast->execute();
$match = $findlast -> fetch();
$userid = $match["user"];

if ($userid == "")
{
echo "No User found";
exit();
} else {
echo $userid;
}
$assignid = $db2->prepare("UPDATE `userpie_incoming` SET  `user_id` = :userid WHERE `from` = :from AND `user_id` IS NULL LIMIT 1 ");
$assignid->bindParam(':userid', $userid);
$assignid->bindParam(':from', $from3);
$assignid->execute();
echo "Updated Incoming Message";
4

Glad that you found the answer and that you were kind enough to post it. Sorry that I didn’t see your message before but it is good to see that you didn’t give up until you found the solution :slight_smile: