Help with PHP / mysql database UPDATE SCRIPT

eric11 · September 21, 2011

Hi,

Im just in the middle of creating an update script for my mysql database but don't know why it's not working. p.s. I'm a little new to PHP, but know quite a bit, it's probably something really small.. *facepalm*

Here's the script:

the form (update.php)

<?

// Connect to the database

$link = mysql_connect('###', '###', '###');

if (!$link) {

die('Could not connect: ' . mysql_error());

}

mysql_select_db('###', $link);

$id = $_GET['id'];

// Ask the database for the information from the links table

$query="SELECT * FROM orders WHERE id='$id'";

$result = mysql_query("SELECT * FROM orders");

$num=mysql_numrows($result);

mysql_close();

$i=0;

while ($i < $num) {

$name=mysql_result($result,$i,"Name");

$location=mysql_result($result,$i,"Location");

$fault=mysql_result($result,$i,"Fault");

?>

Name: <input type="text" name="ud_name" value="<? echo "$name"?>"><br>

Location: <input type="text" name="ud_location" value="<? echo "$location"?>"><br>

Fault: <input type="text" name="ud_fault" value="<? echo "$fault"?>"><br>

</form>

<?

++$i;

}

?>

------------------------------------------------------

(processor) updated.php

<?php

// Connect to the database

$link = mysql_connect('###', '###', '###');

if (!$link) {

die('Could not connect: ' . mysql_error());

}

mysql_select_db('###', $link);

$query="UPDATE orders SET Name='" . $_POST['ud_name'] . "', Location='" . $_POST['ud_location'] . "', Fault='" . $_POST['ud_fault'] . "' WHERE $id='" . $_POST['ud_id'] . "'";

echo $query;

$checkresult = mysql_query($query);

if ($checkresult) echo '<p>update query succeeded';

else echo '<p>update query failed';

mysql_close();

?>

------------------------------------------------------

Every time I want to update, it comes up with:

UPDATE orders SET Name='TEST', Location='TEST', fault='jbjh' WHERE =''

update query failed

Any help would be appreciated.

Psycho · September 21, 2011

First off use [ code ] tags when posting code.

As to your problem, you should always echo out your database errors (in a development environment).

if ($checkresult)
{
    echo '<p>update query succeeded';
}
else
{
    echo '<p>Update query failed<br>Query: {$query}<br>Error: ' . mysql_error();
}

However, I would think you see the error based upon the query generated. The WHERE clause if f'd up.

UPDATE orders SET Name='TEST', Location='TEST', fault='jbjh' WHERE =''

Here is where you create the query:

$query="UPDATE orders SET Name='" . $_POST['ud_name'] . "', Location='" . $_POST['ud_location'] . "', Fault='" . $_POST['ud_fault'] . "' WHERE $id='" . $_POST['ud_id'] . "'";

There seem to be two problems:

1. I *think* you meant to use "id" as the field name to use in the where clause and not the variable "$id" - which apparently isn't defined in that script.

2. The second problem seems to be that $_POST['ud_id'] also has no value. Well, that field should be passed from the form in a hidden field - which is populated from a $_GET variable. So, you need to validate that the value is getting passed correctly through the pages.

For debugging purposes, I would change the hidden field to a text field. That will allow you to see if the field is getting populated with the value you expect. If not, then you have to look backwards in the logic to find the problem. If the value is getting populated in that form field, then you have to validate that it is getting passed to the processor page. So, on that page do a print_r($_POST) to ensure the form data is getting passed and is what you expect.

Just looking at what you have, I would think it should work. So, I am guessing that on the form page the value $_GET['id']; does not exist or is empty.

By the way, if you are going to use double quotes to define your query, I find it easier to read to put the variables inside the quoted string:

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}' WHERE id='{$_POST['ud_id']}'";

Psycho · September 21, 2011

On second look, I see many more problems.

In your form page you have these two lines

