Link to home
Start Free TrialLog in
Avatar of sbsupport
sbsupportFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Need to insert/update data to an sql database

Below is a quick preview of my table.

I have the most set up but I really need help on having the data from the form put into sql (and would like to keep all coding on one page)

Many Thanks in advanced!

=====================================================
[________] [Search]

ID   |   Start    |   Complete   |   Name   |   Subject   |   Enquiry   |   Assigned
----------------------------------------------------------------------------------------------
1     |01-01-01|   Yes            |    Jason  |    E-Mail     |    How do..|    JD
2     |02-01-01|   Yes            |    Adam  |    Printer    |    Is this... |    JD  <--any clicked, data goes below

Name:    [________]                                    Complete: [________]
Subject: [________]                                    Assigned:  [________]

[New Record] | [Update Record] | [Clear]
=====================================================

The PHP script is attached.

Thanks
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="table.css" type="text/css">
</head>
<body background="/Themes/default/images/main_bg.gif" style="margin: 0 0 0 0;">
<?
 
$idsel = $_GET['id'];
$search = $_GET['search'];
$sort = $_GET['sort'];
$order = $_GET['order'];
 
 if($order =='ASC'){
 $vorder = 'DESC';
 }
 else{
 $vorder = 'ASC';
 }
 
@$vstart = $_GET["vstart"];
if($vstart =='')
    $vstart =0;
 
$host = "localhost";
$username = "root";
$password = "password";
$db = "helpdesk";
$table ="helpdesk_data";
 
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
//total number of records in the table
 
