Viewed   82 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?



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

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...

Saturday, August 27, 2022

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc 
Wednesday, August 17, 2022

I would try something like this:

FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id =

which is quite faster that your own solution

Saturday, October 8, 2022

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(
      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
Wednesday, August 31, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :