Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I list column names from a table?

Posted on 2014-11-03
11
Medium Priority
?
244 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 111

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 111

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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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 111

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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 …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
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…

721 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