Asked  7 Months ago    Answers:  5   Viewed   35 times

I am trying to use mySQLi for the first time. I have done it in case of loop. Loop results are showing but i am stuck when i try to show single record. Here is loop code that is working.

<?php
// Connect To DB
$hostname="localhost";
$database="mydbname";
$username="root";
$password="";

@$conn = mysqli_connect($hostname, $username, $password)
        or die("Could not connect to server " . mysql_error()); 
    mysqli_select_db($conn, $database)
        or die("Error: Could not connect to the database: " . mysql_error());

    /*Check for Connection*/
    if(mysqli_connect_errno()){
        // Display Error message if fails
        echo 'Error, could not connect to the database please try again again.';
    exit();
    }
?>

<?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 records */
for($i=0; $i<$num_results; $i++) {
$row = mysqli_fetch_assoc($result);
?>

<?php // echo 'Name' .$row['ssfullname'] . 'email' . $row['ssemail'] . "n"; ?>

Name: <?php print $row['ssfullname']; ?>
<br />
Email: <?php print $row['ssemail']; ?>
<br /><br />

<?php 
// end loop
} 
?>

Above code is fine in case of loop. Now how do i show single record, any record, name or email, from first row or whatever, just single record, how would i do that? In single record case, consider all above loop part removed and lets show any single record without loop.

 Answers

26

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 ud=?";
$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).

Wednesday, March 31, 2021
 
jedwards
answered 7 Months ago
34

Normally, I wouldn't advocate having database queries inside loops; it is inefficient and can cause excessive load on your database server.

That said, I don't see much alternative unless you can refactor your data model. What you have is a parent/child relationship within a single table ("superseded_sku" references a child record of the same table). This is bad for what you are trying to do as you never know beforehand if the child record has any children of it's own.

Assuming refactoring is not feasible, you want to run the query, check if there are matching records, update the query and repeat until there are no more records.

Try something like this...

// get initial result set (don't forget to escape your variables)
$query = "SELECT superseded_sku FROM mar15 WHERE sku = '" .
        mysqli_real_escape_string($mysqli, $ss1) . "'";
$result = mysqli_query($mysqli, $query);

// loop while we have results
while(mysqli_num_rows($result) > 0) {
    $row = mysqli_fetch_array($result);

    // double check the row is valid
    if (empty($row["superseded_sku"])) {
        break;
    }
    $ss1 = $row["superseded_sku"];

    // run the query again to check for further superseded skus
    $query = "SELECT superseded_sku FROM mar15 WHERE sku = '" .
        mysqli_real_escape_string($mysqli, $ss1) . "'";
    $result = mysqli_query($mysqli, $query);
}

I would seriously advise against using this approach as a long term measure, as it does not scale well at all. You will almost certainly encounter server resource issues or timeouts, especially if your product list continues growing. You will need to give some thought to refactoring the data if this is something you plan to use in the future.

Wednesday, March 31, 2021
 
exxed
answered 7 Months ago
91

This question and thus my answer may be close to the gray line of subjective, but at the least I think it would be common practice to separate out the 'admin' tables into their own db for what it sounds like you're doing. If you can tie a client to a specific server and db instance then by having separate db instances, it opens up some easy paths for adding servers to add clients. A single db would require you to monkey with various clustering approaches if you got too big.

[edit]Building in the idea early that each client gets it's own DB also just sets the tone for how you develop when it is easy to make structural and organizational changes. Discovering 2 yrs from now you need to do it will become a lot more painful. I've worked with split dbs plenty of times in the past and it really isn't hard to deal with as long as you can establish some idea of what the context is. Here it sounds like you already have the idea that the client is the context.

Just my two cents, like I said, you could be close to subjective on this one.

Monday, July 5, 2021
 
hjalpmig
answered 4 Months ago
41

Whatever is given in the SELECT statement to mysqli_query is going to return a mysql_result type if the query was successful. So if you have a SELECT statement such as:

SELECT sum(field) FROM table1

you still need to fetch the row with the result, and the value of the sum() function will be the only entry in the row array:

$res = mysqli_query($dbh,'SELECT sum(field) FROM table1');
$row = mysqli_fetch_row($res);
$sum = $row[0];
Thursday, July 8, 2021
 
Packy
answered 4 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share