davidcriniti
-
Posts
93 -
Joined
-
Last visited
Posts posted by davidcriniti
-
-
I tried that code and it seems to grab one instance of each athlete and show the data for all rows...however, the instance it grabs shows the first entry for each athlete, rather than the most recent entry. I changed the MAX to MIN but still got the same result. I also changed the script so it was incorporating the prog_expiry_date rather than the date column (tried MIN and MAX variations of this to no avail).
Ie:
SELECT a.*, MAX(p.prog_expiry_date) AS `mprog_expiry_date`, p.* FROM t_athletes AS a JOIN programs_for_athletes AS p ON a.athlete_id = p.athlete_id_prog GROUP BY a.athlete_id ORDER BY $field $sort
Any idea what can rectify this?
-
Thanks for the advice cbones,
I had a bit of a play around with your suggestion and tried as many variations as I could, but the best I could get was that it did come up with one link for each athlete, however, there was no info displayed in the table except the program link.
ie. no firstname, lastname, sex or program expiry date.
Any tips for how to get that?
Yours sincerely,
Dave
-
Thanks,
I've included both tables as attachments and code as I'm not sure which is easier.
Here's the t_athletes one:
-- phpMyAdmin SQL Dump -- version 2.11.11.3 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 18, 2011 at 08:57 AM -- Server version: 4.1.22 -- PHP Version: 5.2.6 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `tesolcla_mylongrun` -- -- -------------------------------------------------------- -- -- Table structure for table `t_athletes` -- CREATE TABLE IF NOT EXISTS `t_athletes` ( `athlete_id` int(11) NOT NULL auto_increment, `approved` enum('Yes','No') NOT NULL default 'No', `date_started` date NOT NULL default '0000-00-00', `firstname` text NOT NULL, `lastname` text NOT NULL, `emailaddress` varchar(100) NOT NULL default '', `password` varchar(15) NOT NULL default '', `phone` varchar(20) NOT NULL default '', `sex` text NOT NULL, `dob` date NOT NULL default '0000-00-00', `height` varchar(30) NOT NULL default '', `weight` varchar(30) NOT NULL default '', `pulse` varchar(30) NOT NULL default '', `current_injuries` longtext NOT NULL, `previous_injuries` longtext NOT NULL, `goal1` text NOT NULL, `goaldate1` text NOT NULL, `goal2` text NOT NULL, `goaldate2` text NOT NULL, `goal3` varchar(255) NOT NULL default '', `goaldate3` varchar(255) NOT NULL default '', `current_training` longtext NOT NULL, `monday` varchar(255) NOT NULL default '', `tuesday` varchar(255) NOT NULL default '', `wednesday` varchar(255) NOT NULL default '', `thursday` varchar(255) NOT NULL default '', `friday` varchar(255) NOT NULL default '', `saturday` varchar(255) NOT NULL default '', `sunday` varchar(255) NOT NULL default '', `inclusions` longtext NOT NULL, `exclusions` longtext NOT NULL, `other` longtext NOT NULL, `notes` longtext NOT NULL, PRIMARY KEY (`athlete_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ; -- -- Dumping data for table `t_athletes` -- INSERT INTO `t_athletes` (`athlete_id`, `approved`, `date_started`, `firstname`, `lastname`, `emailaddress`, `password`, `phone`, `sex`, `dob`, `height`, `weight`, `pulse`, `current_injuries`, `previous_injuries`, `goal1`, `goaldate1`, `goal2`, `goaldate2`, `goal3`, `goaldate3`, `current_training`, `monday`, `tuesday`, `wednesday`, `thursday`, `friday`, `saturday`, `sunday`, `inclusions`, `exclusions`, `other`, `notes`) VALUES (3, 'Yes', '2011-07-05', 'Belinda', 'Summerville', 'b_summerville@bigpond.com', 'secret', '', 'F', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'asdf'), (4, 'Yes', '0000-00-00', 'Bernard', 'Lew', 'bernardlew@hotmail.com', 'secret', '', 'M', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'asdf'), (32, 'Yes', '2011-08-09', 'Pej', 'Ziaei', 'Pej123@hotmail.com', 'secret', '', 'M', '1981-01-11', 'Na', 'Na', 'Na', 'None', 'None', 'Run half marathon in under two hours ', 'Sep 18', 'Run NY and Boston marathon', '2013', 'Duathlon and triathlon', '2012', 'I train 3 times a week\r\nLong run is 12 km', 'Rest', '5pm run', 'Cycle 20min', 'Rest', 'Rest', '12km run', 'Rest', '', 'I work 7 days a week so \r\nMon- Friday I train at 10 pm as I have kids\r\nAnd sat and sun I finish work at 2 so I can train a bit earlier', '', ''), (29, 'Yes', '0000-00-00', 'Amanda', 'Smith', 'walter391@bigpond.com', 'secret', '', 'F', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'Sutherland 1/2 20/08/11 Lake Mac 1/2 28/08/11 Bankstown 1/2 04/09/11 Blackmores 1/2 18/09/11'), (30, 'Yes', '2011-07-15', 'Dave', 'Criniti', 'davidcriniti@gmail.com', 'secret', '', 'M', '1978-06-12', 'asdf', 'asdf', 'asdf', 'This''s a test.', 'test', 'test', '', '', '', '', '', 'test', '', '', '', '', '', '', '', '', '', '', '');Here's the t_programs_for_athletes table:
Server: localhost Database: tesolcla_mylongrun Table: programs_for_athletes -- phpMyAdmin SQL Dump -- version 2.11.11.3 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Aug 18, 2011 at 09:00 AM -- Server version: 4.1.22 -- PHP Version: 5.2.6 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `tesolcla_mylongrun` -- -- -------------------------------------------------------- -- -- Table structure for table `programs_for_athletes` -- CREATE TABLE IF NOT EXISTS `programs_for_athletes` ( `id` int(11) NOT NULL auto_increment, `athlete_id_prog` int(255) NOT NULL default '0', `date` datetime NOT NULL default '0000-00-00 00:00:00', `program_link` text NOT NULL, `prog_start_date` date NOT NULL default '0000-00-00', `prog_expiry_date` date NOT NULL default '0000-00-00', `next_prog_completed` enum('Yes','No') NOT NULL default 'No', `program_message` longtext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=55 ; -- -- Dumping data for table `programs_for_athletes` -- INSERT INTO `programs_for_athletes` (`id`, `athlete_id_prog`, `date`, `program_link`, `prog_start_date`, `prog_expiry_date`, `next_prog_completed`, `program_message`) VALUES (44, 4, '2011-07-18 22:21:00', 'Bernard_Training_Log_2011071.xls', '0000-00-00', '2011-08-07', 'No', 'Bernard, I hope your injury is behind you now. We are trying to build back up the km in this program, and gradually introduce a bit more pace in bite sized chunks. \r\n\r\nLet me know how you go.\r\nDave'), (43, 29, '2011-07-16 12:51:20', 'Amanda_Training_Log_11_071.xls', '0000-00-00', '2011-08-14', 'No', 'Hi Amanda. This program sees you continue to build your mileage as well as including more work at tempo. It incorporates the M7 half and C2S. I have restructured your rest days to ensure you are able to continue building, yet to be as fresh as possible for these two runs. Let me know if you have any concerns.\r\nDave'), (42, 3, '2011-07-16 11:53:19', 'Belinda_Training_Log2011071.xls', '0000-00-00', '2011-08-14', 'No', 'Good luck with this program Belinda. The mileage is almost identical to last month, so hopefully it will be fairly manageable. \r\n\r\nCheers,\r\nDave'), (41, 30, '2011-07-15 20:57:13', 'market research.doc', '0000-00-00', '2011-07-31', 'No', ''), (39, 30, '2011-07-15 20:55:31', 'welcome pack.doc', '0000-00-00', '2011-07-19', 'No', 'Hi Dave, good luck with this program, and with the Hunter Valley run.'), (47, 30, '2011-07-23 15:29:06', 'hdd2.jpg', '2011-08-31', '2011-08-31', 'No', 'Good luck with this program Dave.'), (49, 4, '2011-08-07 18:00:57', 'Bernard_Training_Log_2011081.xls', '2011-09-04', '2011-09-04', 'No', 'Hi mate,\r\n\r\nThis program sees you continue to progress with both distance and intensity towards a maximum of 72k in week 3, before dropping back to 60k in week 4. I hope your injury is now a thing of the past?\r\n\r\nBest wishes,\r\nDave'), (52, 32, '2011-08-14 08:17:25', 'Pej_Training_Log_11_081.xls', '2011-08-15', '2011-09-11', 'No', 'Hi Pej. Your first program will see your weekly km climb relatively steeply in preparation for the Sydney Half Marathon. You hit a maximum long run of 16k in this program, which falls back to 12k in your rest week (wk 4). It also introduces the concept of pace work. Look at the ''Notes'' worksheet for an idea of what the words "easy", "tempo", and "steady" mean. It also starts with a 5k time trial so we can get an idea of your current speed. Let me know how you go. Dave'), (53, 29, '2011-08-14 08:24:57', 'Amanda_Training_Log_11_081.xls', '2011-08-15', '2011-09-11', 'No', 'Hi Amanda. This block is an ambitious one, taking you through 3 halves. I''ve tried to find a balance between allowing sufficient recovery and still allowing you to build towards Chicago. Hopefully you''ll be happy to target Lake Mac as the one to really have a crack at, since it''s fast and will give us the best indication of your current progress. The other two, I''d recommend running hard, but leaving a little in the tank. Sutherland is an out-and-back, so try to ease into it and finish stronger. Let me know how you go. Dave'), (54, 3, '2011-08-14 09:00:16', 'Belinda_Training_Log2011081.xls', '2011-08-15', '2011-09-18', 'No', 'Wow, time does fly. Race day is nearly upon us. I decided to send through a 5 week block this time, so you know what you''re doing right up until then. This block sees you continue to progress with a bit of a rise in both mileage and intensity for the first 3 weeks, then an easier 2 weeks leading into race day. Also take note of the bike notes. I''ve got you building to 16 mins at 60hard, 30 easy, before switching to keeping the time the same but increasing the proportion of intensity to 90 sec hard, 30 sec easy. Good luck with this. Let me know how you go. Dave');There is some same data in the programs_for_athletes table.
Thanks for your time,
Dave
[attachment deleted by admin]
-
Hi,
I have some code to help me keep track of the programs that I write for athletes. It is basically a traffic light system, where if an athlete's program does not need to be updated for more than a week, the cell background is green, if it needs to be updated in less than a week, it turns amber, and if it is overdue, it turns red.
However, the problem is that when I upload a new program, that row appears in green, but the old program can be seen in red. Basically I want the code to only show the most recent program for each athlete. Not sure if I use Max? for this.
I'm looking for the most recent instance of athlete_id_prog in the programs_for_athletes table.
Here's the relevant code:
<?php $sql = mysql_query("SELECT * FROM t_athletes, programs_for_athletes WHERE athlete_id = athlete_id_prog and next_prog_completed='No' ORDER BY $field $sort"); echo "<table border='1' align='center' bordercolor='#000000' CELLPADDING=5 cellspacing='0' STYLE='font-size:13px'>"; echo "<tr bgcolor='#000000'> <td>*</td><td><H3><font color='white'>First name</font> $srt[1]</h3></td> <td><H3><font color='white'>Last name</font> $srt[2]</H3></td> <td><H3><font color='white'>Sex</font> $srt[3]</H3></td> <td><H3><font color='white'>Program link</font> $srt[4]</H3></td> <td><H3><font color='white'>Program Expiry Date</font> $srt[5]</H3></td></tr>"; // keeps getting the next row until there are no more to get $row_counter = 1; //create row counter with default value 0 // Print out the contents of each row into a table /*?>$bgColors = array('F'=>'#FF99FF', 'M'=>'#CCCCCC'); while ($row = mysql_fetch_array($sql)) { // Print out the contents of each row into a table echo "<tr bgcolor=\"{$bgColors[$row['sex']]}\">\n"; echo "</td><td>"; echo $row_counter++; echo "</td>"; echo "<td>{$row['firstname']}</td>\n"; echo "<td>{$row['lastname']}</td>\n"; echo "<td>{$row['sex']}</td>\n"; echo "<td>{$row['notes']}</td>\n"; echo "<td>{$row['note_end_date']}</td>\n"; echo "</tr>\n";<?php */ while ($row = mysql_fetch_array($sql)){ if(strtotime($row['prog_expiry_date']) > time()){ // date hasn't expired yet, so we check the remaining days $diff = strtotime($row['prog_expiry_date']) - time(); if($diff <= (60*60*24*7) ){ $bgColors = '#CC6600'; }else{ $bgColors = '#99FF99'; } }else{ // date has expired $bgColors = '#FF3333'; } echo "<tr bgcolor=\"{$bgColors}\">\n"; echo "</td><td>"; echo $row_counter++; echo "</td>"; echo "<td>{$row['firstname']}</td>\n"; echo "<td>{$row['lastname']}</td>\n"; echo "<td>{$row['sex']}</td>\n"; echo "<td>\n"; echo "<a href='http://www.mytestwebsite.com/athlete_programs/" . $row["program_link"] . "' target='_blank'>" . $row["program_link"] . "</a>"; echo "</td>\n"; echo "<td>{$row['prog_expiry_date']}</td>\n"; echo "</tr>\n"; } echo "</table>"; ?>Any advice is much appreciated.
-
Hi everyone,
I have a table that allows people to be added to a database (Firstname, lastname, sex and notes).
Had to tweak it a little so I could add names like "O'Brien" etc, by putting in a real escape string.
I can add these names ok now. However, when I go to edit one of these names, the edit does not complete. I've tried adding the real escape string to other parts of the code (just guesswork), but nothing I've done so far has worked. Does anyone have any ideas?
I presume the changes need to be made in this part of the code? :
<?php include 'mysql_connect.php'; $act = $_POST['act']; switch($act){ case 'save_new': // save for new action $firstname = mysql_real_escape_string ($_POST["firstname"]); $lastname = mysql_real_escape_string ($_POST["lastname"]); $sex = strtoupper($_POST['sex']); $notes = mysql_real_escape_string ($_POST["notes"]); $date_started = date("Y/m/d h:i:s"); $sql = " INSERT INTO t_athletes(firstname, lastname, sex, notes, date_started) VALUES('{$firstname}', '{$lastname}', '{$sex}', '{$notes}', '{$date_started}') "; if(mysql_query($sql)){ $_SESSION['message'] = 'Add new entrant success'; }else{ $_SESSION['message'] = 'Add new entrant fail'; } ?> <script type="text/javascript"> window.location = 'athlete_notes3.php'; </script> <? die(); break; case 'delete_list': // delete action $cid = $_POST['cid']; if(count($cid)){ $sql = " DELETE FROM t_athletes WHERE athlete_id IN('".implode("','", $cid)."') "; mysql_query($sql); $_SESSION['message'] = count($cid) . ' entrant(s) deleted'; ?> <script type="text/javascript"> window.location = 'athlete_notes3.php'; </script> <? die(); } break; case 'save_all': // save for edit action $cid = $_POST['cid']; $ids = $_POST['ids']; $firstnames = $_POST['firstnames']; $lastnames = $_POST['lastnames']; $sexes = $_POST['sexes']; $notess = $_POST['notess']; $sql = " UPDATE t_athletes SET "; if(count($cid)) foreach($cid as $key => $val){ $fields = array(); $fields[] = "firstname='".$firstnames[$key]."'"; $fields[] = "lastname='".$lastnames[$key]."'"; $fields[] = "sex='".strtoupper($sexes[$key])."'"; $fields[] = "notes='".$notess[$key]."'"; mysql_query($sql . implode(",", $fields) . " WHERE athlete_id = '".$cid[$key]."'"); } $_SESSION['message'] = 'Entrants edited success'; ?> <script type="text/javascript"> window.location = 'athlete_notes3.php'; </script> <? die(); break; } ?>...but I'll paste the whole code for the page in case changes are needed elsewhere?
<?php // Get all the data from the "example" table $result = mysql_query("SELECT * FROM t_athletes") or die(mysql_error()); ?> <form name="form" method="post"> <div style="text-align:center;color:#FF0000;"> <?php echo $_SESSION['message']; $_SESSION['message'] = ''; ?> </div> <table width="100%"> <tr> <td valign="top" align="center"> <table style="display:none;" id="addNewForm"> <tr> <td align="left">First Name</td> <td align="left"><input type="text" name="firstname" /></td> </tr> <tr> <td align="left">Last Name</td> <td align="left"><input type="text" name="lastname" /></td> </tr> <tr> <td align="left">Sex</td> <td align="left"> <select name="sex"> <option value="M">M</option> <option value="F">F</option> </select> </td> </tr> <tr> <td align="left">notes</td> <td align="left"><input type="text" name="notes" /></td> </tr> <tr> <td colspan="2"> <button type="button" onClick="save();">Save</button> <button type="button" onClick="cancelAddNew();">Cancel</button> </td> </tr> </table> <button type="button" onClick="addNew();" id="btn_add">Add</button> <button type="button" onClick="deleteList();" id="btn_delete">Delete</button> <button type="button" onClick="editAll();" id="btn_edit_all">Edit All</button> <button type="button" onClick="saveAll();" id="btn_save_all">Save All</button> <button type="button" onClick="cancelAll();" id="btn_cancel_all">Cancel All</button> </td> </tr> </table> <table border="1" align="center" cellpadding="10" cellspacing="0" bordercolor="#000000" class="list" width="100%"> <tr bgcolor="#6175BE"><th>Delete</th> <th>firstname</th> <th>lastname</th> <th>sex</th> <th>notes</th><th>Edit</th></tr> <?php // keeps getting the next row until there are no more to get while($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table ?> <tr id="row_<?php echo $row['athlete_id'];?>" bgcolor='#CCCCCC'> <td width="30" align="center"> <input type="checkbox" name="cid[<?php echo $row['athlete_id'];?>]" value="<?php echo $row['athlete_id'];?>" class="checkedbox" /> </td> <td> <span><?php echo $row['firstname'];?></span> <input type="text" value="<?php echo $row['firstname'];?>" class="editable" name="firstnames[<?php echo $row['athlete_id'];?>]" /> </td> <td width="200"> <span><?php echo $row['lastname'];?></span> <input type="text" value="<?php echo $row['lastname'];?>" class="editable" name="lastnames[<?php echo $row['athlete_id'];?>]" /> </td> <td width="50" align="center"> <span><?php echo $row['sex'];?></span> <!--<input type="text" value="<?php echo $row['sex'];?>" class="editable" name="" />--> <select name="sexes[<?php echo $row['athlete_id'];?>]" class="editable"> <option value="M" <?php echo ($row['sex'] == 'F') ? '' : 'selected="selected"';?>>M</option> <option value="F" <?php echo ($row['sex'] == 'M') ? '' : 'selected="selected"';?>>F</option> </select> </td> <td align="center" width="150"> <span><?php echo $row['notes'];?></span> <textarea value="<?php echo $row['notes'];?>" class="editable" name="notess[<?php echo $row['athlete_id'];?>]" ></textarea> </td> <td width="60" align="center"> <input type="hidden" value="<?php echo $row['athlete_id'];?>" class="rowid" name="ids[]" /> <button type="button" onClick="javascript:editRow('<?php echo $row['athlete_id'];?>');" class="edit">Edit</button> <button type="button" class="save" onClick="saveRow('<?php echo $row['athlete_id'];?>')">Save</button> <button type="button" class="cancel" onClick="cancelRow('<?php echo $row['athlete_id'];?>');">Cancel</button> </td> </tr> <?php }?> <!-- <tr> <td colspan="6" align="center"> </td> </tr>--> </table> <input type="hidden" name="act" value="" /> </form> <script> // show form to add new entrant function addNew(){ var form = document.form; form.firstname.value = ''; form.lastname.value = ''; form.sex.value = 'm'; form.notes.value = ''; $("addNewForm").setStyles({display: 'block'}); $("btn_add").setProperty("disabled", "disabled"); $("btn_delete").setProperty("disabled", "disabled"); $("btn_edit_all").setProperty("disabled", "disabled"); $("btn_save_all").setProperty("disabled", "disabled"); $("btn_cancel_all").setProperty("disabled", "disabled"); $$("button.edit").setProperty("disabled", "disabled"); } // hide form to add new entrant function cancelAddNew(){ $('addNewForm').setStyles({display: 'none'}); $("btn_add").setProperty("disabled", ""); $("btn_delete").setProperty("disabled", ""); $("btn_edit_all").setProperty("disabled", ""); $$("button.edit").setProperty("disabled", ""); } // save new entrant function save(){ if(!checkInput()) return; document.form.act.value = 'save_new'; document.form.submit(); } // delete entrants function deleteList(){ var ids = $$("input.checkedbox"); var selected = 0; for(var i=0;i<ids.length;i++){ if(ids.checked) selected++; } if(selected==0){ alert("Please select which record(s) you wish to delete"); return; } if(!confirm("Are you sure you want to delete "+selected+" record(s) from this table")){ return; } document.form.act.value = 'delete_list'; document.form.submit(); } // show row editable function editRow(rowid){ var row = $("row_"+rowid); row.editing = true; //row.getElements("span").setStyles({display: 'none'}); //row.getElements("input.editable").setStyles({display: 'block'}); var spans = row.getElements("span"); var inputs = row.getElements(".editable"); for(var j = 0; j < spans.length; j++){ spans[j].setStyles({display: 'none'}); inputs[j].setStyles({display: 'block'}); inputs[j].value = spans[j].innerHTML; } row.getElements("button.edit").setStyles({display: 'none'}); row.getElements("button.save").setStyles({display: 'block'}); row.getElements("button.cancel").setStyles({display: 'block'}); $$("button.edit").setProperty("disabled", "disabled"); $("btn_add").setProperty("disabled", "disabled"); $("btn_delete").setProperty("disabled", "disabled"); $("btn_edit_all").setProperty("disabled", "disabled"); $("btn_save_all").setProperty("disabled", "disabled"); $("btn_cancel_all").setProperty("disabled", "disabled"); } // hide row editable function cancelRow(rowid){ var row = $("row_"+rowid); row.editing = false; row.getElements("span").setStyles({display: 'block'}); row.getElements(".editable").setStyles({display: 'none'}); row.getElements("button.edit").setStyles({display: 'block'}); row.getElements("button.save").setStyles({display: 'none'}); row.getElements("button.cancel").setStyles({display: 'none'}); $$("button.edit").setProperty("disabled", ""); $("btn_add").setProperty("disabled", ""); $("btn_delete").setProperty("disabled", ""); $("btn_edit_all").setProperty("disabled", ""); $("btn_save_all").setProperty("disabled", "disabled"); $("btn_cancel_all").setProperty("disabled", "disabled"); } // action for edit all rows function editAll(){ var ids = $$("input.rowid"); for(var i=0;i<ids.length;i++){ var row = $("row_"+ids.value); var spans = row.getElements("span"); var inputs = row.getElements(".editable"); for(var j = 0; j < spans.length; j++){ spans[j].setStyles({display: 'none'}); inputs[j].setStyles({display: 'block'}); inputs[j].value = spans[j].innerHTML; } } $$("button.edit").setProperty("disabled", "disabled"); $("btn_add").setProperty("disabled", "disabled"); $("btn_delete").setProperty("disabled", "disabled"); $("btn_edit_all").setProperty("disabled", "disabled"); $("btn_save_all").setProperty("disabled", ""); $("btn_cancel_all").setProperty("disabled", ""); } // function cancelAll(){ var ids = $$("input.rowid"); for(var i=0;i<ids.length;i++){ cancelRow(ids.value); } $("btn_add").setProperty("disabled", ""); $("btn_delete").setProperty("disabled", ""); $("btn_edit_all").setProperty("disabled", ""); $("btn_save_all").setProperty("disabled", "disabled"); $("btn_cancel_all").setProperty("disabled", "disabled"); } function saveRow(rowid){ var row = $("row_"+rowid); var checkboxes = row.getElements("input.checkedbox")[0]; checkboxes.checked = true; document.form.act.value = 'save_all'; document.form.submit(); } function saveAll(){ var ids = $$("input.checkedbox"); var ret = new Array(ids.length); var success =0, fail = 0; for(var i=0;i<ids.length;i++){ ids.checked = true; } document.form.act.value = 'save_all'; document.form.submit(); } $("btn_save_all").setProperty("disabled", "disabled"); $("btn_cancel_all").setProperty("disabled", "disabled"); // make sure all fields are fill before save function checkInput(){ var form = document.form; if(form.firstname.value == ''){ alert("Please enter firstname"); form.firstname.focus(); return false; } if(form.lastname.value == ''){ alert("Please enter lastname"); form.lastname.focus(); return false; } if(form.notes.value == ''){ alert("Please enter notes"); form.notes.focus(); return false; } return true; } </script>All advice is greatly appreciated.
Dave
MOD EDIT: code tags added.
-
Thanks Webstyles,
I managed to get it working after changing the code slightly to make it strtotime($row['note_end_date']) - time() instead of vice versa.
while ($row = mysql_fetch_array($sql)){
if(strtotime($row['note_end_date']) > time()){
// date hasn't expired yet, so we check the remaining days
$diff = strtotime($row['note_end_date']) - time();
if($diff <= (60*60*24*7) ){
$bgColors = '#CC6600';
}else{
$bgColors = '#99FF99';
}
}else{
// date has expired
$bgColors = '#FF3333';
}
Seems to be working like a charm now - ta muchly!
Dave
-
Having played with this over the past couple of days I got to thinking about the possibility of having a third colour (for example, #CC6600) for the background of the cells in a situation where note_end_date is nearing, but has not yet arrived.
Kind of like traffic signals I guess: Green when the end date is a way off, orange when the end date is approaching (say less than 7 days away) and red for once the end date has arrived.
Currently the code just has the two options (green and red):
$bgColors = strtotime($row['note_end_date']) > time() ? '#99FF99' : '#FF3333';Can anyone advise how I'd tweak it to include the third option?
Yours gratefully,
Dave
-
Yeah that's pretty much all I did. Thanks for that tip - I'll watch out for the same thing in future.
-
Yep, it was inside the form. I managed to get it working though it seems to be a case of more good luck than good management. Here's the working code:
(Part 1 from the form):
<?php include 'mysql_connect.php';
$result = mysql_query( "SELECT firstname, lastname, athlete_id FROM t_athletes ORDER BY lastname ASC" )
or die("SELECT Error: ".mysql_error());
$options = "<option selected>Please select...</option>";
while ($row=mysql_fetch_array($result)) {
$options .= "<option value=\"$row[athlete_id]\">{$row['firstname']}"." "."{$row['lastname']}</option>";
}
?>
<label>
<select name="athlete_id" id="athlete_id">
<?php echo $options?>
</select>
</label>
...and the code that processes the form:
<?php
//MySQL Database Connect
include 'mysql_connect.php';
global $_POST;
$athlete_id = $_POST["athlete_id"] ;
$prog_expiry_date = $_POST["prog_expiry_date"] ;
$program_link = $_POST["program_link"] ;
$program_message = $_POST["program_message"] ;
$docx=$_FILES['program_link']['name'];
if($docx!="")
{
if (!copy($_FILES['program_link']['tmp_name'], "athlete_programs/$docx"))
{
echo "failed to copy \n";
}
}
//**********************SEND TO DATABASE****************************
//MySQL Database Connect
include 'mysql_connect.php';
$query = "INSERT INTO programs_for_athletes (date, athlete_id_prog, program_link, prog_expiry_date, program_message)" . "VALUES (NOW(), '$athlete_id', '$docx', '$prog_expiry_date' , '$program_message')";
//if($query){echo 'data has been placed'}
mysql_query($query) or die(mysql_error());
//***********************END OF DATABASE CODE***********************
?>
Cheers,
Dave
-
Hi,
I have a form which a coach uses to upload programs (usually excel documents) for his athletes. This part of the form works perfectly.
However, the coach doesn't type the athlete's name into a text field. Rather, the athlete's name is selected from a dropdown menu which is populated by the names of athletes in the database.
See code below:
<?php include 'mysql_connect.php';
$result = mysql_query( "SELECT firstname, lastname, athlete_id FROM t_athletes ORDER BY lastname ASC" )
or die("SELECT Error: ".mysql_error());
$options = "<option selected>Please select...</option>";
while ($row=mysql_fetch_array($result)) {
$options .= "<option value=\"$row[id]\">{$row['firstname']}"." "."{$row['lastname']}</option>";
}
?>
<label>
<select name="id" id="id">
<?php echo $options?>
</select>
</label>
The athlete's id is the only thing that doesn't go through to the database. Not sure if I need to change something in the code above or something in the form processing code below. Any ideas?
<?php
//MySQL Database Connect
include 'mysql_connect.php';
global $_POST;
$id = $_POST["id"] ;
$prog_expiry_date = $_POST["prog_expiry_date"] ;
$program_link = $_POST["program_link"] ;
$program_message = $_POST["program_message"] ;
$docx=$_FILES['program_link']['name'];
if($docx!="")
{
if (!copy($_FILES['program_link']['tmp_name'], "athlete_programs/$docx"))
{
echo "failed to copy \n";
}
}
//**********************SEND TO DATABASE****************************
//MySQL Database Connect
include 'mysql_connect.php';
$query = "INSERT INTO programs_for_athletes (date, athlete_id_prog, program_link, prog_expiry_date, program_message)" . "VALUES (NOW(), '$id', '$docx', '$prog_expiry_date' , '$program_message')";
//if($query){echo 'data has been placed'}
mysql_query($query) or die(mysql_error());
//***********************END OF DATABASE CODE***********************
?>
-
Thanks webstyles and jcbones.
I've now got a solution thanks to both of you. I'll post the working code below for the benefit of anyone wanting to do this in future:
<?php while ($row = mysql_fetch_array($sql)){ $bgColors = strtotime($row['note_end_date']) > time() ? '#99FF99' : '#FF3333'; // Print out the contents of each row into a table echo "<tr bgcolor=\"{$bgColors}\">\n"; echo "</td><td>"; echo $row_counter++; echo "</td>"; echo "<td>{$row['firstname']}</td>\n"; echo "<td>{$row['lastname']}</td>\n"; echo "<td>{$row['sex']}</td>\n"; echo "<td>{$row['notes']}</td>\n"; echo "<td>{$row['note_end_date']}</td>\n"; echo "</tr>\n"; } echo "</table>"; ?>Thanks again,
Dave
-
No luck with that. I changed one of your lines to
echo "<tr bgcolor=\"{$bgColors[$row['note_end_date']]}\">\n";
...Was that a move in the right direction at least? Any other tips?
Thanks,
Dave
-
Hi everyone,
In the script below, the background colour (I'm Australian, not a bad speller
) is determined by the sex field.Any tips on how I can change the array (or replace it with an if / else statement) so it's determined by the 'note_end_date' field and set out so that if the value in the note_end_date is sometime in the future, then the cell background is #FF99FF, and if that is not the case, then the cell background is #CCCCCC.
Any advice is much appreciated. Here's the relevant code:
$bgColors = array('F'=>'#FF99FF', 'M'=>'#CCCCCC'); while ($row = mysql_fetch_array($sql)) { // Print out the contents of each row into a table echo "<tr bgcolor=\"{$bgColors[$row['sex']]}\">\n"; echo "</td><td>"; echo $row_counter++; echo "</td>"; echo "<td>{$row['firstname']}</td>\n"; echo "<td>{$row['lastname']}</td>\n"; echo "<td>{$row['sex']}</td>\n"; echo "<td>{$row['notes']}</td>\n"; echo "<td>{$row['note_end_date']}</td>\n"; echo "</tr>\n"; } echo "</table>"; -
Thanks for those replies.
I got the result I wanted with xangelo's advice, but will also have a look at your way too duster889.
I notice I made a few silly mistakes in the code I provided above, so I might post the working code here for the benefit of anyone who looks up this problem in the future:
Code from the first page ( app_form_1.php ) :
<form id="form1" name="form1" method="post" action="app_form_2.php"> Firstname: <label> <input type="text" name="firstname" id="firstname" /> </label> <p> <label> <input type="submit" name="submit" id="Go to page 2" value="Go to page 2" /> </label> </form>
Code from the second page ( app_form_2.php ):
<?php session_start(); $_SESSION['firstname'] = $_POST['firstname']; ?> /* * HTML code - DOCTYPE etc */ <form id="form1" name="form1" method="post" action="app_form_3.php"> Lastname: <label> <input type="text" name="lastname" id="lastname" /> </label> <p> <label> <input type="submit" name="submit" id="Go to page 3" value="Go to page 3" /> </label> </form>
Code from the third page (app_form_3.php ) :
<?php session_start(); $_SESSION['lastname'] = $_POST['lastname']; ?> /* * HTML code - DOCTYPE etc */ <form id="form1" name="form1" method="post" action="app_form_process.php"> Email address: <label> <input type="text" name="emailaddress" id="emailaddress" /> </label> <p> <label> <input type="submit" name="submit" id="Submit" value="Submit" /> </label> </form>
Code from the 4th page ( app_form_process.php ):
<?php session_start(); $firstname = $_SESSION['firstname'] ; $lastname = $_SESSION['lastname']; $emailaddress = $_POST["emailaddress"]; //**********************SEND TO DATABASE**************************** //MySQL Database Connect include 'mysql_connect.php'; $query = "INSERT INTO application_form (firstname, lastname, emailaddress)" . "VALUES ('$firstname', '$lastname', '$emailaddress')"; //if($query){echo 'data has been placed'} mysql_query($query) or die(mysql_error()); ?> -
Hi,
I've tried to follow a couple tuts on multi-page forms using php. The reason I'm doing it is that I'd like to create a mock-job application form for my students at school. This would be too long to have on one page.
Anyway, I've created the most simple form I could think of and was hoping someone could help me out and show me how to get the info from page 1 to 2, then the info from pages 1 and 2 to 3, and then submit the info from pages 1, 2 and 3 to the database. Page 1 asks for firstname, page 2 asks for lastname, and page 3 asks for email address. The fourth page is the one that processes the form.
Page 1: (app_form_1.php)
<form id="form1" name="form1" method="post" action="app_form_2"> Firstname: <label> <input type="text" name="firstname" id="firstname" /> </label> </form> <p> <label> <input type="submit" name="Continue" id="Continue" value="Continue" /> </label>
Page 2 (app_form_2.php)
<form id="form1" name="form1" method="post" action="app_form_3"> Lastname: <label> <input type="text" name="firstname" id="firstname" /> </label> </form> <p> <label> <input type="submit" name="Continue" id="Continue" value="Continue" /> </label>
Page3 ( app_form_3.php )
<form id="form1" name="form1" method="post" action="app_form_process"> Email address: <label> <input type="text" name="emailaddress" id="emailaddress" /> </label> </form> <p> <label> <input type="submit" name="submit" id="Submit" value="Submit" /> </label>
Page 4 (app_form_process.php )
<?php global $_POST; $firstname = $_POST["firstname"] ; $lastname = $_POST["lastname"]; $emailaddress = $_POST["emailaddress"]; //**********************SEND TO DATABASE**************************** //MySQL Database Connect include 'mysql_connect.php'; $query = "INSERT INTO application_form (firstname, lastname, emailaddress)" . "VALUES ('$firstname', '$lastname', '$emailaddress')"; //if($query){echo 'data has been placed'} mysql_query($query) or die(mysql_error()); ?>Any help is much appreciated.
Thanks,
Dave
-
why do you think that cramming more code onto a single page and having the server run through all the code each time using PHP_SELF is more efficient?
Well, firstly, thanks Muddy_Funster. I followed your instructions and I got it working.
I could have got it working on my own using two php pages (as alluded to in my first post), but I was just thinking of minimising the total number of pages in my site, since there are a lot of forms.
Does your quote above mean you would you suggest I keep using the two page method with the form action being to call a confirmation page, rather than using PHP self?
Secondly, although the below code works, I'm not sure if it's good practice to have the form outside of the php tags? I had to interrupt the if / else statement by ending the php code, then ending the form, then starting up php code again. Like I said it works, but not sure if it's the best way to go about it? - I'm new to this game.
Thanks,
Dave
<form id="form1" name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <?php if(!isset($_POST['hiddenField'])) { echo ' <p> <label> So...You forgot your password eh? Give us your email address and we\'ll send it to you. <input type="text" name="emailaddress" id="emailaddress" /> </label> </p> <p> <input name="hiddenField" type="hidden" id="hiddenField" value="1" /> </p> <p> <label> <input type="submit" name="submit" id="submit" value="Submit Form" /> </label> </p>'; ?> </form> <?php } else { $emailaddress = $_POST['emailaddress']; echo "We've sent an email to $emailaddress, reminding you of your password."; //MySQL Database Connect include 'mysql_connect_applications.php'; // Get all the data from the "example" table $result = mysql_query("SELECT * FROM applications2011 WHERE emailaddress= '$emailaddress' LIMIT 0,1 ") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { $password = $row['password']; } // keeps getting the next row until there are no more to get //***********************Email to forgetful user code*********************** $to = "$emailaddress\n"; $subject = "C2K Application"; $headers = "From: coast2kosci@coast2kosci.com"; $message = "Hi there, Seems you forgot your password. Here it is: Password: $password Yours in the long run, Dave.\n"; if (preg_match(' /[\r\n,;\'"]/ ', $_POST['emailaddress'])) { exit('Invalid Email Address'); } else { mail($to,$subject,$message,$headers); } //***********************End of Email to applicant code*********************** } ?> -
Hi there,
I'm trying to make my code more efficient by using <?php echo $_SERVER['PHP_SELF']; ?> ....where I've been used to having two pages with forms - one for filling out the form, and then a second page to process it / confirm, etc.
The page in question is designed to send a reminder of a user's password to their email address.
They basically put their email address into a form with one field, next to which it says:
"So...You forgot your password eh? Give us your email address and we'll send it to you."
I've got the code set up so it sends the email with the password, no problems.
However, once they've pressed submit, they can still see the message "So...You forgot your password eh? Give us your email address and we'll send it to you"...as well as the form field and submit button.
I'd love to know what I need to do to hide these after the user has pressed submit??
Here's the code:
<?php if(isset($_POST['submit'])) { $emailaddress = $_POST['emailaddress']; echo "We've sent an email to $emailaddress, reminding you of your password."; //MySQL Database Connect include 'mysql_connect_applications.php'; // Get all the data from the "example" table $result = mysql_query("SELECT * FROM applications2011 WHERE emailaddress= '$emailaddress' LIMIT 0,1 ") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { $password = $row['password']; } // keeps getting the next row until there are no more to get //***********************Email to forgetful user code*********************** $to = "$emailaddress\n"; $subject = "C2K Application"; $headers = "From: coast2kosci@coast2kosci.com"; $message = "Hi there, Seems you forgot your password. Here it is: Password: $password Yours in the long run, Dave.\n"; if (preg_match(' /[\r\n,;\'"]/ ', $_POST['emailaddress'])) { exit('Invalid Email Address'); } else { mail($to,$subject,$message,$headers); } //***********************End of Email to applicant code*********************** } ?> <form id="form1" name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> <p> <label> So...You forgot your password eh? Give us your email address and we'll send it to you. <input type="text" name="emailaddress" id="emailaddress" /> </label> </p> <p> <label> <input type="submit" name="submit" id="submit" value="Submit Form" /> </label> </p> </form> -
Hi,
I've got a date picker on a form which puts data into a database in the YYYY-MM-DD format. Just wondering how I could also put the name of the month (extracted from that) as well as just the year into separate columns. Ie: To use the field race_date from the form to also fill the 'race_month' and 'race_year' columns in the database.
This code obviously only fills the 'race_date' column so far:
global $_POST; $race_date = $_POST["race_date"] ; .... $query = "INSERT INTO 10k_races (race_date, race_month, race_year)" . "VALUES ( '$race_date', '$race_month', '$race_year')";
-
Hi there,
I got some good advice from Harristweed the other day about how to export from Mysql to excel, so that each field appears in a different column in excel.
I expanded the code Harristweed gave me to cover all fields in my database, and all went well until the 2nd last field, when I hit a brick wall. Unlike previous fields, which were based on info inputted via radio buttons, dropdown lists, or text boxes, the field causing problems was based on data that came from a text area on the web form.
A few tests revealed that the problem was related to when a user pressed the "Enter" key while filling in that field, to begin a new paragraph. When this happens, it would go into the database fine. However, when I used the code to export it to excel, anything after the "Enter" key had been pressed would appear in a new cell on a new row, thereby putting the table out of alignment.
I could get around this by rewording the question into a few questions which each use a text box rather than a text area box.
However, if there is a way to get around this by tweaking the php code, I'd love to know. The field with which I'm having the problem is the one called "experience".
Thanks for your time,
Dave
<?PHP $db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD"); mysql_select_db("MYDATABASE",$db); $query="SELECT * FROM applications2010"; $result=mysql_query($query); $header=" date\t firstname\t lastname\t sex\t dobday\t dobmonth\t dobyear\t streetaddress\t suburb\t state\t postcode\t country\t preferredphone\t secondphone\t thirdphone\t emailaddress\t emconname\t emconphone\t experience\t "; while($row = mysql_fetch_assoc($result)) { if(empty($row[date]))$row[date]=" "; if(empty($row[firstname]))$row[firstname]=" "; if(empty($row[lastname]))$row[lastname]=" "; if(empty($row[sex]))$row[sex]=" "; if(empty($row[dobday]))$row[dobday]=" "; if(empty($row[dobmonth]))$row[dobmonth]=" "; if(empty($row[dobyear]))$row[dobyear]=" "; if(empty($row[streetaddress]))$row[streetaddress]=" "; if(empty($row[suburb]))$row[suburb]=" "; if(empty($row[state]))$row[state]=" "; if(empty($row[postcode]))$row[postcode]=" "; if(empty($row[country]))$row[country]=" "; if(empty($row[preferredphone]))$row[preferredphone]=" "; if(empty($row[secondphone]))$row[secondphone]=" "; if(empty($row[thirdphone]))$row[thirdphone]=" "; if(empty($row[emailaddress]))$row[emailaddress]=" "; if(empty($row[emconname]))$row[emconname]=" "; if(empty($row[emconphone]))$row[emconphone]=" "; if(empty($row[experience]))$row[experience]=" "; $line = ''; $line .= "$row[date]\t $row[firstname]\t $row[lastname]\t $row[sex]\t $row[dobday]\t $row[dobmonth]\t $row[dobyear]\t $row[streetaddress]\t $row[suburb]\t $row[state]\t $row[postcode]\t $row[country]\t $row[preferredphone]\t $row[secondphone]\t $row[thirdphone]\t $row[emailaddress]\t $row[emconname]\t $row[emconphone]\t $row[experience]\t "; $data .= trim($line)."\n"; } $data = str_replace("\r","",$data); if ($data == "") { $data = "\n(0) Records Found!\n"; } header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=filename=".date("d-m-Y")."-export.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data";exit; ?> -
Worked perfectly. Thanks harristweed !
-
Hi everyone,
I got a bit of code off the web and did some tweaking. I want to use it to export data from a mysql table to excel. I do a website for a friend and have been exporting it myself and emailing it to him. However, I thought it'd be easier if I could just direct him to a link so he could get it whenever.
Anyway, the code below works pretty well...the only problem being that it exports all data to the first column of the excel file. There are commas between all fields, so it is possible to use the 'text to columns' feature in excel to separate teh data into separate columns.
However, if there is a way of fixing the code that would negate the need to have to do this, it would be much appreciated.
Here goes:
<?php $db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD"); mysql_select_db("MYDATABASE",$db); $query="SELECT * FROM MYTABLENAME"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $csv_output = "firstname,lastname,emailaddress,crew_name_001"; $csv_output .= "\r\n"; $i=0; while ($i < $num) { $id=mysql_result($result,$i,"id"); $firstname = mysql_result($result,$i,"firstname"); $lastname = mysql_result($result,$i,"lastname"); $emailaddress = mysql_result($result,$i,"emailaddress"); $crew_name_001 = mysql_result($result,$i,"crew_name_001"); $csv_output .= "$firstname,$lastname,$emailaddress,$crew_name_001\n"; ++$i; } $mode="xls"; $type="excel"; header("Content-type: text/x-csv"); header("Content-disposition: attachment; filename=".date("d-m-Y")."-export.xls"); print $csv_output; exit; ?>Thanks for your time,
Dave
-
Hi,
I've used this code before, but have had to make some modifications and am now getting a mysql fetch array error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/tesolcla/public_html/test/results10k201010.php on line 134
If anyone can help, it would be much appreciated.
<?php $dbcnx = @mysql_connect('localhost', 'MYUSERNAME', 'MYPASSWORD'); //$dbcnx = @mysql_connect('localhost', 'root', 'mysql'); if (!$dbcnx) { exit('<p>Unable to connect to the ' . 'database server at this time.</p>'); } if (!@mysql_select_db('MYDATABASE')) { //if (!@mysql_select_db('rac')) { exit('<p>Unable to locate the results ' . 'database at this time.</p>'); } $asc_on = '<img src="images/results_sorting/Asc.gif" border="0" />'; $asc_off= '<img src="images/results_sorting/AscOff.gif" border="0" />'; $desc_on = '<img src="images/results_sorting/Desc.gif" border="0" />'; $desc_off= '<img src="images/results_sorting/DescOff.gif" border="0" />'; $sortfield = isset($_GET['sort']) ? $_GET['sort'] : '4'; $sorttype = isset($_GET['type']) ? $_GET['type'] : '1'; for($i=1; $i<5; $i++) { if($i==$sortfield) { if ($sorttype==1) $srt[$i] = $asc_on.'<a href="?sort='.$i.'&type=2">'.$desc_off.'</a>'; else $srt[$i] = '<a href="?sort='.$i.'&type=1">'.$asc_off.'</a>'.$desc_on; } else { $srt[$i] = '<a href="?sort='.$i.'&type=1">'.$asc_off.'</a><a href="?sort='.$i.'&type=2">'.$desc_off.'</a>'; } } $fields = array("firstname", "lastname", "time", "position"); $sorts = array("ASC", "DESC"); $field = $fields[$sortfield-1]; $sort = $sorts[$sorttype-1]; $field = $field=="" ? $fields[4] : $field; $sort = $sort=="" ? $sorts[0] : $sort; $sql = mysql_query("SELECT firstname, lastname, time, position FROM 10k_results ORDER BY $field $sort"); echo "<table border='1' align='center' bordercolor='#000000' CELLPADDING=5 cellspacing='0' STYLE='font-size:13px'>"; echo "<tr bgcolor='#008000' STYLE='color:white'> <td>*</td><td><H3>First name $srt[1]</h3></td> <td><H3>Lastname $srt[2]</H3></td> <td><H3>Time $srt[3]</H3></td> <td><H3>Position $srt[4]</H3></td></tr>"; // keeps getting the next row until there are no more to get $row_counter = 1; //create row counter with default value 0 // Print out the contents of each row into a table while ($row = mysql_fetch_array($sql)) { // Print out the contents of each row into a table echo "<tr>\n"; echo "</td><td>"; echo $row_counter++; echo "</td>"; echo "<td>{$row['firstname']}</td>\n"; echo "<td>{$row['lastname']}</td>\n"; echo "<td>{$row['time']}</td>\n"; echo "<td>{$row['position']}</td>\n"; echo "</tr>\n"; } echo "</table>"; ?> -
Well it was definitely more good luck than good management (and I'm not really sure what some of the code means), but I found a solution. The solution came from this page:
http://stackoverflow.com/questions/2534253/sql-query-group-by-more-than-one-column-but-distinct
and this is the script I have which now works. I hope it's of use to someone in the future. Thanks again litebearer and laanes
<?php mysql_connect ("localhost", "MYUSERNAME", "MYPASSWORD"); mysql_select_db ("MYDATABASE"); $result = mysql_query("SELECT b.pagename, b2.Latestdate, b.content FROM editablepage b JOIN ( SELECT pagename, MAX(date) AS Latestdate FROM editablepage GROUP BY pagename) b2 ON b.pagename = b2.pagename AND b.date = b2.Latestdate ORDER BY pagename ASC" ) or die(mysql_error()); echo "<table border='1' align='center' cellpadding='10' cellspacing='0' bordercolor='#000000'>"; echo "<tr bgcolor='#6175BE'> <th>Page Name</th> <th>Content </th></tr>"; while($row = mysql_fetch_array( $result )) { $pagename = $row['pagename']; $content = $row['content']; echo "<tr bgcolor='#CCCCCC'><td>"; echo $row['pagename']; echo "</td><td>"; echo $row['content']; echo "</td></tr>"; } echo "</table>"; ?> -
Thanks for your response Litebearer,
I tried using GROUP By and again seem to have made some progress in the sense that I now have unique values in the pagename column and the corresponding content of each of those values in the content column.
As I understand, GROUP BY must be used with an aggregate function, so I've tried a couple - I tried MAX(date) * and also MAX(id) **, but when I update my table the most recent records are not necessarily those which show. I also tried playing around with MIN instead of MAX, but couldn't seem to find any rhyme or reason. Occasionally with the max dates, values would update but not all the time. I wondered if the alphetetical order of the content may have had something to do with it, and tried inputting data in various ways to see if I could find a pattern, but could not.
* date is a field in my table which has a datetime datatype
** id is my primary key in this table, which is an autoincrementing integer.
Here is my current code. Any more tips?
<?php mysql_connect ("localhost", "MYADMIN", "MYPASSWORD"); mysql_select_db ("MYDATABASE"); // Get all the data from the "example" table $result = mysql_query("SELECT pagename, content, MAX(id) FROM editablepage GROUP BY pagename") or die(mysql_error()); echo "<table border='1' align='center' cellpadding='10' cellspacing='0' bordercolor='#000000'>"; echo "<tr bgcolor='#6175BE'> <th>Page Name</th> <th>Content </th></tr>"; while($row = mysql_fetch_array( $result )) { $pagename = $row['pagename']; $content = $row['content']; echo "<tr bgcolor='#CCCCCC'><td>"; echo $row['pagename']; echo "</td><td>"; echo $row['content']; echo "</td></tr>"; } echo "</table>"; ?>
Spry Tab table
in Javascript Help
Posted
Spent a while googling to try and find a solution to this, so despite it being an old topic, I just wanted to say thanks for posting your solution xcandiottix!
A very grateful,
Dave