Php not returning the latest updated sql procedure result

Question

Im using mariaDB, codeigniter php

when execute the procedure in workbench it showing correct result.

But when i run the same procedure using the php codeigniter its return different result set.

array(1) {
[0]=>
array(1) {
    [0]=>
    array(2) {
        ["stuScore"]=> string(7) "44.0000"
        ["answerdQues"]=> string(2) "50"
    }
}
}

query in procedure...

SELECT sum(Score) as stuScore, count(distinct ta1.idTestQuestion) as answerdQues
            FROM (select ta0.*, @running_time := if(@running_student = idStudent, @running_time, 0) + ta0.TimeTaken, date_add(ta0.StartTime, INTERVAL @running_time SECOND) as running_time, @running_student := idStudent
                from (select tap.idStudent, ta.score, ta.idTestQuestion, tap.StartTime, ta.TimeTaken
                    from `testanswerpaper` tap
                    left join testanswer ta on ta.idTestAnswerPaper = tap.idTestAnswerPaper and (ta.Status = 'Flagged' || ta.Status = 'Answered')
                    where  tap.`idTestQuestionPaper` = TestQuestionPaperID
                    order by tap.idStudent, ta.SortOrder, ta.idTestAnswer
                ) ta0
                join (select @running_time := 0, @running_student) running
            ) ta1
            join student s on s.idStudent = ta1.idStudent
            join user u on s.idUser = u.idUser
            WHERE ta1.running_time <= now()
            group by ta1.idStudent
            order by stuScore desc, answerdQues DESC;

php code is

$this->readDB = $this->load->database('read', TRUE);
        $connectId = $this->readDB->conn_id ;
        $sql = "call GetLeaderBoardData($TestQuestionPaperID);";
        if (mysqli_multi_query($connectId,$sql))
        {
            do
            {
                // Store first result set
                if ($result=mysqli_store_result($connectId)) {

                        $resultArray[] = mysqli_fetch_all($result, MYSQLI_ASSOC);

                }
            } while (mysqli_next_result($connectId));

        } 
        var_dump($resultArray);

Why are you using mysql raw queries inside CodeIgniter. Have you tried using $this->db->query( $sql ) ? — ascsoftw
– ascsoftw, Commented Oct 3, 2019 at 6:00
Are you sure that TestQuestionPaperID is same in both PHP query and database calling procedure? — Sunny
– Sunny, Commented Oct 3, 2019 at 6:21
What are you trying to do with this Stored procedure. Most likely, variables are not getting executed (as per your understanding of order of execution). Anyways, a bit of explanation is given here: stackoverflow.com/a/53465139 Still if you need assistance you need to setup a Fiddle with some sample data to play with. Please read: Why should I provide a Minimal Reproducible Example for a very simple SQL query? — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 3, 2019 at 7:41

HoldOffHunger · Accepted Answer · 2020-08-14 20:03:08Z

6

+50

The difference might come from the fact that the user-defined variables might have different values when you execute the code from workbench vs codeigniter as the user-defined variables keep their values throughout the session.

To rule this out, reset the @running_time and @running_student values at the beginning of the procedure.

set @running_time = null;
set @running_student = null;

SELECT sum(Score)...

edited Aug 14, 2020 at 20:03

HoldOffHunger

21.3k11 gold badges123 silver badges147 bronze badges

answered Oct 3, 2019 at 20:35

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rick James Over a year ago

@running_time is initialized to zero; @running_student is not.

438sunil Over a year ago

But i have intialized @running_time to zero.

slaakso Over a year ago

As @RickJames said, the @running_student is not initialized. When you use user-defined variables (@var) in your code, keep in mind that they maintain their values throughout the session and can also change when you call other routines.

Rick James Over a year ago

@438sunil - I don't think join (select @running_time := 0, @running_student) running changes running time.

Salman Arshad Over a year ago

Even if you initialize the variables properly, there is no guarantee that MariaDB will honor the order by tap.idStudent... or calculate @running_time := before calculating date_add(..., INTERVAL @running_time SECOND) or before @running_student := in the select clause.

Swarna Sekhar Dhar · Accepted Answer · 2019-10-04 12:24:31Z

0

try this code or use $this->readDB->reconnect(); function just before procedure coll every time.

try {
            $this->readDB = $this->load->database('read', TRUE);
            $this->readDB->reconnect();
            $sql = "CALL GetLeaderBoardData(".$TestQuestionPaperID.")";
            $resultArray = $this->readDB->query($sql)->custom_result_object(); 
            $this->readDB->close();
        } catch (Exception $e) {
            echo $e->getMessage();
        }
        var_dump($resultArray);

edited Oct 4, 2019 at 12:24

answered Oct 4, 2019 at 12:17

Swarna Sekhar Dhar

5461 gold badge8 silver badges26 bronze badges

Comments

Shaunak Sontakke · Accepted Answer · 2019-10-09 14:13:09Z

0

Try var_dump on mysqli_fetch_all() statement that you have. It would give you idea of what’s being returned.

In your stored procedure, assign the complete query to a variable and print that to ensure that the sql is what you are expecting.

answered Oct 9, 2019 at 14:13

Shaunak Sontakke

1,3001 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

Php not returning the latest updated sql procedure result

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related