 |
Submit Your Code | |
 |
SEO Monitor | |
 |
UpTime Monitor | |
 |
PHP Code Contest | |
 |
My Favorite Examples | |
|
My Favorite Articles | |
 |
Update Your Profile | |
|
|
| More Weber Sites | ||
| PHP Code Search | ||
| Web Development Forums | ||
| Learn MySQL Playing Trivia | ||
| PHPBB2 Templates | ||
| Web Development Index | ||
| PHP Web Logs (BLogs) | ||
| Web Development Resources | ||
| Web Development Content | ||
| Recommended Links | ||
| PHPClasses | ||
| PHP Editor | ||
| PHP Jobs | ||
| Vision.To Design | ||
| Ajax Tutorials | ||
| PHP Programming Help | ||
| PHP/MySQL Programming | ||
| Webmaster Resources | ||
| Webmaster Forum | ||
| XML meta language | ||
| website builder | ||
| Recommended | ||
| Submit Site | ||
| Forex Trading Online forex trading platform | ||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Code | |||||
MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. If you are new to Regular Expressions, first checkout .. http://www.netrpx.com/tutorials/php/regularexp.php http://www.netrpx.com/tutorials/php/regexpposix.php This article will take you through .... -connecting-disconnecting -Creating and Selecting a Database -MySQL Column Types -Creating Tables -Populating Tables -Retrieving Information from table ---Selecting all Rows ---Selecting Particular Column ---Sorting Rows ---Counting Rows ---Using Multiple Tables connecting-disconnecting This is going to assume that you already have MySQL setup.To connect to MySQL sever you need to provide hostname, userid and password.Once you have proper parameters you should be able to connect like this .. c:\mysql\bin\mysql.exe -h localhost -u usedid -p Enter Password : ****** Welcome to MySQL Monitor. Command end with ; or \g. Your MySQL connection id is 3 to server version: 3.23.46-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. After you connect sucessfully you can disconnect anytime by typing QUIT any time mysql>quit bye To see a list of option provided by MySQL use mysql>help or use mysql>? or use mysql> \h *Creating and Selecting Database To create a database insert command line .. mysql> create database testing; Under unix Database names are case sensitive, under windows this restriction does not apply. You have to explicitly select database to use it by typing .. mysql>use testing database changed If you would try to create a database which already exists, MySQL will show an error. To avoid it create database with [if not exists] option. mysql> create database if not exists testing; *MySQL Column Types Mainly MySQL supports three type of column types. 1) String or Character Type. 2) Date and Time Type. 3) Numeric Types. I am just writing most used types. TINYINT - A very small integer. The signed range is -128 to 127 and unsigned range is 0 to 255. SMALLINT - A small Integer. Range -32768 to 32767. MEDIUMINT - A Medium Size Interger. Range -8388608 to 8388607 INT - A Normal Size Integer The signed range is -2147483648 to 2147483647. INTEGER - Synonym for INT. Note .: if you specify ZEROFILL for a column, MySQL will automatically add the UNSIGNED attribute to the column. FLOAT[(M,D)] - A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number. DOUBLE[(M,D)] - A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E- 308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. The M is the display width and D is the number of decimals. DOUBLE without an argument or FLOAT (X) where 25 <= X <= 53 stands for a double-precision floating-point number. DATE - A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format. DATETIME - A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. TIME - A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format. YEAR[(2|4)] - A year in 2- or 4-digit format (default is 4-digit). The allowable values are 1901 to 2155, 0000 in the 4-digit year format, and 1970-2069 if you use the 2-digit format (70-69). VARCHAR(M) - A variable-length string. The range of M is 1 to 255 characters. ENUM('value1','value2',...) An enumeration. An ENUM can have a maximum of 65535 distinct values. SET('value1','value2',...) - A set. A string object that can have zero or more values. A SET can have a maximum of 64 members *Creating Tables You have to decide what table you need and what column will be in each of them. You will want a table that contains a record for each of your product(medicine) you manufacture. Atleast it should contain product id (pr_id), product name (pr_name) , product batch(pr_batch), product expiry date(pr_exp),food suppliment flag (is_it_foodsuppl). VARCHAR is a good choice for product name, product batch . You can pick length from 1 to 255.You can use INT for product id , DATE for Expiry Date and ENUM for food suppliment flag. Use CREATE TABLE statment to specify layout of your table. mysql> create table productinfo (pr_id int(5) NOT NULL, pr_name varchar(32) NOT NULL, pr_batch varchar(25), pr_exp DATE, is_it_suppl enum ('y','n') ); Query OK, 0 rows affected (0.01 sec) NOT NULL/NULL - Signifies whether or not the column can be NULL. Now you have created table productinfo now type show tables to see your table in database. mysql> show tables; +--------------------+ | Tables_in_testing | +-------------------+ | productinfo | +-------------------+ 1 row in set (0.00 sec) Its now time for verifying the productinfotable. mysql> describe productinfo; +-------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+-------+ | pr_id | int(5) | | | 0 | | | pr_name | varchar(32) | | | | | | pr_batch | varchar(25) | YES | | NULL | | | pr_exp | date | YES | | NULL | | | is_it_suppl | enum('y','n') | YES | | NULL | | +-------------+------------+------+-----+---------+-------+ 5 rows in set (0.06 sec) *Populating Tables If you are inserting one record at a time, INSERT INTO Statement is very useful. mysql> insert into productinfo values (1, 'Ampoxin 250 mg inj', 'A00122', '2004-02- 12', 'n'); Query OK, 1 row affected (0.05 sec) You can Insert NULL Directly to represent a missing value. To populate productinfo table from a product.txt file use following .. mysql> LOAD DATA LOCAL INFILE "product.txt" INTO TABLE productinfo; *Retrieving Information from table Selecting all Rows Use SELECT statement to pull data out of table.The general format of SELECT is ... SELECT stuff-to-select FROM table-name WHERE conditions. Stuff to select could be column list. If you want to select all columns use "*" instead. mysql> select * from productinfo; +-------+--------------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------------+----------+------------+-------------+ | 1 | Ampoxin 250 mg inj | A00122 | 2004-02-12 | n | | 2 | Ampoxin 500 mg inj | A0024 | 2004-08-12 | n | | 3 | Ampoxin 1gm inj | Adf122 | 2005-02-12 | n | | 4 | Ferrodol Food | F122 | 2001-02-12 | y | | 5 | Sharkaferrol | Ff122 | 2007-02-12 | y | +-------+--------------------+----------+------------+-------------+ 5 rows in set (0.20 sec) Selecting A Particular Row To select a particular row use WHERE clause mysql> select * from productinfo where pr_id=4; +-------+---------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+---------------+----------+------------+-------------+ | 4 | Ferrodol Food | F122 | 2001-02-12 | y | +-------+---------------+----------+------------+-------------+ 1 row in set (0.00 sec) If you want to know which product has expiry date > current date mysql> select * from productinfo where pr_exp>=now(); +-------+--------------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------------+----------+------------+-------------+ | 1 | Ampoxin 250 mg inj | A00122 | 2004-02-12 | n | | 2 | Ampoxin 500 mg inj | A0024 | 2004-08-12 | n | | 3 | Ampoxin 1gm inj | Adf122 | 2005-02-12 | n | | 5 | Sharkaferrol | Ff122 | 2007-02-12 | y | +-------+--------------------+----------+------------+-------------+ 4 rows in set (0.00 sec) you can also use mysql> select * from productinfo where pr_exp>="2002-04-30"; To combine conditions use AND , OR Operators. mysql> select * from productinfo where pr_exp>=now() and is_it_suppl='y'; +-------+--------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------+----------+------------+-------------+ | 5 | Sharkaferrol | Ff122 | 2007-02-12 | y | +-------+--------------+----------+------------+-------------+ 1 row in set (0.02 sec) You can also use SELECT as calculator like .. mysql> select 5*6; +-----+ | 5*6 | +-----+ | 30 | +-----+ 1 row in set (0.00 sec) to check the version number use ... mysql> select version(); +------------+ | version() | +------------+ | 3.23.46-nt | +------------+ 1 row in set (0.00 sec) Likewise you can print Current_Date, User, Now. Selecting Particular Column mysql> select pr_name,pr_batch from productinfo; +--------------------+----------+ | pr_name | pr_batch | +--------------------+----------+ | Ampoxin 250 mg inj | A00122 | | Ampoxin 500 mg inj | A0024 | | Ampoxin 1gm inj | Adf122 | | Ferrodol Food | F122 | | Sharkaferrol | Ff122 | +--------------------+----------+ 5 rows in set (0.00 sec) To pull out unique output use DISTINCT keyword. mysql> select distinct is_it_suppl from productinfo; +-------------+ | is_it_suppl | +-------------+ | n | | y | +-------------+ 2 rows in set (0.01 sec) Sorting Rows To sort a result use ORDER BY Clause. mysql> select pr_name from productinfo order by pr_name; +--------------------+ | pr_name | +--------------------+ | Ampoxin 1gm inj | | Ampoxin 250 mg inj | | Ampoxin 500 mg inj | | Ferrodol Food | | Sharkaferrol | +--------------------+ 5 rows in set (0.00 sec) To sort result in reverse order use DESC mysql> select pr_name from productinfo order by pr_name desc; +--------------------+ | pr_name | +--------------------+ | Sharkaferrol | | Ferrodol Food | | Ampoxin 500 mg inj | | Ampoxin 250 mg inj | | Ampoxin 1gm inj | +--------------------+ 5 rows in set (0.00 sec) Counting Rows You might want to know how many food suppliment product you have ? You can use COUNT keyword. mysql> select count(*) from productinfo where is_it_suppl='y'; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.03 sec) If you want to count products grouped by food suppliment tag use GROUP BY CLAUSE. mysql> select is_it_suppl, count(*) from productinfo group by is_it_suppl; +-------------+----------+ | is_it_suppl | count(*) | +-------------+----------+ | y | 2 | | n | 3 | +-------------+----------+ 2 rows in set (0.00 sec) *Pattern Matching Use the LIKE or NOT LIKE comparision operators. mysql> select * from productinfo where pr_name like "a%"; +-------+--------------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------------+----------+------------+-------------+ | 1 | Ampoxin 250 mg inj | A00122 | 2004-02-12 | n | | 2 | Ampoxin 500 mg inj | A0024 | 2004-08-12 | n | | 3 | Ampoxin 1gm inj | Adf122 | 2005-02-12 | n | +-------+--------------------+----------+------------+-------------+ 3 rows in set (0.00 sec) Use _ to match any single character and % to match an arbitrary number of characters (including zero characters). To find name ending with INJ use following ... mysql> select * from productinfo where pr_name like "%inj"; +-------+--------------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------------+----------+------------+-------------+ | 1 | Ampoxin 250 mg inj | A00122 | 2004-02-12 | n | | 2 | Ampoxin 500 mg inj | A0024 | 2004-08-12 | n | | 3 | Ampoxin 1gm inj | Adf122 | 2005-02-12 | n | +-------+--------------------+----------+------------+-------------+ 3 rows in set (0.01 sec) To find name containing x: mysql> select * from productinfo where pr_name like "%x%"; +-------+--------------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------------+----------+------------+-------------+ | 1 | Ampoxin 250 mg inj | A00122 | 2004-02-12 | n | | 2 | Ampoxin 500 mg inj | A0024 | 2004-08-12 | n | | 3 | Ampoxin 1gm inj | Adf122 | 2005-02-12 | n | +-------+--------------------+----------+------------+-------------+ 3 rows in set (0.01 sec) To find batch no containing exacly six chars : mysql> select * from productinfo where pr_batch like "______"; +-------+--------------------+----------+------------+-------------+ | pr_id | pr_name | pr_batch | pr_exp | is_it_suppl | +-------+--------------------+----------+------------+-------------+ | 1 | Ampoxin 250 mg inj | A00122 | 2004-02-12 | n | | 3 | Ampoxin 1gm inj | Adf122 | 2005-02-12 | n | +-------+--------------------+----------+------------+-------------+ 2 rows in set (0.00 sec) Using Multiple Tables To use multiple table create a new table priceinfo. Column Names : pr_id - INT(5) pr_price - FLOAT(5,2) mysql> create table priceinfo (pr_id int(5), pr_price float(6,2)); Query OK, 0 rows affected (0.07 sec) Insert prices for Products and view all records. mysql> select * from priceinfo; +-------+----------+ | pr_id | pr_price | +-------+----------+ | 1 | 20.22 | | 2 | 21.22 | | 3 | 35.90 | | 4 | 10.82 | | 5 | 16.93 | +-------+----------+ 5 rows in set (0.00 sec) Now if you want to print Product ID, Name and Price you can use following ... mysql> select productinfo.pr_id, productinfo.pr_name, priceinfo.pr_price oductinfo, priceinfo where priceinfo.pr_id = productinfo.pr_id; +-------+--------------------+----------+ | pr_id | pr_name | pr_price | +-------+--------------------+----------+ | 1 | Ampoxin 250 mg inj | 20.22 | | 2 | Ampoxin 500 mg inj | 21.22 | | 3 | Ampoxin 1gm inj | 35.90 | | 4 | Ferrodol Food | 10.82 | | 5 | Sharkaferrol | 16.93 | +-------+--------------------+----------+ 5 rows in set (0.00 sec) | |||||
| Related Code Examples | |||||
