Transcript Slide 1

Intro to Databases
SQL REVIEW
To talk to the database, you have to use SQL
SQL is used by many databases, not just MySQL.
SQL stands for Structured Query Language, and is used by many database, not just MySQL.
SQL’s basic commands include
INSERT – add a new record
UPDATE – change an existing record
SELECT – retrieve record(s) from the database
DELETE – remove a record(s) from the database
Class 4
Intro to Databases
DELETING A RECORD
Class 4
Intro to Databases
Deleting a Record
To delete a record, we have to identify what record to delete
$query="DELETE FROM table WHERE x='y' ";
Class 4
Intro to Databases
Deleting a Record
To delete a user, we need to write a new script, called “delete_user.php”
We also need to add a link to delete user from the “view_users.php” script
1- open view_users.php
Class 4
Intro to Databases
Class 4
Deleting a Record
in view_users.php
<?
…
## PRINT OUT RESULTS
echo '<p>List of all registered users</p>';
// check that there are results
$num_rows=mysql_affected_rows($link);
// output content
if($num_rows>0){
// get info
while ($myrow = mysql_fetch_array($result)) {
$user_id=$myrow["user_id"];
–
$user_name =$myrow["user_name"];
etc…
print("$user_id | $user_name |… <a href=\"delete_user.php?id=$user_id\">delete user</a><br>");
}
}else{
Note we are passing a variable in the
// tell user there's no info
url string.
print("sorry, no records");
}
…
?>
This is the GET method
Intro to Databases
Deleting a Record
2- open delete_user.php
Class 4
Intro to Databases
Class 4
Deleting a Record
in delete_user.php
<?
…
// 1 - open the connection to the database
include ('mysqlconnect.php');
// initialize error message to nothing
$error_message="";
This time we're opening the database connection at the
start of the page.
Here it doesn't make much of a difference, but will make
things easier as our scripts get more complicated
// BASIC ERROR CHECKING
// USER ID
// note the use of the exclamation point to mean NOT
if (!$_GET['id']) {
$error_message.= "<p>The user ID is missing</p>";
–
}
// Do I PASS ERROR CHECKING?
if ($error_message=="" ) { // If everything's okay.
## DATABASE FUNCTIONALITY WILL GO HERE
// 2- formulate the question
// 3- ask the question
// 4- output results
}else{
// there's an error - print error message
echo '<p>'.$error_message.'</p>';
}
…
?>
Error checking – make
sure we have a user id
to delete
Intro to Databases
Class 4
Deleting a Record
in delete_user.php
<?
…
// 2- formulate the question
$id=$_GET['id']; // convert to make it easier
$query="DELETE FROM $tableName WHERE user_id = '$id'";
Note that this uses $_GET
$id specifies what user to delete
// 3- ask the question
$result =mysql_query($query)OR die("error 3 - query failed".mysql_error());
// 4- output results
print("The user with the id $id has been deleted");
…
?>
–
Do deletion
Intro to Databases
Class 4
Deleting a Record
in delete_user.php
A fancier ending – here we redirect back to the “view_users.php” page
<?
…
// 4- output results
// redirect to the users page
$go_here="Location: http://".$_SERVER['HTTP_HOST']. dirname($_SERVER['PHP_SELF'])."/viewusers.php";
header($go_here); // redirect the page
exit(); // end the script
…
?>
$_SERVER['HTTP_HOST'] - the server
dirname($_SERVER['PHP_SELF']) – the directory
viewusers.php – the page
–
Intro to Databases
Deleting a Record
3- test both files
Class 4
Intro to Databases
USING SELECT PART II
Class 4
Intro to Databases
Selecting a specific record
Class 4
To allow users to log in, we need to determine if their user name and password exist in the database.
To do this we use SELECT with a WHERE clause
WHERE allows us to qualify what records we get
AND allows us to include more than one requirement
For example
$query ="SELECT * FROM $tableName WHERE user_id=5";
$query ="SELECT * FROM $tableName WHERE user_name=‘Martin’ AND password=’12qwerty’";
–
Intro to Databases
Class 4
Selecting a specific record
When we ask a user to log in, we’re using the username and password sent through the form to get a specific user id.
Our query will look like
$query="SELECT user_id FROM $tableName WHERE user_name=‘$user_name’ AND password=‘$password’ ";
Column
Names
–
Intro to Databases
Class 4
Selecting a specific record
1- open login.php
–
Intro to Databases
Class 4
Selecting a specific record
login.php
<?
…
if (isset($_POST['submit'])) { // Handle the form.
// BASIC ERROR CHECKING
// DO I PASS ERROR CHECKING?
if ($error_message=="" ) { // If everything's okay.
## DATABASE FUNCTIONALITY WILL GO HERE
// 2- formulate the question
// 3- ask the question
## PRINT OUT RESULTS
// 4- test for a result
}else{
// error message isn’t empty
echo '<p>'.$error_message.'</p>';
// Display the form.
print_login_form();
}
} else { // Display the form.
print_login_form();
} // End of the main SUBMIT conditional.
–
Intro to Databases
Class 4
Selecting a specific record
login.php
…
### USER DEFINED FUNCTIONS
function print_login_form(){
print("
<!-- Start Form -->
<form action=".$_SERVER['PHP_SELF']." method='post'>
<fieldset><legend>Please Login:</legend>
<p><b>User Name:</b> <input type='text' name='user_name' value='".$_POST['user_name']."' size='20' maxlength='10'
/></p>
<p><b>Password:</b> <input type='password' name='password' value='".$_POST['password']."' size='20'
maxlength='10' /></p>
–
<div align='center'><input type='submit' name='submit' value='Login' /></div>
</form><!-- End of Form -->
");
}
?>
Intro to Databases
Class 4
Selecting a specific record
login.php
// BASIC ERROR CHECKING
// USER NAME
if (strlen($_POST['user_name']) <= 0) {
$error_message.= "<p>You forgot to enter your username!</p>";
}
// PASSWORD.
if (strlen($_POST['password']) <= 0) {
$error_message.= "<p>You forgot to enter your password!</p>";
}
–
Intro to Databases
Class 4
Selecting a specific record
login.php
<?
…
// 2- formulate the question
$user_name=$_POST['user_name']; // convert to make it easier
$password=$_POST['password']; // convert to make it easier
$query="SELECT * FROM $tableName WHERE user_name='$user_name' AND password='$password' ";
// 3- ask the question
$result =mysql_query($query)OR die("error 3 - query failed".mysql_error());
## PRINT OUT RESULTS
// 4- test for a result
if(mysql_num_rows($result)==1){
–
// 5- get results
$myrow = mysql_fetch_array($result);
// 6- print results
echo '<p>Welcome back '.$myrow[“first_name"].' '. $myrow["last_name"];
else{ // no records were found
echo '<p>That username and password couldn\'t be found</p>';
// Display the form.
print_login_form();
}
(mysql_num_rows($ result) – returns the number of records retrieved. In this case it should be 1 and only 1
Intro to Databases
Class 4
Selecting a specific record
Upload to the server and test for errors
–
Intro to Databases
USING UPDATE
Class 4
Intro to Databases
Using Update
Class 4
Update allows us to change existing records
The basic query is UPDATE with a WHERE clause
WHERE allows us to qualify what records we update
SET tells the database what to change
For example
$query =“UPDATE $tableName SET password=‘$password’
WHERE user_id=5";
–
Column
Names
Intro to Databases
Using Update
Class 4
Open change_password.php
–
Intro to Databases
Class 4
Using Update
changepassword.php
<?
…
if (isset($_POST['submit'])) { // Handle the form.
// BASIC ERROR CHECKING
// DO I PASS ERROR CHECKING?
if ($error_message=="" ) { // If everything's okay.
// PART 1 - CHECK FOR USER IN DATABASE & GET ID
// PART 1a - formulate the question
// PART 1b- ask the question
// PART 1c - test for a result
if(mysql_num_rows($result)==1){
// PART 2 - IF USER EXISTS UPDATE INFORMATION USING THE USER'S ID
// PART 2a - get the user id
// PART 2b -make a new query
–
// PART 2c -ask the new question
// PART 2d - check success
}else{ // there's an error – user not found
echo '<p>That username and password couldn\'t be found</p>';
// Display the form.
print_changepass_form();
}
}else{
// there's an error - print error message
echo '<p>'.$error_message.'</p>';
// Display the form.
print_changepass_form();
}
} else { // Display the form.
print_changepass_form();
} // End of the main SUBMIT conditional.
…
Intro to Databases
Class 4
Using Update
changepassword.php
<?
…
### USER DEFINED FUNCTIONS
function print_changepass_form(){
print("
<!-- Start Form -->
<form action=".$_SERVER['PHP_SELF']." method='post'>
<fieldset><legend>To change your password, please enter your current name and password<br> and your new
password:</legend>
<p><b>User Name:</b>
<input type='text' name='user_name' value='".$_POST['user_name']."' size='20' maxlength='10' /></p>
–
<p><b>Password:</b>
<input type='password' name='password' value='".$_POST['password']."' size='20' maxlength='10' /></p>
<p><b>NEW Password:</b>
<input type='password' name='new_password' value='".$_POST['new_password']."' size='20' maxlength='10' /></p>
<div align='center'><input type='submit' name='submit' value='Change Password' /></div>
</form><!-- End of Form -->
");
}
…
?>
Intro to Databases
Using Update
changepassword.php
<?
…
// BASIC ERROR CHECKING
// user name
if (strlen($_POST['user_name']) <= 0) {
$error_message.= "<p>You forgot to enter your username!</p>";
}
// password
if (strlen($_POST['password']) <= 0) {
$error_message.= "<p>You forgot to enter your password!</p>";
}
// new password
if (strlen($_POST['new_password']) <= 0) {
$error_message.= "<p>You forgot to enter your NEW password!</p>";
–
}
…
?>
Class 4
Intro to Databases
Class 4
Using Update
changepassword.php
<?
…
// PART 1 - CHECK FOR USER IN DATABASE & GET ID
// PART 1a - formulate the question
$user_name=$_POST['user_name']; // convert to make it easier
$password=$_POST['password']; // convert to make it easier
$query="SELECT user_id FROM $tableName WHERE user_name='$user_name' AND password='$password' ";
// PART 1b- ask the question
$result =mysql_query($query)OR die("error 3 - query failed".mysql_error());
–
Intro to Databases
Class 4
Using Update
changepassword.php
<?
…
// PART 1c - test for a result
if(mysql_num_rows($result)==1){
// PART 2 - IF USER EXISTS UPDATE INFORMATION USING THE USER'S ID
// PART 2a - get the user id
$row=mysql_fetch_array($result);
$user_id= $row['user_id'];
// PART 2b -make a new query
$new_password=$_POST['new_password']; // convert to make it easier
$query="UPDATE $tableName SET password='$new_password' WHERE user_id='$user_id'";
–
// PART 2c -ask the new question
$result =mysql_query($query)OR die("error 3 - query failed".mysql_error());
// PART 2d - check success
if (mysql_affected_rows()==1){
echo '<P>Your password has been changed</p>';
}else{
echo '<P> There has been an error. Please contact the webmaster</p>';
}
}else{ // there's an error – user not found
echo '<p>That username and password couldn\'t be found</p>';
// Display the form.
print_changepass_form();
}
…
?>
Intro to Databases
INSERTING THE DATETIME WHEN A USER REGISTERS
Class 4
Intro to Databases
Inserting the DateTime when a user registers
Class 4
register.php
// 2- formulate the question
$query="INSERT INTO $tableName (user_name, password, first_name, last_name, email, registration_date) values
('$user_name', '$password', '$first_name', '$last_name', '$email', now())";
View_users.php
// output content
if($num_rows>0){
// get info
while ($myrow = mysql_fetch_array($result)) {
$user_id=$myrow["user_id"];
–
$user_name =$myrow["user_name"];
$password =$myrow["password"];
$first_name =$myrow["first_name"];
$last_name =$myrow["last_name"];
$email =$myrow["email"];
$registration_date =$myrow["registration_date"];
print("$user_id | $user_name | $password | $first_name | $last_name | $email | $registration_date |<br>");
]