Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    Question Unanswered: php5 - apache - ubuntu server - MS Access Database

    Hello,

    Strange question, i know, but i can't find the solution.

    Situation:
    We have ubuntu webserver with apache, php5, mysql for running our webbased software.
    We also have some kind of a logging system on one of our external machines. Those logs are stored in a MS Access Database ...

    Now, we would like to use those data into our web based system; just read only, doing some querys on that remote Access Database (so we always want to see the live data).

    What i've done;
    - I've installed & configured ODBC on the Ubuntu server
    - created simple html page that shows the columns i need (this works)

    but now, i would like to filter, sort,... the log file... And that doesn't work...

    my php code to fetch the complete table (this works!):
    PHP Code:
    $conn=odbc_connect('APN','****','');
    if (!
    $conn)
      {exit(
    "Connection Failed: " $conn);}
    $sql="SELECT DateTime, Location, Who FROM Log";
    $rs=odbc_exec($conn,$sql);
    if (!
    $rs)
      {exit(
    "Error in SQL");}
    echo 
    "<table border='1'><tr>";
    echo 
    "<th>Datum - Tijd</th>";
    echo 
    "<th>locatie</th>";
    echo 
    "<th>Wie</th></tr>";
    //sort($rs,0);
    while (odbc_fetch_row($rs))
    {
      
    $datetime=odbc_result($rs,"DateTime");
      
    $location=odbc_result($rs,"Location");
      
    $who=odbc_result($rs,"Who");
      echo 
    "<tr><td>$datetime</td>";
      echo 
    "<td>$location</td>";
      echo 
    "<td>$who</td></tr>";
    }
    odbc_close($conn);
    echo 
    "</table>"
    When i add ORDER BY to my query; i get the folowing error:
    Warning: odbc_fetch_row() [function.odbc-fetch-row]: No tuples available at this result index in /var/www/...

    my new query:
    PHP Code:
    $sql="SELECT DateTime, Location, Who FROM Log ORDER BY DateTime";  //also tried it with the Location & Who 
    The Group by clause also doesn't work
    The Where clause works

    Anyone who can help me out?
    Last edited by lowagie; 09-06-07 at 07:09.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can't give a definitive answer but Im very wary of using a reserved word datetime as a column name.. its entirely possible that that may be causing grief
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Posts
    4
    Quote Originally Posted by healdem
    can't give a definitive answer but Im very wary of using a reserved word datetime as a column name.. its entirely possible that that may be causing grief
    Hy,

    DateTime is the name off the column, it's used by that external program, so i can't change that...

    But it's strange that i could do a select on that column, and not an order by...

    I've tried it with table Cardholder and same problem here, when i add the order by clause, i get the warning, and nothing in my table...
    Warning: odbc_fetch_row() [function.odbc-fetch-row]: No tuples available at this result index in /var/www/... on line 17

    PHP Code:
    $conn=odbc_connect('APN','****','');
    if (!
    $conn)
      {exit(
    "Connection Failed: " $conn);}
    $sql="SELECT FirstName, LastName, CardNumber FROM Cardholder ORDER BY LastName";
    $rs=odbc_exec($conn,$sql);
    if (!
    $rs)
      {exit(
    "Error in SQL");}
    echo 
    "<table border='1'><tr>";
    echo 
    "<th>Voornaam</th>";
    echo 
    "<th>Familienaam</th>";
    echo 
    "<th>Kaartnummer</th></tr>";
    //sort($rs,0);
    while (odbc_fetch_row($rs))
    {
      
    $firstname=odbc_result($rs,"FirstName");
      
    $lastname=odbc_result($rs,"LastName");
      
    $cardnumber=odbc_result($rs,"CardNumber");
      echo 
    "<tr><td>$firstname</td>";
      echo 
    "<td>$lastname</td>";
      echo 
    "<td>$cardnumber</td></tr>";
    }
    odbc_close($conn);
    echo 
    "</table>"
    Last edited by lowagie; 09-06-07 at 07:12.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Doesn't it need to be in inverted commas?
    Not a string but a column specification.
    i.e. ORDER BY "LastName" ?
    Can't remember what MSAccess likes but I know you can use `` in MySQL to specify column names. Perhaps this is the issue?

  5. #5
    Join Date
    Jun 2007
    Posts
    4
    ' or " or ` makes no diff!

    right now i have the question if it is even possible to do some querys like that... i use MDBtools (http://mdbtools.sourceforge.org) and don't really know if that does what i want it to do...

    Anyone any help on this issue?

    otherwise i'll have to create a copy of this table in my MySQL db, copy the complete table into my MySQL db and the work with the MySQL db to get my querys...
    Any better solution?

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I believe there is a bug/caveat in PHP5 and odbc. I refer you to the documentation : http://uk.php.net/odbc_fetch_row

    Take note of the comment by "eolscr at gmail dot com" on 07-Apr-2005 03:05 . There appears to be an issue with the row returns. Your sql appears to be fine as you're not getting an "Error in SQL" returned.

    edit: connect to MSAccess using OpenOffice and run the query directly through that. You should find that it's returning your resultset fine (with the ORDER BY clause).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •