Membership

Main Menu

Forum Boards

Stats

20 tutorials
74,815 members
734,914 forum posts
13 blog posts

Tutorials

PHP Basic Database Handling

by Crayon Violent on Jun 20, 2008 2:06:06 PM

The Goods

Okay, okay! Here's the whole script! Step away from the google button!

Anyways, the script will display the names from the database. You can edit the names, delete names, add a new name, and sort by the name or id. The code is divided into two parts: "Dealing with the database" and "List everything out."

<?php
/**** Dealing with the database ****/
// connect to db
$conn = mysql_connect('localhost','dbusername','dbpassword') or trigger_error("SQL", E_USER_ERROR); 
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

// INSERT: if we have a name to add...
if($_POST['name']) {
   // little bit of cleaning...
   $name = mysql_real_escape_string($_POST['name']);
   // insert new name into table
   $sql = "INSERT INTO info (id, name) VALUES ('','$name')";
   $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
} // end if

// UPDATE: if we have name(s) to change...
if($_POST['cname']) {
   // for each name to change...
   foreach($_POST['cname'] as $cid => $cname) {
      // little bit of cleaning...
      $id = mysql_real_escape_string($cid);
      $name = mysql_real_escape_string($cname);
      // update name in the table  
      $sql = "UPDATE info SET name = '$name' WHERE id = '$id'";
      $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
   } // end foreach
} // end if
	 
// DELETE: if we have a name to delete...
if($_GET['name']) {
   // little bit of cleaning...
   $name = mysql_real_escape_string($_GET['name']);
   // delete name from table
   $sql = "DELETE FROM info WHERE name = '$name'";
   $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
} // end if

// ORDERBY: if one of the links was clicked..
if ($_GET['orderby']) {
   // make an aray of allowed names
   $allowed = array('id','name');
   // bit of cleaning...
   $order = mysql_real_escape_string($_GET['orderby']);
   // is it a valid column name? yes: use it. no: default to 'id'
   $order = (in_array($order, $allowed))? $order : "id";
// if no link clicked, default to 'id'
} else {
   $order = "id";
} // end else

// SELECT: get the list of names from database
$sql = "SELECT id, name FROM info ORDER BY $order";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
/**** end deal with the database ****/

/**** list everything out ****/
// list columns
echo <<<LISTCOLS
<form action = '{$_SERVER['PHP_SELF']}' method = 'post'>
<table border = '1'>
   <tr>
      <td><a href = '{$_SERVER['PHP_SELF']}?orderby=id'>id</td>
      <td><a href = '{$_SERVER['PHP_SELF']}?orderby=name'>name</td>
      <td>delete</td>
   </tr>
LISTCOLS;

// loop through list of names 
while ($list = mysql_fetch_assoc($result)) {
echo <<<LISTINFO
   <tr>
      <td>{$list['id']}</td>
      <td><input type = 'text' name = 'cname[{$list['id']}]' value = '{$list['name']}'>
      <td><a href = '{$_SERVER['PHP_SELF']}?name={$list['name']}'>delete</a></td>
   </tr>
LISTINFO;
} // end while

// list input box for adding new entry
echo <<<NEWENTRY
   <tr>
      <td bgcolor = 'gray'></td>
      <td><input type = 'text' name = 'name'></td>
      <td bgcolor = 'gray'></td>
   </tr><tr>
      <td></td>
      <td align = 'center'><input type = 'submit' value = 'submit'></td>
      <td></td>
   </tr>
</table>
</form>	 
NEWENTRY;
/**** end list everything out ****/

« Previous
1
2
3
4
5
6
7
8
9
10
11
12
13
Next »

Comments

This is an incredible tutorial, i knew how to handle databases but it was so goood to recap, plus a few little things that i learned, and you have a nice way to explain. I had a great time reading it. Thanks :D

1. fearlex on Jun 27, 2008 3:54:27 PM

We're using ODBC-connect, ODBC_exec, etc. How do I create a new record using ODBC? I don't imagine I can do it with mysql commands?

2. peteschulte on Jul 23, 2008 6:36:06 PM

Well I don't really know anything about ODBC but they do have their own database handling functions in PHP that are very similar to all of the mysql_xxx counterparts. Here is a link to the functions php offers for ODBC: PHP ODBC functions

A kind of skimmed over the functions and it looks like all you really need to do is switch out the mysql_xxx functions with their ODBC_xxx counterparts.

All the code for the form and receiving info from the user etc.. would not need to be altered.

3. Crayon Violent on Jul 26, 2008 3:02:30 PM

I knew that how to handle the database through PHP, but after reading this tutorial i think it's more than i think. It's really good. Thanks!

4. deepshah on Aug 11, 2008 2:05:41 AM

I've just started to learn how to use PHP with MySQL so this tutorial has been *very* useful!

I had already put together scripts and an html form to accomplish similar things to those shown in the tutorial but with the scripts in several seperate .php files. Having everything in one file makes much more sense!

Many thanks

5. ma2tt on Aug 16, 2008 10:29:59 AM

Great Job 'Crayon Violent'!

I like the way you presented the information.
Where Can I go from here to learn more things.
Please guide me!

Many Thanks!

6. mahender on Nov 24, 2008 4:06:02 AM

PHP Freaks