search

Home  >  Q&A  >  body text

Get a single result from database using mysqli

<p>This is my first time trying to use mySQLi. I've done this in the case of a loop. The results of the loop show up, but when I try to show a single record it gets stuck. Below is the loop code, it works. </p> <pre class="brush:php;toolbar:false;"><?php // Connect to database $hostname="localhost"; $database="mydbname"; $username="root"; $password=""; $conn = mysqli_connect($hostname, $username, $password, $database); ?> <?php $query = "SELECT ssfullname, ssemail FROM userss ORDER BY ssid"; $result = mysqli_query($conn, $query); $num_results = mysqli_num_rows($result); ?> <?php /* Loop through each row and display the records */ for($i=0; $i<$num_results; $i ) { $row = mysqli_fetch_assoc($result); ?> Name: <?php print $row['ssfullname']; ?> <br /> Email: <?php print $row['ssemail']; ?> <br /><br /> <?php // end loop } ?></pre> <p>How can I display a single record, any record like name or email, from the first row or whatever, just a single record, how can I do this? In case of single record, consider removing the above loop part and let us display any single record without using loop. </p>
P粉156532706P粉156532706462 days ago427

reply all(2)I'll reply

  • P粉930448030

    P粉9304480302023-08-22 21:34:58

    Use mysqli_fetch_row(). Try this:

    $query = "SELECT ssfullname, ssemail FROM userss WHERE user_id = ".$user_id;
    $result = mysqli_query($conn, $query);
    $row   = mysqli_fetch_row($result);
    
    $ssfullname = $row['ssfullname'];
    $ssemail    = $row['ssemail'];

    reply
    0
  • P粉265724930

    P粉2657249302023-08-22 12:11:29

    When only one result is needed, there is no need to use a loop. Get the row directly.

    • If you need to obtain the entire row of data as an associative array:

      $row = $result->fetch_assoc();
    • If only one value is required, starting with PHP 8.2:

      $value = $result->fetch_column();
    • Or for older versions:

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

    Here are complete examples of different use cases

    Variables used for query

    When using variables in a query, prepared statements must be used. For example, let's say we have a variable $id:

    PHP >= 8.2

    // 获取单行数据
    $sql = "SELECT fullname, email FROM users WHERE id=?";
    $row = $conn->execute_query($query, [$id])->fetch_assoc();
    
    // 如果只需要一个值
    $sql = "SELECT count(*) FROM users WHERE id=?";
    $count = $conn->execute_query($query, [$id])->fetch_column();

    Older versions of PHP:

    // 获取单行数据
    $query = "SELECT fullname, email FROM users WHERE id=?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("s", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    
    // 如果只需要一个值
    $query = "SELECT count(*) FROM userss WHERE id=?";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("s", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $count = $result->fetch_row()[0] ?? false;

    A detailed explanation of the above process can be found in my article . The reasons why this process must be followed are explained in this famous Question

    There are no variables in the query

    In your case, there are no variables to use in the query, you can use the query() method:

    $query = "SELECT fullname, email FROM users ORDER BY ssid";
    $result = $conn->query($query);
    // 如果需要一个数组
    $row = $result->fetch_assoc();
    // 或者如果只需要一个值
    $value = $result->fetch_row()[0] ?? false;

    reply
    0
  • Cancelreply