Title: Repeat this MySQL to CSV multiple times with different SQL queries
Tags: php;mysql
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";
Comment: @MikeW anychance you may be able to give me an example of this? Unsure of the loop.

Comment: Put all your codes in an array and look through it constructing a new query for each code as you go.

Comment: instead of trying to code it from scratch, you can use PHPexcel library. It'll take less time.


Here is another answer: 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-&gt;query("select * from subscribers where list=27 and custom_fields LIKE '%{$code}%'");
if ($result-&gt;rowCount()) {
while ($row = $result-&gt;fetch(PDO75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0TCH_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);

Here is another answer: The easiest way is to insert all your values in the query using OR operator:
SELECT *
FROM subscribers
WHERE list = 27
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.
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' =&gt; 'myvalue1.csv',
'value2' =&gt; 'myvalue2.csv',
'value3' =&gt; 'myvalue3.csv',
...
Then iterate this array:
foreach ($data as $value =&gt; $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);
Comment for this answer: 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..
Comment for this answer: 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.

Here is another answer: "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";

Here is another answer: 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( ) );
Comment for this answer: INTO OUTFILE requires both administrative access to the SQL server, and filesystem-level access to the machine.

Here is another answer: 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"=&gt;"field_a, field_b", "LC"=&gt;"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%'";
// ...

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 5, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:11/13/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<php><mysql>', 'Title': 'Repeat this MySQL to CSV multiple times with different SQL queries', 'ViewCount': 1323, 'id': 26900691, 'user_AboutMe': '<p>Hi, I am Jordan.</p>\n\n<p>I am a front end developer with a lot of questions! ha.</p>\n', 'user_AccountId': 1052217.0, 'user_DisplayName': 'wharfdale', 'user_Reputation': 1138.0, 'user_Views': 253.0, 'user_id': 1055843.0}}