SQLite with PHP

by Nikolai Shokhirev

Introduction

This is a practical guide to creation and management of SQLite database on your website. We are going to create a web-based database of publications.

You need the following

A personal web page at some server (usually at Linux computer).
PHP5 or PHP4 with SQLite installed on the server
File transfer and Shell client programs, assuming that you work from your PC (whatever is recommended at your computing center)
File editor.

The latter can be any text editor. For windows I recommend to download DevPHP frpm http://devphp.sourceforge.net/ and install it. Versions 2 (stable) and 3 (alpha) are available.

References

PHP 101, http://www.zend.com/php/beginners/
PHP in a Nutshell, http://www.hudzilla.org/phpbook/index.php
SQLite homepage. http://www.sqlite.org/
SQL As Understood By SQLite, http://www.sqlite.org/lang.html
SQLite My Fire! http://www.zend.com/php/beginners/php101-9.php

Credits

Special thanks to Lucy M. Carruthers, principal research specialist at CCIT of the University of Arizona for valuable consultations.

Preliminary checks

Suppose you just got a website, say www.u.arizona.edu/~bob/. If someone types http://www.u.arizona.edu/~bob/ in a browser and presses Enter, the server at u.arizona.edu tries to find in Bob's directory index.html, index.htm, default.htm ... . The list of files depends on a server configuration.

Suppose you already have your index.html:

<html>
  <head>
    <title>The Home Page of Bob Smith</title>
  </head>
  <BODY>
    <h1>Welcome to my homepage!</h1>
    <p>My other stuff ...</p>
  </body>
</html>

If not, you can create one and upload it using the program recommended at your computing center (e.g. SSH Secure File Transfer Client).

We also assume that PHP is installed on your server. In this case the file index.php is in the list of predefined files. Rename your index.html to index.php and try http://www.u.arizona.edu/~bob/ again. If it does not work, contact your system administrator.

Normally it should work because any HTML file is a valid PHP file as well. Let us add some actual PHP functionality:

<html>
  <head>
    <title>The Home Page of Bob Smith</title>
  </head>
  <body bgcolor = "#EEEEF0">
    <h1>Welcome to my homepage!</h1>
    <p>My other stuff ...</p>
<?php
  $s= $_SERVER['SCRIPT_FILENAME'];
  echo "SCRIPT_FILENAME = $s <br>";
  echo "DOCUMENT_ROOT = ".$_SERVER['DOCUMENT_ROOT']."<br>";
  echo 'PHP_SELF = '.$_SERVER['PHP_SELF'].'<br>';
//  phpinfo();
?>
  </body>
</html>

If you run http://www.u.arizona.edu/~bob/index.php now, you get something like this:

Welcome to my homepage!

My other stuff ...

SCRIPT_FILENAME = /home/u123/bob/public_html/index.php
DOCUMENT_ROOT = /home/httpd/www.u.arizona.edu
PHP_SELF = /~bob/index.php

It means that the internet address http://www.u.arizona.edu/~bob/index.php corresponds to /home/u123/bob/public_html/index.php in the server file system and /home/u123/bob/public_html/ is Bob's web directory there.

If you uncomment the last statement, you get much more information, in particular, PHP and SQLite versions.

SQLite is included in PHP5. If your web host is stuck with PHP4 (as in case of u.arizona.edu) you must be sure that the database engine is installed.

Adding some security

The database is supposed to be publicly available from your page. However you probably do not want to allow for everyone editing it. We are going to keep our administrative scripts in a password-protected directory, say, /home/u123/bob/public_html/admin/ . We should place there the file called .htaccess - a file without a name and eight-letters extension:

AuthUserFile /home/u123/bob/hidden/.htpasswd
AuthGroupFile /dev/null
AuthName Restricted_Access
AuthType Basic

<Limit GET POST>
require valid-user
</Limit>

The CHMOD of an htaccess file should be 644 or (RW- R-- R--). That setting makes it usable as well as more secure. The file refers to another file .htpasswd. For better security we are going to put it outside the web directory: /home/u123/bob/hidden/.htpasswd

The file .htpasswd contains one line per user - a pair username and password (encrypted):

adminuser:x0ysJcLr78iE2

To create this file, start your shell client and cd to /home/u123/bob/hidden/, then type htpasswd -c .htpasswd user_name (say, adminuser). This command creates the file and adds "adminuser" as the first user. The program will prompt you for a password, then verify by asking again. You will not see the password when entering it:

htpasswd -c .htpasswd adminuser

Adding password for adminuser.

New password:

password

Re-type new password:

password

To add more users in the future, use the same command without the -c switch. To delete users, open the .htpasswd file in a text editor and delete the appropriate line.

Alternatively you can create the above files at your PC and upload them to the appropriate directories. Refer to the following tutorials http://www.soundfeelings.com/free/password.htm and http://www.htmlite.com/HTA001.php .

A database file must be in a directory with a full read/write access (CHMOD set to 777). Therefore the file needs to be kept somewhere it cannot be accessed through the browser by visitors to your site. That means that you need to create it outside your web directory (public_html, in this example). We are going to keep it in /home/u123/bob/refdb/ (one level above).

Database design

Our database will contain only journal references. It will consist of two tables.

Table ref_list

Field	Type	Commet
ridx	INTEGER, NOT NULL, AUTOINCREMENT	Record index
authors	TEXT, NOT NULL
title	TEXT, NOT NULL
volume	VARCHAR(6)	not necessarily integer
year	INTEGER
pages	VARCHAR(20)	e.g. 123456-234567
no	VARCHAR(6)	not necessarily integer
keywords	TEXT
jidx	INTEGER, NOT NULL	foreign key from journals

Table joutnals

Field	Type	Commet
jidx	INTEGER, NOT NULL, AUTOINCREMENT	Record index
jname	TEXT, NOT NULL

In SQLite the primary key is AUTOINCREMENT by default. Also SQLite does not enforce field length, so we can use TEXT instead of VARCHAR(N).

The tables can be created with the following SQL scripts:

create table ref_list (
  ridx INTEGER PRIMARY KEY,
  authors TEXT NOT NULL,
  title TEXT NOT NULL,
  volume TEXT,
  year INTEGER NOT NULL,
  pages TEXT,
  no TEXT,
  keywords TEXT,
  jidx INTEGER NOT NULL);

create table journals (
  jidx INTEGER PRIMARY KEY,
  jname TEXT NOT NULL);

Database management

We need to view the list of references, add new articles and journals, edit and delete existing records. There are a lot of database management tools. In particular, PHP5 comes with SQLite Admin. For a training purpose we are going to make our own simple tools.

Control page

First, make manage_db.html file

<html>
  <head>
    <title>DB Management</title>
  </head>
  <body bgcolor = "#EEEEF0">
    <h2>DB Management</h2>
<ul>
  <li><a href="create_db.php">Create/Info</a></li>
  <li><a href="add_new.php">Add new record</a></li>
  <li><a href="edit_db.php">Edit/delete record</a></li>
  <li><a href="show_all.php">Show All references</a></li>
</ul>
  </body>
</html>

and put it in our secure admin directory.

The page looks as

DB Management

In index.php add a link to this file:

<a href="admin/manage_db.html">DB management</a>

Create/Info script

File create_db.php

 <html>
  <head>
    <title>Create/Info Ref DB</title>
  </head>
  <body bgcolor = "#EEEEF0">
    <h2>Ref DB Info</h2>
<?php
// define constants
$jt = 'journals';
$rt = 'ref_list';
$db = '/home/u123/bob/refdb/ref_db.sqlite';

function sqlite_table_exists($dh, $mytable)
{
  $result = sqlite_query($dh,"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$mytable'");
  // casts into integer
  $count = intval(sqlite_fetch_single($result));
    return $count > 0;
}

function sqlite_table_list($dh)
{
  $result = sqlite_query($dh,"SELECT name FROM sqlite_master WHERE type='table'");
  if (sqlite_num_rows($result) > 0)
  {
	$s = 'Tables:<br />';
    while (sqlite_has_more($result)) {
      $row = sqlite_fetch_single($result);
      $s .= $row.'<br />';
    }
  }
  else
  {
    $s = 'Empty DB: No tables';
  }
  return $s;
}

// checking if SQLite library is loaded, just in case ...
if (!extension_loaded("sqlite"))
{
  echo 'sqlite was not loaded, loading . . .<br />';
  dl("sqlite.so");
}
else
{
  echo 'sqlite was already loaded, OK<br />';
}
// general info
echo 'version: '.sqlite_libversion().'<br />';
echo 'libencoding: '.sqlite_libencoding().'<br />';
echo 'db file='.$db.'<br>';

// databse handle
$dh = sqlite_open($db, 0666, $err) or die ($err); // open if exists, create if not
echo sqlite_table_list($dh).'<br />';

