SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...I have spent ALL DAY trying to figure a work-around for this one. I've been creating a message board for the company I work for using PHP and MS Access. I want to limit the number of posts per page to 10 (or whatever) and dynamically add a "page 1,2,3,4,ect." to the bottom of the page to navigate between the different pages.

    freddydoesphp (the php GOD) has offered a lot of examples to do this using MySQL with a "SELECT bla bla bla LIMIT $offset,$limit" statement. The problem is that odbc (ie.MS ACCESS) doesn't recognize the LIMIT SQL function. I've spent all day trying to find a php work-around or odbc alternate to this problem with out any success. The best I found was a Forum Post which said odbc doesn't accept the LIMIT function, but gave no solution to the problem.

    If anyone has any solutions to this problem, I would be eternaly in your debt.
    Joe Eliason
    Just a dog learnin' PHP from cat.

  2. #2
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just me ranting some more

    freddydoesphp was giving examples of mySQL ... using SQL query language.. ODBC's connectivity language is slightly different, and thats why you cant apply his exact code .. again, i'm not a microsoft fan, so i wouldnt be able to help you there ..

    i can however be totally useless and tell you to move to mySQL .. hehe... dont mind me ...

    anyhow, i will give you a tip: ... post in the ASP forums located at: http://www.sitepointforums.com/forum...php?forumid=35

    ASP users generally connect to Access databases and would be able to provide you with more detailed information on how to replicate SQL LIMIT capabilities with ODBC
    cogito, ergo sum

  3. #3
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Vinay
    just me ranting some more

    freddydoesphp was giving examples of mySQL ... using SQL query language.. ODBC's connectivity language is slightly different, and thats why you cant apply his exact code ..
    I realize this...I stated this in my first post.

    Thanks for the suggestion to post in the ASP Forum. Hopefully they'll have some better advice on a work-around.
    Joe Eliason
    Just a dog learnin' PHP from cat.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Joe

    to satisfy my own curiosity as well as freddy's we looked around last night for something, all we could find was the MaxRecords attribute in Access, though we couldn't figure out how to use it. I tried to create my own Access database and use odbc and php to connect, but I couldn't get it working, anyways hope that helps, Good Luck!
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats like Cold Fusion. There is no SQL component you specify in your CF code when calling the query.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  6. #6
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freddy, can your cat think of any php work arounds. Is it possible to put the messages in an array and then slect from the array which messages to show??

    Or could I code the database to count the messages in an array and list the array number in a new field that could be accessed in PHP?

    I know you're not an ACCESS programmer, but if you have any suggestions...
    Joe Eliason
    Just a dog learnin' PHP from cat.

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure I would loop through the resultset and add each record to an array using an array key starting at 1. I don't know the code for lopping through a result set using odbc but inside that loop so try something along these lines


    if(!isset($start)) $start = 1;

    $limit = 10;

    $i = 1;
    $q = odbc_exec($conn_id,"select field1,field2 from table_name");

    while(odbc_fetch_row($q)){
    $j = 1;
    $records[$i] = array(
    "field1" => odbc_result($q,$j),
    "field2" => odbc_result($q,++$j)
    );
    }

    reset($records);

    for($i=$start;$i<$start + $limit;$i++) {
    print $records[$i]["field1"]."<br>";
    }

    //then just pass $start + $limit and the search criteria on to the next pages
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll give it a try and let you know how it works. I know that with a BIG database this would be VERY slow. But since it's just our internal Intranet, it should be fine.

    I may just go to a mySQL database. The only beef I have with mySQL is I HATE working from a C prompt. Even though I have developed an Administrative page to do all my editing and such, I just enjoy having the option of opening up the database and having a nice interface to work in. Plus I have a couple semesters of Access from college and would like to get into more Access programming on the side. But maybe if I start working with mySQL more and become more familiar with it I'll use it more.
    Joe Eliason
    Just a dog learnin' PHP from cat.

  9. #9
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've never worked from a prompt when working with a MySQL DB.

    I basically just use phpmyadmin which has a nice web interface.

    http://www.phpwizard.net/projects/phpMyAdmin/

    and besides. If you're on a *nix box anyways I don't know why you'd want to use access. MySQL is a superior database.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  10. #10
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I've found the answer. Actually I found a couple of answers. I've found 2 ways to make this work. I'll post the one I feels works the best.

    Taking freddy's lead with his example, this is what I came up with.

    <?php
    //Connect to database
    $dsn = "msgBoard";
    $dbcnx = @odbc_connect("$dsn","", "");

    //Count the number of total messages
    $sql = "SELECT COUNT(*) as totalnum FROM messages";
    $query = odbc_do($dbcnx,$sql);
    $totalnum = odbc_result($query,'totalnum');

    //Number of messages per page
    $msgCount = 3 ;

    //Set the offset to 0 if not already set
    if (!isset($offset)) $offset = 0;

    //Set the default number of messages per page limit
    if (!isset($limit)) {
    if ($totalnum <= $msgCount) {
    $limit = $totalnum;
    }
    else $limit = $msgCount;
    }

    //This sets up variables for what message to start on
    //and which to stop on per page

    $start = $offset+1 ;
    $stop = $offset + $limit;

    // Select the message database
    $sql = "SELECT ID, messageText, messageDate FROM messages ORDER BY ID DESC";
    $messageRow = odbc_do($dbcnx,$sql);

    /********Post Messages********/

    //Use the for loop to setup how many messages to post on each page
    for($i=$start;$i<=$stop;$i++) {

    //This is the variable to use to select the starting column in the selected row
    $j=1;

    //This is very important. This checks to see if a row exists. With out this you
    //will print the last message several times until the message limit for the page is reached

    if(odbc_fetch_row($messageRow,$i)) {

    //Import the messages into an array
    $records[$i] = array(
    "ID" => odbc_result($messageRow,$j),
    "message" => odbc_result($messageRow,++$j),
    "date" => odbc_result($messageRow,++$j),
    );
    reset($records);

    //Capture the array values to variables
    $messageid = $records[$i]["ID"];
    $messageText = $records[$i]["message"];
    $date = $records[$i]["date"];

    //Or print out the array values
    print $records[$i]["ID"] ."-----";
    print $records[$i]["messages"] ."-----";
    print $records[$i]["date"] ."<br><br>";
    }
    }
    ?>

    This is the bare-bone of the code. Using this though, one should be able to apply it to whatever code you are putting together.

    To further improve the code visit http://www.sitepointforums.com/showt...threadid=14915. freddy does an excelent job showing us how to put dynamic page numbers to the bottom of the page to navigate from page to page.

    I sincerely hope this helps anyone out there not using MySQL and runs into this problem. This will work with any database since it is only PHP code doing all the work and not the SQL language.

    Again...I thank freddy for all his help.

    PS...I have since switched to a MySQL database though and have installed MySQL odbc drivers so I can link the tables into MS ACCESS for printing out reports and easier management of the data. It works pretty good.

    PPS..if any one cares how I did it the other way, they can email me and I'll give them the alternate example of how to do the same thing.
    <Edited by HotDog on 01-30-2001 at 07:40 PM>
    Joe Eliason
    Just a dog learnin' PHP from cat.

  11. #11
    SitePoint Zealot
    Join Date
    May 2000
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just wondering...

    Isn't this inefficient?

    Doesn't your program download ALL the results from the database and then select the required info? So its wasting bandwidth, processing time...

    I had the same problem with MS SQL Server. I had to create temporary tables to overcome it.

    Arpith


  12. #12
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you come up with a better idea for ODBC calls from php, I am sure we would all love to hear it.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  13. #13
    SitePoint Zealot
    Join Date
    May 2000
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I don't unfortunately :-(

    Wonder how all the other sites do it though....

  14. #14
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by arpith
    Just wondering...

    Isn't this inefficient?

    Doesn't your program download ALL the results from the database and then select the required info? So its wasting bandwidth, processing time...
    I don't understand why you think this is inefficient?? I don't download the whole table at once. All I'm doing is looking for which row to start pulling data and then where to stop. If you notice in the for loop, I call each row individually from the loop. The other rows are not called. It wouldn't use any more or less bandwidth than calling any array, weither it be mysql_fetch array or odbc_fetch_row or even the good old LIMIT variable in SQL.
    Joe Eliason
    Just a dog learnin' PHP from cat.

  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is somewhat inefficient as the DB has to query all of those rows, even if you are not going to use it. I know some database management systems will wait for you to continue fetching things even if you drop out of a loop in PHP.

    If you are sure they are sequential numbers you could do something like:
    Code:
      SELECT ID, 
             messageText, 
             messageDate 
        FROM messages 
       WHERE ID BETWEEN( $start, $stop )
    ORDER BY ID DESC
    But, for a small-time use like you are doing it probably isn't any big deal, unless the messages table has a whole lot of rows.

  16. #16
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tip Matt. I'll give it a try and let you know if that works. I'd rather do my row limit by SQL anyway as I do believe it would be faster in the long run with a larger table.
    Joe Eliason
    Just a dog learnin' PHP from cat.

  17. #17
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what I tried:

    $sql = "SELECT ID, messageText, messageDate FROM messages WHERE ID BETWEEN $start AND $stop ORDER BY ID DESC";

    This works....but doesn't work. It selects the IDs between $start and $stop, but the ID field is AUTONUMBERed. So if a previous message has been deleted and there is a skip in the sequence, another message doesn't fill the gap.

    For example, if I have BETWEEN 1 AND 5 but message 3 and 4 have been deleted and there is no ID 3 and 4 only messages 1, 2 and 5 show up, not 5 messages. I acutally thought of this before, but in order for this to work, you would need to program the database to have a field that would count the row sequence and have it stay the same. So if a row was deleted, the next one would move up in the sequence. I talked to an ACCESS friend of mine, and he wasn't sure how to do this efficiently on the database side and thought it better on the server side as I have done. But if it could be done on the DB side, it think it would be better and faster. I don't know of any SQL statement to look at row sequence and then use a BETWEEN $ AND $ other than the LIMIT statement in MqSQL.

    I also thought about trying to put the rows in an array with a COUNT SQL statement and having the messages selected from that, but I don't know if that is possible or if it is how to do it.

    Matt, I read your post in the VB vs UBB and you said that Sybase had the same problem. What did you use as a work-around for that?? (If you are able to share)
    Joe Eliason
    Just a dog learnin' PHP from cat.

  18. #18
    SitePoint Zealot
    Join Date
    May 2000
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Originally posted by HotDog
    $sql = "SELECT ID, messageText, messageDate FROM messages ORDER BY ID DESC";
    $messageRow = odbc_do($dbcnx,$sql);
    ...
    if(odbc_fetch_row($messageRow,$i))
    ...
    I don't know php :-(, I have a question.

    In the above statement, does PHP load ALL the rows (of the result of the SQL query) from the database server into memory and then print rows START to STOP, or does it only get the rows START to STOP from the database server?

    Thanks,
    Arpith


  19. #19
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by arpith
    I don't know php :-(, I have a question.

    In the above statement, does PHP load ALL the rows (of the result of the SQL query) from the database server into memory and then print rows START to STOP, or does it only get the rows START to STOP from the database server?

    Thanks,
    Arpith
    This is actually a pretty good question, and I don't know. But if PHP does query the whole database and store it in memory before selecting rows $stop to $start, then every time you say mysql_query("SELECT * FROM db") you are doing the same thing and run into the same problem of having to query the whole database, right??

    If the information stays in the database until PHP actually calls any data, then this is a moot point. But if not, then a more effecient way would be to come up with a WHERE statement to limit the database query. But as I have stated before, I don't know how query ROW sequence without some sort of refference data. If I could say WHERE ROWS BETWEEN 5 AND 10 that would be great!!

    Like I said, if someone else has found a SQL work around for this, I'd love to know it. I think this would be important to know how to do since MySQL is the only database (that I know of) that uses the LIMIT feature. So if there is another way of doing it in a SQL statement, or at least be able to limit you query on the database, it would enable other databases to emulate the LIMIT feature.

    But once again, if PHP doesn't hold the whole table query in memory and only selects the rows as they are actually called, then it really shouldn't make any difference, right??
    <Edited by HotDog on 01-31-2001 at 03:53 PM>
    Joe Eliason
    Just a dog learnin' PHP from cat.

  20. #20
    SitePoint Enthusiast einstien_ii's Avatar
    Join Date
    Jan 2001
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey! Hotdog,

    I just thought of your PHP + MS Access Combo. I am planning to use them too, and if it ain't too much trouble, can I have a link to this Message Board which your building, so as to get a look at it?

    Regs,

    Krishna Narayanan
    einstien_ii@zeenext.com
    http://www.talofians.net


  21. #21
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Too funny...I've for the most part converted it over to a MySQL database...But I do have some original code from the ACCESS database and some I've been playing with. For the most part all you would need to do is change the MySQL function calls for ODBC calls.

    Since the message board is for our corporate Intranet, it isn't accessable from the web, but I'd be more than happy to email you what I have so far. I suppose I could convert it all to an ACCESS database before I send it. It would take me an hour or so, but I'd like to have an ODBC example around anyway. My work uses IBM AS/400s as their main server with DB2 as the database and you access DB2 from PHP through ODBC. So if I ever convert the Intranet to the AS/400, I'd need an ODBC version of my PHP anyway.
    Joe Eliason
    Just a dog learnin' PHP from cat.

  22. #22
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by HotDog
    This is actually a pretty good question, and I don't know. But if PHP does query the whole database and store it in memory before selecting rows $stop to $start, then every time you say mysql_query(&quot;SELECT * FROM db&quot you are doing the same thing and run into the same problem of having to query the whole database, right??

    If the information stays in the database until PHP actually calls any data, then this is a moot point. But if not, then a more effecient way would be to come up with a WHERE statement to limit the database query. But as I have stated before, I don't know how query ROW sequence without some sort of refference data. If I could say WHERE ROWS BETWEEN 5 AND 10 that would be great!!

    Like I said, if someone else has found a SQL work around for this, I'd love to know it. I think this would be important to know how to do since MySQL is the only database (that I know of) that uses the LIMIT feature. So if there is another way of doing it in a SQL statement, or at least be able to limit you query on the database, it would enable other databases to emulate the LIMIT feature.

    But once again, if PHP doesn't hold the whole table query in memory and only selects the rows as they are actually called, then it really shouldn't make any difference, right??
    &lt;Edited by HotDog on 01-31-2001 at 03:53 PM&gt;
    Well, I know the database *does* do tons of work behind the scenes to keep it working, and I'm pretty sure PHP does some trickery as well.

    For instance, if I do "SELECT * FROM post" on our 1.2 million row post table PHP immediately DIES without spitting out a single row. I can only attribute this to some internal buffer in PHP getting filled up as it queries the rows (even before I execute the fetch_array( .. ) function) -- perhaps it is the memory cap set in the ini file? In any rate, the database has to set up temporary tables and such in memory to handle the result set, so any time you can limit it that would be a good thing. The "LIMIT x, y" syntax in MySQL is non-standard SQL which is why it only works in MySQL. I know MS-SQL server has similar syntax (also non-standard), as well as Oracle and Sybase.

    For your "BETWEEN( x, y )" problem I can only suggest this:
    Every time you delete a record, reload all of the data into your table, essentially removing the "holes" in the system (as long as you donít mind the ID being reset that is). The way to do this (if Access supports SELECT INTO) would be (GO is equivalent to ; at the end of MySQL statements):
    Code:
    SELECT *
    INTO temp_messages
    FROM messages
    GO
    
    DELETE messages
    GO
    
    INSERT INTO messages
    SELECT * 
    FROM temp_messages
    GO
    If Access does not support the SELECT INTO SQL extension, then you will have to do something like:
    Code:
    CREATE TABLE temp_messages ( blah )
    GO
    
    INSERT INTO temp_messages
    SELECT * FROM messages
    GO
    
    DELETE messages
    GO
    
    INSERT INTO messages
    SELECT * 
    FROM temp_messages
    GO
    That way you can be sure that you are grabbing the correct amount with the BETWEEN( Ö ) clause.

    Or, if you donít want to mess with re-numbering them, you can take a look at odbc_fetch_row( Ö ); in the PHP manual (http://www.php.net/manual/en/function.odbc-fetch-row.php) to see how that works, but I think because the ODBC drivers arenít API calls directly to the SQL server like the other PHP database functions are, you might still have the same database result set overhead as before.

  23. #23
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's definitely a way of limiting results using ODBC - I've seen it somewhere before but I cant for the life of me track it down

  24. #24
    SitePoint Zealot HotDog's Avatar
    Join Date
    May 2000
    Location
    Salt Lake City, UT
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR

    For instance, if I do "SELECT * FROM post" on our 1.2 million row post table PHP immediately DIES without spitting out a single row. I can only attribute this to some internal buffer in PHP getting filled up as it queries the rows (even before I execute the fetch_array( .. ) function) -- perhaps it is the memory cap set in the ini file?
    What if you were to use a some sort of odbc_prepare to setup what you wanted to query first, and once you have all the parameters set, do the execution. Would you still run into the same problem??


    For your "BETWEEN( x, y )" problem I can only suggest this:
    Every time you delete a record, reload all of the data into your table, essentially removing the &quot;holes&quot; in the system (as long as you donít mind the ID being reset that is). The way to do this (if Access supports SELECT INTO) would be (GO is equivalent to ; at the end of MySQL statements):
    Code:
    SELECT *
    INTO temp_messages
    FROM messages
    GO
    
    DELETE messages
    GO
    
    INSERT INTO messages
    SELECT * 
    FROM temp_messages
    GO
    If Access does not support the SELECT INTO SQL extension, then you will have to do something like:
    Code:
    CREATE TABLE temp_messages ( blah )
    GO
    
    INSERT INTO temp_messages
    SELECT * FROM messages
    GO
    
    DELETE messages
    GO
    
    INSERT INTO messages
    SELECT * 
    FROM temp_messages
    GO
    That way you can be sure that you are grabbing the correct amount with the BETWEEN( Ö ) clause.
    I'll give this a try and let you know how it works. My only question is wouldn't this tie up the db server while it recalculated the ID numbers. And if so, what would be the time frame with a long table (1-2 million rows, not that my humble message board will get that big). To so this I suppose I would need to lock the table until the update is complete, right.

    Or, if you donít want to mess with re-numbering them, you can take a look at odbc_fetch_row( Ö ); in the PHP manual (http://www.php.net/manual/en/function.odbc-fetch-row.php) to see how that works, but I think because the ODBC drivers arenít API calls directly to the SQL server like the other PHP database functions are, you might still have the same database result set overhead as before.
    [/B]
    I actually used this function in my working example above. I have it in a FOR loop to count through the rows setting up a $start and $stop variable for the row selection, then passing a $start+10 variable on to the next page to where to start counting the next set of rows. It works great, but if PHP loads the whole query into memory before selecting anything and executing anything, then with a large row table it would bogg down and crash out. But if php doesn't do this, or if we can work around it, why not just do it that way?
    Joe Eliason
    Just a dog learnin' PHP from cat.


Bookmarks

Posting Permissions

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