Import and export data in databases to XML files

This class is meant to import and export data stored in SQL databases to XML files using the ADODB library.

It provides a function to export the data of SQL query result set to XML file.

It also provides another function that parses XML files that define the data of fields to be inserted as new rows of a table also specified in the XML files.

You can download these files (the class and the examples) from phpclass.org or here (8kb)

Optimize All Tables In A MySQL Database

If you have a database driven site and you want to optimize MySQL tables then this is perfect. It goes through all the tables in a MySQL database and does table optimization on each one using the MySQL Optimize Table syntax.

<?php 

dbConnect() 

$alltables = mysql_query("SHOW TABLES"); 

while ($table = mysql_fetch_assoc($alltables)) 

{ 

   foreach ($table as $db => $tablename) 

   { 

       mysql_query("OPTIMIZE TABLE '".$tablename."'") 

           or die(mysql_error()); 

   }     

} 

?>

Sam Pagination

This class is supplied by Olowoyo Samuel and can be used to display database query results split in multiple pages.

It can perform queries to a MySQL database table and retrieve a range of results to display in the current page given the page number and the limit of results to display per page.

The class can display the results as a listing of articles or as photo gallery.

You can download it here (303kb including examples)

WebSite Backup + Database mySQL Backup

These classes are made by ThoR® aka Kender functioning to back up files and MySQL database. You can download it here (6kb).

Below are the instructions of use and sample of how to use it:

INSTRUCTIONS

/********************************

BACKUP OF FILES

*********************************/

$mkBackup = new BackUp;

this initialize the class

$mkBackup->WhatBackup(PATH_TO_SAVE);

this set which folder will be compressed

Relative path. Full path should work. No final "/"

$mkBackup->WhereBackup(MOVE_COMPRESSED_FOLDER_HERE);

this set in which folder the compressed archive will be saved

Relative path. Full path should work. No final "/"

$mkBackup->FileName(NAME_OF_ARCHIVE);

this set the name to give to archive

you can create name on-the-fly, using date(); or whatever you want

$mkBackup->Debug();

if you want to display all the infos about compression

This will display all vars set before: What Backup, Where, and name of archive

Nothing more...

/********************************

BACKUP OF DATABASE

Stored Procedure Class For MySQL

Stored Procedure is a set of some sql statements which are stored in database server and can be executed anytime we call them.

As MySQL older than version 5 doesn't support the stored procedure functionatlity, then we have to use ST_Proc class. It can be downloaded from phpclasses.org or from here (3 kb including an example).

In this package there should be 2 main files, namely, sqlLayer.php and STProc.php. 

For practicing you should prepare a database named test with a table named test as well (just for dummy). Then create 2 fields,  id int (11) and text varchar (20). Insert some dummy data on the table.

Then create stored procedure and save it (just like in the example default.sp in the package), and create a file to call and execute it (sp.php in the example file). Execute this file to see the results.

Delete multiple records

Sometimes we may want to delete multiple records at once for more efficient work. We should provide checkbox for each records and users can select which records to delete.

In this example we should first create a database, a table and insert some data.

Type this sql script:

create database delsampel ;

use delsampel;

create table tabledelete (id int(5) primary key auto_increment, name varchar(50), position varchar(20), level int(2));

insert into tabledelete values('','Janet Jackson','Admin 2','8');

insert into tabledelete values('','Tommy Leo','Admin 1','5');

insert into tabledelete values('','John Connery','Admin 3','4');

then we should create 2 files

1. view.php

2. delete.php

below is the script for both files:

<?php

/*  view.php */

$host = "localhost";

$username = "root";

$password = "";

$database = "delsampel";

$connection = mysql_connect($host, $username, $password);

mysql_select_db($database, $connection) or die("MysQL Error");

$sql = mysql_query("select * from tabledelete");

?>

<html><body>

<form action="delete.php" method="POST">

<?php

echo "<table border=1>";

echo "<tr><th> </th><th>name</th><th>position</th><th>level</th></tr>";

while ($result = mysql_fetch_array($sql))

{

echo "<tr><td><input type=checkbox name=del[] id=del value=$result[id]></td><td>$result[name] </td><td> $result[position] </td><td> $result[level]</td></tr>";

}

?>

</table>

<input type="submit" name="justdel" value="Delete !!" id="justdel">

</form>

</body></html>

delete.php

<?php

/* delete.php */

$host = "localhost";

$username = "root";

$password = "";

$database = "delsampel";

$connection = mysql_connect($host, $username, $password);

mysql_select_db($database, $connection) or die("MysQL Error");

$id = $_POST[del];

$count = count($id); //counting how many rows (from checkbox) to delete

if($_POST['justdel'])

{

for ($i=0; $i<$count; $i++)

{

$sql = mysql_query("delete from tabledelete where id=$id[$i]");

}

if ($sql)

{

print "Record successfully deleted<br>";

print "<a href=view.php>Click here to go back</a>";

}

}

?>

those example files can be downloaded here!! 2kb

Counter and Hits With MySQL

Below is the script on how to make visitor's counter based on Mysql database

create table on your database:

CREATE TABLE `counter` ( 

   `count_id` varchar (225) NOT NULL default'', 

   `count` longtext NOT NULL, 

   `hits` longtext NOT NULL 

) TYPE = MyISAM; 

notes: 

for initial value for:

"count_id" enter the value 1 

"count"  enter the value 1 

"hits" enter the value 1 

below is the script for counter: 

<?php

session_start();

/* Database connection ----- begin ------- */

$dbhost = 'localhost'; /* your host ----- ------- */

$dbusername = 'username'; /* your user ----- ------- */

$dbpasswd = 'password'; /* your password ----- ------- */

$database_name = 'database'; /* your database name ----- ------- */

/* ----- Start db connection, code below do not change in ------- */

$connection = mysql_pconnect("$dbhost", "$dbusername", "$dbpasswd") or die ( "Could not connect to server.");

$db = mysql_select_db("$database_name", $connection) or die ( "Could not select database.");

/* Database connection out ----- ------- */

//  ----- If session "counted" has not been listed ------- *

if (!session_is_registered("counted")) {

/* ----- Update the cell "count" to add a +1 ------- */

mysql_query("UPDATE counter SET count = (count + 1) WHERE count_id = 1");

/* ----- Register session "counted" ------- */

session_register("counted");

}

?>

//script for visitor:

/* ----- Show value / value from the table "counter" column to the 2-I mean the "count" ------- */

$sql = mysql_query ("SELECT * FROM counter limit 1");

while ($row = mysql_fetch_array ($sql)) {

echo $row [1];

}

?>

script for hits:

<?php

/* ----- For the hits, we do not need the function "IF" ------- */

// Update cell ----- "hits" to add a +1 ------- */

mysql_query("UPDATE counter SET hits = (hits + 1) WHERE count_id = 1");

/* ----- Show value / value from the table "counter" column to the 3-I mean the "hits" ------- */

/* ----- Who has been in the process of update of the above ------- */

$sql = mysql_query ("SELECT * FROM counter limit 1");

while ($row = mysql_fetch_array($sql)) {

echo $row [2];

}

?>