************************************************************************************
Page Name : OcelotCreatingWebsite
Author : Michael Robinson
URL : http://ocelot.aul.fiu.edu/~mrobi002
Course : CGS4854 - Web Design and Management
Professor : Michael Robinson
Purpose : To present to the students a complete website,
required by all students as the course project.
This website will be built in 5 steps, programs 1, 2, 3, 4, and 5.
Each student can choose their own topic,
except politics, race, religion, drugs or sex.
This site will be updated every semester, including new technologies.
This website contains dozens of other programs samples,
using HTML 5, CSS 3, Javascript, PHP, mySql, XML, JSP, Servlet.
Updated : June 27, 2020
*************************************************************************************
***********************************************************************
* *
* BACKEND *
* *
* TERMINAL MODE *
* *
* WEBSITE CODE TO MANAGE mySql DATABASE *
* *
***********************************************************************
Ocelot Creating Website's sql dababase
1 - From any operating system, open a terminal and
connect to your server where mysql is installed
ssh ocelot.aul.fiu.edu or
ssh username@ocelot.aul.fiu.edu or
putty for windows
2 - Connect to your mysql, example:
mrobi999@ocelot:~ 111% mysql -h ocelot.aul.fiu.edu -u username -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2033
Server version: 5.1.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| username_db |
+--------------------+
3 rows in set (0.00 sec)
mysql> connect username_db;
Connection id: 2034
Current database: username_db
mysql> show tables from username_db;
Empty set (0.00 sec)
Now that you know you have an account and access to it write the code
for your website.
***********************************************************************
* *
* BACKEND *
* *
* PHP MODE *
* *
* WEBSITE CODE TO MANAGE mySql DATABASE *
* *
***********************************************************************
The complete source code for the following program located at
http://users.cis.fiu.edu/~mrobi002/includes/WebM_site/pgm3.php
is shown below.
Please adapt it to your projects, but more important LEARN the code below.
You are welcome to ADD, MODIFY, DELETE, FIND AND CLEAR as many records
as needed so that you can test the full functionality of this project.
Any suggestions to make it better are welcome.
This program requires the following steps:
1 - Create/Modify the frontend used by the users
2 - Create a Controller.php program to control the all other programs
3 - Create one program for each function implemented
=========================================================================================
1 - Modify FRONTEND from program 2 as follows:
Michael Robinson - header.php
The Website Company
Michael Robinson - pgm3.php
0 )
{
//echo " Leave it alone it means that find.php was already executed Found = [" . $found . "]";
//leave it alone it means that find.php was already executed
}
else
{
$found = ""; //set the value of $found to empty
}
?>
=========================================================================
2 - Create a Controller.php program to control all other programs
ControllerPgm3
I am going to connect to mySql";
// server user password database
$connection = mysqli_connect("ocelot.aul.fiu.edu","joeSmith001","password","dbName");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
{
//echo " I have connected to mySql ";
// Change database to another name if needed
$dbName="web4854";
$db_selected = mysqli_select_db( $connection, $dbName );
if (!$db_selected)
{
die( $dbName . ' does not exist, can\'t use it ' . mysqli_error());
}
else
{
//echo "I selected database : " . $db_selected . " " . $dbName . " " ;
//access to a table
$tableName = "customers";
$query = mysqli_query( $connection, "SELECT * FROM $tableName" );
//if table does not exist, create it
if(!$query)
{
echo "The ".$tableName." does not exists ";
echo " Creating table : ".$tableName." ";
$sql = "CREATE TABLE ".$tableName."(
Telephone VARCHAR(20) NOT NULL,
PRIMARY KEY(Telephone),
FirstName VARCHAR(30),
LastName VARCHAR(30),
Email VARCHAR(30),
Age VARCHAR(8),
Gender VARCHAR(10),
IT VARCHAR(2),
CS VARCHAR(2),
Robotics VARCHAR(8),
Engineering VARCHAR(11),
Others VARCHAR(30),
SpecialNeeds VARCHAR(200)
)";
$result = mysqli_query( $connection, $sql );
//confirm table creation
if ($result)
{
echo "table ". $tableName." created ";
}
else
{
die ("Can\'t create ". $tableName." ". mysqli_error() );
}
}//if(!$query) if table does not exist, create it
}//end if (!$db_selected) connecting to db
}//end if (mysqli_connect_errno()) connecting to mysql
//extract the data inputed by the user creating global php fields
$Telephone = $_POST['Telephone'];
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$Email = $_POST['Email'];
$Age = $_POST['Age'];
$Gender = $_POST['Gender'];
$IT = $_POST['IT'];
$CS = $_POST['CS'];
$Robotics = $_POST['Robotics'];
$Engineering = $_POST['Engineering'];
$Others = $_POST['Others'];
$SpecialNeeds = $_POST['SpecialNeeds'];
$found = $_POST['found'];
/*
//verify that the data entered by the user is being received
echo $Telephone." ";
echo $FirstName." ";
echo $LastName." ";
echo $Email." ";
echo $Age." ";
echo $Gender." ";
echo $IT." ";
echo $CS." ";
echo $Robotics." ";
echo $Engineering." ";
echo $Others." ";
echo $SpecialNeeds." ";
*/
if ( $_POST['Find'] )
{
include('find.php');
}
else if ( $_POST['Save'] )
{
include('save.php');
}
else if ( $_POST['Modify'] )
{
include('modify.php');
}
else if ( $_POST['Delete'] )
{
include('delete.php');
}
else if ( $_POST['Clear'] )
{
include('clear.php');
}
else
{
echo "
you pressed UNKOWN button
";
}
mysqli_close($connection);
include( "pgm3.php" );
?>//end controler.php
=========================================================================
3 - Create one program for each function implemented
";
echo $FirstName." ";
echo $LastName." ";
echo $Email." ";
echo $Age." ";
echo $Gender." ";
echo $IT." ";
echo $CS." ";
echo $Robotics." ";
echo $Engineering." ";
echo $Others." ";
echo $SpecialNeeds." ";
echo "inserting record into table ".$tableName." ";
*/
$Telephone=trim($Telephone);
if(strlen($Telephone)>0)
{
$sql="INSERT INTO customers (
Telephone,
FirstName,
LastName,
Email,
Age,
Gender,
IT,
CS,
Robotics,
Engineering,
Others,
SpecialNeeds
)
VALUES
(
'$Telephone',
'$FirstName',
'$LastName',
'$Email',
'$Age',
'$Gender',
'$IT',
'$CS',
'$Robotics',
'$Engineering',
'$Others',
'$SpecialNeeds'
)";
if (mysqli_query($connection, $sql))
{
//echo " New record created successfully";
$message ="RECORD $Telephone ADDED";
}
else
{
//echo " Error: " . $sql . " " . mysqli_error($connection);
$message ="RECORD $Telephone EXISTS NOT ADDED";
}
}//end if(strlen($Telephone)>0)
else
{
$message ="RECORD NOT ADDED Telephone CAN NOT BE EMPTY";
}
?>
//end save.php
";
echo $FirstName;
echo $LastName;
echo $Email;
echo $age;
echo $gender;
echo $Preferences;
echo $checkbox;
echo $Others;
echo $SpecialNeeds;
echo "find.php table : ".$tableName." ";
echo " find.php found = [" . $found . "]";
$found = $Telephone;
echo " find.php Found = Telephone = [" . $found . "]";
*/
//$sql="SELECT * FROM customers ORDER BY Telephone";
$sql="SELECT * FROM customers where Telephone = '$Telephone'";
if ($result=mysqli_query($connection,$sql))
{
//printf("Result of mysqli_query(connection,sql) = %d ", $result);
// Return the number of rows in result set
$rowcount=mysqli_num_rows($result);
//printf("Result set has %d rows.\n",$rowcount);
while( $row = mysqli_fetch_array( $result ) )
{
$Telephone = $row['Telephone']; //primary key
$FirstName = $row['FirstName']; //type="text"
$LastName = $row['LastName']; //type="text"
$Email = $row['Email']; //type="text"
$Age = $row['Age']; //type="dropdown"
$Gender = $row['Gender']; //type="radio"
$IT = $row['IT']; //type="checkbox"
$CS = $row['CS']; //type="checkbox"
$Robotics = $row['Robotics']; //type="checkbox"
$Engineering = $row['Engineering']; //type="checkbox"
$Others = $row['Others']; //type="text"
$SpecialNeeds = $row['SpecialNeeds']; //type="textarea"
}
/*
echo $Telephone." ";
echo $FirstName." ";
echo $LastName." ";
echo $Email." ";
echo $Age." ";
echo $Gender." ";
echo $IT." ";
echo $CS." ";
echo $Robotics." ";
echo $Engineering." ";
echo $Others." ";
echo $SpecialNeeds." ";
*/
//printf("\ni am here in find.php\n [%s] [%s]", $Telephone, $FirstName );
//echo "(".$Telephone." ".$FirstName.")";
$Telephone=trim($Telephone); //take all front and back spaces out
//if (mysqli_query($connection, $sql))
if ( $rowcount )
{
$found = $Telephone;
$message ="RECORD $found FOUND";
}
else if( strlen($Telephone) ==0 )
{
$message ="Telephone CAN NOT BE EMPTY ";
//echo " Error: " . $sql . " " . mysqli_error($connection);
//clear data in variables
//$Telephone = "";
$FirstName = "";
$LastName = "";
$Email = "";
$Age = "";
$Gender = "";
$IT = "";
$CS = "";
$Robotics = "";
$Engineering = "";
$Others = "";
$SpecialNeeds = "";
$found = "";
}
else
{
$message ="RECORD $Telephone NOT FOUND ";
//echo " Error: " . $sql . " " . mysqli_error($connection);
//clear data in variables
//$Telephone = "";
$FirstName = "";
$LastName = "";
$Email = "";
$Age = "";
$Gender = "";
$IT = "";
$CS = "";
$Robotics = "";
$Engineering = "";
$Others = "";
$SpecialNeeds = "";
$found = "";
}
}
?>
//end find
";
echo $Telephone." ";
echo $FirstName." ";
echo $LastName." ";
echo $Email." ";
echo $Age." ";
echo $Gender." ";
echo $IT." ";
echo $CS." ";
echo $Robotics." ";
echo $Engineering." ";
echo $Others." ";
echo $SpecialNeeds." ";
echo " modify found = [" . $found . "]";
*/
if ( ( strlen(trim($found)) > 0 ) && ($found == $Telephone) )
{
$query = "UPDATE customers
SET FirstName = '$FirstName',
LastName = '$LastName',
Email = '$Email',
Age = '$Age',
Gender = '$Gender',
IT = '$IT',
CS = '$CS',
Robotics = '$Robotics',
Engineering = '$Engineering',
Others = '$Others',
SpecialNeeds = '$SpecialNeeds'
WHERE Telephone = '$Telephone'";
$sql = mysqli_query( $connection,$query );
if ($sql)
{
$message ="RECORD $found MODIFIED";
}
else
{
//echo "Problem updating record. MySQL Error: " . mysqli_error($connection);
$message ="PROBLEM UPDATING RECORD";
}
}
else
{
$message ="FIND THE RECORD BEFORE MODIFING IT";
}
?>
//end modify.php
";
// sql to delete a record
$sql="DELETE FROM customers WHERE Telephone='$Telephone'";
$Telephone=trim($Telephone);
// if(strlen($Telephone)>0)
if ( ( strlen(trim($found)) > 0 ) && ($found == $Telephone) )
{
if (mysqli_query($connection, $sql))
{
//echo "Record deleted successfully found = {".$found."}";
$message ="RECORD $found DELETE";
$found=""; //this clear the flag for record found to be able to modify
}
else
{
$message = "Error deleting record: " . mysqli_error($connection);
}
}
else
{
$message ="RECORD NOT DELETED Telephone CAN NOT BE EMPTY";
}
?>
//end delete.php
//end clear.php
Leads First Next Previous Last
0)
{
$location--;
}
mysqli_data_seek($results, $location);
$row=mysqli_fetch_assoc($results);
$Telephone = $row['Telephone'];
$Name = $row['Name'];
$Email = $row['Email'];
$Comments = $row['Comments'];
}//end if ($results=mysqli_query($connection,$sql))
}//end else if($_POST['Previous'])
else if($_POST['Next'])
{
$sql="SELECT * FROM emails order by Telephone ASC";
if ($results=mysqli_query($connection,$sql))
{
//total number of rows for the upper limit of the search function.
$rowcount=mysqli_num_rows($results);
//Increment location only if it is below the highest possible value.
if ($location <$rowcount-1)
{
$location++;
}
mysqli_data_seek($results, $location);
$row=mysqli_fetch_assoc($results);
$Telephone = $row['Telephone'];
$Name = $row['Name'];
$Email = $row['Email'];
$Comments = $row['Comments'];
}//end if ($results=mysqli_query($connection,$sql))
}//end else if($_POST['Next'])
else //get First record
{
//Resets location to position 0, and grab the first record in the table.
$location=0;
$sql="SELECT * FROM emails order by Telephone ASC limit 1";
if ($result=mysqli_query($connection,$sql))
{
$row = mysqli_fetch_array( $result );
$Telephone = $row['Telephone'];
$Name = $row['Name'];
$Email = $row['Email'];
$Comments = $row['Comments'];
}//end if ($result=mysqli_query($connection,$sql))
}//end else
mysqli_close($connection); //close sql connection
?>