shavit shavit - 3 months ago 10
MySQL Question

Looping through table columns

I have a query that is supposed to loop through all the tables in the database. It does that fine, I test it by outputting the table names.

But, I'm trying to loop through every column in the database so I could use it inside another query.

This is my current code, that loops through tables in the database:

<?php
$host = "127.0.0.1";
$username = "username";
$password = "password";
$database = "database";

$link = new mysqli($host, $username, $password, $database);

if($link->connect_error)
{
die("Connection died: ".$link->connect_error);
}

$showtables = $link->query("SHOW TABLES;");

foreach($showtables->fetch_all() as $table)
{
printf($table[0] . "\n");

// what i'm trying to achieve:
foreach(/* ??? */ as $column)
{
printf("\t- ".$column."\n");
}
}
?>


Could anyone lend their hand? Thank you!

Answer

Something like :

$host = "127.0.0.1";
$username = "username";
$password = "password";
$database = "database";

$link = new mysqli($host, $username, $password, $database);

if($link->connect_error)
{
    die("Connection died: ".$link->connect_error);
}

$showtables = $link->query("DESCRIBE name_of_table;");

foreach($showtables->fetch_all() as $table)
{
    printf($table[0] . "\n");
}

If you want all the table in your database use this sql :

select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position