ICS 325
Lab 13
News Services using PHP/MySql
(Linux Redhat 7.1)
Log onto
redhat.ics.metrostate.edu using your loginid and password given in class.
$ cd
$ cd public_html
$ mkdir lab13
$ cd lab13
1.
Create
a database table for Authentication:
Log onto MySQL
$ mysql -u su04325??
–p
<enter your
password>
mysql> use su04325??;
mysql>
CREATE TABLE myPass(
-> username varchar(20),
-> password varchar(20));
mysql>
INSERT INTO myPass VALUES("ics325","summer04");
mysql>
SELECT * FROM myPass;
mysql>quit
2.
Create
the Authentication Form
§
Edit
the following file with the name as “auth.php” using pico:
<html>
<head><title>Lab
13 Authentication</title></head>
<body>
<form action="<? PHP_SELF ?>"
name="myForm" method="post">
User
Name: <input type="text" name="user" /><br />
Password:
<input type="password" name="pass" /><br />
<input
type="submit" name="submit" value="Enter" />
</form>
<?php
if(isset($submit)){
$dbcn
=
@mysql_connect("localhost","su04325??","[yourdbpassword]");
if(!$dbcn){
echo
"<p>Error creating database connection: ".
mysql_error() ."</p>";
exit;
}
if(!
@mysql_select_db("su04325??",$dbcn)){
echo
"<p>Unable to locate database su04325??</p>";
exit;
}
$sql
= "SELECT username, password FROM myPass ";
$sql
= $sql . "WHERE username='$user' AND password='$pass';";
$result
= @mysql_query($sql, $dbcn);
if(!$result){
echo(
"<p>Unable to query database at this time.</p>" );
exit();
}
$numRows
= mysql_num_rows($result);
if($numRows
> 0){
$row
= mysql_fetch_array($result);
echo
"<h2>Welcome " . $row["username"] .
"</h2>";
}
else {
echo
"<h2>User name or password is incorrect</h2>";
echo
"<h3>Please Try Again</h3>";
}
}
?>
</body>
</html>
http://redhat.ics.metrostate.edu/~su04325??/lab13/auth.php
3.
Create
a database table for the News Application:
Log onto MySQL
$ mysql -u su04325??
–p
<enter your
password>
mysql> use su04325??;
mysql> create table mynews (
->
news_id int not null auto_increment,
->
title varchar(40),
-> date DATE,
->
author_name varchar(40),
->
author_email varchar(48),
->
website varchar(80),
-> body
TEXT,
->
primary key(news_id));
mysql>quit
4.
Enter
news items
§
Edit
the following file with the name as “newsinput.php” using pico:
<html>
<head>
<title>
News Item Input </title>
</head>
<body>
<?php
if
( isset($submit) ) {
$dbConNum
= @mysql_connect("localhost", "su04325??",
"yourDBPasswd");
if
(!$dbConNum){
echo("<p>Error
creating Database Connection: " .
mysql_error()
. "</p>");
exit;
}
if(!
@mysql_select_db("su04325??", $dbConNum)) {
echo(
"<p>Unable to locate the database at this time.</p>" );
exit;
}
$date
= date("Y-m-d");
$sql="INSERT
INTO mynews VALUES (NULL, '$title', '$date',
'$auth',
'$email', '$website', '$body')";
if (
@mysql_query($sql) ) {
echo("<p>I
got the data!</p>");
}
else
{
echo("<p>Error
inserting Data: " . mysql_error() . "</p>");
}
print("<h3>
Do you want to add another one? </h3>");
mysql_close($dbConNum);
}
?>
<p><h2> You can
enter news items for ICS 325 Class through the following form:</h2>
<form action="<?
$PHP_SELF ?>" method="post">
<table>
<tr>
<td>Your
Name </td>
<td><input
type=text size = 50 name="auth"></td>
<tr>
<tr>
<td>Your
Email </td>
<td><input
type=text size = 50 name="email"></td>
</tr>
<tr>
<td>News
Title </td>
<td><input
type=text size = 50 name="title"></td>
</tr>
<tr>
<td>Web
Site </td>
<td><input
type=text size = 50 name="website"></td>
</tr>
</table>
<h3>
News </h3>
<textarea
cols = 60 rows = 6 name="body" wrap="virtual">
</textarea>
<br
/><input type="submit" name="submit"
value="Submit News" />
</form>
</body>
</html>
§
Invoke
the script using the following link,
http://redhat.ics.metrostate.edu/~su04325??/lab13/newsinput.php
5.
View
the news items
<html>
<head>
<title>
Showing News on the Screen </title>
</head>
<body>
<h1>
News for ICS 325 Class </h1>
The
news can be ordered by
<a
href="newsout.php?orderby=date">Date</a>,
<a
href="newsout.php?orderby=title">Title</a>
or by
<a
href="newsout.php?orderby=author">Author</a>
<p><form
action="<? PHP_SELF ?>" method="post">
<input
type="submit" name="submit" value="Submit">
</form>
<table
border="1" cellpadding="4">
<?php
if
( isset($submit) || isset($orderby)) {
$dbConNum =
@mysql_connect("localhost", "su04325??",
"yourDBPasswd");
if
(!$dbConNum){
echo("<p>Error
creating Database Connection: " .
mysql_error() . "</p>");
exit;
}
if(!
@mysql_select_db("su04325??", $dbConNum)){
echo("<p>Unable
to locate the database at this time.</p>");
exit;
}
if
( $orderby == 'date' ):
$sql
= "select * from mynews order by 'date'";
elseif
( $orderby == 'title' ):
$sql
= "select * from mynews order by 'title'";
elseif
( $orderby == 'author' ):
$sql
= "select * from mynews order by 'author_name'";
else:
$sql
= "select * from mynews";
endif;
$result
= @mysql_query($sql, $dbConNum);
if
(!$result) {
echo("<p>Error
performing query: " . mysql_error()."</p>");
exit();
}
mysql_close($dbConNum);
while ( $row = mysql_fetch_array($result) ) {
?>
<tr>
<td
bgcolor=#003399><b><font color=white>
<?=$row["title"]?></font></b>
</td>
</tr>
<tr>
<td>
By:<a
href="mailto:<?=$row["author_email"]?>">
<?=$row["author_email"]?></a>
<br
/> Posted on: <?=$row["date"]?>
<br
/> Website: <a href=
"<?=$row["website"]?>"><?=$row["website"]?></a>
<hr
/><?= $row["body"]?>
</td>
</tr>
<?php
}
}
?>
</table>
</body>
</html>
http://redhat.ics.metrostate.edu/~su04325??/lab13/newsout.php