Embedding PHP query results into your web page

In an earlier post I gave an example of a PostgreSQL function and a php script that would output the information in a sample web page. Now what am I going to do with this script? I really would like to embed the information within another web page. I also have other little snippets of information that I would like to update in a similar manner, just straight queries instead of a function.

I quickly learned that you can not just embed you php code into a normal html file and expect it to display the information you want. Searching google with the phrase embed php into HTML does not bring back the results I was looking for. Well one result hinted at what I wanted. SSI (Server Side Includes) Searching on this phrase got me the following link on how to enable it on your apache web server.

Here is some example code.

DIV style="position:absolute;left:500;top:130px;width:300px;height:160px;z-index:1" align="center"
FONT style="font-size:24px" color="#0000FF" face="Arial" B Most Viewed Topics /B
PRE
!--#exec cgi="/cgi-bin/4rca/top_ten_views.php"--
/PRE
/DIV

This is the code that you embed into your page. I took a shortcut on my setups of SSI. I’m having it parse all HTML pages. Eventually I’m going to embed all of the pages with small snippets of code. So I feel comfortable with this decision.

DIV helps to place this inserted result exactly where you want it to display on the page.

!--#exec cgi="/cgi-bin/4rca/top_ten_views.php"--

This line makes the call to the script that is pulling back the information that you want to embed into your page.

Next is the php program


?php
// This defines some base paths to locate other files needed in by the demo
define ('IN_ROOT', true);
$root_path= './';
include($root_path . 'phpbb_db_connect.php');
// Connect and get Top N results
$connection = pg_connect("host=$host dbname=$db user=$user password=$pass");
if (!$connection)
{
die ("Could not open connection to database server");
}

$query = "select * from get_top_posts() AS (users varchar , topic varchar, views int )";

$res = pg_query($connection, $query) or die("Error in query: $query." . pg_last_error($connection));

$count_row = pg_fetch_row( $res, 0 );

$rows = pg_num_rows($res);

$query_info = array(null);

for ( $counter = 0; $counter = $rows-1; $counter += 1)
{ $query_info[$counter] = pg_fetch_row( $res, $counter );
}

?
table
?php
print " tr td FONT style=\"font-size:14px\" color=\"#000000\" face=\"Arial\" Users /FONT /td td FONT style=\"font-size:14px\" color=\"#000000\" face=\"Arial\" Topics /FONT /td td FONT style=\"font-size:14px\" color=\"#000000\" face=\"Arial\" Views /FONT /td /tr \n";

for ($k=0;$k =$rows-1;$k++) {
print " tr ";
for ($j=0;$j =2;$j++) {
echo " td FONT style=\"font-size:12px\" color=\"#000000\" face=\"Arial\" ",$query_info[$k][$j]," /FONT /td ";
}
print " /tr \n";
}
?
/table

This program is a lot shorter than the original. I’ve been able to pull out all of the extra HTML formatting that was included in the original. Now that the SSI process returns the results I just have to encapsulate the results with the proper HTML format and not worry about the Page setups. Everything between the table tags is what will be displayed on the embedded page.

You can go here to see a demo of the embedded code results. I’ll include future code results here as well.

http://www.4rca.com/index_test.html

PHP and Top N Things Example

Here is a PHP example for the top N items. There are a lot of bonuses to using PHP as opposed to a perl script. The PHP can be embedded into your HTML easily. Plus common parts can be pulled out into separate files that you reference. In the example below I have a file that keeps all of my DB connection information. This way in the future I will only have to make a change in one file if I need to point to a different database.

To See the PostgreSQL function see yesterdays post. Here


http://www.4rca.com/cgi-bin/top_ten_views.php

I had to remove all of the \> and \< symbols so that the code would print.

?php

// This defines some base paths to locate other files needed in by the demo


define ('IN_ROOT', true);
$root_path= './';
include($root_path . 'phpbb_db_connect.php');

?

html
head title Top Ten N Example PHP /title

/head
body

DIV style="position:absolute;left:180;top:130px;z-index:4" align="center"
?php

// Connect and get Top N results

$connection = pg_connect("host=$host dbname=$db user=$user password=$pass");

if (!$connection)
{
die ("Could not open connection to database server");
}


$query = "select * from get_top_posts() AS (users varchar , topic varchar, views int )";

$res = pg_query($connection, $query) or die("Error in query: $query." . pg_last_error($connection));

$rows = pg_num_rows($res);

$query_info = array(null);

for ( $counter = 0; $counter = $rows-1; $counter += 1)
{ $query_info[$counter] = pg_fetch_row( $res, $counter );
}
?

table

?php
print " tr td Users /td td Topics /td td Views /td /tr ";

for ($k=0;$k =$rows-1;$k++) {
print " tr ";
for ($j=0;$j =2;$j++) {
echo " td ",$query_info[$k][$j]," /td ";
}
print " /tr ";
}
?
/table
/DIV
/body
/html

PostgreSQL Perl and a Top N results

Ok , Real Example: check it out at http://www.4rca.com/cgi-bin/top_ten_views.cgi

