MySQL structure and way of working

The entire access permission system for the server, databases, and their tables, MySQL
store in a table called mysql, which like all will be in the /data directory. The main
The MySQL tool is mysqladmin, which as its name suggests is responsible for
the administration.

MySQL by default creates the user root with all possible permissions enabled, we can
use this user as an administrator or create another, for example mysqladmi. Like the user
Root creates without access key, the first thing we must do is assign one:

mysqladmin -u root password "mykey"

From now on, any operation we perform as root must specify the password. There is
It is worth noting that between the modifier -p and the key there should be no spaces.

mysqladmin -u root -pmykey

The structure of MySQL

In the /benc directory we will find examples of scripts and SQL. In the /share directory are the
server error messages for different languages. The directories /include and /lib contain the
*.h file and the necessary libraries, in /bin are the executable files and in /data
we will find as a subdirectory each of the databases we have created, for each
The table we define in MySQL will create three files: mitabla.ISD, mitabla.ISM, mitabla.frm

The file with the ISD extension contains the data of our table, the ISM contains information.
about the keys and other data that MySQL uses to search for data in the ISD file. And the file
from the structure of the table itself.

Security

As we discussed earlier, the entire MySQL permissions system is stored in a database.

MySQL call data, which is composed of five tables: host, user, db, tables_priv,
colums_priv.

The user table contains information about the users, from which machines they can access.
our MySQL server, its key, and its different permissions. The host table informs us about
What machines will be able to access our system, as well as the databases they will have?
access and its different permissions. Finally, the tables db, tables_priv, columns_priv no
They provide individual control of the databases, tables, and columns (fields).

Here is a brief description of the different permissions:

Allows the use of the SELECT statement

Insert_priv: Allows the use of the INSERT statement
Update_priv: Allows the use of the UPDATE statement
Delete_priv: Allows the use of the DELETE statement
Create_priv: Allows using the CREATE statement or creating databases
Drop_priv: Allows the use of the DROP statement or to delete databases
 Reload_priv: Allows reloading the system via mysqladmin reload
Shutdown_priv: Allows stopping the server via mysqladmin. Allows stopping the server.
via mysqladmin shutdown
Process_priv: Allows handling of server processes
File_priv: Allows reading and writing files using commands like SELECT INTO OUTFILE and
LOAD DATA INFILE
Allows granting permissions to other users
Index_priv: Allows creating or deleting indexes
Alter_priv: Allows the use of the ALTER TABLE statement

If we leave the fields user, hostodb blank, we will refer to any user, server or
database. We will achieve the same effect by placing the % symbol in the field.

PHP FUNCTIONS FOR ACCESSING MYSQL

mysql_affected_rows: returns the number of rows affected in the last SELECT, UPDATE or
DELETE question on the server associated with the specified connection identifier.
For the SELECT, it is recommended to use mysql_num_rows.

int mysql_affected_rows(int [link_identifier] );

mysql_close: closes the connection to the MySQL database associated with the identifier
specified connection. If a connection identifier is not specified, it is assumed to be the
last open connection.

int mysql_close(int [link_identifier] );

Returns: TRUE if it was closed correctly, FALSE in case of error.

mysql_connect: establishes a connection to a MySQL server. All arguments

optional, and if not specified, the default values are (' the localhost', name of the
user who owns the server process, the empty password). The hostname string
it may also include a port number, "hostname:port". In case of making a
second call to mysql_connect with the same arguments, it will only return the already
existent.

The connection to the server will close as soon as the script execution ends, unless the
Let's close it first with the mysql_close function.

int mysql_connect(string [hostname], string [username], string [password]);

Returns: a connection identifier, or FALSE in case of error.

mysql_create_db: attempts to create a new database on the associated server

specified connection identifier.

int mysql_create_db(string database name, int [link_identifier] );

 mysql_data_seek: moves the pointer that indicates the current row to the number of the row of the
query that indicates the identifier. The next call to mysql_fetch_row or
mysql_fetch_array would return that row.

int mysql_data_seek(int result_identifier, int row_number);

Returns: TRUE if everything has gone well, and FALSE in case of an error.

returns the name of the database saved in position i of the

results of the query indicator returned by the mysql_list_dbs function. The function
mysql_num_rows can be used to determine how many database names
are available.

string mysql_dbname(string result, int i);

mysql_db_query: Executes a query on a database. If the identifier does not

specifically, the function attempts to find an open connection with the server. If not
find a connection, it will try to create one (similar to mysql_connect() without arguments).

