HTML: MySQL Database Handling in PHP

MySQL Database Handling in PHP

Written by John L

Continued from page 1

The @ operator is used to suppress any error messages that mysql_connect() and mysql_select_db() functions may produce if an error occurred. The die() function is used to end repparttar script execution and display a custom error message.

Executing SQL Statements against a MySQL database

Once repparttar 105568 connection and database selection is successfully performed, repparttar 105569 PHP script can now proceed to operate on repparttar 105570 database using standard SQL statements. The mysql_query() function is used for executing standard SQL statements against repparttar 105571 database. In repparttar 105572 following example, repparttar 105573 PHP script queries a table called tbl_login in repparttar 105574 previously selected database to determine if a username/password pair provided by repparttar 105575 user is valid.

Assumption: The tbl_login table has 3 columns named login, password, last_logged_in. The last_logged_in column stores repparttar 105576 time that repparttar 105577 user last logged into repparttar 105578 system.

// The $username and $passwd variable should rightly be set by repparttar 105579 login form // through repparttar 105580 POST method. For repparttar 105581 purpose of this example, we’re manually coding it. $username = “john”; $passwd = “mypassword”;

// We generate a SELECT SQL statement for execution. $sql="SELECT * FROM tbl_login WHERE login = '".$username."' AND password = '".$passwd."'";

// Execute repparttar 105582 SQL statement against repparttar 105583 currently selected database. // The results will be stored in repparttar 105584 $r variable. $r = mysql_query($sql);

// After repparttar 105585 mysql_query() command executes, repparttar 105586 $r variable is examined to // determine of repparttar 105587 mysql_query() was successfully executed. if(!$r) { $err=mysql_error(); print $err; exit(); }

// If everything went well, check if repparttar 105588 query returned a result – i.e. if repparttar 105589 username/password // pair was found in repparttar 105590 database. The mysql_affected_rows() function is used for this purpose. // mysql_affected_rows() will return repparttar 105591 number of rows in repparttar 105592 database table that was affected // by repparttar 105593 last query if(mysql_affected_rows()==0){ print "Username/password pair is invalid. Please try again."; } else {

// If successful, read out repparttar 105594 last logged in time into a $last variable for display to repparttar 105595 user $row=mysql_fetch_array($r); $last=$row["last_logged_in"]; print “Login successful. You last logged in at ”.$last.”.”;

}

The above example demonstrated how a SELECT SQL statement is executed against repparttar 105596 selected database. The same method is used to execute other SQL statements (e.g. UPDATE, INSERT, DELETE, etc.) against repparttar 105597 database using repparttar 105598 mysql_query() and mysql_affected_rows() functions.