2018-11-25_mysql_popular_commands_in_php

<?php

/**************************************************
*
* -------------------------------------------------
* MySQL Basics for PHP
* -------------------------------------------------
* Copyright 2001 Mike Gieson
* http://www.gieson.com/
*
*
**************************************************/




// Setup
$MY_CONFIG['host']      = "localhost";
$MY_CONFIG['adminUser'] = "root";
$MY_CONFIG['adminPwd']  = "hackme";
$MY_CONFIG['db']        = "myCoolDatabaseName";
$MY_CONFIG['tableName'] = "music";


// NOTE: There are a number of "Helper" functions at the bottom of this script that:
// - help make the code a bit more streamlined
// - allow for this script to run scoothly.
// - allow for the printout to look smashing in your browser.


// This sets up the HTML CSS styleshoot on the "page"
printStyles();

/***********************************************/
print "<h1>PHP MySQL Test Script</h1>";
print "<p>Use this script to familiarize yourself with how to work with MySQL through PHP.</p>";
print '<p><a href="http://www.gieson.com/">Copyright 2011. Mike Gieson ( www.gieson.com )</a></p>';
/************************************************/



/***********************************************/
print "<h1>YOUR CONFIGURATION</h1>";
/************************************************/
print "<pre>";
print 'host:        '.  $MY_CONFIG['host']          ."\n";
print 'adminUser:   '.  $MY_CONFIG['adminUser']     ."\n";
print 'adminPwd:    '.  $MY_CONFIG['adminPwd']      ."\n";
print 'db:      '   .   $MY_CONFIG['db']            ."\n";
print 'tableName:   '.  $MY_CONFIG['tableName']     ."\n";
print "</pre>";

/***********************************************/
print "<h1>CREATE DATABASE</h1>";
/************************************************/




// Connect
$link = mysql_connect($MY_CONFIG['host'], $MY_CONFIG['adminUser'], $MY_CONFIG['adminPwd'])
    or die("<BR>Unable to connect. Check username and passowrd.<BR>".mysql_error());

/*
// Traditionally we would follow immediately with:
mysql_select_db($MY_CONFIG['db'], $link)
    or die(mysql_error());
// But for this example, we need to first check to see if the DB
// was created, since we're doing everything in one-fell-swoop.
*/

$db_select = mysql_select_db($MY_CONFIG['db'], $link);
if(!$db_select){
    // This is our actual command to create a DB:
    $sql = 'CREATE DATABASE IF NOT EXISTS '.$MY_CONFIG['db'];
    $result = do_query($sql);
    if(!$result){
        die(mysql_error());
    }
} else {
    print "<p>Database already exists.</p>";
}

// Traditionally, this goes just after mysql)connect,
// but since we've just created the DB, we need to put it here
mysql_select_db($MY_CONFIG['db'], $link)
    or die(mysql_error());


/***********************************************/
print "<h1>CREATE TABLE</h1>";
/************************************************/


$sql = "DROP TABLE IF EXISTS ". $MY_CONFIG['tableName'];
do_query($sql);

$sql = 'CREATE TABLE IF NOT EXISTS `'.$MY_CONFIG['tableName'].'` (
`id` int(8) unsigned NOT NULL,
`artist` text,
`album` text,
`title` text,
`track` int(8) unsigned NULL,
`year` int(8) unsigned NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `artist` (`artist`),
FULLTEXT KEY `album` (`album`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;';

do_query($sql);








/***********************************************/
print "<h1>ALTER TABLE</h1>";
/************************************************/

$sql = 'ALTER TABLE ' . $MY_CONFIG['tableName'] . ' MODIFY id int(8) unsigned NOT NULL AUTO_INCREMENT;';
do_query($sql, $link);






/***********************************************/
print "<h1>INSERT STATEMENTS</h1>";
/************************************************/

$Adata = Array();
$Adata['id']        = "1";
$Adata['artist']    = "the beatles";
$Adata['album']     = "Abbey Road";

$sql = "INSERT INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") .") VALUES (" . arrayToSQLString($Adata, "value") . ")";
do_query($sql);
show_table();


// Not including "artist"
$Adata = Array();
$Adata['id']        = "2";
$Adata['album']     = "Abbey Road 2 test";

$sql = "INSERT INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") .") VALUES (" . arrayToSQLString($Adata, "value") . ")";
do_query($sql);
show_table();


// Not including "id" or "artist"
// the "id" is set to "auto increment" so the ID is automatically chosen by MySQL
$Adata = Array();
$Adata['album']     = "Abbey Road 3 test";