int mysql_db_query(string database, string query, int link_identifier);

Returns: a connection identifier, or FALSE in case of error.

mysql_drop_db: Deletes a database from the server associated with the identifier
connection.

int mysql_drop_db(string database_name, int [link_identifier] );

Returns: TRUE if everything has gone well, and FALSE in case of error.

mysql_errno: Returns the error number associated with the last operation performed.

int mysql_errno();

mysql_error: Returns the text associated with the error produced in the last operation
performed by the database.

string mysql_error();

<?php
mysql_connect("marliesle");
echo mysql_errno() . ": " . mysql_error() . "<BR>";
mysql_select_db("nonexistentdb");
echo mysql_err_db("nonexistentdb");
echo mysql_errno().": ".mysql_error()."<BR>";
mysql_query("SELECT * FROM nonexistentable");
echo mysql_errno() . ": " . mysql_error() . "<BR>";
?>
 mysql_fetch_array: Returns an array with the information corresponding to the result of
a query specified by its identifier or 'false' if there are no more rows.

array mysql_fetch_array(int result);

mysql_fetch_field: Returns an object that contains information about the fields.

They make up a result of a query. If 'offset' is not specified, it returns information.
about the following field that has not yet been returned.

object mysql_fetch_field(int result, int [field_offset] );

Properties of the returned object:

field name
table - name of the table to which the field belongs
max_length - maximum length that the field can take
not_null - 1 if the field cannot take null values
primary_key - 1 if the field is a primary key
unique_key - 1 if the field has a uniqueness constraint
multiple_key - 1 if the field does not have rest
multiple_key - 1 if the field does not have uniqueness constraint
numeric - 1 if the field is numeric
blob - 1 if the field is BLOB
type -data type (of the field)
unsigned - 1 if the field is 'unsigned'
zerofill - 1 if the field is filled with zeros

mysql_fetch_lengths: stores in a table the length of each field of a query

performed with mysql_fetch_row or mysql_fetch_array. The table index starts at 0.

int mysql_fetch_lengths(int result);

Returns: a table or FALSE if there is an error.

mysql_fetch_object: It is similar to 'mysql_fetch_array', only that the results of a query,

instead of a table, it returns them as an object. In this case, you can only access the
data through the names of its fields. To reference the value of a field we must
use the operator of objects (-->)

int mysql_fetch_object(int result);

Returns: an object or FALSE in case of error.

mysql_fetch_row: Returns a table with the values of the fields of the current row of the
consultation, which specifies the indicator (result), and moves the internal pointer that marks the row
actual to the next row, if there are no more rows return FALSE. The index of the table starts
in 0.

array mysql_fetch_row(int result);

Returns: a table or FALSE if there is an error.

Returns the name of the field specified by the index.

string mysql_field_name(string result, int i);

mysql_field_seek: Moves the current field pointer forward the current positions
forward the positions indicated by 'offset'.

int mysql_field_seek(int result, int field_offset);

mysql_field_table: Returns the name of the table that stores the specified field
for the index ('field_offset').

string mysql_field_table(int result, int field_offset);

mysql_field_type: Returns the type of the specified index field.

string mysql_field_type(string result, int field_offset);

mysql_field_flags: Returns the specifiers (flags) of the specified field as a

text string in which each specifier corresponds to a word, and these go
separated by a single space. You can analyze the string using explode().

Los especificadores son: "not_null", "primary_key", "unique_key", "multple_key", "blob",

"unsigned", "zerofill", "binary", "enum", "auto_increment", "tmestamp".

string mysql_field_flags(string result, int field_offset);

Returns the length of the specified field

int mysql_field_len(string result, int field_offset);

mysql_free_result: It should only be used if the amount of memory used for

storing the result of a query is very large. When this function is executed,
all the memory associated with the result is freed.

int mysql_free_result(int result);

This function returns the ID (identifier) generated for the fields

auto-incremented. The returned ID corresponds to that of the last
INSERT operation.

int mysql_insert_id(void);

mysql_list_fields: Returns information about the table. The resulting value can be
used with mysql_field_flags(), mysql_field_len(), mysql_field_name(), and
mysql_field_type(). The identifier it returns is a positive integer or '-1' in case of
error. The text describing the error is located in $phperrmsg.
int mysql_list_fields(string database, string tablename);

mysql_list_dbs: Returns a pointer that contains the available databases for the
current server (msql daemon). This value is used with the function mysql_dbname().

