Solved

php simple database manipulation

Posted on 2007-03-29
5
200 Views
Last Modified: 2013-12-13
I'm developing a school website which uses php to connect to a database and manipulate it. The web interface allows me to edit and delete records in the database but adding does not work.

Here is the code:




<?php
session_start();
include('configpass.php');
if($_SESSION['loggedin']!==true)
{
  if($_POST['pass']==$password)
  {
    $_SESSION['loggedin']=true;
  }
  else
  {
    include('loginform.php');
  }
}
if($_SESSION['loggedin']===true)
{

/*
My Calendar database editor - version 2.0 - issued 2004-10-23
Created by andy@halfadot.com
*/

?>

<html>
<head>
<style type="text/css">
p {font-family: verdana, arial, helvetica, sans-serif; font-size: 12px; color: #336699; }
h1 {font-family: arial, helvetica, sans-serif; font-size: 15px; color: #336699; }
body {background-color: #ffffff; }
input {background-color: #fafafa; font-family: verdana, sans-serif: font-size: 12px; color: #3333cc; }
textarea {background-color: #fafafa; font-family: verdana, sans-serif; font-size: 12px; color: #3333cc; }
</style>
</head>
<body>

<table width="96%" cellpadding="0" cellspacing="0" border="0" align="center">
<tr>
<td width = "130" valign="top">
<a href="http://www.digitalmidget.com/scripts" target="new"><img src="images/powered-by.gif" width="130" height="60" alt="Visit Now" border="0" /></a><br />
<?php
  echo "<p align='right'><a href='". $_SERVER['PHP_SELF']. "?act=add'>Add Event</a>&nbsp;<br /><br />";
  echo "<a href='". $_SERVER['PHP_SELF']. "?act=edit'>Edit Event</a>&nbsp;<br /><br />";
  echo "<a href='". $_SERVER['PHP_SELF']. "?act=delete'>Delete Event</a>&nbsp;<br /><br />";
  echo "<a href='". $_SERVER['PHP_SELF']. "?act=show'>Show All Events</a>&nbsp;";
?>
</td>
<td width="20"><img src="images/clear.gif" width="20" height="8" alt="" border="0" /></td>
<td valign="top">

<?php
// ===============================================================
// function for minimal validation of form inputs

function goof_check($dat,$titl,$locn,$desc,$itsok)
{
  GLOBAL $itsok;
  $goof_msg = ""; // clear error message string on entry  
  // pseudo-check on date information
  $pieces = explode("-", $dat);
  $yy = strlen($pieces[0]);
  $mm = strlen($pieces[1]);
  $dd = strlen($pieces[2]);
  if (($yy<4) || ($mm<2) || ($dd<2))
  {
    $goof_msg = "Date error: yyyy-mm-dd format is required<br />";
  }
 
  if (strlen($titl)<10)  { $goof_msg.="Title is too short<br />"; }
  if (strlen($locn)<10)  { $goof_msg.="Location is too short<br />"; }
  if (strlen($desc)<10)  { $goof_msg.="Event description is too short<br />"; }
 
  if ($goof_msg)
  {
    echo "<p><font color='#cc3333'><strong>Data Entry Error[s]</strong></font><br>". $goof_msg. "</font></p>";  
    $itsok = "0";
  }
  return $itsok;
}

// ===============================================================
// function for 'pretty' page headers

function hdr($sec_hdr)
{
  echo "<h1>". $sec_hdr. "</h1>";
}  

// ===============================================================
// style, constants and other stuff
$ro1 = "#fdfdfd"; // odd rows
$ro2 = "#f7f7f7"; // even rows
$nodat = "<p><strong>The event database is <font color='#cc3333'>empty</font>.</strong><br /><br />Your selected action is unavailable.</p>";

// ===============================================================
// REAL script begins ... why are we here? Retrieve the 'action'
$act = (!empty($_GET['act'])) ? $_GET['act'] : 'add';

// ===============================================================
// add record module - part 1
if ($act == "add")
{
  $sec_hdr = "Add Calendar Event";
  hdr($sec_hdr);
  ?>
  <form action = <?php echo "'". $_SERVER['PHP_SELF']. "?act=add1'" ?> method="post">
  <p>Event date [yyyy-mm-dd format]<br />
  <input name="dat" size="10" /></p>
  <p>Event title/name [at least 10 characters]<br />
  <input name="titl" size="40" /></p>
  <p>Event location [at least 10 characters]<br />
  <input name="locn" size="40" /></p>
  <p>Event Description/Details [at least 10 characters]<br />
  <textarea name="desc" rows="5" cols="40"></textarea></p>
  <input type="submit" value="Add Event" />
  </form>
  <?
}

// ===============================================================
// add record module - part 2
if ($act == "add1")
{
  $dat = $_POST['dat'];
  $titl = $_POST['titl'];
  $locn = $_POST['locn'];
  $desc = $_POST['desc'];
 
  $itsok = "1";
  goof_check($dat,$titl,$locn,$desc,$itsok); // check user input
  if ($itsok!="0")
  {
    include("../cal_db_conn.php");
    mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
    mysql_select_db($db_name) or die ("Error: Unable to open the database.");
    $query = "INSERT INTO $db_table (id, ev_dat, ev_title, ev_locn, ev_desc) VALUES ('', '$dat' , '$titl' , '$locn' , '$desc' )";
      $result = mysql_query($query);
  }
  $act = ""; // unset action variable
}

// ===============================================================
// delete record module - part 1
if ($act == "delete")
{
  include("../cal_db_conn.php");
  mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
  mysql_select_db($db_name) or die ("Error: Unable to open the database.");
  $query = "SELECT * FROM $db_table";
  $result = mysql_db_query($db_name, $query);
  $recs = mysql_num_rows($result); // any information in database?
  if(!$recs)
  {
    echo $nodat;
  }
  else
  {      
    $sec_hdr = "Delete Calendar Event";
    hdr($sec_hdr);
    echo "<table cellspacing='2' cellpadding='2' border='0'>";
    echo "<tr bgcolor='#efefef'><td><p>Date</p></td><td><p>Event</p></td><td><p>Location</p></td><td><p>Short Description</p></td><td><p>Action</p></td></tr>";
    while ($myrow = mysql_fetch_array($result)) // loop through all results
    {
      $style = $style == $ro1 ? $ro2 : $ro1;
      echo "<tr bgcolor='". $style. "'>\n";
      echo "<td valign='top'><p>". $myrow['ev_dat']. "</p></td>";
      echo "<td valign='top'><p>". substr($myrow['ev_title'], 0, 14). " ...</p></td>";
      echo "<td valign='top'><p>". substr($myrow['ev_locn'], 0, 14). " ...</p></td>";
      echo "<td valign='top'><p>". substr($myrow['ev_desc'], 0, 28). " ...</p></td>";
      echo "<td valign='top'><p><a href='";
      echo $_SERVER['PHP_SELF']. "?act=delete1&rec=". $myrow['id']. "'>delete</a>";
      echo "</p></td>\n";
      echo "</tr>\n";
    }
    echo "</table>";
    echo "<p><img src='images/caution.gif' width='40' height='40' alt='' border='0' align='left' />There is NO confirmation request for deletion!! If you click a 'delete' link, the event record WILL BE DELETED immediately.</p>";
  }
  $act = ""; // unset action variable  
}

// ===============================================================
// delete record module - part 2
if ($act == "delete1")
{
  include("../cal_db_conn.php");
  mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
  mysql_select_db($db_name) or die ("Error: Unable to open the database.");
 
  $id = $_GET['rec'];
  $query = "DELETE from $db_table where id = '$id' ";
  $result = mysql_query($query);
  $act = ""; // unset action variable  
}

// ===============================================================
// edit record module - part 1
if ($act == "edit")
{
  include("../cal_db_conn.php");
  mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
  mysql_select_db($db_name) or die ("Error: Unable to open the database.");
  $query = "SELECT * FROM $db_table";
  $result = mysql_db_query($db_name, $query);
  $recs = mysql_num_rows($result); // any information in database?
  if(!$recs)
  {
    echo $nodat;
  }
  else
  {      
    $sec_hdr = "Edit Calendar Event";
    hdr($sec_hdr);
    echo "<table cellspacing='2' cellpadding='2' border='0'>";
    echo "<tr bgcolor='#efefef'><td><p>Date</p></td><td><p>Event</p></td><td><p>Location</p></td><td><p>Short Description</p></td><td><p>Action</p></td></tr>";
    while ($myrow = mysql_fetch_array($result)) // loop through all results
    {
      $style = $style == $ro1 ? $ro2 : $ro1;
      echo "<tr bgcolor='". $style. "'>\n";
      echo "<td valign='top'><p>". $myrow['ev_dat']. "</p></td>";
      echo "<td valign='top'><p>". substr($myrow['ev_title'], 0, 14). " ...</p></td>";
      echo "<td valign='top'><p>". substr($myrow['ev_locn'], 0, 14). " ...</p></td>";
      echo "<td valign='top'><p>". substr($myrow['ev_desc'], 0, 28). " ...</p></td>";
      echo "<td valign='top'><p><a href='";
      echo $_SERVER['PHP_SELF']. "?act=edit1&rec=". $myrow['id']. "'>edit</a>";
      echo "</p></td>\n";
      echo "</tr>\n";
    }
    echo "</table>";
  }      
  $act = ""; // unset action variable  
}

// ===============================================================
// edit record module - part 2
if ($act == "edit1")
{
  $id = $_GET['rec'];
  include("../cal_db_conn.php");
  mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
  mysql_select_db($db_name) or die ("Error: Unable to open the database.");
  $query = "SELECT * FROM $db_table where id=$id";
  $result = mysql_db_query($db_name, $query);
  $myrow = mysql_fetch_array($result);

  $sec_hdr = "Edit Calendar Event";
  hdr($sec_hdr);
 
  ?>
  <form action = <?php echo "'". $_SERVER['PHP_SELF']. "?act=edit2'" ?> method="post">
  <input type="hidden" name="rec" value="<? echo $myrow['id']; ?>" />
  <p>Event date [in yyyy-mm-dd format]<br />
  <input name="dat" size="10" value="<? echo $myrow['ev_dat']; ?>" /></p>
  <p>Event title/name [at least 10 characters]<br />
  <input name="titl" size="40" value="<? echo $myrow['ev_title']; ?>" /></p>
  <p>Event location [at least 10 characters]<br />
  <input name="locn" size="40" value="<? echo $myrow['ev_locn']; ?>" /></p>
  <p>Event Description/Details [at least 10 characters]<br />
  <textarea name="desc" rows="5" cols="40"><? echo $myrow['ev_desc']; ?></textarea></p>
  <input type="submit" value="Edit Event" />
  </form>
  <?
}

// ===============================================================
// edit record module - part 3
if ($act == "edit2")
{
  // get POSTed data and fire it to database
  $id = $_POST['rec'];
  $dat = $_POST['dat'];
  $titl = $_POST['titl'];
  $locn = $_POST['locn'];
  $desc= $_POST['desc'];

  $itsok = "1";
  goof_check($dat,$titl,$locn,$desc,$itsok); // check user input
  if ($itsok!="0")
  {
    include("../cal_db_conn.php");
    mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
    mysql_select_db($db_name) or die ("Error: Unable to open the database.");
    $query = "UPDATE $db_table SET ev_dat = '$dat', ev_title = '$titl', ev_locn = '$locn', ev_desc = '$desc' WHERE id = '$id' ";
    $result = mysql_query($query);
  }
  $act = ""; // unset action variable
}

// ===============================================================
// show all records module - future events only!
if ($act == "show")
{
  include("../cal_db_conn.php");
  mysql_connect($db_host, $db_login, $db_pass) or die ("Error: Unable to connect to the database.");
  mysql_select_db($db_name) or die ("Error: Unable to open the database.");
  $query = "SELECT * FROM $db_table ORDER by ev_dat";
  $result = mysql_db_query($db_name, $query);
  $recs = mysql_num_rows($result); // any information in database?
  if(!$recs)
  {
    echo $nodat;
  }
  else
  {      
    $sec_hdr = "All Events Listing";
    hdr($sec_hdr);
    while ($myrow = mysql_fetch_array($result)) // loop through all results
    {
      echo "<p><strong>". $myrow['ev_dat']. "</strong><br />";
      echo "Event: ". $myrow['ev_title']. "<br />Location: ". $myrow['ev_locn']. "<br />Details: ". nl2br($myrow['ev_desc']). "</p>";
    }
  }
  $act = ""; // unset action variable  
}
?>
</td>
</tr>
</table>

<?php

// end of the editor scripting

}
?>





Many thanks.
0
Comment
Question by:llantwit
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
elfe69 earned 500 total points
ID: 18815653
Try removing the id from your insert statement, try this:

$query = "INSERT INTO $db_table (ev_dat, ev_title, ev_locn, ev_desc) VALUES ('$dat' , '$titl' , '$locn' , '$desc' )";
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18815659
"adding does not work."

Do you receive an error when you try to add? If so, what error? What input are you providing when trying to add a record?
0
 
LVL 11

Expert Comment

by:elfe69
ID: 18815664
...and show the SQL error

$result = mysql_query($query) or die(mysql_error());
0
 
LVL 24

Expert Comment

by:glcummins
ID: 18815676
In addition to elfe69's suggestion, try adding some feedback on the mysql_query() line after the insert statement:

      $result = mysql_query($query) or die("Unable to insert a record using query: $query. The MySQL error was: " . mysql_error());
0
 
LVL 11

Expert Comment

by:Joseph Melnick
ID: 18815687
Hello,

The included fine needs to have mysql database host, usermame, and password plus the name of the database where your database table exists.

1) check that the database/table exist
2) check that the above are correct and that username/password has been granted insert into that database / table

Joseph Melnick
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question