MySQL data types

Data types can be Numeric type is usually integer "int(x)" where x = # of digits. It is also "unsignes" meaning cannot be negative.

String type is "varchar(x)" where x = # of characters. Varchar is used up to 255 characters, for longer ones use text() or blob().

Tables and Views

Tables contain columns and rows. A row is actually a record in database and contains more fields. Field is spot where row and column intersect.

View is virtual table. View has colums/rows like real table and fields are result from our search, so view can have data from more tables. Deleting view doesn't influance real tables.

Example of creating view (with name = hwcomment_per_unix_team):
mysql> CREATE VIEW hwcomment_per_unix_team AS
    -> SELECT hardware.hwcomment, team.teamname
    -> FROM hardware
    -> INNER JOIN team
    -> ON hardware.team_id = team.team_id
    -> WHERE teamname LIKE 'un%';
Example of deleting view:

DROP VIEW view_name

Relationship between Tables

Each table has one column with Primary Keys. This means that each record (or row) in table has primary key. This is unique integer, setup with NOT NULL and AUTO_INCREMENT.
Each time new row is inserted into table, the primary key increments for 1.

Some colums have primary keys from other tables, they are called foreign keys.

Take a look at tables vendor and vendor_contact.
Vendor table has list of vendors, each vendor has primary key.
Vendor_contact table has list of people, each person has its own primary key.

Person works for vendor and can be more people work for same vendor. So we don't want to repeate vendor name is vendor_contact table.
Instead of that we use vendor primary key.

This is known as one-to-many relationship, since many people work for same vendor.

HTML forms and PHP

HTML forms are used to get input from users. Type of input can be text, radio, checkbox, drop-down menu and "submit".

Once user inputs data and click submit, HTML form uses 'post' method for calling PHP script.
It looks like:
< form action="script_name.php" method=post>
< input type="text" size=50 name="city">
.
.
< input type="submit" value="Submit City" />
< /form>
This will setup PHP array $_POST and assign variable to it:
< ?php
$city = $_POST["city"] ;
?> 

PHP built-in functions for MySQL

Functions for connecting to MySQL Functions for Queries Note:
To get first element in numerical array use $array_name[0].
Element from associative array can be found like $array_name[column_name] or using variable $array_name[$hwname].

Other Functions How everything works together?

For example, create your SQL query and assign to variable.

$select = " SELECT * FROM office ";

Then send query to database using mysql_query() and assign result to variable. Also use die() or exit() for errors.
$result = mysql_query($select) or die ("Sorry, cannot do this query" .mysql_error());
Now use variable $result in previously mentioned functions.

Escape input data with slashes

See magic_quotes_gpc value in /etc/opt/webstack/php/5.2/php.ini file.

By default is Off, meaning PHP doesn't escape quote ('), double quote ("), backslash (\) and NULL with slashes.
These characters have to be escaped when entering data into MySQL.
Since PHP can talk with many different databases, this option is disabled on PHP level, so specific MySQL function should be used. The function is mysql_real_escape_string().

Function trim() strips whitespace from beginning/end of the string.
Our function for "cleaning" input string is checking magic_quotes_gpc value in php.ini file.
If it's ON, function just trim. If it's OFF (should be default), function trim and add slashes.
function clean($string)
{
        #$string=trim($string);
        if (get_magic_quotes_gpc())
                {
                # it is ON
                $string=trim($string);
                }
                else
                {
                # it is OFF
                $string=trim(mysql_real_escape_string($string));
                }
        # return() ends execution of function and return its argument as value of function
        return($string);
}

Sessions in PHP

Session is used to preserve certain data across different pages. Closing browser ends the session. Session is started with function session_start() and must be started before ‹ html › tag.
< ?php 
session_start(); 
?>
< html>
< body>
 ......
< /body>
< /html> 
Variable $_SESSION can be used for storing users information, like username, login time, etc.
For example, username is received from HTML page with post method and stored in $username variable.
That username is stored in sessions variable. Also another session variable saves login time using function time().
...
$username=clean($_POST['username']);
...
$_SESSION['username'] = $username;
$_SESSION['start'] = time();
...
Now when we have session variable with username, we can perform user validation for other pages.
Other pages that require authentication start with code that checks if $_SESSION['username'] is set. If yes, perform code from the page. If not, function die() [or can be used exit() ] sends warrning message and exit the script.
< ?php
# start session
session_start();
if (!isset($_SESSION['username']))
{
die ( " < html>< head>
        < link href=\"format.css\" rel=\"stylesheet\" type=\"text/css\" />
        < /head>
        < body>You are not authorized to see the page, first login.
        < a href=\"userlogin.php\"> Login in < /a>
        < /html>" );
}
else
{
.....


HOME