$sql = "INSERT INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") .") VALUES (" . arrayToSQLString($Adata, "value") . ")";
do_query($sql);
show_table();









/***********************************************/
print "<h1>REPLACE STATEMENTS</h1>";
/************************************************/

$Adata = Array();
$Adata['id'] = "2";
$Adata['artist'] = "The Beatles";
$Adata['album'] = "Let It Be";

$sql = "REPLACE INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") . ") VALUES (" . arrayToSQLString($Adata, "value") . ")";
do_query($sql);
show_table();


// Replace also acts as insesrt if the primary key is not defined. And since the primary key is set to auto increment, MySQL must treat this as a new insert.
$Adata = Array();
$Adata['artist'] = "The Beatles";
$Adata['album'] = "The Magical Mystery Tour";

$sql = "REPLACE INTO " . $MY_CONFIG['tableName'] . " (" . arrayToSQLString($Adata, "key") . ") VALUES (" . arrayToSQLString($Adata, "value") . ")";
do_query($sql);
show_table();









/***********************************************/
print "<h1>UPDATE STATEMENTS</h1>";
/************************************************/


$Adata = Array();
$Adata['id']        = "1";
$Adata['artist']    = "the beatles";
$Adata['album']     = "Abbey Road";


$sql = "UPDATE " . $MY_CONFIG['tableName'] . " SET title=" . myStringPrep("Come Together") . " WHERE id=" . myStringPrep( "1" );
do_query($sql);
show_table();


$sql = "UPDATE " . $MY_CONFIG['tableName'] . " SET track=" . myStringPrep("1") . ", year=" . myStringPrep("1969") . " WHERE id=" . myStringPrep( "1" );
do_query($sql);
show_table();




$sql = "UPDATE " . $MY_CONFIG['tableName'] . " SET title=" . myStringPrep("Mike's Song") . " WHERE id=1";
do_query($sql);
show_table();










/***********************************************/
print "<h1>SELECT STATEMENTS</h1>";
/************************************************/


print "<p>NOTE: Before getting into the SELECT stuff let's first populate the table with a bunch of crap.
We're using our handy-dandy populate_table_with_generic_data() function, which, if you look at the source code, pops up a few times.<p>";

// Need to add alot of data for experimentation on the next examples:
populate_table_with_generic_data();

$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title='You Never Give Me Your Money';";
$result = do_query($sql);
show_table($result);


$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE 1;";
$result = do_query($sql);
show_table($result);


$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE track IN ('1','2');";
$result = do_query($sql);
show_table($result);


$Adata = Array();
$Adata[] = "You Won't See Me";
$Adata[] = "I'm Looking Through You";

$sql = "SELECT title,album,year FROM " . $MY_CONFIG['tableName']. " WHERE title IN (" . arrayToSQLString($Adata, "value") .");";
$result = do_query($sql);
show_table($result);


$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title RLIKE (". myStringPrep('you') .") OR title RLIKE (". myStringPrep('mustard') . ");";
$result = do_query($sql);
show_table($result);


$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title RLIKE (". myStringPrep('you') .") AND title RLIKE (". myStringPrep('me') . ");";
$result = do_query($sql);
show_table($result);


$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE title RLIKE (". myStringPrep('you') .") OR (title RLIKE (". myStringPrep('the') . ") AND title RLIKE (". myStringPrep('me') . "));";
$result = do_query($sql);
show_table($result);


$sql = "SELECT * FROM " . $MY_CONFIG['tableName']. " WHERE 1 ORDER BY RAND() LIMIT 3;";
$result = do_query($sql);
show_table($result);


$sql = "SELECT DISTINCT year FROM " . $MY_CONFIG['tableName']. " WHERE 1;";
$result = do_query($sql);
show_table($result);



$sql = "SELECT DISTINCT artist FROM " . $MY_CONFIG['tableName']. " WHERE 1;";
$result = do_query($sql);
show_table($result);


$sql = "SELECT DISTINCT artist,album FROM " . $MY_CONFIG['tableName']. " WHERE 1 ORDER BY RAND() LIMIT 5;";
$result = do_query($sql);
show_table($result);


$sql = "SELECT MAX(id) FROM " . $MY_CONFIG['tableName'];
$result = do_query($sql);
show_table($result);

$sql = "SELECT MAX(track) FROM " . $MY_CONFIG['tableName'];
$result = do_query($sql);
show_table($result);










/***********************************************/
print "<h1>DELETE STATEMENTS</h1>";
/************************************************/

