PHP Using mysqli_fetch_assoc to get Database listing

Question

I know there are many many documentations and tutorials on mysqli_fetch_assoc() function which I read but there is one question I can't seem to find the answer to. By the way I am totally new to PHP just started learning it 3 days ago. What I'm trying to do here is.

1) Connect to the server
2) Choose a database
3) List available databases on the connected server

# Connecting to mySQL server on localhost

# creating host and credentials to login to server
$user = "root";
$pass = "pass123";
$host = "localhost";
$DB = "artechdb";
# Create a connection
$sqlconnect = mysqli_connect($host, $user, $pass, $DB);

echo "Connecting to {$host}....";


# Check if connection successful
if (!$sqlconnect) {
    die ("Could not connect to $host");
} else {
    print "<br>Connected Successfully to $host<br><br>"; 
}


print "Lets see what kind of databases we have on our mySQL server.<br>";
$queryString = "SHOW databases";

$result = mysqli_query($sqlconnect, $queryString);
if($result = mysqli_query($sqlconnect, $queryString) ) {
    # fetch associative array
    while($row = mysqli_fetch_assoc($result) ) {
        printf ("%s", $row["Database"] . "<br>");
        }
}

My questions is at the end the printf statement contains $row["Database"] where is the word "Database" coming from? I do not have a database named "Database".

I thought since it is an associative array I could access and/or print out my database names using $row[0] ..$row[1] .. etc which does not work. I am banging my head past several hours trying to figure out where the Database keyboard is coming from and why we use it instead of accessing our array using zero based index. Any help really appreciate.

You're querying mysqli_query twice. Just do if($result){ - Add or die(mysqli_error($sqlconnect)) to mysqli_query() and you'll see what pops out. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 13, 2014 at 4:14

Kevin · Accepted Answer · 2014-11-13 04:29:00Z

2

When you use mysqli_fetch_assoc() you are given an associative array which will contain the rows found, and the keys will be the column names. The numeric indices will not be included.

If you're wondering where that Database came from, its the column name of the result set.

When you directly query it (example, PHPMyadmin or terminal), should look something like this:

+--------------------+
| Database           |   <----- $row['Database']
+--------------------+
| information_schema |
| phpmyadmin         |
| test               |
+--------------------+

So you want to access the numeric indices too?

If you want to access the numeric indices as well $row[0], you need to use mysqli_fetch_array()

So that:

// you can now acces both
// $row[0] / $row['Database']

After that, to the codes:

Remove that superfluous mysqli_query():

$result = mysqli_query($sqlconnect, $queryString);
if($result = mysqli_query($sqlconnect, $queryString) ) { // no need for this

So finally:

$result = mysqli_query($sqlconnect, $queryString);
while($row = mysqli_fetch_array($result)) { // fetch_array()
    printf ("%s", $row["Database"] . "<br>");
    echo $row[0];
    // now you can access both
    // $row[0] / or / $row['Database']
}

edited Nov 13, 2014 at 4:29

answered Nov 13, 2014 at 4:16

Kevin

41.9k12 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Funk Forty Niner Over a year ago

There's something else...

Kevin Over a year ago

@Fred-ii- yeah thanks for the heads up, no need for that extra function

Collectives™ on Stack Overflow

PHP Using mysqli_fetch_assoc to get Database listing

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related