[mysql] Table Doesn't Exist

[dbldutch21]

I've created a query inside my mySQL database. In the past with Access I simply had to run a query to get the information.

<cfquery name='qGetQuery' datasource='mydatabase'>
SELECT mycolumn
FROM mytable
</cfquery>

That is not working since i switched to mySQL. I can see the query and the results using Navicat but when i run the CF page i get the following error.

Error Executing Database Query.
Table 'mydatabase.mytable' doesn't exist

Any ideas?

Thanks

[paross1]

Was mytable created in a different schema than the "other" tables in mydatabase?

Phil

[dbldutch21]

i shouldn't have called it mytable.... more like myquery.

Its not a table... I created a query in mySQL which has all Distinct Years from my Event Table.

I'd like to select that query and output the information like i would with a table.

I'm new to mySQL... in Access you just ran CFQUERY like you would for a table. But with mySQL it does not work for me.

Thanks in advance.

[paross1]

Are you trying to say that you saved it as a stored procedure in MySQL?

Phil

[dbldutch21]

I'm using the mySQL GUI Navcat. There is an area to add a Query or a Stored Procedure. I'm am chosing Query and entering the following information.

SELECT DISTINCT Year(`event`.`startDate`) AS eventYear
FROM `event`
ORDER BY eventYear asc;

If I preview the query it shows me a set of tablular data with the values 2000,2001,2002,2005

I want to cfquery this query but it says the "Table" is not found.

Thanks for being patient.

[paross1]

Sorry, I have no idea what GUI Navcat is or where it would store a "query" where you could access it from ColdFusion. Could you possibly be referring to a view?

Phil

[dbldutch21]

Navicat is a Graphical User Interface for mySQL so you don't have to use the command line to make changes to your database.

It's similar to Access where you can create Tables or Queries.

In Access if I create a Table or a Query i can access it with CFQUERY

In mySQL if I create a Table I can access it with CFQUERY but if I create a saved query in mySQL I cannot access it with CFQUERY.

Thanks for trying to help.

[paross1]

Why don't you create a view with your query, which I know you can save in MySQL, and then query the view from ColdFusion.

In MySQL......

CREATE VIEW my_vw AS SELECT DISTINCT Year(`event`.`startDate`) AS eventYear
FROM `event`
ORDER BY eventYear asc;

Then something like this in CF.....

<cfquery name='qGetQuery' datasource='mydatabase'>
SELECT eventYear
FROM my_vw
</cfquery>

Phil