IGGt

I have a script that runs a query against a MySQL database, then, if it returns a resultset, it takes the 'freindly name from an array containg the database connections, then adds the data for the resultset. Unfortunately at the minute, it doesn't quite work the way that I want. So far I have: //get list of servers to query, and choose them one at a time for($a = 0; $a <sizeof($slaveRes_array); $a++) { $con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']); mysql_select_db($dbs, $con); //get list of MySQL Queries, and run them against the current server, one at a time for($b = 0; $b <sizeof($query_array); $b++) { $SlaveState = mysql_query($query_array[$b]['query1'], $con); // 1st Query // If there is a resultset, get data and put into array while($row = mysql_fetch_assoc($SlaveState)) { for($c = 0; $c <mysql_num_rows($SlaveState); $c++) { $slave_array[]['name'] = $slaveRes_array[$a]['database']; for($d = 0; $d <mysql_num_fields($SlaveState); $d++) { $slave_array[][mysql_field_name($SlaveState,$d)] = mysql_result($SlaveState,$c,mysql_field_name($SlaveState,$d)); }} } } // Run Query2...Query3....etc. } The problem is that at the minute it puts each field into a separate part of the array e.g. Array ( [0] => Array ( [name] => MySQL02_5083 ) [1] => Array ( [slave_IO_State] => Waiting for master to send event ) [2] => Array ( [Master_Host] => localhost ) [3] => Array ( [Master_User] => root ) Whereas what I am trying to achieve is more like: Array ( [0] => Array ( [name] => MySQL02 ) [slave_IO_State] => Waiting for master to send event ) [Master_Host] => localhost ) [Master_User] => root )... [1] => Array ( [name] => MySQL03 etc. etc. But I can't see how to achieve this?

What I have so far is: //make the relevant connection for($a = 0; $a <sizeof($slaveRes_array); $a++) { $con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']); mysql_select_db($dbs, $con); for($b = 0; $b <sizeof($query_array); $b++) { $slaveState = mysql_query($query_array[$b]['query1'], $con); and then two options: while(($slave_array2[] = mysql_fetch_assoc($slaveState)) || array_pop($slave_array2)); and: while($row = mysql_fetch_assoc($slaveState)) { if (($row['Slave_IO_Running'] == "No") || ($row['Slave_IO_Running'] == "Yes")) { //it should always report Yes / No / or nothing. Nothing means there is no resultset, and so this database should be ignored. $slave_array[]['name'] = $slaveRes_array[$a]['database'];} else { print "nothing";}; } Both of these work individually, but they don't work together. Whichever one I put on top runs, and the second one basically gets ignored.

