我正在处理的一些代码遇到了问题,这些代码可以达到这个目的:首先,它将从一个表中提取具有相同公司ID并且是类型上传的所有记录,然后将结果与第二个表中的记录进行比较。通常,这对我来说不是问题...简单。
也就是说,我的问题在于几个因素:查询来自一系列类。这是正在使用的基本功能:
public function query($query, $params = array())
{
try {
//$query = $this->build_query($query, $table);
$params = $this->build_params($params);
$params = $this->clean_params($query, $params);
$this->lastquery = $this->pdo->prepare($query);
//$this->lastquery->bindParam(':table', $this->DBPrefix . $table, PDO::PARAM_STR); // must always be set
foreach ($params as $val)
{
$this->lastquery->bindParam($val[0], $val[1], @$val[2]);
}
$this->lastquery->execute();
//$this->lastquery->debugDumpParams();
}
catch(PDOException $e) {
//$this->lastquery->debugDumpParams();
$this->error_handler($e->getMessage());
}
//$this->lastquery->rowCount(); // rows affected
}
现在,这就是我得到的代码。请注意,company_id在此之前提供,$DBPrefix定义为数据库所有表上使用的前缀。哦,暂时忽略参数...我还没有包括那部分,但它会完成:
unlink($includeFile);
//Start Scan Procedures
//Step one - Read all records with type "uploads" & company_id that match current company_id
$query = "SELECT property_id, account_id, company_id, description, pict_url, photo_uploaded, bed,
bath, sqft, built, embed_url, ext_tour_url, street, city, state, zip, unit, agent,
lat, lng, mlsid, ptype, pets, furnished, length, security_deposit, pet_deposit,
filename, created, type, status
FROM " . $DBPrefix . "properties_uploads
WHERE company_id = " . $company_id . "
AND type = 'uploads'";
$db->query($query);
$rec_to_upload = $db->result();
$count_uploads = 0;
foreach ($rec_to_upload as $rec) {
$query = "SELECT property_id
FROM " . $DBPrefix . "properties
WHERE company_id = " . $company_id . "
AND street = '" . $rec['street'] . "'
AND city = '" . $rec['city'] . "'
AND state = '" . $rec['state'] . "'
AND unit = '" . $rec['unit'] . "'";
if ($db->numrows() == 0) {
//update to be an original type
$query = "UPDATE " . $DBPrefix . "properties_uploads
SET type = 'original'
WHERE property_id = " . $rec['property_id'];
$db->query($query);
$count_uploads++;
} else {
//update to be a duplicate type
$query = "UPDATE " . $DBPrefix . "properties_uploads
SET type = 'duplicate'
WHERE property_id = " . $rec['property_id'];
$db->query($query);
}
}
当我自己通过 MYSQL 运行 SQL 查询时,它们运行得很好......我每次都得到正确的回答。
但是,当我对此进行测试时,$rec_to_upload 结果作为单个记录返回,而不是应该显示的 9。它不会运行 foreach 循环超过一次。如果我运行 numrows 函数,我们必须确定行数,它在第一次查询时是正确的,但它在第一次通过内部查询时保留其结果,然后转到 0 并在 0 和 1 之间反弹。属性表为空,因此应始终为 0。
我不能做$rec['城市'],因为它只运行一条记录。如果我做 $rec_to_upload['city'],就会有一个结果,但这仍然不是多条记录的解决方案。
虽然它应该将所有记录的"类型"更改为原始记录或重复记录,但它会保留一些作为上传。
最后,第一个查询的结果似乎推了两次:我得到了关联结果和数值结果。
提前感谢您提供的任何帮助。如果您有任何问题需要帮助澄清,我非常乐意回答。
其他信息。它从第一个有问题的查询开始。我做了一个备份,然后在sql之后清除了所有内容,直到我只剩下这个:
//Start Scan Procedures
//Step one - Read all records with type "uploads" & company_id that match current company_id
$query = "SELECT property_id, account_id, company_id, description, pict_url, photo_uploaded, bed,
bath, sqft, built, embed_url, ext_tour_url, street, city, state, zip, unit, agent,
lat, lng, mlsid, ptype, pets, furnished, length, security_deposit, pet_deposit,
filename, created, type, status
FROM " . $DBPrefix . "properties_uploads
WHERE company_id = " . $company_id . "
AND type = 'uploads'";
$db->query($query);
$rec_to_upload = $db->result();
$i = 1;
foreach ($rec_to_upload as $rtu) {
echo $i . '<br>';
$i++;
}
echo $db->numrows();
结果:它记录了62条记录。有 9 条记录,有 30 列。看起来它已经通过并逐个字段地放置一条记录,并将字段作为单独的记录......关联和数字,而不是输入行。
请考虑以下事项。我指定的要求与您的要求略有不同,但我认为原则保持不变:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,type VARCHAR(20) NOT NULL
);
INSERT INTO my_table VALUES
(1,'Adam','unreconciled'),
(2,'Ben','reconciled'),
(3,'Charlie','reconciled'),
(4,'Adam','unreconciled'),
(5,'Ben','unreconciled'),
(6,'Dan','unreconciled');
SELECT * FROM my_table;
+----+---------+--------------+
| id | name | type |
+----+---------+--------------+
| 1 | Adam | unreconciled |
| 2 | Ben | reconciled |
| 3 | Charlie | reconciled |
| 4 | Adam | unreconciled |
| 5 | Ben | unreconciled |
| 6 | Dan | unreconciled |
+----+---------+--------------+
SELECT x.*
, CASE WHEN y.id IS NOT NULL THEN 'duplicate' ELSE 'original' END type
FROM my_table x
LEFT
JOIN my_table y
ON y.name = x.name
AND y.id < x.id
WHERE x.type = 'unreconciled';
+----+------+--------------+-----------+
| id | name | type | type |
+----+------+--------------+-----------+
| 1 | Adam | unreconciled | original |
| 4 | Adam | unreconciled | duplicate |
| 5 | Ben | unreconciled | duplicate |
| 6 | Dan | unreconciled | original |
+----+------+--------------+-----------+
UPDATE my_table x
LEFT
JOIN my_table y
ON y.name = x.name
AND y.id < x.id
SET x.type = CASE WHEN y.id IS NOT NULL THEN 'duplicate' ELSE 'original' END
WHERE x.type = 'unreconciled';
Query OK, 4 rows affected (0.04 sec)
SELECT * FROM my_table;
+----+---------+------------+
| id | name | type |
+----+---------+------------+
| 1 | Adam | original |
| 2 | Ben | reconciled |
| 3 | Charlie | reconciled |
| 4 | Adam | duplicate |
| 5 | Ben | duplicate |
| 6 | Dan | original |
+----+---------+------------+
这相当于总共一个查询。不是 2 个查询,不是 3 个查询,不是每个循环 1 个查询。只需 1 个查询。如果你能根据你的目的调整它,它将比你现在的解决方案快几个数量级。
解决了!
考虑到它正在覆盖$db>结果,我所做的是尝试几种方法来防止它......尝试填充数组。我最终通过简单地做了:
while ($row = $db->result()) {
$prop_data[] = $row;
}
然后从那里开始。感谢所有试图提供帮助的人!