topic

Job with the text and graphic data in common in PHP and MySQL

I have read many documents devoted to this subject, in result have tried ten from them, and nothing having received in the answer correctly working, have decided to write itself more less suitable for my problem{task} the decision.

So, production of a problem{task}:

1. It is required to store{keep} such information in a database:

Surname, name, patronymic email, a photo and the brief description (or the biography) the person.

For achievement of an object in view quite widespread complete set of the web - server is chosen: Apache 1.3.20, PHP 4.1.0, MySQL 4.0.0.

For the beginning we create a DB:

mysqladmin-p create testdb

Then we do{make} its{her} current:

use testdb.

Further we create the table for storage of the information:

CREATE TABLE infouser (

id_infouser int (7) unsigned NOT NULL auto_increment,

lastname varchar (255) NOT NULL default ",

firstname varchar (255) NOT NULL default ",

patronym varchar (255) NOT NULL default ",

imageinfouser mediumblob,

filename1 varchar (50) default NULL,

filesize1 varchar (50) default NULL,

filetype1 varchar (50) default NULL,

infoinfouser varchar (255) default NULL,

emailinfouser varchar (100) default NULL,

PRIMARY KEY (id_infouser))

First we shall create a file for storage of functions, such as, connection with a DB, and patterns that ten times to not copy same:

<? php

// tags for open html-docs

function html_begin ($header)

