Efficient way to display multiple fields of MySQL table into a webpage

[Jagand]

I have some data in MySQL table. When user searches for certain keywords, I must search MySQL table for those keywords and provide results to users. When I am providing those results, I want to display few additional columns so that user can get an idea of how data looks like. My requirement is something common in today's webpages - for example in GMail where data is fetched from database and is parsed into multiple columns such as author, subject, date written etc. What is the most efficient way to code such requests. Below were what I could think but felt that they are not very efficient

1. Fetch each row from MySQL table and construct a HTML/CSS based table with the results. This solution is lacky in sense that there will be multiple requests and responses going between MySQL and webpage
    
   
2. Get each row from database and construct an XML. Pass all this to Javascript and write a javascript code to parse the XML before displaying it as HTML/CSS based table. This again seems lacky in sense that code is once creating XML (through MySQL + PHP) and then parsing it in Javascript
   

Given these, I would like to know how would a good programmer address the problem of displaying data from MySQL into front end with minimal requests and yet without lot of constructing and parsing information.

 

Thank you in advance for any suggestions you can offer

[gizmola]

Either of your solutions is. Neither one is non-standard nor inefficient.

 

In the case of #1, it seems you misunderstand the nature of a php script. The fetching occurs between the server and mysql, so there are not multiple "requests". There is a single request (the query criteria) and the server executes the query, fetches the rows, and returns the response (the completed page with table).

 

The approach chosen is often driven by the nature of the client. If the client is heavy on javascript then an ajax call is typically preferred. In terms of pure delivery of the data, xml has a lot of overhead, and many people opt for json instead.

 

Depending on the possible number of results, many people want to implement some sort of pagination. Under the covers this typically depends on use of the mysql LIMIT clause. That way, a search that returns 5000 rows will not have to build a 5k row table before it could display data to the user.

 

Unless you're really talking about pagination, I'm not sure what your efficiency concerns are.

[Zane]

I am participating in this question mainly because I too am interested.

 

I like the idea of having a XML/JSON file created. What I wonder though, what would be the method with the most integrity, etcetera. My idea is that you could create a new JSON file per session. If the user uses the search bar more than one, the JSON file is just overwritten since the file would be named using their session_id(). Then, somehow I would need to delete all of these files systematically. It seems a chrontab could do this the best?.

 

I am moving this to Application Design btw.

Edited February 6, 2013 by Zane

[Jagand]

Thank you both. I want to place a lot less stress on PHP but with what you said, it looks like I will not be stressing the server a lot using case #1 if I happen to display just 10 - 20 results at a time. I am a newbie to programming and I will learn the concepts of JSON before I choose between option 1 and 2.

[Jagand]

By the way, can JSON be turned off in a browser the way Javascript can be turned off?

[Jessica]

JSON is a notation - not something that can be turned on and off. If the user has Javascript turned off you can't use Javascript. JSON is for Javascript.

[Jagand]

Got it, Thank you