Cannot figure out how to run a mysqli_multi_query and use the results from the last query

Question 1

I've never used mysqli_multi_query before and it's boggling my brain, any examples I find on the net aren't helping me to figure out exactly what it is I want to do.

Here is my code:

<?php

    $link = mysqli_connect("server", "user", "pass", "db");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }

    $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
        `agent_name` varchar(20) NOT NULL,
        `job_number` int(5) NOT NULL,
        `job_value` decimal(3,1) NOT NULL,
        `points_value` decimal(8,2) NOT NULL
    );";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

    $i = 0;
    $agentsresult = mysqli_multi_query($link, $agentsquery);

    while ($row = mysqli_fetch_array($agentsresult)){
        $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
        $i++;
?>

            <tr class="tr<?php echo ($i & 1) ?>">
                <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                <td><?php echo $row['SUM(job_value)'] ?></td>
                <td><?php echo $row['SUM(points_value)'] ?></td>
                <td><?php echo $number_of_apps; ?></td>
            </tr>

<?php

    }
?>

All I'm trying to do is run a multiple query and then use the final results from those 4 queries and put them into my tables.

the code above really doesn't work at all, I just get the following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocshydroboardhydro_reporting_2010.php on line 391

any help?

Question 2

Okay after some fiddling around, trial and error and taking reference from another post that I came across in a Google search I've managed to solve my problem!

Here's the new code:

<?php

    $link = mysqli_connect("server", "user", "pass", "db");

    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }

    $agentsquery = "CREATE TEMPORARY TABLE LeaderBoard (
        `agent_name` varchar(20) NOT NULL,
        `job_number` int(5) NOT NULL,
        `job_value` decimal(3,1) NOT NULL,
        `points_value` decimal(8,2) NOT NULL
    );";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`, `job_number`, `job_value`, `points_value`) SELECT agent_name, job_number, job_value, points_value FROM jobs WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "INSERT INTO LeaderBoard (`agent_name`) SELECT DISTINCT agent_name FROM apps WHERE YEAR(booked_date) = $current_year && WEEKOFYEAR(booked_date) = $weeknum;";
    $agentsquery .= "SELECT agent_name, SUM(job_value), SUM(points_value) FROM leaderboard GROUP BY agent_name ORDER BY SUM(points_value) DESC";

    mysqli_multi_query($link, $agentsquery) or die("MySQL Error: " . mysqli_error($link) . "<hr>nQuery: $agentsquery");
    mysqli_next_result($link);
    mysqli_next_result($link);
    mysqli_next_result($link);

    if ($result = mysqli_store_result($link)) {
        $i = 0;
        while ($row = mysqli_fetch_array($result)){
            $number_of_apps = getAgentAppsWeek($row['agent_name'],$weeknum,$current_year);
            $i++;
?>

            <tr class="tr<?php echo ($i & 1) ?>">
                <td style="font-weight: bold;"><?php echo $row['agent_name'] ?></td>
                <td><?php echo $row['SUM(job_value)'] ?></td>
                <td><?php echo $row['SUM(points_value)'] ?></td>
                <td><?php echo $number_of_apps; ?></td>
            </tr>

<?php

        }
    }
?>

after sticking mysqli_next_result in there multiple times for each query it magically worked! yay! I understand why it works, because i'm telling it to skip to the next result 3 times, so it skips to the result for query #4 which is the one i want to use.

Seems a bit clunky to me though, there should just be a command for something like mysqli_last_result($link) or something if you ask me...

Thanks for the help rik and f00, I got there eventually :)

Question 3

Try it with

} while ($mysqli->more_results() && $mysqli->next_result());

sscce:

<?php
ini_set('display_errors', 'on');
error_reporting(E_ALL|E_STRICT);

$mysqli = new mysqli("localhost", "localonly", "localonly", "test");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %sn", mysqli_connect_error());
    exit();
}

$mysqli->query('CREATE TEMPORARY TABLE City (ID int auto_increment, `Name` varchar(32), primary key(ID))') or die($mysqli->error);

