2

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.

levidhuyvetter
  • 401
  • 5
  • 16
  • 1
    If `INSERT` issues an error, I would not expect it to return an (even empty) result set. Instead if it goes through, the database might return an empty result set. This would likely explain your problem, but since I barely understand what you want to achieve... have you taken a look at this: http://stackoverflow.com/questions/7395326/how-do-i-ensure-i-caught-all-errors-from-mysqlimulti-query ? It seems to me that execution of multi-queries ends as soon as an error arises. – Ulrich Thomas Gabor Feb 26 '14 at 23:28
  • 1
    Go for a RESTful communication with json, it will be much easier to manipulate data and much lighter/faster to send & receive data – Kalzem Feb 26 '14 at 23:39
  • I never used JSON before, I might have a look into it and see how I can use it in my project. Currently I'm parsing my string with [string componentsSeparatedByString:@"|*|"]. Seems like a lot of work though, changing everything to JSON. – levidhuyvetter Feb 26 '14 at 23:44

2 Answers2

1

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.

Tommy Logik
  • 93
  • 1
  • 7
1

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

Alon Eitan
  • 11,997
  • 8
  • 49
  • 58