example of connecting to the database and running an SQL command. The query returns a php array, within a while loop.
<?php
//hide the statement below in an include file
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
// PHP supports multi line strings which help readability.
// Multi Line SQL readable SQL statement, EOD; must be
$sql =<<<EOD
SELECT *
FROM boats ;
EOD;
// my local function to convert returned data to HTML
//
function renderRow( $row ){
#$html = $row[0];
$html = "";
foreach ( $row as $key => $value ) {
#print_r( $row );
$html .= "<td> $value </td>";
}
return "<tr>$html</tr>\n";
}
// SQL statement
$sql = " SELECT 'hello' as msg " ;
$uresult = $mysqli->query( $sql , MYSQLI_USE_RESULT);
// Use the result, possible call a function called render
if ($uresult) {
$html = "";
while ($row = $uresult->fetch_assoc()) {
echo $row[ 'msg' ] ."<BR>". PHP_EOL;
// or call function to render row and gather HTML
$html .= renderRow( $row );
}
// emit the HTML as required
// This can inprove readability
//echo $html ;
// emit the HTML as required
// This can inprove readability
echo "<table>\n $html\n </table>\n" ;
}
?>
<?php
//https://www.php.net/manual/en/pdo.connections.php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here
$sth = $dbh->query('SELECT * FROM foo');
// and now we're done; close it
$sth = null;
$dbh = null;
?>
If using an SQL database, things get much more complicated as you need to learn SQL, a server side language like PHP or Perl. How to connect to the database and design of a database schema
<?php
//
// select guestbook entries and return
$db_host = "host_name_here";
$username = "userid_here";
$password = "your_password_here";
$DB_name = "your_dbname_here";
/*
// old format
include( './pw/db0.php' );
$chan = mysql_connect ($db_host, $username, $password);
mysql_select_db ($DB_name, $chan);
//$resultid = mysql_query ("select * from GB order by postedOn desc ", $chan);
$resultid = mysql_query ("select * from GB order by postedOn ", $chan);
*/
// include passwords and connection details from a file elsewhere.
include( './pw/db0.php' );
$mysqli = new mysqli($db_host, $username, $password, $DB_name);
if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); }
/* fetch data */
$uresult = $mysqli->query( " select * from GB order by postedOn desc " , MYSQLI_USE_RESULT);
?>
<?php
function escp ( $s ) {
// escape characters that are code to Java Script, and HTML
// PHP escapes " and ' by default, so no need for call to // $ss = addslashes ( $s );
$ss = $s;
// //CRLF and LF //HTML specials
$search = array ( "'\r'", "'\n'", "'&'", "'<'", "'>'" );
$replace = array ( "\\r\\\r", "", "&", "<", ">" );
// Uses regular expressions to remove
return preg_replace ($search, $replace, $ss );
}
/*
while ( $row = mysql_fetch_assoc( $resultid ) ) {
//echo " // ".$row["guestbook"] ." //<BR>\n";
echo " gbF( '".escp($row["name"])."','".escp($row["email"])."',".
"'".escp($row["postedOn"])."','".escp($row["IPaddress"])."',".
"'".escp($row["userfield1"])."','".escp($row["userfield2"])."',".
"'".escp($row["userfield3"])."','".escp($row["userfield4"])."',".
"'".escp($row["comments"])."' ); //<BR>\n";
}
*/
if ($uresult) {
while ($row = $uresult->fetch_array()) {
//echo " // ".$row["guestbook"] ." //<BR>\n";
echo " gbF( '".escp($row["name"])."','".escp($row["email"])."',".
"'".escp($row["postedOn"])."','".escp($row["IPaddress"])."',".
"'".escp($row["userfield1"])."','".escp($row["userfield2"])."',".
"'".escp($row["userfield3"])."','".escp($row["userfield4"])."',".
"'".escp($row["comments"])."' ); //<BR>\n";
}
}
?>
The guest book script accepts a post from a form and appends the data onto the end of a database table.
It is possible to query the database and the client gets a list of javascript function calls populated with the data from the database. It is up to you to write the guts of the function to format as you wish.
/tdb2.php uses renderRow() and multiple $sql=
Download PHP scripts: dbPHP.zip
Form example:
/db0601/db8.php
Action: <form name="F1" action="/db0601/db6.php" method="POST" >
/db0601/db6.php
Returns data in JavaScript gbF() format:
/db0601/db9.php
Webpage that includes db9.php:
gbook_db9.htm
Returns data in Table format:
/db0601/db7.php
Include the javascript into your web page:
<script language = "JavaScript" type ="text/javascript"
src ="/db0601/db9.php?par" >
</script>
Here is an example web page: gbook_db9.htm
<html><body>
<script>
opStr ="";
// The function below is called once per guestbook entry.
// You write this function to do what you want!!
function gbF(
name,email,postedOn,IPaddress,userfield1,userfield2,userfield3,userfield4,comments){
// You do what you want here
opStr += postedOn +"<BR>\n"
}
// test call
gbF( "Test Call","email","15 Nov 2003","IPaddress","userfield1","userfield2","userfield3","userfield4","Testcomments")
</script>
<!-- Include remote guestbook data-->
<!--
<script language = "JavaScript" type =
"text/javascript"
src =
"/gb/gbookFXXX.js" >
</script>
-->
<!-- Include remote guestbook that runs a PHP script to
query the database -->
<script language = "JavaScript" type =
"text/javascript"
src =
"/db0601/db9.php?par" >
</script>
<script> self.document.write( opStr )
</script>
</body></html>
You can also read the javascript downloaded
/db0601/db9.php?par
And you get JavaScript:
//par//<BR>
// XXX //<BR>
gbF( 'Doug ','email','2007-10-29 07:06:24','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Mon Oct 29 07:06:03 UTC 2007|A test post' ); //<BR>
// XXX //<BR>
gbF( 'name','email','2007-10-28 17:57:53','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Sun Oct 28 17:57:39 UTC 2007|' ); //<BR>
// Account //<BR>
gbF( 'Doug','email','2007-10-28 08:33:33','212.56.108.219 | dougrice.plus.com','userfield1 - test','userfield2 - uf2','userfield3 - uf3','userfield4 - uf4','uf5 Sun Oct 28 08:32:00 UTC 2007|Here is a test.' ); //<BR>
// XXX //<BR>
gbF( 'Doug','email','2007-10-28 08:32:02','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Sun Oct 28 08:32:00 UTC 2007|' ); //<BR>
This has not been made secure yet but is a attempt to fetch the guestbook from a MySQL database.
Insert guest book entries with SQL like:
-- phpMyAdmin SQL Dump
-- version 2.10.1
-- http://www.phpmyadmin.net
--
--------------------------------------------------------
--
-- Table structure for table `GB`
--
CREATE TABLE `GB` (
`guestbook` text,
`name` text,
`email` text,
`postedOn` text,
`IPaddress` text,
`userfield1` text,
`userfield2` text,
`userfield3` text,
`userfield4` text,
`comments` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `GB`
--
INSERT INTO `GB` (`guestbook`, `name`, `email`,
`postedOn`, `IPaddress`, `userfield1`, `userfield2`, `userfield3`,
`userfield4`, `comments`) VALUES
('guestbookname', 'CRtest', 'email', now() ,
'212.56.108.219 | dougrice.plus.com', 'userfield1', '4', 'userfield3',
'userfield4', 'uf5 Tue Oct 23 08:24:05 UTC+0100 2007|Another test 1 " ')
INSERT INTO `GB` (`guestbook`, `name`, `email`,
`postedOn`, `IPaddress`, `userfield1`, `userfield2`, `userfield3`,
`userfield4`, `comments`) VALUES
('guestbookname', 'CRtest', 'email', '2007-10-27
16:58:28', '212.56.108.219 | dougrice.plus.com', 'userfield1', '4',
'userfield3', 'userfield4', 'uf5 Tue Oct 23 08:24:05 UTC+0100 2007|Another
test 1 " '),
('XXX', 'name', 'email', '2007-10-28 17:57:53',
'212.56.108.219 | dougrice.plus.com', 'userfield1', 'userfield2',
'userfield3', 'userfield4', 'uf5 Sun Oct 28 17:57:39 UTC 2007|'),
('XXX', 'Doug ', 'email', '2007-10-29 07:06:24',
'212.56.108.219 | dougrice.plus.com', 'userfield1', 'userfield2',
'userfield3', 'userfield4', 'uf5 Mon Oct 29 07:06:03 UTC 2007|A test post');
<?php
//
// select guestbook entries and return
// <script src=db9.php></script>
$db_host = "host_name_here";
$username = "userid_here";
$password = "your_password_here";
$DB_name = "your_dbname_here";
// Pick up database login details
include( './pw/db0.php' );
echo "//".$QUERY_STRING."//<BR>\n";
$chan = mysql_connect ($db_host, $username, $password);
mysql_select_db ($DB_name, $chan);
$resultid = mysql_query ("select * from GB order by postedOn desc ", $chan);
$infoStr = mysql_info();
function escp ( $s ) {
// escape characters that are code to Java Script, and HTML
// PHP escapes " and ' by default, so no need for call to
// $ss = addslashes ( $s );
$ss = $s;
// //CRLF and LF //HTML specials
$search = array ( "'\r'", "'\n'", "'&'",
"'<'", "'>'" );
$replace = array ( "\\r\\\r", "", "&",
"<", ">" );
// Uses regular expressions to remove
return preg_replace ($search, $replace, $ss );
}
while ( $row = mysql_fetch_assoc( $resultid ) ) {
echo " // ".$row["guestbook"] ." //<BR>\n";
echo " gbF( '".$row["name"]."','".$row["email"].
"','".$row["postedOn"]."','".$row["IPaddress"]."',".
"'".$row["userfield1"]."','".$row["userfield2"]."',".
"'".$row["userfield3"]."','".$row["userfield4"]."',".
"'".escp($row["comments"])."' ); //<BR>\n";
}
?>
<script language = "JavaScript" type =
"text/javascript"
src = "/db0601/db9.php?par" >
</script>
Each row is supplied:
// XXX //<BR>
gbF( 'Doug','email','2007-10-28 08:32:02','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Sun Oct 28 08:32:00 UTC 2007|' ); //<BR>
<script language="php">
// db6.php - Guestbook script the saves the data wrapped in JavaScript function calls and inserts into db
// copyright 2007 douglas rice.
// This script takes the data from the form fields.
// and wraps it in a JavaScript Function Call:
// gbF( "name","email",postedOn,IPaddress,"userfield1","userfield2","userfield3","userfield4","comments", ).
// After appending the data the URL in form field: nextpage replaces the page.
//
// This uses the same form fields as the BTOpenWorld Guestbook.
//
ini_set ( "always_populate_raw_post_data","true") ;
function esc ( $s ) {
// escape characters that are code to Java Script, and HTML
// PHP escapes " and ' by default, so no need for call to
// $ss = addslashes ( $s );
$ss = $s;
// //CRLF and LF //HTML specials
$search = array ( "'\r'", "'\n'", "'&'",
"'<'", "'>'" );
$replace = array ( "\\r\\\r", "", "&",
"<", ">" );
// Uses regular expressions to remove
$result = preg_replace ($search, $replace, $ss );
// The "i" after the pattern delimiter indicates a case-insensitive search
if (preg_match("/alprazolam|url=/i", $result))
{
$result = "url=spam ";
} else {
$result = preg_replace ($search, $replace, $ss );
}
return $result;
}
$userfield5 = $_REQUEST["userfield5"];
$guestbook = $_REQUEST["guestbook" ];
$name = $_REQUEST["name"];
$email = $_REQUEST["email"];
$userfield1 = $_REQUEST["userfield1"];
$userfield2 = $_REQUEST["userfield2"];
$userfield3 = $_REQUEST["userfield3"];
$userfield4 = $_REQUEST["userfield4"];
$comments = $_REQUEST["comments"];
$nextpage = $_REQUEST["nextpage"];
$date=gmstrftime ("%d %b %Y %H:%M:%S");
//$HTTP_RAW_POST_DATA);
//file_put_contents("php://filter/write=string.rot13/resource=gbOP.txt","--------------");
$filename = 'gbOP.txt';
// Let's make sure the file exists and is writable first.
if (is_writable($filename)) {
// In our example we're opening $filename in append mode.
// The file pointer is at the bottom of the file hence
// that's where $somecontent will go when we fwrite() it.
if (!$handle = fopen($filename, 'a')) {
echo "Cannot open file ($filename)";
exit;
}
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
if (fwrite($handle, "\n---------------- "
.$date. "--" .$_SERVER['REMOTE_ADDR']. "--" .$hostname. "--\n " ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
foreach ($_REQUEST as $key => $value ) {
// Write $somecontent to our opened file.
if (fwrite($handle, $key."=".$value."\n" ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
}
if (fwrite($handle, "\n------------------------ ENV ------------------------\n " ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
$spam = false;
foreach ($_ENV as $key => $value ) {
// Write $somecontent to our opened file.
if (fwrite($handle, $key."=".$value."\n" ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
if (preg_match("/compatible; MSIE 6\.0; Windows NT 5\.1/i", $result)) {
$spam = true;
}
}
// Write $somecontent to our opened file.
// if (fwrite($handle, $HTTP_RAW_POST_DATA."\n".$HTTP_POST_VARS ) === FALSE) {
// echo "Cannot write to file ($filename)";
// exit;
// }
//echo "Success, wrote ($somecontent) to file ($filename)";
fclose($handle);
} else {
echo "The file $filename is not writable";
}
$date=gmstrftime ("%d %b %Y %H:%M:%S");
if ( $spam == false ) {
// append to the file gbookF + the name specified by $guestbook + .js
$fp = fopen ("./gbookF$guestbook.js", "a+b");
fputs($fp,"// HTTP_USER_AGENT:".$_ENV["HTTP_USER_AGENT"]."\n");
fputs($fp,"// QUERY_STRING:".$_ENV["QUERY_STRING"]."\n");
//fputs($fp,"// HTTP_REFERER:".$_ENV["HTTP_REFERER"]."\n");
fputs($fp,"gbF( //guestbook.php ".esc( $userfield5 )."\n");
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
fputs($fp,"'".esc( $name )."',\n'".esc( $email )."',\n '$date',\n '".
getenv ("REMOTE_ADDR" )." ".getenv ( "HTTP_X_FORWARDED_FOR" )." | ".esc($hostname)."',\n");
// output the 4 user fields and comments
fputs($fp,"'".esc( $userfield1 )."',\n'".esc( $userfield2 )."',\n");
fputs($fp,"'".esc( $userfield3 )."',\n'".esc( $userfield4 )."',\n");
fputs($fp,"'".esc( $userfield5 )."|".esc( $comments )."'\n");
fputs($fp,");\n");
fclose($fp);
$db_host = "host_name_here";
$username = "userid_here";
$password = "your_password_here";
$DB_name = "your_dbname_here";
// Pick up database login details
include( './pw/db0.php' );
$chan = mysql_connect ($db_host, $username,
$password);
mysql_select_db ($DB_name, $chan);
$sqlStr =
"INSERT INTO GB VALUES " .
// fputs($fp,"// HTTP_USER_AGENT:".$_ENV["HTTP_USER_AGENT"]."\n");
// fputs($fp,"// QUERY_STRING:".$_ENV["QUERY_STRING"]."\n");
// //fputs($fp,"// HTTP_REFERER:".$_ENV["HTTP_REFERER"]."\n");
// fputs($fp,"gbF( //guestbook.php ".esc( $userfield5 )."\n");
// $hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
"( ".
"'".esc( $guestbook )."',".
// "'".esc( $name )."',\n '".esc( $email )."',\n '$date',\n '".getenv ( "REMOTE_ADDR")." ".getenv ( "HTTP_X_FORWARDED_FOR" )." | ".esc($hostname)."',\n".
"'".esc( $name )."',\n '".esc( $email )."',\nnow(),\n '".
getenv ( "REMOTE_ADDR" )." ".getenv ( "HTTP_X_FORWARDED_FOR" )." | ".esc($hostname)."',\n".
// output the 4 user fields and comments
"'".esc( $userfield1 )."',\n'".esc( $userfield2 )."',\n".
"'".esc( $userfield3 )."',\n'".esc( $userfield4 )."',\n".
"'".esc( $userfield5 )."|".esc( $comments )."'\n ); " ;
// echo( $sqlStr ."<BR>\n" );
$resultid = mysql_query ( $sqlStr , $chan);
$infoStr = mysql_info() . " - affected rows: " . mysql_affected_rows();
// echo( "insert result:".$resultid." " . $infoStr . "<P>");
// echo "<br>";
}
// reload the page with the URL provided in next page.
header("Location: $nextpage\n\n\n" );
exit;
</script>