I am having an issue with $smcFunc returning and information about views. It does not seem to want to do so. I coded a MOD with native mysqli calls to make sure I could get what I needed done. When satisfied, I converted the mysqli calls to $smcFunc calls one at a time to verify the that they worked the same and had a few issues (a big one with db_create_table but that's another discussion). Now these queries works with the native mysqli calls but not with the $smcFunc calls. Is this a pre-defined limitation with views and $smcFunc calls. This works fine for regular tables...
$result = $smcFunc['db_query']('', '
SHOW TABLES LIKE \'{db_prefix}log_topic_stats_view\'',
array()
);
or
$result = $smcFunc['db_query']('', '
Select Hits from \'{db_prefix}log_topic_stats_view\'',
array()
);
BTW, I tried with single and double quotes.
Error Message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''rs_log_topic_stats_view'' at line 1
File: /home/content/57/10005557/html/smftest/Sources/TopicVisitsStats.php
Line: 35
Try without the quotes on your table names. Everything else should be fine after that.
IIRC SMF will replace {db_prefix} with:
$db_name . "." . $db_prefix
So it would convert {db_prefix}messages into mydb.smf_messages, similarly it would convert `{db_prefix}messages` into `mydb.smf_messages` which isn't the same thing. If you try that in PHPMyAdmin I don't think it'll work whilst the former will work.
Although `mydb`.`smf_messages` may be better to some extent, it would take a lot of processing on SMF's behalf to allow you to use that form along with the form that is normally used. It also might not work on SQLite/PostgreSQL - it's easier to just use the normal method.
Ok, that makes sense now. Since in the error message I did not get the full sql statement to view, it was not readily evident. This is the reason it will work with native mysqli calls - no conversions. Thanks for your insight. I think I saw some calls in SMF that will take a sql statement and return it converted, in the future I will run any SQL errors thru that to check for conversion issues.
$smcFunc['db_quote']($statement, $values) should return the formatted query for you as a string (just doesn't run it) - that's the function you're looking for I think.
Yes, Thanks. It will save some headaches ;)