WAMFT1

Excellent, this partially working, are you able to assist a little further? The above pulls through the last note but I need it to pull the last note for each prospect_id in database_prospects. Effectively all records from database_prospects and the last record for each from database_prospect_notes. Hopefully this makes sense?

Attached is a link to the SQL dump, thanks for your help. Hopefully there will be a work around. http://www.wamft.com.au/prospects.sql

Hi all, I am trying to create a query to pull all information from one table and just the last record for each prospect_id. I have been able to create the query in MSAccess (below) but cannot work out how to get this to work in php. Can someone please help? In access this works but to run on SQL it does not return any data. The problem I believe lies around the LAST request. SELECT DATABASE_Prospects.FirstName, DATABASE_Prospects.LastName, DATABASE_Prospects.State, DATABASE_Prospects.Active, DATABASE_Prospects.prospect_id, DATABASE_Prospects.dlr_group, DATABASE_Prospects.Income, DATABASE_Prospects.Probability, DATABASE_Prospects.added, Last(DATABASE_Prospect_Notes.comment) AS LastOfcomment FROM DATABASE_Prospect_Notes RIGHT JOIN DATABASE_Prospects ON DATABASE_Prospect_Notes.prospect_id = DATABASE_Prospects.prospect_id GROUP BY DATABASE_Prospects.FirstName, DATABASE_Prospects.LastName, DATABASE_Prospects.State, DATABASE_Prospects.Active, DATABASE_Prospects.prospect_id, DATABASE_Prospects.dlr_group, DATABASE_Prospects.Income, DATABASE_Prospects.Probability, DATABASE_Prospects.added HAVING (((DATABASE_Prospects.Active)=1));

May have misread your question, the form is below <form method="post"> <table width="600" border="0" align="center" > <tr><td class="standard_left">Date:</td> <td class="standard_left"><input name="date" type="text" id="date" value="<?php print(Date("d-m-Y H:i:s")); ?>" readonly></td> </tr> <tr><td width="141" class="standard_left">Added By:</td> <td class="standard_left"><input name="staff_id" type="hidden" id="staff_id" value="<?php echo $staff_id?>"><?php echo $FirstName?> <?php echo $LastName?><input name="prospect_id" type="text" id="prospect_id" value="<?php echo $prospect_id?>" readonly></td> </tr> <tr><td valign="top" class="standard_left">File Note:</td> <td><textarea name="comment" id="comment" cols="45" rows="5"></textarea></td> </tr> <tr><td class="text-questions"> </td> <td class="text-questions"> </td> </tr> <tr><td colspan="2" class="text-send"><span class="text-questions" style="text-align: center"><input type="button" onClick="location.href='database_view_prospects.php'" value="Close"></span><input type="submit" name="submit" id="submit" value="Save Changes"></td> </tr> <tr><td> </td> <td> </td> </tr> </table> </form>

The form posts and all the information is captured except the prospect_id field but this does not show on the form either.

Hi all I know this is old code and it is in the process of being updated. In the meantime I need to get this simple code working. I cannot get the prospect_id to populate in the form or on sql. Can someone please help me shed some light on this. <?php require("../edb.php"); $prospect_id =$_REQUEST['prospect_id']; $staff_id =$_SESSION['uid']; $result = mysql_query("SELECT * FROM `eusers` WHERE id = $staff_id"); $test = mysql_fetch_array($result); if (!$result) { die("Error: Data not found.."); } $FirstName=$test['FirstName']; $LastName=$test['LastName']; $staff_id=$test['id']; if(isset($_POST['submit'])){ $staff_id = protect($_POST['staff_id']); $date = protect($_POST['date']); $prospect_id = protect($_POST['prospect_id']); $comment = protect($_POST['comment']); $res = mysql_query("INSERT INTO `DATABASE_Prospect_Notes` (`prospect_id`, `staff_id`, `comment`, `date`) VALUES ('".$prospect_id."', '".$staff_id."', '".$comment."', '".$date."')"); echo "Saved!"; } ?>

