Solved

How can I list column names from a table?

Posted on 2014-11-03
11
226 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
Comment Utility
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 108

Expert Comment

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

Author Comment

by:brucegust
Comment Utility
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
 

Author Comment

by:brucegust
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:brucegust
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Never mind, I got it!

Thanks!
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now