The list of column name of a table

[joon1]

I have a database named database1 in mySQL.

I have a table named table1.
I don’t know how many columns and each name in table1.

The code below doesn’t work correctly, but I hope that you understand what I want.

SELECT columName WHERE tableName='table1' ORDER BY columName
I like to see the result of the code above. with your help.

I am sorry, I had to post this in database community.but I don’t know how I can move from php to database.

[Gandalf]

@joon1

I don’t know how I can move from php to database.

Done!

[Dormilich]

@joon1

I don’t know how many columns and each name in table1.

This is likely caused by a database design failure. You should always know which fields are in a table.

See also: https://dev.mysql.com/doc/refman/8.0/en/show-columns.html

[r937]

@joon1

The code below doesn’t work correctly,

you forgot the FROM clause

[joon1]

[quote=“r937, post:4, topic:300441”]
you forgot the FROM clause
[/quote]I wrote “FROM clause” like the below

[code]$column_list=mysql_query(“SELECT columName
FROM database1
WHERE tableName=‘table1’
ORDER BY columName”);
while($rows=mysql_fetch_array($column_list)) {

$columName=$rows[‘columName’]
}[/code]
However it produces error saying “supplied argument is not a valid MySQL”

[Mittineague]

@joon1

not a valid MySQL

There is no excuse for writing code that has “mysql_query” or “mysql_fetch_array” or any other ”mysql_" functions. They are obsolete in current versions of PHP and any version of PHP that supports them no longer gets any security updates Dec 31 2018 and should be upgraded now if not sooner.

That said. you are querying against the wrong table. The table you want is the “information_schema” “columns” table.

https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.5/en/columns-table.html

mysql> CREATE TABLE joon1_test (
    ->   id INT(10) NOT NULL AUTO_INCREMENT
    ->   , column_one VARCHAR(10) DEFAULT NULL
    ->   , column_two VARCHAR(10) DEFAULT NULL
    ->   , column_tri VARCHAR(10) DEFAULT NULL
    ->   , last_column VARCHAR(10) DEFAULT NULL
    ->   , PRIMARY KEY (id)
    ->   );
Query OK, 0 rows affected (0.22 sec)

mysql> SELECT column_name
    -> FROM information_schema.columns
    -> WHERE table_name LIKE 'joon1_test';
+-------------+
| column_name |
+-------------+
| id          |
| column_one  |
| column_two  |
| column_tri  |
| last_column |
+-------------+
5 rows in set (0.02 sec)

mysql> DROP TABLE joon1_test;
Query OK, 0 rows affected (0.17 sec)

[r937]

@joon1

However it produces error saying “supplied argument is not a valid MySQL”

1. that’s not a MySQL error – please learn how to test MySQL queries directly in MySQL and ~not~ via php

2. i gave you a link to the page in the manual which shows you which table you should be using in your FROM clause… ah, wait, it looks like @Mittineague already explained it so that you could simply copy his results instead of learning it for yourself

[Mittineague]

My intent was not to do a disservice to joon1. I felt that since the very first example query in the documentation page you linked to showed exactly what was needed my example would not setback learning.

My assumption is that the example(s) found in the documentation were tried, but because of the poorly written PHP code there may have been an impression that the queries did not work, when in fact they do.

Because this topic is in the Database query, and moreso because I do not “patch” PHP code that needs to be replaced, I did not post any PHP related code.

[joon1]

@Dormilich

You should always know which fields are in a table.

Yes, I know them. but server doesn’t know

I like to explain What I am trying to do is the below.

(1) I have a table named “table1”
(2) I like to copy “table1” as table1_copy for my own reason(backup purpose).
(2-1) I have to do that work online because the server is not my office.
(2-2) I like to do that work(backup) by one click.
(3)I use php at the moment for server language.
(3-1) so I have to use PHP for the automatic backup

As a result, I am making, I think, automatic table copy system in PHP.

For the automatic table copy system,
I have to know the all column names at first.
Later I might need their attributes.

I know the column names but server doesn’t know them.
That is the reason why I need the query which is listing columnNames.

[rpkamp]

MySQL has an awesome tool for backups. It’s called mysqldump. Please use it instead of spending a lot of time and energy into reinventing the wheel.

[Mittineague]

@rpkamp

Please use it instead of spending a lot of time and energy into reinventing the wheel.

^ YES

If you are planning to write your own “dump” script, be warned that the effort will be rife with all manner of snares lying in wait to trip you up. It will require a deep intimate knowledge of the architecture, schemas and datatypes. There will be nested conditional controlled loops. And it will be inefficient at best.

If your host doesn’t provide mysqldump, ask for it.

[joon1]

Thanks, ScallioXTXShiny and Mittineague.
I admit I have to accept your advice.

I have to give it up by your advice.
Thank you very much.