Archive for the ‘MySql’ Category

« Older Entries

SQL: Constraining the Database

Friday, October 30th, 2009

Constraining what gets entered into a relational database is a good thing. It maintains the data integrity that is so important for database use. You don’t want users to enter the wrong types of information (I.e. letters instead of numbers)

My database class teaches that there are three different ways to constrain input for a database:

1. Declarative Integrity
2. Procedural Application Code
3. Business Procedure

Declarative Integrity means you put restraints directly in the table design. The constraints specify what input the database accepts. More on them below.

When you restrain by using the Procedural Application Code, you put the constraints not in the database tables, but in the programming logic that handles the input of the data (PL/SQL), with actions such as “triggers.”

Business rules or for those cases when you can not (easily) check the integrity of the data by computer, so you put the rules in the employee handbook (“Enter your real birth date, not a false birth date” would be a silly example).

Here’s some basics on Declarative Integrity. When you create a data table, you can add in constraints on what data is accepted. This can be done as part of a column definition, or at the end of the “create table” statement.

Here are the basic types of Integrity Constraints you can use, at least for Oracle databases:

NOT NULL: When an insert is made, a column defined as NOT NULL must be given some data. The NOT NULL declaration goes right after the data type. For instance, when creating the column with the DATE data type, you would add:

[NameOfColumn] DATE NOT NULL,

UNIQUE: UNIQUE requires each new value entered into that column be different from all those value entered before.

PRIMARY KEY: The PRIMARY KEY is the one column that identifies the column from all the others. As such it is considered UNIQUE, meaning each value entered will be different from all the other values entered. However, you don’t use the UNIQUE qualifier when declaring the PRIMARY KEY (it is implied). Here is an example:

[NameOfColumn] [DataType] PRIMARY KEY,

The PRIMARY KEY can be a composite of multiple column entries, which means each each key must be comprised of a unique combination of values from the participating columns. The composite key is defined at the end of the table creation statement, after the last column definition:

constraint [Name_Of_Composite_Primary_Key] PRIMARY KEY ([Name_of_1st_Participating_column], [Name_of_2nd_Participating_column], [...] )

FOREIGN KEY: a FOREIGN KEY uses as its domain of possible values a PRIMARY KEY from another table. This is written as a column definition:

constraint [Name_Of_Foreign_Key] foreign key ([Name_of_External_Column])

references [Name_Of_External_Table]([Name_of_External_Column])

Note, you can not refer to a table in another database, only to another table in the same database. But you can refer to the primary key even in the same table.

The references clause tells the database to delete the dependent row when the corresponding row in the parent table is deleted.

CHECK: The CHECK constraint allows you to specify only certain values can be inserted, as such:

[Name_Of_Column] [Datatype] Check([Name_Of_Column] [operator] [value]),

For example,

Stats VARCHAR2(2) CHECK (Stats => 0)

….means that any values entered for the Stats column must be 0 or higher.

Material taken from this book….



…As well as from a class I’m taking on database design. All mistakes are my own…–Joab Jackson

Tags: , , ,
Posted in MySql, Oracle, data types, database, datatypes | Comments Off

PHP: Post the results of a simple MySql Query on a Web Page

Saturday, October 24th, 2009

Say you want to post the results of a simple query from a MySQL database on a Web page, using PHP. You would think that all you’d need to do is assign a variable name to the results of the SQL query, and then ask PHP to print the variable.

It doesn’t work that way. Instead of PHP printing the result, what gets printed is a mysterious message, like “Resource ID #3″

As explained here, the variable itself points to a place holder of sorts. To get the actual value, you have to use another MySQL function.

In this case that function would be mysql-fetch-row.

For example, within the PHP body of code, you do something like this:

$QueryResult = mysql_query(“select avg(Height) from Boys);

$ResultInBetweenStep = mysql_fetch_row($QueryResult);

$ResultPresent = $ResultInBetweenStep[0];

echo($ResultPresent);

In the above quote, we’re getting the result of a query from a table called Boys that is the average of all the entries in the Height column. It is assigned to the variable $QueryResult.

