PHP mysql LEFT JOIN输出 [英] PHP mysql LEFT JOIN output
问题描述
我在apache上运行了php-script.每次用户访问特定的URL时，他/她将获得csv文件.
I have php-script running on top of apache. Every time when user goes to specific URL, he/she will get csv-file.
以这种方式获取列名(感谢Daniel Figueroa:)
Column names are fetched like this (thanks to Daniel Figueroa :)
$csv_output .= "\n";
// get the column name from the first DB (ins.data)
mysql_select_db($db, $link) or die("Can not connect to DB1.");
$result = mysql_query("SHOW COLUMNS FROM ".$table." WHERE Field NOT IN
('ID','Key','Text')");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
// get the column names from the second DB (Cu.data)
mysql_select_db($db2, $link) or die("Can not connect to DB2.");
$result = mysql_query("SHOW COLUMNS FROM ".$table2." ");
;
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
$csv_output .= "\n";
对PHP脚本的实际查询如下:
Actual query on PHP-script goes like this:
$values = mysql_query(" SELECT ins.data.`Date`, ins.data.`Number`,
ins.data.`Email`, ins.data.`TargetId`, ins.data.`CSW`,
ins.data.`TSW`, ins.data.`CType`,
Cu.data.`Cus`, Cu.data.`Co`,Cu.data.`Ci`,
Cu.data.`SID`, Cu.data.`SType`
FROM ins.data
LEFT JOIN Cu.data ON (ins.data.TargetId = Cu.data.TargetID)
ORDER BY ins.data.ID DESC");
'desc'的输出:
mysql> desc ins.data;
+-------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------------------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Date | timestamp | NO | | 0000-00-00 00:00:00 | |
| Number | text | NO | | NULL | |
| Text | text | NO | | NULL | |
| Email | text | NO | | NULL | |
| TargetId | varchar(20) | NO | | NULL | |
| CSW | text | NO | | NULL | |
| TSW | text | NO | | NULL | |
| Key | text | NO | | NULL | |
| CType | text | NO | | NULL | |
+-------------------+------------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)
mysql> desc Cu.data;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| Title | decimal(15,0) | NO | | NULL | |
| Cu | text | NO | | NULL | |
| Co | text | NO | | NULL | |
| Ci | text | NO | | NULL | |
| SID | text | NO | | NULL | |
| TargetID | varchar(20) | NO | MUL | NULL | |
| SType | text | NO | | NULL | |
| empty1 | int(11) | NO | | NULL | |
| empty2 | int(11) | NO | | NULL | |
| empty3 | int(11) | NO | | NULL | |
| empty4 | int(11) | NO | | NULL | |
| empty5 | int(11) | NO | | NULL | |
| empty6 | int(11) | NO | | NULL | |
| empty7 | int(11) | NO | | NULL | |
+----------+---------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
更新3:
这不再是NATURAL LEFT JOIN问题.替换为LEFT JOIN.
This is no more NATURAL LEFT JOIN-issue. Replaced with LEFT JOIN.
将int.1-5字段添加到ins.data中，以将数据获取到csv文件中.如果没有字段empty1-5，则只有第一个数据库(ins.data)中的数据位于csv.file中.
Added fields empty1-5 to ins.data to get data to csv-file. Without fields empty1-5, only data from first db (ins.data) was on csv.file.
现在我在所有字段上都有数据，但是csv上的字段(或excel上的列名)名称顺序错误，不需要的字段(列)可见，例如Title和empty1-5.
Now i have data on all fields but field (or column names on excel) names on csv are on wrong order and not wanted fields (columns) are visible like Title and empty1-5.
有什么办法解决此问题吗?不用"SHOW COLUMNS"就可以将字段名称获取到csv文件中的其他方法吗?
Any ideas how to fix this? Some other way to get Field names to csv-file without "SHOW COLUMNS"?
我可以在csv文件值的开头加上'echo'来写我想要的内容. IE 日期；数字；电子邮件； TargetID，CSW； TSW； CType； Cu； SID； Co； Ci； SType；"但是我对PHP这么新手，我不知道如何:(
I could write with 'echo' in the beginning of csv-file values what i want. ie "Date; Number; Email; TargetID, CSW; TSW; CType; Cu; SID; Co; Ci; SType;" but i am so newbie with PHP that i don't know how :(
另一个问题是，如果字段ID是excel的第一列，则excel无法处理该问题，因此必须将其从SHOW COLUMNS输出中排除.
Another issue is that if field ID is first column on excel, excel cannot handle that and it must be excluded from SHOW COLUMNS output.
UPDATE4:向DB2(Cu.data)添加了更多的空字段，并对SQL查询进行了重新排序，现在所有值都是可见的，并且顺序正确.
UPDATE4: Added more empty-fields to DB2 (Cu.data) and reordered SQL-query, now all values are visible and on right order.
推荐答案
尝试在表名上添加别名.这也将使查询更具可读性:
Try putting aliases on table names. It will make the query more readable, too:
SELECT
i.`Date`, i.Number, i.Email, i.TID, i.CSW, i.TSW, i.CType,
c.Cu, c.Co, c.Ci, c.SID, c.TID, c.SType
FROM
ins.data AS i
LEFT JOIN
Cu.data AS c
ON i.TID = c.TID
ORDER BY
i.ID DESC ;
这篇关于PHP mysql LEFT JOIN输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
