MySQL query (Posts history) Yii

[Koji6ac9H]

Hello everyone.
I need help with a question:
We have posts table (my own forum on Yii), structure:

[IMG]http://s28.postimg.org/moxiu0vst/stru.png[/IMG]

post_id = 0 means it's root post, else post_id is id of parent (root) post

I need to make a history of posts that will keep to my posts and and those in which I was responsible.

E.G.
data:
id : 1, title: FirstPost, post_id : 0, user : Alex
id : 2, title: SecondPost, post_id : 0, user : Matt
id : 3, title: null, text: reply for SecondPost, post_id : 2, user : Alex

History:
FirstPost - cuz i wrote
SecondPost - cuz i answered here

how can i get it in one (if possible) query for FOREACH it then?

P.S. Sorry for my English.

[Bushmaster030]

This is what I've come up with. You can change it as needed.

<?php
    // Using the PDO extension.
    $stmt = $pdo->prepare("SELECT * FROM posts_table WHERE user_id='' ORDER BY created DESC LIMIT 5");
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($result as $key => $value) {
    	echo '<p>Post ID: '.$value['id'].'</p>';
    }
?>

[DrTaxi]

You forgot parameters.
Also, since OP is using Yii, they probably aren't writing their own queries.

[Koji6ac9H]

[QUOTE=Bushmaster030;45521046]This is what I've come up with. You can change it as needed.

<?php
    // Using the PDO extension.
    $stmt = $pdo->prepare("SELECT * FROM posts_table WHERE user_id='' ORDER BY created DESC LIMIT 5");
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($result as $key => $value) {
    	echo '<p>Post ID: '.$value['id'].'</p>';
    }
?>

[/QUOTE]

Nope. ur code just fetching posts but i need fetch and get parent posts where i wrote

[Nexzer]

you need a recursive function for that i believe, ill take a look at in an hour.
can you export the sql table and upload it somewhere?

[Koji6ac9H]

[QUOTE=Lizart;45522551]you need a recursive function for that i believe, ill take a look at in an hour.
can you export the sql table and upload it somewhere?[/QUOTE]

[URL="https://www.dropbox.com/s/7hb7roly1gggoml/ultimase_gmod%20%283%29.sql"]https://www.dropbox.com/s/7hb7roly1gggoml/ultimase_gmod%20%283%29.sql[/URL]

if do recursive function that need give all model for parsing... e.g. makeHistoryRecFunc(Post::model()->findAll()) it's so big query, we need to select only my and post where i'm answered.

[Nexzer]

<style>

.thread{
    border: 1px solid red;
    margin-bottom: 20px;
    padding: 5px;
}
.post{
    border: 1px solid green;
    margin-bottom:5px;
}
</style>

<?php
//use ur own connection here
$con = mysqli_connect("", "", "", "") or die($con->error);

$userid = 1;
$threadResult = $con->query("SELECT * FROM posts WHERE user_id='$userid' ORDER BY id ASC") or die($con->error);
$i = 1;
while ($thread = $threadResult->fetch_object()) {
    $postResult = $con->query("SELECT * FROM posts WHERE post_id='$thread->id'") or die($con->error);
    if ($postResult->num_rows > 0) {
        echo "<div class='thread'>";
        echo "<p>thread: #$i</p>";
        echo "ID: " . $thread->id . "<br>";
        echo $thread->text;

        while ($post = $postResult->fetch_object()) {
            echo "<div class='post'>";
            echo "ID: " . $post->id . "<br>";
            echo $post->text;     
            echo "</div>";
        }
        echo "</div>";
        $i++;
    }
}
?>

this wil fetch all the threads where user_id 1 is involved
the code might not be the prettiest but it does the job.

[Koji6ac9H]

[QUOTE=Lizart;45522910]

<style>

.thread{
    border: 1px solid red;
    margin-bottom: 20px;
    padding: 5px;
}
.post{
    border: 1px solid green;
    margin-bottom:5px;
}
</style>

<?php
//use ur own connection here
$con = mysqli_connect("", "", "", "") or die($con->error);

$userid = 1;
$threadResult = $con->query("SELECT * FROM posts WHERE user_id='$userid' ORDER BY id ASC") or die($con->error);
$i = 1;
while ($thread = $threadResult->fetch_object()) {
    $postResult = $con->query("SELECT * FROM posts WHERE post_id='$thread->id'") or die($con->error);
    if ($postResult->num_rows > 0) {
        echo "<div class='thread'>";
        echo "<p>thread: #$i</p>";
        echo "ID: " . $thread->id . "<br>";
        echo $thread->text;

        while ($post = $postResult->fetch_object()) {
            echo "<div class='post'>";
            echo "ID: " . $post->id . "<br>";
            echo $post->text;     
            echo "</div>";
        }
        echo "</div>";
        $i++;
    }
}
?>

this wil fetch all the threads where user_id 1 is involved
the code might not be the prettiest but it does the job.[/QUOTE]

thanks but this way (fetch root threads then do new query) i know.
I'm trying to create it in ONE query using relations JOINS etc..