How to Convert MySQL query to CSV in PHP


function setExcelContentType() {
if (headers_sent())
return false;

header('Content-type: application/vnd.ms-excel');
return true;
}

function setDownloadAsHeader($filename) {
if (headers_sent())
return false;

header('Content-disposition: attachment; filename=' . $filename);
return true;
}

function csvFromResult($stream, $result, $showColumnHeaders = true) {
if ($showColumnHeaders) {
$columnHeaders = array();
$nfields = mysql_num_fields($result);
for ($i = 0; $i < $nfields; $i++) { $field = mysql_fetch_field($result, $i); $columnHeaders[] = $field->name;
}
fputcsv($stream, $columnHeaders);
}

$nrows = 0;
while ($row = mysql_fetch_row($result)) {
fputcsv($stream, $row);
$nrows++;
}

return $nrows;
}

function csvFileFromResult($filename, $result, $showColumnHeaders = true) {
$fp = fopen($filename, 'w');
$rc = csvFromResult($fp, $result, $showColumnHeaders);
fclose($fp);
return $rc;
}

function csvToExcelDownloadFromResult($result, $showColumnHeaders = true, $asFilename = 'data.csv') {
setExcelContentType();
setDownloadAsHeader($asFilename);
return csvFileFromResult('php://output', $result, $showColumnHeaders);
}

$sql = "SELECT filed1, field2 FROM table1";

$result = mysql_query($sql);
csvToExcelDownloadFromResult($result);

How to remove duplicate rows in MySQL

A really easy way to do this is to add a UNIQUE index on the one or more columns. When you write the ALTER statement, include the IGNORE keyword. Like so:

ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company );

This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this.

If you do not want the field to be unique, drop the unique key once done.

How to switch on PHP magic quotes gpc and magic quotes runtime on Hostgator reseller hosting

There are two steps involved in it.

First, under public_html folder, in .htaccess file write the following code.

<IfModule mod_suphp.c>
suPHP_ConfigPath /home/username
<Files php.ini>
order allow,deny
deny from all
</Files>
</IfModule>

*Replace username with your hosting account’s username (NOT the main reseller username).

Then under /home/username folder, create a php.ini file and write the below mentioned code in it.

magic_quotes_gpc = On
magic_quotes_runtime = On

Please make sure there are no empty spaces at the end of .htaccess and php.ini files.

How to prevent ENTER keypress to submit a web form

Place the following code in head.

<script type=”text/javascript”>
function searchItem(e){
e = e? e : window.event;
var k = e.keyCode? e.keyCode : e.which? e.which : null;
if (k == 13){

if (e.preventDefault)
e.preventDefault();
//Your action here
alert(‘here..’);
return false;

}

return true;
};
</script>

Call in form like this.

<form id=”form1″ name=”form1″ method=”post” action=”">
<input type=”text” name=”textfield” onkeypress=”return searchItem(event)” />
<input type=”submit” name=”Submit” value=”Submit” />
</form>

download