I'm sure there must be a way to do whatI am after, as it seems like such an obvious thing to do, but I can't figure it out. 1. I have an array which contains a list of database's and their connections $slaveRes_array[0] = array('server' => 'localhost:3306', 'user' => $u, 'password' => $p, 'database' => 'MySQL01_5083', 'sendmail' => '0', 'repeat' => '0', 'dbID' => '1' ); and so on 10 times I then have a list of MySQL Queries: (at the moment there is only one, but there will be more later on). $query_array[] = array( 'query1' => "SHOW SLAVE STATUS"); The idea is that I take the first database from $slaveRes_array, connect to it, and run each query from the $query_array in turn against that database, and ultimately pass that resultset into an array that I can refer back to later on. Within $slaveRes_array I have a DB name (e.g. MySQL01_5083), I want this to be the first entry in my resultset array, so I can reference this. However, not all database's will return a result. So what I want to end up with is something like: [0] => Array ( [name] => MySQL02_5083 ) ( [slave_IO_State] => Waiting for master to send event)... (in this case the first server (MySQL01) didn't return a result, so the first item in the array is MySQL02. I have found lots of ways to achieve bits of this, but none that work together. I can get a list of database names that returned a result, but can't get the data to return to the array (or any array). I can get the data into an array, but not the database names. Surely this must be something straight forward that people do all the time, right?

Cheers, I've changed it to use single quotes now, and that makes it easier to see what's happening. The 4th \ seems to be required, to get the filename correct. If I use 3 \ then the filename is '{Report_xxx.csv}', whereas with 4 \ it is correct 'Report_xxx.csv'. But only on the second query: $Query .= "INTO OUTFILE 'c:\\\wamp\\\www\\\ReportFiles\\\{$Filename}'"; the others are all fine. I guess this is related to the $Reportfiles variable which is set to: $ReportFiles = "c:\\\wamp\\\www\\\ReportFiles\\\\"; because again, anything less than 4 \ at the end and it doesn't work.

OK, I got there. The lines I needed are: $ReportFiles = "c:\\\wamp\\\www\\\ReportFiles\\\\"; $Filename = str_replace('.php', '', basename("QuadronReport")) . '_' . date("Ymdhis") . '.csv'; $Newfile = $ReportFiles.$Filename; //$Query .= "INTO OUTFILE \"$Newfile\""; //$Query .= "INTO OUTFILE \"c:\\\wamp\\\www\\\ReportFiles\\\\$Filename\""; //$Query .= "INTO OUTFILE \"$ReportFiles$Filename\""; //$Query .= "INTO OUTFILE \"c:\\\wamp\\\www\\\ReportFiles\\\Report_1.csv\""; Which then echoes the following results: 1:"c:\\wamp\\www\\ReportFiles\\Report_1.csv" 2:"c:\\wamp\\www\\ReportFiles\\Report_20091217121530.csv" 3:"c:\\wamp\\www\\ReportFiles\\Report_20091217121530.csv" 4:"c:\\wamp\\www\\ReportFiles\\Report_20091217121530.csv" which all work fine when put into MySQL. phew, so many \ !!!

but if I echo out the results I get: //$Query .= "INTO OUTFILE \"c:\\\ReportFiles\\\Report_1.csv\""; //$Query .= "INTO OUTFILE \"c:\\\ReportFiles\\\ $Filename\""; //$Query .= "INTO OUTFILE \"$ReportFiles$Filename\""; //$Query .= "INTO OUTFILE \"$Newfile\" "; 1:"c:\\ReportFiles\\Report_1.csv" 2:"c:\\ReportFiles\\ Report_20091217111456.csv" 3:"c:\ReportFiles\Report_20091217111456.csv" 4:"c:\ReportFiles\Report_20091217111456.csv" so the resulting address is the same for all queries, therefoere presumably it should work?

I tried the four queries ($Query) below: The first one was ok, as you would expect. The second one worked OK as well The third one though didn't give an error message, but also failed to work. I echoed out the query, and that is fine, but nothing actually happened. The fourth one was the same as the third one. //variables $ReportFiles = "c:\\ReportFiles\\""; $Filename = str_replace('.php', '', basename("Report")) . '_' . date("Ymdhis") . '.csv'; $Newfile = $ReportFiles.$Filename; //$Query .= "INTO OUTFILE \"c:\\\wamp\\\www\\\ReportFiles\\\Report_1.csv\""; //$Query .= "INTO OUTFILE \"c:\\\wamp\\\www\\\ReportFiles\\\ $Filename\""; //$Query .= "INTO OUTFILE \"$ReportFiles$Filename\""; //$Query .= "INTO OUTFILE \"$Newfile\" ";

OK, I found two ways to do this. 1. use a MySQL SELECT INTO OUTFILE query 2. Use $fopen, $fwrite, $fclose

Cheers, but how can I get the dynamic filename into the query. I tried: $ReportFiles = "c:\\ReportFiles\\"; $Filename = str_replace('.php', '', basename("Report")) . '_' . date("Ymdhis") . '.csv'; $Newfile = $ReportFiles.$Filename; //MySQL Query $Query = "SELECT ........ [color=red]$Query .= "Into outfile ".$Newfile. " lines terminated by etc...."[/color] $Query .= "FROM ...........;"; but I just got a parse error

I see, I was missing some "\" (e.g. into outfile c:\\\ReportFiles\\\report.csv) cheers, (and for anybody that may be interested - the answer to the original problem was that it needed a { after $String = implode(",",$row); )

I Looked at that, but it didn't work: //Set variables $uName = "root"; $pWord = "password"; $host = "localhost"; $Query = "SELECT ... INTO OUTFILE \"c:\ReportFiles\Report_1.csv\" FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM ..;"; //Run MySQL Query $connection = mysql_connect($host, $uName, $pWord) ; mysql_select_db("dbName", $connection) ; $result = mysql_query ($Query, $connection);

I am looking to write a MySQL query to a csv file. At the minute I: check for the original file, and delete it; create a new (blank) file; run the MySQL query and pass it into an array; write it to the new file; But it is missing something, as it only writes the first line from the query. my code so far is: <?php //Delete Original File ... //Set variables $uName = "root"; $pWord = "password"; $host = "localhost"; $Query = "[i]...MySQL query...[/i]"; $Filename = str_replace('.php', '', basename("Report")) . '_' . date("Ymdhis") . '.csv'; $ReportFiles = "c:\ReportFiles\\"; $Newfile = $ReportFiles.$Filename; $cr = "\n"; //Create a new (empty) file echo exec("<nul (set/p z=) > \"$Newfile\""); //Run MySQL Query $connection = mysql_connect($host, $uName, $pWord) ; mysql_select_db("dbName", $connection) ; $result = mysql_query ($Query, $connection); while ($row = mysql_fetch_array($result, MYSQL_NUM)) $String = implode(",",$row); { //Open file and save contents of query to that file $fp = fopen($Newfile,"a"); if($fp){ fwrite($fp,$String.$cr); fclose($fp); echo "File saved successfully"; } else { echo "Error saving file!"; } } ?> What am I missing?

Thats fine, essentially everything is going to run on my local machine for administrative purposes, so all files will be saved to my local machine.

I have a set of scripts that are all designed to run a MySQL query. They then pass that query ($Query), along with the new filename ($Filename) to another php script which is designed to convert that to a csv file. At the minute it works fine, except that it opens a dialogue box asking you to open or save the file. Ideally I need it to automatically save the file to a set location. Can I modify my existing code to do this, or is there a better way of doing this? my code is: <?php function exportMysqlToCsv($Query,$filename) { $csv_terminated = "\n"; $csv_separator = ","; $csv_enclosed = '"'; $csv_escaped = "\\"; $sql_query = $Query; // Gets the data from the database $result = mysql_query($sql_query); $fields_cnt = mysql_num_fields($result); $schema_insert = ''; for ($i = 0; $i < $fields_cnt; $i++) { $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(mysql_field_name($result, $i))) . $csv_enclosed; $schema_insert .= $l; $schema_insert .= $csv_separator; } // end for $out = trim(substr($schema_insert, 0, -1)); $out .= $csv_terminated; // Format the data while ($row = mysql_fetch_array($result)) { $schema_insert = ''; for ($j = 0; $j < $fields_cnt; $j++) { if ($row[$j] == '0' || $row[$j] != '') { if ($csv_enclosed == '') { $schema_insert .= $row[$j]; } else { $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed; } } else { $schema_insert .= ''; } if ($j < $fields_cnt - 1) { $schema_insert .= $csv_separator; } } // end for $out .= $schema_insert; $out .= $csv_terminated; } // end while header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Length: " . strlen($out)); // Output to browser with appropriate mime type, you choose //header("Content-type: text/x-csv"); //header("Content-type: text/csv"); header("Content-type: application/csv"); header("Content-Disposition: attachment; filename=$filename"); //echo "Download the file <a href=reportFiles/$filename>here</a>"; echo $out; exit; } ?>