I cannot run a query inside another query in Mysql. Table messages. Display inbox

Question

I have a table messages, and I would like to display the most recent message. If user 1 is logged in, I would like to do this: For instance if user 1 sent an message to user 2, then user 2 to user 1, I would like to show to user 1, the newest message. So it would be the one that was sent by user 2. My table has:

id |  id_from|id_to|text 
1     1       2     abc     
2     2       1     dif      
3     3       1     jsd

So, I have to display to user 1, the ids 2 and 3.

I try this: thanks a lot!

   $consulta=mysql_query("select * from messages where id_from='".$id."' or id_to='".$id."'  ");
while($filas=mysql_fetch_array($consulta)){
    $id=$filas['id'];
    $id_to=$filas['id_to'];
    $status=$filas['status'];
    $text=$filas['text'];
    $time=$filas['time'];
    echo "lo primero: ";

$consulta2=mysql_query("select * from messages where id_from='".$id."' or id_to='".$id."' ");
    while($filas2=mysql_fetch_array($consulta2)){
        echo "es ";
        $id2=$filas2['id'];
        $id_to2=$filas2['id_to'];
        $status2=$filas2['status'];
        $text2=$filas2['text'];
        $time2=$filas2['time'];
        echo "es ";
        echo $id2;
            if($id_from==$id_from2 and $id_to==$id_to2 and $id2>$id){ 
                $id=$id2;           
                echo "el mas grande ";
                echo $id;
            }
    }
    echo $id;
    echo " ";
    //Almaceno en un vector los mensajes a mostrar
    $result  = array ( "'".$id."'");

The code never enters to the second while ,thats my Question : ) — user1905120
– user1905120, Commented Jan 2, 2013 at 6:44
And how can I make it? I would like to display the most recent message — user1905120
– user1905120, Commented Jan 2, 2013 at 6:46
Heads up! The next major release of PHP is deprecating the mysql_ family of functions. Now would be a great time to switch to PDO or mysqli. — Charles
– Charles, Commented Jan 2, 2013 at 8:44

Muhammad Raheel · Accepted Answer · 2013-01-02 06:43:44Z

0

Use a single simple query

SELECT * FROM mytable WHERE id_from != 1 AND id_to = 1

answered Jan 2, 2013 at 6:43

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1905120 Over a year ago

What I put on that table is an example the table perhaps has lots of rows, so I have to show for the user that is logged in, all the inboxes that he sent, or he received

Arun Unnikrishnan · Accepted Answer · 2013-01-02 07:50:07Z

0

I assume first column id is set to auto increment. To show the most recent activity of user with id=$userid use

SELECT * 
FROM  `messages` 
WHERE  `id_from` =  '$userid' ||  `id_to` =  '$userid'
ORDER BY  `id` DESC 
LIMIT 1

From this query we will get the single last activity of the user.From that resultant row u can check whether the activity is a sent message or recieved one and show the text accordingly.

If ur simply tring to show inbox messages with most recent one on top use

    SELECT * 
    FROM  `messages` 
    WHERE  `id_to` =  '$userid'
    ORDER BY  `id` DESC

edited Jan 2, 2013 at 7:50

answered Jan 2, 2013 at 7:23

Arun Unnikrishnan

2,3492 gold badges25 silver badges40 bronze badges

2 Comments

user1905120 Over a year ago

I want to do what facebook does. for instance you send an inbox to user 2, then to user 3, then user 2 answers you, so you have to display the message that user 2 sent to you and the one that you sent to user 3

Arun Unnikrishnan Over a year ago

Ok. So u r trying to display messages like chat .Clicking on a user will show all conversations between u and him in order.

Collectives™ on Stack Overflow

I cannot run a query inside another query in Mysql. Table messages. Display inbox

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related