Print an entire MySQL table

I'd like to ask how to print out an entire MySQL table in php.
I'd like to have the names of the fields on a row on top and each corresponding value below.
Having a script for php ver < 5 that works, but I can't get it to work with mysqli. I enclose the old file.
Please suggest a decent method.
matrikel-excel-old.php
Lennart EricsonAmateurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Consider using phpMyAdmin or SequelPro.  We do not need to write our own scripts for utility functions any more; there is a robust Open Source community that we can tie into.  Dumping the contents of a table is one of those "fields that have already been plowed!"
0
Lennart EricsonAmateurAuthor Commented:
Ray,
Thanks for your reply and, please, excuse my late answer.
The file "matrikel_excel_old.php" I was refering to is a script that sort of fills in a xml file. It is intended to take values from the database and fill it into the xml-file and at the end use the xml-file to create an excel chart in MS Excel. The old file worked fine to fulfil this purpose. However, having read your advice to move to mysqli, I tried to "translate" the old file to mysqli, but I was not very successful.
My original question is not very well formulated. So, I'd better ask this instead: How do I "translate" the old file from using mysql to using mysqli?
0
Ray PaseurCommented:
OK, that's probably a do-able task.  Almost all of the query strings will move forward without any changes.  Obviously I cannot test this because I do not have your database, but this is what I think might do the job for you, or at least get close.  Some error visualization code has been added so if something breaks, we can see .

Warning: Untested Code!
<?php // demo/temp_lericson.php
error_reporting(E_ALL);
include "../../dbconfig/dbconfig.php";
header("content-type: text/xml");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=Matrikel ".date("Y-m-d").".xls ");
header("Content-Transfer-Encoding: binary ");
print "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>\n";
print "<?mso-application progid=\"Excel.Sheet\"?>\n";
?>
<ss:Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<ss:DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Ericson Productions i Täby</Author>
<LastAuthor>Lennart Ericson</LastAuthor>
<Created><?php echo date("Y-m-d"); ?>T<?php echo date("H:i:s"); ?>Z</Created>
<LastSaved><?php echo date("Y-m-d"); ?>T<?php echo date("H:i:s"); ?>Z</LastSaved>
<Company><?php echo $batklubbens_namn; ?></Company>
<Version>11.6360</Version>
</ss:DocumentProperties>
<ss:ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>8535</WindowHeight>
<WindowWidth>12345</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>90</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ss:ExcelWorkbook>
<ss:Styles>
<Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21" ss:Name="Hyperlink">
   <Font ss:Color="#0000FF" ss:Underline="Single"/>
  </Style>
  <Style ss:ID="s22">
   <Font x:Family="Swiss" ss:Bold="1"/>
  </Style>
  <Style ss:ID="s23">
   <Font x:Family="Swiss" ss:Bold="0"/>
  </Style>
  <Style ss:ID="s24">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font x:Family="Swiss" ss:Bold="1"/>
   <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
  </Style>
</ss:Styles>
<ss:Worksheet ss:Name="Matrikel">

<ss:Table

x:FullColumns="1"
x:FullRows="1"
ss:DefaultColumnWidth="65.0"
ss:DefaultRowHeight="15.0">
<ss:Row ss:StyleID="s22">
<?php

// CONNECT AND SELECT
$mysqli = new mysqli($host, $usr, $pwd, $db);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "<p>Kan ej ansluta till databasen " . $db . ".</p>"
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACQUIRE THE COLUMN NAMES
$SQL = " DESCRIBE matrikel ";
$ret = $mysqli->query($SQL);
if (!$ret)
{
    trigger_error( $mysqli->error, E_USER_ERROR );
}
else
{
    $num_rows = $ret->num_rows;
    while ($row = $ret->fetch_array())
    {
        $i=0;
        while ($i < $num_rows)
        {
            $field = $ret->fetch_array();
            $field = $field[0];
            echo "<Cell ss:StyleID=\"s24\"><Data ss:Type=\"String\">$field</ss:Data></Cell>";
            $i++;
        }
    }
}
echo '</ss:Row>' . PHP_EOL;

// ACQUIRE THE DATA SET
$SQL1 = " SELECT * FROM matrikel ";
$ret1 = $mysqli->query($SQL1);
if (!$ret1)
{
    trigger_error( $mysqli->error, E_USER_ERROR );
}
else
{
    while ($row = $ret1->fetch_assoc())
    {
    	echo "<ss:Row ss:StyleID=\"s23\">";
        foreach ($row as $k => $v)
        {
            echo "<ss:Cell ss:StyleID=\"s23\"><ss:Data ss:Type=\"String\">$v</ss:Data></ss:Cell>";
        }
        echo "</ss:Row>";
    }
}

?>
</ss:Table>
<ss:WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ss:Print>
<ValidPrinterInfo/>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</ss:Print>
<ss:Selected/>
<ss:Panes>
<ss:Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</ss:Pane>
</ss:Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</ss:WorksheetOptions>
</ss:Worksheet>
</ss:Workbook>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lennart EricsonAmateurAuthor Commented:
Ray,
Thanks! Works like a charm!
I'll analyse your code and learn from it (as usual when you help me out!)
Regards,
lericson
0
Ray PaseurCommented:
Woo-hoo!  I must have gotten lucky -- I never get code to work right the first time.

Thanks for using E-E, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

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.