// journals
if (!sqlite_table_exists($dh,$jt)) // 'journals' does not exists, creating
{
  $sql = "create table $jt (jidx INTEGER PRIMARY KEY, jname TEXT NOT NULL)";
  $result = sqlite_query($dh, $sql) or
            die("Error in query: ".sqlite_error_string(sqlite_last_error($dh)));
  echo " Table $jt was created<br>";  // double-quoted string can embed variables, $jt in this case
}
else
{
  $sql = "SELECT jidx FROM $jt";
  $result = sqlite_query($dh, $sql);
  $num = sqlite_num_rows($result);
  echo " Table $jt has $num records<br>";
}
// ref_list
if (!sqlite_table_exists($dh,$rt))
{
  $sql = "create table $rt (ridx INTEGER PRIMARY KEY, authors TEXT NOT NULL,
          title TEXT NOT NULL, volume TEXT, year INTEGER NOT NULL,
		  pages TEXT, no TEXT, keywords TEXT, jidx INTEGER NOT NULL)";
  $result = sqlite_query($dh, $sql) or
            die("Error in query: ".sqlite_error_string(sqlite_last_error($dh)));
  echo " Table $rt was created<br>";
}
else
{
  $sql = "SELECT ridx FROM $rt";
  $result = sqlite_query($dh, $sql);
  $num = sqlite_num_rows($result);
  echo " Table $rt has $num records<br>";
}
// all done, close database file
sqlite_close($dh);
?>
  <p>
  <a href="pages/manage_db.html">Back to management</a>
  </p>
  </body>
</html>

This script creates our database and tables if they were not existed. Next time you run, it gives some genereal DB information.

Refactoring

We are going to use the same constants and functions in all our files. Instead of using an ancient "copy and paste" technique, cut them once and put the code in a separate file sqlite_utils.php.

<?php
$jt = 'journals';
$rt = 'ref_list';
$db = '/home/u123/bob/refdb/ref_db.sqlite';

function sqlite_is_empty($dh)
{
  $result = sqlite_query($dh,"SELECT name FROM sqlite_master WHERE type='table'");
  return (sqlite_num_rows($result) == 0);
}

function sqlite_table_exists($dh, $mytable)
{
  // counts the tables that match the name given
  $result = sqlite_query($dh,"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$mytable'");
  // casts into integer
  $count = intval(sqlite_fetch_single($result));
  return $count > 0;
}

function sqlite_table_list($dh)
{
  $result = sqlite_query($dh,"SELECT name FROM sqlite_master WHERE type='table'");
  if (sqlite_num_rows($result) > 0)
  {
	$s = 'Tables:<br />';
    while (sqlite_has_more($result)) {
      $row = sqlite_fetch_single($result);
      $s .= $row.'<br />';
    }
  }
  else
  {
    $s = 'Empty DB: No tables';
  }
  return $s;
}

if (!extension_loaded("sqlite"))
{
  dl("sqlite.so");
};
?>

Now instead of actual pasting we just call include('sqlite_utils.php');

Add new record

File add_new.php:

<HTML>
<HEAD>
<TITLE>Add new record</TITLE>
</HEAD>
<BODY bgcolor = "#EEEEF0">
<?php
include('sqlite_utils.php');
$dh = sqlite_open($db, 0666, $err) or die ($err);

