How to access a saved query

  • Thread starter Thread starter tarweesh
  • Start date Start date
T

tarweesh

hi, i created a query joining several tables and saved it in a
Microsoft Access database.
Now i want to access this query using PHP the same way i access a table
but i am getting an error.
$sql="SELECT * FROM q3";
$rs=odbc_exec($conn,$sql);

q3 is the saved query, i am sure $conn is working because when i try
writing any table's name instead of q3 it works properly.

i know i can create the query in the PHP but it's getting data from
several tables and there are lots of calculated fileds in the query so
this will take a lot of time each time the page is loaded.
 
You missed providing probably the single most import piece of information,
the SQL view of the query. Any way you could share this?
 
Duane said:
You missed providing probably the single most import piece of information,
the SQL view of the query. Any way you could share this?
Click to expand...

you can assume anything !!!
that's an example:
let say i have a database with 2 tables t1 & t2.
t1(id, title, size1, size2, size3)
t2(id, rate, date)
t2 can have several entries for each id so i need to have only the
latest one in the query.

i created a query to show these fields: id, title, size
"size1+size2+size3" and the newest rate stored in t2.

here is the SQL of the query if it's gonna help you!
SELECT t1.id, t1.title, [t1].[size1]+[t1].[size2]+[t1].[size3] AS
[size], A.date
FROM t1 INNER JOIN t1 AS A ON t1.id = A.id
WHERE (((A.date)=(SELECT MAX(B.date) FROM t1 AS B WHERE B.id = A.id )))

i stored the query in the Microsoft Access as "q" and i need to display
the query "q" on a web page using PHP, and i don't want to create the
query in the PHP because that's just a simple example but my database
have lots of complex calcutations and it would be a mess to execute it
every time the PHP page is loaded.

i get an error here.
$conn=odbc_connect('db1','','');
$sql="SELECT * FROM q";
$rs=odbc_exec($conn,$sql);
 
Does a change to this work:
$sql="SELECT * FROM t1";
In your SQL:
SELECT t1.id, t1.title, [t1].[size1]+[t1].[size2]+[t1].[size3] AS
[size], A.date
FROM t1 INNER JOIN t1 AS A ON t1.id = A.id
WHERE (((A.date)=(SELECT MAX(B.date) FROM t1 AS B WHERE B.id = A.id )))
There is no date field in t1.
--
Duane Hookom
Microsoft Access MVP


tarweesh said:
Duane said:
You missed providing probably the single most import piece of information,
the SQL view of the query. Any way you could share this?
Click to expand...

you can assume anything !!!
that's an example:
let say i have a database with 2 tables t1 & t2.
t1(id, title, size1, size2, size3)
t2(id, rate, date)
t2 can have several entries for each id so i need to have only the
latest one in the query.

i created a query to show these fields: id, title, size
"size1+size2+size3" and the newest rate stored in t2.

here is the SQL of the query if it's gonna help you!
SELECT t1.id, t1.title, [t1].[size1]+[t1].[size2]+[t1].[size3] AS
[size], A.date
FROM t1 INNER JOIN t1 AS A ON t1.id = A.id
WHERE (((A.date)=(SELECT MAX(B.date) FROM t1 AS B WHERE B.id = A.id )))

i stored the query in the Microsoft Access as "q" and i need to display
the query "q" on a web page using PHP, and i don't want to create the
query in the PHP because that's just a simple example but my database
have lots of complex calcutations and it would be a mess to execute it
every time the PHP page is loaded.

i get an error here.
$conn=odbc_connect('db1','','');
$sql="SELECT * FROM q";
$rs=odbc_exec($conn,$sql);
Click to expand...
 
In your SQL:
SELECT t1.id, t1.title, [t1].[size1]+[t1].[size2]+[t1].[size3] AS
[size], A.date
FROM t1 INNER JOIN t1 AS A ON t1.id = A.id
WHERE (((A.date)=(SELECT MAX(B.date) FROM t1 AS B WHERE B.id = A.id )))
There is no date field in t1.
Click to expand...

i made a small mistake in the SQL, it's t1 INNER JOIN t2:
SELECT t1.id, t1.title, [t1].[size1]+[t1].[size2]+[t1].[size3] AS
[size], A.date
FROM t1 INNER JOIN t2 AS A ON t1.id = A.id
WHERE (((A.date)=(SELECT MAX(B.date) FROM t1 AS B WHERE B.id = A.id
)))

and it seems that you don't understand what i want, i created this
Query and saved in the Microsoft Access database.

Does a change to this work:
$sql="SELECT * FROM t1";
Click to expand...

yes it makes a change, this works properly and i can access all the
data of t1 but the point is that i need to access q not t1.
 
thanks Duane for your help, i know now where the problem is, it's the
IIf («expr», «truepart», «falsepart»)
it worked when i removed it from my query. but it was working in the
Microsoft Access with this IIf statement so i guess it cannot be
executed in PHP
 
thanks Duane for your help, i know now where the problem is, it's the
IIf («expr», «truepart», «falsepart») which i have in my query.
it worked when i removed it from my query. but it was working in the
Microsoft Access with this IIf statement so i guess it cannot be
executed in PHP
 
You are correct about the IIf() breaking your query from PHP. That's why I
asked to see some SQL. You can try other Access query syntax or use code in
PHP for the same results.
 
Back
Top