$sql = "DELETE FROM " . $MY_CONFIG['tableName'] . " WHERE album=" . myStringPrep("rubber soul");
do_query($sql);
show_table();


$Adata = Array();
$Adata[] = "Her Majesty";
$Adata[] = "Because";
$Adata[] = "Something";
$Adata[] = "Oh! Darling";


$sql = "DELETE FROM " . $MY_CONFIG['tableName'] . " WHERE title IN (" . arrayToSQLString($Adata, "value") . ");";
do_query($sql);
show_table();

// Repopulate table
populate_table_with_generic_data();
//show_table();

$sql = "DELETE FROM " . $MY_CONFIG['tableName'] . " WHERE title RLIKE ('you') OR artist RLIKE ('me') OR artist RLIKE ('my');";
do_query($sql);
show_table();





print '<p><a href="http://www.gieson.com/">MySQL basics for PHP. Copyright 2011 Mike Gieson.</a></p>';




/*********************************************************************

Helper function and other miscellaneous crap

*********************************************************************/


function myStringPrep($value){
    if (get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }
    if (!is_numeric($value)) {
        $value = mysql_real_escape_string($value);
    }
    //$value = "'" . str_replace("'", "\'", $value) . "'";
    //if (!is_numeric($value)) {
        $value = "'" . $value . "'";
    //}
    return $value;
}

function arrayToSQLString($theArray, $theKind){
    $Aretval = array();
    foreach ($theArray as $key => $val) {
        if($theKind == "key"){
            $Aretval[] = $key;
        } else if($theKind == "value"){
            $Aretval[] = myStringPrep($val);
        } else if($theKind == "all"){
            $Aretval[] = $key . "=" . myStringPrep($val);
        }
    }
    return implode(",", $Aretval);
}

function do_error(){
    print "<br><br>---------------ERROR:-----------------<br><br>";
    print (mysql_error());
    exit;
}

function do_query($theQuery){
    global $link, $sql;
    print '<pre class="codedBox">';
    print '$sql = "'.$theQuery.'";<br>';
    print 'mysql_query($sql);<br>';
    $query = mysql_query($theQuery, $link);
    if (!$query) {
        do_error();
    } else {
        print "<br>&gt;&gt; RESULT: ";
        print ($query);

    }
    print '</pre>';
    return $query;
}

function show_table($result = null){
    global $MY_CONFIG, $link;
    if($result == null){
        $sql = "SELECT * FROM " . $MY_CONFIG['tableName'] . ";";
        $result = mysql_query($sql, $link);
    }
    if($result){


        $printTable = "";

        $printTable .= '<table width="495" border="0" cellpadding="5" cellspacing="0" class="singleOutline">'."\n";
        $count = 0;
        $countCols = 0;
        while (@$line = mysql_fetch_assoc($result)) {

            // Header Row
            if($count == 0){
                $printTable .= "<tr>\n";
                foreach ($line as $key => $val) {
                        $printTable .= '<td valign="top" nowrap="nowrap" class="tableHeader">' . $key . "</td>\n";
                        $countCols = $countCols + 1;
                }
                $printTable .= "</tr>\n";
            }

            // Data Row
            $printTable .= "<tr>\n";
            foreach ($line as $key => $val) {
                $printTable .= '<td align="left" valign="top" nowrap="nowrap">' . $val . "</td>\n";
            }
            $printTable .= "</tr>\n";

            $count = $count + 1;

        }

        $printTable .= "<tr>\n";
        $printTable .= '<td align="left" valign="top" nowrap="nowrap" colspan="'.$countCols.'" class="tableHeader">Records: ' . $count . "</td>\n";
        $printTable .= "</tr>\n";

        $printTable .= "</table>\n";
        $printTable .= "<p>&nbsp;</p>\n";
        $printTable .= "<p>&nbsp;</p>\n";

        print $printTable;


    } else {
        do_error($sql);
    }
}

function show_raw_data($theQueryResult){
    $count = 1;
    print "<pre>";
    while ($line = mysql_fetch_assoc($theQueryResult)) {
        print "<BR>line #" . ($count) . "\n";
        foreach ($line as $key => $val) {
            print "key: " . $key . " \tval: ". $val . "\n";
        }
        $count = $count + 1;
    }
    print "</pre>";
}