In order to get the actual data from the query, the function mysql_fetch_row is applied to $QueryResult, and the results are stored in another variable, $ResultInBetweenStep.

The final step is to assign a variable to the first row of $ResultInBetweenStep only (which would be the *only* row in the query, as the average function will return a single number), which, here, is called $ResultPresent.

$ResultPresent can then be printed.

There are other MySql functions that allow you to extract more complex bits of information from a MySQL query. Check the mysql_fetch_* entries here for more info.–Joab Jackson

Posted in MySql, PHP, SQL, Web, database | Comments Off

SQL: Adding a New Column to a Database Table

Sunday, May 24th, 2009

When you have already created a database, and a table, and you need to add another column to the table This is what you add:

ALTER TABLE [TableName] ADD [ColumnName DataType]

To see what you’ve done, use the SHOW COLUMNS command:

SHOW COLUMNS FROM [name of database table]

Taken from >SQL-Tutorial.com and the MySQL Reference Manual. MySQL 5.1 was used here.
–Joab Jackson

Posted in MySql, SQL, database | Comments Off

SQL: Fetching & Parsing with SELECT

Wednesday, May 20th, 2009

The SELECT statement is used in SQL to select data from the database.

Some background first: SQL stands for structured query language. It is the language used for working with a relational database.

SQL commands can be split into two groups. One is for working with the data within the database (called the The Data Manipulation Language, or DML). The other works designing the tables, as well as defining the relationship among them (The Data Definition Language, or DDL).

All of these commands can be used from the command line prompt, once you’ve gained admittance to the Relational Database Management System (that is the MySQL software itself, or whatever RDMS you are using).

SELECT Falls under DML. It is the basic command for pulling data from the database, either directly from the command line, or as part of a Java or PHP connector.

The basic format for SELECT is:

SELECT [column(s)] FROM [table]

(Note for all these blog pages, you fill your own values in the brackets “[ ]“).

So, if you wanted to see all the info in the database “test,” you would write:

SELECT * from test;

(“*” is a wildcard operator. MySQL requires a “;” at the end of each statement, not shown here. Multiple columns can be selected to show, separated by a comma.)

If you just wanted to see one column of data, say the column of numbers called “measure” you would write:

SELECT measure FROM test;

So SELECT is pretty easy, yes? But it is also rather coarse-grained, returning a tree when what you want is a branch. You need to do some more whittlin’!

You build a more precise query from SELECT using additional qualifiers.

The main way of doing this is through the WHERE keyword, which come after the FROM statement that specifies the table. To further qualify you can also append, in various mixes, the BETWEEN, LIKE, IN, AND or OR keywords, along with selected values to filter by.

Finally, you can order the results by ORDER BY and filter them by DISTINCT, niether of which require WHERE, but could be used in conjunction with WHERE.

What follows are the details:

* * *

With WHERE you can pull only those records from a table that meet some “specific criteria,” as the W3C puts it. WHERE is part of a SELECT statement.

SELECT [column] FROM [table] WHERE [column]=[value you seek]

i.e.,

SELECT measure FROM test WHERE measure=0;

In this example, I used the “=” But other conditionals include…

<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal

Now, for some other keywords:

* * *

BETWEEN: BETWEEN returns a range of data specified between two values.

The format for this is:

SELECT [column(s)] FROM [table] WHERE [column]
BETWEEN [lower value] AND [upper value]

The values can be numbers, date or even text! Text is ranked, ascending or descending, by alphabetical order. Here is an example:

SELECT * FROM Bike WHERE Date
BETWEEN 20090501 AND 20090515

…Will return all the data in all the columns in the table Bike that have a date between May 1 and May 15 2009.

* * *

IN: IN specifies specific entries. When you are looking for values within known entries, then IN is what you’d use.

SELECT [column] FROM [table] WHERE [column] IN ([value(s)])

Commas separate multiple values in the IN set. And, as always if the values are text, they should be in single quotes. Example:

SELECT * FROM Bike WHERE RideTime IN [30, 60]

..Returns all the rows in the Bike table where the RideTime column equals 30 or 60.

* * *

