Solved

How can I list column names from a table?

Posted on 2014-11-03
11
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

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 …
This article discusses four methods for overlaying images in a container on a web page
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.

751 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