CODEWITHSUNDEEP
phpmysqlsql
taze totero
taze totero

Reputation: 359

SQL query to retrieve columns names list

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 !

Upvotes: 0

Views: 295

Answers (1)

Nana Partykar
Nana Partykar

Reputation: 10548

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?

Upvotes: 2

Related Questions