joomla-php mysql没有使用先前查询中的数据更新记录

问题描述：

I'm counting the right answers field of a table and saving that calculated value on another table. For this I'm using two queryes, first one is the count query, i retrieve the value using loadResult(). After that i'm updating another table with this value and the date/time. The problem is that in some cases the calculated value is not being saved, only the date/time.

queries look something like this:

        $sql = 'SELECT count(answer)

                FROM #_questionsTable
                WHERE 

                    answer = 1

                    AND

                    testId = '.$examId;

        $db->setQuery($sql);

        $rightAnsCount = $db->loadResult();

        $sql = 'UPDATE #__testsTable

                SET finish = "'.date('Y-m-d H:i:s').'", rightAns='.$rightAnsCount.'

                WHERE testId = '.$examId;

        $db->setQuery($sql);

        $db->Query();

answer = 1 means that the question was answered ok.

I think that when the 2nd query is executed the first one has not finished yet, but everywhere i read says that it waits that the first query is finished to go to the 2nd, and i don't know how to make the 2nd query wait for the 1st one to end.

Any help will be appreciated. Thanks!

我正在计算表的正确答案字段并将该计算值保存在另一个表上。为此，我使用两个查询，第一个是计数查询，我使用loadResult（）检索值。之后，我正在使用此值和日期/时间更新另一个表。问题是，在某些情况下，未保存计算值，只有日期/时间。 p>

查询如下所示： p>

  $ sql ='SELECT count（answer）
 
 FROM #_questionsTable 
 WHERE 
 
 answer = 1 
 
 AND 
 
 testId ='。$ examId; 
 
 $ db  - ＆gt; setQuery（$ sql）; 
 
 $ rightAnsCount = $ db-＆gt; loadResult（）; 
 
 $ sql ='UPDATE #__ testsTable 
 
 SET finish =“'。date（'Ymd  H：i：s'）。'“，rightAns ='。$ rightAnsCount。'
 
 WHERE testId ='。$ examId; 
 
 $ db-＆gt; setQuery（$ sql）; 
 
  $ db-＆gt; Query（）; 
  code>  pre> 
 
  answer = 1表示问题已得到解答。 p> 
 
 
我认为 当第二个查询执行时，第一个查询尚未完成，但我读到的任何地方都说它等待第一个查询完成到第二个，我不知道如何使第二个查询等待 第一次 e结束。 p> 
 
 
任何帮助将不胜感激。 谢谢！ p> 
  div>

答

a PHP MySQL query is synchronous ie. it completes before returning - Joomla!'s database class doesn't implement any sort of asynchronous or call-back functionality.
While you are missing a ';' that wouldn't account for it working some of the time.
How is the rightAns column defined - eg. what happens when your $rightAnsCount is 0
Turn on Joomla!'s debug mode and check the SQL that's generated in out the profile section, it looks something like this

eg.

Profile Information

Application afterLoad: 0.002 seconds, 1.20 MB
Application afterInitialise: 0.078 seconds, 6.59 MB
Application afterRoute: 0.079 seconds, 6.70 MB
Application afterDispatch: 0.213 seconds, 7.87 MB
Application afterRender: 0.220 seconds, 8.07 MB
Memory Usage

8511696
8 queries logged.

SELECT * 
      FROM jos_session 
      WHERE session_id = '5cs53hoh2hqi9ccq69brditmm7'
DELETE 
      FROM jos_session 
      WHERE ( TIME < '1332089642' )
etc...

答

you may need to add a semicolon to the end of your sql queries

...testId = '.$examID.';';

ah, something cppl mentioned is the key I think. You may need to account for null values from your first query.

Changing this line:

$rightAnsCount = $db->loadResult();

To this might make the difference:

$rightAnsCount = ($db->loadResult()) ? $db->loadResult() : 0;

Basically setting to 0 if there is no result.

答

I am pretty sure you can do this in one query instead:

    $sql = 'UPDATE #__testsTable

            SET finish = NOW()
            , rightAns = (
               SELECT count(answer)
                 FROM #_questionsTable
                 WHERE 
                    answer = 1
                    AND
                    testId = '.$examId.'
            )

            WHERE testId = '.$examId;

    $db->setQuery($sql);
    $db->Query();

You can also update all values in all rows in your table this way by slightly modifying your query, so you can do all rows in one go. Let me know if this is what you are trying to achieve and I will rewrite the example.

joomla-php mysql没有使用先前查询中的数据更新记录

相关推荐