mysql接收并比较来自多个表的数据

Question

the database tables for debat and comments are the same. it seems that $title and $date isnt working. how can i get the information from both, and order by date?

<?php
$showwall = mysql_query("(SELECT * FROM debat WHERE user='$user') UNION (SELECT * FROM comments WHERE user='$user') ORDER BY date LIMIT 0, 25");
$results = array();
while($wall = mysql_fetch_array($showwall, MYSQL_ASSOC)){ // Line 21
$results[] = $wall;

<li class="comment">
<div class="comment">
    <div class="comment-author vcard user frame">
<?php echo "<a href=view.php?action=view&ID=".$wall['ID']."><img src=images/avatar/".$select['avatar']." class=avatar avatar-70 photo height=70 width=70 /></a>"; ?>
    </div>
    <div class="message">
    <span class="reply-link"><?php echo "<a href=view.php?action=edit&ID=".$wall['ID'].">"; ?> Edit </a></span>

<div class="info"> 
<h2><?php echo $wall['title']; ?></h2>
<span class="meta">
<?php 
$date->setTimestamp($wall['date']);
echo "Dato: ";
echo $date->format('d-m-Y') . "
";
echo "Kl: ";
echo $date->format('H:i:s') . "
";
?>
</span>
</div>
        <div class="comment-body ">
        <p><?php echo $wall['comment']; ?></p>
        </div>
    </div>
</div>
</li>

<?php } ?>

I want to add another database to load from, it seems to leave a blank result.

$showwall = @mysql_query("(SELECT * FROM debat WHERE user='$user') UNION (SELECT * FROM comments WHERE user='$user') UNION (SELECT * FROM nyheder WHERE user='$user') ORDER BY date LIMIT 0, 25");

the database contains tables with the same names, but some have more fields than others. So this is isnt the right way to do it?

Answer 1

$showwall = @mysql_query("(SELECT * FROM debat WHERE user='$user') UNION (SELECT * FROM comments WHERE user='$user') UNION (SELECT * FROM nyheder WHERE user='$user') ORDER BY date LIMIT 0, 25")

As mentioned in the comments, the @ will suppress error messages; those are there for a reason. You're also not checking the result - adding or die (mysql_error()) would give you the error message from the database.

In this case, the issue is because you're using UNIONs. That will only work if all tables have columns in the same order, and I suspect that that isn't the case. I think you just need a simple JOIN:

SELECT * FROM debat d 
    INNER JOIN comments c ON d.user=c.user 
    INNER JOIN nyheder n ON d.user=n.user
WHERE d.user='$user'

One final note - you're using mysql_* functions, which are being removed from PHP. You should look at moving to mysqli_* or PDO instead - they both make it easier for you to write safer code.