• To evade confusion of the field (if we deal with fields having similar titles) use AS operator in requests: "SELECT table1.id as id1, table2.id as id2". This will help you to evade errors in the request (for example, if the table isn�t indicated and there are some tables containing field with such a title, mysql shows up an error), you�ll also evade misunderstanding working with the extracted data (echo $row["id1"] is written much easier than $row[$x]).
  • Data like DATE, TIME, DATETIME and TIMESTAMP can be formatted by means of function date_format (look mysql manual). Use it and don�t format data through php- it�s just a waste of system resources.
  • Try to minimize the usage of LEFT JOIN for joining tables. This operation is rather laborious for the data base.
  • Use identificators where possible � the data selection occurs more quickly by indication of key field than by indication of a usual one.
  • Instead of "WHERE id=1 OR id=3 OR id=232" you may use built-in function IN: "WHERE id IN (1,3,232)".
  • If you need a text search, be careful with sign �%�. At least, you�d better not to do request like somefield LIKE '%a%'- this operation is also too laborious. Anyway, you are to filter the word and cut off those of them which have less than 3 symbols.
  • Use as little necessary fields in your request as you can. "SELECT * FROM sometable" is done more slowly than "SELECT id FROM sometable", especially if there is a lot of data in the table. One field is enough to count up the general number of the table lines (or the ones complying some condition).
  • Distribute data among the pages using LIMIT operator. This saves the time of request completion and reduces the volume of the page which the user gets.Even if you are not threaten with �falling down� because of customers� flow, it�s better to take a habit for not having problems with adaptation to the new tasks. Now about the safety of the work.
  • Try to omit input of single inverted commas ("'") in the data base, as it is the service symbol for the data base requests. You may treat the field with function str_replace: $somefield = str_replace ("'", "'", $somefield) before its input in the base.