LIKE and NOT LIKE: LIKE is a pattern-matcher. You can structure a query with the elements that you know are in a value that you are looking for, with wild card elements both before and after the known-nugget of info.

The format:

SELECT [column(s)] FROM [table] WHERE [column] LIKE [pattern]

For these operations the percentage sign, %, is the wildcard operator. In a hypothetical database, “Turkey%” will return “TurkeyShoot” and “TurkeyBreast” and “%Turkey” will return “unTurkey” and “%Turkey%” will return all three i.e.

NOT LIKE is the same but returns all the results that DON’T match the criteria you select.

SELECT * FROM BirdWords WHERE Fowl LIKE ‘%Turkey%’

NOTE: Single quotes are used also for fetching numerical responses, dig?

* * *

DISTINCT: DISTINCT is actually a precursor to WHERE–It comes before WHERE in the SQL statement and, in fact, can be used without WHERE. It picks out all of the original values in a column, eliminating any duplicates.

SELECT DISTINCT [column] FROM [Table]

* * *

AND/OR An AND or OR statement may be placed after WHERE for further distinction:

SELECT [column(s)] FROM table WHERE [column]=[value1] AND [column]=[value2]

SELECT [column(s)] FROM table WHERE [column]=[value1] OR [column]=[value2]

SELECT [column(s)] FROM [table] WHERE [column]=[value1] AND ([column]=[value2] OR [column]=[value3])

Note, operators other than the “=” could be used. See above. In the final example AND and OR can be combined, with a parenthesis to clarify order of evaluation with the machine. Play around with these for greater nuances, i.e.

SELECT * from Run WHERE Time=60 OR Time=50 AND (Date=20090101 OR Date=20090201);

…Will return all those entries with a date of Jan 1 2009 or Feb 1 2009 in which the Run Time was 50 or 60.

* * *

ORDER BY The ORDER BY specifies how the results of a query should be returned. They results can be returned either in a numerically ascending (or alphabetical), or descending (reverse alphabetical) for whatever column you choose. Default is ascending.

Again, ORDER BY can be done without WHERE. It is placed at the end of the SQL statement:

SELECT [column(s)] FROM [table] ORDER BY [column] [ASC or DESC]

Example:

SELECT * FROM Run ORDER BY Time DESC

…Will return all the entries from the Run table ranked by time, longest time first.

Taken from the W3C School’s tutorial on SQL, as well as from SQLCourse, a helpful site I found on the Web.

–Joab Jackson

Posted in MySql, SQL, Unix, database | Comments Off

PHP: Entering data into MySql with PHP

Tuesday, May 12th, 2009

For some reason, there is very little direct instruction on the Web about how to enter data into a MySQL database from a Web page, using PHP. This dearth of info is surprising insofar as this procedure is one of the most commonly executed actions on the Web.

There are plenty of online tutorials, but for some reason, they tend to be made more complicated than they need to be.

Anyway, forthwith, here is the very basic mechanism used for entering data into a database over the Web.

First gather the ingredients: This tutorial uses Linux for the Web server (Ubuntu 8.04), with Apache running the PHP module, with the MySQL package. Also, MySQL (5.1 in this example) is already set up and running, with all the permissions worked out.

In this example, we will use a new database to be run inside MySQL, called “test.” It will have a single table called “sample” with one column of data text data. What it is called is not relevant. You will also require a username and password for the database.

For this action to happen, we will need TWO web pages, an HTML landing page where the user enters the data, and a PHP page that the HTML page will call that will do the actual work of entering the data into the database. (These can be combined into one page with the “form action = self” html declaration, which I’ll figure out another day).

First, you create an HTML page, where the user can enter data. Instructions on how all this is done are here.

In the code on this page, the user has a box in which some data can be entered, which the page tags as the variable “somedata” when the submit button is pushed. Also, when the submit button is pushed, this page will call up a PHP page in the same directory, called “InsertingIntoDatabase.php” (which we will create in a moment).

<FORM ACTION=”InsertingIntoDatabase.php” METHOD=”get”>
<p>
Enter Data: <INPUT TYPE=”text” NAME=”somedata” />
</p>
<INPUT TYPE=SUBMIT VALUE=”GO” />
</FORM>