if (!sqlite_is_empty($dh))
{
  $sql = "SELECT * FROM $jt";
  $result = sqlite_query($dh, $sql);

  if (sqlite_num_rows($result) > 0)
  {
    // drop-down menu generation
    $option = '<select name="jname">'."\n";
    while($row = sqlite_fetch_array($result))
    {
      $option .= '<option value='.$row[0].'>'.$row[1].'</option>'."\n";
    }
    $option .= "</select>\n";
  }
  // check to see if the form was submitted with a new journal name
  if (isset($_POST['submit']))
  {
    if (!empty($_POST['new_jname']) )
    {
      $jname = sqlite_escape_string($_POST['new_jname']);
      $sql = 'INSERT INTO '.$jt.' (jidx,jname) VALUES (NULL, "'.$jname.'")';
	  $result = sqlite_query($dh, $sql)
	            or die("Error in query: ".sqlite_error_string(sqlite_last_error($dh)));
	  echo "<p><i>Journal successfully inserted!</i></p>";

      $sql = "SELECT * FROM $jt";  // for sorting add: ORDER BY jname
      $result = sqlite_query($dh, $sql);
      if (sqlite_num_rows($result) > 0)
      {
        $option = '<select name="jname">'."\n";
	    while($row = sqlite_fetch_array($result))
	    {
	      $option .= '<option value='.$row[0].'>'.$row[1].'</option>'."\n";
	    }
	    $option .= "</select>\n";
      }
    }
    else
    {
      echo "<p><i>Incomplete form input. Record not inserted!</i></p>";
    }
  }

 // check to see if the form was submitted with a new article reference
  if (isset($_POST['save']))
  {
    if (!empty($_POST['authors']) && !empty($_POST['title']) && !empty($_POST['year']) )
    {
	  $authors = sqlite_escape_string($_POST['authors']);
	  $title = sqlite_escape_string($_POST['title']);
	  $jidx = sqlite_escape_string($_POST['jname']);
	  $volume = sqlite_escape_string($_POST['volume']);
	  if ( empty($_POST['volume']) ) {
	    $volume = 'NULL';
	  } else {
	    $volume = "'".sqlite_escape_string($_POST['volume'])."'";
	  }
	  $year = sqlite_escape_string($_POST['year']);
	  $pages = sqlite_escape_string($_POST['pages']);
	  if ( empty($_POST['no']) ) {
	    $no = 'NULL';
	  } else {
	    $no = "'".sqlite_escape_string($_POST['no'])."'";
	  }
	  $keywords = sqlite_escape_string($_POST['keywords']);
	  $sql = "INSERT INTO $rt (ridx,authors,title,volume,year,pages,no,keywords,jidx)
	          VALUES (NULL,'$authors','$title',$volume,$year,'$pages',$no,'$keywords',$jidx);";
//	   echo "sql = $sql<br />"; // un-comment for debugging
	  $result = sqlite_query($dh, $sql) or die("Error in query: ".sqlite_error_string(sqlite_last_error($dh)));
	  echo "<p><i>Record successfully inserted!</i></p>";
    }
    else
    {
      echo "<p><i>Incomplete form input. Record not inserted! Go Back</i></p>";
    }
  }
}
else
{
  echo 'Create DB first:<br />';
}
  sqlite_close($dh);
?>
<h3>Enter new record:</h3>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<br />
<table>
  <tr><td>Authors: </td><td><input type="text" name="authors" size="80"></td></tr>
  <tr><td>Title: </td><td><input type="text" name="title" size="80"></td></tr>
  <tr><td>Select Journal: </td><td><?php echo $option;?></td></tr>
  <tr><td>Volume: </td><td><input type="text" name="volume" size="8"></td></tr>
  <tr><td>Year: </td><td><input type="text" name="year" size="6"></td></tr>
  <tr><td>Pages: </td><td><input type="text" name="pages"></td></tr>
  <tr><td>No: </td><td><input type="text" name="no" size="8" value =""></td></tr>
  <tr><td>Keywords: </td><td><input type="text" name="keywords" size="80"></td></tr>
  <tr><td><input type="submit" name="save" value="Save"></td><td><input type=reset value="Clear All"></td>
</table>
<p>
  <a href="manage_db.html">Back to management</a>
</p>
<hr />
Update Journal List<br />
New Journal: <input type="text" name="new_jname"> &nbsp;
<input type="submit" name="submit" value="Update">
</form>
</BODY>
</HTML>

The page looks like this

Enter new record

Note: Add at least one journal before entering the other fields!

Show All references

File show_all.php:

<HTML>
<HEAD>
<TITLE>Show all records</TITLE>
</HEAD>
<BODY bgcolor = "#EEEEF0">
<?php
include('sqlite_utils.php');
$dh = sqlite_open($db, 0666, $err) or die ($err);

if (!sqlite_is_empty($dh))
{
  $sql = "SELECT r.ridx,r.authors,r.title,j.jname,r.volume,r.year,r.pages,r.no
          FROM $rt r, $jt j WHERE r.jidx=j.jidx" ;
  $result = sqlite_query($dh, $sql);

  if (sqlite_num_rows($result) > 0)
  {
	$linenumber = 0;
	$refs = '';
	while($row = sqlite_fetch_array($result))
	{
	  if (is_null($row[4])) {$v = ''; } else {$v =  " <b>$row[4]</b>,";}
	  if (is_null($row[7])) {$n = ''; } else {$n =  " No $row[7],";}
	  if (is_null($row[6])) {$p = '.'; } else {$p =  ", $row[6].";}
	  $linenumber++;
	  $refs .= "$linenumber. $row[1]. $row[2]. <i>$row[3]</i>,$v$n $row[5] $p<br />";
	}
	$refs = stripslashes($refs);
  }
}
else
{
  echo 'Create DB first!<br />';
}
  // all done, close database file
  sqlite_close($dh);
