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

1
SQL REVIEW To talk to the database, you have to
use SQLSQL is used by many databases, not just
MySQL.SQL stands for Structured Query Language,
and is used by many database, not just
MySQL.SQLs basic commands includeINSERT
add a new recordUPDATE change an existing
recordSELECT retrieve record(s) from the
databaseDELETE remove a record(s) from the
database
2
DELETING A RECORD
3
Deleting a RecordTo delete a record, we have to
identify what record to deletequery"DELETE
FROM table WHERE x'y' "
4
Deleting a RecordTo delete a user, we need to
write a new script, called delete_user.phpWe
also need to add a link to delete user from the
view_users.php script1- open view_users.php
5
Deleting a Record
in view_users.php lt? PRINT OUT
RESULTSecho 'ltpgtList of all registered
userslt/pgt'// check that there are results
num_rowsmysql_affected_rows(link) //
output content if(num_rowsgt0) // get info
while (myrow mysql_fetch_array(result))
user_idmyrow"user_id"
user_name myrow"user_name"
etc print("user_id user_name
lta href\"delete_user.php?iduser_id\"gtdelete
userlt/agtltbrgt") else // tell user
there's no info print("sorry, no
records")?gt

Note we are passing a variable in the url
string. This is the GET method
6
Deleting a Record2- open delete_user.php
7
Deleting a Recordin delete_user.php lt?//
1 - open the connection to the databaseinclude
('mysqlconnect.php')// initialize error
message to nothingerror_message""// BASIC
ERROR CHECKING// USER ID// note the use of the
exclamation point to mean NOTif (!_GET'id')
error_message. "ltpgtThe user ID is
missinglt/pgt" // 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
'ltpgt'.error_message.'lt/pgt' ?gt
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
Error checking make sure we have a user id to
delete

8
Deleting a Recordin delete_user.php lt? //
2- formulate the question id_GET'id' //
convert to make it easier query"DELETE FROM
tableName WHERE user_id 'id'" // 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") ?gt
Note that this uses _GET
id specifies what user to delete
Do deletion

9
Deleting a Recordin delete_user.php A
fancier ending here we redirect back to the
view_users.php pagelt? // 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 ?gt
_SERVER'HTTP_HOST' - the serverdirname(_SER
VER'PHP_SELF') the directoryviewusers.php
the page

10
Deleting a Record3- test both files
11
USING SELECT PART II
12
Selecting a specific record 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 clauseWHERE allows
us to qualify what records we getAND allows us
to include more than one requirementFor
example query "SELECT FROM tableName WHERE
user_id5" query "SELECT FROM tableName
WHERE user_nameMartin AND password12qwerty"


13
Selecting a specific record When we ask a user
to log in, were using the username and password
sent through the form to get a specific user
id.Our query will look likequery"SELECT
user_id FROM tableName WHERE user_nameuser_nam
e AND passwordpassword "
Column Names

14
Selecting a specific record 1- open login.php

15
Selecting a specific record login.phplt?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 isnt
empty echo 'ltpgt'.error_message.'lt/pgt'
// Display the form. print_login_form()
else // Display the form.
print_login_form() // End of the main
SUBMIT conditional.

16
Selecting a specific record login.php
USER DEFINED FUNCTIONSfunction
print_login_form()print("lt!-- Start Form
--gtltform action"._SERVER'PHP_SELF'."
method'post'gtltfieldsetgtltlegendgtPlease
Loginlt/legendgtltpgtltbgtUser Namelt/bgt ltinput
type'text' name'user_name' value'"._POST'user
_name'."' size'20' maxlength'10'
/gtlt/pgtltpgtltbgtPasswordlt/bgt ltinput type'password'
name'password' value'"._POST'password'."'
size'20' maxlength'10' /gtlt/pgtltdiv
align'center'gtltinput type'submit' name'submit'
value'Login' /gtlt/divgtlt/formgtlt!-- End of Form
--gt")?gt

17
Selecting a specific record login.php // BASIC
ERROR CHECKING // USER NAME if
(strlen(_POST'user_name') lt 0)
error_message. "ltpgtYou forgot to enter your
username!lt/pgt" // PASSWORD. if
(strlen(_POST'password') lt 0)
error_message. "ltpgtYou forgot to enter your
password!lt/pgt"

18
Selecting a specific record login.phplt? //
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_row
s(result)1) // 5- get results myrow
mysql_fetch_array(result) // 6- print
results echo 'ltpgtWelcome back
'.myrowfirst_name".' '. myrow"last_name"
else // no records were found echo
'ltpgtThat username and password couldn\'t be
foundlt/pgt' // 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