$res = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table`");
@$rows = mysql_num_rows ($res);
 
if($order ==''){
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE id LIKE \"%$search%\" OR (start LIKE \"%$search%\" or  name LIKE \"%$search%\" or  subject LIKE \"%$search%\" or enquiry LIKE \"%$search%\" or  follow_up LIKE \"%$search%\") ORDER BY complete ASC, id DESC LIMIT $vstart,25");}
else{
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE id LIKE \"%$search%\" OR (start LIKE \"%$search%\" or  name LIKE \"%$search%\" or  subject LIKE \"%$search%\" or enquiry LIKE \"%$search%\" or  follow_up LIKE \"%$search%\") ORDER BY $sort $order LIMIT $vstart,25");}
 
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
echo "<table align='center' width='750'><td><form>
<input type='hidden' name='id' value='$idsel'>
<input type='hidden' name='sort' value='complete asc,'>
<input type='hidden' name='order' value='id desc'>
<input name='search' type='text' value='$search'>&nbsp;
<input type='submit' value='search'></td></table>";
$cols = mysql_num_fields($result);
$records = mysql_num_rows ($result);
 echo "<table align='center' bgcolor='FFFFFF' width='750' cellspacing='1' cellpadding='0' border='0' >";
 echo "<tr bgcolor='505050' class='menu'>";
 
 for ($i = 0; $i < $cols;$i++)
 {
    echo "<td align='center'><a href='?id=$idsel&search=$search&sort=".mysql_field_name($result,$i)."&order=$vorder&vstart=$vstart'>".mysql_field_name($result,$i)."</a></td>";
 }
 echo "</tr>";
 while ($row = mysql_fetch_array($result, MYSQL_NUM))
 {
    echo "<tr bgcolor='F6F6F6' class='normal'>";
 
$idget = $row[0];
 
   foreach ($row as $value)
   { echo "<td class='table'><a href='?id=$idget&search=$search&sort=$sort&order=$order&vstart=$vstart'>".$value ."</a></td>";
   } 
   echo "</tr>";
 }
 $end = $vstart + $records;
 echo "
 
 <tr>
<td width='30'></td>
<td width='80'></td>
</tr>
 
 <tr  align = 'center' bgcolor = '505050' class='menu'><td colspan=$cols>";
 
     if($vstart != 0)
    {
    $prev = $vstart - 25;
    echo "<font size='2'>|</font> <a href='?id=$idsel&search=$search&sort=$sort&order=$order&vstart=$prev'>Previous</a> <font size='2'>|</font>";
    }
 
 echo " Records $vstart  to  $end  of $rows ";
 
 
      if($vstart<$rows-25)
     {
       $next = $vstart + 25;
       echo "<font size='2'>|</font> <a href='?id=$idsel&search=$search&sort=$sort&order=$order&vstart=$next'>Next</a> <font size='2'>|</font>";
    }
 
 
echo "</td></tr></form></table>";
 
  if ($id) {
    $sql = "SELECT * FROM helpdesk_data WHERE id=$id";
    $result = mysql_query($sql);
    $myrow = mysql_fetch_array($result);
    $start = $myrow["start"];
    $complete = $myrow["complete"];
    $name = $myrow["name"];
    $email = $myrow["email"];
    $subject = $myrow["subject"];
    $enquiry = $myrow["enquiry"];
    $ext = $myrow["ext"];
    $follow_up = $myrow["follow_up"];
    $finish = $myrow["finish"];
    $assigned_to = $myrow["assigned_to"];
    $attachment = $myrow["attachment"];
 
echo "<input type=hidden name='id' value='$id''>";
  }
 
$newv = "INSERT INTO helpdesk_data (start, complete, name, email, subject, enquiry, ext, follow_up, finish, assigned_to, attachment) VALUES ($start, $complete, $name, $email, $subject, $enquiry, $ext, $follow_up, $finish, $assigned_to, $attachment)";
 
$new = $_GET['new'];
if($new =='New Record'){
$newv;
 }
else{}
 
$updatev = "UPDATE helpdesk_data SET start = $start, complete = $complete, name = $name, email = $email, subject = $subject, enquiry = $enquiry, ext = $ext, follow_up = $follow_up, finish = $finish, assigned_to = $assigned_to, attachment = $attachment WHERE id = $id";
 
$update = $_GET['update'];
if($update =='Update Record'){
$updatev;
}
else{}
 
echo "<table align='center' width='750'><td>
<form>
<tr>
<td width='50'>ID:</td><td>$id</td><input name='id' type='hidden' value='$id'>
<td width='50'>Start:</td><td><input name='start' type='text' value='$start'></td>
</tr>
<tr>
<td width='50'>Name:</td><td><input name='name' type='text' value='$name'></td>
<td width='50'>Complete:</td><td><input name='complete' type='text' value='$complete'></td>
</tr>
<tr>
<td width='50'>Subject:</td><td><input name='subject' type='text' value='$subject'></td>
<td width='50'>Assigned:</td><td><input name='assigned' type='text' value='$assigned_to'></td>
</tr>
<tr>
<td width='50'>Enquiry:</td><td><textarea name='enquiry' cols=35 rows=8>$enquiry</textarea></td>
<td width='50'>Follow:</td><td><textarea name='follow' cols=35 rows=8>$follow_up</textarea></td>
</tr>
<tr>
<td width='50'>Ext:</td><td><input name='ext' type='text' value='$ext'></td>
<td width='50'>Finished:</td><td><input name='finished' type='text' value='$finish'></td>
</tr>
<tr>
<td width='50'>Attachment:</td><td><a target='_blank' href='/support/admin/files/$attachment'>$attachment</a></td>
</tr>
</table>
<table align='center' width='750'><td>
<tr>
<td width='10'><input name='new' type='submit' value='New Record'></td>
<td width='10'><input name='update' type='submit' value='Update Record'></td></form>
<td><form>
<input name='id' type='hidden' value=''>
<input name='clear' type='submit' value='Clear'></td></form>
</tr></td></table>";
?>
</body>
</html>

Open in new window

screenshot.png
ASKER CERTIFIED SOLUTION
Avatar of Angelp1ay
Angelp1ay
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sbsupport

ASKER

Many Thanks !

Code used is attached :)
=========
index.php
=========
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="table.css" type="text/css">
</head>
<body background="/Themes/default/images/main_bg.gif" style="margin: 0 0 0 0;">
<?
 
$idsel = $_GET['id'];
$search = $_GET['search'];
$sort = $_GET['sort'];
$order = $_GET['order'];
 
 if($order =='ASC'){
 $vorder = 'DESC';
 }
 else{
 $vorder = 'ASC';
 }
 
@$vstart = $_GET["vstart"];
if($vstart =='')
    $vstart =0;
 
$host = "localhost";
$username = "root";
$password = "password";
$db = "helpdesk";
$table ="helpdesk_data";
 
$link = mysql_connect($host,$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
    die ("Can't use $db : " . mysql_error());
}
//total number of records in the table
 
$res = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table`");
@$rows = mysql_num_rows ($res);
 
if($order ==''){
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE id LIKE \"%$search%\" OR (start LIKE \"%$search%\" or  name LIKE \"%$search%\" or  subject LIKE \"%$search%\" or enquiry LIKE \"%$search%\" or  follow_up LIKE \"%$search%\") ORDER BY complete ASC, id DESC LIMIT $vstart,25");}
else{
$result = mysql_query("SELECT helpdesk_data.id as 'ID', helpdesk_data.start as 'Start', helpdesk_data.complete as 'Complete', if(CHAR_LENGTH(helpdesk_data.name)>15, concat(left(helpdesk_data.name,15),'...'), helpdesk_data.name) as 'Name', if(CHAR_LENGTH(helpdesk_data.subject)>20, concat(left(helpdesk_data.subject,20),'...'), helpdesk_data.subject) as 'Subject', if(CHAR_LENGTH(helpdesk_data.enquiry)>50, concat(left(helpdesk_data.enquiry,50),'...'), helpdesk_data.enquiry) as 'Enquiry', helpdesk_data.assigned_to as 'Assigned' FROM `$table` WHERE id LIKE \"%$search%\" OR (start LIKE \"%$search%\" or  name LIKE \"%$search%\" or  subject LIKE \"%$search%\" or enquiry LIKE \"%$search%\" or  follow_up LIKE \"%$search%\") ORDER BY $sort $order LIMIT $vstart,25");}
 
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
echo "<table align='center' width='740'><td><form>
<input type='hidden' name='id' value='$idsel'>
<input type='hidden' name='sort' value='complete asc,'>
<input type='hidden' name='order' value='id desc'>
<input name='search' type='text' value='$search'>&nbsp;
<input type='submit' value='search'></td></table>";
$cols = mysql_num_fields($result);
$records = mysql_num_rows ($result);
 echo "<table align='center' bgcolor='FFFFFF' width='740' cellspacing='1' cellpadding='0' border='0' >";
 echo "<tr bgcolor='505050' class='menu'>";
 
 for ($i = 0; $i < $cols;$i++)
 {
    echo "<td align='center'><a href='?id=$idsel&search=$search&sort=".mysql_field_name($result,$i)."&order=$vorder&vstart=$vstart'>".mysql_field_name($result,$i)."</a></td>";
 }
 echo "</tr>";
 while ($row = mysql_fetch_array($result, MYSQL_NUM))
 {
    echo "<tr bgcolor='F6F6F6' class='normal'>";
 
$idget = $row[0];
 
   foreach ($row as $value)
   { echo "<td class='table'><a href='?id=$idget&search=$search&sort=$sort&order=$order&vstart=$vstart'>".$value ."</a></td>";
   } 
   echo "</tr>";
 }
 $end = $vstart + $records;
 echo "
 
 <tr>
<td width='30'></td>
<td width='80'></td>
</tr>
 
 <tr  align = 'center' bgcolor = '505050' class='menu'><td colspan=$cols>";
 
     if($vstart != 0)
    {
    $prev = $vstart - 25;
    echo "<font size='2'>|</font> <a href='?id=$idsel&search=$search&sort=$sort&order=$order&vstart=$prev'>Previous</a> <font size='2'>|</font>";
    }
 
 echo " Records $vstart  to  $end  of $rows ";
 
 
      if($vstart<$rows-25)
     {
       $next = $vstart + 25;
       echo "<font size='2'>|</font> <a href='?id=$idsel&search=$search&sort=$sort&order=$order&vstart=$next'>Next</a> <font size='2'>|</font>";
    }
 
 
echo "</td></tr></form></table>";
 
  if ($id) {
    $sql = "SELECT * FROM helpdesk_data WHERE id=$id";
    $result = mysql_query($sql);
    $myrow = mysql_fetch_array($result);
    $start = $myrow["start"];
    $complete = $myrow["complete"];
    $name = $myrow["name"];
    $email = $myrow["email"];
    $subject = $myrow["subject"];
    $enquiry = $myrow["enquiry"];
    $ext = $myrow["ext"];
    $follow_up = $myrow["follow_up"];
    $finish = $myrow["finish"];
    $assigned_to = $myrow["assigned_to"];
    $attachment = $myrow["attachment"];
 
echo "<form action='process_form.php' method='post'><input type='hidden' name='id' value='$id''>";
  }
 
echo "<table align='center' width='740'><td>
<tr>
<td width='50'>ID:</td><td>$id</td>
<td width='50'>Start:</td><td><input name='start' type='text' value='$start'></td>
</tr>
<tr>
<td width='50'>Name:</td><td><input name='name' type='text' value='$name'></td>
<td width='50'>Complete:</td><td><input name='complete' type='text' value='$complete'></td>
</tr>
<tr>
<td width='50'>Subject:</td><td><input name='subject' type='text' value='$subject'></td>
<td width='50'>Assigned:</td><td><input name='assigned' type='text' value='$assigned_to'></td>
</tr>
<tr>
<td width='50'>Enquiry:</td><td><textarea name='enquiry' cols=35 rows=8>$enquiry</textarea></td>
<td width='50'>Follow:</td><td><textarea name='follow' cols=35 rows=8>$follow_up</textarea></td>
</tr>
<tr>
<td width='50'>Ext:</td><td><input name='ext' type='text' value='$ext'></td>
<td width='50'>Finished:</td><td><input name='finished' type='text' value='$finish'></td>
</tr>
<tr>
<td width='50'>Attachment:</td><td><a target='_blank' href='/support/admin/files/$attachment'>$attachment</a></td>
</tr>
</table>
<table align='center' width='740'><td>
<tr>
<td width='10'><input name='new' type='submit' value='New Record'></td>
<td width='10'><input name='update' type='submit' value='Update Record'></td></form>
<td><form>
<input name='id' type='hidden' value=''>
<input name='clear' type='submit' value='Clear'></td></form>
</tr></td></table>";
?>
</body>
</html>
 
