PHP和Access数据库之间的连接 [英] Connection between PHP and Access DB

问题描述

我工作了几个星期，试图解决这个问题。
我需要管理PHP和Microsoft Access数据库.mdb之间的连接。

我的目标是创建一个网页，包含一个表，数据库表。

示例：

数据库名称： DinamicoWeb

表名称：订单

字段名称： ID Ord，Ord日期，姓名，姓氏，价格，总计

这是我设法写入的代码（放在所有列中）。

config.php

 <？php 
 define '，./ DinamicoWeb.mdb）; //数据库名称
 define（'DBTBL'，Ordini）; //表名
 define（'PKNAME'，Id Ord）; // Primary Key 
 define（'PKCOL'，0）; // Posotion Primary Key 
 define（'LINKPK'，true）; // PK link for edit / delete 
？> 
  

test.php

 <？php 
 require_once（config.php）; 
 
 $ cn = new COM（ADODB.Connection）; 
 $ cnStr =DRIVER = {Microsoft Access Driver（* .mdb）}; DBQ =。 
 realpath（DBNAME）。;; 
 $ cn-> open（$ cnStr）; 
 $ rs = $ cn-> execute（SELECT * FROM.DBTBL）; 
 $ numFields = $ rs-> Fields-& gt; count; 
 
 //打印HTML 
 echo'<！DOCTYPE html PUBLIC -  // W3C // DTD XHTML 1.0 Strict // EN
http： w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">' 
 echo'< html xmlns =http://www.w3.org/1999/xhtml>'; 
 echo'< head>'; 
 echo'< meta http-equiv =Content-Type
 content =text / html; charset = utf-8/> 
 echo'< title> Gestione degli'.DBTBL。'< / title>'; 
 echo'< link href =styles.css =stylesheettype =text / css/>'; 
 echo'< / head>< body>'; 
 echo'< h1> GESTIONE'.DBTBL。'< / h1>'; 
 // Elenca records ----- 
 echo（< table class ='datatable'summary ='Prova dati con MS Access'>）; 
 echo（< caption> Tabella.DBTBL。< / caption> \\\
）; 
 echo（< thead>< tr> \\\
）; 
 for（$ i = 0; $ i< $ numFields; $ i ++）{
 echo（< th scope ='col'>） 
 echo $ rs-> Fields（$ i） - > name; 
 echo（< / th> \\\
）; 
} 
 echo（< / tr>< / thead> \\\
）; 
 echo（< tbody>）; 
 
 $ alt = false; 
 while（！$ rs-> EOF）
 {
 echo（< tr>）; 
 for（$ i = 0; $ i <$ numFields; $ i ++）{
 $ altClass = $ alt？ class ='alt'：; 
 if（LINKPK&& $ i == PKCOL）{
 echo< td。$ altClass。>< a href ='？id =。$ rs-> ; Fields（$ i） - > value 
。'>。$ rs-> Fields（$ i） - > value。< / a>< / td> \\\
 ; 
} 
 else {
 echo< td。$ altClass。>。$ rs-> Fields（$ i） - > value。< / td> ; \\\
; 
} 
} 
 echo（< / tr> \\\
）; 
 $ rs-> MoveNext（）; 
 $ alt =！$ alt; 
} 
 echo（< / tbody>）; 
 echo（< / table> \\\
