Reputation: 511

editing a record in a database with a form

first of all i would like to appolagise on the amount of code i am about to paste, i didn't want to snippet any more incase its a bit that's giving me the errors

i have a table named contacts and want to update the table by a form.

i am not sure if its the form or if its the code as the delete user isn't working

i have just started to learn this (a few days ago)so the code might be messy or not 100% secure as it should this is for a offline database so i would improve it as i learn.

<?php include("header.php");

//include database connection
include 'db_connect.php';

$action = isset( $_POST['action'] ) ? $_POST['action'] : "";
if($action == "update"){
//write query
$query = "update contacts 
set
name = '".$mysqli->real_escape_string($_POST['name'])."', 
surname = '".$mysqli->real_escape_string($_POST['surname'])."',
email = '".$mysqli->real_escape_string($_POST['email'])."',
pcode = '".$mysqli->real_escape_string($_POST['pcode'])."',
website = '".$mysqli->real_escape_string($_POST['website'])."',
gender  = '".$mysqli->real_escape_string($_POST['gender'])."'
mobile  = '".$mysqli->real_escape_string($_POST['mobile'])."'
phone  = '".$mysqli->real_escape_string($_POST['phone'])."'
county  = '".$mysqli->real_escape_string($_POST['county'])."'
town  = '".$mysqli->real_escape_string($_POST['town'])."'
address  = '".$mysqli->real_escape_string($_POST['address'])."'
  notes  = '".$mysqli->real_escape_string($_POST['notes'])."'
business  = '".$mysqli->real_escape_string($_POST['business'])."'
where id='".$mysqli->real_escape_string($_REQUEST['id'])."'";

if( $mysqli->query($query) ) {
echo "User was updated.";
}else{
echo "Database Error: Unable to update record.";
}
}
if($action=='delete'){ //if the user clicked ok, run our delete query

$query = "DELETE FROM users WHERE id = ".$mysqli->real_escape_string($_GET['id'])."";
if( $mysqli->query($query) ){
echo "User was deleted.";
}else{
echo "Database Error: Unable to delete record.";
}}

$query = "select id, name, pcode, website, email, surname, mobile, phone, business, gender, address, town, county, notes
from contacts
where id='".$mysqli->real_escape_string($_REQUEST['id'])."'
limit 0,1";

$result = $mysqli->query( $query );
$row = $result->fetch_assoc();

$id = $row['id'];
$name = $row['name'];
$surname = $row['surname'];
$pcode = $row['pcode'];
$email = $row['email'];
$business = $row['business'];
$phone = $row['phone'];
$mobile = $row['mobile'];
$gender = $row['gender'];
$address = $row['address'];
$county = $row['county'];
$notes = $row['notes'];
$town = $row['town'];
$website = $row['website']; ?>
<?php echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";

?>

<body>
<div class="div-middle-big">
<!--we have our html form here where new user information will be entered--> 

<a href='index.php'>Back to index</a>
</td>
</tr>
</table>
</form>
<div id="loader_cont"><img src="img/loaders/page_loader.gif"></div>
<?php include'topnav.php' ?>
<div class="container">
<div class="main_content row-fluid">
<div class="span3">
  <?php include'menu.php' ?>
  <!--/.well --> 
</div>
<!--/span-->
<div class="span9">
<div class="row-fluid">
<div class="span12">
<ul class="breadcrumb br_styled no_space">
  <li> <a href="index.html">Dashboard</a> <span class="divider">/</span> </li>
  <li class="active">Profile</li>
</ul>
<div class="widget profile_cont">
<header>
  <h3>Profile: <span class="profile_title"><?php echo$name; ?> <?php echo$surname; ?></span></h3>
  <ul class="toggle_content">
    <li class="arrow"><a href="#">Toggle Content</a></li>
  </ul>
</header>
<section class="group">
<div class="info"> <img src="http://api.thumbalizr.com/?url=http://<?php echo$website; ?>&width=250" alt="Profile picture">
  <h4>Profile Picture</h4>
  <div class="profile_picture">
    <input type="file" />
    <!-- <input type="submit" /> --> 
    <a href="http://<?php echo$website; ?>" class="btn">visit website</a> 
    <!-- <a href="#" class="btn">UPLOAD</a> --> 
  </div>
  <ul>
    <li><a href="#"><i class="sweet-user"></i> Profile</a></li>
    <li><a href="#"><i class="sweet-settings"></i> Settings</a></li>
    <li><a href="mailto:<?php echo$email; ?>"><i class="sweet-mail"></i> Email <?php echo$name; ?></a></li>
    <li><a href="widgets.html"><i class="sweet-cog-4"></i> Widgets</a></li>
    <li><a href="login.html"><i class="sweet-exit"></i> Logout</a></li>
  </ul>
  <div class="span3">
    <div class="widget">
      <header>
        <h3>Grid 3</h3>
        <ul class="toggle_content" style="display: none;">
          <li class="arrow"><a href="#">Toggle Content</a></li>
        </ul>
      </header>
      <section class="code_align"> <code>class="span3"</code> </section>
    </div>
  </div>
</div>
<div class="details">
<form action='#' method='post' border='0' class="well form-horizontal">
  <fieldset>
    <h4 class="group"> <span>Personal details</span> </h4>
    <div class="control-group">
      <div class="controls"> </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="name">First name</label>
      <div class="controls">
        <input id="name" type="text" name="name" value="<?php echo$name; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="surname">Last name</label>
      <div class="controls">
        <input id="surname" type="text" name="surname" value="<?php echo$surname; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="business">Company Name</label>
      <div class="controls">
        <input id="business" type="text" name="business" value="<?php echo$business; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="phone">Phone number</label>
      <div class="controls">
        <input id="phone" type="text" name="phone" value="<?php echo$phone; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="mobile">Mobile number</label>
      <div class="controls">
        <input id="mobile" type="text" name="mobile" value="<?php echo$mobile; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="gender">Sex</label>
      <div class="controls">
        <select class="gender" style="width:210px;" tabindex="2">
          <option value="<?php echo$gender; ?>"><?php echo$gender; ?></option>
          <option value="female">Female</option>
          <option value="male">Male</option>
        </select>
      </div>
    </div>
    <h4>Contact details</h4>
    <div class="control-group">
      <label class="control-label" for="email">E-mail</label>
      <div class="controls">
        <input id="email" type="text" name="email" value="<?php echo$email; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="website">Website</label>
      <div class="controls">
        <input id="website" type="text"  name="website" value="<?php echo$website; ?>" data-original-title="Without the http://">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="address">Address</label>
      <div class="controls">
        <textarea id="address" rows="3" name="address" ><?php echo$address; ?></textarea>
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="skypeid">Town</label>
      <div class="controls">
        <input id="town" type="text" name="town" value="<?php echo$town; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="county">County</label>
      <div class="controls">
        <input id="county" type="text"  name="county" value="<?php echo$county; ?>">
      </div>
    </div>
    <div class="control-group">
      <label class="control-label" for="pcode">Post code</label>
      <div class="controls">
        <input id="pcode" type="text"  name="pcode" value="<?php echo$pcode; ?>">
      </div>
    </div>
    <h4>Notes about <?php echo$name; ?> <?php echo$surname; ?></h4>
    <p>
      <textarea id="notes" rows="5" name="notes" ><?php echo$notes; ?></textarea>
    </p>
    <div class="form-actions"> 
      <!-- so that we could identify what record is to be updated -->
      <input type='hidden' name='id' value='<?php echo $id ?>' />

      <!-- we will set the action to edit -->
      <input type='hidden' name='action' value='update' />
      <input type='submit' value='Edit' />
    </div>
  </fieldset>
</form>

The problem with the above code is thats its not updating my database and i am getting

Database Error: Unable to update record

UPDATE

i have gone back to my old files and now this dosent work

ok i gone right back to the basic files i had....

<meta http-equiv="refresh" content="0; url=../contacts.php"> <?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = "DELETE FROM contacts
        WHERE created='$_GET[id]'";

mysql_select_db('pcrepairs');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>

i am now getting this error

Could not delete data: Unknown column 'created' in 'where clause'

Upvotes: 0

Answers (4)

kaizenCoder

Reputation: 2229

Forgetting PHP for a moment if you were to issue a SQL query, say in the command-line, you would need to use single quotes to signify the search string.

So it would like this:

DELETE FROM users WHERE id = '100';

The above has to remain true when you construct the query via PHP:

$query = "DELETE FROM users WHERE id='".$mysqli->real_escape_string($_GET['id'])."'";

If your code's failing, you really need to get into the mindset of debugging your code. Approach it in smaller chunks and work your way back up. So for instance, you can try executing the above query with a hard-coded id value in the console and confirm it works.

Upvotes: 1

Abhijith Babu

Reputation: 86

You seems to be using users table in your delete query.Does the users table exist?,if not please change it to contacts.Please let me know

Thanks

Upvotes: 2

yafrani

Reputation: 521

You forgot the commas in your SQL UPDATE statement:

$query = "update contacts 
set
name = '".$mysqli->real_escape_string($_POST['name'])."', 
surname = '".$mysqli->real_escape_string($_POST['surname'])."',
email = '".$mysqli->real_escape_string($_POST['email'])."',
pcode = '".$mysqli->real_escape_string($_POST['pcode'])."',
website = '".$mysqli->real_escape_string($_POST['website'])."',
gender  = '".$mysqli->real_escape_string($_POST['gender'])."',
mobile  = '".$mysqli->real_escape_string($_POST['mobile'])."',
phone  = '".$mysqli->real_escape_string($_POST['phone'])."',
county  = '".$mysqli->real_escape_string($_POST['county'])."',
town  = '".$mysqli->real_escape_string($_POST['town'])."',
address  = '".$mysqli->real_escape_string($_POST['address'])."',
  notes  = '".$mysqli->real_escape_string($_POST['notes'])."',
business  = '".$mysqli->real_escape_string($_POST['business'])."'
where id='".$mysqli->real_escape_string($_REQUEST['id'])."'";

You also need to review your HTML code.

EDIT

The SQL syntax for an update statement is:

UPDATE my_table_name SET col1='value1', col2='value2', ... WHERE conditions

And this should work for the delete query:

$query = "DELETE FROM users WHERE id='".$mysqli->real_escape_string($_GET['id'])."'";

If you are using PHP5+ I recommend you to use PDO instead of the old sqlite functions. You also need to verify your data before saving into the DB.

Upvotes: 1

John Lee

Reputation: 1387

Can you try echo'ing the $query value before running it through mysqli? Get that sql statement and try manually running it through the database. You may also want to double check your data types. You can get an error if you try, for example, setting an NUMBER/INT field with a string value.

Upvotes: 1

editing a record in a database with a form

Answers (4)

Related Questions