================
process_form.php
================
 
<?php
 
@$new = $_POST['new'];
@$update = $_POST['update'];
 
// Receiving IP and Host Name
$REMOTE_ADDR = getenv ("REMOTE_ADDR");
$REMOTE_HOST = gethostbyaddr ("$REMOTE_ADDR");
 
// Receiving variables
@$start = addslashes($_POST['start']);
@$complete = addslashes($_POST['complete']);
@$name = addslashes($_POST['name']);
@$email = addslashes($_POST['email']);
@$subject = addslashes($_POST['subject']);
@$enquiry = addslashes($_POST['enquiry']);
@$ext = addslashes($_POST['ext']);
@$attachment_Name = $_FILES['attachment']['name'];
@$attachment_Size = $_FILES['attachment']['size'];
@$attachment_Temp = $_FILES['attachment']['tmp_name'];
@$attachment_Mime_Type = $_FILES['attachment']['type'];
 
function RecursiveMkdir($path)
 {
   if (!file_exists($path)) 
   { 
      RecursiveMkdir(dirname($path));
      mkdir($path, 0777);
    }
  }
 
if( $attachment_Size == 0){}
else
{
$uploadDir = "../admin/files/";
$uploadFile = "../admin/files/".$attachment_Name ;
$fileExt = substr(strrchr($attachment_Name, "."), 1);
$randName = md5(rand() * time());
  {
  @chmod(dirname($uploadFile), 0777);
  }
$filePath = $uploadDir . $randName . '.' . $fileExt;
@move_uploaded_file( $attachment_Temp , $filePath);
chmod($filePath, 0644);
$attachment_URL = "http://forum/support/admin/files/".$randName . '.' . $fileExt;
$md5Name = $randName . '.' . $fileExt;
}
 
 
 if($new =='New Record'){
 //saving record to MySQL database
@$pfw_strQuery = "INSERT INTO `helpdesk_data`(`start`,`complete`,`name`,`email`,`subject`,`enquiry`,`ext`,`attachment`)VALUES (\"$start\",\"$complete\",\"$name\",\"$email\",\"$subject\",\"$enquiry\",\"$ext\",\"$md5Name\")" ;
@$pfw_host = "localhost";
@$pfw_user = "root";
@$pfw_pw = "password";
@$pfw_db = "helpdesk";
$pfw_link = mysql_connect($pfw_host, $pfw_user, $pfw_pw);
if (!$pfw_link) {
 die('Could not connect: ' . mysql_error());
}
$pfw_db_selected = mysql_select_db($pfw_db, $pfw_link);
if (!$pfw_db_selected) {
die ('Can not use $pfw_db : ' . mysql_error());
}
 
//insert new record
$pfw_result = mysql_query($pfw_strQuery);
if (!$pfw_result) {
 die('Invalid query: ' . mysql_error());
}
mysql_close($pfw_link);
}
 else{}
 
  if($update =='Update Record'){
 
  //updating record in MySQL database
 
@$pfw_strQuery = "UPDATE helpdesk_data SET start = \"$start\", complete = \"$complete\", name = \"$name\", email = \"$email\", subject = \"$subject\", enquiry = \"$enquiry\", ext = \"$ext\", attachment = \"$md5name\" WHERE id = \"$id\"";
@$pfw_host = "localhost";
@$pfw_user = "root";
@$pfw_pw = "Ae3B7DdG2";
@$pfw_db = "helpdesk";
$pfw_link = mysql_connect($pfw_host, $pfw_user, $pfw_pw);
if (!$pfw_link) {
 die('Could not connect: ' . mysql_error());
}
$pfw_db_selected = mysql_select_db($pfw_db, $pfw_link);
if (!$pfw_db_selected) {
die ('Can not use $pfw_db : ' . mysql_error());
}
 
//updating record
$pfw_result = mysql_query($pfw_strQuery);
if (!$pfw_result) {
 die('Invalid query: ' . mysql_error());
}
mysql_close($pfw_link);
  
  }
 else{}
 
header('Location: http://forum/help/index.php');
die();
?>

Open in new window