Repeat this MySQL in CSV several times with different SQL queries

Question

I have the following PHP that will return the results in my query to me in CSV format, but the code (two letters) in the LIKE instruction, as shown above, should change between several different codes.

I have about 30 different codes. I need to define all codes, for example:

CV, LC, RCA, JOR, etc.

And let the script create a new CSV for each other code and quickly go through and process each one one by one. So in the end I have 30 files. I may need to do this several 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"; ?> 

Answer 1

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

Then you create your request, but only after you have verified 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 two fields in the request ( select FIELD1, FIELD2 from subscribers... )?
If these field names change by codes, you can use something like this:

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

Answer 2

The easiest way is to insert all your values ​​into the query using the 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%' ) 

Etc.

Please read why you should not use mysql_ * functons in PHP .

Edit

Sorry, I did not understand your question this way.

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

First create an array with all the values ​​and the file associated with it:

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

Then repeat 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); } 

Answer 3

The easiest way to export data to a 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( ) ); } ?> 

Answer 4

• Do not use the mysql extension.

• Use the built-in csv burn functions, as there is no reason to do this manually.

---

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

Answer 5

"Put all of your codes in an array and view it, building a new query for each code when you go," says Hobo Sapiens ... let it 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 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"; } ?>