johnhenderson
Posts: 3
|
| Posted: 08/27/2007, 4:43 PM |
|
Hi I wonder if anyone can help me...
I have looked at all the code examples in the forum, but none of it appears to help. I am trying to set a blank field to contain a value simply to save the user entering it, when creating a new record. I have a number of fields that need this function, but I am really stumped. My first attempt got me this error...
Fatal error: Call to a member function on a non-object in G:\xampp\htdocs\CollectionSystem\payments_events.php on line 61
after more playing around I ended up getting no error message but no result either?
So the following is how i have everything set up
I have a database called... CMS
I have a table called... payments and WorkPrice
I have a hidden field called... hidepayment
I have a field called... payment, which is formated as 0.00
I have a grid called... newpayment
I have used the before_show event on the form (and on the actual field to no avail)
The following is what I have managed so far using the forum examples.
function myfunction_BeforeShow(& $sender) {
global $newpayments;
global $DBCMS;
$db=new clsDBCMS();
$newpayments->payment->SetValue(CCDLookUp("payment","SELECT *
FROM workprice INNER JOIN payments ON
workprice.workpriceid = payments.payment
","payment=". $DBCMS->ToSQL(getvalue(payments), ccsInteger), $DBCMS) );
print $newpayments;
}
I am using the demo version of CCS3.2 and if I can get past this stage I will certainly purchase it. Otherwise, everything else about the software is just amazing and it really knocks the socks of dreamweaver.
thanks in antisipation
john henderson
|
 |
 |
datadoit.com
|
| Posted: 08/27/2007, 5:40 PM |
|
> global $DBCMS;
> $db=new clsDBCMS();
> $newpayments->payment->SetValue(CCDLookUp("payment","SELECT *
> FROM workprice INNER JOIN payments ON
> workprice.workpriceid = payments.payment
> ","payment=". $DBCMS->ToSQL(getvalue(payments), ccsInteger), $DBCMS) );
> print $newpayments;
> }
>
> john henderson
> ---------------------------------------
John, what do the table structures look like for your tables 'payments'
and 'WorkPrice'? What are the key fields for these tables and type of
relationship (one-to-one, one-to-many)?
It's really hard to tell what data you're trying to retrieve from your
sample so far. I think you'll be pleasantly surprised when you see how
the final lookup statement will look.
|
|
|
|
DonP
|
| Posted: 08/28/2007, 11:33 PM |
|
For CCS's built-in CCDLookUp() function, you do not need or want "SELECT"
nor any other SQL statements, nor do joins work. You CAN use some SQL
functions, such as "COUNT(fieldname)" for example. CCDLookUp() is designed
for selecting only one value from one field from one table like this:
$db=new clsDBCMS();
$fieldvalue = CCDLookUp("fieldname","tablename","ID=5", $db);
You'll need to follow the examples for using custom SQL rather than the
CCDLookUp() function. For example (and NOT tested nor even proofed well):
function myfunction_BeforeShow(& $sender) {
$db=new clsDBCMS();
$SQL = "SELECT payment FROM workprice INNER JOIN payments ON workpriceid =
payment ".
"WHERE payment=". $DBCMS->ToSQL(getvalue(payments),
ccsInteger), $DBCMS) );
$db->query($SQL);
$Result = $db->next_record();
if ($Result) {
$newpayments->payment->SetValue($db->f("payment"));
}
}
You probably also don't need the two globals although I am not sure how it
will work within a function. You didn't say what type of database you're
using but if it's MySQL, try LEFT rather than INNER on the join.
Don
"johnhenderson" <johnhenderson@forum.codecharge> wrote in message
news:546d361ac6676b@news.codecharge.com...
> Hi I wonder if anyone can help me...
>
> I have looked at all the code examples in the forum, but none of it
> appears to
> help. I am trying to set a blank field to contain a value simply to save
> the
> user entering it, when creating a new record. I have a number of fields
> that
> need this function, but I am really stumped. My first attempt got me this
> error...
>
> Fatal error: Call to a member function on a non-object in
> G:\xampp\htdocs\CollectionSystem\payments_events.php on line 61
>
> after more playing around I ended up getting no error message but no
> result
> either?
>
> So the following is how i have everything set up
>
> I have a database called... CMS
> I have a table called... payments and WorkPrice
> I have a hidden field called... hidepayment
> I have a field called... payment, which is formated as 0.00
> I have a grid called... newpayment
> I have used the before_show event on the form (and on the actual field to
> no
> avail)
>
> The following is what I have managed so far using the forum examples.
>
> function myfunction_BeforeShow(& $sender) {
> global $newpayments;
> global $DBCMS;
> $db=new clsDBCMS();
> $newpayments->payment->SetValue(CCDLookUp("payment","SELECT *
> FROM workprice INNER JOIN payments ON
> workprice.workpriceid = payments.payment
> ","payment=". $DBCMS->ToSQL(getvalue(payments), ccsInteger), $DBCMS) );
> print $newpayments;
> }
>
>
> I am using the demo version of CCS3.2 and if I can get past this stage I
> will
> certainly purchase it. Otherwise, everything else about the software is
> just
> amazing and it really knocks the socks of dreamweaver.
>
> thanks in antisipation
>
>
> john henderson
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
DonP
|
| Posted: 08/28/2007, 11:51 PM |
|
I don't think you want to use the ToSQL() function in your query as I cannot
see what it's for in this case, although it's late so maybe I missed the
obvious. If I understand correctly, you appear to also be using a
non-function getvalue() rather than CCGetParam(). If that's the case, try
something like this instead of the ToSQL() function:
function myfunction_BeforeShow(& $sender) {
$db=new clsDBCMS();
$SQL = "SELECT payment FROM workprice INNER JOIN payments ON workpriceid =
payment ".
"WHERE payment=". CCGetParam("payments"), $DBCMS) );
$db->query($SQL);
$Result = $db->next_record();
if ($Result) {
$newpayments->payment->SetValue($db->f("payment"));
}
}
"johnhenderson" <johnhenderson@forum.codecharge> wrote in message
news:546d361ac6676b@news.codecharge.com...
> Hi I wonder if anyone can help me...
>
> I have looked at all the code examples in the forum, but none of it
> appears to
> help. I am trying to set a blank field to contain a value simply to save
> the
> user entering it, when creating a new record. I have a number of fields
> that
> need this function, but I am really stumped. My first attempt got me this
> error...
>
> Fatal error: Call to a member function on a non-object in
> G:\xampp\htdocs\CollectionSystem\payments_events.php on line 61
>
> after more playing around I ended up getting no error message but no
> result
> either?
>
> So the following is how i have everything set up
>
> I have a database called... CMS
> I have a table called... payments and WorkPrice
> I have a hidden field called... hidepayment
> I have a field called... payment, which is formated as 0.00
> I have a grid called... newpayment
> I have used the before_show event on the form (and on the actual field to
> no
> avail)
>
> The following is what I have managed so far using the forum examples.
>
> function myfunction_BeforeShow(& $sender) {
> global $newpayments;
> global $DBCMS;
> $db=new clsDBCMS();
> $newpayments->payment->SetValue(CCDLookUp("payment","SELECT *
> FROM workprice INNER JOIN payments ON
> workprice.workpriceid = payments.payment
> ","payment=". $DBCMS->ToSQL(getvalue(payments), ccsInteger), $DBCMS) );
> print $newpayments;
> }
>
>
> I am using the demo version of CCS3.2 and if I can get past this stage I
> will
> certainly purchase it. Otherwise, everything else about the software is
> just
> amazing and it really knocks the socks of dreamweaver.
>
> thanks in antisipation
>
>
> john henderson
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
DonB
|
| Posted: 08/29/2007, 7:22 AM |
|
CCDLookUp can return only one VALUE from one ROW of the database. With the
"*" in the 2nd parameter, you are asking to retrieve multiple columns and
that is invalid. With no 'where', this probably will not limit the result
to one row, either.
The correct way to do the lookup is:
$value = CCDLookUp("columname","tablename","where", connection);
e.g.
$value = CCDLookUp("payment","workprice INNER JOIN payments ON
workprice.workpriceid = payments.payment, "something",$db);
the 'where' is not determinable from the information you provided. I
probably don't have the column name correct either because that's not really
clear from what you wrote.
--
DonB
http://ccswiki.gotodon.net
"johnhenderson" <johnhenderson@forum.codecharge> wrote in message
news:546d361ac6676b@news.codecharge.com...
> Hi I wonder if anyone can help me...
>
> I have looked at all the code examples in the forum, but none of it
appears to
> help. I am trying to set a blank field to contain a value simply to save
the
> user entering it, when creating a new record. I have a number of fields
that
> need this function, but I am really stumped. My first attempt got me this
> error...
>
> Fatal error: Call to a member function on a non-object in
> G:\xampp\htdocs\CollectionSystem\payments_events.php on line 61
>
> after more playing around I ended up getting no error message but no
result
> either?
>
> So the following is how i have everything set up
>
> I have a database called... CMS
> I have a table called... payments and WorkPrice
> I have a hidden field called... hidepayment
> I have a field called... payment, which is formated as 0.00
> I have a grid called... newpayment
> I have used the before_show event on the form (and on the actual field to
no
> avail)
>
> The following is what I have managed so far using the forum examples.
>
> function myfunction_BeforeShow(& $sender) {
> global $newpayments;
> global $DBCMS;
> $db=new clsDBCMS();
> $newpayments->payment->SetValue(CCDLookUp("payment","SELECT *
> FROM workprice INNER JOIN payments ON
> workprice.workpriceid = payments.payment
> ","payment=". $DBCMS->ToSQL(getvalue(payments), ccsInteger), $DBCMS) );
> print $newpayments;
> }
>
>
> I am using the demo version of CCS3.2 and if I can get past this stage I
will
> certainly purchase it. Otherwise, everything else about the software is
just
> amazing and it really knocks the socks of dreamweaver.
>
> thanks in antisipation
>
>
> john henderson
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
datadoit.com
|
| Posted: 08/29/2007, 8:37 AM |
|
This wouldn't work for retrieving multiple values with CCDLookUp()?
global $DBConnectionName;
CCDLookUp("*", "users", "user_id=1", $DBConnectionName);
$user_name = $DBConnectionName->f("user_name");
$user_login = $DBConnectionName->f("user_login");
I haven't test this, but I thought I may have done this in the past.
|
|
|
|
DonP
|
| Posted: 08/29/2007, 8:53 AM |
|
It doesn't give errors but your example will probably return only 1 or
whatever value is used in the WHERE portion. I tested it with:
CCDLookUp("*", "lookup_categories", "ID=3", $DBconnection);
and it returned 3. Regardless, it does not return multiple values since
CCDLookUp() will pull up only a single record..
Don
"datadoit.com" <datadoit@forum.codecharge> wrote in message
news:fb43rb$nnq$1@news.codecharge.com...
> This wouldn't work for retrieving multiple values with CCDLookUp()?
>
> global $DBConnectionName;
> CCDLookUp("*", "users", "user_id=1", $DBConnectionName);
> $user_name = $DBConnectionName->f("user_name");
> $user_login = $DBConnectionName->f("user_login");
>
> I haven't test this, but I thought I may have done this in the past.
|
|
|
|
datadoit.com
|
| Posted: 08/29/2007, 10:38 AM |
|
DonP wrote:
> It doesn't give errors but your example will probably return only 1 or
> whatever value is used in the WHERE portion. I tested it with:
>
> CCDLookUp("*", "lookup_categories", "ID=3", $DBconnection);
>
> and it returned 3. Regardless, it does not return multiple values since
> CCDLookUp() will pull up only a single record..
>
------------------------------------
Nossir. DonB stated that CCDLookUp() "can return only one VALUE from
one ROW of the database". That's not entirely true, since it can return
multiple values from a single row in the database (ie. SELECT *).
I think our interpretation of the word "values" differs.
|
|
|
|
DonP
|
| Posted: 08/29/2007, 11:31 AM |
|
You could be right if your definition is different but presuming we mean the
same thing with "value" (to me it means the contents of one single field of
a single row or a single count of entries of the table, for example), it
pulls up only a single value. If you can supply code that pulls up more
values, I would be happy to try it out because it would come in very handy.
There are other ways of pulling up multiple values, of course, but the CCS
Help does not indicate either that such a thing is possible using
CCDLookUp():
"Retrieves a single database value which can be a field or an expression.
The arguments passed to the function determine the value to be retrieved."
Although I had never tried it before this morning, when I tested your idea
of using "*" by echoing the results it returned only the ID value from the
WHERE but I'm not sure why it returned even that. "COUNT(*)" might work but
again it's pulling up only a single value which can be a count of multiple
records. The function itself, it does not appear to be able to pull up more
than one value at a time although it can accept a more elaborate WHERE of
just about any type than I used in my simple single-row example. From
Common.php:
function CCDLookUp($field_name, $table_name, $where_condition, &$db)
{
$sql = "SELECT " . $field_name . ($table_name ? " FROM " . $table_name :
"") . ($where_condition ? " WHERE " . $where_condition : "");
return CCGetDBValue($sql, $db);
}
I suppose you could run CCDLookUp() through a loop and make it return more
values, though.
"datadoit.com" <datadoit@forum.codecharge> wrote in message
news:fb4av7$rt8$1@news.codecharge.com...
> DonP wrote:
>> It doesn't give errors but your example will probably return only 1 or
>> whatever value is used in the WHERE portion. I tested it with:
>>
>> CCDLookUp("*", "lookup_categories", "ID=3", $DBconnection);
>>
>> and it returned 3. Regardless, it does not return multiple values since
>> CCDLookUp() will pull up only a single record..
>>
> ------------------------------------
>
> Nossir. DonB stated that CCDLookUp() "can return only one VALUE from one
> ROW of the database". That's not entirely true, since it can return
> multiple values from a single row in the database (ie. SELECT *).
>
> I think our interpretation of the word "values" differs.
|
|
|
|
datadoit.com
|
| Posted: 08/29/2007, 11:57 AM |
|
>
> I suppose you could run CCDLookUp() through a loop and make it return more
> values, though.
>
-------------------------
Real simple, run the code I provided (adjust to fit your Db of course):
global $DBConnectionName;
CCDLookUp("*", "users", "user_id=1", $DBConnectionName);
$user_name = $DBConnectionName->f("user_name");
$user_login = $DBConnectionName->f("user_login");
etc
etc
From the one record, you have access to ALL field values from that record.
|
|
|
|
DonB
|
| Posted: 08/29/2007, 12:26 PM |
|
True, but a bit of a 'cheat' (an excellent example of 'thinking outside of
the box', though). The CCDLookUp returns a single value (one row, one
column), although it leaves the DB connection 'alive' so you can reference
other elements of the query that was last executed
CCDLookUp executes this:
$dbvalue = $db->next_record() ? $db->f(0) : "";
and returns whatever is in column 0 of the first (or only) row of the result
set. Thus
echo CCDLookUp("somecolumn","sometable","somewhere", $db);
displays the value from 'somecolumn, the same as would
echo $db->f(0);
however
echo CCDLookUp("*","sometable","somewhere", $db);
would return the first column of the first row of 'sometable', thus perhaps
not the value of 'somecolumn'
--
DonB
http://ccswiki.gotodon.net
"datadoit.com" <datadoit@forum.codecharge> wrote in message
news:fb4fid$ugb$1@news.codecharge.com...
> >
> > I suppose you could run CCDLookUp() through a loop and make it return
more
> > values, though.
> >
> -------------------------
>
> Real simple, run the code I provided (adjust to fit your Db of course):
>
> global $DBConnectionName;
> CCDLookUp("*", "users", "user_id=1", $DBConnectionName);
> $user_name = $DBConnectionName->f("user_name");
> $user_login = $DBConnectionName->f("user_login");
> etc
> etc
>
> From the one record, you have access to ALL field values from that
record.
|
|
|
|
wkempees
|
| Posted: 08/29/2007, 2:49 PM |
|
AND as a friendly warning:
Doing the 'multi' value using the global $DBConnection
as opposed to declaring a separate
$db new clsDBConnection
could result in very screwed up data!
Both in the result as well as in the original dataset.
BTW
'value' as in the first parameter of CCDLookup(), thus a single field;
'rows' as in the result of the Where parameter of CCDLookup(), thus one or
many
to be used only to retreive a single 'value' ( count(*), max() etc).
Also the use of single and double quotes is important and handy as well.
Walter
(loved this thread)
|
|
|
|
johnhenderson
Posts: 3
|
| Posted: 09/01/2007, 1:52 PM |
|
Hi would just like to say thanks for all the feed back that you guys gave. Alas it did not solve my problem but I am still learning.
Guess that all takes time. 
regards
John Henderson
|
|
|
|
wkempees
|
| Posted: 09/02/2007, 2:07 PM |
|
John,
post your problem!
Who knows.......
Walter
"johnhenderson" <johnhenderson@forum.codecharge> schreef in bericht
news:546d9d0fef2e70@news.codecharge.com...
> Hi would just like to say thanks for all the feed back that you guys gave.
> Alas
> it did not solve my problem but I am still learning.
>
> Guess that all takes time.
>
>
> regards
>
>
> John Henderson
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