?>

<p>
<h3>References</h3>

<?php echo $refs; ?>
<br />
<p>
  <a href="manage_db.html">Back to management</a>
</p>
</form>

</BODY>
</HTML>

The script gives a formatted list of references similar to this one:

References

1. W. Naumann, N. V. Shokhirev, A.Szabo. The exact asymptoyic relaxation of pseudo-first order reversible reactions. Phys. Rev. Lett., 79, 1997 , 3074-3077.
2. N. V. Shokhirev, F. A. Walker. Co- and counter-rotation of magnetic axes and axial ligands in low- spin Ferriheme Systems. J. Am. Chem. Soc., 120, 1998 , 981-990.
3. T. Kh. Shokhireva, N. V. Shokhirev, F. A. Walker. Assignment of Heme Resonances and Determination of the Electronic Structures of High- and Low-Spin Nitrophorin 2 by ¹H and ¹³C NMR Spectroscopy: An Explanation of the Order of Heme Methyl Resonances in High-Spin Ferriheme Proteins. Biochemistry, 42, 2003 , 679-693.
4. M. J. M. Nesset, N. V. Shokhirev, P. D. Enemark, S. E. Jacobson, F. A. Walker. Models of the Cytochromes. Redox properties and thermodynamic stabilities of Complexes of "Hindered" Iron(III) and Iron(II) Tetraphenylporphyrinates with Substituted Pyridines and Imidazoles. Inorg. Chem., 35, 1996 , 5188-5200.

Back to management

Remarks.

The references are listed in an arbitrary order. The variable $linenumber is used for the consecutive numbering.
You can modify the SQL statement for ordering and selection.

Example 1. Descending sort by year:

  $sql = "SELECT r.ridx,r.authors,r.title,j.jname,r.volume,r.year,r.pages,r.no
          FROM $rt r, $jt j WHERE r.jidx=j.jidx ORDER BY -r.year";

Example2. Search by name:

  $sql = "SELECT r.ridx,r.authors,r.title,j.jname,r.volume,r.year,r.pages,r.no
          FROM $rt r, $jt j WHERE r.jidx=j.jidx AND r.authors LIKE '%Walker%'";

Edit/Delete record

File edit_db.php:

<HTML>
<HEAD>
<TITLE>Edit record</TITLE>
</HEAD>
<BODY bgcolor = "#EEEEF0">
<?php
include('sqlite_utils.php');
$ref_id=0;

$dh = sqlite_open($db, 0666, $err) or die ($err);

// check to see if the form was submitted with ref_id
if (isset($_POST['ref_id_select']))
{
  if (!empty($_POST['ref_id']) )
  {
    $ref_id = sqlite_escape_string($_POST['ref_id']);

    $sql_all = "SELECT r.ridx,r.authors,r.title,r.jidx,r.volume,r.year, r.pages,
	    r.no,r.keywords FROM $rt r, $jt j WHERE (r.jidx=j.jidx AND r.ridx=$ref_id)";
    $result = sqlite_query($dh, $sql_all);

    if (sqlite_num_rows($result) > 0)
    {
      $row = sqlite_fetch_array($result);
      if (is_null($row[4])) {$v = ''; } else {$v =  $row[4];}
      if (is_null($row[7])) {$n = ''; } else {$n =  $row[7];}
      if (is_null($row[6])) {$p = ''; } else {$p =  $row[6];}

      $sql = "SELECT * FROM $jt";
      $res = sqlite_query($dh, $sql);
      if (sqlite_num_rows($result) > 0)
      {
	$option = '<select name="jname">'."\n";
	while($r = sqlite_fetch_array($res))
	{
	  if ($r[0] == $row[3]) {$r0 = $r[0].' selected';} else {$r0 = $r[0];}
	  $option .= '<option value='.$r0.'>'.$r[1].'</option>'."\n";
	}
	$option .= "</select>\n";
      }

        $sinput =
'<table>
  <tr><td>Authors: </td><td><input type="text" name="authors" size="70" value="'.$row[1].'"></td></tr>
  <tr><td>Title: </td><td><input type="text" name="title" size="80" value="'.$row[2].'"></td></tr>
  <tr><td>Select Journal: </td><td>'.$option.'</td></tr>
  <tr><td>Volume: </td><td><input type="text" name="volume" size="8" value="'.$v.'"></td></tr>
  <tr><td>Year: </td><td><input type="text" name="year" size="6" value="'.$row[5].'"></td></tr>
  <tr><td>Pages: </td><td><input type="text" name="pages" value="'.$p.'"></td></tr>
  <tr><td>No: </td><td><input type="text" name="no" size="8" value="'.$n.'"></td></tr>
  <tr><td>Keywords: </td><td><input type="text" name="keywords" size="80" value="'.$row[8].'"></td></tr>
  <tr><td><input type="submit" name="save" value="Save"></td><td><input type=reset value="Clear All"></td></tr>
  <tr><td><b><font color="red">Delete Record </color></b></td>
      <td><input type="submit" name="delete" value="Delete"></td>
  </tr>
</table>';
    }
  }
}