{

print (" <html> n ");

print (" <head> n ");

print (" <META HTTP-EQUIV = "Content-Type"

Content = "text/html"> ");

print (" <title> the Test DB </title> n ");

print (" </head> n ");

print (" <body text = "*000000" bgcolor

= "*52FA90"> n ");

print (" <br> <center> <table width = " 90 % "

border = "1" bgcolor = "green" cols = "1"> ");

print (" <tr> <td> <p style = " text-align:

justify; margin-left: 50 px; margin-right: 50 px ">");

if ($header)

print (" <h3> $header </h3> n ");

print (" </p> <hr width = " 100 % "

size = "1" color = "*c0c0c0"> <p> ");

}

// tags for close html-docs

function html_end ()

{

print (" </td> </tr> </table> </center> ");

print (" </body> </html> ");

}

// function for connect mysql and select database

function connect_mysql ()

{

define ("DBName", "testdb");

define ("HostName", "localhost");

define ("UserName", "valery");

define ("Password", " ");

if (! mysql_connect (HostName, UserName, Password))

{echo " the Server temporarily does not work, come later. ". DBName. "! <br> ";

echo mysql_error ();

exit;

}

mysql_select_db (DBName);

}

We do{make} the form for input of the information.

File with a name insert.php

<? php

include ("function.inc");

$header = (" to enter the new person ");

html_begin ($header);

<form action = "insert2.php" method = "post"

enctype = "multipart/form-data">

Surname: <input type = "text" name = "lastname"

size = "30" maxlenght = "30"> <br>

Name: <input type = "text" name = "firstname"

size = "30" maxlenght = "30"> <br>

Patronymic: <input type = "text" name = "patronym"

size = "30" maxlenght = "30"> <br>

Email: <input type = "text" name = "email"

size = "30" maxlenght = "30"> <br>

Photo: <input type = "file" name = "userfile"> <br>

The information on the person: <br>

<textarea name = "infoinfouser" cols = "40"

rows = "5"> </textarea>

<br>

</form>

<? php

html_end ();

Then there is a page accepting the information a name of a file obviously enough insert2.php:

<? php

include ("function.inc");

$header = (" input of the information ");

html_begin ($header);

// read file image

$fd = fopen ($userfile, "rb");

$userfile2 = fread ($fd, filesize ($userfile));

fclose ($fd);

$userfile2 = addslashes ($userfile2);

// insert in db

connect_mysql ();

mysql_query (" SELECT * FROM infouser ");

mysql_query (" INSERT INTO infouser (id_infouser, lastname, firstname, patronym,

imageinfouser, filename1, filesize1, filetype1, infoinfouser, email_infouser)

VALUES (", ' $ lastname ',' $firstname ',' $patronym ',' $userfile2 ',' $userfile_name ',

' $userfile_size ',' $userfile_type ',' $infoinfouser ',' $email ') ");

echo "<br>";

print " the New user is successfully entered ";

mysql_close ();

html_end ();

That's all we have entered the data, now they should be read somehow.

For this purpose it is used three more files.

The first file gives out the list of users.

prev.php

<? php

include ("function.inc");

$header = (" viewing of recordings ");

html_begin ($header);

connect_mysql ();

print (" <table cols = "1"> ");

// We deduce{remove} all recordings

$r=mysql_query (" SELECT * FROM infouser ");

for ($i=0; $i <mysql_num_rows ($r); $i ++)

{$f=mysql_fetch_array ($r);

print "<tr>";

print " <td align = "center"> ";

print " <a target = " _new " href = " sample.php? id = $ f [id_infouser] "> $f [lastname]

$f [firstname] $f [patronym] </a> ";

print "<p>";

print "</td>";

print "</tr>";

}

print "</table>";

mysql_close ();

html_end ();

The second file is used for delivery of the data on one user: sample.php

<? php

include ("function.inc");

$header = (" viewing of recordings ");

html_begin ($header);

connect_mysql ();

reset ($HTTP_GET_VARS);

while (list ($key, $val) = each ($HTTP_GET_VARS)) {

$sql = " SELECT * FROM infouser WHERE id_infouser = ' $val ' ";

$result = mysql_query ($sql);

$rows = mysql_num_rows ($result);

echo " <table border = "0" width = " 70 % "> n ";

for ($i = 0; $i <$rows; $i ++) {

$data = mysql_fetch_object ($result);

echo " <tr> n ";

echo " <td> <font color = "red" size

= "+1"> $data-> lastname <br> $data-> firstname <br>

$data-> patronym <br> </font> </td> n ";

echo " <td rowspan = "2"> <center> <img

src = ' download.php? id = $ data-> id_infouser ' border =

' 2 ' bgcolor = ' *01cccc '> </center> </td> n ";

echo " </tr> n ";

echo " <tr> n ";

echo " <td> <font color = "green"> $data-> emailinfouser </font> </td> n ";

echo " </tr> n ";

echo " <tr> n ";

echo " <td colspan = "2"> $data-> infoinfouser </td> n ";

echo " </tr> n ";

}

mysql_free_result ($result);

}

mysql_close ();

html_end ();

The following file realizes processing photos and delivery of them: download.php

<? php

reset ($HTTP_GET_VARS);

while (list ($key, $val) = each ($HTTP_GET_VARS)) {

define ("DBName", "testdb");

define ("HostName", "localhost");

define ("UserName", "valery");

define ("Password", " ");

if (! mysql_connect (HostName, UserName, Password))

{echo " the Server temporarily does not work, come

Later. ". DBName. "! <br> ";

echo mysql_error ();

exit;

}

mysql_select_db (DBName);

$sql = " SELECT imageinfouser, filename1, filetype1 FROM

infouser WHERE id_infouser = ' $ val ' ";

$result = @mysql_query ($sql);

$data = @mysql_result ($result, 0, "imageinfouser");

$name = @mysql_result ($result, 0, "filename1");

$type = @mysql_result ($result, 0, "filetype1");

header (" Content-type: $type ");

echo $data;

}

mysql_close ();

Change of appearance of the counter in CNStats

Requirements: CNStats 2.7 and is higher. GD 1.0 and is higher

At installation CNStats in section " the Configuration - the Code of the counter " is offered to you for accommodation on a site two types of a code:

* An obligatory code of gathering of statistics of a site - he counts visitors of a site;

* opcional`nyj a code of display of the counter - this code simply draws a picture with figures of visitings - the graphic counter.

Appearance of the counter which displays the current visitings a site it is possible to change. In given clause{article} variants of graphic counter CNStats are considered{examined}.

By default, counter CNStats looks as follows:

On him three figures are displayed. The uppermost - it is hit everything, average - it is hit today, bottom - hosts today. In this note we shall consider ways of change of appearance of the counter and ways of display on him another, not less the helpful information.

At the end of a note you can see all kinds of considered{examined} counters and to download them for use.

The initial data

CNStats can give the following information for display to the counter:

* It is hit today, yesterday and all;

* Hosts today, yesterday and all;

* Users today, yesterday and all;

* Users now on a site.

As CNStats can takes into account visitings robots it is possible to deduce{remove} the information and on them:

* Robots today, yesterday and all;

* Percentage attitudes{relations} robots / users.

Besides it is possible to make various counters changes of attendance of a site graphically displaying dynamics{changes}.

Display of counters in CNStats

By default, the script displaying a picture of the counter is in CNStats root and called cnts.php (from English Counter-Show). I recommend to name files of your counters in a similar way, for example cnts-big.php, cnts-ttf.php, etc.

Let's begin a spelling of a code with some, standard "fish" whom the code of any counter CNStats should contain:

error_reporting (E_ALL and ~E_NOTICE);

// We are connected to a configuration file.

include "config.php";

// We create images, from a preset pattern.

$im=ImageCreateFromPng ("button.png");

// We are connected with MySql the server.

$CONN = mysql_connect ($STATS_CONF ["sqlhost"],

$STATS_CONF ["sqluser"],

$STATS_CONF ["sqlpassword"]);

if (mysql_errno () == 0) {

// We choose a database

@mysql_select_db ($STATS_CONF ["dbname"]);

if (mysql_errno () == 0) {

//. Here all necessary actions are carried out

// On creation of the image...

}

// We send HTTP heading Content-Type

Header (" Content-type: image/png ");

// We generate images

ImagePng ($im);

// We clear memory

ImageDestroy ($im);

Further this code will be inserted without comments.

The counter ¹1 - standard

error_reporting (E_ALL and ~E_NOTICE);

include "config.php";

$im=ImageCreateFromPng ("button.png");

$CONN = mysql_connect ($STATS_CONF ["sqlhost"],

$STATS_CONF ["sqluser"],

$STATS_CONF ["sqlpassword"]);

if (mysql_errno () == 0) {

@mysql_select_db ($STATS_CONF ["dbname"]);

if (mysql_errno () == 0) {

// We choose the data

// t_hits - it is hit all

// hits - it is hit today

// hosts - hosts today

$r = mysql_query (" SELECT t_hits, hits, hosts FROM cns_counter ");

// It is transferable{tolerable} the unique chosen recording in an associative file

$res = mysql_fetch_array ($r, MYSQL_ASSOC);

// We release{exempt} memory

@mysql_free_result ($r);

// Vydeljam necessary colors

$black=ImageColorAllocate ($im, 0,0,0);

$color=ImageColorAllocate ($im, $COUNTER ["inkR"],

$COUNTER ["inkG"],

$COUNTER ["inkB"]);

// We deduce{remove} numbers

ImageString ($im, 2,86-6*strlen ($res ["t_hits"]), 1, $res ["t_hits"], $color);

ImageString ($im, 1,85-5*strlen ($res ["hits"]), 13, $res ["hits"], $color);

ImageString ($im, 1,85-5*strlen ($res ["hosts"]), 20, $res ["hosts"], $color);

}

Header (" Content-type: image/png ");

ImagePng ($im);

ImageDestroy ($im);

The most interesting part of this code is SQL search which chooses the displayed data from the table:

SELECT t_hits, hits, hosts FROM cns_counter

Let's consider, that else we can "pull out" from tables CNStats so that it practically did not load the server.

SELECT hits, hosts, users,

t_hits, t_hosts, t_users,

u_hits, u_hosts,

u_t_hits, u_t_hosts

FROM cns_counter;

Here:

* hits - it is hit today

* hosts - hosts today

* users - users today

* t_hits - it is hit all

* t_hosts - hosts of all

* t_users - users of all

* u_hits - it is hit from visitors for today (robots are excluded)

* u_hosts - hosts from visitors for today (robots are excluded)

* u_t_hits - it is hit from visitors of all (robots are excluded)

* u_t_hosts - hosts from visitors of all (robots are excluded)

* u_hits-hits - it is hit from robots for today

* u_hosts-hosts - hosts from robots for today

* u_t_hits-t_hits - it is hit from robots of all

* u_t_hosts-t_hosts - hosts from robots of all

Remember! Robots are taken into account only at use "PHP-Include" and the "combined" counters.

The search which obtains quantity{amount} of visitors in the data the moment taking place on a site (conditionally) is below resulted.

SELECT count (DISTINCT hid) as online

FROM cns_log

WHERE date> NOW () - INTERVAL 3 MINUTE;

The counter ¹2 - is hit everything, hit today, users today and now on a site

As you see from the image of the counter, here we used other base image, for that what to find room for figure " now on a site ". (red color)

error_reporting (E_ALL and ~E_NOTICE);

include "config.php";

$im=ImageCreateFromPng ("button-tt.png");

$CONN = mysql_connect ($STATS_CONF ["sqlhost"],

$STATS_CONF ["sqluser"],

$STATS_CONF ["sqlpassword"]);

if (mysql_errno () == 0) {

@mysql_select_db ($STATS_CONF ["dbname"]);

if (mysql_errno () == 0) {

// We choose the data

// t_hits - it is hit all

// hits - it is hit today

// users - users today

$r = mysql_query (" SELECT t_hits, hits, users FROM cns_counter ");

// It is transferable{tolerable} the unique chosen recording in an associative file

$res = mysql_fetch_array ($r, MYSQL_ASSOC);

// We release{exempt} memory

@mysql_free_result ($r);

// We select necessary colors

$black=ImageColorAllocate ($im, 0,0,0);

$red=ImageColorAllocate ($im, 255,0,0);

// We deduce{remove} numbers

ImageString ($im, 2,3,1, $res ["t_hits"], $black);

ImageString ($im, 1,3,13, $res ["hits"], $black);

ImageString ($im, 1,3,20, $res ["users"], $black);

// We choose " now on a site "

$r = mysql_query (" SELECT count (DISTINCT hid) as online

FROM cns_log

WHERE date> NOW () - INTERVAL 3 MINUTE; ");

$text = mysql_result ($r, 0,0);

@mysql_free_result ($r);

// We deduce{remove} " now on a site "

ImageString ($im, 1,80-5*strlen ($text), 4, $text, $red);

}

Header (" Content-type: image/png ");

ImagePng ($im);

ImageDestroy ($im);

The counter ¹3 - use True-Type of fonts at generation of counters

Unfortunately, that support TrueType of fonts is established far from being on all servers, such counter is not included in the distribution kit. It is a pity, very beautiful counter

On this counter, for a change, we have deduced{removed} is hit today, hosts today and users today.

error_reporting (E_ALL and ~E_NOTICE);

include "config.php";

// This function allows to define{determine} length of a line in pixels

function width ($text) {

$box=imagettfbbox (7, 0, FONT_TAHOMA, $text);

return ($box [2] - $box [1]);

}

// A font which we use for a conclusion of the counter.

// It is possible to take from C:WindowsFonts

define (' FONT_TAHOMA ',' tahoma.ttf ');

// Attention! The image should be True-Color (24 bat)

$im=ImageCreateFromPng ("button-tt.png");

$CONN = mysql_connect ($STATS_CONF ["sqlhost"],

$STATS_CONF ["sqluser"],

$STATS_CONF ["sqlpassword"]);

if (mysql_errno () == 0) {

@mysql_select_db ($STATS_CONF ["dbname"]);

if (mysql_errno () == 0) {

// We choose the data

// hits - it is hit today

// hosts - hosts today

// users - users today

$r = mysql_query (" SELECT hits, hosts, users FROM cns_counter ");

// It is transferable{tolerable} the unique chosen recording in an associative file

$res = mysql_fetch_array ($r, MYSQL_ASSOC);

// We release{exempt} memory

@mysql_free_result ($r);

// We deduce{remove} the text

$text = $res ["hits"];

Imagettftext ($im, 7, 0, 4, 10, 0x03314A, FONT_TAHOMA, $text);

$text = $res ["hosts"];

Imagettftext ($im, 7, 0, 4, 19, 0x03314A, FONT_TAHOMA, $text);

$text = $res ["users"];

Imagettftext ($im, 7, 0, 4, 28, 0x03814A, FONT_TAHOMA, $text);

$r=mysql_query (" SELECT count (DISTINCT hid) as online

FROM cns_log

WHERE date> NOW () - INTERVAL 10 MINUTE; ");

$text = mysql_result ($r, 0,0);

Imagettftext ($im, 7, 0, 80-width ($text), 12, 0xC0314A, FONT_TAHOMA, $text);

}

ImagePng ($im);

ImageDestroy ($im);

The counter ¹4 - is hit everything, the percent{interest} of robots is hit today, users today,

In this example, besides robots we shall a little change appearance of the counter - we shall add shadows, and with the help of spaces we shall make numbers more legible.

error_reporting (E_ALL and ~E_NOTICE);

include "config.php";

// This function allows to define{determine} length of a line in pixels

function width ($text) {

$box=imagettfbbox (7, 0, FONT_TAHOMA, $text);

return ($box [2] - $box [1]);

}

// A font which we use for a conclusion of the counter.

// It is possible to take from C:WindowsFonts

define (' FONT_TAHOMA ',' tahoma.ttf ');

// Attention! The image should be True-Color (24 bat)

$im=ImageCreateFromPng ("button-tt.png");

$CONN = mysql_connect ($STATS_CONF ["sqlhost"],

$STATS_CONF ["sqluser"],

$STATS_CONF ["sqlpassword"]);

if (mysql_errno () == 0) {

@mysql_select_db ($STATS_CONF ["dbname"]);

if (mysql_errno () == 0) {

// We choose the data

// t_hits - it is hit all

// hits - it is hit today

// users - users today

// u_hits - it is hit from visitors

$r = mysql_query (" SELECT t_hits, hits, users, u_hits FROM cns_counter ");

// It is transferable{tolerable} the unique chosen recording in an associative file

$res = mysql_fetch_array ($r, MYSQL_ASSOC);

// We release{exempt} memory

@mysql_free_result ($r);

// We deduce{remove} the text

$text = number_format ($res ["t_hits"], 0 ",", " ");

Imagettftext ($im, 7, 0, 5, 11, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 4, 10, 0x03314A, FONT_TAHOMA, $text);

$text = number_format ($res ["hits"], 0 ",", " ");

Imagettftext ($im, 7, 0, 5, 20, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 4, 19, 0x04476C, FONT_TAHOMA, $text);

$text = number_format ($res ["users"], 0 ",", " ");

Imagettftext ($im, 7, 0, 5, 29, 0xcccccc, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 4, 28, 0x04876C, FONT_TAHOMA, $text);

// We consider percentage of robots

if ($res ["hits"]! =0)

$text = intval ((1-$ res ["u_hits"] / $res ["hits"]) *100). "%";

else

$text = "0";

// We deduce{remove} the text

Imagettftext ($im, 7, 0, 80-width ($text), 12, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 79-width ($text), 11, 0x03804A, FONT_TAHOMA, $text);

}

ImagePng ($im);

ImageDestroy ($im);

The counter ¹5 - all together

This big counter is the sum of all previous.

error_reporting (E_ALL and ~E_NOTICE);

include "config.php";

// This function allows to define{determine} length of a line in pixels

function width ($text) {

$box=imagettfbbox (7, 0, FONT_TAHOMA, $text);

return ($box [2] - $box [1]);

}

// A font which we use for a conclusion of the counter.

// It is possible to take from C:WindowsFonts

define (' FONT_TAHOMA ',' tahoma.ttf ');

// Attention! The image should be True-Color (24 bat)

$im=ImageCreateFromPng ("button-big.png");

$CONN = mysql_connect ($STATS_CONF ["sqlhost"],

$STATS_CONF ["sqluser"],

$STATS_CONFs ["sqlpassword"]);

if (mysql_errno () == 0) {

@mysql_select_db ($STATS_CONF ["dbname"]);

if (mysql_errno () == 0) {

$r = mysql_query (" SELECT t_hits, t_hosts, t_users, hits, hosts,

users, u_hits FROM cns_counter ");

$res = mysql_fetch_array ($r, MYSQL_ASSOC);

@mysql_free_result ($r);

$text = number_format ($res ["t_hits"], 0 ",", " ");

Imagettftext ($im, 7, 0, 86-width ($text), 11, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 10, 0x03314A, FONT_TAHOMA, $text);

$text = number_format ($res ["hits"], 0 ",", " ");

Imagettftext ($im, 7, 0, 86-width ($text), 20, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 19, 0x0331FF, FONT_TAHOMA, $text);

$text = number_format ($res ["t_hosts"], 0 ",", " ");

Imagettftext ($im, 7, 0, 86-width ($text), 31, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 30, 0x03314A, FONT_TAHOMA, $text);

$text = number_format ($res ["hosts"], 0 ",", " ");

Imagettftext ($im, 7, 0, 86-width ($text), 40, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 39, 0x0331FF, FONT_TAHOMA, $text);

$text = number_format ($res ["t_users"], 0 ",", " ");

Imagettftext ($im, 7, 0, 86-width ($text), 51, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 50, 0x03314A, FONT_TAHOMA, $text);

$text = number_format ($res ["users"], 0 ",", " ");

Imagettftext ($im, 7, 0, 86-width ($text), 60, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 59, 0x0331FF, FONT_TAHOMA, $text);

$r=mysql_query (" SELECT count (DISTINCT hid) as online

FROM cns_log

WHERE date> NOW () - INTERVAL 3 MINUTE; ");

$text = mysql_result ($r, 0,0);

Imagettftext ($im, 7, 0, 86-width ($text), 70, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 69, 0xC0314A, FONT_TAHOMA, $text);

$text = sprintf (" % .2f %% ", (1-$ res ["u_hits"] / $res ["hits"]) *100);

Imagettftext ($im, 7, 0, 86-width ($text), 82, 0xCCCCCC, FONT_TAHOMA, $text);

Imagettftext ($im, 7, 0, 85-width ($text), 81, 0x03804A, FONT_TAHOMA, $text);

}

ImagePng ($im);

ImageDestroy ($im);