The usual process for me to retrive a result is:

$result = $this->database->query("SELECT `name` FROM `table` WHERE id='1'")
$row = $result->fetch_array(MYSQLI_ASSOC);
echo $row['name'];

Is there an easy way to return a single result from a table without having to create an associated array.

for instance

$result = $this->database->query("SELECT `name` FROM `table` WHERE id='1'")
echo $result->fetch('name');

I know it seems like a remedial thing to be bothering about but im always having to write the same thing over and over again and OO is supposed to help reduce the amount of repetition.

I will eventually get round to building a decent database layer to manage queries along the lines of code igniter but until then this would just be useful.

thanks in advance.

    Not with MySQLi, if I remember correctly. Of course, you can always prepare the statement then bind a variable to it, but that could be even more work (in terms of PHP code) than retrieving the result set in an array.

      When would you use prepare/binding etc... and the mysqli stmt class?

      PHP.net is very informative about what each function does but I am yet to find a decent resource to break down when to use certain functions especially within the database and results management.

      Do you have any resources that you can recommend (books or links)?

        One option that you have is extending mysqli into your own class and then add methods in there to do what you want. Here is an example without any real error checking (untested):

        <?php

class MyDbClass extends mysqli
{

/**
 * Initializes the class.  Passes given parameters to mysqli
 *
 * @return void
 */
public function __construct()
{
    // Get all of the arguments that were passed
    $args = func_get_args();
    // Clean up the arguments
    $cleaned_args = join(',', array_map('addSingleQuotes', $args));
    // Pass all arguments to the parent's constructor
    parent::init();
    eval("parent::__construct(" . $cleaned_args . ");");
}

/**
 * Runs given query and returns the first field of the first row returned.
 * Ignores all other data returned by the query.
 *
 * @param string $query_string A valid MySQL query string.
 *
 * @return string
 */
function queryToSingleField($query_string) {
    $result = $this->query($query_string);
    $row    = $result->fetch_array(MYSQLI_NUM);
    return $row[0];
}
}

/**
 * Escapes Single Quotes and Backslashes from the given string
 * 
 * @param string $arg The string that we want to clean up.
 * 
 * @return string
 */
function addSingleQuotes($arg)
{
    // single quote and escape single quotes and backslashes
    return "'" . addcslashes($arg, "'\\") . "'";
}

?>

        Usage:

        <?php
/**
 * Usage
 */
$db_conn = new MyDbClass("localhost", "my_user", "my_password", "my_db");

$name = $db_conn->queryToSingleField("SELECT `name` FROM `table` WHERE id='1'");

?>

        Here is the page that explains this better. http://devzone.zend.com/node/view/id/687

        For an example of what laserlight was talking about take a look here: mysqli_stmt::fetch

          Write a Reply...