How can i make a query like Microsoft Access, and a query from a query 1

[scriggs]

Hi

I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL

Thanks for any help.

 

[jemminger]

if you're using MySQL Query Browser, write your query then File > Save As...

-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }
i like your sleeves...they're real big

 

[TonyGroves]

MySQL differs from Access in that it is a multi-user server, not an all-in-one database tool. The server can then be communicated with using many different front-end interfaces, including GUI's and custom application programs.

Regarding your first question, turning a variable into a field name, that can be done very easily by assembling your query using program code.

If you are using MySQL 5.0 or later, you can declare a query as a "view", and later access that view as if it were a read-only table.

 

[scriggs]

Thanks jemminger,

I have saved queries like that before, but how can I use that query then in code?

And How can i reference a query inside another query?

 

[scriggs]

Thank TonyGroves, my second question tonight you have answered. How do i make a view then, sounds like that is what i am looking for.

 

[TonyGroves]

[tt]CREATE VIEW v1 AS
SELECT id,name FROM customers

SELECT * FROM v1
[/tt]

 

[scriggs]

Thanks TonyGroves, that is the link I have been missing!

Do you know if you can create a view with a variable, and then just supply the variable from code? (Im using PHP, but an example in anything would help).

 

[TonyGroves]

Do you mean something like "CREATE VIEW v1 AS SELECT id,$field FROM tbl"? No, you can't do that. But you can include all the fields you'd be likely to need, and your queries can select whichever fields they want from the view.

 

[scriggs]

Yes, I wanted to supply the $field through code, but if i can't then I can't. Thanks...