$query="SELECT * FROM orders WHERE id='$id'";
$result = mysql_query("SELECT * FROM orders");

You define a query to pull orders based upon an ID, but you don't use it. Instead you run a query to pull ALL orders. That leads me to believe that you should be pulling the order ID from each record to populate the hidden field and not using the variable you define at the top of the script.

Also, you use mysql_close() before you access the data from your query. I wouldn't think that would work. I almost never use mysql_close() since PHP is good at releasing those resources at the end of script execution. But, I would at least put that after I have done all the database functions. Also, I think your while() loop in the first script is a little complicated by using multiple mysql_result() functions. It's easier just to use one of the fetch functions. Lastly, you are not escaping the input from the user, which leaves you open to SQL injection attacks.

Give this a try

// Ask the database for the information from the links table
$query = "SELECT `id`, `Name`, `Location`, `Fault` FROM orders";
$result = mysql_query($query);
if(!$result)
{
    echo "Query failed<br>Query: {$query}<br>Error: " . mysql_error();
}
else
{
    //Create an update form for each order
    while($order = mysql_fetch_assoc($result))
    {
        echo "<form action='updated.php' method='post'>\n";
        echo "<input type='hidden' name='ud_id' value='{$order['id']}'>\n";
        echo "Name: <input type='text' name='ud_name' value='{$order['Name']}'><br>\n";
        echo "Location: <input type='text' name='ud_location' value='{$order['Location']}'><br>\n";
        echo "Fault: <input type='text' name='ud_fault' value='{$order['Fault']}'><br>\n";
        echo "<input type='Submit' value='Update'>\n";
        echo "</form>\n";
    }
}

eric11 · September 21, 2011

thanks for the reply!

I've followed your instructions and tested it out (thanks for making the code simpler btw)

when attempting to update the fields on the 'updated.php' page, it echoes

"UPDATE orders SET Name='Name', Location='ee', Fault='Fault' WHERE id='id'

Update query failed

Query: {$query}

Error: Unknown column 'id' in 'where clause'Array ( [ud_id] => id [ud_name] => Name [ud_location] => ee [ud_fault] => Fault )"

So I'm thinking that it has to do with the WHERE id

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}' WHERE id='{$_POST['ud_id']}'";

that is there. Been playing around with it for a while now... not sure what I can put in there..

but this is what I have atm:

-------------------------------------

update.php page

$id = $_GET['id'];

// Ask the database for the information from the orders table
$query = "SELECT 'id', 'Name', 'Location', 'Fault' FROM orders";
$result = mysql_query($query);
if(!$result)
{
    echo "Query failed<br>Query: {$query}<br>Error: " . mysql_error();
}
else
{
    //Create an update form for each order
    while($order = mysql_fetch_assoc($result))
    {
        echo "<form action='updated.php' method='post'>\n";
        echo "<input type='hidden' name='ud_id' value='{$order['id']}'>\n";
        echo "Name: <input type='text' name='ud_name' value='{$order['Name']}'><br>\n";
        echo "Location: <input type='text' name='ud_location' value='{$order['Location']}'><br>\n";
        echo "Fault: <input type='text' name='ud_fault' value='{$order['Fault']}'><br>\n";
        echo "<input type='Submit' value='Update'>\n";
        echo "</form>\n";
    }
}
?>

-------------------------------------

updated.php

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}' WHERE id='{$_POST['ud_id']}'"; 
echo $query;
$checkresult = mysql_query($query); 
if ($checkresult)
{
    echo '<p>update query succeeded';
}
else
{
    echo '<p>Update query failed<br>Query: {$query}<br>Error: ' . mysql_error();
}
print_r($_POST)

?>

-------------------------------------

thank you so much for your help

Psycho · September 22, 2011

Well, I know I didn't test the code, so you may have needed to make some changes. But, you changes some things that shouldn't have been changed.

You changed this

