[RESOLVED] Storing Query in Table

[tweaky]

I am working on a project where certain sql queries are stored in a TEXT data field. I am trying to get the following sql into the table and not having any luck.

SELECT per_ID as AddToCart,CONCAT('<a
href=PersonView.php?PersonID=',per_ID,'>',per_FirstName,'
',per_LastName,'</a>') AS Name,
CONCAT(per_BirthMonth,'/',per_BirthDay,'/',per_BirthYear) AS 'Birth Date',
YEAR(CURRENT_DATE) - per_BirthYear - 1 AS 'Age'
FROM person_per
WHERE
DATE_ADD(CONCAT(per_BirthYear,'-',per_BirthMonth,'-',per_BirthDay),INTERVAL
~min~ YEAR) <= CURDATE()
AND
DATE_ADD(CONCAT(per_BirthYear,'-',per_BirthMonth,'-',per_BirthDay),INTERVAL
(~max~ + 1) YEAR) >= CURDATE()

My php code is:

$sSQL = "UPDATE `query_qry` SET `qry_SQL` = \'SELECT per_ID as
AddToCart,CONCAT(\'<a href=PersonView.php?PersonID=\',per_ID,\'>\',
per_FirstName,\' \', per_LastName,\'</a>\') AS Name,
CONCAT(per_BirthMonth,\'/\',per_BirthDay,\'/\',per_BirthYear) AS \'Birth
Date\', YEAR(CURRENT_DATE) - per_BirthYear - 1 AS \'Age\' FROM person_per
WHERE DATE_ADD
(CONCAT(per_BirthYear,\'-\',per_BirthMonth,\'-\',per_BirthDay), INTERVAL
~min~ YEAR) <= CURDATE() AND DATE_ADD
(CONCAT(per_BirthYear,\'-\',per_BirthMonth,\'-\',per_BirthDay), INTERVAL
(~max~ + 1 YEAR)  >= CURDATE()\' WHERE `query_qry`.`qry_ID` = 4 ";

everything gets entered correctly except the

INTERVAL (~max~ + 1 YEAR)

The results are

INTERVAL ~max~ YEAR)

Anyone have any ideas? If I just use the UPDATE code (minus the escaping ) in phpmyadmin, it works correctly.

[tomhath]

I can't offer help on why it doesn't work. But I am curious why you would want to store the queries in the database. Why not just put them in .php files and call them directly?

[NogDog]

I would probably do it like this:

$queryText = <<<EOD
SELECT per_ID as AddToCart,CONCAT('<a
href=PersonView.php?PersonID=',per_ID,'>',per_FirstName,'
',per_LastName,'</a>') AS Name,
CONCAT(per_BirthMonth,'/',per_BirthDay,'/',per_BirthYear) AS 'Birth Date',
YEAR(CURRENT_DATE) - per_BirthYear - 1 AS 'Age'
FROM person_per
WHERE
DATE_ADD(CONCAT(per_BirthYear,'-',per_BirthMonth,'-',per_BirthDay),INTERVAL
~min~ YEAR) <= CURDATE()
AND
DATE_ADD(CONCAT(per_BirthYear,'-',per_BirthMonth,'-',per_BirthDay),INTERVAL
(~max~ + 1) YEAR) >= CURDATE()
EOD;

$sSQL = "UPDATE `query_qry` SET `qry_SQL` = '" .
         mysql_real_escape_string($queryText) .
         "' WHERE `query_qry`.`qry_ID` = 4 ";

[tweaky]

Thank you!! That solved the issue and I learned something new... EOD