PHP Basic : Connect, Insert, Upadate, Delete, Select.

CONNECT TO DATABSE


<?php
  //these variables are used for database connection
  $host = "localhost";
  $user = "db_user";
  $pass = "db_pass";
  $database = "db_name";

  //connect to the database.
  $db = mysql_connect($host, $user, $pass);
  mysql_select_db ($database);
?>



INSERT


<?php
  //load database connection
  //...

  //we will pretend we're getting the info from a form.
  $firstName = $_POST['firstName'];
  $lastName = $_POST['lastName'];
  $emailAddress = $_POST['email'];
  $zipCode = $_POST['zip'];

  //do your error checking on the input.
  //...

  //Make the query to insert into the table.
  $query = "INSERT INTO `people` (`firstName`,`lastName`,`email`,`zip`, `dateAdded`)
                 VALUES ('$firstName','$lastName','$emailAddress','$zipCode', NOW());";

  //run the query to insert the person.
  $result = mysql_query($query) OR die(mysql_error());

  //let them know the person has been added.
  echo "You have successfully added " . $firstName . " " . $lastName . " to the database.";
?>


UPDATE



<?php
  //load database connection
  //...

  //we will pretend we're getting the info from a form.
  $firstName = $_POST['firstName'];
  $lastName = $_POST['lastName'];
  $emailAddress = $_POST['email'];
  $zipCode = $_POST['zip'];

  //we want to know who we are supposed to update.
  //It would be safer to grab the userid from a cookie or session
  //and check with the database to make sure it's a valid user
  //and that the person is trying to update their own info and not
  //someone elses info.
  $userId = $_POST['userId'];

  //do your error checking on the input.
  //...

  //Make the query to update the table.
  $query = "UPDATE `people` SET firstName='$firstName', lastName='$lastName',
                 email='$emailAddress', zip='$zipcode' WHERE userid='$userId';";

  //run the query to update the person.
  $result = mysql_query($query) OR die(mysql_error());

  //let them know the person has been added.
  echo "You have successfully updated " . $firstName . " " . $lastName . " in the database.";
?>





DELETE

<?php
  //load database connection
  //...

  //get the userid of the person we're deleting.
  $userId = $_POST['userId'];

  //write the query to delete the person.
  $query = "DELETE FROM `people` WHERE userid='$userId'";

  //run the query to delete the person.
  $result = mysql_query($query) OR die(mysql_error());

  echo 'You successfully deleted the user.';
?>




SELECT

php:
<?php
  //load database connection
  //...

  //make the query to run.
  $query = "SELECT * FROM `people`";
  $result = mysql_query($query) OR die(mysql_error());

  //now we turn the results into an array and loop through them.
  while($row = mysql_fetch_array($result))
  {
    $firstName = $row['firstName'];
    $lastName = $row['lastName'];

    echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
  }
?>


SELECT Statement (With ordering):

<?php
  //load database connection
  //...

  //make the query to run.
  //Sort the last name in an ascending order (A-Z)
  $query = "SELECT * FROM `people` ORDER BY lastName ASC";
  $result = mysql_query($query) OR die(mysql_error());

  //now we turn the results into an array and loop through them.
  while($row = mysql_fetch_array($result))
  {
    $firstName = $row['firstName'];
    $lastName = $row['lastName'];

    echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
  }
?>


SELECT Statement (Limiting results):

<?php
  //load database connection
  //...

  //make the query to run.
  //order the date descending (most recent first) and get the last 5 entries.
  $query = "SELECT * FROM `people` ORDER BY dateAdded DESC LIMIT 5";
  $result = mysql_query($query) OR die(mysql_error());

  //now we turn the results into an array and loop through them.
  while($row = mysql_fetch_array($result))
  {
    $firstName = $row['firstName'];
    $lastName = $row['lastName'];

    echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
  }
?>




SELECT Statement (with WHERE clause):

<?php
  //load database connection
  //...

  //make the query to run.
  //UPPER makes the lastName uppercase.  This way if the first character is lowercase,
  //it will still find it.
  $query = "SELECT * FROM `people` WHERE UPPER(lastName) LIKE 'W%'";
  $result = mysql_query($query) OR die(mysql_error());

  //now we turn the results into an array and loop through them.
  while($row = mysql_fetch_array($result))
  {
    $firstName = $row['firstName'];
    $lastName = $row['lastName'];

    echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
  }
?>


SELECT Statement (Counting the results):

<?php
  //load database connection
  //...

  //get the username and password from the form.
  $userName = $_POST['username'];
  $password = $_POST['password'];

  //make the query to run.
  $query = "SELECT * FROM `people` WHERE username='$userName' AND password='$password'";
  $result = mysql_query($query) OR die(mysql_error());
  $count = mysql_num_rows($result);

  //if count > 0, then it returned a result, meaning the info provided was valid.
  //now we can welcome them.
  if($count > 0) {
    //since there can only be one person with that username and password,
    //we don't have to loop
    $row = mysql_fetch_array($result);

    $firstName = $row['firstName'];

    echo 'Welcome back, '.$firstName.'!<br/>';
  }
  else
  {
    echo 'The information you provided was not valid.';
  }
?>