Dreamweaver 8 Query - GetSQLValueString()

Report · Jan 08, 2007

Can anyone explain how the new DW8 function GetSQLValueString() works and why I now see CONCAT('%%', %s, '%%') in my queries? I was just getting up to speed on how MySql queries work and now we have a new wrench in the cog.

I typically do a fair amount of customization in my queries and am now having all sorts of problems with the new format. Help!?

For Reference:

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

Report · Jan 09, 2007

lbombardier wrote:
> Can anyone explain how the new DW8 function GetSQLValueString() works and why I
> now see CONCAT('%%', %s, '%%') in my queries? I was just getting up to speed on
> how MySql queries work and now we have a new wrench in the cog.

Which version of Dreamweaver are you using? CONCAT('%%', %s, '%%') is
not the correct syntax. It's certainly not the syntax used by
Dreamweaver 8.0.2 with the PHP extension fix (get it from
http://www.adobe.com/go/b6c2ae2a).

The type of query you're doing should look like this:

$query_Recordset1 = sprintf("SELECT * FROM authors WHERE family_name
LIKE %s", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 09, 2007

On Tue, 09 Jan 2007 11:14:19 +0000, David Powers <david@example.com>
wrote:

>Which version of Dreamweaver are you using? CONCAT('%%', %s, '%%') is
>not the correct syntax. It's certainly not the syntax used by
>Dreamweaver 8.0.2 with the PHP extension fix (get it from
> http://www.adobe.com/go/b6c2ae2a).
>
>The type of query you're doing should look like this:
>
>$query_Recordset1 = sprintf("SELECT * FROM authors WHERE family_name
>LIKE %s", GetSQLValueString("%" . $colname_Recordset1 . "%", "text"));

I just looked at a few of my recent queries and found some that have
this:

DATE_FORMAT(tbl_membership.m_renewdate, '%%b %%e %%Y') AS renewdate

is this also wrong? It works and was generated by DW. I'm using ver.
8.0.2 with the PHP hotfix.

--
Steve
steve at flyingtigerwebdesign dot com

Report · Jan 09, 2007

Steve wrote:
> I just looked at a few of my recent queries and found some that have
> this:
>
> DATE_FORMAT(tbl_membership.m_renewdate, '%%b %%e %%Y') AS renewdate
>
> is this also wrong? It works and was generated by DW. I'm using ver.
> 8.0.2 with the PHP hotfix.

Yes, it is. I've never seen that before, but there shouldn't be a
doubling of the percentage signs.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 09, 2007

David Powers wrote:
> Steve wrote:
>> I just looked at a few of my recent queries and found some that have
>> this:
>>
>> DATE_FORMAT(tbl_membership.m_renewdate, '%%b %%e %%Y') AS renewdate
>>
>> is this also wrong? It works and was generated by DW. I'm using ver.
>> 8.0.2 with the PHP hotfix.
>
> Yes, it is. I've never seen that before, but there shouldn't be a
> doubling of the percentage signs.

I take it all back. I was just looking at the snippet of code you
quoted. It's perfectly fine in the context of a Dreamweaver recordset.
I've just double-checked some of my own code; it's the same - and it
works perfectly.

The Dreamweaver-generated query uses sprintf(), which is why the
percentage signs in DATE_FORMAT need to be escaped. Once that has been
parsed by sprintf(), it will look like this:

DATE_FORMAT(tbl_membership.m_renewdate, '%b %e %Y') AS renewdate

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Jan 09, 2007

On Tue, 09 Jan 2007 13:50:13 +0000, David Powers <david@example.com>
wrote:

>David Powers wrote:
>> Steve wrote:
>>> I just looked at a few of my recent queries and found some that have
>>> this:
>>>
>>> DATE_FORMAT(tbl_membership.m_renewdate, '%%b %%e %%Y') AS renewdate
>>>
>>> is this also wrong? It works and was generated by DW. I'm using ver.
>>> 8.0.2 with the PHP hotfix.
>>
>> Yes, it is. I've never seen that before, but there shouldn't be a
>> doubling of the percentage signs.
>
>I take it all back. I was just looking at the snippet of code you
>quoted. It's perfectly fine in the context of a Dreamweaver recordset.
>I've just double-checked some of my own code; it's the same - and it
>works perfectly.
>
>The Dreamweaver-generated query uses sprintf(), which is why the
>percentage signs in DATE_FORMAT need to be escaped. Once that has been
>parsed by sprintf(), it will look like this:
>
>DATE_FORMAT(tbl_membership.m_renewdate, '%b %e %Y') AS renewdate

Many thanks for the clarification, David.
--
Steve
steve at flyingtigerwebdesign dot com