function populate_table_with_generic_data(){
    global $MY_CONFIG;
    $sql = "REPLACE INTO " . $MY_CONFIG['tableName'] . ' (id, artist, album, title, track, year)
    VALUES
    ("1", "the beatles", "Abbey Road", "Come Together", "1", "1969"),
    ("2", "the beatles", "Abbey Road", "Something", "2", "1969"),
    ("3", "the beatles", "Abbey Road", "Maxwell\'s Silver Hammer", "3", "1969"),
    ("4", "the beatles", "Abbey Road", "Oh! Darling", "4", "1969"),
    ("5", "the beatles", "Abbey Road", "Octopus\'s Garden", "5", "1969"),
    ("6", "the beatles", "Abbey Road", "I Want You (She\'s So Heavy)", "6", "1969"),
    ("7", "the beatles", "Abbey Road", "Here Comes the Sun", "7", "1969"),
    ("8", "the beatles", "Abbey Road", "Because", "8", "1969"),
    ("9", "the beatles", "Abbey Road", "You Never Give Me Your Money", "9", "1969"),
    ("10", "the beatles", "Abbey Road", "Sun King", "10", "1969"),
    ("11", "the beatles", "Abbey Road", "Mean Mr. Mustard", "11", "1969"),
    ("12", "the beatles", "Abbey Road", "Polythene Pam", "12", "1969"),
    ("13", "the beatles", "Abbey Road", "She Came in Through the Bathroom Window", "13", "1969"),
    ("14", "the beatles", "Abbey Road", "Golden Slumbers", "14", "1969"),
    ("15", "the beatles", "Abbey Road", "Carry That Weight", "15", "1969"),
    ("16", "the beatles", "Abbey Road", "The End", "16", "1969"),
    ("17", "the beatles", "Abbey Road", "Her Majesty", "17", "1969"),
    ("18", "beatles", "Rubber Soul", "Drive My Car", "1", "1965"),
    ("19", "beatles", "Rubber Soul", "Norwegian Wood (This Bird Has Flown)", "2", "1965"),
    ("20", "beatles", "Rubber Soul", "You Won\'t See Me", "3", "1965"),
    ("21", "beatles", "Rubber Soul", "Nowhere Man", "4", "1965"),
    ("22", "beatles", "Rubber Soul", "Think for Yourself", "5", "1965"),
    ("23", "beatles", "Rubber Soul", "The Word", "6", "1965"),
    ("24", "beatles", "Rubber Soul", "Michelle", "7", "1965"),
    ("25", "beatles", "Rubber Soul", "What Goes On", "8", "1965"),
    ("26", "beatles", "Rubber Soul", "Girl", "9", "1965"),
    ("27", "beatles", "Rubber Soul", "I\'m Looking Through You", "10", "1965"),
    ("28", "beatles", "Rubber Soul", "In My Life", "11", "1965"),
    ("29", "beatles", "Rubber Soul", "Wait", "12", "1965"),
    ("30", "beatles", "Rubber Soul", "If I Needed Someone", "13", "1965"),
    ("31", "beatles", "Rubber Soul", "Run for Your Life", "14", "1965");';

    do_query( $sql);
}


function printStyles(){
    $styles = '<style type="text/css">

    h1 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 16px;
        font-weight: bold;
        color: #666;
        padding-top: 40px;
    }
    .codedBox {
        background-color: #EFEFEF;
        padding: 5px;
        border: 1px solid #CCCCCC;
        font-family: "Courier New", Courier, mono;
        font-size: 12px;
        color: #3300FF;
    }
    .mysqlCommand {
        color: #CC0000;
    }
    .cellBkgd {
        background-color: #E6F2FF;
    }
    .tableHeader {
        background-color: #E6F2FF;
        font-weight: bold;
    }
    table.singleOutline {
        border-width: 0px 0px 0px 0px;
        border-spacing: 0px;
        border-style: outset outset outset outset;
        border-color: #CCCCCC;
        border-collapse: collapse;
        background-color: white;
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12px;
    }
    table.singleOutline th {
        border-width: 1px 1px 1px 1px;
        padding: 3px 3px 3px 3px;
        border-style: inset inset inset inset;
        border-color: #CCCCCC;
        background-color: white;
        -moz-border-radius: 0px 0px 0px 0px;
    }
    table.singleOutline td {
        border-width: 1px 1px 1px 1px;
        padding: 3px 3px 3px 3px;
        border-style: inset inset inset inset;
        border-color: #CCCCCC;
        background-color: #FAF9E6;
        -moz-border-radius: 0px 0px 0px 0px;
    }
    table {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12px;
        margin-top: 0px;
        margin-right: 0px;
        margin-bottom: 0px;
        margin-left: 0px;
        padding-top: 0px;
        padding-right: 0px;
        padding-bottom: 0px;
        padding-left: 0px;
    }
    p {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12px;
    }
</style>';
    print $styles;
}
?>