topic

Classes in MySQL

The introduction

Anybody precisely does not know why, but mysql and php in the majority of web-projects almost always are used together. Like developers php have switched on support and other databases, and mysql it is possible to use not only together with php. And can it is simple providers aspiring to reduce the price of a hosting and to involve clients, included on servers support php and mysql and it became tradition?..

But the fact is the fact. Half of projects on the Internet likely is more is made on this sheaf. Therefore also there was a necessity to create the tool for programmers, with pomosh`ju which it is possible to work with a database simply and effectively. And the class on php with which help programming has much more become simpler has appeared.

The given class does not apply for functional completeness but as shows long-term experience, properties of it suffice for many projects.

That you want, you will receive!

The basis of a class is made with the simple analyzer collected on regular expressions. And the essence is very simple, we should receive result in that form in which we expect.

Well for example, if we want to learn{find out} quantity{amount} of clients brought in a database>

select count (*) from ` customers `;

That is clear, that we expect number. The class assorts search to a database and on the basis of search, gives out result.

But how to be spoken, better once to see, than hundred times to hear.

We take away and look a class.

Well and now we shall see as this animal works.

Step 1.

Let's open a new database and we shall create there a pair of tables.

create database ` test_mysql_class `;

create table ` customers ` (

` customer_id ` smallint (6) not null auto_increment,

` lastname ` varchar (255) not null default ",

` surname ` varchar (255) not null default ",

primary key (` customer_id `)

) type=myisam auto_increment=4;

insert into ` customers ` values (1, ' pupkin ',' vasya ');

insert into ` customers ` values (2, ' mal4ish ',' ploxish ');

insert into ` customers ` values (3, ' mal4ish ',' kibal4ish ');

create table ` orders ` (

` customer_id ` smallint (6) not null default ' 0 ',

` product_id ` smallint (6) not null default ' 0 ',

key ` customer_id '(' customer_id ',' product_id `)

) type=myisam;

insert into ` orders ` values (1, 12);

insert into ` orders ` values (1, 23);

insert into ` orders ` values (1, 34);

insert into ` orders ` values (1, 65);

insert into ` orders ` values (2, 12);

insert into ` orders ` values (3, 33);

insert into ` orders ` values (3, 43);

insert into ` orders ` values (3, 655);

At us has appeared two tables, with clients and with their orders.

For the test will suffice!

SHag2.

Let's try to incorporate to a database by means of a class:

<? php

require ("class.mysql.php");

$host = "localhost";

$only_db = "test_mysql_class";

$username = "username";

$password = "password";

$db = new mysql_db ();

if (! $db-> getconnect ($host, $only_db, $username, $password)) {

echo " net contact:-(";

exit;

} else echo " yes contact!;-) ";

If at you the inscription " yes contact has appeared!;-) ", means connection with a database has passed successfully, it is possible to pass to studying a class.

Attention! All examples will be will be finished in the end of the first program!

Step 3.a

And so we shall try to learn{find out}, how much at us clients in a database?

<? php

$sql = " select count (*) from ` customers ` ";

$count = $db-> query ($sql, 1);

echo $count;

Easier it does not happen.

The class has analysed our search, and has understood, that on an output{exit} it is necessary to send one number. If we do not want, that the debugging information would be printed by a class it is necessary instead of:

$count = $db-> query ($sql, 1);

To write so:

$count = $db-> query ($sql, 0); or it is simple $count = $db-> query ($sql);

Also it works with min (*) and the MOVE (*).

Step 3.b.

Now we shall try to learn{find out} what id number{room} from our client whom call pupkin vasya

<? php

...

$sql = " select ` customer_id 'from' customers ` ";

$sql. = " where ` lastname '=' pupkin 'and' surname '=' vasya ' limit 0,1 ";

$customer_id = $db-> query ($sql, 1);

echo $customer_id;

At "disassembly" of this expression, the class "has understood", that on "mountain" it is necessary to give out only one number, namely $customer_id. Why? Because first we do{make} select only on ` customer_id `, a second and this most important, in the end $sql at us costs{stands} limit 0,1. How to be spoken comments izlishni.

