Tempat Mencatat

Question

I would like to see an example of how to call using bind_result vs. get_result and what would be the purpose of using one over the other.

Also the pro and cons of using each.

What is the limitation of using either and is there a difference.

score 99 · Accepted Answer

The deciding factor for me, is whether I call my query columns using *.

Using `bind_result()` would be better for this:

// Use bind_result() with fetch()
$query1 = 'SELECT id, first_name, last_name, username FROM table WHERE id = ?';

Using `get_result()` would be better for this:

// Use get_result() with fetch_assoc() 
$query2 = 'SELECT * FROM table WHERE id = ?';

Example 1 for `$query1` using `bind_result()`

$query1 = 'SELECT id, first_name, last_name, username FROM table WHERE id = ?';
$id = 5;

if($stmt = $mysqli->prepare($query)){
   /*
        Binds variables to prepared statement

        i    corresponding variable has type integer
        d    corresponding variable has type double
        s    corresponding variable has type string
        b    corresponding variable is a blob and will be sent in packets
   */
   $stmt->bind_param('i',$id);

   /* execute query */
   $stmt->execute();

   /* Store the result (to get properties) */
   $stmt->store_result();

   /* Get the number of rows */
   $num_of_rows = $stmt->num_rows;

   /* Bind the result to variables */
   $stmt->bind_result($id, $first_name, $last_name, $username);

   while ($stmt->fetch()) {
        echo 'ID: '.$id.'<br>';
        echo 'First Name: '.$first_name.'<br>';
        echo 'Last Name: '.$last_name.'<br>';
        echo 'Username: '.$username.'<br><br>';
   }

   /* free results */
   $stmt->free_result();

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

Example 2 for `$query2` using `get_result()`

$query2 = 'SELECT * FROM table WHERE id = ?'; 
$id = 5;

if($stmt = $mysqli->prepare($query)){
   /*
        Binds variables to prepared statement

        i    corresponding variable has type integer
        d    corresponding variable has type double
        s    corresponding variable has type string
        b    corresponding variable is a blob and will be sent in packets
   */
   $stmt->bind_param('i',$id);

   /* execute query */
   $stmt->execute();

   /* Get the result */
   $result = $stmt->get_result();

   /* Get the number of rows */
   $num_of_rows = $result->num_rows;



   while ($row = $result->fetch_assoc()) {
        echo 'ID: '.$row['id'].'<br>';
        echo 'First Name: '.$row['first_name'].'<br>';
        echo 'Last Name: '.$row['last_name'].'<br>';
        echo 'Username: '.$row['username'].'<br><br>';
   }

   /* free results */
   $stmt->free_result();

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

As you can see you can't use bind_result with *. However, get_result works for both, but bind_result is simpler and takes out some of the mess with $row['name'].

bind_result()

Pros:

Simpler
No need to mess with $row['name']
Uses fetch()

Cons:

Doesn't work with SQL query that use *

get_result()

Pros:

Works with all SQL statements
Uses fetch_assoc()

Cons:

Must mess around with array variables $row[]
Not as neat
requires MySQL native driver (mysqlnd)

Hugh Lee · Accepted Answer · 2016-03-03 07:33:43Z

Do not delete, use truncate:

Truncate table XXX

The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

Source.

Truncate works well with non-constrained tables, but if your table has a Foreign Key constraint, you may consider using the Delete method. See this post if you have FK constraints: truncate foreign key constrained table — Julian Soro, May 16 '14 at 23:41

Tempat Mencatat

Monday, September 13, 2021

Cara Export dan Import MySQL Melalui Command Line

Tahap Persiapan

Cara Export MySQL

Cara Import MySQL

Kesimpulan

Tuesday, February 21, 2017

Example of how to use bind_result vs get_result

5 Answers

Using `bind_result()` would be better for this:

Using `get_result()` would be better for this:

Example 1 for `$query1` using `bind_result()`

Example 2 for `$query2` using `get_result()`

bind_result()

get_result()

Sunday, January 15, 2017

Accessing MYSQL through terminal

Thursday, November 17, 2016

MySQL Delete all rows from table and reset ID to zero

4 Answers

Monday, September 13, 2021

Cara Export dan Import MySQL Melalui Command Line

Tahap Persiapan

Cara Export MySQL

Cara Import MySQL

Kesimpulan

Tuesday, February 21, 2017

Example of how to use bind_result vs get_result

5 Answers

Using bind_result() would be better for this:

Using get_result() would be better for this:

Example 1 for $query1 using bind_result()

Example 2 for $query2 using get_result()

bind_result()

get_result()

Sunday, January 15, 2017

Accessing MYSQL through terminal

Thursday, November 17, 2016

MySQL Delete all rows from table and reset ID to zero

4 Answers

Using `bind_result()` would be better for this:

Using `get_result()` would be better for this:

Example 1 for `$query1` using `bind_result()`

Example 2 for `$query2` using `get_result()`