How can I list column names from a table?

I can retrieve the column names from my table when I input this sql directly into my MySQL database:

SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='json_machine' AND `TABLE_NAME`='verizon'

Open in new window


But when I try to do it in the context of a PHP page using this:

$test = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='json_machine' AND `TABLE_NAME`='verizon'";
$test_query=mysqli_query($cxn, $test);
while($test_row=mysqli_fetch_assoc($test_query))
{
extract($test_row);
echo $test_row[0];
}

Open in new window


I've tried playing with arrays and loops and I still can't figure it out. How do I display my column names?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bruce GustPHP DeveloperAuthor Commented:
When I do this:

$test = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='json_machine' AND `TABLE_NAME`='verizon'";
$test_query=mysqli_query($cxn, $test);
$test_array=mysqli_fetch_array($test_query);

foreach($test_array as $row)
{
echo "$row<br>";
}

Open in new window


...I get:

id
id

What the smoke?
Ray PaseurCommented:
I think the query you want is "SHOW COLUMNS FROM verizon"
Bruce GustPHP DeveloperAuthor Commented:
Ray, when I do that, I get:

id
id
int(11)
int(11)
NO
NO
PRI
PRI


auto_increment
auto_increment

Something's still foul...
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Bruce GustPHP DeveloperAuthor Commented:
And this is what I'm doing:

$test = "SHOW COLUMNS FROM verizon";
$test_query=mysqli_query($cxn, $test);
$test_array=mysqli_fetch_array($test_query);

foreach($test_array as $row)
{
echo "$row<br>";
}

Open in new window

Ray PaseurCommented:
This shows how I've done it.  See lines 56-59.
<?php // demo/mysqli_table_to_csv.php
error_reporting(E_ALL);

// DEMONSTRATE HOW TO EXPORT A TABLE IN CSV FORMAT

// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL "GET" ARGUMENT?
$table_name = 'visitors';

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'storage/EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');
if (!$fp) trigger_error("UNABLE TO OPEN $csv", E_USER_ERROR);

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
if ($res->num_rows == 0)
{
    trigger_error("WTF? $table_name HAS NO COLUMNS", E_USER_ERROR);
}
else
{
    while ($show_columns = $res->fetch_object())
    {
        $my_columns[] = $show_columns->Field;
    }
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) trigger_error("FAILURE WRITING COLUMN NAMES TO $csv", E_USER_ERROR);

Open in new window

Bruce GustPHP DeveloperAuthor Commented:
Alright, Ray!

Here's what I've got, based on your counsel:

function TableInfo()
{
	global $mysqli;
	
	$sql = "show columns from verizon";
	$query = $mysqli->query($sql);
	
	if(!$query)
	{
	$error = $mysqli->errno.': '.$mysqli->error;
	trigger_error($error, E_USER_WARNING);
	}
	
	if($query->num_rows==0)
	{
	trigger_error("you don't have any column names", E_USER_WARNING);
	}
	//return $data_count;
	
	while($show_columns=$query->fetch_object())
	{
	$the_columns=$show_columns->Field;
	}
	return $the_columns;
}

Open in new window


What I want to do is list them on my page, and not write them to a csv file. That said, here's what I tried:

$columns = TableInfo();

foreach($columns->$the_columns as $row)
{
echo "$row<br>";
}

Open in new window


I got an error that says "$the_columns" is an undefined variable, and then the subsequent error of trying to get property of a non-object and invalid argument supplies for $the_columns  as well.

Where am I blowing it?
Ray PaseurCommented:
Try it like this (untested):
<?php // demo/mysqli_table_to_csv.php
error_reporting(E_ALL);

// DEMONSTRATE HOW TO LIST TABLE NAMES

// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL "GET" ARGUMENT?
$table_name = 'visitors';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
if ($res->num_rows == 0)
{
    trigger_error("WTF? $table_name HAS NO COLUMNS", E_USER_ERROR);
}
else
{
    while ($show_columns = $res->fetch_object())
    {
        $my_columns[] = $show_columns->Field;
    }
}

print_r($my_columns);

Open in new window

Ray PaseurCommented:
Here is a small segment of code annotated with comments.
// ITERATE OVER A RESULTS SET, RETURNING EACH ROW IN THE FORM OF AN OBJECT
while($show_columns=$query->fetch_object())
{
    // ASSIGN THE VALUE OF THE OBJECT->Field PROPERTY TO A STRING VARIABLE, OVERWRITING IT EVERY TIME
    $the_columns=$show_columns->Field;
}
// RETURN THE STRING VALUE
return $the_columns;

Open in new window


What you more likely wanted was something like this.  The empty square brackets are PHP array notation meaning, "Add a new element to the array."
// ITERATE OVER A RESULTS SET, RETURNING EACH ROW IN THE FORM OF AN OBJECT
while($show_columns=$query->fetch_object())
{
    // ASSIGN THE VALUE OF THE OBJECT->Field PROPERTY TO ELEMENTS OF AN ARRAY, APPENDING A NEW ELEMENT EACH TIME
    $the_columns[]=$show_columns->Field;
}
// RETURN THE ARRAY OF COLLECTED STRING VALUES
return $the_columns;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bruce GustPHP DeveloperAuthor Commented:
Love it, love it, love it!

OK, Ray, here's my question: I've got a working function and I've got an array filled with all variables represented by $the_columns (line 8 of the code that you have written above).

I am now in the body of my page and I want to print the results of that array. I was thinking that syntax would look like this:

$columns = TableInfo();
$rows=$columns->the_columns;

foreach($rows as $value)
{
echo "$value<br>";
}

Open in new window


...but I'm wrong. I get an error that says: "Trying to get property of non-object in C:\wamp\www\sandbox\csv_upload.php on line 127" (line 127 being $rows=$columns->the_columns) and another error that reads "Invalid argument supplied for foreach() in C:\wamp\www\sandbox\csv_upload.php on line 129," which I'm assuming is the logical result of the shortcomings represented by line 127. How do I accurately "unpack" the array as it's being supplied by my function?
Bruce GustPHP DeveloperAuthor Commented:
Never mind, I got it!

Thanks!
Ray PaseurCommented:
Whenever you're not 100% sure what your variables contain, this function is your best friend:
http://php.net/manual/en/function.var-dump.php

Thanks for the points, ~Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.