PHP+database+code

Note: This wiki mangles comments made with two slashes sometimes. There might be something that looks like code below which is actually supposed to be a comment after two slashes...

$server = mysql_connect(‘host.internet.com’,’username’,’password’); //the hostname will often be “localhost”// in other words, the same computer as the server if(!$server) { die (“Unable to connect to database” . mysql_error); }
 * Connecting to a Database Server:**

mysql_select_db(‘website’); //on the student web server, you have one database – and it’s name is your MacID//
 * Selecting to your specific database:**

//**Executing basic queries on a table from the database:**// //$r = mysql_query('SELECT * FROM users');// r for result... $n = mysql_num_rows($r); //n will be number of rows in result, from 0 to whatever...// //$p = mysql_result($r,0,'password');// p will be the password field/column of the 1st user

$q = 'SELECT * FROM users WHERE login="'; //note the quotations carefully!// //$q .= $login;// we assume the variable login exists, possibly taken from an HTML form $q .= '";' //note the quotations carefully// //$r = mysql_query($q);// //$n = mysql_num_rows($r);// //if($n == 1) {// //$p = mysql_result($r,0,'password');// //} else {// //echo "Error: User not found.";// //}//
 * But in practice it is a MUCH better strategy to construct the MySQL query using variables:**

//**To change fields in the database, construct a MySQL "UPDATE" query and execute it:**// //$q = 'UPDATE users SET age=';// //$q .= $newAge;// //$q .= ' WHERE login="';// //$q .= $login;// perhaps $login was set as follows: $login = $_GET['login']; $q .= '";'; $r = mysql_query($q);

mysql_close($s); $s is a variable returned by mysql_connect (above) obviously...
 * When you're finished - close the server:**

function getPassword($login) { $s = mysql_connect(‘host.internet.com’,’username’,’password’); if(!$s) die (“Unable to connect to database” . mysql_error); $q = 'SELECT * FROM users WHERE login="'; //note the quotations carefully!// //$q .= $login;// we assume the variable login exists, possibly taken from an HTML form $q .= '";' //note the quotations carefully// //$r = mysql_query($q);// //$n = mysql_num_rows($r);// //if($n == 1) $p = mysql_result($r,0,'password');// //else $p = '';// if no password is found, $p will be an empty string mysql_close($s); return $p; }
 * Of course, it can make a lot of sense to put common operations on your database in functions like:**

//now if everything is working, you can look up a user's password from (say) an HTML form value just like this...// //$password = getPassword($_GET['login']);// //if($password != '') {// if user was found, in other words //allow login to proceed// //} else {// //echo "User not found.";// //}//

You could make a whole set of functions to look up certain kinds of things in your database, keep them in a separate file //and then include it using require_once// //require_once "databaseFunctions.php"//

//**What about a function to set a particular value of a database?:**// //function setStatus($login,$newStatus) {// $s = mysql_connect(‘host.internet.com’,’username’,’password’); if(!$s) die (“Unable to connect to database” . mysql_error); $q = 'UPDATE users SET status ="'; //note the quotations carefully// //$q .= $newStatus;// //$q .= '"// //WHERE// //login="';// note the quotations carefully! $q .= $login; //$q .= '";'// note the quotations carefully $r = mysql_query($q); // no need to return a value - the point of this function isn't to inquire, it is to change }