PHP & MySQL basic

CREATE DATABASE ijdb;
DROP DATABASE ijdb;

CREATE TABLE joke (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    jokeText TEXT,
    jokeDate DATE NOT NULL
) DEFAULT CHARACTER SET utf8;

SHOW TABLES;
DESCRIBE joke;
DROP TABLE joke;


INSERT INTO joke SET
joketext = "Why did the chicken cross the road? To get to the other side!",
jokedate = "2011-04-01";

INSERT INTO joke
(joketext, jokedate)
VALUES (
"Why did the chicken cross the road? To get to the other side!",
"2011-04-01"
);


SELECT * FROM joke;
SELECT id, jokedate from joke;
SELECT id, LEFT(joketext, 20), jokedate FROM joke; - LEFT , lets us tell MySQL to display a column’s contents up to a specified maximum number of characters.
SELECT COUNT(*) FROM joke; - COUNT , which lets us count the number of results returned.
SELECT COUNT(*) FROM joke WHERE jokedate >= "2011-01-01";
SELECT joketext FROM joke WHERE joketext LIKE "%chicken%";

SELECT joketext FROM joke WHERE
joketext LIKE "%knock%" AND
jokedate >= "2011-04-01" AND
jokedate < "2011-05-01";


UPDATE joke SET jokedate = "2011-04-01"
WHERE joketext LIKE "%chicken%";

DELETE FROM tableName WHERE conditions;
DELETE FROM tableName WHERE conditions;


PHP
-----------
$var1 = 'PHP';
echo "$var1 rules!";    // Outputs 'PHP rules!'
echo '$var1 rules!';    // Outputs '$var1 rules!'
ou can include the name of a variable right inside a text string, and have the value inserted in its place if you surround the string with double quotes instead of single quotes. This process of converting variable names to their values is known as variable interpolation. However, as the last line demonstrates, a string surrounded with single quotes will not interpolate the variable names it contains.



$myarray = array('one', 2, '3');
echo $myarray[0];       // Outputs 'one'
echo $myarray[1];       // Outputs '2'
echo $myarray[2];       // Outputs '3'

$myarray[1] = 'two';    // Assign a new value
$myarray[3] = 'four';   // Create a new element

$myarray[] = 'the fifth element';
echo $myarray[4];       // Outputs 'the fifth element'


$birthdays['Kevin'] = '1978-04-12';
$birthdays['Stephanie'] = '1980-05-16';
$birthdays['David'] = '1983-09-09';



htmlspecialchars($firstname, ENT_QUOTES, 'utf-8')

----------------------------------
For documentation - php.net/date (for eg); dev.mysql.com (click on Developer zone);

-----------------------------------

Connecting to MySQL database from PHP
------------
Need a user account for php. Go to phpMyAdmin, select a database, click on Priviledge. Add user.

<?php

$link = mysqli_connect('localhost', 'ijdbuser', 'mypassword');
if (!$link)
{
$output = "Unable to connect to the database server";
include 'output.html.php';
exit();
}

if (!mysqli_set_charset($link, 'utf8'))
{
$output = "Unable to set  database connection encoding";
include 'output.html.php';
exit();
}

if (!mysqli_select_db($link, 'ijdb'))
{
$output = 'Unable to locate joke database';
include 'output.html.php';
exit();
}

$output = 'Database connection established';
include 'output.html.php';

?>

SQL Join
Select * From joke INNER JOIN author on authorid = author.id

File List

PHP-L3S4-Variables,Operators.pdf
PHP-L3S5-Arrays.pdf
PHP-L3S6-PassingVariablesInLinks.pdf
PHP-L3S8-PassingVariablesInForms.pdf
PHP-L4S3-ConditionalStatements.pdf
PHP-L4S4-Loops.pdf
PHP-L4S7-ControllerAndTemplates.pdf
PHP-L6S5-SendingSqlQueries with PHP.pdf
PHP-L6S6-Handling SELECT Resultset.pdf
PHP-L7S3-Inserting Data Into Database.pdf
PHP-L7S4-Deleting Data from Database.pdf
PHP-L11S4-Types Of Includes.pdf
PHP-L11S6-Variable Scope&Global Access.pdf

sample codes.zip