19
Selecting a specific record Upload to the
server and test for errors

20
USING UPDATE
21
Using Update Update allows us to change
existing recordsThe basic query is UPDATE with
a WHERE clauseWHERE allows us to qualify what
records we updateSET tells the database what to
changeFor example query UPDATE
tableName SET passwordpassword WHERE
user_id5"

Column Names
22
Using Update Open change_password.php

23
Using Update changepassword.phplt?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(r
esult)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
'ltpgtThat username and password couldn\'t be
foundlt/pgt' // Display the form.
print_changepass_form() else
// there's an error - print error message
echo 'ltpgt'.error_message.'lt/pgt' // Display
the form. print_changepass_form() else
// Display the form. print_changepass_form()
// End of the main SUBMIT conditional.?gt

24
Using Update changepassword.phplt? USER
DEFINED FUNCTIONSfunction print_changepass_form()
print("lt!-- Start Form --gtltform
action"._SERVER'PHP_SELF'."
method'post'gtltfieldsetgtltlegendgtTo change your
password, please enter your current name and
passwordltbrgt and your new passwordlt/legendgtltpgtlt
bgtUser Namelt/bgt ltinput type'text'
name'user_name' value'"._POST'user_name'."'
size'20' maxlength'10' /gtlt/pgtltpgtltbgtPasswordlt/
bgt ltinput type'password' name'password'
value'"._POST'password'."' size'20'
maxlength'10' /gtlt/pgtltpgtltbgtNEW Passwordlt/bgt
ltinput type'password' name'new_password'
value'"._POST'new_password'."' size'20'
maxlength'10' /gtlt/pgtltdiv align'center'gtltinput
type'submit' name'submit' value'Change
Password' /gtlt/divgtlt/formgtlt!-- End of Form
--gt")?gt

25
Using Update changepassword.phplt? // BASIC
ERROR CHECKING // user name if
(strlen(_POST'user_name') lt 0)
error_message. "ltpgtYou forgot to enter your
username!lt/pgt" // password if
(strlen(_POST'password') lt 0)
error_message. "ltpgtYou forgot to enter your
password!lt/pgt" // new password if
(strlen(_POST'new_password') lt 0)
error_message. "ltpgtYou forgot to enter your
NEW password!lt/pgt" ?gt

26
Using Update changepassword.phplt?// PART 1 -
CHECK FOR USER IN DATABASE GET ID// PART 1a -
formulate the question user_name_POST'user_n
ame' // convert to make it easierpassword_PO
ST'password' // convert to make it
easierquery"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())

27
Using Update changepassword.phplt? // PART
1c - test for a resultif(mysql_num_rows(result)
1) // PART 2 - IF USER EXISTS UPDATE
INFORMATION USING THE USER'S ID // PART 2a - get
the user id rowmysql_fetch_array(result) us
er_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 'ltPgtYour password has been
changedlt/pgt' else echo 'ltPgt There has been
an error. Please contact the webmasterlt/pgt'
else // there's an error user not
found echo 'ltpgtThat username and password
couldn\'t be foundlt/pgt' // Display the form.
print_changepass_form() ?gt

28
INSERTING THE DATETIME WHEN A USER REGISTERS
29
Inserting the DateTime when a user registers
register.php// 2- formulate the
questionquery"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_rowsgt0) // get info
while (myrow mysql_fetch_array(result))
user_idmyrow"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 ltbrgt")