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!

1. 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.

2. While you are missing a ';' that wouldn't account for it working some of the time.

3. How is the rightAns column defined - eg. what happens when your $rightAnsCount is 0

4. 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.