Mysql查询插入新行 [英] Mysql query insert new row
问题描述
我有这三张桌子零售商、imovo、rbpos_epos.我要做的是匹配它们，然后得到一个结果，然后打印在页面中.
我需要做的是将这些结果插入到一个新表中，我将制作一个匹配"按钮，它将查询两个表零售商和 imovo，并且匹配将转到名为 :matching 的新表.
为了避免重复，我需要的是从零售商表中删除并 imovo 匹配的行..
这是我用来匹配的查询:
$data = mysql_query( "select r.`time`, r.`epos_id`, r.`date`,re.`location`,i.`user_id`,i.`mobile_number`,i.`时间`、i.`rbpos_id`、i.`日期`来自零售商 r加入 rbpos_epos re on r.epos_id = re.epos_id在 i.rbpos_id = re.rbpos_id 上加入 imovo i和 addtime(r.`time`, '0:0:50') >i.`时间`和 r.`time` <i.`时间`和 r.`date` = i.`date`;")或死(mysql_error())；打印 "";打印<tr>";打印日期:</th><th>时间</th><th>位置</th><th>用户ID</th><th>手机号码</th>";打印</tr>";while($info = mysql_fetch_array($data)){打印<tr>";打印 " <td>".$info['date'] .</td><td>".$info['time'] ." </td><td>".$info['location'] ." </td><td>".$info['user_id'] ." </td><td>".$info['mobile_number'] ." </td></tr>";}打印的行是我需要插入匹配表中的行.谢谢！
我试过了:
$data = mysql_query( "INSERT INTO Matching(date, time, location, user_id, phone)选择r.`日期`，`时间`，重新.`位置`，i.`user_id`，i.`mobile_number`来自零售商 r加入 rbpos_epos re on r.epos_id = re.epos_id在 i.rbpos_id = re.rbpos_id 上加入 imovo i和 addtime(r.`time`, '0:0:50') >i.`时间`和 r.`time` <i.`时间`和
r.date = i.date;")但我收到错误:
列数与第 1 行的值数不匹配
解决方案 使用 INSERT INTO ... SELECT ...:
INSERT INTO Matching(date, time, location, user_id, phone)选择r.`日期`，`时间`，重新.`位置`，i.`user_id`，i.`mobile_number`来自零售商 r加入 rbpos_epos re on r.epos_id = re.epos_id在 i.rbpos_id = re.rbpos_id 上加入 imovo i和 addtime(r.`time`, '0:0:50') >i.`时间`和 r.`time` <i.`时间`和 r.`date` = i.`date`;
然后你必须在按钮match被点击时执行这个INSERT.
更新:如果要删除匹配的行.您可以使用 JOINDELETE代码>.类似的东西:
删除 r, re, i来自零售商 r加入 rbpos_epos re on r.epos_id = re.epos_id在 i.rbpos_id = re.rbpos_id 上加入 imovo i和 DATE(r.`date`) = DATE(i.`date`);
这将从所有三个表中删除所有匹配的行，如手册中所述:
<块引用>对于多表语法，DELETE 从每个 tbl_name 中删除满足条件的行.
SQL Fiddle 演示
注意:最好使用单列 DATETIME 而不是两个单独的部分 date 和 time就像我在演示中所做的那样.然后你可以使用 MySQL 的日期和时间函数来控制它们，就像我在连接条件 DATE(r.date) = DATE(i.date) 中所做的一样.
i have this three tables
Retailer, imovo, rbpos_epos.
What i do is i match them, and get a result which i print in in a page.
What i need to do is insert those results into a new table, i will make a "Match" button which will query the two tables retailer and imovo, and the matches will go to the new table named :matching.
What i need in order to avoid duplications.. is to remove from the retailer table and imovo the matched rows..
Here is the query i use to match:
$data = mysql_query( "select r.`time`, r.`epos_id`, r.`date`,re.`location`,i.`user_id`,i.`mobile_number`,
i.`time`, i.`rbpos_id`, i.`date`
from retailer r
join rbpos_epos re on r.epos_id = re.epos_id
join imovo i on i.rbpos_id = re.rbpos_id
and addtime(r.`time`, '0:0:50') > i.`time`
and r.`time` < i.`time`
and r.`date` = i.`date`;
")
or die(mysql_error());
Print "<table border cellpadding=3>";
Print "<tr>";
Print "<th>Date:</th>
<th>Time</th>
<th>Location</th>
<th>User ID</th>
<th>Mobile Number</th>";
Print "</tr>";
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print " <td>".$info['date'] . "</td>
<td>".$info['time'] . " </td>
<td>".$info['location'] . " </td>
<td>".$info['user_id'] . " </td>
<td>".$info['mobile_number'] . " </td>
</tr>";
}
The printed rows are those i need to insert in the matching table..
Thanks!
I tried this:
$data = mysql_query( "INSERT INTO Matching(date, time, location, user_id, phone)
SELECT
r.`date`,
`time`,
re.`location`,
i.`user_id`,
i.`mobile_number`
from retailer r
join rbpos_epos re on r.epos_id = re.epos_id
join imovo i on i.rbpos_id = re.rbpos_id
and addtime(r.`time`, '0:0:50') > i.`time`
and r.`time` < i.`time`
and
r.date = i.date;
")
But i get the error:
Column count doesn't match value count at row 1
解决方案 Use INSERT INTO ... SELECT ...:
INSERT INTO Matching(date, time, location, user_id, phone)
SELECT
r.`date`,
`time`,
re.`location`,
i.`user_id`,
i.`mobile_number`
from retailer r
join rbpos_epos re on r.epos_id = re.epos_id
join imovo i on i.rbpos_id = re.rbpos_id
and addtime(r.`time`, '0:0:50') > i.`time`
and r.`time` < i.`time`
and r.`date` = i.`date`;
Then you have to do this INSERT when the button match is clicked.
Update: If you want to delete the matched rows. You can DELETE with JOIN. Something like:
DELETE r, re, i
from retailer r
join rbpos_epos re on r.epos_id = re.epos_id
join imovo i on i.rbpos_id = re.rbpos_id
and DATE(r.`date`) = DATE(i.`date`);
This will delete all the matched rows from all of the three tables, as explained in the manual:
For the multiple-table syntax, DELETE deletes from each tbl_name the
rows that satisfy the conditions.
SQL Fiddle Demo
Note that: it is better to use single column DATETIME instead of two separate parts date and time like what I did in the demo. Then you can use the MySQL Date and time functions to control them, like what I did in the join condition DATE(r.date) = DATE(i.date).
这篇关于Mysql查询插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
登录
关闭
扫码关注1秒登录
发送“验证码”获取
|
15天全站免登陆