SQL UPDATE syntax if php variable

I have a simple 1 record, 1 field, MySQL database whose record I am trying to change using a php script but the UPDATE script does not update the record but instead merely redirects to the index page (which I want to happen after the UPDATE occurs).

Attached is my script. Might someone give me assistance in troubleshooting my script so that the “notes” field updates? I had assumed that changing $_POST[notes] to $_POST[‘notes’] would work but that failed.

Thanks in advance.

<?php
//Connect To Database

$hostname="hostresource";
$username="john";
$password="thepassword";
$dbname="thedbname";
$usertable="tbl_list";
$yourfield = "notes";

mysql_connect($hostname,$username, $password) or die ("<html><script language='JavaScript'>alert('Unable to connect to database! Please try again later.'),history.go(-1)</script></html>");
mysql_select_db($dbname);

$query = "UPDATE $usertable SET $yourfield = $_POST[notes]";

header("Location: http://www.website.com/index.php");

exit
?>

Open in new window

lepirtleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shobinsunCommented:
Hi try using:

$query = "UPDATE $usertable SET $yourfield = mysql_real_escape_string($_POST['notes'])";
0
shobinsunCommented:
Or:

$query = "UPDATE $usertable SET $yourfield ="'. $_POST['notes'] .'";
0
AielloJCommented:
lepirtle:

You created a variable named $query that holds the query string, however, you never executed the msSQL command to make it happen.  Try this:

$query = "UPDATE $usertable SET $yourfield = $_POST[notes]";
mysql_query($query);

Regards,

AielloJ
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

lepirtleAuthor Commented:
shobinsun and AielloJ,
I tried both shobinsun solutions and they produced syntax errors in Dreamweaver. I tried AielloJ's solution and thought there were no errors, neither did the update occur.
Thanks for you continuing help.
0
shobinsunCommented:
Hi,

try this:

$query = "UPDATE $usertable SET $yourfield ='". $_POST['notes'] ."'";
0
shobinsunCommented:
or:

$query = "UPDATE $usertable SET $yourfield = ".mysql_real_escape_string($_POST['notes']);
0
shobinsunCommented:
Then use AielloJ's solution:
mysql_query($query);

Hope this will work for you definitely.

Regards.
0
Ray PaseurCommented:
Line 14 assigns a variable but does not run a query.
$query = "UPDATE $usertable SET $yourfield = $_POST[notes]";

Here is a teaching example showing how to do some of the basics in PHP and MySQL.

I think I would write this part more or less like this...  Be aware that UPDATE without a WHERE clause will update every row of the table.  You might or might not want to do that.

if (!empty($_POST["notes"]))
{
    $safe_notes = mysql_real_escape_string($_POST["notes"]);
    $query = "UPDATE $usertable SET $yourfield = '$safe_notes' ";
    $result = mysql_query($query) or die( mysql_error() );
}
else
{
    echo "POST ARRAY HAS NO 'notes' ";
}

<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
lepirtleAuthor Commented:
shobinsun and AielloJ,
Thank you for your solutions. Sorry not to reply earlier but a 1.5 hour dental appointment slowed me. I hope that my splitting points between the two of you is fair since you both provided pieces of the solution.

Ray_Paseur, Thank you for your continuing help in my php education. I will use your example in my further efforts. And I was aware of the WHERE issue but since my table  contains only 1 record and field I thought I'd be OK.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.