CVS For Databases?

Php, Databases, And Ssl
I am having issues with my implementing an ssl page to secure my site. My database access is password protected, but the issue is the actual transmission of the data from the login.php page to the database. A user was concerned about the security of this aspect, which is understandable.

What I want to do is find a way to have the query of the database take place behind a ssl layer, and once that is accomplished, leave that area. Quite simply, after login, all I do is access their name to greet them, and use their username as a session variable. I use Yahoo web hosting, and they have a ssl folder to use to get security. I tinkered with things, but couldn't figure out what links needed to be changed, nor how to properly direct the changes.

I was able to get it to work somewhat, but once I tried to login, I kept getting in a loop that reloaded the login page.

I hope this is enough information, although I have a premonition that it is difficult to answer this query without access to the code.

---

Two Databases
My query takes data from two seperate databases on the same MySQL server. The only problem is, each database has it's a username prefix. For example:

"user1_artists"
"user2_store"

This is because each database is on a seperate domain account (they both use the same MySQL username and password though). The code below works on my computer at home, but when I run it on the server it doesn't work. The reason why it works on my home computer is because the databases don't have user prefixes like my server does. Any ideas? PHP Code:

---

Addslashes() In Databases
Is that true, that when i want to store data into a database it's better to use addslashes($var)

and when i want to retrive the data from the database it's better to stripslashes($var).

---

Huge Databases
Say I have a remote site, how would I go about "reuploading" a 2gb database that was crashed. I had to delete one as the records were skewed, and I have a backup on my computer. But doing like 15 million queries, that doesn't sounds like the best way to have this database back up and running.

---

Closing Databases
I am using mysql_connect($host,$user,$db_password); to open a mySQL database. Do I need to be closing the database manually after working with it on the page or does php automatically take care of this ?

---

Two Classes - Two Databases
I have two functions which make a new class and each a connection to a different database.
PHP Code:

---

PHP & Paradox Databases?
I've been experimenting with PHP & MySQL-databases for a while now, but now I'm trying to use existing Paradox databases. The problem is I don't know how to connect to it (I read something about ODBC but I don't really get it..)

---

Databases And Classes
I wrote this as a test to see how classes work with database connections. The first part of feedback (PART A) works, and it cycles through my list of users and displays them no problem, but it doesn't move onto the second loop. I expect it's something to do with the 'while' statement. Can anyone see what i'm doing wrong? PHP Code:

---

