Asked  7 Months ago    Answers:  5   Viewed   37 times

I'd like to get all of a mysql table's col names into an array in php?

Is there a query for this?

 Answers

85

The best way is to use the INFORMATION_SCHEMA metadata virtual database. Specifically the INFORMATION_SCHEMA.COLUMNS table...

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

It's VERY powerful, and can give you TONS of information without need to parse text (Such as column type, whether the column is nullable, max column size, character set, etc)...

Oh, and it's standard SQL (Whereas SHOW ... is a MySQL specific extension)...

For more information about the difference between SHOW... and using the INFORMATION_SCHEMA tables, check out the MySQL Documentation on INFORMATION_SCHEMA in general...

Wednesday, March 31, 2021
 
Corne
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
74

I previously said that this couldn't be done, but I was wrong. I ended up needing something like this myself so I looked around, and discovered that server-side prepared statements let you build and execute arbitrary SQL statements from strings.

Here is an example I just did to prove the concept:

set @query := (
  select concat(
    "select",
      group_concat(concat("n  1 as ", column_name) separator ','),
    "nfrom dual")
  from information_schema.columns
  where table_name = 'columns')
;
prepare s1 from @query
;
execute s1
;
deallocate prepare s1
;
Friday, June 18, 2021
 
aurelijusv
answered 4 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 3 Months ago
47

To get raw counts

select window_height, count(*) totalusers
from tbl
group by window_height
order by totalusers desc  # or by window_height

To get the modal average (this will show multiple values if there are ties for the highest count)

select window_height, totalusers
from (
    select @r := if(totalusers>@r,totalusers,@r) maxcount, window_height, totalusers
    from (select @r:=0) initvars, (
        select window_height, count(*) totalusers
        from tbl
        group by window_height
    ) X ) Y
where totalusers = @r

This uses a MySQL trick of using a variable to store the max count as it goes through the aggregated subquery. Summary of operations

  • O(n): scan table once and build the counts (T1)
  • O(n): scan the derived table T1 and keep the highest count in the variable @r (T2)
  • O(n): scan the derived table T2 and filter only for the heights with the highest count
Thursday, September 2, 2021
 
uiroshan
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :