MySQL by examples

Content

1. Using what query in MySQL it is possible to display several maximum values?
2. What does the error ‘Too many connections’ mean?
3. How to provide access to MySQL Denwer from the local network?
4. How to delete records from a table if their date is 10 days earlier than the current one?
5. How to use queries with UPDATE MySQL command?
6. How to output the data from the primary and subordinated table into one by means of UNION MySQL query?

---

Using what query in MySQL it is possible to display several maximum values?

Example of query:

SELECT field1 FROM Table ORDER BY field1 DESC LIMIT 5

or if there is one value

SELECT MAX(field) FROM Table

What does the error ‘Too many connections’ mean?

If an error occurred ‘Too many connections’, when you tried to open the page of your web-site,
it means that all connections to your MySQL server are occupied by other users.

How to solve this problem? The number of simultaneous connections is regulated with the variable max_connections;
100 connections are set on default. Or may be your hoster poses too strict limitation on a number of
simultaneous connections to MySQL; in this case connect him and discuss the problem.

If it is a local server Denwer:

1. Find a configuration file mysql (I have it here: C:joomlaserverusrlocalmysql5my.cnf ).
2. In the subsection [mysqld] add a line max_connections = 300. The more is a number the better; however it depends on your computer; it may not cope with it.
3. Save a file and restart MySQL server.

How to provide access to MySQL Denwer from the local network?

Using a command ipconfig in Windows command line indicate local IP-address of your computer.

Adjust firewall Windows XP:

1. Open Start – Control board, select firewall Windows.
2. Go to tab Exceptions.
3. Click the button Add port; enter you IP-address in the field Name (mine is 192.168.1.33), and indicate 80 in the field Number.

For Windows 7:

1. Open Start – Control board — System and safety, select firewall Windows.
2. On the left board select Additional parameters.
3. On the left board select Rules for incoming connections.
4. On the right board select Create a rule…
5. Type of a rule – select For port. Next.
6. In the field Specified local: indicate 80. Next.
7. Select Permit connection. Next.
8. Select Private profile. Next.
9. Write the name of the profile. Ready.

For Windows Vista:

1. Оpen Start – Control board — Safety, select firewall Windows.
2. Click Permit access through firewall Windows. Button Continue.
3. On the tab “Exceptions” clock the button Add port .
4. In the field “Name” enter the title (TCP for port 80). In the field “Port” enter 80.
5. Make sure you selected parameter TCP. Button Change of field.
6. Select Only local network (subnetwork). ОК.

My host is placed in Directory C:joomlaserverhomelocalhostwww.
In order to assign it IP-address 192.168.1.33, I have created a file C:joomlaserverhomelocalhost.htaccess and added lines to it:

## File C:joomlaserverhomelocalhost.htaccess
## Indicate here your external IP-address, not 192.168.1.33!
# dnwr_ip 192.168.1.33

Restart Denwer. Make sure that using address http://192.168.1.33
your site is opened /home/localhost/www. Instead of 192.168.1.33, your IP-address.

Now you can work with MySQL Denwer from another computer indicating the needed IP-address.
For instance, you can connect to phpMyAdmin using the address:
http://192.168.1.33/phpMyAdmin/.

How to delete records from a table if their date is 10 days earlier than the current one?

Suppose we have a table “users”, in which there is a field “join_date” of the type “date”.
Use built-in functions “TO_DAYS” and “NOW”, to carry out the task set:

DELETE FROM `users` WHERE (TO_DAYS( NOW( ) ) - TO_DAYS( `join_date` )) > 10

How to use queries with UPDATE MySQL command?

For instance, we have primary with articles and another table (faq) subordinated to it according to «id». In the subordinated table (faq) there is a field «post», which contains «id» from the primary table (articles).
Both tables have a field «date»; this field is filled out in the primary table (articles) in lines,
and in the subordinated table this field was added later, therefore it is empty.

articles				faq
id	date	title		post	date	title
1	2012-09-15	mysql update		1	0000-00-00	mysql update
2	2012-04-11	server Denwer		2	0000-00-00	server Denwer
3	2012-03-05	Too many connections		4	0000-00-00	Too many connections
4	2012-03-05	TO_DAYS		1	0000-00-00	UPDATE MySQL

The task is to fill in all lines in the field «date» in the subordinated table (faq) depending on
what record from the primary table (articles) the lines from the subordinated table belong to (faq).
For this, let’s use two queries with UPDATE MySQL command:

UPDATE faq JOIN articles ON faq.post = articles.id
SET faq.date = articles.date

OR

UPDATE faq, articles
SET faq.date = articles.date
WHERE faq.post = articles.id

Thus, by means of a query with UPDATE MySQL command, lines were updated in the subordinated table (faq)
with the field «date» according to «id».

articles				faq
id	date	title		post	date	title
1	2012-09-15	mysql update		1	2012-09-15	mysql update
2	2012-04-11	server Denwer		2	2012-04-11	server Denwer
3	2012-03-05	Too many connections		4	2012-03-05	Too many connections
4	2012-03-05	TO_DAYS		1	2012-09-15	UPDATE MySQL

For example, if in the primary table (articles) there was a line with id = 1 and date = ‘2012-09-15’, and in the subordinated table
(faq) two lines are subordinated to the record of the primary table (articles), i.e. in the field «post» of these lines there is value 1,
after the completion of a query with UPDATE MySQL command
these lines in the field «date» will be filled with value «2012-09-15».

How to output the data from the primary and subordinated table into one by means of UNION MySQL query?

So, there are two table: a primary table (articles) and a subordinated to it according to «id», table (faq):

articles				faq
id	date	title		id	post	date	title
1	2012-09-15	UNION MySQL		1	1	2012-09-17	union mysql
2	2012-04-11	Server Denwer		2	2	2012-05-13	Server Denwer
3	2012-03-05	TO_DAYS		3	4	2012-03-25	Too many connections
4	2012-03-10	Too many connections		4	1	2012-09-28	union mysql

When new line appears in the primary table, the date its adding is recorded. In the subordinated table, the date can differ,
since records to it are added later, when new notes appear. Primary record from the primary table as if
describes the category of notes included in it. It is necessary to get one table from two, using
UNION MySQL.

SELECT a.id, a.title, b.date AS mdate, b.title, b.id AS faq_id
FROM DATA a, faq b
WHERE a.id = b.post
UNION
SELECT a.id, a.title, a.date AS mdate, 'null', 0
FROM DATA a

As a result of the use of UNION MySQL, we will get the following table:

articles
id	title	mdate	title	faq_id
1	UNION MySQL	2012-09-17	UNION MySQL	1
1	UNION MySQL	2012-09-28	UNION MySQL	4
2	Server Denwer	2012-05-13	Server Denwer	2
4	Too many connections	2012-03-25	Too many connections	3
1	UNION MySQL	2012-09-15	null	0
2	Server Denwer	2012-04-11	null	0
3	TO_DAYS	2012-03-05	null	0
4	Too many connections	2012-03-10	null	0

Now you may use this table at your discretion.

---