Rad Intro to SQL & Databases

My study of the PHP programming language has taken me to » DATABASES, and how to manipulate them using SQL .. specifically MySQL, since that's the database used by most web-based applications.

MySQL databaseThe data stored in most databases is manipulated using the Structured Query Language (or SQL - pronounced ess-cue-el or see-kwul) to perform functions such as » create, read, update and delete.

It surprised me to hear the importance given to databases. A database has the ability to supercharge a PHP application. Without a database, a PHP application would not be nearly so powerful.

All Content Management Systems (CMS), such as Drupal, Joomla, and MODx .. work in conjunction with (require) a database. So do blogs, such as Movable Type and WordPress.

Back when I interviewed for that job last summer, they asked some very basic SQL questions. And I think it was my lack of experience in this area (more than anything) that disqualified me. They were looking for the ability to craft a basic SQL statement such as:

SELECT * FROM table_name WHERE column_name = 'some_text' ORDER BY column_name ASC;

PHP has long been designed to work seamlessly with databases, and this is one of the things that makes it so powerful.

Databases contain tables. A database is a collection of tables. My installation of Movable Type, for example, has 34 tables. MODx has 57 tables. Drupal has the most with 68. And all these tables are 'related'. Hence the term » 'relational' database.

••• today's entry continues here below •••

Tables contain records (rows, usually numbered). Tables also contain columns, usually labeled with a descriptive heading. Records contain data. Some things I've learned:

  • A record is really just a row. (A column is called a column.)
  • SQL commands are normally issued with ALL-CAPS, but SQL is actually case-INsenitive.

  • The 4 basics SQL functions for working with records are » Create (INSERT INTO), Read (SELECT), Update (UPDATE) & Delete (DELETE).
  • To connect with a MySQL database, PHP uses the mysql_connect command.

  • The upgrade to MySQL 5.1.x (from 5.0.x) is one-way. Can't go back.
  • I can ace the SQL quiz (20 questions) at W3Schools, but that doesn't mean I know SQL.
  • Use 'single quotes' with SQL strings. "Double quotes" won't work.

  • phpMyAdmin is a graphical user interface that makes it easy to work with MySQL databases.
  • WAMP comes with a copy of phpMyAdmin, but you'll have to enter your MySQL password in the file » config.inc.php .. located in the phpMyAdmin directory (located in the apps folder).

Speaking of the MySQL database password, you can update/change the root password from the MySQL console by entering the following commands:

  • use mysql <enter>
  • UPDATE user <enter>
  • SET Password=PASSWORD('EnterYourNewPasswordHere') <enter>
  • WHERE user='root'; <enter>
  • MySQL returns » [Query OK, 1 row affected]
  • MySQL returns » [Query OK, 0 rows affected]

About this Entry

This page contains a single entry by Rad published on May 24, 2010 5:24 AM.

Continuing to Learn PHP was the previous entry in this blog.

Cormac McCarthy & The Road is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.