$stmt = $mysqli->prepare("INSERT INTO City (`Name`) VALUES (?)") or die($mysqli->error);
$stmt->bind_param('s', $city) or die($stmt->error);
foreach(range('A','Z') as $c) {
    $city = 'city'.$c;
    $stmt->execute() or die($stmt->error);
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (!$mysqli->multi_query($query)) {
    trigger_error('multi_query failed: '.$mysqli->error, E_USER_ERROR);
}
else {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("'%s'n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------n");
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

prints

'localonly@localhost'
-----------------
'cityU'
'cityV'
'cityW'
'cityX'
'cityY'

without warnings/notices.

Question 4

First: there is an error. You are missing a where in the first query:

$query = "SELECT * FROM `student_record` id = 201102887;";

has to be:

$query = "SELECT * FROM `student_record` where id = 201102887;";

That is sufficient to have a blank screen.

For the rest your code is ok, in line with the classical example from:

http://php.net/manual/en/mysqli.multi-query.php

What I wonder is if you really configured your server to execute php inside html.

Put this part of your code with the above correction in a .php file and you will see results:

<?php
$link = mysqli_connect('localhost', 'root', '', 'uoh');
$query = "SELECT * FROM `student_record` where id = 201102887;";
$query .= "SELECT * FROM `course` where id = 201102887;"; 

    if (mysqli_multi_query($link, $query)) {
        do {
            if ($result = mysqli_store_result($link)) {
                while ($row = mysqli_fetch_array($result)) {
                    echo $row['code'];
                    echo $row['term'];
                }
            }   
        } while (mysqli_next_result($link));
    }
/* close connection */
mysqli_close($link);
?>

I added the missing where and the closure of the connection.

By the way, I hope you are sure the records with those ids exist in your DB.

Question 5

Stick with a single main loop, and don't use threads if you don't really need them.

You already figured out that your game consists of different screens (let's call them Scene so we don't confuse it with the actual screen where we draw stuff), so the next logical step is to seperate them out from the main loop.

Say you have the following scenes:

intro
main menu
game play

then you should create a class for each of those, which will then be responsible for drawing/event handling/whatever-needed-in-this-part.

A simple example:

import pygame
pygame.init()

class Scene(object):
    screen = None

class Intro(Scene):
    def __init__(self):
        self.c = (32, 32, 100)

    def draw(self):
        Scene.screen.fill(self.c)

    def update(self):
        # since scenes are classes, they have a state that we can modify
        r,g,b = self.c
        r += 1
        g += 1
        b += 2
        if r > 255: r = 0
        if g > 255: g = 0
        if b > 255: b = 0
        self.c = r, g, b

    def handle(self, event):
        # move to Menu-scene when space is pressed
        if event.type == pygame.KEYDOWN:
            if event.key == pygame.K_SPACE:
                # returning a scene from handle or update changes the current scene
                return Menu()

class Menu(Scene):
    def draw(self):
        # draw menu
        Scene.screen.fill((200, 200, 100))

    def update(self):
        pass
        # do something

    def handle(self, event):
        # handle menu input
        if event.type == pygame.KEYDOWN:
            if event.key == pygame.K_a:
                return Game()
            if event.key == pygame.K_b:
                return Intro()

class Game(Scene):
    pass # implement draw update handle

Scene.screen = pygame.display.set_mode((800, 600))

scene = Intro()
while True:
    if pygame.event.get(pygame.QUIT): break
    for e in pygame.event.get(): 
        scene = scene.handle(e) or scene
    scene = scene.update() or scene
    scene.draw()
    pygame.display.flip()

This way, you have a single mainloop, but different parts of your game are managed by seperate classes. In the simple example above, each scene knows what other scene to activate on a certain event, but this is not necessary. You could create some kind of SceneHandler that manages the transitions between the scenes. Also, you may want to have some kind of game state object that you want to pass around scenes (e.g. show your points from the game scene in your game-over scene).

Question 6

As was explained yesterday in this, the [CONCURRENT]-processing is technically achievable in several different ways in python, each with a way different cost.

Today, let's have a look onto another approach - using a framework, that was developed with the very same motivation - having the natural [CONCURRENT]-scheduling-already-in-DNA - originally intended for easy composing and smooth operating complex GUI Man-Machine-Interactions ( MMI ).

This framework may and will help you achieve a lot, right due to the fact, it has evolved with a lot of care for exactly the same scenarios, where more than one thing has to be monitored at once:

Welcome to Tkinter GUI framework, which we will use just for its smart concurrently operated event handlers.

I was many times positively surprised, how easy it comes to build a quite complex composition of Finite-State-Automata ( FSA ), that smoothly cooperate together ( a coalition of FSA-s ), using tools for both independent, isolated operations ( the inner-logic of each FSA ), yet being easily able to propagate signals / messages from one FSA towards another(s). Yes, they can actually operate in 1-event-source-FSA : N-consumer(s)-FSA(s)

There you can create ( with ZeroMQ always in a non-blocking manner ) handlers -- one "sniffer" for a regular checking ( best by a timeout-controlled .poll() method to { NACK | POSACK } anything to read ) -- another "reader" for actual reading from the ZeroMQ Socket() instance ( triggered by the POSACK-signal from the "sniffer", as was mentioned previously -- another "do-a-work-er" for any other task one may wish to operate

Tkinter .mainloop() method is the global controller, which orchestrates the dirty job for you.

The hich level concept of Tkinter-mediated agent's co-processing starts in main() as simple as:

def main():
    root = Tk()                      # INIT a Tk() instance
    root.lift()                      #      + make it visible
    app = myApplication( root )      # SET ( [HERE] are all your app gems )
    root.mainloop()                  # START the core event-handling orchestrator

Tkinter might look as a garrage full of various GUI-gadgets, that have nothing to do with your problem, but don't panic.

Tkinter has incredibly well created tools right for your needs.

using control variables that will be used as a means for storing, signalling and propagating changes of values among otherwise independent and explicitly un-coordinated actors ( ref. "sniffer", "reader", "worker" and any others ... )
tools for handling events - real, abstract and even virtual
tools for handling timed-operations - in a form of an almost a lightweight real-time system, using setups with preferred timing of what shall happen next, the .mainloop()-yet tasked to bear in mind
an explicitly specified timing .after( thisAmountOfMILLISECONDS, callThisFUNCTION ) or a liberal .after_idle( callAlwaysThatFUNCTION ).

One does not need indeed anything more to go and solve your task, using these already perfect tools.

So all the rest is just in principle under your creativity how to re-use these smart tools.

A small demo,
how to make two ( 3 ! ) things happen "at the same time independently"

Let's setup the case, when one wants to process ( here demonstrated by a printing ) several independent processes, all at the same time.

    >>> #-----------------------------------------------FAST MOCK-UP EXAMPLE
    >>> import Tkinter as tk                          # python27
    >>> root = tk.Tk()
    >>> root.protocol( "WM_DELETE_WINDOW", root.quit() )
    '3071841620Ldestroy'
    >>> #------VAR-------------------------------------IMPORTANT TOOL:
    >>> aStringVAR = tk.StringVar()
    >>> aStringVAR.set( "_init_" )

    >>> def aKeyPressEventHANDLER( anEvent ): # SIMPLE EventHANDLER,
            #                                 #        also ignites remote responsive processes
    ...     aTemplate = "[KEY]::{3: >10s}n<s/n>::{0: >10d}n(=@=)::{1: > 10d}n^from::({5:})"
    ...     sString   = aTemplate.format( anEvent.serial,
    ...                                   anEvent.time,
    ...                                   anEvent.char,
    ...                                   anEvent.keysym,
    ...                                   anEvent.keysym_num,
    ...                               str(anEvent.widget )
    ...                               )
    ...     aStringVAR.set( sString )
    ...     print sString
    ... 
    >>> #----VAR_TRACER----------------------------------------[#1]
    >>> def aVAR_TRACER_A( p1_quasiNAME, p2_indexOrEmptyString, p3_accessMODE ):
    ...     print "aVAR_TRACER_A()-called::(on){0:} traced_event({1:})".format( str( p1_quasiNAME ), str( p3_accessMODE ) )
    ...     # ###############=[A]#######
    ...     # < do some task =[A] here >
    ...     # ###############=[A]#######
    ...     print "aVAR_TRACER_A()         [{0:}]".format(   str( root.globalgetvar( p1_quasiNAME ) ).replace( " ", "" ) )
    ...

    >>> #----VAR_TRACER----------------------------------------[#2]
    >>> def aVAR_TRACER_B( p1_quasiNAME, p2_indexOrEmptyString, p3_accessMODE ):
    ...     print "aVAR_TRACER_B()-called::(on){0:} traced_event({1:})".format( str( p1_quasiNAME ), str( p3_accessMODE ) )
    ...     # ###############=[B]#######
    ...     # < do some task =[B] here >
    ...     # ###############=[B]######
    ...     print "aVAR_TRACER_B()         [{0:}]".format(   str( root.globalgetvar( p1_quasiNAME ) ).replace( " ", "" ) )
    ... 

    >>> #-----VAR_A_tracer_ID------------------------------"w" EVENT SNIFFER
    >>> aTraceVAR_A_tracer_ID = aStringVAR.trace_variable( "w", aVAR_TRACER_A )

    >>> #-----VAR_B_tracer_ID------------------------------"w" EVENT SNIFFER
    >>> aTraceVAR_B_tracer_ID = aStringVAR.trace_variable( "w", aVAR_TRACER_B )

    >>> #-----------tracer_ID values for ev. theirs resp. de-activation:
    >>> aTraceVAR_A_tracer_ID
    '3071960124LaVAR_TRACER_A'
    >>> aTraceVAR_B_tracer_ID
    '3071961284LaVAR_TRACER_B'

    >>> #---.bind()-----------------------EventHANDLER with a system event <KeyPress>
    >>> root.bind( "<KeyPress>", aKeyPressEventHANDLER ) # <-since here LIVE (!)
    '3071841740LaKeyPressEventHANDLER'
    >>> #------------------------------------------------^^^ since here, it went live
    >>> #                                                 1: having put a mouse on tk-window,
    >>> #                                                 2: set-focus by click
    >>> #                                                 3: started keys:
    >>> #                                                    ( "a",
    >>> #                                                       6-on-<NumKeyPad>,
    >>> #                                                       *-on-<NumKeyPad>

    >>> # this happened "independently, at the same time" ( see time (=@=):: values )
    >>> 

    aVAR_TRACER_B()-called::(on)PY_VAR0 traced_event(w)
    aVAR_TRACER_B()         [[KEY]::a<s/n>::832(=@=)::88486992^from::(.)]
    aVAR_TRACER_A()-called::(on)PY_VAR0 traced_event(w)
    aVAR_TRACER_A()         [[KEY]::a<s/n>::832(=@=)::88486992^from::(.)]
    [KEY]::         a
    <s/n>::       832
    (=@=)::  88486992
    ^from::(.)
    aVAR_TRACER_B()-called::(on)PY_VAR0 traced_event(w)
    aVAR_TRACER_B()         [[KEY]::KP_6<s/n>::832(=@=)::88509107^from::(.)]
    aVAR_TRACER_A()-called::(on)PY_VAR0 traced_event(w)
    aVAR_TRACER_A()         [[KEY]::KP_6<s/n>::832(=@=)::88509107^from::(.)]
    [KEY]::      KP_6
    <s/n>::       832
    (=@=)::  88509107
    ^from::(.)
    aVAR_TRACER_B()-called::(on)PY_VAR0 traced_event(w)
    aVAR_TRACER_B()         [[KEY]::KP_Multiply<s/n>::832(=@=)::88541180^from::(.)]
    aVAR_TRACER_A()-called::(on)PY_VAR0 traced_event(w)
    aVAR_TRACER_A()         [[KEY]::KP_Multiply<s/n>::832(=@=)::88541180^from::(.)]
    [KEY]::KP_Multiply
    <s/n>::       832
    (=@=)::  88541180
    ^from::(.)

Cannot figure out how to run a mysqli_multi_query and use the results from the last query

Answers

A small demo,
how to make two ( 3 ! ) things happen "at the same time independently"

php,mysql,mysqli,mysqli-multi-query

Cannot figure out how to run a mysqli_multi_query and use the results from the last query

Answers

A small demo,how to make two ( 3 ! ) things happen "at the same time independently"

php,mysql,mysqli,mysqli-multi-query

A small demo,
how to make two ( 3 ! ) things happen "at the same time independently"