Ken,
The typical naming convention for tables is all lower
case. It isn't an issue so much with mysql, but will be
a problem in the future when we start to support more
databases.
Also, mod_nakes should preclude all database names.
This is more of an organization thing, and a phpws
standard than anything.
There are several problems with use of table prefixes
within your code. Many of those problems could be
avoided by simply changing your installer to use only
one .sql file and do something like this:
set_time_limit(180);
if($status =
$GLOBALS["core"]->sqlImport(PHPWS_SOURCE_DIR .
"mod/nakes/boost/install.sql", TRUE)) {
with all the data from the nakes.sql file added to this
file. The true statement adds the table prefix if it
exists. This is very important, otherwise, you'll have
a bunch of trouble with core/Database.php
I removed all references to PHPWS_TBL_PREFIX with the
exception of one. Pretty much all of core/Database.php
functions second parameter is for adding the database
prefix. This is the prefered method for doing this as
it saves alot of extra syntax that can cause errors.
I can see that you were probably having problems
because of spacing in your sql queries. For example, if
you make your sql query like this:
$sql = "SELECT category, naics_id, naics, approved
FROM Nakes_naics, ".PHPWS_TBL_PREFIX."mod_Nakes_items
WHERE naics_id = naics AND approved = 1 AND hidden = 0
ORDER BY category";
$result = $GLOBALS["core"]->query($sql);
will output properly as is, however, say I remove the
table prefix constant and add the second parameter of
TRUE to query($sql, TRUE); the statement will render
incorrectly as:
SELECT category, prefix_naics_id, naics, approved
FROM Nakes_naics, ".PHPWS_TBL_PREFIX."mod_Nakes_items
WHERE naics_id = naics AND approved = 1 AND hidden = 0
ORDER BY category;
The code for the second parameter puts the prefix in
where it encounters the first space in the statement
that doesn't separate reserved sql query words. so to
avoid this...along with my name changes, my code looks
like this:
$sql = "SELECT category,naics_id,naics,approved FROM
mod_nakes_naics,
".PHPWS_TBL_PREFIX."mod_nakes_items
WHERE naics_id = naics AND approved = 1 AND hidden = 0
ORDER BY category";
This is kind of a complex example. The table prefix
code doesn't allow for a second table as it is written,
so the above statement is rendered correctly, but if
you leave the table prefix constant off the second
table name and a prefix actually exists, it will fail.
In the core/Database.php rewrite for 0.9.4, this has
been corrected, for now, this is the workaround.
HTH
Wendall
$result = $GLOBALS["core"]->query($sql,TRUE);
I used