if (isset($_POST['save'])) // save updates
{
  if (!empty($_POST['authors']) && !empty($_POST['title']) && !empty($_POST['year']) )
  {
    $ref_id = $_POST['ref_id'];
    $authors = sqlite_escape_string($_POST['authors']);
    $title = sqlite_escape_string($_POST['title']);
    $jidx = sqlite_escape_string($_POST['jname']);
    $volume = sqlite_escape_string($_POST['volume']);
    if ( empty($_POST['volume']) ) {
      $v = 'NULL';
    } else {
      $v = "'".sqlite_escape_string($_POST['volume'])."'";
    }
    $year = sqlite_escape_string($_POST['year']);
    if ( empty($_POST['pages']) ) {$p = 'NULL';}
    else {
      $p = "'".sqlite_escape_string($_POST['pages'])."'";}
    if ( empty($_POST['no']) ) {$n = 'NULL';}
    else {
      $n = "'".sqlite_escape_string($_POST['no'])."'";
    }
    $keywords = sqlite_escape_string($_POST['keywords']);
    $sql = "UPDATE $rt SET authors='$authors',title='$title',volume=$v,
	          year='$year',pages=$p,no=$n,keywords='$keywords',jidx='$jidx'
	    WHERE ridx='$ref_id'";
//  echo "sql = $sql<br />";  // debug
    $result = sqlite_query($dh, $sql) or die("Error in query: ".sqlite_error_string(sqlite_last_error($dh)));
    echo "<p><i>Record successfully updated!</i></p>";
  } else {
    echo "<p><i>Incomplete form input. Record not updated! Go Back</i></p>";
  }
}

if (isset($_POST['delete'])) // delete selected record
{
  $ref_id = $_POST['ref_id'];
  $sql = "DELETE FROM $rt WHERE ridx='$ref_id'";
  $result = sqlite_query($dh, $sql) or die("Error in query: ".sqlite_error_string(sqlite_last_error($dh)));
	echo "<p><i>Record successfully deleted!</i></p>";
}
?>

<h3>References</h3>
<?php

$sql_all = "SELECT r.ridx,r.authors,r.title,j.jname,r.volume,r.year,r.pages,r.no
          FROM $rt r, $jt j WHERE r.jidx=j.jidx";
$result = sqlite_query($dh, $sql_all);

if (sqlite_num_rows($result) > 0)
{
  $refs = '';
  while($row = sqlite_fetch_array($result))
  {
    if (is_null($row[4])) {$v = ''; } else {$v =  " <b>$row[4]</b>,";}
    if (is_null($row[7])) {$n = ''; } else {$n =  " No $row[7],";}
    if (is_null($row[6])) {$p = '.'; } else {$p =  ", $row[6].";}
    $refs .= "$row[0]. $row[1]. $row[2]. <i>$row[3]</i>,$v$n $row[5] $p<br />";
  }
  echo stripslashes($refs);
}
sqlite_close($dh);

?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

<br />
Enter Ref ID: <input type="text" name="ref_id" value="<?php echo $ref_id; ?>" size="8">
<input type="submit" name="ref_id_select" value="OK"><br>
<br />
<?php echo $sinput; ?>
<p>
  <a href="manage_db.html">Back to management</a>
</p>
</form>

</BODY>
</HTML>

This code is functional but still can be refactored (a home exercise).

IT Tutorials | ABC Tutorials | Home

Please e-mail me at nikolai@u.arizona.edu

Authors:
Title:
Select Journal:
Volume:
Year:
Pages:
No:
Keywords: