Mysql update function

Question

I have created a form that submits to the mysql database. Now what I am trying to do is get it to update. The bit I'm having trouble with is the update query below, I just can not figure out where I am going wrong.

 <?php
/*
Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password)
*/
include 'db.php';

// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Escape user inputs for security
$title = mysqli_real_escape_string($link, $_POST['title']);
$price = mysqli_real_escape_string($link, $_POST['price']);
$sqm = mysqli_real_escape_string($link, $_POST['sqm']);
$sqm_land = mysqli_real_escape_string($link, $_POST['sqm_land']);
$type = mysqli_real_escape_string($link, $_POST['type']);
$area = mysqli_real_escape_string($link, $_POST['area']);
$location = mysqli_real_escape_string($link, $_POST['location']);
$bedroom = mysqli_real_escape_string($link, $_POST['bedroom']);
$terrace = mysqli_real_escape_string($link, $_POST['terrace']);
$orientation = mysqli_real_escape_string($link, $_POST['orientation']);
$water = mysqli_real_escape_string($link, $_POST['water']);
$seaview = mysqli_real_escape_string($link, $_POST['seaview']);
$pool = mysqli_real_escape_string($link, $_POST['pool']);
$ownerinfo = mysqli_real_escape_string($link, $_POST['ownerinfo']);
$gaddress = mysqli_real_escape_string($link, $_POST['gaddress']);
$description = mysqli_real_escape_string($link, $_POST['description']);




// attempt insert query execution
$sql = "update INTO property (title, price, sqm, sqm_land, type, area, location, bedroom, terrace, orientation, water, seaview, pool, ownerinfo, gaddress, description) VALUES 
('$title', '$price', '$sqm', '$sqm_land', '$type', '$area', '$location', '$bedroom', '$terrace', '$orientation', '$water', '$seaview', '$pool', '$ownerinfo', '$gaddress', '$description' )";
if(mysqli_query($link, $sql)){
    echo "Records updated successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// close connection
mysqli_close($link);

?>

You're already checking for errors with MySQL, but not with PHP http://php.net/manual/en/function.error-reporting.php and Lord knows what's in here `db.php`. — Funk Forty Niner, Mar 22 '16 at 17:20
Possible duplicate of [Update mysql column](http://stackoverflow.com/questions/6503824/update-mysql-column) — Machavity, Mar 22 '16 at 17:25
@Machavity Had I known beforehand, I'd of closed it in one go. I guess in trying to teach someone "how to fish" wasn't enough. I had to literally pull the fish out of the river myself. People are so in a hurry to eat before the dinner bell even rings. — Funk Forty Niner, Mar 22 '16 at 17:45
@Fred-ii- It's all good. And here's your [dinner bell](https://www.youtube.com/watch?v=x8PWHOVYhI8) ;) — Machavity, Mar 22 '16 at 18:15

Funk Forty Niner · Answer 1 · 2016-03-22T17:40:26.913

You're using the wrong syntax for UPDATE.

Read the manual:

http://dev.mysql.com/doc/en/update.html

What you're using is INSERT syntax. http://dev.mysql.com/doc/en/insert.html

Example from the manual:

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

and use a WHERE clause, otherwise you will be updating your entire db.

Example from the manual:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

So in your case and for example (fill in the rest):

UPDATE property SET title = '$title', price = '$price' ... WHERE column = ?

column being the column name you want to target and the ? being the row.

Your mysqli_error($link) would have thrown you something about it.

Sidenote: "Teach a person how to fish, rather than throwing them a fish".

However, if the goal here is to INSERT, then you need to use INSERT INTO table and not UPDATE INTO table.

Also make sure your form uses a POST method and that all POST arrays contain values.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

Footnotes:

The MySQL API used to connect with in db.php is unknown. Make sure you are using the same API you are using to query with, being mysqli_. Different APIs do not intermix.

Hello, thanks sorry I totally missed your post. How can I put a Column / row down when it will be different each time? — stephenaxe18, Mar 22 '16 at 18:06

score 0 · Answer 2 · answered Mar 22 '16 at 17:29

0

Your syntax is incorrect, it should be formatted like this:

$sql = "UPDATE property SET title='$title'";

You'll have to add all the name/value pairs separated by commas since I only included 'title.'

answered Mar 22 '16 at 17:29

robbymarston

344
3
16

I have just altered that. Worked great thanks apart from its updated all the other pages to the same. Any ideas on how to only update that one page? – stephenaxe18 Mar 22 '16 at 17:43
@stephenaxe18 *"Any ideas on how to only update that one page?"* - I gave you answer already. You waiting for the other guy to grab what I have in mine to answer you? – Funk Forty Niner Mar 22 '16 at 18:01

Mysql update function

2 Answers2