I’m trying to learn query syntax and attempting an exercise to create a combination of the control PHP, SQL and HTML (all on one page) that’s required to display data in table form on a resulting web page. The goal is to have only the most basic code necessary to do the job. The database data I’m trying to display contains rows of three criteria that are results of web-form entries. At some later point I will add (what for me would be) the additional complexity of writing, placing and referencing a separate PHP control file. Once I can be sure syntax is correct, I think it would be easier for me to add the next step with separate PHP file. I have been reading lots of tutorials and looking at similar code examples, but nothing seems to address these needs from start to finish, or it includes references that don’t apply. The query code I came up with isn’t pretty, but I think I have successfully connected and accessed the database because when I change field references it produces an error on the resulting web page that complains that there is an “Unknown column in ‘field list’”. The page normally displays only the formatted html table lines and column headings. I have changed the db reference a bit in the select statement to post this code online. I think the problem is that I haven’t been able to figure out syntax needed to load the database data into the rows. The current version shows field names surrounded by curly brackets in the html table, but I’m sure this is an oversimplification, or I’m missing the bit of code that directs these references to the database. I could use a little guidance on this link.
Does anyone have suggestions on the correct query syntax in this application?
Thanks for your time.
Thank you for taking the time to help me learn! I’ll try to insert your code suggestions in the right places to get my query working.
Best Regards,
Mike
I now have the query displaying data pulled from its database on a web page. Thanks again for your help. I’ve been working a few hours on getting data rows to display properly though, and don’t seem to be able to make further progress. Another small boost would be appreciated if you, or anyone else would be so kind. As suggested in the code I first posted, the table is to display each row (containing the three criteria) with a horizontal rule below each data row. There are also 2 thicker horizontal rules at top and bottom of the table to help delineate the table. With the new code version I just submitted the latest form entry from the database (containing those 3 fields) displays in its row properly, but all the remaining entries (sets of 3) display below the thicker bottom horizontal rule that is supposed to mark the bottom of the table. Each additional entry set (beyond entry #1) also displays its three criteria vertically instead of horizontally across its row. Any ideas for correcting this are appreciated.
Thanks for your time.
Your missing a <tr> start tag just before your first silver backgrounded strip. And unless you are actually using the thead, tbody, tfooter tags i’d loose those too.
HOWEVER, using a blank data table row just to get a line isnt very hot , consider using CSS on the table elements of the table to get your seperations.
That worked great! You gave me a big head-start on learning/using CSS which is one of my next goals. That result looks better than mine. Awesome, thanks! Up to this point I was happy just to connect and have db results actually display on the result page, but it’s now getting close to what we need for the final result. When the final webpage is coded I hope to be able to limit the results displayed and have been working on the select statement to that end. The goal is to have a concise grid display table on the resulting web page that shows only the latest entry for each of the users with the 3 criteria on each row, (– text_1 {name} – text_2{location} – recordtime{time of submission}). There would be only one row entry per name, and only the location and time of submission would change with each update. The code included below does return results for all the users, but in the form of a long list of all entries for each user on multiple pages instead of just updating a single row for each user. It seems like I need to use “DISTINCT” and/or “LIMIT” in the string, but my attempts at this haven’t produced the desired results. Do you have any ideas on this?
Thanks so much for your time and continued help.
Best regards, Mike
$myquery = "select text_1, text_2, recordtime from ".$conf->site_db."db_myformdata order by recordtime DESC";
Please change your database names to something more meaningfull. If the value held is a name, then call the field ‘name’ it will make your code much easier to read 6 months down the line and for anyone to make sense of it
So assuming you change your names
your query needs to group the results by ‘name’ (because you only want each person once), and then select the maximum ‘recordtime’ from each group.
SELECT Name, Location, MAX(Recordtime)
FROM table_t
GROUP BY Name
small caveat: the use of MAX may depend on how you are storing your ‘recordtime’ data
Thanks Mandes, the “GROUP BY” worked fine, but for some reason the the “MAX” changes date/time for all rows displayed to the time the query was run (all today’s date). As I played with other areas of the code and removed a some modifications in date/time formatting that have been working fine on all versions, date/time disappeared entirely from the display (while using MAX in the select statement). I tried removing MAX and adding “SORT BY” along with the “GROUP BY”, but that resulted in error.
It is a time-stamp of when the user web-form data was submitted.
IE: 2011-03-02 - 18:08:29 is returned to the front end display if no additional formatting is employed in the query.
Thanks for the new idea, but the result is the same. Either all rows display today’s date or (if date/time formatting is changed in php) nothing is displayed in the date/time column.
, consider using CSS on the table elements of the table to get your seperations.
Up to this point I was happy just to connect and have db results actually display on the result page, but it’s now getting close to what we need for the final result. When the final webpage is coded I hope to be able to limit the results displayed and have been working on the select statement to that end. The goal is to have a concise grid display table on the resulting web page that shows only the latest entry for each of the users with the 3 criteria on each row, (– text_1 {name} – text_2{location} – recordtime{time of submission}). There would be only one row entry per name, and only the location and time of submission would change with each update. The code included below does return results for all the users, but in the form of a long list of all entries for each user on multiple pages instead of just updating a single row for each user. It seems like I need to use “DISTINCT” and/or “LIMIT” in the string, but my attempts at this haven’t produced the desired results. Do you have any ideas on this?