Step 3.v.

Well and now we shall try, to find a name and a surname of the client knowing it id.

<? php

…

$sql = " select ` lastname ',' surname 'from' customers ` ";

$sql. = " where ` customer_id ` = 1 limit 0,1 ";

$obj = $db-> query ($sql, 1);

echo $obj;

Well also what we shall receive? object

Yes, we have received object. But this object has properties necessary to us,

Namely ` lastname 'and' surname `, and it that is necessary to us!

<? php

...

$sql = " select ` lastname ',' surname 'from' customers ` ";

$sql. = " where ` customer_id ` = 1 limit 0,1 ";

$obj = $db-> query ($sql, 1);

echo $obj-> lastname;

echo " ";

echo $obj-> surname;

Now we have received:

pupkin

vasya

That also it was necessary to us!

Step 3.g.

And now we shall want to receive all names of clients from a database.

<? php

...

$sql = " select ` lastname ',' surname 'from' customers ` where 1 ";

$array = $db-> query ($sql, 1);

echo $array;

Also what we have received on an output{exit}? A file! array

But it is a file of objects, we need to look through and receive only them all names!

<? php

...

$sql = " select ` lastname ',' surname 'from' customers ` where 1 ";

$array = $db-> query ($sql, 1);

if (is_array ($array))

foreach ($array as $obj)

echo $obj-> lastname. " ". $obj-> surname. "";

They our clients:

pupkin vasya

mal4ish ploxish

mal4ish kibal4ish

SHag4.

It would seem all listed could suffice, but....

If we to be necessary to receive all products which clients with a surname mal4ish have reserved? Certainly problems no, as mysql not all enclosed sql supports searches, sql the search will be the following:

select ` orders'. product_id ` as id from (` orders ` left join ` customers 'on' customers'. customer_id '=' orders'. customer_id `) where ` customers'. lastname '=' mal4ish '

<? php

...

$sql = " select ` orders'. product_id ` as id from ";

$sql. = " (` orders ` left join ` customers 'on' customers'. customer_id '=' orders'. customer_id `) ";

$sql. = " where ` customers'. lastname '=' mal4ish ' ";

$array = $db-> query ($sql, 1);

if (is_array ($array))

foreach ($array as $obj)

echo $obj-> id. "";

But it is difficult and not clear for the some people, and sometimes such search to make it is simply impossible.

What to do{make}? It is necessary to translate kommandy which we want to execute in that format which understands mysql.

Well for example sql it would be possible to write search so:

select ` product_id 'from' orders 'where' customer_id `

in (select ` customer_id 'from' customers 'where' lastname '=' mal4ish ')

Only do not try to pass{miss} it through mysql, certainly it is not correct.

But if we shall say to our class, that he would disassemble this design in parts and has replaced what is in brackets on real values? Why also no? But what to avoid mess in the present{true} and artificial searches, we shall put in our example instead of round, braces, and we shall pass{miss} in the end it through our class.

<? php

...

$sql = " select ` product_id 'from' orders 'where' customer_id ` in ";

$sql. = " {select ` customer_id 'from' customers 'where' lastname '=' mal4ish '} ";

$array = $db-> query ($sql, 0);

if (is_array ($array))

foreach ($array as $obj)

echo $obj-> product_id. "";

If to switch on output mode on the screen we shall see, that our class changes search which costs{stands} in braces, on result divided{shared} by a point and again carries out search, yet will not receive last result:

yes contact!;-)

----sql

select ` product_id 'from' orders 'where' customer_id `

in {select ` customer_id 'from' customers 'where' lastname '=' mal4ish '}

----in sql

select ` customer_id 'from' customers 'where' lastname '=' mal4ish '

----sql

select ` product_id 'from' orders 'where' customer_id ` in (2,3)

655

The result coincides with expected!

The conclusion

In brief all. A class as a class, use on health! It is necessary to watch{keep up} only syntax, and behind blanks, that regular expressions would understand, that you want.