Solved

Check if column exists in mysql table with php

Posted on 2012-04-10
6
1,948 Views
Last Modified: 2012-04-10
Hi Experts!

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

Thanks for any help.
0
Comment
Question by:Zado
  • 4
  • 2
6 Comments
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 37828526
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.
0
 
LVL 8

Author Comment

by:Zado
ID: 37828623
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...
0
 
LVL 8

Author Comment

by:Zado
ID: 37828685
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

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 8

Author Comment

by:Zado
ID: 37828692
*up  'it's probably not the best way to do it'
0
 
LVL 34

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 37828697
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

0
 
LVL 8

Author Closing Comment

by:Zado
ID: 37828829
Thanks :-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now