Page 1 of 1

Smartening up my php/html/mysql interaction...

Posted: Sat Oct 17, 2009 3:42 pm
by dkperez
With help from in here I now have an html form that lets me retrieve data from MySQL and display the information on the form. BUT, I'm not happy with the methodology I used and am hoping y'all can smarten me up...

Currently the process is:

form displays empty
user hits "find" button then fills in information in one or more fields
user hits "submit" button and php code connects to the database and returns the first record of the set
user hits "next" button and a counter is incremented and the next record gets returned

my query is "SELECT * FROM <table> WHERE <predicate> limit <currentrow> , 1"

repeat for entire bunch... This works but it's gross..... It means making a round trip to the database EVERY time the user wants either the next or previous record. The predicate gets set in the find pass, the currentrow gets incremented or decremented based on next or previous button......

Is there a way for me to retrieve all the records and process them in php/html to move through them by button press? Currently, based on advice in here, my php code is at the top of my file with the html below, so every time I press a button the file gets run through. I have a simple "if-then-else" to look at the button and do what's needed, but I figure there's got to be a more efficient way of displaying the retrieved records that getting them 1 at a time.....

In the "old" days this would be a MAJOR bozo nono and I'd what any of my designers that did something this inefficient. Now, with the astronomical slowness of shoving things around the web compare to hitting the database repeatedly maybe it's no longer an issue but I figured I'd ask...

Re: Smartening up my php/html/mysql interaction...

Posted: Sat Oct 17, 2009 4:16 pm
by califdon
Is it your desire to always display only one record? You know, I presume, that you can omit the LIMIT parameter and receive a complete record set that meets the WHERE criteria, then your PHP can display successive records, as many as exist, on the same page. But if you want to display only one row at a time, you will either have to go back to the database for each row (which isn't usually considered a particular problem) or get all the rows and format them as a multidimensional array that is sent to the browser the first time, then processed with Javascript. In theory, you could store the array in session variables in lieu of re-querying the database, but I don't think that's often done. Unless you're dealing with a really enormous database and/or millions of queries an hour, the successive queries shouldn't be thought of as a problem, I'd say.

Re: Smartening up my php/html/mysql interaction...

Posted: Sat Oct 17, 2009 9:56 pm
by dkperez
The application is a single-record display form. I'm dealing with small numbers - in the hundreds or possible a couple thousand. But, I guess it's good to know banging on a database isn't as bad any more as it's always been...

Bu, at the same time, it's incredibly disappointing that to do basic, trivial, retrieval and display of records now requires at least FOUR products (html, php, javascript, and AJAX)...

I guess I'll just leave the code as it is...

Re: Smartening up my php/html/mysql interaction...

Posted: Sat Oct 17, 2009 11:31 pm
by Mirge
dkperez wrote:The application is a single-record display form. I'm dealing with small numbers - in the hundreds or possible a couple thousand. But, I guess it's good to know banging on a database isn't as bad any more as it's always been...

Bu, at the same time, it's incredibly disappointing that to do basic, trivial, retrieval and display of records now requires at least FOUR products (html, php, javascript, and AJAX)...

I guess I'll just leave the code as it is...
Really it's only 3... HTML, PHP, Javascript. You don't have to use XML to write "Ajax" applications. But I'd just do without using Javascript to load new content w/out a page refresh.

Re: Smartening up my php/html/mysql interaction...

Posted: Sun Oct 18, 2009 1:42 pm
by califdon
As Mirge said, Ajax isn't a separate language, it's just the technique of using Javascript and PHP (or any server-side script) together to achieve asynchronous data transfer. And the reason there are several technologies is that you're not working in a defined environment anymore--the web is an amorphous collection of computing and communicating systems, and you have to deal with servers and clients and transmission modes, etc. So I think it's amazing that you can do so much with so few tools, I really do.

But yes, database technology is such that most servers should be able to process hundreds of queries per second. Now, if you have thousands of people trying to each process hundreds of queries per second against the same table, you would surely have a problem. That's why the programmers at Google and Amazon get the big bucks, to figure out how to handle that realm of operations. But returning for another query every few seconds is totally inconsequential.