mysql查询中的表名语法错误 [英] Syntax error on table name in mysql query

问题描述

我无法使用php代码在Joomla文章中引用数据库视图.尽管我可以访问表，但无法引用视图.请注意，数据使用phpMyAdmin存储在MySQL中.以下是我的代码，其中视图名称是jos2_vw_member_contribution.

I'm unable to reference a database view in a Joomla article using php code. Though I am able to access a table, I'm unable to reference a view. Please note that the data is stored in MySQL using phpMyAdmin. Below is my code where the view name is jos2_vw_member_contribution.

<?php 
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('Recipient','Target', 'Actuals'))));
$query->from($db->quoteName('jos2_vw_member_contribution'));
$query->setOrder('Recipient','ASC');
$db->setQuery($query);
$results = $db->loadObjectList();
?>

以上抛出错误 1146-表'.#__ vw_member_contribution'不存在.

感谢您对上述问题的早期帮助.另外，我强烈建议不使用此线程来讨论视图与表用法的优缺点.

Your early help is appreciated to the above problem. Also, I strongly urge not to use this thread to discuss the pros and cons of the view vs. table usage.

谢谢，Paippad.

Thanks, Paippad.

推荐答案

符合 #__ .(尽管您的错误表明您正在使用 #__ .)

In accordance with Joomla's Coding Standards (Section labeled: SQL Queries), you should not be literally naming the table "prefix", you should use #__. (Though your error indicates that you are using #__.)

表名前面那个讨厌的小.看起来很麻烦.您最好检查一下表名是否正确:php中的 #__ vw_member_contribution .

That pesky little . in front of your table name looks troublesome. You'd better check that your table name is precisely: #__vw_member_contribution in your php.

-> setOrder()将会悄悄终止您的查询，因为它不是查询方法(资源:

->setOrder() is going to silently kill your query because it is not a query method (resource: https://api.joomla.org/cms-3/classes/JDatabaseQuery.html), you should be using order()

建议的未经测试的代码段，带有诊断和错误捕获功能:

Suggested untested snippet with diagnostics and error catching:

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select($db->quoteName(array('Recipient','Target','Actuals')))
    ->from($db->quoteName('#__vw_member_contribution'))
    ->order($db->quoteName('Recipient'));  // ASC is the default direction

JFactory::getApplication()->enqueueMessage("<div>Rendered Select Query:<br>" . $query->dump() . "</div>", 'notice');  // don't show to public

try
{
    $db->setQuery($query);
    if (!$results = $db->loadObjectList())  // declare and check $result
    {
        echo "No matches found";
    }
    else
    {
        // do your thing with $result
    }
}
catch (Exception $e)
{
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // don't show to the public
}

p.s.如果您有关于Joomla的问题，请将其发布在 Joomla Stack Exchange 上，以从具有Joomla专门知识的志愿者那里获得宝贵的反馈意见.

p.s. When you have Joomla questions, please post them at Joomla Stack Exchange to receive valuable feedback from volunteers with intimate expertise with Joomla.

这篇关于mysql查询中的表名语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！