View Full Version : Amateur performances � tables connections in MySQL

10-20-2010, 10:03 AM
There are four reasons why you shouldn�t start writing a big application to learn a language.
Firstly I intended to release am issue of �Amateur performances� with collection of errors which were made mostly by me. Something I�ve extracted from articles about traditional errors and so on. But live proves out to be much more interesting! I write about a new amateur performance immediately.
So some person, we�ll call him John, asks the Community of a forum about MySQL.
J: It isn�t clear enough how to build a connection between tables is such a way that the data from the second table would be automatically inserted into the field of the first one (of course, it should occur selectively and according to ID).
Variant like SELECT db.user, db.delete_priv, user.user, user.delete_priv FROM db,user WHERE db.user = user.user doesn�t suit at all as far as it can be hardly called a connection�
C: Frankly speaking, I don�t understand why this connection doesn�t suit you. In what respect is it tense?
J: I simply dislike the connection based on the request syntax�I would like to have something more considerable otherwise there is no sense in keys and indexes.
C: Do you mean graphic interface as one in MS Access under something more considerable?
Unfortunately Access itself works exactly like this � it�s enough to press �SQL� button and you�ll see such connections.
J: It�s simply easier for me to do from the program point of view as far as tables are small� In case it doesn�t function in MySQL in fact.
C: Through nested loops and recursions? Tell me what for do you need the data base and tables then?
J: No nested loops and recursions � I read some massive number and work with them�. And I don�t need to build a connection based on select at all�
C: Right� Save everything in files and you will have no questions at all.
And so I think my duty is to examine carefully tables� connections.


create table.....
reference_definition: REFERENCES tbl_name [(index_col_name,...)]
[ON DELETE reference_option]
[ON UPDATE reference_option] The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. See section 5.4 Functionality Missing from MySQL.
Connection without straining

There was no straining at all in the description of connections within a request � data bases have been working like this for ages. Access with its pointers and form-builders appeared considerably later.
Interrelation between tables is the essence of a relative data base. As an ideal no information is kept outside of the data base. Within base different things are separated into different tables � for example, messages and forums, messages and their authors (if the users� authorization exists); access to forums can be also contained in the separate table for each participant. At that all the data are placed where they should be to, they don�t mix and repeat each other. This is the essential sense of the relative data bases. Except for complicated tasks (for example, to build a tree of forum�s discussions) data selection is done with one request. There is no need in massives� usage.
What is wrong

For example, it can be something like that:

$res1 = mysql_query("SELECT id, name FROM rubs");
while ($row = mysql_fetch_row($res1))
$rub[$row[0]] = $row[1];
Rubrics� names are obtained from request and recorded into $rub massive.

$res2 = mysql_query("SELECT id, url, name, rub FROM sites WHERE some condition ");
while ($row = mysql_fetch_array($res2)) {
echo "<a href=", $row["url"], ">", $row["name"], "</a>";
echo "(rubric <a href=rub.phtml?id=", $row["id"], ">";
echo $rub[$row["rub"]], "</a><br>";
};Now news are selected and we insert a corresponding element of rubrics� massive instead of selected rubric�s number.
In fact, you could save yourself necessity to carry this massive $rub through the whole program (and should we take through GLOBAL if functions address rubrics?) and possibility to have an error with $rub[$row["rub"]] - if there are some similar requests on the page, it�s simple to be mistaken somewhere.
Beside that massive $rub needs some memory value (and what if we have many rubrics?). In the third PHP-version such script will be accomplished longer than by usage of tables� uniting because it interprets a program sting-by-string when accomplishing (unlike the 4th one which compiles a program and only after that accomplishes it).
What should you do

In the example given above it�s possible to apply joining up of tables and thus set free from the disadvantages described.

$res = mysql_query("SELECT sites.id, url, sites.name as sitename, rubs.name as rubsname,
rubs.id as rub_id FROM sites, rubs WHERE sites.rub=rubs.id
and-some-condition ");
while ($row = mysql_fetch_array($res2)) {
echo "<a href=", $row["url"], ">", $row["sitename"], "</a>";
echo "(rubric <a href=rub.phtml?id=", $row["rub_id"], ">";
echo $row["rubsname"], "</a><br>";
}; It�s better to use request "SELECT sites.id, url, sites.name as sitename, rubs.name as rubsname, rubs.id as rub_id FROM sites, rubs WHERE sites.rub=rubs.id" here. We prove out to have a ready-made massive, take care about output of its elements only and write a shorter code.
Tables joining syntax

Simple connection - INNER JOIN:

e2.field or

SELECT <fields> FROM table1, table2 WHERE table1.field1=table2.field2 or

SELECT <fields> FROM table1 INNER JOIN table2 USING (field1) If tables are united on field1.
In such a connection only those table strings are selected which correspond with the joining condition � fields� meanings equality. If there is no corresponding string in table 2 for the one from table 1, the string doesn�t get into the result of request. If you need to calculate number of sites within a rubric (I continue the example with catalogue), such a request doesn�t suit you at all � only those rubrics will appear in the list which have sites. You are to use LEFT JOIN for operation like this.

SELECT <fields> FROM table1 LEFT JOIN table2 ON table1.field1=table2.field2 or

SELECT <fields> FROM table1 LEFT JOIN table2 USING (field1) if the tables are joined up on field1.
At this there can be no corresponding string in the table 2; then we�ll get NULL in the fields from table 2. And if it is a group operation as in the case with sites� number in a rubric, the field will contain 0:

SELECT rubs.id, name, COUNT(sites.id) AS sites FROM rubs LEFT JOIN sites ON
rubs.id=sites.rub GROUP BY rubs.id