$query = "SELECT `id`, `Name`, `Location`, `Fault` FROM orders";

To this

$query = "SELECT 'id', 'Name', 'Location', 'Fault' FROM orders";

The backquotes ` where changed to normal single quotes '. The backquoates are a way to distinguish field names in your query. They are absolutely necessary unless you have fields that use reserved words, but it's not a bad habit to use them anyways. By using the strait single quotes you are telling the MySQL engine to select the literal strings. So, instead of getting the values for the fields `id`, `Name`, `Location`, and `Fault`, you are instead getting the actual text strings of 'id', 'Name', 'Location', and 'Fault'. I'm surprised you didn't see this on the page that displayes the records.

That is why you query is failing on the other page. You are passing the text "id" instead of the id value from the database.

Also, you used this line:

echo '<p>Update query failed<br>Query: {$query}<br>Error: ' . mysql_error();

Which generated the error

Update query failed
Query: {$query}

Error: Unknown column 'id' in 'where clause'Array ( [ud_id] => id [ud_name] => Name [ud_location] => ee [ud_fault] => Fault )"

You are seeing the literal string of "{$query}" instead of the value associated with the variable $query. That is because you defined that string using single quotes. When using double quotes to define a string, any variables will be interpreted. In other words, you would have seen the actual query in the error message if you had used double quotes.

eric11 · September 22, 2011

Thanks for the reply and explanation,

I think i got what you said about the first bit with the ``

however, when you said

You are passing the text "id" instead of the id value from the database.

i got a little lost with how I could implement that into my code. Sorry, I'm pretty horrible with the syntax of PHP.

but i think it has to do with these 2 sets of code:

update.php

$query="SELECT * FROM orders WHERE id='$id'";

updated.php

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}' WHERE `id`='{$_POST[`ud_id`]}'";

I just cant get it through my head :'(

Thanks for helping mate, I should pay you

Muddy_Funster · September 22, 2011

this line is still a problem:

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}' WHERE `id`='{$_POST[`ud_id`]}'";

You have used backticks in the PHP POST call (highlighted blue) - these need to be changed to single quotes - and still have backticks around id even though you have taken them off the other field names (highlighted red). Please also remember that your field names in MySQL, unlike ACCESS or SQL SERVER are case sensitive, so id is not the same as ID, is not the same as Id and so on.

eric11 · September 22, 2011

thanks for that, you helped get rid of the errors it now says:

UPDATE orders SET Name='TEST', Location='TEST', Fault='TEST', Completed='1' WHERE 'id'=''
update query succeededArray ( [ud_id] => [ud_name] => TEST [ud_location] => TEST [ud_fault] => TEST [ud_completed] => 1 )

but.. seems like it's not updating on the database itself..

anything else I could try?

Muddy_Funster · September 22, 2011

now you have the id field refference in quotes (highlighted below in red), remove these entierly as well as the fact that $_POST['ud_id'] does not apear to contain any data.

WHERE 'id'=''

if you still can't get it to work, insert the following in the line directly above the mysql_query($query) in your code and then copy and paste the output for us to see as well as a copy and paste of your php for assigning $query= as it stands now.

print_r($query);
exit;

eric11 · September 22, 2011

Thanks

We're getting close! When i deleted the

WHERE 'id'=''

it updated the database. for example, changed 'Completed=0' to 'Completed=1'

the result was that it changed the database value. but it changed ALL of the rows instead of the specific row I wanted to change.

Here is what I have at the moment:

update.php

$id = $_GET['id'];

// Ask the database for the information from the orders table
$query="SELECT * FROM orders WHERE id='$id'";
$result = mysql_query("SELECT * FROM orders"); 
if(!$result)
{
    echo "Query failed<br>Query: {$query}<br>Error: " . mysql_error();
}
else
{
    //Create an update form for each order
    while($order = mysql_fetch_assoc($result))
    {
        echo "<form action='updated.php' method='post'>\n";
        echo "<input type='hidden' name='ud_id' value='{$order['id']}'>\n";
        echo "Name: <input type='text' name='ud_name' value='{$order['Name']}'><br>\n";
        echo "Location: <input type='text' name='ud_location' value='{$order['Location']}'><br>\n";
        echo "Fault: <input type='text' name='ud_fault' value='{$order['Fault']}'><br>\n";
	echo "Completed: <input type='text' name='ud_completed' value='{$order['Completed']}'><br>\n";
        echo "<input type='Submit' value='Update'>\n";
        echo "</form>\n";
    }
}
?>

updated.php

<?php 
// Connect to the database 
$link = mysql_connect('ericlee.dot5hostingmysql.com', 'eric', '11111'); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db('fixitdb', $link);

[color=red]$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}', Completed='{$_POST['ud_completed']}'"; [/color]
echo $query;
$checkresult = mysql_query($query); 
if ($checkresult)
{
    echo '<p>update query succeeded';
}
else
{
    echo '<p>Update query failed<br>Query: {$query}<br>Error: ' . mysql_error();
}
print_r($_POST)

?>

Thankyou!

Muddy_Funster · September 22, 2011

ok, so I assume that your print_r($POST) proves that there is a value in $_POST['ud_id']. I musn't have maid myself clear in the last post, I just want you to remove any use of backticks/quotes entierly, not remove the WHERE entierly. Try adding this into the end of the query string, where you had your where before:

WHERE id = {$_POST['ud_id']}

nothing else, just copy and paste that into the query string and let me know what you get back.

eric11 · September 22, 2011

i inserted the WHERE like you asked me to.

it came out with

UPDATE orders SET Name='TEST', Location='TEST', Fault='TEST', Completed='1', WHERE id =
Update query failed

Query: {$query}

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id =' at line 1Array ( [ud_id] => [ud_name] => TEST [ud_location] => TEST [ud_fault] => TEST [ud_completed] => 1 )

this is right, right?

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}', Completed='{$_POST['ud_completed']}', WHERE id = {$_POST['ud_id']}";

Muddy_Funster · September 22, 2011

yip, that's spot on, the problem is that you have no value in $_POST['ud_id']

I think we need to go back in the process to see what is coming out of this piece of code:

$id = $_GET['id'];

// Ask the database for the information from the orders table
$query="SELECT * FROM orders WHERE id='$id'";
$result = mysql_query("SELECT * FROM orders"); 
if(!$result)
{
    echo "Query failed<br>Query: {$query}<br>Error: " . mysql_error();
}else
{
    //Create an update form for each order
    while($order = mysql_fetch_assoc($result))
    {
        echo "<form action='updated.php' method='post'>\n";
        echo "<input type='hidden' name='ud_id' value='{$order['id']}'>\n";   //---------------------<<<<<<<change this line
        echo "Name: <input type='text' name='ud_name' value='{$order['Name']}'><br>\n";
        echo "Location: <input type='text' name='ud_location' value='{$order['Location']}'><br>\n";
        echo "Fault: <input type='text' name='ud_fault' value='{$order['Fault']}'><br>\n";
	echo "Completed: <input type='text' name='ud_completed' value='{$order['Completed']}'><br>\n";
        echo "<input type='Submit' value='Update'>\n";
        echo "</form>\n";
    }
}

change the maked line to the following:

echo "<input type='text' name='ud_id' value='{$order['id']}'>\n";

eric11 · September 22, 2011

yeah, coming up with the same error.. :S

would a count(); be useful for this? not sure..

tqtq

Buddski · September 22, 2011

Two questions.

On update.php are you actually specifying the 'id' parameter in the URL string?

Secondly, can you post your `orders` table structure, MySQL seems to think that the `id` column doesn't exist.

Unknown column 'id' in 'where clause'

eric11 · September 22, 2011

thanks Buddski for having a look

1. i dont think i am.. but.. not quite sure what to do..

2.

CREATE TABLE `orders` (
`Name` varchar(30) NOT NULL,

`Location` varchar(30) NOT NULL,

`Fault` varchar(30) NOT NULL,

`Description` longtext NOT NULL,

`Time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`Date` date NOT NULL,

