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 for mysqli commands

TomNash
– TomNash

2015-09-07 18:34:04 +00:00
Commented Sep 7, 2015 at 18:34 — TomNash
– TomNash, Commented Sep 7, 2015 at 18:34
stackoverflow.com/a/18951537/1816093

Drew
– Drew

2015-09-07 18:38:18 +00:00
Commented Sep 7, 2015 at 18:38 — Drew
– Drew, Commented Sep 7, 2015 at 18:38

vinz · Accepted Answer · 2015-09-07 18:52:39Z

1

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";

answered Sep 7, 2015 at 18:52

vinz

5663 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kiki Over a year ago

It works fine. The only problem is that characters in excel are shown in a weird encoding.

Community · Accepted Answer · 2017-05-23 11:58:04Z

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, 2017 at 11:58

CommunityBot

11 silver badge

answered Sep 7, 2015 at 18:37

TomNash

3,3272 gold badges28 silver badges68 bronze badges

2 Comments

Drew Over a year ago

I prefer it too. Probably runs 5 times faster

vinz Over a year ago

Note that this usually doesn't work on shared hosting environments because the INTO OUTFILE option is often disabled there.

Collectives™ on Stack Overflow

Convert a mysql for loop to mysqli for loop

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related