Pwede ko ba innerjoin na lang

[loktoy]

pwede ko ba pagsamahin o i join yan tatlo database post, registration at comment..

$sql = mysqli_query($con, "SELECT *, UNIX_TIMESTAMP() - date_created AS TimeSpent FROM post inner join registration on registration.user_id = post.user_id") or die (mysqli_error());
while($post_row = mysqli_fetch_array($sql,)){
$id_user = $post_row['username'];
$id_comment = $post_row ['comment_id'];
?>
<hr>

<div class="">
<?php echo $id_user;?><a href="postinsert1.php?id=<?php echo $id_comment;?>"><?php echo $post_row['title']; ?></a>
<?php
}
$sql_query = mysqli_query($con, "SELECT * FROM comment inner join registration on registration.user_id = comment.user_id") or die (mysqli_error());
while($comment_row = mysqli_fetch_array($sql_query)){
$id_user = $comment_row['username'];
$id_comment = $comment_row ['comment_id'];
?>
<a href="comment1.php?id_comment=<?php echo $id_comment;?>"></a>


<?php

 

[🤖]

Yes, you can join the "post", "registration", and "comment" tables together using an inner join. In your code, you are already performing inner joins between the "registration" and "post" tables, as well as between the "registration" and "comment" tables.

To retrieve data from all three tables, you can modify your query to include the "comment" table as well:

$sql = mysqli_query($con, "SELECT post.*, registration.*, comment.*, UNIX_TIMESTAMP() - post.date_created AS TimeSpent 
                            FROM post 
                            INNER JOIN registration ON registration.user_id = post.user_id 
                            INNER JOIN comment ON comment.user_id = registration.user_id") or die(mysqli_error());

while ($row = mysqli_fetch_array($sql)) {
    $id_user = $row['username'];
    $id_comment = $row['comment_id'];
    ?>

    <hr>
    <div class="">
        <?php echo $id_user; ?><a href="postinsert1.php?id=<?php echo $id_comment; ?>"><?php echo $row['title']; ?></a>
    </div>

    <?php
}

In this modified query, we added the "comment" table to the join condition, linking it with the "registration" table based on the "user_id" column. This will retrieve data from all three tables and allow you to access the relevant columns in your loop.

 

[loktoy]

Yes, you can join the "post", "registration", and "comment" tables together using an inner join. In your code, you are already performing inner joins between the "registration" and "post" tables, as well as between the "registration" and "comment" tables.

To retrieve data from all three tables, you can modify your query to include the "comment" table as well:

$sql = mysqli_query($con, "SELECT post.*, registration.*, comment.*, UNIX_TIMESTAMP() - post.date_created AS TimeSpent
                            FROM post
                            INNER JOIN registration ON registration.user_id = post.user_id
                            INNER JOIN comment ON comment.user_id = registration.user_id") or die(mysqli_error());

while ($row = mysqli_fetch_array($sql)) {
    $id_user = $row['username'];
    $id_comment = $row['comment_id'];
    ?>

    <hr>
    <div class="">
        <?php echo $id_user; ?><a href="postinsert1.php?id=<?php echo $id_comment; ?>"><?php echo $row['title']; ?></a>
    </div>

    <?php
}

In this modified query, we added the "comment" table to the join condition, linking it with the "registration" table based on the "user_id" column. This will retrieve data from all three tables and allow you to access the relevant columns in your loop.

problima ko po is maraming sya lumalabas o nadodoble po sya ng mas marami yun output

 

[🤖]

problima ko po is maraming sya lumalabas o nadodoble po sya ng mas marami yun output

If you are getting duplicate or multiple rows in the output, it could be due to the way your tables are structured or the data in them. Here are a few potential reasons and solutions:

1. Duplicate data: Make sure that there are no duplicate entries in any of the tables. You can use the DISTINCT keyword to eliminate duplicate rows from the result set. Modify your query like this:

php
   $sql = mysqli_query($con, "SELECT DISTINCT post.*, registration.*, comment.*, UNIX_TIMESTAMP() - post.date_created AS TimeSpent
                            FROM post
                            INNER JOIN registration ON registration.user_id = post.user_id
                            INNER JOIN comment ON comment.user_id = registration.user_id") or die(mysqli_error());

This will ensure that only unique rows are returned.

2. Incorrect join condition: Check if the join conditions are accurate and do not lead to multiple matches. For example, if the same user has multiple comments or posts, the join may produce multiple rows. In such cases, you might need to refine the join conditions or add additional filtering criteria to get the desired result.

3. Missing or incorrect primary/foreign key relationships: Ensure that the primary and foreign key relationships between the tables are correctly defined. This will help in linking the records accurately and avoiding duplicate results.

4. Data integrity issues: Check for data integrity problems, such as incorrect or inconsistent data. Make sure that the values in the join columns are matching correctly across the tables.

By analyzing these possible issues and implementing the appropriate solutions, you should be able to avoid getting duplicate or multiple rows in your output.