Hi There I am trying to create a login screen within a framed site that when you log in it takes you to a new page for the portal. When I enter the login credentials the frame I am working in goes blank and does not take me to the portal. Can someone please help me out with the coding I have. The coding itself has 2 parts... 1) login and 2) if already logged in gives you a button to open to portal. <? if(isset($_POST['submit'])){ //protect the posted value then store them to variables $Username = protect($_POST['Username']); $Password = protect(sha1($_POST['Password'])); //Check if the username or password boxes were not filled in if(!$Username || !$Password){ //if not display an error message echo "<center>Please enter your <b>Username</b> and <b>Password</b>!</center>"; }else{ //if the were continue checking //select all rows from the table where the username matches the one entered by the user $res = mysql_query("SELECT * FROM `eusers` WHERE `Username` = '".$Username."'"); $num = mysql_num_rows($res); //check if there was not a match if($num == 0){ //if not display an error message echo "<center>The <b>Username</b> or <b>Password</b> you supplied is incorrect!</center>"; }else{ //if there was a match continue checking //select all rows where the username and password match the ones submitted by the user $res = mysql_query("SELECT * FROM `eusers` WHERE `Username` = '".$Username."' AND `Password` = '".$Password."'"); $num = mysql_num_rows($res); //check if there was not a match if($num == 0){ //if not display error message echo "<center>The <b>Username</b> or <b>Password</b> you supplied is incorrect!</center>"; }else{ //if there was continue checking //split all fields fom the correct row into an associative array $row = mysql_fetch_assoc($res); //check to see if the user has not activated their account yet if($row['Active'] != 1){ //if not display error message echo "<center>Your login has been <b>deactivated</b>, Please contact Website Administrator for assistance.</center>"; }else{ //if they have log them in //set the login session storing there id - we use this to see if they are logged in or not $_SESSION['uid'] = $row['id']; //show message echo "<center>You have successfully logged in!</center>"; //update the online field to 50 seconds into the future $time = date('U')+50; mysql_query("UPDATE `eusers` SET `Online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'"); //redirect them to the usersonline page echo"<a target=\'_blank\" href='portal/index2.php'></a>";?> <?php if(isset($_POST['submit2'])){ //update the online field to 50 seconds into the future $time = date('U')+50; mysql_query("UPDATE `eusers` SET `Online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'"); //redirect them to the usersonline page echo"<script type='text/javascript' language='Javascript'>window.open('portal/index2.php');</script>";}}}}}}?> <?php include "portal/edb.php"; //if the login session does not exist therefore meaning the user is not logged in if(strcmp($_SESSION['uid'],"") == 0){ //display and error message echo "<form name='main.php' method='post' action=''> <table bgcolor='#FFFFFF' width='320' border='0' align='center' cellpadding='0' cellspacing='0'> <tr> <td ><table border='0' align='center' cellpadding='2' cellspacing='0' class='page-border'> <tr><td colspan='2' class='text_heading_center'>Portal</td> </tr> <tr> <td width='100' class='text_standard_right'>Username:</td> <td width='150' class='text_standard_right'><input name='Username' type='text' class='text_standard_left' /></td> </tr> <tr> <td class='text_standard_right'>Password:</td> <td class='text_standard_right'><input name='Password' type='password' class='text_standard_left' /></td> </tr> <tr> <td height='29' colspan='2' align='right'><input type='submit' name='submit' value='Login' /></td> </tr> </table>"; }else{ $time = date('U')+50; $update = mysql_query("UPDATE `eusers` SET `Online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'"); $id =$_REQUEST['id']; $result=mysql_query("SELECT * FROM `eusers` WHERE id='".$_SESSION['uid']."'"); print"<form name='main.php' method='post' action=''>"; print"<br/>"; print"You are already logged into the Portal"; print"<br/>"; print"Click here to return the the Portal"; print"<br/>"; print"<input name='submit2' type='button' value='Return to Portal' />"; print"</form>"; }?>