big problam with updating

[redarrow]

advance thank you.

big problam today with my stats updating for my members what happens a user go to see anothe users profile and the stats colum should update via the date and the users id.

now all this was working the first entry get put in the database correctly but if you goto the same profile agin the database does not update the next entry i have echoed querys are all correct any idear cheers.

here the update query echoed out
[code]

update hits set Dec=Dec+1 where id='65535'

update problam
[/code]
[code]
<?php session_start();

$db=mysql_connect("localhost","xxx","xxx");
mysql_select_db("xxxx",$db);

$name=$_GET['name'];

$query="select * from members where name='$name'";
$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){

if($_GET['cmd']=="hit"){

$colname=($_POST['colname']);

$colname=date('M');

$query1="select * from hits where id='".$record['id']."'";
$result1=mysql_query($query1)or die("query problam");

if(!mysql_num_rows($result1)){

$id=$record['id'];
$jan="1";
$feb="1";
$mar="1";
$apr="1";
$may="1";
$jun="1";
$jul="1";
$aug="1";
$sep="1";
$oct="1";
$nov="1";
$dec="1";

$insert="insert into hits (`id`, `jan`, `feb`, `mar`, `apr`, `may`, `jun`, `jul`, `aug`, `sep`, `oct`, `nov`, `dec`)values('$id','$jan','$feb','$mar','$apr','$may','$jun','$jul','$aug','$sep','$oct','$nov','$dec')";
$in=mysql_query($insert)or die("insert problam");

}elseif(mysql_num_rows($result1)>0){

$update="update hits set $colname=$colname+1 where id='".$record['id']."'";
$resultu=mysql_query($update)or die("update problam");
}
}
[/code]

[jsladek]

What is the feild type in the database for [b]id[/b] 65535 is the highest possible value if it is 16bit.

-John

[redarrow]

database info
[code]
Field Type Attributes Null Default Extra Action
  id smallint(5)  UNSIGNED ZEROFILL Yes NULL               
  jan varchar(100)  No                 
  feb varchar(100)  No                 
  mar varchar(100)  No                 
  apr varchar(100)  No                 
  may varchar(100)  No                 
  jun varchar(100)  No                 
  jul varchar(100)  No                 
  aug varchar(100)  No                 
  sep varchar(100)  No                 
  oct varchar(100)  No                 
  nov varchar(100)  No                 
  dec varchar(100)  No
[/code]

[redarrow]

i have echoed out all the php code querys there all correct i have also tryed the id with a varchar it still coming back with a update problam any more idears cheers.

[trq]

Firstly, its [i]problem[/i] not [i]problam[/i]. Now, try using mysql_error() to see what the [i]problem[/i] might be.

[code=php:0]
die(mysql_error());
[/code]

[jsladek]

Try changing it from a smallint to an int

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

-John

[redarrow]

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 'Dec=Dec+1 where id='00000'' at line 1

[redarrow]

ok it this line but got no idear how to correct it

$update="update hits set $colname=$colname+1 where id='".$record[id]."'";

[trq]

Echo your sql statement and show us its output.

[redarrow]

update hits set Dec=Dec+1 where id='00000'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 'Dec=Dec+1 where id='00000'' at line 1

from this

$update="update hits set $colname=$colname+1 where id='".$record['id']."'";
echo $update;
$resultu=mysql_query($update)or die(mysql_error());

[craygo]

this
[code]$colname=$colname+1[/code]

Makes no sense. That would be a string + a number. i think tou want the value in the colname not the colname

[code]$colname = $result1[$colname]+1[/code]

Ray

[redarrow]

craygo the colname is working correctly the way i had it i tried your method but no joy, what colname is doing is getting the month name and then updating the database set on that month name so that correct as what i can see the id has got a double quote and the start of the stats got a single qoute for a strange reason.

[craygo]

well where si record['id'] coming from, because I do not see an array called $record. I see one called $result1. Unless it is somewhere else in the script.

Ray