`Completed` tinyint(1) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

any help would be appreciated.. Sorry im a little new to the syntax of PHP.

eric11 · September 22, 2011

this is what i have at the moment:

update.php

$id = $_GET['id'];

// Ask the database for the information from the orders table
$query="SELECT * FROM orders WHERE id='$id'";
$result = mysql_query("SELECT * FROM orders"); 
if(!$result)
{
    echo "Query failed<br>Query: {$query}<br>Error: " . mysql_error();
}
else
{
    //Create an update form for each order
    while($order = mysql_fetch_assoc($result))
    {
        echo "<form action='updated.php' method='post'>\n";
        echo "<input type='text' name='ud_id' value='{$order['id']}'>\n";
        echo "Name: <input type='text' name='ud_name' value='{$order['Name']}'><br>\n";
        echo "Location: <input type='text' name='ud_location' value='{$order['Location']}'><br>\n";
        echo "Fault: <input type='text' name='ud_fault' value='{$order['Fault']}'><br>\n";
	echo "Completed: <input type='text' name='ud_completed' value='{$order['Completed']}'><br>\n";
        echo "<input type='Submit' value='Update'>\n";
        echo "</form>\n";
    }
}
?>

updated.php

$query="UPDATE orders SET Name='{$_POST['ud_name']}', Location='{$_POST['ud_location']}', Fault='{$_POST['ud_fault']}', Completed='{$_POST['ud_completed']}', WHERE id = {$_POST['ud_id']}"; 
echo $query;
$checkresult = mysql_query($query); 
if ($checkresult)
{
    echo '<p>update query succeeded';
}
else
{
    echo '<p>Update query failed<br>Query: {$query}<br>Error: ' . mysql_error();
}
print_r($_POST)

