Reputation: 511
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
Views: 4544
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
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
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
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