sql tutorial for beginners,sql server tutorial,sql tutorial pdf,sql tutorials

The Complete Joomla Tutorial Package (Joomla 1.5 Videos Just Added
The Complete Joomla Tutorial Package. 17 Videos, 400 Templates, 75 Module Addons And How To Create Joomla Templates Ebook. Joomla 1.5 Videos Just Added!
The Complete Joomla Tutorial Package (Joomla 1.5 Videos Just Added

Article by Thiyagarajan G

http://tutorial.iclienttech.comPHP IntroductionWhat is PHP?

PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.

PHP stands for PHP: Hypertext PreprocessorPHP is a server-side scripting language, like ASPPHP scripts are executed on the serverPHP supports many databases (MySQL, Informix, Oracle, Sybase, Solid, PostgreSQL, Generic ODBC, etc.)PHP is an open source softwarePHP is free to download and useWhat is a PHP File?PHP files can contain text, HTML tags and scriptsPHP files are returned to the browser as plain HTMLPHP files have a file extension of “.php”

What is MySQL?

MySQL is a database serverMySQL is ideal for both small and large applicationsMySQL supports standard SQLMySQL compiles on a number of platformsMySQL is free to download and use

PHP + MySQL

PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

What do you Need?

What is MySQL?

MySQL is a database serverMySQL is ideal for both small and large applicationsMySQL supports standard SQLMySQL compiles on a number of platformsMySQL is free to download and use

PHP + MySQL

PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

http://tutorial.iclienttech.comPHP IntroductionWhat is PHP?

PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.

PHP stands for PHP: Hypertext PreprocessorPHP is a server-side scripting language, like ASPPHP scripts are executed on the serverPHP supports many databases (MySQL, Informix, Oracle, Sybase, Solid, PostgreSQL, Generic ODBC, etc.)PHP is an open source softwarePHP is free to download and useWhat is a PHP File?PHP files can contain text, HTML tags and scriptsPHP files are returned to the browser as plain HTMLPHP files have a file extension of “.php”

What is MySQL?

MySQL is a database serverMySQL is ideal for both small and large applicationsMySQL supports standard SQLMySQL compiles on a number of platformsMySQL is free to download and use

PHP + MySQL

PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

What do you Need?

What is MySQL?

MySQL is a database serverMySQL is ideal for both small and large applicationsMySQL supports standard SQLMySQL compiles on a number of platformsMySQL is free to download and use

PHP + MySQL

PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

The Legend of Zelda – The Minish Cap

• Challenging puzzles and a new set of enemies to add to the classic Octorocks, Tektites and more
• Story-driven action as you explore the Minish world, with animated cutscenes and interactive dialogues for dozens of characters
• As you play you’ll collect Kinstones to uncover secrets – match them by face and they’ll open up new points of the map, new characters and more

The Legend Of Zelda: The Minish Cap takes young Link into an all-new adventure where he’ll explore Hyrule and battle evil on the microscopic level. An ancient magical sword releases the evil mage Vaati and he causes trouble all over Hyrule. Link has been sent to restore the sword and seal Vaati inside it — but it can only be done by visiting the Minish people and getting their help. Linnk will have to shrink to do that – and the strange Minish Cap will be his guide in this new mirco-world.

List Price: $ 30.99

Price:

Find More Oracle Tutorial Products

The Jack Russell Lover’s Ultimate Guide to Training
This ebook is a complete guide for Jack Russells of all ages and Jrt mix breeds. It includes everything you need to know about JRTs to make sure your dog is well-behaved, learns commands, and so much more! The solutions are all in this e-book!
The Jack Russell Lover’s Ultimate Guide to Training

MySQL is a free to use database program that follows the SQL language guidelines so what you will learn here applies to all databases that will accept a SQL connection which is nearly all of them. A database is a collection of tables, each table is rather like a spreadsheet grid, the columns being called ‘fields’ and the rows being called entries.

The fields have types applied which specifies what sort of information they may contain (text,numerical etc) and this information is used by the database when sorting, storing retrieving and manipulating the data stored. To create a database you have to specify a database name, a user name and a password.

Sometimes this information is put in by yourself via your sites control panel, or sometimes you have to email your web hosting company to do it for you. You then need to know the host name for the mysql installation – usually but not always it is ‘localhost’- its worth asking.

Once a database exists it will be blank, you need to decide what to put in it- and we are ready to rock and roll!

Connecting to a MySQL Database from PHP

In php you must do this at the top of each page that uses MySQL use:

$ dbhandle = mysql_connect(hostname, username, password) or die(“Cannot connect to DB”);

Then you must specify what database (it may be a shared installation with thousands on) you wish to work on:

mysql_select_db(databasename) or die(“Connected but error accessing DB”);

Now PHP has opened an internal connection on your server to the database software.

Creating a MySQL Table

You will only need to do this step once at the start of your project, or when you want to add a new table. This lays down a ‘skeleton’ for the data structure you will use. As an example we will have a simple picture gallery manager. This will be a simple list of pictures. Each will have an ID number, the title, the artist or photographers name,the filename where the picture is stored (as a URL so pictures may be taken from off site),the x dimension, the Y dimension, a comments field and finally the number of times the picture is viewed.

We create the table like this:

$ sql_command = “CREATE TABLE `pictures` (
`id` int unsigned NOT NULL auto_increment,
`title` varchar(50) NOT NULL default ‘Unknown Title’,
`artist` varchar(50) NOT NULL default ‘Unknown Artist’,
`url` varchar(50) NOT NULL default ‘http://www.mygallery.com/pics/unknown.jpg’,
`width` int unsigned NOT NULL default ’100′,
`height` int unsigned NOT NULL default ’100′,
`comments` longtext, `views` int unsigned NOT NULL default ’1′,
PRIMARY KEY (`id`),
KEY `search_title` (`title`),
KEY `search_name` (`artist`), ) TYPE=MyISAM;”;
mysql_query($ sql_command);
if(mysql_error())echo “Error creating pictures table -”.mysql_error();

The first line is the instruction to create the table which we are calling pictures. Then we have a line for each field in the table. This specifies the field name, the type of field, the keywords NOT NULL means that the database checks when an entry is created that it is not empty and then the default value is entered if it is. Its a very good idea to use not null and default values wherever possible.

The first field (id) is specified as auto_increment – this means that each time a new entry is created in the table this id number will increase by one automatically. The Comments field is specified as longtext which means you could have the entire contents of war and peace in there, or nothing. The database will handle both extremes. The other text fields are specified as varchar(50) – a max 50 chars long string. You will have to police the length in your PHP program.

Now we have Key definitions. The primary key is the default way that you will usually look up each picture – and it will only ever return a single line because it must be unique for each line. We have set it to ‘id’. So that we can easily list and search by title and artist we have added two other ordinary keys as well called search_title and search_artist. Then we have a TYPE statement and you should never need anything other than MyISAM unless you have a big book about MySQL!

We send the command to the database using mysql_query and check for and report any errors. Thats it! You created a table. Note that its important to use capitals for the commands – the old joke is that SQL is deaf and you have to SHOUT!

Adding an Entry to a MySQL table

To add an entry first you get the data from form fields using $ _POST['fieldname'] or calculate them from your program, retrieve them from the net or whatever. Next you check the lengths of any strings and filter for any harmful characters (especially if the input comes from a public form!). This will filter out bad characters:

For all integer values: $ value=intval(0+$ value);
For all floating numbers: $ value=(intval(10000*(0+$ value)))/10000;rounds off to 5dp
For all strings: Sstring=mysql_real_escape_string($ string);

Having done this you would use:

$ sql = “INSERT INTO pictures (title,artist,url,width,height,comments,views)
VALUES(‘$ title’,'$ artist’,'$ picurl’,`$ picwidth`,’$ picheight’,$ comments,’0′}”;
mysql_query($ sql);
if(mysql_error())echo “Error adding row $ title -”.mysql_error();

Assuming the PHP variable $ title etc were what you stored the data in.

Retrieving an Entry from a MySQL table

If we want just one entry and we know what the id of the picture is (we stored it in $ picid) then:

$ sql = “SELECT * FROM pictures WHERE id=’$ picid’”;
$ result = mysql_query($ sql) or die(mysql_error());
$ row = mysql_fetch_array($ result)

We can then acess the returned data from the $ row array using field names
$ title=$ rows['title'] and so on

Retrieving multiple Entries from a MySQL table

We can retrieve the whole table by leaving out the where clause:

$ sql = “SELECT * FROM pictures”;
$ result = mysql_query($ sql) or die(mysql_error());
$ html=’All pictures in our gallery-”;
while($ row = mysql_fetch_array($ result)){
$ html.=”$ rows['title'] $ rows['artist']“;
}

and show all our pictures… or if we wanted to show them sorted by artist:

$ sql = “SELECT * FROM pictures ORDER BY artist”;

The rest of the code is the same as above. Order by can be order by any field so if we wanted to show them sorted by popularity:

$ sql = “SELECT * FROM pictures ORDER BY views”;

instead!

Deleting an entry or entries from a MySQL table

$ sql=”DELETE FROM example WHERE id=’15′”; would remove one row whose id was 15
$ result = mysql_query($ sql) or die(mysql_error());

or

sql=”DELETE FROM example WHERE artist=’Dudley Dogood’”; removes all pictures by Dudley Dogood $ result = mysql_query($ sql) or die(mysql_error());

Modifying an entry in a MySQL Table

Each time we display a picture in our example we would have to update the number of views. Since we just displayed it we would know the picid and number of views existing so:

$ views++;
$ sql=(“UPDATE pictures SET views=’$ views’ WHERE id=’$ picid’”);
$ result = mysql_query($ sql) or die(mysql_error());

Thats all you need to know to get a simple application up and running! Have fun… A link back would be appreciated if you found this useful. Malcolm at Webmaster Alpha

PLEASE, watch this video in high def here www.youtube.com its much better, and make sure to subscribe to my channel! thenewboston.com

Pro MySQL (Expert’s Voice in Open Source)

The MySQL database server continues to enjoy an aggressive development schedule, with new features and enhancements released on a regular basis. Most notably, the changes to version 4 and the forthcoming version 5 are among the most significant in the history of the project, and serve to further extend the already considerable capabilities this popular open source database server offers its millions of users worldwide. MySQL 5 is expected to be released in late spring/early summer 2005. Pro MyS

List Price: $ 49.99

Price: $ 24.59