Solved

php simple database manipulation

Posted on 2007-03-29
5
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

690 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