int mysql_listdbs(void);

mysql_list_tables: Returns an identifier, which we will pass to the function

mysql_tablename to extract the names of the tables from the specified database.

int mysql_list_tables(string database, int link_identifier);

mysql_num_fields: returns the number of fields in a query.

int mysql_num_fields(int result);

mysql_num_rows: Returns the number of rows in the result of a query

int mysql_num_rows(string result);

mysql_pconnect

int mysql_pconnect(string [hostname], string [username], string [password]);

Returns a persistent disconnection identifier or 'false' in case of error. The function

mysql_close does not close an open connection with mysql_pconnect.

mysql_query: Executes a query on the active database in the associated server.

connection identifier. If not specified, the last open connection is used. This
function returns TRUE or FALSE to indicate whether the UPDATE, INSERT, or DELETE operations
have been successful. For the SELECT operation, it returns a new result identifier.

int mysql_query(string query, int [link_identifier]);

Returns the content of a cell from a result. The 'field' argument

it can be an index or the name of the field in the form: table.field.

int mysql_result(int result, int row, mixed field);

mysql_select_db: Sets the active database on the server. If not specified

the connection identifier uses the last open connection. If there are no active connections,
the function tries to establish one. From the call to mysql_select_db the calls to
mysql_query() operates on the new active database.

int mysql_select_db(string database_name, int [link_identifier] );

true or false in case of error.

 mysql_tablename: Takes a pointer returned by the function as an argument
mysql_list_tables() and returns the name of the table associated with index i. The function
mysql_num_rows() can be used to determine the number of tables.

string mysql_tablename(int result, int i);

CONNECT TO MYSQL FROM PHP

With the following script, we will connect to the MySQL server database to obtain the data from a
registration.

Connection to MySQL

1. <html>

2. <body>

3. <?php

4. $linkp>

5. <?php

6. $link = mysql_connect("localhost", "nobody"); //mysql_connect() opens a connection with

the MySQL server on the specified host (in this case, it is the same machine 'localhost') and
a user ("nobody", "root", etc.). If the connection works, it returns the stored value.
in$link, if it returns 0.

7. mysql_select_db("mydb", $link); // PHP tells the server that in the connection $link, we
we want to connect to the database mydb.

$result = mysql_query("SELECT * FROM agenda", $link); //Performs a query and the

store in $result.

9. echo "Name: ".mysql_result($result, 0, "name")."<br>"; //mysql_result() is used

to display the values of the fields returned by the query ($result).

Address:

Phone:

E-Mail:

13. ?>

14. </body>

15. </html>

Creation of a Database in MySQL

We are going to see two ways to create a database and its tables. To do this, we are going to create the database.
data that will serve as an example in the following chapters:

Command line

mysqladmin create mydb: Creates the database

Create table and add records in mybd

CREATE TABLE agenda (id INT NOT NULL AUTO_INCREMENT, name CHAR(50), address
CHAR(100), phone CHAR(15), email CHAR(50), KEY (id) )\g

INSERT INTO agenda VALUES (0, 'Juan Pérez', 'C/ Laguna, 15. Sevilla', '95.455.55.55',
'[email protected]' )\g

INSERT INTO agenda VALUES (1, 'Luis García', 'C/ Bets, 22. Cádiz', '95.655.66.33',
'[email protected]' )\g

INSERT INTO agenda VALUES (2, 'Carlos Rodríguez', 'C/ Sevilla, 6. Huelva', '95.113.22.77',
'[email protected]' )\g

We must keep in mind that the commands above should be written each on a single line.

It is important to highlight the id field, which cannot be empty and is also auto-incrementable, which
We must take this into account when updating and adding records.

MySQL.

2. Script

Another way to create databases and tables is to use the functions that provide for this.
PHP. To create a database we have two options, one is to use the function
mysql_create_db(), or by sending an SQL query with the instruction "CREATE DATABASE mydb".
As an example, we will create a script create_my.php that will create the previous database.

1. <html>
<body>
2. <?PHP
Hello World
print(CONSTANT);
?>
3. </body>
</html>
4. $database = "mydb";
5. //we connect to the server
6. $link = @mysql_connect("localhost", "root", "");

7. // we check that we have established a connection on the server

8. if (! $link){
 <h2 align='center'>ERROR: Unable to establish connection with the
server
exit;
9. }

10. // we obtain a list of the databases of the server

$db = mysql_list_dbs();

12. // we see how many databases there are

13. $num_bd = mysql_num_rows($db);

14. //we check if the DB we want to create already exists

NO
16. for ($i=0; $i<$num_bd; $i++) {
if (mysql_dbname($db, $i) == $basedatos) {
YES
break;
}
17. }

18. // if it does not exist, we create it

19. if ($exists == "NO") {
/* way 1 */

if (! mysql_create_db($database, $link)) {
<h2 align='center'>ERROR 1: Unable to create database</h2>
data</h2>
exit;
}

way 2
if (! mysql_query("CREATE DATABASE $basedatos", $link)){
<h2 align='center'>ERROR2: Impossible to create database</h2>
data
exit;
} */
20.

21. // we create the table

22. $sql = "CREATE TABLE agenda (";
23. $sql .= "id INT NOT NULL AUTO_INCREMENT, ";
24. $sql .= "name CHAR(50), ";
25. $sql .= "address CHAR(100), ";
26. $sql .= "telephone CHAR(15), ";
27. $sql .= "email CHAR(50), ";
28. $sql .= "KEY (id) ) ";

29. if (@mysql_db_query($database, $sql, $link)) {

 <h2 align='center'>The table has been created successfully</h2>
30. } else {
<h2 align='center'>The table could not be created</h2>
31. }
32. ?>
33. </body>
</html>

Import databases from MS Access

Migrate Access databases from Windows systems to MySQL on Linux.

The steps qut/exportsql/" target="_blank">CYNERGI.

The steps we must follow to install this module and its subsequent use are:

1. Open the Access.mdb file that you want to export.

2. In the database objects window, select 'Modules', and then click on 'New'.
3. Then a new window will open, delete all text (code) that has been written.
4. Copy all the text from Pedro Freire's file and paste it into the new module.
5. Close the module code window, select 'Yes' to save changes and
name the module (e.g. "MexportSQL"). The module is now part of your database
Access.
6. Reopen the module, or click with the mouse on 'Design' with our new module
selected. Move the cursor to where the first word "Functon" appears, and
press F5 or select "Run" from the menu.

The execution of the module will create two files (esql_add.txt and esql_del.txt) in the directory
C:/temp (the one that comes by default, but we can change it). The file that we
interesting esesql_add.txt, which we should take with us in the way we see fit.
Linux machine.

Now we only have to follow the steps we explained in the previous chapter (Starting with
MySQL). First we create the database, and then we input the data.

Show the data from a query

Now that we know how to connect to the DB server, we will see how to display the data by
screen.

Database query

1. <html>
2. <body>
3. <?php
$link = mysql_connect("localhost", "nobody"odigo">$link = mysql_connect("localhost",
nobody
5. mysql_select_db("mydb", $link);
 $result = mysql_query("SELECT name, email FROM agenda", $link);
7. echo "<table border = '1'> \n";
8. echo "<tr> \n";
<td><b>Name</b></td>
<td><b>E-Mail</b></td>
11. echo "</tr> \n";
12. while ($row = mysql_fetch_row($result)){// evaluates the function mysql_fetch_row(), which
returns an array with the content of the current record (which is stored in $row)
echo "<tr> \n";
echo "<td>$row[0]</td> \n";
echo "<td>$row[1]</td> \n";
</tr>
13. }
14. echo "</table> \n";
15. ?>
16. </body>
17. </html>

The function mysql_fetch_row() has a small problem, which is that the array it returns only
it allows numerical references to the fields obtained from the query. The first field
referenced is el0, the second el1 and so on. In the following script we will solve.
this small inconvenience.

Modified database query

1. <html>
2. <body>
3. <?php
$link = mysql_connect("localhost", "nobody&ost", "nobody");
5. mysql_select_db("mydb", $link);
$result = mysql_query("SELECT name, email FROM agenda", $link);
7. if ($row = mysql_fetch_array($result)){
echo "<table border = '1'> \n";
8. echo "<tr> \n";
<td><b>Name</b></td>
10. echo "<td><b>Email</b></td> \n";
11. echo "</tr> \n";
do {
echo "<tr> \n";
<td>
<td>
</tr>
} while ($row = mysql_fetch_array($result));
echo "</table> style=\"margin-left: 50\""><br>echo "</table> \n";
12. } else {
The database is empty!
13. }
14. ?>
15. </body>
 16. </html>

Essentially, this script does the same as the previous one. We store the current record in $row.
with the mysql_fetch_array() function which does exactly the same as mysql_fetch_row(), with the
exception that we can reference the fields by their name ($row["email"]), instead of by a
number.

With the if/else statement, we assign to $row the first record of the query, and in case there is none
none (else) we show a message ("Not found..."). While with the statement
do/while, we ensure that all the records returned by the query are displayed to us
in case there is more than one.

A search engine for our database

We will write a script that serves to search for a certain string (which we will receive from a
form, and we store it in the variable $search), within our database,
specifically within the field 'name'.

First, we will create the input form, we will call it form.htm.

1. <html>
2. <body>
3. <form method = "POST" action = "http://myserver/php/search.php3">
4. <strong>Keyword:</strong>
5. <input type="text" name="search" size="20"><br><br>
6. <input type="submit" value="Search">
7. </form>
8. </body>
9. </html>

The following script will be called buscador.php3, and it will perform the search in the database.

1. <html>
2. <body>
3. <?php
4. if (!isset($buscar)){ //function isset(), checks if a string is empty or not. If the variable
search this empty sends the following message
You must specify a string to search for.
echo &quot
echo <p>Must specify a chain a search
<p><a href=buscador_bd.htm>Back</p>
</html></body>
exit;
5. }
6. $link = mysql_connect("localhost", "nobody");
7. mysql_select_db("mydb", $link);
SELECT * FROM agenda WHERE name LIKE '%$search%' ORDER BY name
9. $result = mysql_query($sql, $link);
10. if ($row = mysql_fetch_array($result)){
echo "<table border = '1'> \n";
 We show the names of the tables
echo "<tr> \ndigo" style="margin-left: 50">echo "<tr> \n";
mysql_field_seek($result, 0);
while ($field = mysql_fetch_field($result)){
<td><b>$field->name</b></td>
}
</tr>
do {
echo "<tr> \n";
echo "<td>" . $row["id"] . "</td> \n";
<td>
<td>
<td>
<td><a
href='mailto:'
href='mailto:'.$row['email'].'>
$row["email"]."</a></td> \n";
</tr>
} while ($row = mysql_fetch_array($result));
<p><a href=buscador_bd.htm>Back</p>
echo "</table> \n";
11. } else {
<p>No records were found!</p>
<p><a href=buscador_bd.htm>Return</p>
12. }
13. ?>
14. </body>
15. </html>

Add records to the database

In this lesson, we will see how we can add new records to our database. The collection of
We are going to do the data through a web interface.

1. <html>
2. <body>
3. <?php
//Script to insert data
4. if ($send) {
// process form
mysql_connect("localhost", "root");
mysql_select_db("mydb", $db);
INSERT INTO agenda (name, address, phone, email)
VALUES ('$name', '$address', '$phone', '$email')
result = mysql_query(sql);
Thank you! We have received your data.
5. }else{
 ?>
//Creation of the form

<form method="post" action="add_reg.php3">

Name :<input type="Text" name="name"><br>
Address:
direccion
Phone:
E-mail
Submit
</form>

6. } //end if
7. ?>
8. </body>
9. </html>

Modify records in MySQL

Option A

1. <html>
2. <body>
3. <?php
4. if (isset($id)){
process form
mysql_connect('localhost', 'root');
mysql_select_db("mydb", $db);
SELECT * FROM agenda WHERE id = $id
$result = mysql_query($sql);
$sql = "UPDATE agenda SET nombre='$nombre', "
$direccion
The result of the query is obtained from executing the SQL statement.

5. }else{
You must specify an 'id'.
6. }
7. </body>
8. </html>

Option B

1. <html>
2. <body>
3. <?php
4. if (icodigo"?php
5. if (isset($id)){
// process form
$link = mysql_connect("localhost", "root");
mysql_select_db("mydb", $db);
 $sql = "UPDATE agenda SET name='$nombre', address='$direccion', ";
phone='$telefono', email='$email' WHERE id=$id
$result = mysql_query($sql);
6. }else{
You must specify an 'id'.
7. }
8. ?>
9. </body>
10. </html>

Delete records

The process of deleting a record is identical to that of modifying, only instead of using UPDATE
We use DELETE in the SQL statement. Therefore, the script would be as follows.

1. <html>
2. <body>
3. <?php
4. <body>
5. <?php
6. if (isset($id)){
// process form
$link = mysql_connect("localhost", "root");
mysql_select_db("mydb", $db);
DELETE FROM agenda WHERE id=$id
mysql_query($sql);
7. }else{
You must specify an 'id'.
8. }
9. ?>
10. </body>
11.