Can I do mulitple queries on a single ODBC connection (1 Viewer)

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
Im pretty sure im doing this wrong, but I cant think of any other way to do it.

My issue is I have a link table where the user ID , date and approval are entered in to the table to give me a request ID.

I would then use the requestID and add into another table along with many other things.

I am trying to get the max requestID from a table based on the userID and date, but for some reason, its not returning anything.

My question is, can I do multiple queries off of the same connection? or do I have to close the connection everytime and then reconnect to run a new query?

Code:
$conn=odbc_connect('database','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);

}
    $insert="INSERT INTO tblRequest (EmployeeID, RequestDate, RequestApproval) VALUES ($empID,'$reqDate',1)";
    echo $insert;    
    $rs=odbc_exec($conn,$insert);

    if (odbc_error())
        {
             echo odbc_errormsg($conn);
        }

    $requestID="SELECT Max(tblRequest.RequestID) AS MaxOfRequestID, tblRequest.EmployeeID, tblRequest.RequestDate FROM tblRequest GROUP BY tblRequest.EmployeeID, tblRequest.RequestDate HAVING (((tblRequest.EmployeeID)=$empID) AND (tblRequest.RequestDate)=$reqDate)";
    echo $requestID;    
    $rs2=odbc_exec($conn,$requestID);

    while (odbc_fetch_row($rs2))
      {    
      $reqID = odbc_result($rs2,"MaxOfRequestID");    
     }    

    echo $reqID;
    if (odbc_error())
        {
          echo odbc_errormsg($conn);
        }

Any help would be appreciated.
 

ajetrumpet

Banned
Local time
Yesterday, 20:12
Joined
Jun 22, 2007
Messages
5,638
i am just getting started with mysql rainman, but why would you have to close the conn everytime to run a new query? It just doesn't make sense. just my thoughts.

if it helps, I was on a movie site just the other day that produced a plethera of errors during the wee hours of the morning, and the PHP code it showed out of error gave many "mysql_query" commands. if accessing a page requires multiple queries like that, I guess the answer would be yes, right?
 

Banana

split with a cherry atop.
Local time
Yesterday, 18:12
Joined
Sep 1, 2005
Messages
6,318
FYI, MySQL does support multiple statement.

However, I should caution that I'm not familiar with PHP. That said, I don't think you should be using ODBC connection - there's supposed to be a native MySQL PHP connector.

http://dev.mysql.com/doc/refman/5.0/en/connector-php.html

If you really intend to use ODBC connection regardless, you may to enable support for multiple statements by specifying option 67108864. If you already have other options, add them together.

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

HTH.
 

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
i am just getting started with mysql rainman, but why would you have to close the conn everytime to run a new query? It just doesn't make sense. just my thoughts.

if it helps, I was on a movie site just the other day that produced a plethera of errors during the wee hours of the morning, and the PHP code it showed out of error gave many "mysql_query" commands. if accessing a page requires multiple queries like that, I guess the answer would be yes, right?


Yeah thats the issue, it doesnt make sense to me either, BUT I cant figure out any reason that I can't get a record from it.

Im wondering if i need to pause or something to get the record in the table before I go and pull the ID out of the table, BUT if that were the case, wouldn't I get the wrong record?

Im gonna try it today to see if I need to close the connection and re-establish it.

I'd like to find an example where they populate a link table before using that ID in the new record in the other table, that would probably point me in the right direction. I'll keep looking.
 

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
Ok so the issue is not with the multiple queries on the same connection, its now on this query

Code:
$requestID="SELECT Max(tblRequest.RequestID) AS MaxOfRequestID, tblRequest.EmployeeID, tblRequest.RequestDate FROM tblRequest GROUP BY tblRequest.EmployeeID, tblRequest.RequestDate HAVING (((tblRequest.EmployeeID)=$empID) AND (tblRequest.RequestDate)=".date('m/j/Y').")";

So it works out to be
Code:
SELECT Max(tblRequest.RequestID) AS MaxOfRequestID, tblRequest.EmployeeID, tblRequest.RequestDate FROM tblRequest GROUP BY tblRequest.EmployeeID, tblRequest.RequestDate HAVING (((tblRequest.EmployeeID)=1) AND (tblRequest.RequestDate)=03/1/2010)

For some reason it will not return a result with the date requirement there. The request date is set to date/time shortdate in the table.

If i try adding the single quotes around the date, then I get a query mismatch error for the date.

Any thoughts?
 

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
FYI, MySQL does support multiple statement.

However, I should caution that I'm not familiar with PHP. That said, I don't think you should be using ODBC connection - there's supposed to be a native MySQL PHP connector.

http://dev.mysql.com/doc/refman/5.0/en/connector-php.html

