Mysql update? help required.

[Paul Eden]

Hello all.
I'm totally stumped. Please de-stump me!

I've read my data in from the database, changed it according to what i
want to do and now I want to write it back.

But it seems I can only use the update statement will only accept
explicit fieldnames and value. The fieldnames I can live with, I only
have 5 columns in the database, but why can't I pass a $variable to
this statement?

I realise its a MySQL statement and not PHP. Either I'm missing
something pretty fundamental or this whole PHP/MySQL thing is useless.
What is the point if you cannot write back $variables?

I'm just a simple soul. Answers in words of less than 2 syllables
please. :)

[Andy Hassall]

On 27 Mar 2005 05:21:00 -0800, pa**@moontech-racing.com (Paul Eden) wrote:

I've read my data in from the database, changed it according to what i
want to do and now I want to write it back.

But it seems I can only use the update statement will only accept
explicit fieldnames and value. The fieldnames I can live with, I only
have 5 columns in the database, but why can't I pass a $variable to
this statement?

I realise its a MySQL statement and not PHP. Either I'm missing
something pretty fundamental or this whole PHP/MySQL thing is useless.
What is the point if you cannot write back $variables?

You can. What have you tried?

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Paul Eden]

Andy Hassall <an**@andyh.co.uk> wrote in message news:<0a********************************@4ax.com>. ..

On 27 Mar 2005 05:21:00 -0800, pa**@moontech-racing.com (Paul Eden) wrote:

I've read my data in from the database, changed it according to what i
want to do and now I want to write it back.

But it seems I can only use the update statement will only accept
explicit fieldnames and value. The fieldnames I can live with, I only
have 5 columns in the database, but why can't I pass a $variable to
this statement?

I realise its a MySQL statement and not PHP. Either I'm missing
something pretty fundamental or this whole PHP/MySQL thing is useless.
What is the point if you cannot write back $variables?

You can. What have you tried?

I asked Google and it came up with a page (which I now can't find)
that gave a function to do it. It claimed to be nicely commented and
documented by frankly I couldn't follow it and in any case, my PHP
knowledge doesn't extend to functions just yet.
Obviousy, I wrote
update table set columnname = $variable[$arraypointer];
and it threw it out.
I've had an idea though; please feel free to shoot it down;
The data I want to write back to the table is numerical and only ever
increases. Could I;
$counter = $newvalue-$currentvalue;
for ($counter=0; $counter<$num_rows; $counter++)
{
update table set columnname=columnname+1
}
in other words, as the value i want to write back only ever increases,
I just work out the difference between new and current, and then using
a loop, increment the value that many times?

[Andy Hassall]

On 27 Mar 2005 10:55:04 -0800, pa**@moontech-racing.com (Paul Eden) wrote:

Andy Hassall <an**@andyh.co.uk> wrote in message news:<0a********************************@4ax.com>. ..

On 27 Mar 2005 05:21:00 -0800, pa**@moontech-racing.com (Paul Eden) wrote:

I realise its a MySQL statement and not PHP. Either I'm missing
something pretty fundamental or this whole PHP/MySQL thing is useless.
What is the point if you cannot write back $variables?
You can. What have you tried?

I asked Google and it came up with a page (which I now can't find)
that gave a function to do it. It claimed to be nicely commented and
documented by frankly I couldn't follow it and in any case, my PHP
knowledge doesn't extend to functions just yet.

Well, you'll have to call a function. mysql_query(), at least.

http://uk.php.net/mysql_query

The manual link above has several examples.
Obviousy, I wrote
update table set columnname = $variable[$arraypointer];
and it threw it out.
With what error? Always post the errors you receive, along with the smallest
code you can reduce down to demonstrating your problem.
I've had an idea though; please feel free to shoot it down;
The data I want to write back to the table is numerical and only ever
increases. Could I;
$counter = $newvalue-$currentvalue;
for ($counter=0; $counter<$num_rows; $counter++)
{
update table set columnname=columnname+1
You can't just put SQL in PHP and expect it to work, you have to call
mysql_query(). Also, this has to be the most insane way ever to set a value in
a database :-)
}
in other words, as the value i want to write back only ever increases,
I just work out the difference between new and current, and then using
a loop, increment the value that many times?

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[ZeldorBlat]

$query = "update table set columnname = " . $variable[$arraypointer];
mysql_query($query);

You have to write your SQL in a string variable, then pass that to
mysql_query(). Of course, you'll need to open a database connection as
well (if you haven't already). You've probably figured that out since
you managed to get data out of the database. In that case, use:

mysql_query($query, $dbconn);

where $dbconn is your link identifier returned by mysql_connect().

[sketch]

On 27 Mar 2005 10:55:04 -0800, in comp.lang.php
pa**@moontech-racing.com (Paul Eden) wrote:

I asked Google and it came up with a page (which I now can't find)
that gave a function to do it. It claimed to be nicely commented and
documented by frankly I couldn't follow it and in any case, my PHP
knowledge doesn't extend to functions just yet.
Obviousy, I wrote
update table set columnname = $variable[$arraypointer];
and it threw it out.
I've had an idea though; please feel free to shoot it down;
The data I want to write back to the table is numerical and only ever
increases. Could I;
$counter = $newvalue-$currentvalue;
for ($counter=0; $counter<$num_rows; $counter++)
{
update table set columnname=columnname+1
}
in other words, as the value i want to write back only ever increases,
I just work out the difference between new and current, and then using
a loop, increment the value that many times?

Before you increment the value, you have to query the database to see
what it was previously:

$query = "SELECT value FROM tablename WHERE column = '$old_value'";
$result = mysql_query($query);
$old_value = mysql_result($result, 0, 'column');

Now that you know the old value, you can increment it by one without
any loop:

$new_value = $old_value + 1;

Now you can do your update whith the new value:

$query = "UPDATE tablename SET column = '$new_value' WHERE column =
'$old_value'";
$result = mysql_query($query);
if ($result) {
echo 'Success!';
}
else {
echo '<a href="www.php.net">I better read this carefully</a>';
}

Good Luck!

--sketch

[Paul Eden]

"ZeldorBlat" <ze********@gmail.com> wrote in message news:<11**********************@o13g2000cwo.googleg roups.com>...

$query = "update table set columnname = " . $variable[$arraypointer];
mysql_query($query);

You have to write your SQL in a string variable, then pass that to
mysql_query(). Of course, you'll need to open a database connection as
well (if you haven't already). You've probably figured that out since
you managed to get data out of the database. In that case, use:

mysql_query($query, $dbconn);

where $dbconn is your link identifier returned by mysql_connect().

Thanks for all your help. I did understand much of it. :)

I decided to use flat data files in the end as I could work out how to
use them on my own and the complexity of the task didn't really
warrant using a database.

Once again. Many thanks.