Convert a mysql for loop to mysqli for loop

Question

I found a piece of code on the internet that does exactly what I was looking for. The code is about downloading the mysql data into excel sheet format using PHP mysql.

The problem is that I need to convert mysql to mysqli functions and it doesn't seems to work. The for loop needs to be completely changed. Any suggestions?

$conn = mysqli_connect("localhost","root","","dvarsam2"); //server,username,password,db
mysqli_query($conn,'SET NAMES utf8');

$setCounter = 0;

$setExcelName = "download_excal_file";

$setSql = "SELECT * FROM wp_applications";

$setRec = mysqli_query($conn,$setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
    $setMainHeader .= mysql_field_name($setRec, $i)."\t";
}

while($rec = mysql_fetch_row($setRec))  {
  $rowLine = '';
  foreach($rec as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }  else  {
//It escape all the special charactor, quotes from the data.
      $value = strip_tags(str_replace('"', '""', $value));
      $value = '"' . $value . '"' . "\t";
    }
    $rowLine .= $value;
  }
  $setData .= trim($rowLine)."\n";
}
  $setData = str_replace("\r", "", $setData);

if ($setData == "") {
  $setData = "\nno matching records found\n";
}

$setCounter = mysql_num_fields($setRec);



//This Header is used to make data download instead of display the data
 header("Content-type: application/octet-stream");

header("Content-Disposition: attachment;      filename=".$setExcelName."_Reoprt.xls");

header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."\n".$setData."\n";

See [here](http://php.net/manual/en/book.mysqli.php) for `mysqli` commands — TomNash, Sep 07 '15 at 18:34

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

You can also just fix your SELECT statement to format output in Excel format:

SELECT * FROM mytable
INTO OUTFILE '/mytable.csv'
FIELDS ESCAPED BY '""'
TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

See here for more. The code you supplied is from the accepted answer, but I prefer this solution.

edited May 23 '17 at 11:58

Community

1
1

answered Sep 07 '15 at 18:37

TomNash

3,147
2
21
57

I prefer it too. Probably runs 5 times faster – Drew Sep 07 '15 at 18:41
Note that this usually doesn't work on shared hosting environments because the `INTO OUTFILE` option is often disabled there. – vinz Sep 07 '15 at 18:56

score 1 · Accepted Answer · answered Sep 07 '15 at 18:52

For MySQLi:

<?php

$conn = mysqli_connect("localhost","root","","dvarsam2"); //server,username,password,db
mysqli_query($conn,'SET NAMES utf8');

$setExcelName = "download_excal_file";

//This Header is used to make data download instead of display the data
header("Content-type: application/octet-stream");

header("Content-Disposition: attachment;      filename=".$setExcelName."_Reoprt.xls");

header("Pragma: no-cache");
header("Expires: 0");

$result = mysqli_query( $conn, "SELECT * FROM wp_applications" );

$i = 0;

while( $row = $result->fetch_assoc() )
{
  if( $i == 0 )
  {
    // Print field names
    foreach( $row as $key => $value )
    {
      echo $key."\t";
    }

    echo "\n";
  }

  // Print data
  foreach( $row as $key => $value )
  {
    $value = strip_tags(str_replace('"', '""', trim($value)));
    echo '"' . str_replace("\r", "", $value ) . '"' . "\t";
  }

  echo "\n";

  $i++;
}

if( $result->num_rows == 0 ) echo "no matching records found\n";

It works fine. The only problem is that characters in excel are shown in a weird encoding. — Kiki, Sep 07 '15 at 19:17

Convert a mysql for loop to mysqli for loop

2 Answers2