PHP mysql LEFT JOIN output

Question

I have php-script running on top of apache. Every time when user goes to specific URL, he/she will get csv-file.

Column names are fetched like this (thanks to Daniel Figueroa :)

$csv_output .= "
";
// 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 .= "
";

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");

Output of '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)

UPDATE 3:

This is no more NATURAL LEFT JOIN-issue. Replaced with LEFT JOIN.

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.

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.

Any ideas how to fix this? Some other way to get Field names to csv-file without "SHOW COLUMNS"?

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 :(

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: Added more empty-fields to DB2 (Cu.data) and reordered SQL-query, now all values are visible and on right order.

Answer 1

EDIT

First of all, your table naming schema is weird and unusual... but assuming I understand it correctly then this query should work (if it does not then rename your tables without the dots (periods) to make things less confusing:

mysql_query('SELECT ins.data.Date, ins.data.Number, ins.data.Email, ins.data.TID, ins.data.CSW, ins.data.TSW, ins.data.CType, CU.data.SID, cu.data.SType, cu.data.CU, cu.data.CO, cu.data.Ci, FROM ins.data, cu.data ORDER BY ins.data.ID DESC');

---

According to to the mysql_query function reference, data should not end with a semicolon when using mysql_query in PHP... i never put the semicolon in there so I don't ever have a problem, that's what I initially noticed with your script (as I said i've never tried it so I don't know if thats the issue). Should be:

$values = mysql_query("SELECT Date, Number, Email, TID, CSW, TSW, CType, SID, SType, Cu, Co, Ci FROM ins.data NATURAL LEFT JOIN Cu.data ORDER BY ID DESC");

Also, when doing JOINS, usually you specify what column belongs to what table... like in the standard mysql example here:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal ".
 "FROM family LEFT JOIN food ".
    "ON family.Position = food.Position"; 

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
    echo $row['Position']. " - ". $row['Meal'];
    echo "<br />";
}
?>

I don't always use the JOIN commands either... you can use an alternative syntax like so:

mysql_query('SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a');