Syntax help needed

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.

<?php>
include_once("connect.php");
$conf = new Config();
$myconn = mysql_connect($conf->host,$conf->user,$conf->password) or die(mysql_error());
mysql_select_db($conf->db,$myconn);

$myquery = "select text_1, text_2, recordtime from ".$jconf->site_db."db_myformdata order by recordtime DESC";

$result = mysql_query($myquery,$myconn) or die (mysql_error());

$style = "";
$style .= "
table.cf_listing {
margin-bottom: 12px; font-size: large
}
";

if ( $style) {
$doc =& Factors::getDocument();
$doc->addStyleDeclaration($style);
}
?>

<table class='cf_listing'>

<thead>
<tr>
<th>Name</th><th>Miles</th><th>Updated</th>
</tr>
<tr>
<td colspan='4' style='height:4px; background:silver;'></td>
</tr>
</thead>

<tfoot>
<tr>
<td colspan='4' style='height:4px; background:silver;'></td>
</tr>
</tfoot>

<tbody>

<tr>
<td STYLE= "font-size: large" width="140" align="center">{text_1}</td>
<td STYLE= "font-size: large" width="140" align="center">{text_2}</td>
<td width="140" align="center">{recordtime}</td>
</tr>

<tr>
<td colspan='4' style='height:2px; background:silver;'></td>
</tr>

</tbody>
</table>
$myquery = "select text_1, text_2, recordtime from "
                  . $jconf->site_db . 
                  "db_myformdata order by recordtime DESC";
 

You have no variable $jconf declared in your code.

You also are not reading the contents of the array that youve stored your data into
$result

You need to parse this array

$sql = mysql_fetch_array($result);

and then output to your HTML using PHP

echo $sql['DB_Field_Name'];

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.


<html>
<body>
<?php
include_once("connect.php");
$conf = new Config();
$myconn = mysql_connect($conf->host,$conf->user,$conf->password) or die(mysql_error());
mysql_select_db($conf->db,$myconn);

$myquery = "select text_1, text_2, recordtime from ".$conf->site_db."db_myformdata order by recordtime DESC";

$result = mysql_query($myquery,$myconn) or die (mysql_error());

$sql = mysql_fetch_array($result);
?>
<H2>
<table width="90%">
<thead>
<tr>
<th align="center">NAME</th>
<th align="center">MILES</th>
<th align="center">UPDATED</th>
</tr>
<td colspan='4' style='height:4px; background:silver;'></td>
</thead>
</H2>

<tfoot>
<tr>
<td colspan='4' style='height:4px; background:silver;'></td>
</tr>
</tfoot>

<?php

while($row = mysql_fetch_array($result))
  {

echo "<tbody>"; 
echo "<tr>
<td><h3> ". $row['text_1'] . "</h3></td>
<td><h3>". $row['text_2'] . "</h3></td>
<td><h4>" . $row['recordtime'] ."</h4></td>
</tr>";	  

echo "<tr>";
echo "<td colspan='4' style='height:2px; background:silver;'></td>";
echo "</tr>";
  
echo "</tbody>";
echo "</table>";
}

?>

</body>
</html>

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 :smiley: , consider using CSS on the table elements of the table to get your seperations.

Example


<html>
<head>
<style>
.styled {
border-bottom: 4px solid double red;
}
.styled th {
border-bottom: 4px solid double red;
}
.styled td{
text-align: center;
font-style: italic;
font-weight: bold;
border-bottom: 1px solid grey;
}
</style>
</head>
<body>
<table class="styled" width="200px">
<tr>
<th>NAME</th>
<th>MILES</th>
<th>UPDATED</th>
</tr>

<tr class="tabledata">
<td>data1</td>
<td>data2</td>
<td>data3</td>
</tr>   
 <tr class="tabledata">
<td>data4</td>
<td>data5</td>
<td>data6</td>
</tr>   
<tr class="tabledata">
<td>data7</td>
<td>data8</td>
<td>data9</td>
</tr>   
<tr class="tabledata">
<td>dataA</td>
<td>dataB</td>
<td>dataC</td>
</tr>   
 
</table>
</body>
</html>

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! :slight_smile: 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 :wink:

So assuming you change your names :smiley:

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

Also location is ambiguous unless a single location exists per a name.

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.

what type of data type is ‘recordtime’ in your database

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.

try


SELECT Name, Location, MAX(Recordtime) AS Last_Date
FROM table_t 
GROUP BY Name

when you echo out the record time use the variable
$xxx[‘Last_Date’]

if thats not working for you ask the guys over in the mySQL forum, theyre much hotter on query problems.

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.

Wait, sorry. I didn’t see your notation about changing the echo. I’ll give that a try.

With the adjusted echo, the result is nothing displayed in the date/time column.

Can you post your PHP code for the query and table

<html>
<head>
<style>
.styled {
border-bottom: 2px solid double black;
}
.styled th {
border-bottom: 2px solid black;
}
.styled td{
text-align: center;
font-style: italic;
font-weight: bold;
border-bottom: 1px solid gray;
}
</style>
</head>
<body>
<table class="styled" width="350px">
<tr>
<th>NAME</th>
<th>MILES</th>
<th>UPDATED</th>
</tr>

<?php
while($row = mysql_fetch_array($result))
  {

  echo "<tr>";
  echo "<td>" . $row['text_1'] . "</td>";
  echo "<td>" . $row['text_2'] . "</td>";
  echo "<td>" . $xxx['Last_Date'] . "</td>";
  echo "</tr>";
  
}
?>   
 
</table>
</body>
</html>

<?php

$myquery = "select text_1, text_2, MAX(recordtime) AS Last_Date from ".$website_db.“webform_db group by text_1”;

$result = mysql_query($myquery,$myconn) or die (mysql_error());

$sql = mysql_fetch_array($result);
?>

the xxx was supposed to be replaced by your variable name !!

 
echo "<td>" . $xxx['Last_Date'] . "</td>";

change to

 
echo "<td>" . $row['Last_Date'] . "</td>";

Oh, that was dumb of me not to realize that! The date/time data is displayed now. Thanks!