Solved

php simple database manipulation

Posted on 2007-03-29
5
198 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now