How do I...increment a existing record MySQL INT field

[s_shrum]

I have a table (links) in which I track link-thrus (when I person clicks on a link from my site to another site).

How do you increment a INT field via SQL to a MySQL db? Possible? Internal function, perhaps? Statement snippet appreciahated.

Ultimately, this will be used to increment the hits field in the links table and then after that is done, will redirect the user to the site URL.

I'm kinda hoping that I can do this directly in SQL but it won't kill me if I have to code it.

TIA

Sean Shrum

[xblue]

I wouldn't really call it a function, but is
UPDATE table SET fieldname=fieldname+1 WHERE ...
what you are looking for?

[s_shrum]

That looks good...but:

increment.php?sql=UPDATE%20links%20SET%20hits=hits+1%20WHERE%20title=Apache

mysql_error() is reporting the following message:

Query was empty

on @()

Any ideas?

Sean Shrum

[tomhath]

Missing quotes?

 sql="UPDATE links SET hits=hits+ 1 WHERE title='Apache' ";

[s_shrum]

increment.php?sql=UPDATE%20links%20SET%20hits=hits+1%20WHERE%20title='Apache'

...doesn't work either.

[xblue]

do you absolutely have to pass the query via url?

• would be converted to a space automatically, therefore you'd need to encode it before sending.

[s_shrum]

I would like it better if I can pass the entire SQL statement via the query string but it's not set in stone.

Your right about the '+'...Is there a way to escape that character in the URL?

Sean Shrum

[xblue]

check www.php.net/urlencode or www.php.net/rawurlencode, I think this might work.

[s_shrum]

Bear in mind that the URL is being constructed OUTSIDE of a php script (via a template and keyword replacements).

I figured out that %2b is the URL encoding for '+' which I then urldecode() in my php script.

Still no luck...'Query was empty'.

I've even gone as far as to use the recommended back-ticks on field names:

increment.php?sql=UPDATE%20links%20SET%20hits=hits%2b1%20WHERE%20title='Apache';

Keep it coming. TIA

Sean Shrum

[xblue]

maybe the problem is elsewhere? could you post the code where you execute the query, maybe along with some dummy data that holds the query as it looks when you receive it?

[s_shrum]

Let me rewrite the script a bit.

In it's present form, it allows for full SQL access...that includes purging :-p

I need to 'specialize' the script down as to avoid malicious calls.

Once I get the rewrite done (shouldn't take long), I'll post the code and a link to the script itself that you can pass queries thru to test it out.

Sean Shrum

[jackohara]

Instead of using
hits+1
try using something like
hits$^%$%1
then running preg_replace() replacing $^%$% with + before you use @();

like...

URL: increment.php?sql=UPDATE links SET hits= 'hits$^%$%1' WHERE title= 'Apache'

then, inside increment.php

preg_replace('$^{%$%','+',$sql);}

THEN

@($sql);

[s_shrum]

Here is the complete revisted script I wrote:

=========

<?php

// Get db credentials
require ("./db_credentials.php");

// get the SQL statement parts from the query string
if ( $HTTP_GET_VARS['value'] ) { $value = $HTTP_GET_VARS['value']; } else { $value = '1'; }
$table = urldecode($HTTP_GET_VARS['table']);
$field = urldecode($HTTP_GET_VARS['field']);
$where = urldecode($HTTP_GET_VARS['where']);
$sql = "UPDATE $table SET $field=$field+$value WHERE $where;";

// connect to the database
$dbcnx = @mysql_connect($db_server, $db_user, $db_password);
if (!$dbcnx) { echo( "<p>Unable to connect to the database server at this time.</p>" ); exit(); }
if (! @mysql_select_db($db_table) ) { echo( "<p>Unable to locate database <b>" . $db_table . "</b> at this time.</p>" ); exit(); }
$result = @mysql_query(urldecode($sql));
if (!$result) { echo("<p>Error performing query: " . mysql_error() . "</p>"); exit(); }

header("Location: [url]http://[/url]" . $HTTP_GET_VARS['redirect']);

?>

===========

Instead of taking the SQL statement, I limit the scripts use to JUST incrementing a field based on the WHERE clause.

The problem now is with the '+' where I construct the complete $sql.

I am getting the following error out of mysql_error() after I perform @():

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 '1 WHERE area=\'Downloads\' AND title=\'Serials 2000\' AND url=[/b]

I have tried to escape the '+' character like '+' but that fails too.

Sean Shrum

[xblue]

you don't pass the complete query via url anymore as far as I can see, so I wonder why the + should still be a problem.

I'd recommend to echo the query to see how it looks.

[s_shrum]

Here is my script URL call:

http://local.shrum.php/increment_redirect.php?table=links&field=hits&where=area='Soaring'%20AND%20title='Boomerangwings'%20AND%20url='www.boomerangwings.com'&redirect=www.boomerangwings.com

Here is the output of echo($sql):

UPDATE links SET hits=hits+1 WHERE area=\'Soaring\' AND title=\'Boomerangwings\' AND url=\'www.boomerangwings.com\';

Here is the error message that I get back:

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 '1 WHERE area=\'Soaring\' AND title=\'Boomerangwings\' AND url=[/b]

I'm stumped....this should work.

Thanks for all the help so far...keep it coming.

Sean Shrum

[xblue]

no clue... I agree the statement looks fine, though the backslash in the error message looks a little suspicious to me.

is hits a numeric field? just asking because I'm not sure what would happen if it was a string type field.

apart from that I ran out of ideas, sorry 🙁

[superwormy]

Get rid of that trailing ; in your SQL statement, you don't need those in PHP.

[s_shrum]

The field that I am trying to increment is definitely a INT(4) field.

I've gone as far is creating a test table with just a ID field (INT,auto-increment) and a hits field (INT 4). I created 1 record and I can't even get the script to work with that. I get the same error.

Man this is nuts.

In case it makes any difference the MySQL version is 4.0.4 beta (what my host is running/where my database is located)

Sean Shrum

[s_shrum]

Just on a weird hunch, I decide to URLENCODE the $SQL....IT WORKED!

Thanks to everyone for their help...man that was a pain in the ass.

Is this a bug in MySQL or what?

Sean Shrum