• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

How do I convert this script to PHP PDO

How do I convert this script to PDO?

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);


$name= "Backorders";

$query6 = $conn->query('SELECT orderid, qty, sku, model, description, days FROM backorders');

$export = mysql_query($select);   

$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)) OR ($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/x-msdownload"); 
header("Content-Disposition: attachment; filename=$name.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data";

Open in new window

  • 3
1 Solution
Ray PaseurCommented:
This is an amazingly common question, so E-E has an article about it.  The article maps the familiar but obsolete MySQL extension to the current MySQLi and PDO extensions.   MySQLi is the easier conversion -- with PDO you must change the actual query strings, and that means a whole new set of tests.  Sorry it's not easier, but nobody has been able to automate this, partly because of the way MySQL was so badly implemented in PHP almost 20 years ago.

As written, I don't think the script posted above can run at all.  It relies on undefined variables.  But it looks like it might be gathering data from a MySQL table and creating a CSV file (these are almost universally associated with Excel).  If that's the case I can show you a general design.  PHP has a lot of built-in functionality to make your work easier on tasks like this.
lawrence_devAuthor Commented:
This script works perfectly, and is PDO based.  However, I cannot get the Excel file to generate,  Please advise how to create excel file.

// Time limit to 0 for exporting big records.
    // mysql hostname
    $hostname = 'localhost';
    // mysql username
    $username = $user;
    // mysql password
    $password = $pass;
    // Database Connection using PDO with try catch method.
    try { $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); }
    // In case of error PDO exception will show error message.
    catch(PDOException $e) { echo $e->getMessage(); }
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment;Filename=Backorders.xls");
    echo "<html>";
    echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
    echo "<body>";
    echo "<table border=1>";
	echo"<tr><th>Order ID</th><th>Order Date</th><th>Qty</th><th>Model</th><th>Description</th><th>Days</th></tr>";
    // We will assign variable here for entry By. you can use your variables here.
    $EntryBy = $_GET[val];
    // Get data using PDO prepare Query.
    $STM2 = $dbh->prepare("SELECT `orderid`, `orderdate`, `qty`, `model`, `description`, `days` FROM backorders");
    // bind paramenters, Named paramenters alaways start with colon(:)
    $STM2->bindParam(':EntryBy', $EntryBy);
    // For Executing prepared statement we will use below function
    // We will fetch records like this and use foreach loop to show multiple Results later in bottom of the page.
    $STMrecords = $STM2->fetchAll();
    // We use foreach loop here to echo records.
    foreach($STMrecords as $r)
    echo "<tr>";
    echo "<td>" .$r[0] ."</td>";
    echo "<td>" .$r[1] ."</td>";
    echo "<td>" .$r[2] ."</td>";
    echo "<td>" .$r[3] ."</td>";
    echo "<td>" .$r[4] ."</td>";
    echo "<td>" .$r[5] ."</td>";
    echo "</tr>";
    echo "</table>";
    echo "</body>";
    echo "</html>";
    // Closing MySQL database connection
    $dbh = null;

Open in new window

lawrence_devAuthor Commented:
Disregard.  Works perfect.  Needed to post this script on a second php document because of headers.
lawrence_devAuthor Commented:
I found the solution and made it work
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now