Skip to main content
B
Bagger Vance
Inspiring
August 22, 2008
Answered

testing for existence of a table - MySQL

  • August 22, 2008
  • 2 replies
  • 612 views
I'm using MySQL and I just want to see if a table exists so that I can either select data from it or if it does not exists, I want to pull the same fields from a table that I already know exists.

Any help would be greatly appreciated.

Thanks
This topic has been closed for replies.
Correct answer Bagger Vance
quote:
Originally posted by: BKBK
using MySQL

<cfquery name="tableExists" datasource="DSN">
SELECT count(*) as testValue
FROM information_schema.tables
WHERE table_schema = 'the_database_name'
AND table_name = 'your_table_name'
</cfquery>

<cfif tableExists.testValue[1] GT 0><!--- your table exists --->
<cfquery name="q" datasource="DSN">
SELECT *
FROM your_table_name
</cfquery>
<cfelse><!--- your table doesn't exist --->
<cfquery name="q" datasource="DSN">
SELECT *
FROM other_table_name
</cfquery>
</cfif>

<cfdump var="#q#">

Thanks BKBK!. That works perfectly. I don't administer the ColdFusion Server, so I'm hoping the administrators are handling any security issues.

2 replies

D
Dan_Bracuk
Inspiring
August 23, 2008
BKBK's answer will work as long as cold fusion has access to the mySql system tables, which might not be a good idea from a security perspective.
D
Dan_Bracuk
Inspiring
August 22, 2008
cftry/cfcatch
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
August 23, 2008
using MySQL

<cfquery name="tableExists" datasource="DSN">
SELECT count(*) as testValue
FROM information_schema.tables
WHERE table_schema = 'the_database_name'
AND table_name = 'your_table_name'
</cfquery>

<cfif tableExists.testValue[1] GT 0><!--- your table exists --->
<cfquery name="q" datasource="DSN">
SELECT *
FROM your_table_name
</cfquery>
<cfelse><!--- your table doesn't exist --->
<cfquery name="q" datasource="DSN">
SELECT *
FROM other_table_name
</cfquery>
</cfif>

<cfdump var="#q#">

B
Bagger VanceAuthorCorrect answer
Inspiring
August 23, 2008
quote:
Originally posted by: BKBK
using MySQL

<cfquery name="tableExists" datasource="DSN">
SELECT count(*) as testValue
FROM information_schema.tables
WHERE table_schema = 'the_database_name'
AND table_name = 'your_table_name'
</cfquery>

<cfif tableExists.testValue[1] GT 0><!--- your table exists --->
<cfquery name="q" datasource="DSN">
SELECT *
FROM your_table_name
</cfquery>
<cfelse><!--- your table doesn't exist --->
<cfquery name="q" datasource="DSN">
SELECT *
FROM other_table_name
</cfquery>
</cfif>

<cfdump var="#q#">

Thanks BKBK!. That works perfectly. I don't administer the ColdFusion Server, so I'm hoping the administrators are handling any security issues.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices