Solved

How can I list column names from a table?

Posted on 2014-11-03
11
235 Views
Last Modified: 2014-11-04
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?
0
Comment
Question by:brucegust
  • 6
  • 5
11 Comments
 

Author Comment

by:brucegust
ID: 40420421
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?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40420446
I think the query you want is "SHOW COLUMNS FROM verizon"
0
 

Author Comment

by:brucegust
ID: 40420454
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...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:brucegust
ID: 40420455
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

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40420458
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

0
 

Author Comment

by:brucegust
ID: 40420487
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?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40420638
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

0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40420646
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

0
 

Author Comment

by:brucegust
ID: 40422041
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?
0
 

Author Comment

by:brucegust
ID: 40422129
Never mind, I got it!

Thanks!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40422168
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
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Ajax and PHP 9 53
Delete image(s) associated with record(s) 16 24
How do I Check for duplicate entries in mysql 15 24
php hashing methods 3 11
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question