MySQL PHP CSV File Export

Question

First of, apologies for some of the code below. I am new to PHP and learning and I know the code I have written is neither the most elegant or most efficient.

I am attempting to create a function which generates a CSV type file for use in excel by exporting data from a table in MySQL. So far I have got everything working except that I would like to substitute one of the variables in my table from 'Client ID' (a number) to 'Company Name' (a text value) so it makes the final exported file easier to read.

My code thus far is like this (again apologies!),

//Connect with db
global $smarty;
$tpl_vars = $smarty->get_template_vars();
global $gCms;
$db = &$gCms->GetDb();

//Check config settings
$config = $tpl_vars['invoice_export_columns'];
$headers = str_replace('"','\'',$config);
$rows = str_replace('"','',$config);
$start = $tpl_vars['from'];
$start = date("'Y-m-d'", $start);
$end = $tpl_vars['to'];
$end = date("'Y-m-d'", $end);

//Get client name
$modops = cmsms()->GetModuleOperations();
$joms = $modops->get_module_instance('JoMS');
$client = $tpl_vars['clientrp'];
$query = 'SELECT * FROM '.cms_db_prefix() .'module_joms_clients
    WHERE company_name = ?';
$row = $db->GetRow($query, array($client));
$client = $row['client_id'];
$clientid = "'$client'";

//Check available statuses
if (isset($tpl_vars['csvdraft'])) { $draft = '\'14\''; } else { $draft = '\'0\''; }
if (isset($tpl_vars['csvsent'])) { $sent = '\'15\''; } else { $sent = '\'0\''; }
if (isset($tpl_vars['csvpaid'])) { $paid = '\'25\''; } else { $paid = '\'0\''; }

//Connect with invoices
$db = cmsms()->GetDb();
$table = 'cms_module_joms_invoice_headers';
$file = 'export';

//Create headers
$result = mysql_query("SHOW COLUMNS FROM ".$table." WHERE Field IN ($headers);");
$i = 0;
if (mysql_num_rows($result) > 0) 
{
   while ($row = mysql_fetch_assoc($result)) 
   {                    
      $csv_output .= $row['Field'].", ";
      $i++;   
   }
}
$csv_output .= "\n";

//Create body
if ( $clientid == "''" ) {
$values = mysql_query("SELECT $rows FROM ".$table." WHERE invoicedate >= $start AND invoicedate <= $end AND (status_id = $draft OR status_id = $sent OR status_id = $paid) ");
} else {
$values = mysql_query("SELECT $rows FROM ".$table." WHERE invoicedate >= $start AND invoicedate <= $end AND (status_id = $draft OR status_id = $sent OR status_id = $paid) AND client_id = $clientid ");    
}
while ($rowr = mysql_fetch_row($values)) 
{
   for ($j=0;$j<$i;$j++) 
   {   
       $csv_output .= $rowr[$j].", ";       
   }
   $csv_output .= "\n";
}

//Write file
$myFile = "uploads/csv/invoice.csv";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $csv_output);
fclose($fh);

Their is a column in the table called client_id and this is currently being outputted in the csv body as a 2 digit numerical value. I would like to substitute this for the actual name. Earlier in the code I have managed to do the same thing in reverse (under //Get client name) but I don't know how to do this the other way round whilst generating the $csv_output at the same time.

The $csv_output looks something like this,

invoicenumber client_id invoicedate status_id taxrate amountnett
100 2 2012-06-14 00:00:00 15 19.6 50
103 3 2012-06-16 00:00:00 15 20 23.5
104 2 2012-06-27 00:00:00 15 20 50

What I want to do is substitute the values from the column client_id with the values from the column company_name in the 'module_joms_clients' table.

I appreciate this is a tricky question to answer but I thought I would see if anyone could help at all.

EDIT

I have tried this code below but it is not working as yet and I am not sure why.

//Create body
$values = mysql_query("SELECT $rows FROM ".$table." JOIN cms_module_joms_clients ON cms_module_joms_invoice_headers.client_id = cms_module_joms_clients.company_name WHERE invoicedate >= $start AND invoicedate <= $end AND (status_id = $draft OR status_id = $sent OR status_id = $paid) ");    
while ($rowr = mysql_fetch_row($values)) 
{
   for ($j=0;$j<$i;$j++) 
   {   
       $csv_output .= $rowr[$j].", ";       
   }
   $csv_output .= "\n";
}

The table that stores the client information is called 'cms_module_joms_clients' and the name of the client is stored under the column 'company_name'. The client id is being taken from the 'cms_module_joms_invoice_headers' table and the row is called 'client_id'.

I thought that would be right but I guess not.

EDIT

The 'cms_module_joms_clients' table structure looks like this,

And the 'cms_module_joms_invoice_headers' table structure looks like this,

Answer 1

Got it. Not the best code in the world, I will work on that! But it does work with the following,

$values = mysql_query('SELECT '.$rows.'
        FROM '.cms_db_prefix().'module_joms_invoice_headers ih
        LEFT JOIN '.cms_db_prefix().'module_joms_clients cl
        ON ih.client_id = cl.client_id
        WHERE invoicedate >= '.$start.' AND invoicedate <= '.$end.' AND (ih.status_id = '.$draft.' OR ih.status_id = '.$sent.' OR ih.status_id = '.$paid.') AND ih.client_id != 0
        ');

Thanks for everyone's help

Answer 2

What you're looking for is a JOIN. I'm missing some information on your table structures but What you'll need to do is include "COMPANY_TABLE.COMPANY_NAME AS company_name" in the $rows variable and change your select to something like this:

$values = mysql_query(
   "SELECT
      $rows
    FROM ".$table."
    JOIN 
      module_joms_clients
    ON
      CLIENT_TABLE.COMPANY_ID = module_joms_clients.COMAPANY_ID
    WHERE
      invoicedate >= $start AND invoicedate <= $end AND
      (status_id = $draft OR status_id = $sent OR status_id = $paid) AND
      client_id = $clientid"
);

If you include some details on the company and client tables I can update the query.