SQL query to retrieve columns names list

Question

So I've been struggling with my database to get it to give me the name of columns contained within a table.

Here's my PHP :

  $sql = "SELECT * FROM hacklvrf_db.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'generators'" ; 
  $result = mysqli_query($con, $sql); 
  $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
  $generators = $row['generators'];
  foreach ($row as $lol) {
  echo ($lol);
  }

For some reason this isn't answering with anything (PHP doesn't pop an error but my variables seem to be empty) and I don't really understand what I'm missing.

  echo (gettype ($row));

Shows a 'NULL'

I know this question has been asked before and I actually got my SQL query from other places but I since I can't work it out... here I am !

Thanks in advance guys !

`WHERE TABLE_NAME = N'generators'` that's a syntax error. Check for errors. Btw, `echo` isn't a function, it's a construct. — Funk Forty Niner, Aug 17 '16 at 11:54
Ha Ha `:D` @Fred-ii- Still A Mystery For Me. Where These People Go After Posting A Question. Few Don't Even Reply. — Nana Partykar, Aug 17 '16 at 12:02
@Fred-ii- Thanks for your answer, actually I copied this code and some dude said the N was to support special type of characters. I tried without and get the same answer. (or no answer should I say) — taze totero, Aug 17 '16 at 12:02
@Fred-ii- Also what is implied by the fact that echo isn't a function in this situation? Thanks! — taze totero, Aug 17 '16 at 12:04
http://dev.mysql.com/doc/refman/5.7/en/information-schema.html *"The definition for character columns (for example, TABLES.TABLE_NAME) is generally `VARCHAR(N)` CHARACTER SET utf8 where `N` is at least 64."* - Is this what the guy meant? — Funk Forty Niner, Aug 17 '16 at 12:05
hehehe ;-) well you're not using the right syntax here. The `N` means the name. So remove it `WHERE TABLE_NAME = 'generators'` - Give that a whirl. — Funk Forty Niner, Aug 17 '16 at 12:07
@Fred-ii- My bad thanks for the heads up! I just tried that though and still getting the exact same result :( — taze totero, Aug 17 '16 at 12:08
welcome; add error checking to your query http://php.net/manual/en/mysqli.error.php see what it throws back then. You are connecting successfully with mysqli_ right? and http://php.net/manual/en/function.error-reporting.php or remove the quotes. — Funk Forty Niner, Aug 17 '16 at 12:09
@Fred-ii- Yes the connection is working (I can get other queries to work), here is what I tried : `code` — taze totero, Aug 17 '16 at 12:16

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

Change

$sql = "SELECT * FROM hacklvrf_db.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'generators'";

To

$sql = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='database-name' AND `TABLE_NAME`='table-name'";

Used your sql query after changing database name and table name, I got error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.COLUMNS WHERE TABLE_NAME

Updated Code (Just put your database name and table name in query)

<?php
$sql = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='database-name' AND `TABLE_NAME`='table-name'";
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
  echo $row['COLUMN_NAME']."<br>";
}

?>

For more info, click MySQL query to get column names?

edited May 23 '17 at 12:33

Community

1
1

answered Aug 17 '16 at 12:14

Nana Partykar

10,556
10
48
77

I'll do that right now and update, thanks for your help! – taze totero Aug 17 '16 at 12:17
Works flawlessly! Thank you a lot! Any chance you could quickly explain why the first way didn't work ? (it looks like it's pretty much the same thing) Thanks again! – taze totero Aug 17 '16 at 12:21
I don't have much idea to it. I tried your query in my system and it gave me error which I posted in my answer. So, I searched it and got new query which I posted as link in my answer too. Go through it. There you will get a better explanation. *Glad It Worked.* @tazetotero – Nana Partykar Aug 17 '16 at 12:23

SQL query to retrieve columns names list

1 Answers1