Repeat this MySQL to CSV multiple times with different SQL queries

Question

I have the following PHP which will return the results to my query to me in a CSV format, but the code (two letters) in the LIKE statement as shown at the top needs to change between multiple different codes.

I have about 30 different codes. I need to define all the codes such as:

CV, LC, RCA, JOR etc...

And have the script make a new CSV for each different code and quickly go and process each one, one after another. So I end up with 30 files for example. I may need to do this a few times so manually changing it 30 times is not my top option.

<?php

// database variables
$hostname = "localhost";
$user = "###";
$password = "###";
$database = "###";

$select = "select * from subscribers where list=27 and custom_fields LIKE '%\%CV\%%'";

$con = mysql_connect($hostname, $user, $password);
$db_selected = mysql_select_db($database, $con);

// Check connection
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=list-export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

?>

Put all your codes in an array and look through it constructing a new query for each code as you go. — , Nov 13 '14 at 03:16
@MikeW anychance you may be able to give me an example of this? Unsure of the loop. — wharfdale, Nov 18 '14 at 00:27
instead of trying to code it from scratch, you can use PHPexcel library. It'll take less time. — stormrage, Nov 18 '14 at 02:09

René Hoffmann · Answer 1 · 2014-11-18T15:14:11.823

As Mike W stated, you'd just need an array containing your codes like this one:
$codes = array("CV", "LC", "RCA", "JOR", etc....)

Then you generate your query but not until you checked the connection:

//...
// connection has been checked
foreach($codes as $single_code){
  // do the file-writing-stuff here, using $single_code to generate each query
}
// ...

Why don't you consider the two fields in the query (select FIELD1, FIELD2 from subscribers...)?
If those field names vary by the codes, you can use something like this:

declare the array as
$codes = array("CV"=>"field_a, field_b", "LC"=>"field_c, field_d", ...)
generate the query as follows
foreach($codes as $single_code){ $select = "select $codes[$single_code] from subscribers where list=27 and custom_fields LIKE '%$single_code%'"; // ... }

score 2 · Answer 2 · edited May 23 '17 at 12:15

The easiest way is to insert all your values in the query using OR operator:

SELECT * 
FROM subscribers
WHERE list = 27 
AND (
     custom_fields LIKE '%value_1%'
  OR custom_fields LIKE '%value_2%'
  OR custom_fields LIKE '%value_3%'
  OR custom_fields LIKE '%value_4%'
)

And so on.

Please read why you shouldn't use mysql_* functons in PHP.

Edit

Sorry I did not understood your question this way.

@RenéHoffmann's answer is the way to go, then.

First create an array with all values and associated file:

$data = array(
    'value1' => 'myvalue1.csv',
    'value2' => 'myvalue2.csv',
    'value3' => 'myvalue3.csv',
    ...
);

Then iterate this array:

foreach ($data as $value => $file)
{
    $escapedValue = mysql_real_escape_string($value);
    $select = "select * from subscribers where list=27 and field LIKE '%{$escapedData}%'";
    $response = mysql_query($select);

    $rows = array();
    while ($row = mysql_fetch_assoc($response))
    {
        $rows[] = $row;
    }

    $csv = array2csv($rows); // see https://stackoverflow.com/a/13474770/731138
    file_put_contents($file, $csv);
}

In my case I need it to do every query, not 1 OR the other. I need all 30 csv files. **1 CSV for: LIKE '%value_1%'** **1 CSV for: LIKE '%value_2%'** and so on.. — wharfdale, Nov 18 '14 at 14:09
Jordan, IMHO Alain's code does what you want. Pay attention to the foreach loop, it iterates throw every pair of the reference array. it's supoused to be written something like array( 'CV' => 'list-export-CV.xls', 'LC' => 'list-export-LC.xls', .... ) so you will get different files for different codes. — Carlos Mora, Nov 24 '14 at 10:55

score 1 · Answer 3 · answered Nov 24 '14 at 11:18

Simplest way of exporting data in csv file is to use INTO OUTFILE :

Your Code :

<?php 

$codes = array("CV", "LC", "RCA", "JOR", etc....)


foreach($codes as $single_code){

   $query = "SELECT * 
               INTO OUTFILE '/tmp/document_".$single_code.".csv'
               FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '".'"'."'
               LINES TERMINATED BY '\n'
        FROM subscribers WHERE list=27 AND custom_fields LIKE '%\%".$single_code."\%%'"; 

   $export = mysql_query ( $query ) or die ( "Sql error : " . mysql_error( ) );

}

?>

INTO OUTFILE requires both administrative access to the SQL server, and filesystem-level access to the machine. — Kat R., Nov 24 '14 at 18:59

score 1 · Answer 4 · answered Nov 24 '14 at 19:24

Don't use the 'mysql' extension.
Use the built-in csv-writing functions, as there's no reason to do it by hand.

<?php

$dbh = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');

$data   = fopen('php://temp', 'r+');
$header = false;

$codes = ["CV", "LC", "RCA", "JOR", '...'];
foreach ($codes as $code) {
    $result = $dbh->query("select * from subscribers where list=27 and custom_fields LIKE '%{$code}%'");

    if ($result->rowCount()) {
        while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
            if (!$header) {
                $header   = true;
                fputcsv($data, array_keys($row));
            }

            fputcsv($data, $row);
        }
    } else {
        fputcsv($data, ["(0) Records Found!"]);
    }
}

// Read what we have written.
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=list-export.xls");
header("Pragma: no-cache");
header("Expires: 0");
rewind($data);
echo stream_get_contents($data);

score 0 · Answer 5 · answered Nov 24 '14 at 21:23

"Put all your codes in an array and look through it constructing a new query for each code as you go" say Hobo Sapiens... let's do it!!!

<?php

// database variables
$hostname = "localhost";
$user = "###";
$password = "###";
$database = "###";
$codes = array("CV", "LC", "RCA", "JOR");

$con = mysql_connect($hostname, $user, $password);
$db_selected = mysql_select_db($database, $con);

// Check connection
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}


for ( $c = 0; $c < $codes; $c++ )
{

$select = "select * from subscribers where list=27 and custom_fields LIKE '%\%" + $c + "\%%'";

// and that's it :)


$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=list-export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
}
?>

Repeat this MySQL to CSV multiple times with different SQL queries

5 Answers5