?>

Buddski · September 22, 2011

Your database does not have an 'id' column therefor MySQL will always fail with your current query.

You will need to add an `id` column to your database (this should be, I think, an auto-increment integer value)

This should work.

CREATE TABLE `orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) NOT NULL,
  `Location` varchar(30) NOT NULL,
  `Fault` varchar(30) NOT NULL,
  `Description` longtext NOT NULL,
  `Time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Date` date NOT NULL,
  `Completed` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This will let your queries work, the next thing you need to do is actually pass an ID value into your update.php page like so..

http://yoursite.com/update.php?id=X

where X is the ID of the database row you want to fetch.

Another thing, you are ignoring your first defined $query variable

// Ask the database for the information from the orders table
$query="SELECT * FROM orders WHERE id='$id'";
$result = mysql_query("SELECT * FROM orders");

should be

// Ask the database for the information from the orders table
$query="SELECT * FROM orders WHERE id='$id'";
$result = mysql_query($query);

eric11 · September 22, 2011

I've replaced the code and fixed up the table. but now the update page is blank.

I tried putting back the code I had before, but came up with the same error msg

UPDATE orders SET Name='TEST', Location='TEST', Fault='TEST', Completed='1', WHERE id = 1
Update query failed

Query: {$query}

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1Array ( [ud_id] => 1 [ud_name] => TEST [ud_location] => TEST [ud_fault] => TEST [ud_completed] => 1 )

Your explanation makes sense.. in that i should be using $query because i defined it already.. but doesn't seem to work out..

Buddski · September 22, 2011

You have a , after Completed = '1'

Get rid of it and see what the code outputs.

eric11 · September 22, 2011

you legend!

all fixed i love you!

thanks for all the support guys. i really really appreciate it.

Sign In

Help with PHP / mysql database UPDATE SCRIPT

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information