taze totero taze totero - 5 months ago 15
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?