Count number of columns in table

Using first row information

[kiwibrit]

I need to know the number of columns in a table (which equals the number of fields in the first row.

$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
while ($row = mysql_fetch_row($result))
{
foreach ($row as $item)
$counter=++$i;
}
echo $counter;

counts all the fields of all the rows very nicely.

But is there a way of counting the fields of just the first row (invariably the most populated row for all the tables)?

[jezra]

I haven't tried this, but it "should work":

$myQuery = "show columns from $my_table_name";
$result = mysql_query($myQuery);
$row = mysql_fetch_row($result);
$columnCount = count($row);

YMMV

[kiwibrit]

That does it. You are a star. That one had been driving me nuts. Thank you very much.

[coopster]

Just a quick note to add here regarding the differences between the number of columns in a table versus the number of columns returned in a query. They may not always be the same. Take the following two examples:

CREATE TABLE mytable ( 
 myid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
 myfname CHAR(20), 
 mymname CHAR(20), 
 mylname CHAR(20), 
 PRIMARY KEY (myid) 
); 
INSERT INTO mytable VALUES (NULL, 'firstname', 'middlename', 'lastname'); 
--  
-- returns 4 columns: 
--  
SELECT * FROM mytable; 
--  
-- returns 3 columns: 
--  
SELECT myfname, mymname, mylname FROM mytable; 
--  
-- counting this will also show 4 columns: 
--  
SHOW COLUMNS FROM mytable;

PHP has a function mysql_num_fields() [php.net] which uses a MySQL API to give us the actual number of columns returned on any given query that might be a better fit here. It all depends on how you have written your query statement.

[kiwibrit]

The tables (over 50 of them, and rising) come from Excel. They have been converted into sql format, and imported to MySQL via phpMyAdmin.

They are not indexed. They consist of a series of numbers along the top, and a series of numbers down the left hand side. The remaining fields are either NULL or "X". Basically they show whether a desired feature is available based on parameters shown in 2 axes.

$query = "SELECT * FROM $thisismytable";
$result = mysql_query($query) or die ("Error in query: $query. " .
$row = mysql_fetch_row($result);
$columnCount = count($row);

The above code produces the desired result, verified against the contents of specific tables.

The tables come from Excel, because they derive from calculations needed for offering a catalogue, and were not intended for the web, originally. Since then, I have been able to get away from the odd table needed in mysql by first deriving them in Access or Excel. But I realise I need to get more stuck into the subject. Thanks for your input - it's all adding to the mosaic that's beginning to form in my head.