First Create a function on your postgreSQL database. Note I could pass a parameter to define a top n if I wanted to. This is using the Topics of our PHPBB for my squad. Record count is low but this does scale. You could dump it into a temp table to be used by the function if memory usage becomes an issue. This could be an aggregate function select query you get the same results. Basically the time it takes to run your query is the time it takes to get the results. The added bonus of storing it as a procedure on the database is that the query is already compiled. Also notice how nice the SQL is. Easy to read and maintain. I did modify the HTML tags so that nothing gets totally left off.

CREATE OR REPLACE FUNCTION get_top_posts()
RETURNS SETOF record AS
$$
DECLARE
results record;
record_counter int;
all_user varchar;
all_topic varchar;
all_views int;

BEGIN

record_counter := 0;
all_user := 'Other Users';
all_topic := 'Other Topics';
all_views := 0;


for results in EXECUTE 'select usr.username,top.topic_title, top.topic_views as views
from
phpbb_topics top
, phpbb_users usr
where
top.topic_poster = usr.user_id
order by top.topic_views desc;' LOOP

if record_counter < 9 then
record_counter := record_counter + 1;
Return Next results;
else
record_counter := record_counter + 1;
all_views := all_views + results.views;
end if;

END LOOP;
results := (all_user, all_topic, all_views);
Return Next results;

END
$$
LANGUAGE 'plpgsql' VOLATILE;


This is the stripped down version of the perl script. You can use what ever you want now to submit the query request. You just have to be able to receive the data. Here I’m dumping it out straight. It could be thrown into a data structor and then a “Pie Chart” displayed.


#!/usr/bin/perl

use DBI;

$user="user";
$password="password";
$dbname="databasename";
$host="host address that you are wanting to connect to";

$dsn="dbi:Pg:dbname=$dbname;host=$host;port=5432";
$dbh=DBI->connect("$dsn","$user","$password") or
die print "can't connect $! \n";

$sql = "select * from get_top_posts() AS (users varchar , topic varchar, views int )";

$sth=$dbh->prepare($sql) or
die "can't prepare: $sql \n";

$sth->execute or
die "can't execute: $sql \n";

print "Content-type: text html \n\n";
print "body";
print "table";
print "tr td Users /td td Topics /td td Views /td /tr ";

while (@row=$sth->fetchrow_array)
{
$users = $row[0];
$topic = $row[1];
$views = $row[2];

print "tr td $users /td td $topic /td td $views /td /tr ";
}
print "/table";
print "/body";


It’s a rough Hack, but it works. It’s not supposed to be pretty. You can click on the image and go to the main site and navigate to the forums. Make the numbers change if you like.

Something Old New

BOFH

I can’t remember the exact first time I read this, but I remember the web was new and the text was plain on a light blue background, because that was the default setting for backgrounds for browsers back then. No images, no sound, just text. I’m sure it was originally posted to a uusenet group at one point. It’s been housed at this location for a number of years. Stuff has actually been added since the last time I visited.

Upgrading PostgreSQL 7.4 to 8.1.6

This past weekend I did some testing of exporting and importing databases between postgreSQL 7.4 and 8.1.6. I had to try several different methods before finding the correct way getting the data across. Outside of the Export and import process, I also want to change the character set that the database is encoded in. Apparently the default encoding set for 7.4 was SQL_ASCII and the standard encoding set for 8.1.6 is UTF8. Naturally the goal is get the final upgraded database in an UTF8 encoding.

This default encoding was the first stumbling block. I encountered several errors revolving around incorrect byte size. After I did some Googling found the answer.

The second roadblock was trying to figure out how to get from SQL_ASCII to UTF8. The pg_dump process using a plain text dump doesn’t work correctly between 7.4 and 8.1.6. The 8.1.6 kept giving a “did not find magic string in file header” message when I attempted the restore process. Or an encoding byte value error message while running the plain text dump through psql. These are the abbreviated steps That I’ve found that produce no errors.

pg_dump -f phpbb.dat -Fc phpbb
create database phpbb with encoding 'SQL_ASCII';
pg_restore -Fc -c -d phpbb phpbb.dat
pg_dump -f phpbb.dat -Fp phpbb
drop database phpbb;
create database phpbb;
psql phpbb < phpbb.dat

Dumping out in plain text from the 8.1.6 eliminates some of the indexing errors I received from a 7.4 plain text dump import into a UTF8 database.

When I do move the forums it shouldn’t take more than 20 minutes to go through the entire process. The other database is a different story.

Upgrade time

Well it's upgrade time. Time to upgrade to Fedora Core 6 and PostgreSQL 8.1.6 . Currently everything is on Core 4 and 7.4 . It’s been running great for 2 years.

What does this mean? Forums on the site will be down for period of time. I’m still working on the database data migration. The forum data is small and should take up much time. Hopefully, the forum data will take less than an hour. The other data looks like an 8 hour plus migration. I haven’t taken record counts on all of the tables. Luckily when I started all of this kept the databases separate. It will translate into posting slowness while the import id going on.

All other web site activity should remain normal.

On the plus side, I’ll actually have documented all of the necessary steps of recreating the servers.

Another note: Why not 8.2 for postgreSQL? Apparently the perl DBD::PG is not compatible for 8.2 and that is a must for my perl scripts.