taze totero taze totero - 3 months ago 7
MySQL Question

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 !

Answer

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?

Comments