why doesn't this work? I think it is the way i am meshing two queries?...
$this->page = $this->dbh->selectrow_object(
"select * from ANS where user_ID = ? and ID = ? and q_id = ?",
array( $this->user_ID, $ID, $q_id )
);
if($this->page) { $this->page_ID = $this->page->ID; }
if($this->page_ID) {
$this->dbh->query(
'update ANS
set q_id = ?, score = ?, updated = now()
where ID = ? and user_ID = ?',
array( $q_id, $score, $this->page_ID, $this->user_ID )
);
$ID = $this->page_ID;
}huh?
$this->page = $this->dbh->selectrow_object(
"select * from ANS where user_ID = ? and ID = ? and q_id = ?",
array( $this->user_ID, $ID, $q_id )
);
what is that? you can't have ? as place holders. and what is that array doing? if you want to use place holders than you can only do this with "printf" or a variation of it.
so it ould look something like this
$this->page = $this->dbh->selectrow_object(sprintf(
"select * from ANS where user_ID = '%s' and ID = '%s' and q_id = '%s'" , $this->user_ID, $ID, $q_id ));ok - it still doesn't work, but at least now i understand it a bit more
if($ID) {
$this->dbh->query(
'update ANS
set score = ?, updated = now()
where q_id = ? and user_ID = ?',
array( q_id, $this->user_ID, $score )
);
} else {
$ID = $this->dbh->insert_query(
'insert into ANS
set user_ID = ?, q_id = ?, score = ?,
updated = now(), created = now()',
array( $this->user_ID, $q_id, $score )
);
}
the else statement works as it does the right things in the db
+----+---------+------+-------+---------------------+---------------------+
| ID | user_ID | q_id | score | created | updated |
+----+---------+------+-------+---------------------+---------------------+
| 34 | 9 | 1 | 2 | 2003-10-28 15:27:20 | 2003-10-28 15:27:20 |
it's the if statement that I am having problems with - it wont update the record, but goes straight to the else statement and creates a new record.
it shold be really simple - but I'm not getting it...
help!it is really simple. you can't use ? in your query. also that insert query isn't correct either so how can it work? to insert you don't set anything, you give it values.
none of that code will work.aha! becuase the insert query is defined in the dbhandle.inc
and is thus:
function insert_query( $query, $parameters = 0 ) {
$Q = $this->query( $query, $parameters );
return ($Q && mysql_affected_rows( $this->dbh )==1 ) ? mysql_insert_id( $this->dbh ) : 0;
}
this makes me think that I am using the wrong handler for the updat portion - hmmm. let me fiddle and see what happens...
but any ideas in the meantime are still useful. I am learning this stuff out here on my own from hacking examples and messing about.well there you go. you need to make the insert query correct. hacking away on other scripts is the weay to learn. most people do that.
if($ID) {
$this->dbh->query(
'update ANS
set score = '%s' , updated = now()
where q_id = '%s' and user_ID = '%s' ',
$this->user_ID, $ID, $q_id);
} else {
$ID = $this->dbh->insert_query(
'insert into ANS
(user_ID,q_id, score, updated, created) values ('%s','%s' ,'%s' ,'%s' )', $this->user_ID, $q_id, now(), now() );
}
that should workok - i see where you are getting at in prinicple, BUT, the quotes are mismatched - needs double quotes
"update ANS
set score = '%s' , updated = now()
where q_id = '%s' and user_ID = '%s' ",
and undefined functions of now()...
and once all the small errors have been gone through - it doesn't insert any values
| 45 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
(45 = primary key id)
so I really think where i am going wrong has nothing to do with the place holders, but something else...
if i find an answer i'll post itit has a lot to do with the place holders as those are invalid.
try this
if($ID) {
$this->dbh->query(
"update ANS
set score = '$score ' , updated = '".NOW()."'
where q_id = '$q_id' and user_ID = '$this->user_ID '" );
} else {
$ID = $this->dbh->insert_query(
"insert into ANS
(user_ID,q_id, score, updated, created) values ('$this->user_ID','$q_id' ,'".NOW()."' ,'".NOW()." "));
}
I have no idea what variable you need so you need to update those. if you get the same message about unidentified function for NOW() then do this ".NOW()."
$this->page = $this->dbh->selectrow_object(
"select * from ANS where user_ID = ? and ID = ? and q_id = ?",
array( $this->user_ID, $ID, $q_id )
);
if($this->page) { $this->page_ID = $this->page->ID; }
if($this->page_ID) {
$this->dbh->query(
'update ANS
set q_id = ?, score = ?, updated = now()
where ID = ? and user_ID = ?',
array( $q_id, $score, $this->page_ID, $this->user_ID )
);
$ID = $this->page_ID;
}huh?
$this->page = $this->dbh->selectrow_object(
"select * from ANS where user_ID = ? and ID = ? and q_id = ?",
array( $this->user_ID, $ID, $q_id )
);
what is that? you can't have ? as place holders. and what is that array doing? if you want to use place holders than you can only do this with "printf" or a variation of it.
so it ould look something like this
$this->page = $this->dbh->selectrow_object(sprintf(
"select * from ANS where user_ID = '%s' and ID = '%s' and q_id = '%s'" , $this->user_ID, $ID, $q_id ));ok - it still doesn't work, but at least now i understand it a bit more
if($ID) {
$this->dbh->query(
'update ANS
set score = ?, updated = now()
where q_id = ? and user_ID = ?',
array( q_id, $this->user_ID, $score )
);
} else {
$ID = $this->dbh->insert_query(
'insert into ANS
set user_ID = ?, q_id = ?, score = ?,
updated = now(), created = now()',
array( $this->user_ID, $q_id, $score )
);
}
the else statement works as it does the right things in the db
+----+---------+------+-------+---------------------+---------------------+
| ID | user_ID | q_id | score | created | updated |
+----+---------+------+-------+---------------------+---------------------+
| 34 | 9 | 1 | 2 | 2003-10-28 15:27:20 | 2003-10-28 15:27:20 |
it's the if statement that I am having problems with - it wont update the record, but goes straight to the else statement and creates a new record.
it shold be really simple - but I'm not getting it...
help!it is really simple. you can't use ? in your query. also that insert query isn't correct either so how can it work? to insert you don't set anything, you give it values.
none of that code will work.aha! becuase the insert query is defined in the dbhandle.inc
and is thus:
function insert_query( $query, $parameters = 0 ) {
$Q = $this->query( $query, $parameters );
return ($Q && mysql_affected_rows( $this->dbh )==1 ) ? mysql_insert_id( $this->dbh ) : 0;
}
this makes me think that I am using the wrong handler for the updat portion - hmmm. let me fiddle and see what happens...
but any ideas in the meantime are still useful. I am learning this stuff out here on my own from hacking examples and messing about.well there you go. you need to make the insert query correct. hacking away on other scripts is the weay to learn. most people do that.
if($ID) {
$this->dbh->query(
'update ANS
set score = '%s' , updated = now()
where q_id = '%s' and user_ID = '%s' ',
$this->user_ID, $ID, $q_id);
} else {
$ID = $this->dbh->insert_query(
'insert into ANS
(user_ID,q_id, score, updated, created) values ('%s','%s' ,'%s' ,'%s' )', $this->user_ID, $q_id, now(), now() );
}
that should workok - i see where you are getting at in prinicple, BUT, the quotes are mismatched - needs double quotes
"update ANS
set score = '%s' , updated = now()
where q_id = '%s' and user_ID = '%s' ",
and undefined functions of now()...
and once all the small errors have been gone through - it doesn't insert any values
| 45 | 0 | 0 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
(45 = primary key id)
so I really think where i am going wrong has nothing to do with the place holders, but something else...
if i find an answer i'll post itit has a lot to do with the place holders as those are invalid.
try this
if($ID) {
$this->dbh->query(
"update ANS
set score = '$score ' , updated = '".NOW()."'
where q_id = '$q_id' and user_ID = '$this->user_ID '" );
} else {
$ID = $this->dbh->insert_query(
"insert into ANS
(user_ID,q_id, score, updated, created) values ('$this->user_ID','$q_id' ,'".NOW()."' ,'".NOW()." "));
}
I have no idea what variable you need so you need to update those. if you get the same message about unidentified function for NOW() then do this ".NOW()."