查询

查询

关注
发信
php - PHP服务器端相关字段的验证
php - 带条件的SQL语句
c# - 订购Northwind Access数据库的对象
python - 如何从给定列表中选择所有可能的子列表对，它们的联合将成为列表？
sql - WHERE x IN(5)与WHERE x = 5…为什么使用IN？
c# - 需要视觉控件来可视化逻辑“A跟随B”或“A取决于B和C”以及此类关系
sql - 根据另一个表中的可用性排序一个表的结果psql
mysql - MySQL Workbench中的MySQL WITH ROLLUP查询中的语法错误？
math - 计算径向速度
svg - D3.js-如何在此可折叠树中向文本添加新行？
c - 使用return 0与void main()吗？
asp.net-mvc - 如何使用ASP.NET MVC实现自定义缓存提供程序
php - 如何获取/设置session_id()还是应该自动生成？
ssh - 使用MySQL Workbench连接到Vagrant Box
sql - 与另一个表中的DateTime和Latest Value联接

php - 使用另一个查询的结果查询MySql数据库

扫码查看

我的PHP代码如下：

    $query1=("SELECT * FROM test_log WHERE '$mktime' < expiry ORDER BY id DESC");
        $result1=mysql_query($query1);
        $num1=mysql_num_rows($result1);


    while ($row1 = mysql_fetch_array($result1))
        {
        $id = $row1["id"];
        $keyword = $row1["keyword"];
        $sale = $row1["sale_rent"];
        $agents = $row1["e_num"];
        $email = $row1["email"];
        $cc_email = $row1["cc_email"];
        $expiry = $row1["expiry"];

        $query2= ("SELECT * FROM condo WHERE (location LIKE '%{$row1['keyword']}%' AND sale_rent LIKE '%{$row1['sale_rent']}%' AND e_num LIKE '{$row1['e_num']}') AND (date >= '$sendate') AND TRIM(IFNULL(`phone_1`,'')) <> '' ORDER BY sale_rent, location");
        $result2=mysql_query($query2);
    $num2=mysql_num_rows($result2);
//the code continues


如您所见，我使用$ query1的结果查询公寓表（$ query2）。我不想再这样做了。您能告诉我如何将两个查询合并为一个查询并获得相同的结果吗？

非常感谢。

最近更新
好的，这是我到目前为止所做的。

<?php
include("/path/to/my/website.com/public_html/db_connect.php");

$mktime = date('Y-m-d');
$sendate = date('Y-m-d', strtotime('1 days ago'));

$query1=("SELECT * FROM test_log AS t JOIN condo AS c ON c.location LIKE CONCAT('%', t.keyword, '%') AND c.sale_rent LIKE CONCAT('%', t.sale_rent, '%') AND c.e_num LIKE CONCAT('%', t.e_num, '%') WHERE t.expiry > '$mktime' AND c.date > '$sendate' AND TRIM(IFNULL(c.phone_1, '')) != '' ORDER BY t.id, c.sale_rent, c.location");
$result1=mysql_query($query1);
$num1=mysql_num_rows($result1);

$subject = "Latest Classified Ads - Condo";

$message_tmp  = "<div align='center'>";
$message_tmp .= "<TABLE cellSpacing=0 cellPadding=0 width=100% border=0 id='table1'>";
$message_tmp .= "<TR>";
$message_tmp .= "<TD width=100% height=20 bgcolor='#E5E5E5' align='left' valign='top'>
<p align='center'><b><font size='3'>Comprehensive Real Estate Agency Tools & Property Valuation Services at <a href=\"http://www.cashbillion.com/free-email-alerts\">http://www.cashbillion.com</a></font></b></TD>";
$message_tmp .= "</TR>";
$message_tmp .= "<TR>";
$message_tmp .= "<TD width=100% height=20 bgcolor='#EFEFEF' align='left' valign='top'>
    ~ Classified Ads<br>
    ~ Facebook Promotions<br>
    ~ Public Records Search<br>
    ~ Demographic Statistics<br>
    ~ Property Market Values<br>
    ~ Property Owners Listings<br>
    ~ Real Estate Price Trend Analysis<br>
</p></TD>";
$message_tmp .= "</TR>";
$message_tmp .= "<TR>";
$message_tmp .= "</TR>";
$message_tmp .= "</TABLE>";
$message_tmp .= "</div>";
$message_tmp .= "<h4>List of Latest Classified Ads - Condo</h4>";
$message_tmp .= "<table bgcolor='black' cellspacing='1' width='100%'><tr bgcolor='white' align='center'>";
$message_tmp .= "<td><b>Date</b></td>";
$message_tmp .= "<td><b>Location</b></td>";
$message_tmp .= "<td><b>Property Type</b></td>";
$message_tmp .= "<td><b>Tenure</b></td>";
$message_tmp .= "<td><b>Status</b></td>";
$message_tmp .= "<td><b>Sale / Rent</b></td>";
$message_tmp .= "<td><b>Size</b></td>";
$message_tmp .= "<td><b>Price</b></td>";
$message_tmp .= "<td><b>Price Psf</b></td>";
$message_tmp .= "<td><b>Rooms</b></td>";
$message_tmp .= "<td><b>Baths</b></td>";
$message_tmp .= "<td><b>Renovations</b></td>";
$message_tmp .= "<td><b>Furnishings</b></td>";
$message_tmp .= "<td><b>Phone</b></td>";
$message_tmp .= "<td><b>Advertiser</b></td>";
$message_tmp .= "<td><b>E Number</b></td>";
$message_tmp .= "</tr>";
$message_tmp .= "[new_rows]";
$message_tmp .= "</table>";
$message_tmp .= "<br>To unsubscribe, go to <a href=\"http://www.mywebsite.com/unsubscribe-from-email-alerts/\"><strong>http://www.mywebsite.com/unsubscribe-from-email-alerts/</a></strong>";
$message_tmp .= "<br>Try our free services at <a href=\"http://www.mywebsite.com/free-email-alerts/\"><strong>http://www.mywebsite.com/free-email-alerts/</a></strong> your property market information provider.";

while ($row1 = mysql_fetch_array($result1))
    {
$new_rows .= "<tr bgcolor='white' align='left'>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["date"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row1["location"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["property_type"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["tenure"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row1["status"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["sale_rent"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["size"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row1["price"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["price_psf"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["rooms"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row1["baths"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["renovations"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["furnishings"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row1["phone_1"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["advertiser"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row1["e_num"]."</td>";
$new_rows .= "</tr>";

    $email = $row1["email"];
    $cc_email = $row1["cc_email"];
    $expiry = $row1["expiry"];
    $keyword = $row1["keyword"];

    if (!empty($new_rows))
            {
            $message = str_replace("[new_rows]",$new_rows,$message_tmp);
            $message .= "<br>Your notification will expire on <font color='#FF0000'><strong>$expiry</strong></font>";
            $message .= "<br>Your requested keyword is <font color='#FF0000'><strong>$keyword</strong></font><br><br>";

    echo $email;
    echo ',';
    echo $cc_email;
    echo ',';
    echo $keyword;
    echo '<br>';
    echo $subject;
    echo '<br>';
    echo $message;
    echo '<br>';
    echo $new_rows;
    echo '<br><br>';

            }
    }

    ?>


从此代码中，应该发生的是存储在test_log中的关键字用于查询condo表的location列。然后，该查询的结果将通过电子邮件发送到与关键字相对应的电子邮件地址。电子邮件地址存储在test_log的电子邮件列中。

我还没有合并电子邮件脚本。我专注于首先显示查询结果，以检查是否应该将正确的消息发送到正确的电子邮件。

我认为循环不正常，但我也无法解释为什么...

最近更新
好的，这是我的新查询，当直接在PHP MyAdmin上直接运行时，它将不起作用。

SELECT condo.location, condo.sale_rent, condo.e_num, condo.date, condo.phone_1 innerQuery.* FROM condo JOIN (SELECT * FROM test_log WHERE '2015-04-15' < expiry ORDER BY id DESC) AS innerQuery WHERE condo.location = innerQuery.keyword AND condo.sale_rent = innerQuery.sale_rent AND condo.e_num = innerQuery.e_num AND condo.date >= '2015-04-14' AND TRIM(IFNULL(`condo.phone_1`,'')) <> '' ORDER BY condo.sale_rent, condo.location


我在PHP MyAdmin上收到的消息是：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.* FROM condo JOIN (SELECT * FROM test_log WHERE '2015-04-15' < expiry ORDER BY ' at line 1


我该如何工作？谢谢你的帮助。

最佳答案

使用JOIN：

SELECT *
FROM test_log AS t
JOIN condo AS c
ON c.location LIKE CONCAT('%', t.keyword, '%')
    AND c.sale_rent LIKE CONCAT('%', t.sale_rent, '%')
    AND c.e_num LIKE CONCAT('%', t.e_num, '%')
WHERE t.expiry > '$mktime'
    AND c.date > '$sendate'
    AND TRIM(IFNULL(c.phone_1, '')) != ''
ORDER BY t.id, c.sale_rent, c.location

关于php - 使用另一个查询的结果查询MySql数据库，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29629777/

10-11 01:42
查看更多
Powered by LMLPHP ©2025 查询 0.024435