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?
 
AielloJConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
shobinsunConnect With a Mentor Commented:
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
 
shobinsunConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.