rbragg

Sure. I started out with 2 tables but normalized to 1: actionID name subjectID subject msg ip timestamp 1 robin 1 test of onetable test 2007-04-17 10:38:22 2 robin 1 reply to test of onetable test 2007-04-17 10:51:41 3 Betty 2 Peaches We are losers. 2007-04-17 14:08:22

I've tried defining $var as $first but without the GROUP BY clause in $queryLastReply I get this message: "The last reply query failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause"

That makes good sense. I'm just unsure of how I would use $var in the loop?

"She", by the way. You are exactly right about how I have it set up. I was afraid I was being unclear. I had started a WHERE clause in $queryLastReply but had no idea where I was going with it.

Thanks for all of your replies! I initially tried this and it gives me the first post in the db rather than the last post in each subjectID.

I am developing a simple messageboard that will list threads. There will be the first post of the thread and directly underneath will be the last reply to that thread. My problem is that instead of the last reply, the last post in the db is being displayed under all of the threads. actionID is the auto incremented primary key. subjectID is an integer that is the same for a post and all of its replies. A new post will have the next incremented integer. <?php include 'dbConnect.php'; # this query groups subjects together and displays the first post of each thread $queryFirst = " SELECT MIN(actionID), name, subjectID, subject, timestamp FROM onetable GROUP by subjectID ORDER by timestamp DESC "; $firstResults = mysql_query($queryFirst) or die('The first query failed: ' . mysql_error()); # this query groups subjects together and displays the last post of each thread to show the last reply $queryLastReply = " SELECT MAX(actionID), name, subjectID, subject, timestamp FROM onetable GROUP by subjectID "; $lastResults = mysql_query($queryLastReply) or die('The last reply query failed: ' . mysql_error()); while ($last = mysql_fetch_array($lastResults)) { $reply = "<b>" . $last['subject'] . "</b> by " . $last['name'] . " at " . $last['timestamp'];; } echo "<form name='form_display' method='POST' action='forumDetail.php'>"; echo "<table width='100%' border='0' cellspacing='2' cellpadding='2'> <tr bgcolor='#616161'><td width='8%'><input name='view' type='submit' class='style3' value='view'></td> <td class='style6'>Message</td><td class='style6'>Started by</td><td class='style6'>Date & Time Started</td></tr>"; while ($first = mysql_fetch_array($firstResults)) { echo "<tr><td align='center' class='style3'>"; ?> <input type='radio' name='selected' value='<?php echo $first['subjectID'];?>'> <?php echo "</td><td class='style8'>"; echo $first['subject']; echo "</td><td class='style3'>"; echo $first['name']; echo "</td><td class='style3'>"; echo $first['timestamp']; echo "</td></tr>"; echo "<tr><td colspan='4' align='right' class='style3'><span class='style7'>last reply: </span>$reply</td></tr>"; echo "<tr><td></td></tr>"; } echo "</table>"; echo "</form>"; mysql_close; ?> How do I get the last reply for each thread (the highest actionID for that subjectID) to display instead of the last reply in the db (the highest actionID in the db)? I thought my query was accurate.

Thanks again - I figured it out.

Thanks for your reply. Wouldn't this require having a while loop INSIDE of another while loop?

I have some results to a query called $firstResults and a while loop to display those results: <?php while($row = mysql_fetch_array($firstResults)) { blah blah blah } ?> This works fine. However, I would like to use the results of another query and display those within the above loop. Is this possible? Here is my second query: <?php $queryLast = " SELECT MAX(actionID), name, subjectID, subject, msg, timestamp FROM onetable GROUP by subjectID ORDER by timestamp DESC "; $lastResults = mysql_query($queryLast) or die('This last query failed: ' . mysql_error()); ?>

I had tried (in short): <?php $dateRange = range('$startDate', '$endDate'); $querySearch.= " AND configlog_action.service_date = '$dateRange' "; ?> This did not work for me. I was just making sure it wasn't possible and that I didn't overlook something. Thanks for the tip about the date column. I will try that out. I may have more questions for you later.

In my db, I have stored service_date as tinytext and not a date. Is it safe to say that strtotime will not work for me? Also, as text, would "2007-04-03" to "2007-04-05" ("2007-04-03", "2007-04-04", "2007-04-05") not be seen as a range?

I have some dates stored in my db. Here an example of the orientation: 2007-04-10 I am trying to display results from the db that fall into a range. I have built my date like so: <?php $startDate = "$searchYearS-$searchMonthS-$searchDayS"; $endDate = "$searchYearE-$searchMonthE-$searchDayE"; $dateRange = range('$startDate', '$endDate'); ?> I know that this builds an array. I would like to know how I can use this to set a variable to use in a SQL statement. Thanks in advance!

