PHP 从两个不同的表中选择 *？

Question

我正在制作一个非常简单的拍卖网站，我想阻止用户在已经是最高出价者的情况下对某件元素出价，我想阻止该元素显示。

我有一个名为 bidhistory 的表，用于存储投标人的帐户 ID。下面是我用来防止用户对自己的项目出价的内容。从 bidhistory 表中选择的正确语法是什么？我希望我说得够清楚了。

$sql="SELECT * FROM biditems WHERE NOT accountid = $accountid";

更多我的代码:

$accountid=$_SESSION['accountid'];

$sql=" SELECT * FROM biditems WHERE NOT accountid = $accountid";

$result=mysql_query($sql);

echo "Items for Auction";
$expireDay=new DateTime('2015-07-19 20:12:50');
$theDay=new DateTime('now');
$timeToEnd=$expireDay->diff($theDay);
echo $timeToEnd->format('%R%a days');

while($row=mysql_fetch_array($result)) {
$itemid=$row['itemid'];
$item=$row['biditem'];
$auctionby=$row['username'];
$description=$row['biddesc'];

echo "<ul>";
echo "<li>$item auctioned off by $auctionby. <br> Description: $description
<br> 
<a href='acceptbid.php?itemid=$itemid&item=$item'>Place bid</a></li>";
echo "</ul>";
}
?>

这是我的表格。

$sql = "CREATE TABLE account (
accountid INT(100) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL
)";

$sql = "CREATE TABLE biditems (
itemid INT(100) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
accountid INT(100),
biditem VARCHAR(30) NOT NULL,
biddesc tinytext,
dateadded DATETIME
)";

$sql = "CREATE TABLE bidhistory (
bidhistoryid INT(100) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
accountid INT(100),
biditemid INT,
bidprice INT NOT NULL,
biddate DATETIME 
)";

Answer 1

备注:

• 您想列出所有项目进行竞价，但您不想让用户对他/她自己的项目出价？我们可以做的是获取所有出价项目，然后在 while loop() 中使用基本的 if() else() 语句来检查每个项目是否被拍卖是否由用户决定。
• 如果您在 biditems 中使用 accountid 作为 username(auction by)会容易得多表，以便我们可以在索引方面更快、更轻松地将此表连接到 account 表。尽管如此，我们仍可以使用 INNER JOIN 通过使用 username 列将 biditems 表连接到您的 account 表( 希望用户名都是唯一的)。

你可以用这个:

$sql=" SELECT a.itemid, a.biditem, a.biddesc, b.accountid, b.username FROM biditems a INNER JOIN account b ON a.username = b.username";
/* THE QUERY ABOVE WILL FETCH ALL BID ITEMS */

$result=mysql_query($sql); /* EXECUTE THE QUERY */

echo "Items for Auction";
$expireDay=new DateTime('2015-07-19 20:12:50');
$theDay=new DateTime('now');
$timeToEnd=$expireDay->diff($theDay);
echo $timeToEnd->format('%R%a days');

while($row=mysql_fetch_array($result)) {
  $itemid=$row['itemid'];
  $item=$row['biditem'];
  $auctionby=$row['username'];
  $description=$row['biddesc'];
  $auctionbyid = $row['accountid']; /* accountid OF THE AUCTION-ER */

  $result2 = mysql_query($con,"SELECT accountid FROM bidhistory WHERE biditem = '$itemid' ORDER BY bidhistoryid DESC LIMIT 1");
  while($row2 = mysql_fetch_array($result2)){
    $checkauctionid = $row2['accountid'];
  }

  echo '
    <ul>
      <li>'.$item.' auctioned off by '.$auctionby.'<br>
        Description: '.$desription.'<br>';

  if($accountid != $auctionbyid && $accountid != $checkauctionid){ /* IF THE ITEM IS NOT AUCTIONED BY THE USER OR HE/SHE WAS NOT THE HIGHEST BIDDER */
    echo '<a href="acceptbid.php?itemid='.$itemid.'&item='.$item.'">Place bid</a></li>';
  } /* END OF IF $accountid IS NOT THE SAME WITH $actionbyid */

  else { /* ELSE, IT MEANS THAT THIS ITEM IS HIS/HERS, SO HE/SHE CANNOT BID ON HIS/HER OWN ITEM OR THAT HE IS THE HIGHEST BIDDER FOR THIS ITEM */
    echo 'Cannot Place Bid';
  } /* END OF ELSE */

  echo '</ul>';
} /* END OF WHILE LOOP */

• 确保在您的 acceptbid.php 文件中，您的查询仍然检查他们正在查看的项目是否未被用户拍卖。
• 再次强调，不要按原样使用 mysql_* API deprecated已经。