Note: For the full working code, click here, and save page, and replace the .txt extension in the file name with a .html extension.

For this setup, I used the HTML “get” method. I could have also used “post.” The differences between the two are explained here.

Now onto the PHP page. Basics on creating a PHP page are here. The PHP code block will have a number of discrete steps:

1. Connect to the database server:

$open = @mysql_connect(“localhost”,”[USERNAME]“, “[PASSWORD]“);
if (!$open) {
echo( “

Can’t connect to database!

” );
exit();
}

In this chunk, you are opening the database.

The @mysql_connect PHP command is the command that does the actual work of opening the database. It requires three things: Type of connection, user name and password. Since the Web page is on the same server as the database, the connection will be “localhost”. You need to enter a username and password for an account that has write privileges to the database you will be using.

You save this command and its arguments as a variable, called $open, in this case. $open is run when the Web page is called. If the connection does not work, (signified by “!$open”) either because the database is not found, or your username or password doesn’t work, then you will get an error message (“Can’t connect to database!”).

2. Gather the data and specify database to be used.

$somedata = $_GET["somedata"];
$db = (“test”);

In this step, you grab the data from the server that was saved by the HTML page under the variable “somedata”. In PHP, it will also be called somedata, as $somedata. You also specify the name of the database within MySQL that you will open. Here it is called “test” but you set the name when you created the database.

3. Prepare the SQL query:

$sql = “INSERT into sample VALUE(‘”.$somedata.”‘) “;

Here is the the actual query you will submit to the database. It is formatted exactly like any other MySQL query. (Note: in SQL speak, even inserting data is called a “query,” evidently).

For this query, we are sending the contents of $somedata to a one-column table called “sample.” (Obviously you need to replace “sample” with the name of your own table, the one that you created when you created your database within Mysql). Note all the query is in quotes, except for the PHP variable $somedata.

In PHP, variables can not be called from within a quoted stream of text. So we are concatenating a string together by breaking the query into two strings, one before the variable (which is placed where it is needed in the statement) and the other after. Concatenating elements together in PHP is done with the period, “.”

Also, MySQL itself (at least the 5.1 version) requires that text data must be entered with SINGLE, not double quotes, hence the single quotes above.

This query is saved in php as a variable, called $sql. It looks complicated, but what PHP is storing is a straight-forward MySQL data insert query.

4. Run the query

Here is the code for doing this..

$doit = mysql_db_query($db, $sql);

The database is already open, so all we need to do is enter the query. The PHP “mysql_db_query” command runs the actual query. It requires two bits of information: First element is the name of the database (captured as a variable called “$db” here). The second element is the SQL-formatted query (“$sql”).

The entire command is captured as the variable “$doit” which, again, runs when the page is called.

5. Report results on the page:

If the query for some reason does not take, we need to alert the user. Likewise, if it works, then we should also report that. This mightbe best handled by the PHP “If … Else” statement:

if (!$doit) {
echo(“It didn’t work because of this: ” .mysql_error());
}
else
echo (“Data entered!”);

Here, if the data was not inserted due to some error (“!$doit”) then the PHP returns a statement saying so (“It didn’t work because of this: “) along with the error message returned by MySQL itself (“mysql_error()”) concatenated onto the end.

If the transaction did work (“else”),a message of success is returned.

6. Close database

mysql_close($open);

And that is how you enter data into a MySQL database using PHP. There are lots more you can do it terms of security, handling of errors, input-checking and so on, but this is the bare-bones approach.

Note: For the full working code for this PHP page, click here, and save page, but replace the .txt extension in the file name with a .php extension. Also replace the [USERNAME] [PASSWORD] and [DATABASENAME] with your own variables.

–Joab Jackson

And now a word from our sponsor:

Posted in MySql, PHP, SQL, Ubuntu, Unix, Web | Comments Off

MySQL: Updating passwords

Thursday, March 5th, 2009

Updating a password can be done within mysql, by root, thusly:

mysql>use mysql;
mysql>update user set password=PASSWORD(‘[Password]‘) where user=’[Name of user]‘;

Note, to change the password from the account you are already in, you do not need to specify user i.e.:

mysql>set password=PASSWORD(‘[password]‘)

The second designation of PASSWORD is actually a function call, one that encrypts, or hashes the password you supply.

Also, keep in mind that, after any password updates you must flush privileges:

mysql>flush privileges;

And jumping back out to the command line and restarting MySql is not a bad idea, either:

mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Info taken from here and here, and, if you really get into trouble, here.

–Joab Jackson

Posted in Linux Server Administration, MySql | Comments Off

MySql: Adding or dropping a new user

Wednesday, March 4th, 2009

Adding a new user is a three-step process. First you add the user account, and then you grant privileges to whatever databases that person will be using.

1. To add a user, you use the Create User command. Log onto Mysql as root, then, at the prompt, type:

mysql>create user [name of new user];

[MYSQL BUG: The documentation says you can add password onto this line, i.e. "identified by '[password]‘ but YOU CAN’T!!! You could spend all morning wondering why your new client can’t log in because of this SNAFU. Instead, you have to add the password in a separate command (see the section on adding passwords). So, step 2 is:

2. Add password for new user (see separate blog entry).

3. To grant database privileges to the user, you use the grant command. First you stipulate the privileges you want to grant, specify the name of the databases these privileges would apply to, and finally whom all this should apply to. Intuitively backwards.

mysql>GRANT ALL ON [databasename].* TO ‘[username]‘@’[somehost]‘;
mysql>GRANT SELECT, INSERT ON *.* TO ‘[someuser]‘@’[somehost]‘;

Note, that somehost specifies where the host is working from, either remotely (over a network) or on the terminal of the machine where mysql resides (localhost). You can also specify specific IP numbers, I think. If you leave @ off altogether, mysql assumes the user can access these privileges from anywhere.

Also note that using a database usually involves working with multiple files, all under the name of the database but with different suffixes. So that it is usually good to use a wildcard for the suffix of these files for grant privileges.

[MYSQL BUG: The '%' wildcard is deceptive, insofar as it does not grant privileges from the local host, as the documentation states. Instead, you have to grant a set of privileges especially for the localhost, i.e. '[someuser]‘@’localhost’.]

* * *

Dropping a user account can be done thusly:

mysql>drop user [Name of user]

Taken from here

–Joab Jackson

Posted in Linux Server Administration, MySql | Comments Off

MySql: How to recover a lost password

Saturday, February 21st, 2009

Locked out of MySQL? here is how you recover access: You stop MySQL. Then, you start MySQL in safe mode, then start the MySQl database itself, set the new password, flush the privileges, exit. Stop and then restart the database in normal mode:

# /etc/init.d/mysql stop
# mysqld_safe –skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

From this compilation of MySQL commands..

–Joab Jackson

Posted in Linux Server Administration, MySql | Comments Off

MySQL: Backing up a database

Sunday, January 11th, 2009

The simplest way to back up a database is to copy the files to another location. The table files have the name suffixes of *.frm, *.MYD, and *.MYI.

With MySQL 5.0 (in Ubuntu), the database files are located at /var/lib/mysql, with each database getting its own directory. Copying could be done thusly:

cp -R /var/lib/mysql/[name of database] /[where you want to put the backup copy]

NOTE: The uppercase -R after the copy (cp) command means the function copies recursively, meaning that it copies all the files and subdirectories and their files under that directory.

Taken from here.

–Joab Jackson

Posted in Backups, Linux Server Administration, MySql | Comments Off

MySQL: Entering and viewing data

Sunday, January 11th, 2009

Inserting data into a table:
mysql> INSERT Into Weight VALUES (20090101, 240);

(Note: It appears that you must enter the data in the order in which the columns were first entered. (Also please note that in MySQL 5.1 the only date format that gets ingested properly is in the format yyyymmdd, with no dashes, periods, etc.)

This will show everything in the database:
mysql> SELECT * FROM Weight;

–Joab Jackson

Posted in MySql, SQL, database | Comments Off

« Older Entries