Check if column exists in mysql table with php

Hi Experts!

As in title, I need a php script checking if column exists in table.

Thanks for any help.
Who is Participating?
gr8gonzoConnect With a Mentor ConsultantCommented:
Sorry, try this:

$checkcolumn = mysql_query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{$tablename}' AND COLUMN_NAME = '{$column}';");
if (mysql_num_rows($checkcolumn)>0) {echo "column does not exists!";} else {echo "column exists!";}

Open in new window

The "information_schema" table in MySQL is a special, internal table that holds all the information about the database structure. You can query it like this:

SELECT column_name FROM information_schema.columns WHERE table_name = 'my_table'

You can use that method / query to check for column existence.
ZadoAuthor Commented:
Thanks gr8gonzo, I wrote this:
$checkcolumn = mysql_query("SELECT $column FROM information_schema.columns WHERE table_name = '$tablename'");
if (mysql_num_rows($checkcolumn)>0) {echo "column does not exists!";} else {echo "column exists!";}

Open in new window

but it doesn't work as expected, I keep trying...
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

ZadoAuthor Commented:
Ok, got it, it's probably not the best to do it, but it works, if you know better way, please let me know:

$tablename = $_GET['table'];
		$column = "UserID";
		$columnlist = array();
		$checkcolumn = mysql_query("SELECT column_name FROM information_schema.columns WHERE table_name = '$tablename'");
		while ($row = mysql_fetch_array($checkcolumn)) {
			$columnlist[] = $row['column_name'];
		$columnlist = implode(',',$columnlist);
		if (strpos($columnlist,$column)!==false) {echo "column exists!";} else {echo "column does not exists!";}

Open in new window

ZadoAuthor Commented:
*up  'it's probably not the best way to do it'
ZadoAuthor Commented:
Thanks :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.