Work with database and SQL basics in PHP

CODE:

<?PHP
$result=pg_query($connection,"CREATE TABLE sklad (no smallint, name varchar(100), price integer, last integer);");
?>

$ result - a link to the data with the result of the query. more on that later.
$ connection - a link to our connection, from the pg_connect function.

So, the table is created. It now remains to fill it with data. For this there is an INSERT construct:

INSERT INTO table VALUES (val1, val2, ...);
table - the name of the table
val1, val2, ... is the field value of the added record, the number of values ​​for this type of query must be equal to the number of fields in the table.

Add data to our table:
INSERT INTO sklad VALUES (3, 'Monitor', 200.2);
As you can see from the example, all string and character types, as well as several special types (inet, macaddr), should be in single characters.
quotes.

Now let's imagine that our table consists of 10, or maybe 20 fields, and we need to add a row with only 2-5 filled. What to do? Is it possible to specify a bunch of empty values? Of course not! To do this, use the construction of the form:

INSERT INTO table (col1, col2, ..., coln) VALUES (val1, val2, ..., valn);
Here, immediately after the table name in brackets we write the names of the fields for which we will set values. In this case, the amount
values ​​must match the number of fields declared. For example:

INSERT INTO sklad (no, name) VALUES (3, 'Monitor');
We added an entry with the values ​​set to no = 3 and name = 'Monitor'. The rest of the fields turned out to be NULL.

Now it's time to send this request using the pg_query function already known to us:

<?PHP
$result=pg_query($connection,"INSERT INTO sklad (no,name) VALUES (3,'Монитор');");
?>

Consider a situation where the value of NULL does not suit us at all, and we need to make another value by default.
Then you need to create a table in a slightly different way:
CREATE TABLE name (col1 type DEFAULT val, col2 type DEFAULT val, ...);
where val is the default value for the corresponding fields.

The request to create our table will change to:
CREATE TABLE sklad (
no smallint,
name varchar (100),
price integer DEFAULT 0,
last integer DEFAULT 0 NOT NULL
);

This means that the no and name fields are empty by default, i.e. they must be explicitly specified, and the price and last fields will default to 0. But the price field can be set to NULL, and last is not. This is achieved using the NOT NULL prefix.


Read data.

We learned how to create a table, write data to it, now we will learn how to get data from a table. Here it is necessary to study SQL a little longer. Despite the vastness of each and considered parts. This is the most commonly used. Many options for reading and processing just need to consider.

So for reading the data using the SELECT query:
SELECT col1, col2, ..., coln FROM table;
returns us the fields col1, col2, ..., coln for all records.

If we need to return all fields, then instead of field names we will write simply * (asterisk):
SELECT * FROM table;
And again, we are confronted with the question: what to do if you don’t need to select all of the records? For this again there is a query option:
SELECT * FROM table WHERE exp;
where exp is any logical expression. eg:
SELECT * FROM table WHERE price> 50;
Returns to us all the fields of records whose price field is more than 50.

It is also possible to sort the result by any column. It's enough to add to the end of the previous query
"ORDER BY col" ,
where col is the column name. To reverse sorting (from the largest to the smallest), you must add the DESC prefix to the end:
SELECT * FROM table ORDER BY price DESC; - returns all records from the higher price to the lower.

$ result = pg_query ("SELECT * FROM sklad;");

Returns data in $ result. But print $ result; nothing good will not show us. For further work with the data obtained, they must be processed. I will give the most commonly used, in my opinion, treatment option:

<?PHP
while ($db=pg_fetch_array($result))
{
$no=$db['no'];
$name=$db['name'];
$cena=$db['price'];
$ost=$db['last'];
print "
Номер: $no
Название: $name
Цена: $cena
Остаток: $ost
";
}
?>

These examples will display the contents of our table in a readable form. For this, the pg_fetch_array function was used.
pg_fetch_array takes as an argument a variable resource with the result of the query and returns an associative array with the first record and moves the pointer to the next one, just like the pop function does with the array.

Consider another example:

<?PHP
$rows=pg_num_rows($result);
for ($i=0;$i<$rows;$i++)
{
$no=pg_result($result,$i,'no');
$name=pg_result($result,$i,'name');
$cena=pg_result($result,$i,'price');
$ost=pg_result($result,$i,'last');

print "
Номер: $no
Название: $name
Цена: $cena
Остаток: $ost
";
}
?>

This example will output exactly the same thing as the previous one, but it uses other functions.
The first is a very useful pg_num_rows , as an argument takes a variable with the result of the query. And returns the number of records returned by the request.