）; 
 echo（< p> [< a href ='？ins = 1'> Inserimento nuovo record< / a>]< / p& 
 //修改记录----- 
 if（！empty（$ _ GET ['id']））{
 $ id = intval（$ _ GET ['id']）; 
 $ rs = $ cn-> execute（SELECT * FROM.DBTBL。WHERE.PKNAME。=。$ id）; 
 echo（< form action ='modify.php'method ='post'>）; 
 echo（< fieldset>）; 
 echo（< legend> Modifica record< / legend>）; 
 for（$ i = 0; $ i< $ numFields; $ i ++）{
 if（LINKPK& $ amp;& $ i == PKCOL）{
 echo（< label for ='。$ rs-> Fields（$ i） - > name。'>
。$ rs-> Fields（$ i） - > name。< / label> ）; 
 echo（< input type ='text'readonly ='readonly'name ='
。$ rs-> Fields（$ i） - > name。'value = \ 
。$ rs-> Fields（$ i） - > value。\/>< br /> \\\
 
} 
 else {
 echo（< label for ='。$ rs-> Fields（$ i） - > name。'>
 。$ rs-> Fields（$ i） - > name。< / label>）; 
 echo（< input type ='text'name ='。$ rs-> Fields（$ i） - > name。'value = \
。$ rs - > Fields（$ i） - > value。\/>< br /> \\\
）; 
} 
} 
 echo（< button type ='submit'name ='azione'value ='modifica'> Modifica< / button& 
 echo（< button class ='affiancato'type ='submit'
 name ='azione'value ='cancella'> Cancella< / button>）; 
 echo（< / fieldset>< / form>）; 
} 
 // Inserimento record ----- 
 elseif（！empty（$ _ GET ['ins']））{
 echo（< form action =' modify.php'method ='post'>）; 
 echo（< fieldset>）; 
 echo（< legend> Inserimento record< / legend>）; 
 for（$ i = 0; $ i <$ numFields; $ i ++）{
 if（$ i！= PKCOL）{
 echo（< label for =' rs-> Fields（$ i） - > name。'>
。$ rs-> Fields（$ i） - > name。< / label& 
 echo（< input type ='text'name ='。$ rs-> Fields（$ i） - > name。'/>< br / ）; 
} 
} 
 echo（< button type ='submit'name ='azione'value ='inserisci'> Inserisci< / button& 
 echo（< br />）; 
 echo（< / fieldset>< / form>）; 
} 
 echo'< / body>< / html>'; 
 $ rs-> Close（）; 
 $ cn-> Close（）; 
？> 
  

PS：很棒的是将.php文件转换为.html文件， p>

PSS：下一步是使用combobox和复选框过滤

如果可能，手动添加列的头，而不使用While，以便使带有jQuery和footable的响应表

例如：

 < table class =footabledata-filter =＃filter> 
< thead> 
< tr> 
< th data-sort-initial =descendingdata-class =expand> 
 [Id Ord] 
< / th> 
< th data-sort-ignore =true> 
 [Name] 
< / th> 
< th data-hide =phone，tablet> 
 [Surname] 
< / th> 
< th data-hide =phone，tabletdata-type =numeric> 
 [价格] 
< / th> 
< th data-hide =phonedata-type =numeric> 
 [Total] 
< / th> 
< / tr> 
< / thead> 
  

解决方案

更改

  $ rs = $ cn-> execute（SELECT * FROM.DBTBL）; 
  

只能请求六列

  $ rs = $ cn-> execute（SELECT [Id Ord]，[Ord Date]，[Name]，[Surname]，[Price]，[Total] FROM.DBTBL ）; 
  

I'm working for weeks to try to solve this problem. I need to manage a connection between PHP and Microsoft Access Database .mdb.

My goal is to create a web page that contain a table with the fields of a database table. But my table has about 30 columns, I would like to insert only 6 of these.

Example:
Database name: DinamicoWeb
Table name: Orders
Fields name: Id Ord, Ord Date, Name, Surname, Price, Total

This is the code that I managed to write(put inside ALL columns).

config.php

<?php
define ('DBNAME',"./DinamicoWeb.mdb"); // Database name
define ('DBTBL',"Ordini"); // Table name
define ('PKNAME',"Id Ord"); // Primary Key
define ('PKCOL',0); // Posotion Primary Key
define ('LINKPK',true); // PK link for edit/delete
?>

test.php

<?php
require_once("config.php");

$cn = new COM("ADODB.Connection");
$cnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=".
            realpath(DBNAME).";";
$cn->open($cnStr);
$rs = $cn->execute("SELECT * FROM ".DBTBL);
$numFields = $rs->Fields->count;

// Print HTML
echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">';
echo '<html xmlns="http://www.w3.org/1999/xhtml">';
echo '<head>';
echo '<meta http-equiv="Content-Type" 
     content="text/html; charset=utf-8" />';