This was exactly the problem. I removed some / and { from my form and now it works perfectly. Thanks!

Btw, thank you for your replies. I get: SELECT * FROM configlog_action, configlog_user, configlog_system, configlog_cat WHERE configlog_action.userID = configlog_user.userID AND configlog_action.systemID = configlog_system.systemID AND configlog_action.catID = configlog_cat.catID AND configlog_user.name = '\\\\\\\'Mike' ORDER by service_date DESC LIMIT 50 all all \\\\\\\'Mike

I search using searchName only and get this: SELECT * FROM configlog_action, configlog_user, configlog_system, configlog_cat WHERE configlog_action.userID = configlog_user.userID AND configlog_action.systemID = configlog_system.systemID AND configlog_action.catID = configlog_cat.catID AND configlog_user.name = '\\\\\\\'Mike' ORDER by service_date DESC LIMIT 50 ??? Looks like a lot of unnecessary characters with my value.

I am constructing a query depending on the values chosen from three dropdown lists. The dropdowns are on one page and are called, searchSystem, searchCat, searchName. A button is selected and a new page loads to display results. However, I am not seeing results even though I KNOW there are some. If I echo the query I get the Resource ID #5 error. I've checked my table and column names for errors. I can't find a thing. RESULTS PAGE: <?php include 'db_connect.php'; # constructing query $querySearch = "SELECT * FROM configlog_action, configlog_user, configlog_system, configlog_cat WHERE configlog_action.userID = configlog_user.userID AND configlog_action.systemID = configlog_system.systemID AND configlog_action.catID = configlog_cat.catID "; ##### add restrictions except all ##### $searchSystem = mysql_real_escape_string($_POST['searchSystem']); $searchCat = mysql_real_escape_string($_POST['searchCat']); $searchName = mysql_real_escape_string($_POST['searchName']); # SYSTEM if ($searchSystem != "all"){ $querySearch.= " AND configlog_system.system = '$searchSystem' "; } # CATEGORY if ($searchCat != "all"){ $querySearch.= " AND configlog_cat.category = '$searchCat' "; } # NAME if ($seachName != "all"){ $querySearch.= " AND configlog_user.name = '$searchName' "; } $querySearch.= " ORDER by service_date DESC "; $querySearch.= " LIMIT 50 "; $searchResults = mysql_query($querySearch); # if the query cannot be made due to an error if(!$searchResults) { die( "Database error: " . mysql_error() ); } # if there are no matching records else if(mysql_num_rows($searchResults) == 0) { echo "<table width='100%' border='0' cellspacin='0' cellpadding='1'> <tr class='style7' bgcolor='#2966A3'> <td width='10%'><input name='view' type='submit' class='style1' value='View'></td> <td width='20%'>Service Date</td><td width='15%'>User</td> <td width='20%'>Server</td> <td width='35%'>Category</td></tr> <tr><td colspan='5'><span class='style2'>Displaying the last 50 entries:</span></td></tr> <tr><td colspan='5'><p class='style5'>There are no records for this search request.</p></td></tr>"; } # if the two above statements have returned false else { echo "<form name='form_searchDisplay' method='POST' action='cl_admin_detail.php'>"; echo "<table width='100%' border='0' cellspacing='0' cellpadding='1'> <tr class='style7' bgcolor='#2966A3'> <td width='10%'><input name='view' type='submit' class='style1' value='View'></td> <td width='20%'>Service Date</td> <td width='15%'>User</td> <td width='20%'>Server</td> <td width='35%'>Category</td></tr> <tr><td colspan='5'><span class='style2'>Displaying the last 50 entries:</span></td></tr>"; while($row = mysql_fetch_array($searchResults)) { # print the contents of each row echo "<tr class='style1'><td>"; ?> <input type='radio' name='selected' value='<?php echo $row['actionID'];?>'> <?php echo "</td><td>"; echo $row['service_date']; echo "</td><td>"; echo $row['name']; echo "</td><td>"; echo $row['system']; echo "</td><td>"; echo $row['category']; echo "</td></tr>"; } echo "</table><br>"; echo "</form>"; } mysql_close; ?>

I have many droplists that are populated with the values "open" and "closed". This one is called drop31. As with all of the droplists, which value is default is dependent on which value is currently stored in the db under "Lot31": <?php include 'db_connect.php'; $query_getStatus = mysql_query("SELECT * FROM park_lots") or die(mysql_error()); $get_status = mysql_fetch_array( $query_getStatus ); $_SESSION['status'] = array('open','closed'); echo "<select name='drop31 id='31' class='style1'>"; for ($i=0;$i<=count($_SESSION['status']);$i++) { if ($_SESSION['status'][$i] == $get_status['Lot31']) { echo "<option value=\"{$_SESSION['status'][$i]}\" selected=\"selected\">{$_SESSION['status'][$i]}</option>"; } else { echo "<option value=\"{$_SESSION['status'][$i]}\">{$_SESSION['status'][$i]}</option>"; } } echo '</select>'; ?> The user can change the the value and then upon submission is transferred to a confirmation page where the new value is displayed. This is where my problem is. How do I display the new value there via the Sessions array? I have set up the array at the top of page like so: <?php if( $_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['done']) ) { foreach ($_POST as $key => $value) { if ($key != "done") { $_SESSION[$key] = strip_tags($value); } } header("Location: lots_admin_result.php"); }?> I had this working before I implemented the db aspect.