Besides that, it�s an additional barrier on the hackers� way. The example of �hacking� is simple:
mysql_query("UPDATE users SET password=PASSWORD("$passwd") WHERE login="$login");
If we don�t process inversed commas by the entry, the hacker may use line like "george_smith' OR login LIKE%� as login. Following request will get into the base mysql_query("UPDATE users SET password=PASSWORD('$passwd') WHERE login='george_smith' OR login LIKE '%'"). It means, all the passwords will be the same. It�s just a single example. So process the data got from the address line or from the form and take tem to the type needed to evade errors and site �breaks�. One more example: if you need an identificator (the whole number) you are to process it by means of intval: $id = intval($id)).
Requests for the line insertion (INSERT)


  • The identifier�s field is not to insert. There is field�s property AUTO_INCREMENT for that.It�s amazing to read how people write on the forum
    -What should I do with RNG? It�s out of order!
    -What for?
    -I�d like to use it in the id-base...
    We don�t need such �amateur actions�.
  • If you need to insert current time in the field of DATE, TIME, DATETIME or TIMESTAMP format you are to use built-up in mysql function NOW: "INSERT INTO vote (ip, date) VALUES ($REMOTE_ADDR, NOW())"
  • It�s better to cover passwords stored in the base with function php md5: "INSERT INTO user (login, pass) VALUES ('$login', ". md5($pass). ")" "SELECT * FROM user WHERE login='$login' AND pass=". md5($pass)

The advices seem to come to the end. And the last. Recently I�ve started thinking that by writing scripts working with data base you are to address not only a stupid and frolicsome guest, but a badly-skilled administrator as well. Even if we check the text to be put into the text field carefully (don�t write single inverted commas, make their auto substitution in Word) the possibility that service symbols will get into the request reduces to nothing.
Paginal output

The same question is asked regularly on the forum: how to make paginal output? And every time this person is answered �It�s easy! m lines beginning with line n Select request Limit $n,$m". It�s not so easy in fact.
I�ve already written about LIMIT operation factor�s syntax, but that proved to be useless. For the full value paginal lines� output from the base you�ll need more. And namely

  • Process page number (also check if it is more than general quantity of pages)
  • Draw a navigation line (not just �forwards-backwards�, but also with references to some nearest pages)

And here the main problems begin.
Recently I�ve worked over the site in which these paginal outputs in statistics were represented in each list (there was a lot of lists!). And here the solution came how to reduce all these things to one universal and simple solution. I�ve got four functions which I use whenever I need paginal data output.
The first function is meant for the inner usage by the two following ones. It takes page number, general line quantity and quantity of lines on each page and shows up the page number which is already checked. The second one takes the same, checks the page number and shows up either full (LIMIT n,m), or shortened (LIMIT m) LIMIT parameter if it is the first page. Otherwise it shows up nothing. The third function makes navigation line from the same three parameters and reference address. The last function shows up number for the enumerated list.
It�s enough for the successful work with paginal data output. Let�s look through what occurs in the code of the program:
<?php
// quantity of lines on the pages
$in_page = 10;

// we�ll get quantity of lines
$amount = @mysql_result(mysql_query("SELECT count(id) as goods_total FROM goods"),0);

// we draw a navigation line and write the head of the table
print("<div align=center>". <b>draw_bar($page, $amount, $in_page,
"goods.php?page=")</b>. "</div>\n<table>");

//form request to the base
$goods_result = mysql_query("SELECT id, name, description, price FROM goods
ORDER BY name, price "
. <b>get_limit($page, $amount, $in_page)</b>);

// get number for the enumerated list
$count = <b>get_count_limit($page, $amount, $in_page)</b>;

//put the lines out
while ($good_row = mysql_fetch_array($goods_result)) {
$count++;
print (
"<tr");

//the background for every second line is grey
if ($count/2==intval($count/2))
print (
" bgcolor=#e1e1e1");
print (
"><td align=right>$count.</td><td>${good_row[name]}
<br>${good_row[description]}</td><td align=right>${good_row[price]}</td></tr>\n"
);
};

// the end of the table and lower navigation line
print("</table><div align=center>". <b>draw_bar($page, $amount,
$in_page, "goods.php?page=")</b>. "</div>\n");
That�s all you need for the paginal output!
Only one explanation � as a draw_bar function parameter the address of this script with all parameters is indicated in such way to make possible only adding of the number of the page. If the selection is complete, you�ll have to form this address by hand (so the simplification is quite relative: by simplifying one we complicate another).
The navigation board is made in form of the pages� numbers (" 1 | 2 | 3 "). But it isn�t a problem to bring it to the shape "0-10 | 11-20 | 21-30"
Functions of MySQL data processing

I must confess that it didn�t take me much time to study the MySQL manual; I took it only in the emergency cases. I proved to lose a lot, there were things I didn�t think about, but they had already been realized in the MySQL data bases.
Having looked through the list of mathematic functions, I changed a little bit the data calculation in my game. The data were extracted from the base by request, the calculations were done, and then they were returned. Now the quantity of requests for this operation is reduced to one � the UPDATE-request is sent at once and inside of it all the calculations and attendant data are indicated.
Meanwhile I�ll tell about functions (not all of them, of course) which every person who works a lot with base needs to know.
Conditional functions

IFNULL(x,y) � if x isn�t equal to NULL, it shows up x, otherwise � y.
NULLIF(x,y) � if x and y equal, it shows up NULL, if they aren�t equal � x.
IF(x,y,z) � if x = true (namely, if x isn�t equal to 0 and isn�t NULL), it shows up y, if not � z.
For example, the information about users is saved in forum and there is a possibility not to show your e-mail to other users. Field show_email is created in which 0 is placed if the user doesn�t want to show his address and 1 if he doesn�t mind.
SELECT ..., IF (show_email,CONCAT("<a href=mailto:",email,">write a letter</a>"),"address is not indicated") AS email, ...
Mathematical functions

MOD(N,M) or "%" � is the reminder from division Non M.
FLOOR and CEILING � round-up to the whole number up and down.
ROUND � round-up to the whole number or to the definite decimal fraction.
LEAST (X,Y,...) and GREATEST(X,Y,...) � minimum and maximum numbers from the indicated ones.
I don�t mention standard mathematical functions of taking module, sign, work with angles.
String functions

CONV(N,system _from,system _to) � convertation of a number from one calculation system to another
select CONV("ff",16,10); => 255. By the way, it�s possible to convert not only in the standard systems (2,8,10,16) but in any other from 2 to 36� as far as many letters the Latin alphabet contains.
CONCAT(X,Y,...) � joining up lines and numbers in one line (the example was given before).
CONCAT_WS(separator,X,Y,...) �implode function�s analog.
LENGTH( line) � strlen.
LOCATE(substring, line) � strpos.
SUBSTRING( line, indention, length) � substr.
TRIM() � deleting of spare symbols from the beginning and end of the line. Unlike php trim permits not only gaps but any other symbols and combinations of symbols as well.
REPLACE (line, X, Y) � in the line changes X into Y (don�t mix the order with parameter order in str_replace)
Date and time

There is a lot of functions, I�ll mention only the most important ones; it isn�t true that MySQL counts days only from Sunday, as it is usual in America. You are to use function WEEKDAY instead of DAYOFWEEK, so that Monday would be marked with 0, Tuesday with 1, Sunday with 6.
For the compose date formatting (for example, for the output of the date as 18.08.01) there are functions DATE_FORMAT (for date and time) and TIME_FORMAT (for time only). Working with these functions is more convenient than using your own ones (because this tool is standard and universal what is difficult to achieve in a self-made device) and it�s also faster (built-in functions of mysql-server are used which are already placed in the memory instead of compilation by each starting of your own code�s script).
MySQL also supports Unix timestamp- transfers into it and from it through functions UNIX_TIMESTAMP and FROM_UNIXTIME
UNIX_TIMESTAMP([date-time]) � shows up date in the Unix format (if the argument is omitted � current date).
FROM_UNIXTIME(date [, format]) � shows up the date in the usual format (in the second argument format may be indicated according to the rules as in DATE_FORMAT).
Besides that, the main date parameters (date, day of a week, month (also possible with word), year, quarter (!), week and many others) are available not only through the common function DATE_FORMAT, but through the separate special functions as well.
Other functions

LAST_INSERT_ID() � as well as mysql_insert_id(), shows up the last identifier which was generated by the data base on request from given connection.
MD5(line) � as a password ciphered by means of function PASSWORD() is said to be easy to decode I save hash md5 from the password.
FORMAT(X, D) � format number X as"#,###,###.##", round-up to D signs after the comma. I thought, it would not bad to make formatted for the convenient reading numbers in my game, looked up the manual, and here it is
There are another two functions about that I�ve forgotten writing about the optimization of the logs� work.
INET_NTOA(number) � long2ip() analogue.
INET_ATON(ip-address) �ip2long() analogue