Can't Create Databases
In the code below, the mysql_connect commands work correctly (though
I've changed the passwords here). I get the "We were able to
establish a connection" message. But then the code hangs and does
nothing. What am I doing wrong? The username and password are root, so
I should have the right permissions. The same username and password,
when I put them into phpMyAdmin, allow me to create databases without
a trouble.












$link = mysql_connect('localhost', 'u345', ��');
if ($link) {
echo "<p>We were able to establish a connection to the
database.</p>";
} else {
die('Could not connect: ' . mysql_error());
}


$result = mysql_create_db($website);
if ($results) {
echo "The database '$website' was created.";
} else {
die("There was a problem. We were not able to create the database.
It is possisble that a database of the same name already existed, or
perhaps someone recently changed the configuration of the database
server. YOu might want to contact whoever is in charge of the database
and ask them what is up :". mysql_error());
}

---

Preg_replace(); And Databases
I'm having trouble using the following function. When ever i wish to update fields in my database it automatically escapes the quotes(Which i do not want it to do) - So i've tried using regex to replace the " with a standalone quote ".

Update once:

<div id="example">Example</div>

Update twice:

<div id="example">Example</div>

The preg_replace(); function isn't working. So i'm not sure if its my code or not. (I also do not know the hex for the backslash key)

case "validate":
@include("validate.php");

$title = $_POST['title'];
$description = $_POST['description'];
$keywords = $_POST['keywords'];
$footer = $_POST['footer'];
$header = $_POST['header'];

$sql = sprintf('UPDATE `site_options` SET title = '%s', description = '%s', keywords = '%s', footer = '%s', header = '%s'',
    mysql_real_escape_string($title).preg_replace(x92x22/g, '"'),
    mysql_real_escape_string($description).preg_replace(x92x22/g, '"'),
mysql_real_escape_string($keywords).preg_replace(x92x22/g, '"'),
mysql_real_escape_string($footer).preg_replace(x92x22/g, '"'),
mysql_real_escape_string($header).preg_replace(x92x22/g, '"')
);

if(mysql_query($sql))
{
echo 'Site Information Updated';
}
else
{
die("Error: " . mysql_error());
}

break;

---

Which Databases Are Supported By PHP ?
I would like to make a study about PHP with databases, can you provide
me a list of databases PHP supports,

---

Can I Connect To Two MySQL Databases At Once?
Hi!

Quick question, there wouldn't be any conflicting issues if I connect to two databases at once? Just as long as I use different variables, and field names, right? Thanks lots.

---

Databases And Complex Forms
Got a question for y'all regarding the best way to maintain HUGE forms and
linking them to data tables in MySQL.

I have a set of forms that were created specifically for a Loan Services
Company (ranging from request forms to complex inspection forms with
hundreds of input fields (such as room size, item quantity, item quality and
costs) the problem I had in the initial setup was how to create the
associated data tables for each type of form. I opted to store ONLY the
client information, type of report and a few other miscellaneous items in a
table called reports and the rest of the data is dumped into an INI format
file using a php class that reads and writes ini files.

This current setup works, but not all the fields from the reports are
searchable. Additionally, going this route I had to modify all the forms and
create the associated report format (no form fields, just echoing the
correct data back where needed) and this is not user friendly enough (as if
the forms change, then the report format must change as well). Does anyone
have any recommendations on how best to modify this to use MySQL tables to
store the data without overloading the database? Or am I going about this
the wrong way?

---

PHP & MySQL - Connecting Two Databases
I am trying to understand the best way to extract a list of users from a
table based upon their location.

To do this I have a table containing all the UK postcodes with a grid
reference x & y.
postcode x y
AB10 392900 804900
AB11 394500 805300
AB12 393300 801100
AB13 385600 801900
AB14 383600 801100
AB15 390000 805300
AB16 390600 807800
AB21 387900 813200
AB22 392800 810700
AB23 394700 813500
AB25 393200 806900
AB30 370900 772900

In a seperate table I have a list of users including a postcode.
id firstname postcode
1 Heather BH4
2 Vicky OL1
3 Paola CF8
4 Joanna W14
5 Steve BD13
6 Sally NN1

In short I would like your opinoin on how best (most efficiently) to
calculate the nearest 20 users. The method I am using to calculate the
distance between the users and any chosen postcode is good old Pythagoras
dist = sq root ( (userX * userX) + (userY * userY) - (locationX * locationX)
+ (locationY * locationY) )

It seems that looping through the users to first calculate the distance is
essential. After that point I am not sure about the besr way to proceed.
Do I write the results to a temp table (with distances) then call them in
order of distance or is there a better way?

---

Query Over Severall Databases
I have a 'small' problem. I have made a application in php that connect
trough ODBC to a Oracle RDB (VMS) or Basis+ database (VMS) and present the
data to a user. There are 2 Oracle databases in diffrent city's and 5 Basis+
databases allso in diffrent city's. Now we have to logon to each database
and can ask a question. My goal is now that on one question all databases
give, when present, all its data.

---

Sample Databases For Practicing
I'm currently teaching a course in web programming where we're using PHP to
connect to MySQL. A book we're using has a sample database in it for use in
practicing, but the database is not well populated with data. This works
fine for some applications, but I'd really like the students to experience
working with a variety of database structures and a larger volume of table
data.

Can anyone suggest a source for "sample databases"? The format isn't
critical, since I can probably find a filter to help me get it into MySQL.
My searches thus far on the web haven't been very productive.

---

Speedy Code? (with 2 Databases)
I cannot share the code, but maybe someone can gibve me an idea...
I have to lookup something in a database (updates from specific time,
MS SQL Server), and check them in another database (Oracle). Both might
contain multiple rows, which makes this big...
The point is, that the code below runs at app 10 rows/second, which is
slow... any ideas how I can do it better?

The slow thing is that for each row in DB1, I run a query in DB2.

The code is basically this - there is some more, but

// get data from MS SQL
$result2=odbc_exec($conn2, $sql);

while(odbc_fetch_row($result2) )
{
// test for this is Oracle DB (here might be multiple rows too)
$sql2= select....
$result=odbc_exec($conn, $sql2);

// if row exist
while(odbc_fetch_row($result))
//if(odbc_num_rows($result)>1)
{
// add to array - for next step to search - do not add twice
$k=count($part1)-1;
while(($k>-1) && ($part1[$k]!=odbc_result($result2,1)) &&
($part2[$k]!=odbc_result($result2,2)) &&
$part8[$k]!=trim(odbc_result($result,1)))
$k--;
if($k==-1)
{
$part1[]=trim(odbc_result($result2,1));
$part2[]=trim(odbc_result($result2,2));
$part3[]=trim(odbc_result($result2,3));
$part4[]

---

RFD: Comp.databases.mysql
REQUEST FOR DISCUSSION (RFD)
unmoderated group comp.databases.mysql

Newsgroup line:
comp.databases.mysqlMySQL relational database system discussion group.

This is a formal Request For Discussion (RFD) for the creation of a
world-wide unmoderated Usenet newsgroup comp.databases.mysql.
This is not a Call for Votes (CFV); you cannot vote at this time.
Procedural details are below.

RATIONALE: comp.databases.mysql

MySQL is a relational database server that is finding increasingly
wide acceptance as a backend solution for developing data-driven
Web sites and applications. MySQL is a free, open-source, professional
quality database system similar to Microsoft SQL Server and Oracle.
MySQL is a more powerful alternative to Microsoft Access databases,
but without the expensive costs of other database servers. There
is no newsgroup for MySQL, so this is the time to create one for
the rapidly growing user base.

CHARTER: comp.databases.mysql

This newsgroup comp.databases.mysql is a Big Eight Usenet discussion
group for the MySQL relational database system. Any range of topics
that pertain to MySQL may be discussed in this newsgroup.

Do not post any promotional material of any kind to this newsgroup.

Posting of binaries is not permitted, with the exception of small
digital signatures such as PGP.

END CHARTER.

PROCEDURE:
This is a request for discussion not a call for votes. In this phase
of the process, any potential problems with the proposed newsgroups
should be raised and resolved. The discussion period will continue
for a minimum of 21 days (starting from when the first RFD for this
proposal is posted to news.announce.newgroups), after which a Call
For Votes (CFV) may be posted by a neutral vote taker if the
discussion warrants it. Please do not attempt to vote until this
happens.

All discussion of this proposal should be posted to news.groups.

This RFD attempts to comply fully with the Usenet newsgroup creation
guidelines outlined in "How to Create a New Usenet Newsgroup" and
"How to Format and Submit a New Group Proposal". Please refer to
these documents (available in news.announce.newgroups) if you have
any questions about the process.

DISTRIBUTION:

news.announce.newgroups
news.groups
comp.databases
comp.lang.php
alt.php

---

Transfer Between 2 Mysql Databases
I wanted to know how one can transfer between two my sql databases on the same machine using PHP . The updated table in one of the database is to be transferred to an identical table in the other database.

---

Images, Databases And Dimensions
I have designed an application that allows the user to submit images to be displayed on a web page. I am storing these images on a MySQL database and can limit the file size the user uploads. My problem is the image dimensions. I want the images to fit nicely into a table so image width becomes an issue.

I know it is possible to resize an image with the getimagesize($filename) function, but this only seems to work with a file. I am having trouble reading the dimensions from the image data that is stored in the database. Is this possible or should I rethink things and have the user store the images in a directory? PHP Code:

---

2nd RFD: Comp.databases.mysql
REQUEST FOR DISCUSSION (RFD)
unmoderated group comp.databases.mysql

This is an invitation to discuss the following proposal to create newsgroup
comp.databases.mysql. Please note that YOU CANNOT VOTE NOW; you may be
able to vote on a version of this proposal later. See the PROCEDURE section
below if you need information about how the discussion works.

PLEASE POST ANY FOLLOWUPS TO THE NEWSGROUP NEWS.GROUPS.

Newsgroup line:
comp.databases.mysqlMySQL RDBMS technical discussions.

CHANGES from previous RFD:

Minor changes in syntax have been made to the newsgroup line, rationale, and
charter sections of this proposal.

RATIONALE: comp.databases.mysql

MySQL is a relational database server that is finding increasingly wide
acceptance as a back end solution for developing data-driven web sites and
applications.

There is no dedicated newsgroup for MySQL in the comp.* hierarchy and, as a
result, most of the discussions of MySQL take place in the comp.lang.php
newsgroup. This is unsatisfactory because MySQL is not in any intrinsic way
related to the PHP language, and users of MySQL who do not use it in
conjunction with PHP front ends are unlikely to know to look for discussions
of MySQL there. In addition, discussion of issues related to MySQL but not
to PHP are off-topic for comp.lang.php. A new group specifically for MySQL
would offer a place where all users of MySQL could participate without
risking being off-topic for the group and where all users of MySQL could
easily discover the group.

CHARTER: comp.databases.mysql

This newsgroup comp.databases.mysql is a forum for technical discussion of
the MySQL relational database management system. All topics that pertain to
using, learning, developing, maintaining or improving MySQL are on-topic in
this newsgroup.

Posting of advertising and/or promotional material of any kind to
comp.databases.mysql is explicitly prohibited. Posting of binaries is not
permitted, with the exception of small binary components in otherwise
non-binary postings, such as PGP signatures or X-Face headers. Posts must
be readable as plain text. HTML, RTF and similarly formatted messages are
prohibited

END CHARTER.

PROCEDURE

When an RFD appears, it begins a process called the newsgroup creation
process. The first stage of that process is the discussion period, and
that's the stage that's now in effect.

During the discussion period, the object is indeed to discuss whether this
proposal should be enacted, and to discuss any potential problems with it
and how to resolve them. Because the RFD has a Followup-To header pointing
to news.groups, discussion will be posted there. Therefore, if you want to
be heard in the discussion, you should post your own posts in news.groups
too. Similarly, if you want to read the discussion, you should be prepared
to read it in news.groups.

During the discussion period, the proponents may issue new drafts of the
proposal, perhaps with major changes: "2nd RFD", "3rd RFD", etc. Please do
not assume that these will be the same as this draft; please make a point of
reading each, if you wish to avoid surprises.

Following the discussion period, there may be a second stage, called a
voting period. This cannot begin until 21 days after the first RFD, *and*
10 days after the last RFD. The proponents decide whether to ask for a
voting period. If a voting period is requested by the proponents, then a
neutral vote taker will post a Call For Votes, or CFV, whose appearance
begins the voting period. The CFV will contain instructions on how to go
about voting. You cannot vote until the CFV appears. If you want to be
sure of seeing any CFV, but you don't want to read the discussion, you may
wish to subscribe to news.announce.newgroups, a low-volume group consisting
mostly of RFDs, CFVs, and related posts.

For further information about this process, please see "Guidelines for Big
Eight Newsgroup Creation" in news.announce.newgroups. This post tries to
obey all guidelines in that post and in "How to Format and Submit a New
Group Proposal". Please refer to these documents if you have further
questions.

DISTRIBUTION:

The RFD has been posted directly to the following newsgroups:

news.announce.newgroups
news.groups
comp.lang.php
alt.php.sql
alt.php

---

Uploading Files To Databases
I am trying to upload photos to a mysql databse but with no luck. I'm not getting any error messages, the problem is that nothing appears in the database. Code:

---

List Info In The Databases
im trying to list every username in my database i know that you have to take out the where un the mysql query any way here what i got Code:

---

Php File To Work 2 Databases.
I have a form that asks for a number, when the user types in the number it enters that number into a database. I have a different database that has every number that a user will enter inside it along with names that correspond to that number. I would like for the php file that enters the number in the database to look for the name that goes with it and enter it in.

---

Send Information To Different Databases
I am trying to post some info from a form, and into two databases. I have one of my mysql connections in a config file, and the other one in the file I am posting from. It is something like this:

<?php
include(config.php);
 //send something to the first database
mysql_query("INSERT INTO ....");

// And connect to the other db:
$xconnect = mysql_connect("x", "x", "x");
mysql_select_db("x", $xconnect);

// Then send info to the second db:
mysql_query("INSERT INTO ....");
?>

Is this a good way to do it? I don't feel that it works correct, I get some errors and such.

---

Data Encryption Via PHP For MySQL Databases
Just wondering what type of encryption is the preferred type. I am creating a login section for a site and need to encrypt username / passwords in a MySQL database, and was wondering how to go about using PHP to insert and encrypt this data. Any Ideas?

---

Converting Access Databases To MySQL
how to take an existing Microsoft Access database and transfer the records to an MySQL database. Preferably, I'm looking to write a script that will automatically take the Acces compatible file and write itinto the MySQL tables when the Access file is uploaded to the server...any suggestions?

---

Accessing Remote MySQL Databases
How do I access data from a database located in a server somewhere with an
application running on a local machine?

---

Trouble With Multiple Mysql Databases
I'm having trouble accessing multiple databases using a single user.

Basically, I have 2 databases, db1 and db2 and 2 users, user1 and user2.
Originally these were setup so that user1 would only access db1 and user2
would only access db2. However, now I want user1 to access both db1 & db2.

I've used cPanel to declare user1 as a user of db2. However, whenever I try
to access db2 from user1, I get permission problems.

I've tried various combinations of mysql_db_select(), mysql_query("USE
db2Name", $db1link) or just referencing the appropriate table as
'db2Name.tableName' in my SELECT querys but everytime I get a permission
denied response.

Any ideas?. It looks like a cPanel problem to me as it appears that user1 is
simply not being granted access to db2 despite telling cPanel to do so.

---

Connecting To Two Mysql Databases Simultaneously
is it possible to have two open connections to two different mysql dbs at the same time?  when i tried it, only the one on the bottom of the list was active. my config file looks like this: Code:

---

Connecting Two Mysql Tables From Different Databases
How would you connect two tables from 2 different databases using PHP scripts? Because, one database that contains customer table is dedicated to one project, and then here comes another project that also uses the customer table...the clients dont want to reenter all of values from the first database to the second database. Dumping would not be an option cause they want the two projects to run in different databases... and whenever the user made changes to the customer table from the first database, it should also affect the customer table from the second database.

---

Storing Credit Card In Databases?
What is the deal with storing credit card information in a database

Can anyone give me some in sight on this.

1.) What is the best way to go about it?
2.) What is use to encrypt and decrypt the info?

And anything else that you may think is important on this issue.

---

Merging Results From Two Mysql Databases
I have made two websites on seperate servers both with a database of their own.
They are identical but with different results in the databases. I want to make a page on one of the sites that will merge the information from both databases.

e.g.
site 1 has a list in date order
site 2 has a list in date order

I want site 1 to have the list merged together to form one big list but still be in date order. Also I want to be able to have the results of site 2 in a different font colour to site 1.

i.e.
Site 1 results

10th jan - something
15th jan - something else
19th jan - more

Site 2 results

11th jan - even more
13th jan - some more
16th jan - more stuff

Merged page

10th jan - something
11th jan - even more
13th jan - some more
15th jan - something else
16th jan - more stuff
19th jan - more

can anyone help with this?

---

Connect To Two Separate Databases, And Create A Query ..
Can someone show me how I might be able to connect to two separate
databases, and create a query from the two? I understand in Oracle this is
referred to as "database links" but is there a more general way to do this
(i.e. will work with, say, mysql and other DBs?) I want to comapre a field
that is present in two separate files on two seperat databases.

---

PHP Code/libraries For Creating MySQL Databases
I am working on a registration system, and one of the things I am going to
need to be able to do it setup the database tables for each event that I
have registration for. Normally I would do this by hand, but I am wondering
if there are any code libraries out there that could generate the SQL for
me.

Basically I would have a list of column names and types. I know I could
just foreach() through the list, with a switch to divert control to the
different code segments depending on the column type, and at the end I would
have the SQL generated.

This seems like the sort of thing that somebody might have done before and
released a code snippet for. Maybe in PEAR DB? Any ideas?

---

Optimising MySQL Queries Against Huge Databases?
I have a database of movie titles, with about 78,000 records, and a
database of related people (directors, writers, actors/actresses etc.)
with about 141,000 records. I display a random movie out of this
database on each hit to my website's homepage.

This worked fine when I had only a couple thousand movies, but now that
the DB has grown, it seems to be taking a bit longer to process the
page.

My DB schema for each table:

CREATE TABLE `movies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(200) NOT NULL default '',
`uri` varchar(100) NOT NULL default '',
`year` year(4) NOT NULL default &#55612;&#57200;',
`released` date NOT NULL default &#55612;&#57200;-00-00',
`imdb` int(11) NOT NULL default &#390;',
`allmovies` varchar(25) NOT NULL default '',
`length` int(11) NOT NULL default &#390;',
`colour` enum('c','b') NOT NULL default 'c',
`sound` varchar(25) NOT NULL default '',
`director` int(11) NOT NULL default &#390;',
`writer` int(11) NOT NULL default &#390;',
`asin` varchar(25) NOT NULL default '',
`image` blob NOT NULL,
`genre` varchar(25) NOT NULL default '',
`fatso` int(11) NOT NULL default &#390;',
PRIMARY KEY (`id`),
UNIQUE KEY `imdb` (`imdb`),
KEY `name` (`name`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=78483 ;

CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`born` date NOT NULL default &#55612;&#57200;-00-00',
`died` date NOT NULL default &#55612;&#57200;-00-00',
`imdb` int(11) NOT NULL default &#390;',
`allmusic` varchar(25) NOT NULL default '',
`allmovies` varchar(25) NOT NULL default '',
`uri` varchar(100) NOT NULL default '',
`image` blob NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `imdb` (`imdb`),
KEY `name` (`name`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=141623 ;

The SQL query I am using to fetch a random movie is:

SELECT movies.id, movies.name, movies.asin, movies.fatso, people.id AS
directorid, people.name AS director
FROM movies, people
WHERE movies.image<>'' AND people.id=movies.director
ORDER BY RAND() LIMIT 1;

---

Naming Conventions In Php Referring To Mysql Databases
I am developing on a windows machine, and hosting on a linux server.
I have written my php code using table names like siteStats and column
names like userStatus. I have just realized the conflict this creates
when uploading to the live server; table siteStats is not recognized,
but table sitestats is recognized. The solution seems to be:

1 - change all table names (and other case-sensitive names) to an all-
lowercase convention, i.e. siteStats =sitestats or site_stats;
2 - change table names on live version, in the database itself, to
lowerUpper format.

I really like using the lowerUpper format because I find it annoying
to type underscores all the time, and lowerlower becomes hard to
read. However, it is more important to me that my live and local
scripts are identical.

Does anyone else use the lowerUpper format successfully for things
like mySql table names, when developing on windows and hosting on
linux?

---

Looking For Independent Database Class That Supports All Major Databases
I guess the title says all.

I'm looking for independent database class that supports all major databases.

I ran across this in the past, but now that I need it I can't find it. It is a class that allows a programmer to select which database the program will access. It is built so that a script can be portable without write to database functions.

---

Problems Integrating Two User Databases And Session Systems
I've been working with a client adding features to his website. Over the past few months, we've added a registration system and an array of membership features. His forum, which is running phpBB, is running on a separate database.

Here's the scenario:

Main site - "subscribedUsers" table contains all user information -- uses sessions/cookies system
Forum - "phpbb_users" table contains all user information -- uses sessions/cookies system

As a quick and dirty solution, we have phpBB create a new forum user and associate the forum user_id with the site user_id in a forum_id field.

In the header of the page, we're including /phpBB2/common.php and defining IN_PHPBB. We use the session_pagestart() and init_userprefs() functions. On login, we use the same process that phpBB does to login the forum user at the same time.

We run into problems in a few places. I'll outline them here:

- When you go from site -> forum -> site, the session is lost. However, the forum user remains logged in.
- When you try to log out from the forum, it doesn't budge.
- The append_sid() function stops working in both the site and forum.

Ideally, we want to create a system in which a user logs in from the main website, and is seamlessly logged into the forum using their forum user_id. We want the session to remain constant at all times, even when going from site -> forum -> site. Finally, we want the logout in the forum to work as usual.

---

10 Speed Test For Popular Databases By Justin Silverton
Written by : Justin Silverton
Source : http://blinduser.blogspot.com/

I came across the following 10 benchmark tests covering:

SQLite version 3.3.3
SQLite version 3.3.3
SQLite version 2.8.17
SQLite version 2.8.17
PostgreSQL version 8.1.2
MySQL version 5.0.18
FirebirdSQL version 1.5.2

About the hardware/database settings used:

All databases were installed with default settings.
Tests were run on 1.6GHz Sempron with 1GB of ram and 7200rpm SATA disk
running Windows 2000 + SP4 with all updates applied.

Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t1
VALUES(1,13153,'thirteen thousand one hundred fifty three');INSERT
INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');...
995 lines omittedINSERT INTO t1 VALUES(998,66289,'sixty six thousand
two hundred eighty nine');INSERT INTO t1 VALUES(999,24322,'twenty four
thousand three hundred twenty two');INSERT INTO t1
VALUES(1000,94142,'ninety four thousand one hundred forty two');

SQLite 3.3.3 (sync):
3.823
SQLite 3.3.3 (nosync):
1.668
SQLite 2.8.17 (sync):
4.245
SQLite 2.8.17 (nosync):
1.743
PostgreSQL 8.1.2:
4.922
MySQL 5.0.18 (sync):
2.647
MySQL 5.0.18 (nosync):
0.329
FirebirdSQL 1.5.2:
0.320

Test 2: 25000 INSERTs in a transaction

BEGIN;CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));INSERT
INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three
hundred sixty one');... 24997 lines omittedINSERT INTO t2
VALUES(24999,447847,'four hundred forty seven thousand eight hundred
forty seven');INSERT INTO t2 VALUES(25000,473330,'four hundred seventy
three thousand three hundred thirty');COMMIT;

SQLite 3.3.3 (sync):
0.764
SQLite 3.3.3 (nosync):
0.748
SQLite 2.8.17 (sync):
0.698
SQLite 2.8.17 (nosync):
0.663
PostgreSQL 8.1.2:
16.454
MySQL 5.0.18 (sync):
7.833
MySQL 5.0.18 (nosync):
7.038
FirebirdSQL 1.5.2:
4.280

Test 3: 25000 INSERTs into an indexed table

BEGIN;CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));CREATE
INDEX i3 ON t3(c);... 24998 lines omittedINSERT INTO t3
VALUES(24999,442549,'four hundred forty two thousand five hundred
forty nine');INSERT INTO t3 VALUES(25000,423958,'four hundred twenty
three thousand nine hundred fifty eight');COMMIT;

SQLite 3.3.3 (sync):
1.778
SQLite 3.3.3 (nosync):
1.832
SQLite 2.8.17 (sync):
1.526
SQLite 2.8.17 (nosync):
1.364
PostgreSQL 8.1.2:
19.236
MySQL 5.0.18 (sync):
11.524
MySQL 5.0.18 (nosync):
12.427
FirebirdSQL 1.5.2:
6.351

Test 4: 100 SELECTs without an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;select>=100 AND
b<1100;select>=200 AND b<1200;...>=9700 AND b<10700;select>=9800 AND
b<10800;select>=9900 AND b<10900;>Test 5: 100 SELECTs on a string
comparison

SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%'SELECT count(*),
avg(b) FROM t2 WHERE c LIKE '%two%'SELECT count(*), avg(b) FROM t2
WHERE c LIKE '%three%'... 94 lines omittedSELECT count(*), avg(b)
FROM t2 WHERE c LIKE '%ninety eight%'SELECT count(*), avg(b) FROM t2
WHERE c LIKE '%ninety nine%'SELECT count(*), avg(b) FROM t2 WHERE c
LIKE '%one hundred%'

SQLite 3.3.3 (sync):
4.853
SQLite 3.3.3 (nosync):
4.868
SQLite 2.8.17 (sync):
4.511
SQLite 2.8.17 (nosync):
4.500
PostgreSQL 8.1.2:
6.565
MySQL 5.0.18 (sync):
3.424
MySQL 5.0.18 (nosync):
2.090
FirebirdSQL 1.5.2:
5.803

Test 6: INNER JOIN without an index

SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
SQLite 3.3.3 (sync):
14.473
SQLite 3.3.3 (nosync):
14.445
SQLite 2.8.17 (sync):
47.776
SQLite 2.8.17 (nosync):
47.750
PostgreSQL 8.1.2:
0.176
MySQL 5.0.18 (sync):
3.421
MySQL 5.0.18 (nosync):
3.443
FirebirdSQL 1.5.2:
0.141

Test 7: Creating an index

CREATE INDEX i2a ON t2(a);CREATE INDEX i2b ON t2(b);
SQLite 3.3.3 (sync):
0.552
SQLite 3.3.3 (nosync):
0.526
SQLite 2.8.17 (sync):
0.650
SQLite 2.8.17 (nosync):
0.605
PostgreSQL 8.1.2:
0.276
MySQL 5.0.18 (sync):
1.159
MySQL 5.0.18 (nosync):
0.275
FirebirdSQL 1.5.2:
0.264

Test 8: 5000 SELECTs with an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;select>=100 AND
b<200;select>=200 AND b<300;...>=499700 AND b<499800;select>=499800
AND b<499900;select>=499900 AND b<500000;>Test 9: 1000 UPDATEs without
an index

BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;update b="b*2">=10 AND
a<20;... b="b*2">=9980 AND a<9990;update b="b*2">=9990 AND
a<10000;commit;>Test 10: 25000 UPDATEs with an index

BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE
a=2;... 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE
t2 SET b=423958 WHERE a=25000;COMMIT;

SQLite 3.3.3 (sync):
1.883
SQLite 3.3.3 (nosync):
1.894
SQLite 2.8.17 (sync):
1.994
SQLite 2.8.17 (nosync):
1.973
PostgreSQL 8.1.2:
23.933
MySQL 5.0.18 (sync):
16.348
MySQL 5.0.18 (nosync):
17.383
FirebirdSQL 1.5.2:
15.542

Test 9: 1000 UPDATEs without an index

BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;update b="b*2">=10 AND
a<20;... b="b*2">=9980 AND a<9990;update b="b*2">=9990 AND
a<10000;commit;>Test 10: 25000 UPDATEs with an index

BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE
a=2;... 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE
t2 SET b=423958 WHERE a=25000;COMMIT;

SQLite 3.3.3 (sync):
1.883
SQLite 3.3.3 (nosync):
1.894
SQLite 2.8.17 (sync):
1.994
SQLite 2.8.17 (nosync):
1.973
PostgreSQL 8.1.2:
23.933
MySQL 5.0.18 (sync):
16.348
MySQL 5.0.18 (nosync):
17.383
FirebirdSQL 1.5.2:
15.542

If you want to see some more information on the above and 10 more
tests, you can go Here:

---

PEAR DB_DataObject Auto-generator Multiple Databases
I'm using the DB_DataObject script createTables.php to auto-generate
the necessary database schema on two databases. Using the .ini
approach (not the in-line PHP approach) to configure DB_DataObject in
my script. Everything is by the book, but I'm having a big problem:

Here's my main.ini file:
[DB_DataObject]

database_one = mysql://user:password@localhost/one
database_two = mysql://user:password@localhost/one
schema_location = /home/user/classes/dataobjects
class_location = /home/user/classes/dataobjects
require_prefix = /home/user/classes/dataobjects
class_prefix = DataObject_

And here's my database connect file:
<?php
// separate classes for each database
class DataObject_one extends DB_DataObject { var $_database = 'one' }
class DataObject_two extends DB_DataObject { var $_database = 'two' }
$config = parse_ini_file('/home/user/classes/main.ini', TRUE);
foreach($config as $class=>$value) {
$options = &PEAR::getStaticProperty($class,'options');
$options = $value;
}
$DB = DataObject_one::factory("tableName");
// etc...
?>

The problem is that the auto-generator createTables.php copies
everything into one directory, regardless of the number of databases
you enter in your .ini file, which means, as is the case for me in this
example, if you have two tables with the same name on different
databases, it'll keep overwriting the same file, and the resulting
class is the LAST one you enter in your .ini file.

So my question is this: is there a way to specify different
subdirectories for different databases in your initial configuration
file? If not, is there no other solution than giving every table a
unique name across an entire MySQL server if you don't want to do any
coding?

---

Flood Protection Scripts That Uses Flat File Databases?
Does anyone know of any php flood protection scripts that use a flat file database rather than mysql, and if so could you provide the link to it or something please?

I found a free flood protection class, but it uses mysql, i was wondering, is it possible for flat file database to remove an ip after a period of time, as can be done with mysql? Code:

---