Well, that's exactly what I had at the beginning. That will give me sticky values in case the user presses submit, fails validation and returns to the page. This is what I want along with: Upon the first visit to the page, I want the user to see, as the default value, the value that is currently stored in the database. That value will either be "open" or "closed". So, if in the database a lot is closed, the droplist will say "closed" but they can use the drop-down to see "open" if they wish.

I wanted the droplist to display the description for the options on its own. So, if "closed" is stored in the db, the droplist should be defaulted to "closed" with the option to change to open. Hope I'm making sense.

I have: <?php include 'db_connect.php'; # connect to the db $query_getStatus = mysql_query("SELECT * FROM park_lots") or die(mysql_error()); ?> <?php echo " <select name='drop11' id='11' class='style1'> "; while($get_status = mysql_fetch_array( $query_getStatus )) { echo "<option value=\"{$get_status['Lot11']}\"". (($_SESSION['drop11'] == $get_status['drop11']) ? (" SELECTED") : ("")) .">\n"; } echo "</select>"; ?> My droplist is empty.

Ah ha ... I see. Thanks for replying. So, for the sql statement, what if I wanted to select from multiple columns ... not just Lot11?

I assume I would need something like: <?php $query_getStatus = mysql_query("SELECT * FROM db") or die(mysql_error()); while($getStatus = mysql_fetch_array( $query_getStatus )) { ?> <select name="drop11" id="11" class="style1"> <option value='<?php echo $getStatus['Lot11'];?>' <?php if (isset($_SESSION['drop11']) && $_SESSION['drop11'] == open) { echo 'selected="selected"';} ?> >open </select> <?php } mysql_close; ?> But since there is an option of 2 values (open or closed), I don't know how to go about this.

Say I have a droplist (drop11) with 2 sticky options (open & closed): <select name="drop11" id="11" class="style1"> <option value="open" <?php if (isset($_SESSION['drop11']) && $_SESSION['drop11'] == open) { echo 'selected="selected"';} ?> >open <option value="closed" <?php if (isset($_SESSION['drop11']) && $_SESSION['drop11'] == closed) { echo 'selected="selected"';} ?> >closed </select> I have a column in my db named Lot11 with the value of either "open" or "closed". I want to also have the above droplist to display the contents of Lot11 upon page load. How could I do that? Thanks in advance!

ACK! I'm sorry - I ommitted this when I was removing certain includes that were unrelated to my problem. Please read my modified previous post. Thanks.

Sure guys: <?php session_start(); echo 'Request_Method = ' .$_SERVER['REQUEST_METHOD']; # print testing echo "<br>\n" . 'Post Data: '; @print_r($_POST); echo "<br>\n" . 'Sessions: '; @print_r($_SESSION); echo "<br>\n"; if( $_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['done']) ) # only if form is submitted { foreach ($_POST as $key => $value) # put the values into session variables { if ($key != "done") # if user didn't reach this page by submitting { $_SESSION[$key] = strip_tags($value); # strip tags } } include 'lots_validate.php'; # and direct to validate } ?> <form name="lots_choose" method="POST" action="<?php echo $_SERVER['PHP_SELF'] ?>"> <div id="middle_title">Lots</div> <div class="style1" align="right">sign out</div> <div align="center"> <p class="style1">Please choose to open or close the following lots:</p> <table width="25%" border="0" align="center" cellpadding="1" cellspacing="0"> <tr> <td width="15%" class="style2"><u>Lot</u></td> <td width="5%" class="style2"><u>Open</u></td> <td width="5%" class="style2"><u>Close</u></td> </tr> <tr> <td class="style1">11</td> <td><label for="open11" class="style1"> <?php $sticky11= ( (isset($_SESSION['rb11'])) && ($_SESSION['rb11'] == 'open') )?' checked="checked" ':' '; # maintains entry echo '<input type="radio" name="rb11" id="open11" value="open" ' . $sticky11 . '/> '; # displays radiobutton with maintained entry ?> </label></td> <td><label for="close11" class="style1"> <?php $sticky11= ( (isset($_SESSION['rb11'])) && ($_SESSION['rb11'] == 'closed') )?' checked="checked" ':' '; # maintains entry echo '<input type="radio" name="rb11" id="close11" value="closed" ' . $sticky11 . '/> '; # displays radiobutton with maintained entry ?> </label></td> </tr> <tr> <td colspan="3" align="right"><input class="style1" type="submit" name="done" id="done" value="done"></td> </tr> </table> </div> </form>