If you really intend to use ODBC connection regardless, you may to enable support for multiple statements by specifying option 67108864. If you already have other options, add them together.

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

HTH.

Hi Banana,

Sorry I did not respond to your post earlier, I did not see it until now.

Why should I not be using ODBC for my connection? I'm running it off of a access backend that is on a shared drive for my users to use. I didn't think there was a way for me to connect to it without having an ODBC connection for it.

Think I should make an mySQL backend instead?

Thanks for your input.
 

Banana

split with a cherry atop.
Local time
Yesterday, 18:12
Joined
Sep 1, 2005
Messages
6,318
I'm so dreadfully sorry. I was under the impression you were using MySQL & PHP but you never ever said MySQL. So whatever I said was junk. So sorry!

Yes, ODBC is probably appropriate for Access as backend. If it serves your purpose, then no need to change it.

Again, I'm unfamiliar with PHP, but does PHP require contatenating the variables ($empID) into the SQL string as VBA does? (e.g. "... empID = " & empID & "...")?
 

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
I'm so dreadfully sorry. I was under the impression you were using MySQL & PHP but you never ever said MySQL. So whatever I said was junk. So sorry!

Yes, ODBC is probably appropriate for Access as backend. If it serves your purpose, then no need to change it.

Dont worry I am also very unfamiliar with PHP! This is more of a testing/learning experience that I'm trying for a project I was assigned to here at work.

Again, I'm unfamiliar with PHP, but does PHP require contatenating the variables ($empID) into the SQL string as VBA does? (e.g. "... empID = " & empID & "...")?

Actually, from what I understand so far, it doesnt, if you are using variable as an output in your html, you do need to do .$empID. but in the sql queries, you do not have too.

That is what is confusing me about the problem I am having when I add a date to the query. When I insert the date into the table i use
single quotes, but in the query, if i use single quotes i get datatype mismatch.
 

Banana

split with a cherry atop.
Local time
Yesterday, 18:12
Joined
Sep 1, 2005
Messages
6,318
Right. I thought that would be the case with PHP but wanted to be sure.

As for the quotes... I think that if you do this in a passthrough query, it will work. JET treats anything within single or double quotes as text and delimits date with hash signs (e.g. #9999-01-01#) so it will think you are trying to use a text to compare with a date even though in MySQL and SQL Server, the dates are delimited with the same delimiters as text are.
 

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
Right. I thought that would be the case with PHP but wanted to be sure.

As for the quotes... I think that if you do this in a passthrough query, it will work. JET treats anything within single or double quotes as text and delimits date with hash signs (e.g. #9999-01-01#) so it will think you are trying to use a text to compare with a date even though in MySQL and SQL Server, the dates are delimited with the same delimiters as text are.

Alright, makes sense, but how do I write a passthrough query in SQL?

The query works fine as is in access.
 

Banana

split with a cherry atop.
Local time
Yesterday, 18:12
Joined
Sep 1, 2005
Messages
6,318
What happens if you change your PHP SQL to use # delimiters instead of single quote? Try that first because that's "JET SQL", so to speak.
 

rainman89

I cant find the any key..
Local time
Yesterday, 21:12
Joined
Feb 12, 2007
Messages
3,016
What happens if you change your PHP SQL to use # delimiters instead of single quote? Try that first because that's "JET SQL", so to speak.

Genius!!

I dont know why I couldn't find an example showing me that.

Changed it to
Code:
$requestID="SELECT Max(tblRequest.RequestID) AS MaxOfRequestID, tblRequest.EmployeeID, tblRequest.RequestDate FROM tblRequest GROUP BY tblRequest.EmployeeID, tblRequest.RequestDate HAVING (((tblRequest.EmployeeID)=$empID) AND (tblRequest.RequestDate)=#".$reqDate."#)";
And it got me working.

That is really interesting to me though that inserting was ' ' and criteria was # #.

Thanks Banana
 

Banana

split with a cherry atop.
Local time
Yesterday, 18:12
Joined
Sep 1, 2005
Messages
6,318
Wow, no wonder you were confused! That's mighty inconsistent of JET to accept '' for inserts but throw up a tantrum when you use it for criteria.

But yes, it's supposed to be that way- use ## for dates, ''/"" for strings for JET SQLs.

You probably didn't find an example because I suspect it's not very common to use Access and PHP. OTOH, ASP and Access may be a bit more common, but the thing is that the rules you used in pure Access queries within Access application still applies in PHP because you're querying JET, so you have to speak its dialect even if the PHP examples you find usually show using '' for MySQL, but that's because it's speaking MySQL's dialect.

Best of luck! :)
 

Users who are viewing this thread

Top Bottom