2

If I have [x] [y] [z] columns inside a [mytable] table in MSSQL, I would like to know how can I loop through each of them using php in order to have all of the columns' name? The reason is because I could potentially delete or add a column so that php code would make this dynamic without having to hardcode the php page. Basically, if I could have a php code similar to the following,

foreach( COLUMN as $columname in [SQL table]) 
    echo "$columname";
    // do something else
}

it would be awesome.

Andrew
  • 45
  • 9
  • Look into the INFORMATION_SCHEMA system views. INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS may be helpful – JeffUK Jul 25 '17 at 15:24

2 Answers2

3

Select the data from the INFORMATION_SCHEMA.COLUMNS system view. Filtered on your table name.

select column_name 
from information_schema.columns
where table_name = 'mytable'

Returns

Column_name
-----------
x
y
z

This gives you a resultset containing all column names, loop through and use the values.

JeffUK
  • 4,107
  • 2
  • 20
  • 34
  • But as you can see I want it looped since it's many columns not one specific one – Andrew Jul 25 '17 at 15:29
  • It's a SQL statement, it returns all columns in your table; try it out. – JeffUK Jul 25 '17 at 15:30
  • But I'm trying to loop. I'm trying to loop and for each column, echo the column name and also do something else for the column. Not all together at the same time. – Andrew Jul 25 '17 at 15:33
  • This gives you a resultset containing all column names, loop through it like any other result set and print the values. – JeffUK Jul 25 '17 at 15:34
  • This way also allows you to look at other metadata like datatype, which may be useful if you're not in control of the database schema. – JeffUK Jul 25 '17 at 15:39
  • Ie. you want row number `ID`? Not easily in sql server this might help, if not try something and post another question if you cant work it out https://stackoverflow.com/questions/187998/row-offset-in-sql-server – JeffUK Jul 26 '17 at 20:12
0

If it's PHP, your foreach should look like this:

foreach ($rows as $row) {
    var_dump($row); // you'll see columns and values
    echo $row['user_id']; // for example
}

Or, you can do a key/value foreach:

foreach ($rows as $row) {
    foreach($row as $column => $value){
        echo 'Column '.$column.' has value '.$value."\n";
    }
}
delboy1978uk
  • 12,118
  • 2
  • 21
  • 39
  • Ive edited my answer. If you need to know how to actually fetch data, read up on PDO http://php.net/manual/en/book.pdo.php – delboy1978uk Jul 25 '17 at 15:26
  • @Andrew if you put some sample data and expected results in your question it would be clearer what you're asking. We shouldn't have to read it carefully, it should be obvious! – JeffUK Jul 25 '17 at 15:28
  • JeffUK he doesn't know what columns will be returned, that's his point! – delboy1978uk Jul 25 '17 at 15:30
  • I'm sure it's not indeterminate.. with X scenario he wants Y returned. – JeffUK Jul 25 '17 at 15:33