Menu

PHP and mysqli_* functions

It has long been known that PHP refused mysql_* functions. And someone even knows that such existed. But not the essence of it.

The idea of this post is the mysqli_* functions have a very harmful feature. And it appears when you start to make queries on the sample not by simple queries, and in function. Once it so happened that I work with a nicer and more convenient through the procedures and views than direct requests.

Let's turn to the example:


When we do:

mysqli_real_query($link, "SELECT * FROM `table1`;");

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

The result is:

mysqli_field_count = 2

mysqli_more_results =

Now, we try to create procedure like this:

create procedure `tmp1`()

READS SQL DATA

BEGIN

  SELECT * FROM `table1`;

END;

And repeat, with early created procedure:

mysqli_real_query($link, "call tmp1();");

Result:

mysqli_field_count = 2

mysqli_more_results = 1

So, we obtain two sets of results. And the second set is empty (a more complete example below).

In fact - it's all. :) And for this shit, I had to kill a couple of hours. :( Write queries - everything works. Change simple queries to procedure - fail.

And now, as promised, a complete step by step example:

<?php

$link = mysqli_init();

mysqli_options($link, MYSQLI_INIT_COMMAND, "set autocommit=0");

mysqli_options($link, MYSQLI_INIT_COMMAND, "set names 'utf8'");

mysqli_options($link, MYSQLI_INIT_COMMAND, "set charset 'utf8'");

mysqli_options($link, MYSQLI_OPT_CONNECT_TIMEOUT, 10);

mysqli_real_connect($link, "localhost", "root", "password", "test");

if(mysqli_connect_errno()) {

    echo "Connect failed: ".mysqli_connect_error();

    exit();

}

$sql = "SELECT * FROM `table1`;";

echo "query: ".$sql."\n";

if(!mysqli_real_query($link, $sql)) {

    echo "SQL: ".$sql."\nError: ".mysqli_error($link);

    exit();

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = " . mysqli_next_result($link) . "\n";

    echo "mysqli_field_count = " . mysqli_field_count($link) . "\n";

    echo "mysqli_more_results = " . mysqli_more_results($link) . "\n";

}

$result = mysqli_store_result($link);

if($result) {

    echo "read row 1: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    echo "read row 2: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    echo "read row 3: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    echo "read row 4: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    mysqli_free_result($result);

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = ".mysqli_next_result($link)."\n";

    echo "mysqli_field_count = " . mysqli_field_count($link) . "\n";

    echo "mysqli_more_results = " . mysqli_more_results($link) . "\n";

}

$sql = "drop procedure if exists `tmp1`;";

echo "query: ".$sql."\n";

if(!mysqli_real_query($link, $sql)) {

    echo "SQL: ".$sql."\nError: ".mysqli_error($link);

    exit();

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = ".mysqli_next_result($link)."\n";

    echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

    echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

}

$result = mysqli_store_result($link);

if($result) {

    echo "read row: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    mysqli_free_result($result);

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = " . mysqli_next_result($link) . "\n";

    echo "mysqli_field_count = " . mysqli_field_count($link) . "\n";

    echo "mysqli_more_results = " . mysqli_more_results($link) . "\n";

}

$sql = "create procedure `tmp1`() READS SQL DATA BEGIN SELECT * FROM `table1`; END;";

echo "query: ".$sql."\n";

if(!mysqli_multi_query($link, $sql)) {

    echo "SQL: ".$sql."\nError: ".mysqli_error($link);

    exit();

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = ".mysqli_next_result($link)."\n";

    echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

    echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

}

$result = mysqli_store_result($link);

if($result) {

    echo "read row: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    mysqli_free_result($result);

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = " . mysqli_next_result($link) . "\n";

    echo "mysqli_field_count = " . mysqli_field_count($link) . "\n";

    echo "mysqli_more_results = " . mysqli_more_results($link) . "\n";

}

$sql = "call `tmp1`();";

echo "query: ".$sql."\n";

if(!mysqli_real_query($link, $sql)) {

    echo "SQL: ".$sql."\nError: ".mysqli_error($link);

    exit();

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = " . mysqli_next_result($link) . "\n";

    echo "mysqli_field_count = " . mysqli_field_count($link) . "\n";

    echo "mysqli_more_results = " . mysqli_more_results($link) . "\n";

}

$result = mysqli_store_result($link);

if($result) {

    echo "read row: " . print_r(mysqli_fetch_assoc($result), true) . "\n";

    mysqli_free_result($result);

}

echo "mysqli_field_count = ".mysqli_field_count($link)."\n";

echo "mysqli_more_results = ".mysqli_more_results($link)."\n";

if(mysqli_more_results($link)) {

    echo "mysqli_next_result = ".mysqli_next_result($link)."\n";

    echo "mysqli_field_count = " . mysqli_field_count($link) . "\n";

    echo "mysqli_more_results = " . mysqli_more_results($link) . "\n";

}

Result is:

query: SELECT * FROM `table1`;

mysqli_field_count = 2

mysqli_more_results = 

read row 1: Array

(

    [id] => 1

    [text1] => asdfsadf

)


read row 2: Array

(

    [id] => 2

    [text1] => ewrqwre

)


read row 3: Array

(

    [id] => 3

    [text1] => sadfsadf

)


read row 4: Array

(

    [id] => 4

    [text1] => zxcvzxvc

)


mysqli_field_count = 2

mysqli_more_results = 

query: drop procedure if exists `tmp1`;

mysqli_field_count = 0

mysqli_more_results = 

mysqli_field_count = 0

mysqli_more_results = 

query: create procedure `tmp1`() READS SQL DATA BEGIN SELECT * FROM `table1`; END;

mysqli_field_count = 0

mysqli_more_results = 

mysqli_field_count = 0

mysqli_more_results = 

query: call `tmp1`();

mysqli_field_count = 2

mysqli_more_results = 1

mysqli_next_result = 

mysqli_field_count = 2

mysqli_more_results = 1

read row: Array

(

    [id] => 1

    [text1] => asdfsadf

)


mysqli_field_count = 2

mysqli_more_results = 1

mysqli_next_result = 1

mysqli_field_count = 0

mysqli_more_results = 

P.s.: When receiving the result after mysql_store_result, you can make several requests in sequence and in parallel to process the results can not be said about mysqli_use_result.

P.p.s.: Why this post needed? As shown, to find the specificity by using google, it is not so simple. It is mentioned much less frequently than trivial examples, such as make a selection through mysqli_* functions.

Tags:
php, mysql, mysqli, functions, routines

Keep your comment...