Access Parameter Queries to MySQL

[mistux]

I have a "stuck in a rut question."

Now, keep in mind that I am coming from the MS Access database world trying to
now work in the MySQL world.

In Access I am accustomed to writing a query "Query A" that takes a
"non-hard coded" parameter, like "[Enter City]" and then using that query
in yet another query "Query B". When I run the second query "Query B" I
will be prompted for "[Enter City]" and then get my record set from
"Query A" that used records from "Query B".


I have been converting my MS Access queries to MySQL's SQL code which I
have actually become good at, believe it or not. But when I run into a
query that needs to be passed any information and that query will be used
in another query...then I have to stop, because I don't know how to
write the SQL code.

My mental "rut" that I am stuck in is this: should I be writing SQL code
that does what my "query within a query" does (which I don't think I can,
because that is why I did it in two or more sub queriesin MS Access)
or is there a way to write SQL code that uses other queries that get passed
parameters?

I don't know if this is a "MySQL" confusion or if it is a "writing SQL code"
confusion.

How should I write MySQL code; is it just one big long and ugly statement, or is
there a way to utilize record sets from other paramiterized SQL code?

I hope I am making my self clear, to be honest; this is really fuzzy with
me. I know that MS Access give me a lot of shortcuts, but being able to
use parameter queries within queries is so fundamental to how I work in MS
Access that I am having a difficult time understanding the MySQL steps to
accomplish the same thing.


Any help or suggestions are welcome!

 

[sleipnir214]

You have to remember that Access is an interactive database programming application. It is designed to interact with users through a GUI.

MySQL is a database server. It's designed to interact with other software. The idea of parameterizing queries based on user input is not supported.

The functional equivalent of Access' QueryA using QueryB as a datasource is to use a MySQL version of QueryB to select data to a temporary table, then running a MySQL of QueryA against that temporary table. It's what Access does, anyway.

I recommend that you look into using an external programming language (PHP and perl are two suggestions, though there are others) to generate hard-coded SQL based on user input. It's the way MySQL was designed.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[mistux]

Now I am getting somewhere.

I am starting to get familiar with PHP, this is why I ask the question since I am trying to convert an Access database that I wrote to keep track of my high school hockey team's stats (I'm the coach)

http://pennhockey.org/stats/selections.php

(the buttons with the * don't work yet, but the others do)

I am pretty good with Access, but converting things to SQL is really slowing me down.

Making temp tables is a concept that I can grasp (I think). Now, how do I go about making temp tables and then using them in a MySQL code. I am assuming it will be all one "chunk" of SQL code "strung" together. Again, I am not quite sure what the code would look like so I'm asking for maybe a simple example. I have heard of Hash tables in MS SQL, but that is about all.

 

[hos2]

what I see on your website is that you have 2 queries and the result is passed on to another querie ?? you get something like this

when you have a query like
$query="select teamid,teamname from teamtable"
$rsteam=mysql_query($query,$conn);

$query="select period from periodtable"
$rsperiod=mysql_query($query,$conn);


in your first page you get the select list with

<select name=&quot;teamid&quot;><option value=&quot;&quot;>Empty
<? 
$list = mysql_num_rows($rsteam); 
while($i < $list)	{
$row = mysql_fetch_array($rsteam); 
$teamname=$row[&quot;teamname&quot;];
$teamid=$row[&quot;teamid&quot;];
   echo &quot;<option value='$teamid'>$teamname&quot;;
$i++;
}
?>
</select>

when you submit the values to another php page you can use those values in a query like

$query="select * from maintable where teamid = '$teamid' and period = '$period'";

you can also use the % and LIKE to allow for wildcards or if a field is not neccessary like

$query="select * from maintable where teamid like '$teamid%' and period like '$period%'";

you can check out

http://www.thewhiskysite.com

at the database for how above example works with whisky