CODEWITHSUNDEEP
phpmysql
user3112721
user3112721

Reputation: 15

Counting the number of columns in a mysql table not working

I have been looking on the forum for a means by which to count the number of columns in mysql table.

I had thought I had found the answer however it doesn't seem to be working. A snippet of my query is below:

$count = queryMysql("SELECT count(*) FROM information_schema.columns WHERE table_schema='" . $dbname . "' AND table_name='" . $table . "'");

$dname contains the name of my database and $table is passed into my function as an argument and is the name of the table.

when I echo $count it simple prints Resource id#5

Upvotes: 1

Views: 1499

Answers (4)

r3wt
r3wt

Reputation: 4742

try this:

    $count = mysql_query("SELECT count(*) FROM information_schema.columns WHERE table_schema='" . $dbname . "' AND table_name='" . $table . "'");
    $nums = mysql_num_rows($count);
    for($i=0; $i < $nums; $i++;){
    $result = mysql_result($count,$i,"count");
    echo $table." : ".$result;
    }

Upvotes: 0

Gil Sousa
Gil Sousa

Reputation: 844

After executing the query you need to fetch the data returned from it. Please have a look at this example:

        $query = "SELECT count(*) FROM information_schema.columns WHERE table_schema='" . $dbname . "' AND table_name='" . $table . "'";
        $res = mysql_query($query);
        $row = mysql_fetch_assoc($res);

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

Assuming you have a custom function queryMysql() which doesn't handle the output of the count right, alter it to:

$count = queryMysql("SELECT count(*) AS columnCount 
    FROM information_schema.columns 
    WHERE table_schema='" . $dbname . "' 
    AND table_name='" . $table . "'");

Note how I select it as columnCount so it is named properly.

Upvotes: 0

Marc B
Marc B

Reputation: 360702

Standard mysql libraries' query() functions return a result HANDLE, even if the query would only ever return a single field and/or row. You need to FETCH a row from that handle, e.g.

$result = mysqli_query("SELECT ...");
$row = mysqli_fetch_assoc($result);
echo $row['count'];

In this code, if you did echo $row, you'd get your output of Resource id#xxx because it's NOT the value that was selected - it's the result handle of what you can FETCH that value from.

Upvotes: 2

Related Questions