Display Single Column Value of MySQLi Query

How to get single column values using MySQLi?

do fetch_assoc

while($row = $result->fetch_assoc()) {
  $rows[]=$row['EmailAddress'];
}

How do I retrieve a single column from mysqli results

For starters, you should be using prepared statements to avoid SQL injection. After that, you can use either mysqli::bind_result OR mysqli_stmt::get_result and mysqli_result::fetch_array. I'll demonstrate both methods here.

It is important to take these practices on board, you are using mysqli instead of mysql, so it makes sense to avail yourself of the enhanced features this library brings to bear.

First, preparing a statement:

// initiate database connection
$db = mysqli_connect(
    "localhost",
     "user",
     "password",
     "database"
);

// set up your statement with ? parameters
$statment = $db->prepare('
    SELECT 
        t.tag_name 
    FROM 
        tag_map tm 
    JOIN 
        article p ON
            p.post_id = tm.post_id 
    JOIN 
        tag t ON 
            t.tag_id = tm.tag_id 
    WHERE
        p.post_id = ?
');

// bind values for each parameter
$statement->bind_param('i', $post_id);

// execute the statement
$statement->execute();

The code above will safely prepare the query and fire it at the database, you've got some results to work with now. As mentioned, you can use two different methods. First is bind_result, as follows:

// list the columns you want to get from each row as variables
$statement->bind_result($tagName);

// then loop the results and output the columns you bound above
while($statement->fetch()){
    echo $tagName.'<br>';
}

The second method is to use get_result, then loop those results and use fetch_array to grab an associative array, from which you can get the column you're after, as follows:

$result = $statement->get_result();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    echo $row['tag_name'].'<br>';
}

... in either case, use free_result when you're done.

$statement->free_result();

Documentation

• mysqli::prepare - http://www.php.net/manual/en/mysqli.prepare.php
• mysqli_stmt::get_result - http://www.php.net/manual/en/mysqli-stmt.get-result.php
• mysqli_stmt::bind_param - http://www.php.net/manual/en/mysqli-stmt.bind-param.php
• mysqli_stmt::bind_execute - http://www.php.net/manual/en/mysqli-stmt.execute.php
• mysqli_stmt::bind_result - http://www.php.net/manual/en/mysqli-stmt.bind-result.php
• mysqli_stmt::fetch - http://www.php.net/manual/en/mysqli-stmt.fetch.php
• mysqli_result::fetch_array - http://www.php.net/manual/en/mysqli-result.fetch-array.php

How to get a single value from a query result in php

Your variable $table is an array, so if you want only the userid, you just have to use

$table[0]['userid']

If you want to query only the userid change you query like this :

$query = mysqli_query( $connection,"select userid from register where password='$log_in_password' AND username='$log_in_username'");
$table = mysqli_fetch_all($query,MYSQLI_ASSOC);

And the result of $table will be :

array (size=1)
  0 => 
    array (size=1)
      'userid' => string '7' (length=1)

$table will also be an array, and to acces userid you have to use again

$table[0]['userid']

Single result from database using mysqli

When just a single result is needed, then no loop should be used. Just fetch the row right away.

• In case you need to fetch the entire row into associative array:

    $row = $result->fetch_assoc();
  

• in case you need just a single value

    $row = $result->fetch_row();
    $value = $row[0] ?? false;
  

The last example will return the first column from the first returned row, or false if no row was returned. It can be also shortened to a single line,

$value = $result->fetch_row()[0] ?? false;

Below are complete examples for different use cases

Variables to be used in the query

When variables are to be used in the query, then a prepared statement must be used. For example, given we have a variable $id:

$query = "SELECT ssfullname, ssemail FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

// in case you need just a single value
$query = "SELECT count(*) FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$value = $result->fetch_row()[0] ?? false;

The detailed explanation of the above process can be found in my article. As to why you must follow it is explained in this famous question

No variables in the query

In your case, where no variables to be used in the query, you can use the query() method:

$query = "SELECT ssfullname, ssemail FROM userss ORDER BY ssid";
$result = $conn->query($query);
// in case you need an array
$row = $result->fetch_assoc();
// OR in case you need just a single value
$value = $result->fetch_row()[0] ?? false;

By the way, although using raw API while learning is okay, consider using some database abstraction library or at least a helper function in the future:

// using a helper function
$sql = "SELECT email FROM users WHERE id=?";
$value = prepared_select($conn, $sql, [$id])->fetch_row[0] ?? false;

// using a database helper class
$email = $db->getCol("SELECT email FROM users WHERE id=?", [$id]);

As you can see, although a helper function can reduce the amount of code, a class' method could encapsulate all the repetitive code inside, making you to write only meaningful parts - the query, the input parameters and the desired result format (in the form of the method's name).

PHP and MySQL Select a Single Value

1. Don't use quotation in a field name or table name inside the query.

2. After fetching an object you need to access object attributes/properties (in your case id) by attributes/properties name.

One note: please use mysqli_* or PDO since mysql_* deprecated. Here it is using mysqli:

session_start();
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli('localhost', 'username', 'password', 'db_name');
$link->set_charset('utf8mb4'); // always set the charset
$name = $_GET["username"];
$stmt = $link->prepare("SELECT id FROM Users WHERE username=? limit 1");
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();
$value = $result->fetch_object();
$_SESSION['myid'] = $value->id;

Bonus tips: Use limit 1 for this type of scenario, it will save execution time :)

display multiple value of same column in mysqli php

I think this is what you need.

<div class="list-group">
    <h3>Name</h3>
<?php 
$column = array();
$query = "select name from info_user where user_status = '1'";  
$rs = mysqli_query($con,$query);
while ($color_data = mysqli_fetch_assoc($rs)) {
    $column = array_merge($column, explode(',', $color_data['name']));
}
$column = array_unique($column);
foreach ($column as $value) {
?>
        <a href="javascript:void(0);" class="list-group-item"> 
        <input type="checkbox" class="item_filter colour" value="<?php echo $value; ?>" >
          <?php echo $value; ?>
        </a>
<?php } ?>  
</div> 

---

Related Topics