SakhaliaNetHome PageMilitary HistoryVorKutaAcceptance of cookiesAcceptance of cookies

PHP Tutorial :: SQL reference (II)

Reference of SQL

Common SQL queries - SELECT

The command SELECT retrieves data from the database.

Example: Retrieve the values of dish_name and price for all the rows in the table dishes

SELECT dish_name, price FROM dishes

Example: Retrieve all the content from the table dishes

SELECT * FROM dishes

Example: Retrieve name and price of the dishes whose price is greater than 5.00

SELECT dish_name, price FROM dishes WHERE price > 5.00

Example: Retrieve the price of the dishes whose name exactly matches "Walnut Bun"

SELECT price FROM dishes WHERE dish_name = 'Walnut Bun'

Example: Retrieve the name of the dishes whose price is between 5.00 and 10.00

SELECT dish_name FROM dishes WHERE price >= 5.00 AND price <= 10.00

Example: Retrieve the name and price of the dishes whose price is between 5.00 and 10.00 or the dishes whose name exactly matches "Walnut Bun" (at any price)

SELECT dish_name, price FROM dishes WHERE (price >= 5.00 AND price <= 10.00) OR dish_name = 'Walnut Bun'

Reference of SQL

Common SQL queries - ORDER BY and LIMIT

Rows in a table don't have any inherent order. The command ORDER BY specifies the order in which rows will be arranged when a query retrieves them. This command will not alter the order of the rows in the table, it affects only the display of the results retrieved by the query. By using the command LIMIT in addition to BY ORDER, we can retrieve only a desired number of rows instead of all the available rows that comply with the conditions of the query.

Example: Retrieve the names of the dishes ordered by price in ascending order

SELECT dish_name from dishes ORDER BY price

Example: Retrieve the names of the dishes ordered by price in descending order

SELECT dish_name from dishes ORDER BY price DESC

Example: Retrieve the names of the dishes ordered by price in descending order. If two or more rows have the same price, order them by name

SELECT dish_name from dishes ORDER BY price DESC, dish_name

Example: Select the dishes ordered by price, but retrieving only the first row (therefore, the cheapest dish)

SELECT * FROM dishes ORDER BY price LIMIT 1

Example: Select the name and price of the dishes ordered by name, but retrieving only the first 10 rows

SELECT dish_name, price FROM dishes ORDER BY dish_name LIMIT 10

Reference of SQL

SQL wildcards

Wildcards are useful for matching text in an inexact way, such as searching strings that end with .edu or contain @.SQL. There are two wildcard characters: the underscore (_), that matches a single character and the percentage sign (%) that matches any number of characters. Wildcards are active inside the strings affected by the operator LIKE in a WHERE clause.

Example: Retrieve all rows in which dish_name begins with "D"

SELECT * FROM dishes WHERE dish_name LIKE 'D%'

Example: Retrieve rows in which dish_name is "Fried Cod", "Fried Bod", "Fried Nod", and so on

SELECT * FROM dishes WHERE dish_name LIKE 'Fried _od'

Wilcards are active also in the WHERE clauses for the UPDATE and DELETE commands.

Example: Duplicate the price of all the dishes that have the word "chili" in their name

UPDATE dishes SET price = price * 2 WHERE dish_name LIKE '%chili%'

Example: Delete all the rows whose dish_name ends with "Shrimp"

DELETE FROM dishes WHERE dish_name LIKE '%Shrimp'

For matching with a verbatim % or _ when using the operator LIKE, place a backslash before these characters.

Example: Retrieve all the rows whose dish_name contains "50% off"

SELECT * FROM dishes WHERE dish_name LIKE '%50\% off%'

Without the backslash, this query would match any row that contains "50" followed by a blank space and "off" in some place later in the name, such as "Spicy 50 shrimp with shells off salad" or "Famous 500 offer duck".

Reference of SQL

Basic MySQLi functions

MySQLi is an extension available for MySQL 4.1.2 or superior and for PHP 5, that serves as an interface to manage databases thru diverse functions. Here are listed the most basic ones:

mysqli_connect(DSN): Connects with the database, returning a database object that allows to send queries to the database. Example: $db = mysqli_connect('insert_hostname_here','insert_username_here', 'insert_password_here','insert_databasename_here')

mysqli_connect_error(): Returns an error message if the connection to the database was unsuccessful. Example: if (! $db) { die("Can't connect: " . mysqli_connect_error()); }

mysqli_query($db, SQL): Returns the result of a query sent to the database. Example: $sql = 'SELECT dish_name FROM dishes_2'; $q = mysqli_query($db, $sql)

mysqli_fetch_row($q): Returns a row from a table as a numerically indexed array. Example: $row = mysql_fetch_row($q)

mysqli_fetch_assoc($q): Returns a row from a table as a string-indexed array. Example: $row = mysql_fetch_assoc($q)

mysqli_fetch_object($q): Returns a row from a table as an object. Example: $row = mysqli_fetch_object($q)

mysqli_affected_rows($db): Returns how many rows have been affected by an UPDATE or DELETE command. Example: $rows = mysqli_affected_rows($db)

mysqli_num_rows($q): Returns how many rows returns a SELECT command. Example: $rows = mysqli_num_rows($q)

mysqli_error($db): Returns an error message if a query or a call to other function were unsuccessful.