MySQL/PHP multi query: 3 statements, 2 results

I'm writing a php script that communicates with a MySQL database and formats the results as a string that can be parsed by an Objective-C Framework I'm building.

Currently I'm trying to get multiple statements/results to work, but I'm having some trouble here.

I'm sending 3 statements as a multi-statement:

  • SELECT * FROM table (returns a resultset)
  • SELECT field FROM table (returns a resultset)
  • INSERT INTO table VALUES value (should return a duplicate error)

The do while loop gets called twice, once for each one of the first 2 statements, however it doesn't get called a third time for the INSERT statement.

if ($mysql->more_results()) {
    echo("MULTIRESULT|*|".mysqli_errno($mysql)."|*|".mysqli_error($mysql)."|*|");
    $i4 = 0;
    do {
        if ($i4 != 0) {
            echo("|&|");
        }

        $result = $mysql->store_result();

        if ($result) {
            echo("RESULTSET|%|");
            $i1 = 0;
            while ($field = $result->fetch_field()) {
                if ($i1 != 0) {
                    echo("|+|");
                } echo($field->name."|@|".$field->orgname."|@|".$field->table."|@|".$field->orgtable."|@|".$field->db."|@|".$field->catalog."|@|".$field->def."|@|".$field->length."|@|".$field->maxlength."|@|".$field->flags."|@|".$field->decimals."|@|".$field->charsetnr."|@|".$field->type);
                $i1++;
            }

            echo("|~|");

            $i2 = 0;
            while ($row = $result->fetch_row()) {
                if ($i2 != 0) {
                    echo("|+|");
                }

                $fieldcount = $mysql->field_count;
                for ($i3 = 0; $i3 < $fieldcount; $i3++) {
                    if ($i3 != 0) {
                        echo("|@|");
                    } echo($row[$i3]);
                }

                $i2++;
            }
        } else if ($mysql->field_count == 0) {
            echo("AFFECTEDROWS|%|".$mysql->affected_rows);
        } else {
            echo("UNSUCCESSFUL|%|".mysqli_errno($mysql)."|%|".mysqli_error($mysql));
        } $result->close();
        $i4++;
    } while ($mysql->next_result());
}

For the piece of code that handles a single statement this works fine. There is no result, the mysqli_field_count is not 0, so it echoes the line starting with UNSUCCESSFUL.

$result = $mysql->store_result();
if ($result) {
    echo("RESULTSET|*|".mysqli_errno($mysql)."|*|".mysqli_error($mysql)."|*|");

    $i1 = 0;
    while ($field = $result->fetch_field()) {
        if ($i1 != 0) {
            echo("|+|");
        } echo($field->name."|@|".$field->orgname."|@|".$field->table."|@|".$field->orgtable."|@|".$field->db."|@|".$field->catalog."|@|".$field->def."|@|".$field->length."|@|".$field->maxlength."|@|".$field->flags."|@|".$field->decimals."|@|".$field->charsetnr."|@|".$field->type);
        $i1++;
    }

    echo("|~|");

    $i2 = 0;
    while ($row = $result->fetch_row()) {
        if ($i2 != 0) {
            echo("|+|");
        }

        $fieldcount = $mysql->field_count;
        for ($i3 = 0; $i3 < $fieldcount; $i3++) {
            if ($i3 != 0) {
                echo("|@|");
            } echo($row[$i3]);
        }

        $i2++;
    }
} elseif ($mysql->field_count == 0) {
    echo("AFFECTEDROWS|*|".mysqli_errno($mysql)."|*|".mysqli_error($mysql)."|*|".$mysql->affected_rows);
} else {
    echo("UNSUCCESSFUL|*|".mysqli_errno($mysql)."|*|".mysqli_error($mysql));
} $result->close();

I would say it is because mysqli_next_result only works in case there is a resultset, but when I do an INSERT statement when there's no duplicate yet, the do while loop does get called for that statement.

I'm so in the dark about this issue that I don't even know how to formulate my question, but I guess the question here is pretty obvious.

Answers


If you are looking to have PHP,mySQL and Objective C communicate, I would definitely recommend changing this method to a simplier one.

Sending and receiving JSON is the best method, in my opinion, to handle communication between PHP and Objective C. What you have written can be simplifed when you receive results from mysql.

For example:

If you are looking to send the whole SELECT statement to Objective C, I would use fetch_object() instead of fetch_field() and then echo json_encode() to output a format that would have everything Objective C needs to decode using

[NSJSONSerialization JSONObjectWithData:data];

Hope that gives you direction to the right question to ask.


You have this line in the code } $result->close(); and from the documentation it seems that $result is a mysqli_result object, and that the close() function is not part from this class.

Maybe you need to change that line to } $result->free();?

I hope it will help


Need Your Help

JQuery fadeTo()

javascript jquery html css

I got this code so far:

Animations when showing sheet (Cocoa)

objective-c cocoa appkit

I had a question when showing sheets in Cocoa and particularly, the animations. How do I show a sheet (plain NSWindow/NSPanel) with and without the nice animation? It does this automatically for

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.