echo '<title>Gestione degli '.DBTBL.'</title>';
echo '<link href="styles.css" rel="stylesheet" type="text/css" />';
echo '</head><body>';
echo '<h1>GESTIONE '.DBTBL.'</h1>';
// Elenca records -----
echo ("<table class='datatable' summary='Prova dati con MS Access'>");
echo("<caption>Tabella ".DBTBL."</caption>\n");
echo("<thead><tr>\n");
for ($i=0;$i<$numFields;$i++){
    echo("<th scope='col'>");
    echo $rs->Fields($i)->name;
    echo("</th>\n");
}
echo("</tr></thead>\n");
echo("<tbody>");

$alt = false;
while (!$rs->EOF)
{
    echo("<tr>");
    for ($i=0;$i<$numFields;$i++){
      $altClass = $alt ? " class='alt'" : "";
      if (LINKPK && $i==PKCOL){
        echo "<td".$altClass."><a href='?id=".$rs->Fields($i)->value
              ."'>".$rs->Fields($i)->value."</a></td>\n";
      }
      else{
        echo "<td".$altClass.">".$rs->Fields($i)->value."</td>\n";
      }
    }
    echo("</tr>\n");    
    $rs->MoveNext();
    $alt = !$alt;
}
echo("</tbody>");
echo("</table>\n");
echo ("<p>[ <a href='?ins=1'>Inserimento nuovo record</a> ]</p>");
// Modifica record -----
if (!empty($_GET['id'])){
  $id = intval($_GET['id']);
  $rs = $cn->execute("SELECT * FROM ".DBTBL." WHERE ".PKNAME."=".$id);
  echo ("<form action='modify.php' method='post'>");
  echo ("<fieldset>");
  echo ("<legend>Modifica record</legend>");
  for ($i=0;$i<$numFields;$i++){
    if (LINKPK && $i==PKCOL){
      echo ("<label for='".$rs->Fields($i)->name."'>"
             .$rs->Fields($i)->name."</label>");
      echo ("<input type='text' readonly='readonly' name='"
             .$rs->Fields($i)->name."' value=\""
             .$rs->Fields($i)->value."\" /><br />\n");      
    }
    else {
      echo ("<label for='".$rs->Fields($i)->name."'>"
             .$rs->Fields($i)->name."</label>");
      echo ("<input type='text' name='".$rs->Fields($i)->name."' value=\""
             .$rs->Fields($i)->value."\" /><br />\n");
    }
  }
  echo ("<button type='submit' name='azione' value='modifica'>Modifica</button>");
  echo ("<button class='affiancato' type='submit' 
        name='azione' value='cancella'>Cancella</button>");
  echo ("</fieldset></form>");
}
// Inserimento record -----
elseif (!empty($_GET['ins'])){
  echo ("<form action='modify.php' method='post'>");
  echo ("<fieldset>");
  echo ("<legend>Inserimento record</legend>");
  for ($i=0;$i<$numFields;$i++){
    if ($i!=PKCOL){
      echo ("<label for='".$rs->Fields($i)->name."'>"
             .$rs->Fields($i)->name."</label>");
      echo ("<input type='text' name='".$rs->Fields($i)->name."' /><br />\n");
    }
  }
  echo ("<button type='submit' name='azione' value='inserisci'>Inserisci</button>");
  echo ("<br />");
  echo ("</fieldset></form>");
}
echo '</body></html>';
$rs->Close();
$cn->Close();
?>

PS: It's great to trasform the .php file to .html file with dynamic php inside.

PSS: And the next step would be to make also filters with combobox and checkbox

If is possible, I would add headers of columns manually and not with a While, so as to make the responsive table with jQuery and footable

For example:

<table class="footable" data-filter="#filter">
  <thead>
    <tr>
      <th data-sort-initial="descending" data-class="expand">
        [Id Ord]
      </th>
      <th data-sort-ignore="true">
        [Name]
      </th>
      <th data-hide="phone,tablet">
        [Surname]
      </th>
      <th data-hide="phone,tablet" data-type="numeric">
        [Price]
      </th>
      <th data-hide="phone" data-type="numeric">
        [Total]
      </th>
    </tr>
  </thead>

解决方案

Change

$rs = $cn->execute("SELECT * FROM ".DBTBL);

to only request the six columns

$rs = $cn->execute("SELECT [Id Ord], [Ord Date], [Name], [Surname], [Price], [Total] FROM ".DBTBL);

这篇关于PHP和Access数据库之间的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！