acadia

Hi Premiso, I realised I hadnt used correct join syntax and now it is working perfectly and only take 1-2 seconds to run. What I am trying to do is a) a user has a form with the option of a select box where they can choose to filter the information by "provider" and then only show the rows that are assigned to that provider. This works great with WHERE Provider.ProviderID =".$_get["provider"]" (This is on a local network not on the intertnet by the way so no SQL injections are going to happen) However if a provider is not chosen I get a MySQL systax error and I am unsure how to get around this. Do you have any idea? Please ask if you require more information. Regards, Chris

Hi All, I have a query below that when run causes my MySQL server to time out. I was wondering if there was a better way for me to write it? I am not asking you to do it for me just some hints maybe? Regards, Acadia Select student.FirstName AS SFirst, student.Surname AS SSurname, student.MiddleName AS SMiddle, student.DateOfBirth AS SDOB, student.gender AS sgender, student.NZQANumber AS SNZQANumber, student.Address AS Saddress, student.StreetAddress AS SStreetAddress, student.City AS SCity, student.PostCode AS SPostcode, student.PhoneNumber AS Sphone, student.MobileNumber AS Smob, student.eMail AS Semail, student.Notes AS Snotes, student.IDNumber AS SIDNUMBER, student.Active AS Sactive, date_format(EnrollDate,'%%d-%%m-%%Y') AS EnrollDate1, trainer.FirstName AS Tfirst, trainer.Surname AS Tsurn, trainer.gender AS TGender, trainer.DateOfBirth AS TDOB, trainer.IDNumber AS TIDNumber, trainer.Address AS TAddress, trainer.StreetAddress AS TSTreet, trainer.City AS TCity, trainer.PostCode AS TPostcode, trainer.PhoneNumber AS Tphone, trainer.MobileNumber AS Tmob, trainer.eMail AS Temail, Provider.ProviderID, Provider.Name AS ProvName, Provider.Address AS provadress, Provider.StreetAddress AS provstreetaddress, Provider.City AS provcity, Provider.PostCode AS provpostcode, Provider.PhoneNumber AS provphonenumber, Provider.FaxNumber AS provfax, Provider.WebSite AS ProvWebsite, providercontact.FirstName AS PFirst, providercontact.MiddleName AS PMidle, providercontact.Surname AS Psurname, providercontact.gender AS Pgender, providercontact.PhoneNumber AS PPhone, providercontact.MobileNumber AS PMob, providercontact.FaxNumber AS PFax, providercontact.eMail AS Pemail, providercontact.Active AS PActive, providercontact.Provider AS PProvider, providercontact.StreetAddress AS Pstreet, providercontact.Suburb AS PSuburb, providercontact.City AS Pcity, providercontact.POBox AS PPobox, studentcourse1.StudentID, studentcourse1.SCNZQANo, SCNZQAUnitTitle, SCCredit, SCTrainingHours, SCAssesmentHours, SCAdminHours, SCTotalHours, SCCoursecomplete, SCCompetent, SCAssesSubmitDate, SCNZQAUnitStandardVersionSubmitted, SCNZQAUnitStandardVersion, studentpurchaseorder.StudentPurchaseOrderID, studentpurchaseorder.PruchaseOrderNo AS SPPruchaseOrderNo, studentpurchaseorder.ClaimNo AS SPClaimNo, studentpurchaseorder.DateOfPO AS SPDateOfPO, date_format(Invoice_Date,'%d-%m-%Y') AS SIInvoiceDate, studentinvoice.Number AS SINumber, studentinvoice.Hours AS SIHours, date_format(Date_Paid,'%d-%m-%Y') AS SIDate_Paid, studenttrainingpt1.ACCTNA AS STACCTNA, studenttrainingpt1.WINZTIA AS STWINZTIA, studenttrainingpt1.TIAReceived AS STTiaRec, studenttrainingpt1.TrainingLocation AS STTrainingLoc, studenttrainingpt1.DateAssigned AS STDateAssigned, studenttrainingpt1.DateAssessment AS STDAteAssessment, studenttrainingpt1.DateAssessmentSentACC AS STDateAssesssentACC, studenttrainingpt1.DateAssessmentRecACC AS STDateASSESSRecACC, studenttrainingpt1.HoursApproved AS STHoursApproved, studenttrainingpt1.HoursCompleted AS STHoursComplete, studenttrainingpt1.CourseStartDate AS STCoursStartDate, studenttrainingpt1.CourseCompleteDate AS STCourseCompleteDate From student JOIN studenttrainer ON (student.StudentID = studenttrainer.StudentID ) JOIN trainer ON (studenttrainer.TrainerID = trainer.TrainerID ) JOIN studentprovider ON (student.StudentID = studentprovider.StudentID ) JOIN provider ON ( studentprovider.ProviderID = provider.ProviderID ) JOIN studentcasemanager ON (student.StudentID = studentcasemanager.StudentID ) JOIN providercontact ON ( studentcasemanager.ProviderContactID = providercontact.ProviderContactID ) JOIN (SELECT StudentID, studentcourse.NZQANo AS SCNZQANo, studentcourse.NZQAUnitTitle AS SCNZQAUnitTitle, studentcourse.Credit AS SCCredit, studentcourse.TrainingHours AS SCTrainingHours, studentcourse.AssesmentHours AS SCAssesmentHours, studentcourse.AdminHours AS SCAdminHours, studentcourse.TotalHours AS SCTotalHours, studentcourse.CourseComplete AS SCCoursecomplete, studentcourse.Competent AS SCCompetent, studentcourse.AssesSubmitDate AS SCAssesSubmitDate, studentcourse.NZQAUnitStandardVersionSubmitted AS SCNZQAUnitStandardVersionSubmitted, studentcourse.NZQAUnitStandardVersion AS SCNZQAUnitStandardVersion from studentcourse ) AS studentcourse1 ON ( student.StudentID = studentcourse1.StudentID ) JOIN studentinvoice ON ( student.StudentID = studentinvoice.StudentID ) JOIN studentpurchaseorder ON ( student.StudentID = studentpurchaseorder.StudentID ) JOIN studenttrainingpt1 ON (student.StudentID = studenttrainingpt1.StudentID ) JOIN (SELECT traininghours.StudentID,traininghours.StudentCourseID, (IFNULL(SUM(traininghours.TrainingHours), 0) + IFNULL(SUM(traininghours.AssessmentHours), 0) + IFNULL(SUM(traininghours.AdminHours), 0)) AS train FROM traininghours GROUP BY traininghours.StudentID,traininghours.StudentCourseID) AS T2 ON studentcourse1.StudentID = T2.StudentID AND studentcourse1.SCNZQANo = T2.StudentCourseID

Hi All, I have a form which submits information to a database. The dropdown on the form auto populates two fields which can both be changed at input 1)Assessment Hours 2) Training Hours The next field i need is Total Hours. What I would like it to do is add 1+2 together to output a number. I am guessing this is going to involve some javascript but is there a function in PHP to do this? Regards, acadia

Awsome thanks for that I got it sorted pretty fast.

Hi All, I have a form which inserts information regarding a student into the DB which works fine however when it inserts the information into the database(which also inserts a unique identifier ) I would then like it to go to the next page passing the variable of the unique identifier (bla.php?StudentID=4 ). Is this possible as there is no unique identifier until the information has been passed to the database? Regards, Acadia

Upload display.php to the same folder as default.html and add this line into your default.html: <?php include('display.php');?> acadia

Ok I fixed it, When i selected Provider.PhoneNumber in the beginging I changed it by adding an AS clause. Provider.PhoneNumber AS provphonenumber Regards, acadia

Hi all, I have a 3 table join which then populates information on the page. This works great however I have Provider.PhoneNumber and providercontact.PhoneNumber and when I try to pull the information from the database I only get one phonenumber. Im a little stuck could someone take a look? <?php $result = mysql_query("SELECT StudentProviderCaseManagerID, Provider.Name, Provider.Address, Provider.StreetAddress, Provider.City, Provider.PostCode, Provider.PhoneNumber, Provider.FaxNumber, Provider.WebSite, providercontact.FirstName, providercontact.Surname, providercontact.gender, providercontact.PhoneNumber, providercontact.MobileNumber, providercontact.FaxNumber, providercontact.eMail, providercontact.StreetAddress, providercontact.Suburb, providercontact.City, providercontact.POBox FROM StudentProviderCaseManager JOIN provider ON (StudentProviderCaseManager.ProviderID = provider.ProviderID) JOIN providercontact ON (StudentProviderCaseManager.ProviderContactID = providercontact.ProviderContactID) JOIN student ON (StudentProviderCaseManager.StudentID = student.StudentID) WHERE student.StudentID = $_GET[studentID] ORDER BY StudentProviderCaseManagerID"); while($row = mysql_fetch_array($result)) { echo " <table width='958px' border='0' cellspacing='0' cellpadding='0'> <tr> <td width='8%' class='style1'>Provider</td> <td width='15%'> </td> <td width='25%'> </td> <td colspan='2' class='style1'>Case Manager</td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td width='12%'> </td> <td width='40%'> </td> </tr> <tr> <td> Name:</td> <td>"; echo $row['Name']; echo " </td> <td> </td> <td>Surname:</td> <td>"; echo $row['Surname']; echo" </td> </tr> <tr> <td>Address:</td> <td>"; echo $row['Address']; echo"</td> <td> </td> <td>First Name:</td> <td>"; echo $row['FirstName']; echo "</td> </tr> <tr> <td>Suburb:</td> <td>"; echo $row['StreetAddress']; echo "</td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td>City:</td> <td>"; echo $row['City']; echo "</td> <td> </td> <td>Gender:</td> <td>"; echo $row['gender']; echo "</td> </tr> <tr> <td>PO Box:</td> <td>"; echo $row['POBox']; echo "</td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td>Phone Number:</td> <td>"; echo $row['PhoneNumber']; echo "</td> </tr> <tr> <td>Phone Number</td> <td>"; echo $row['PhoneNumber']; echo "</td> <td> </td> <td>Mobile Number:</td> <td>"; echo $row['MobileNumber']; echo "</td> </tr> <tr> <td>Fax Number:</td> <td>"; echo $row['FaxNumber']; echo "</td> <td> </td> <td>Fax Number:</td> <td>"; echo $row['FaxNumber']; echo "</td> </tr> <tr> <td>Website:</td> <td>"; echo $row['WebSite']; echo"</td> <td> </td> <td>eMail Address:</td> <td>"; echo $row['eMail']; echo "</td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td>Street Address:</td> <td>"; echo $row['StreetAddress']; echo "</td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td>Suburb:</td> <td>"; echo $row['Suburb']; echo "</td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td>City:</td> <td>"; echo $row['City']; echo "</td> </tr> <tr> <td> </td> <td> </td> <td> </td> <td>PO Box:</td> <td>"; echo $row['POBox']; echo "</td> </tr> </table>"; }; ?> Regards, acadia

Thank you for your help that worked wonders!

Hi All, I have a 3 table join that works well however I would only like to show the trainers information acording to the StudentID which is passed through a URL from a previous page. Here is my code: <?php $result = mysql_query("SELECT STrainerID, student.FirstName, student.Surname, trainer.FirstName , trainer.Surname, trainer.NZQANumber, trainer.Address, trainer.City, trainer.PostCode, trainer.PhoneNumber, trainer.MobileNumber, trainer.eMail FROM studenttrainer JOIN trainer ON (studenttrainer.TrainerID = trainer.TrainerID) JOIN student ON (studenttrainer.StudentID = student.StudentID) ORDER BY STrainerID"); while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['Surname']; echo "<br />"; echo $row['NZQANumber'] . " " . $row['eMail']; echo "<br />"; }; ?> Could you please take a look, Thanks, acadia

I have just tested it and it works fine for me. Are you sure everything is turned on? IE Apache Acadia

Hi Everyone, I have a simple form below that that dynamically populates a dropdown from one table in the database and when you press insert record it is supposed to insert the name into another table. It does autopopulate the drop down however it does not insert the data. Can someone take a look for me? <?php require_once('Connections/SBS.php'); ?> <?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") { $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) { case "text": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "long": case "int": $theValue = ($theValue != "") ? intval($theValue) : "NULL"; break; case "double": $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL"; break; case "date": $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL"; break; case "defined": $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue; break; } return $theValue; } } $editFormAction = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) { $insertSQL = sprintf("INSERT INTO student 'CaseManager' VALUES (%s)", GetSQLValueString($_POST['CaseManager'], "text")); mysql_select_db($database_SBS, $SBS); $Result1 = mysql_query($insertSQL, $SBS) or die(mysql_error()); $insertGoTo = "index.php"; if (isset($_SERVER['QUERY_STRING'])) { $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; $insertGoTo .= $_SERVER['QUERY_STRING']; } header(sprintf("Location: %s", $insertGoTo)); } mysql_select_db($database_SBS, $SBS); $query_student = "SELECT * FROM student ORDER BY FirstName ASC"; $student = mysql_query($query_student, $SBS) or die(mysql_error()); $row_student = mysql_fetch_assoc($student); $totalRows_student = mysql_num_rows($student); mysql_select_db($database_SBS, $SBS); $query_case = "SELECT FirstName, Surname FROM providercontact ORDER BY FirstName ASC"; $case = mysql_query($query_case, $SBS) or die(mysql_error()); $row_case = mysql_fetch_assoc($case); $totalRows_case = mysql_num_rows($case); ?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1"> <table align="center"> <tr valign="baseline"> <td nowrap="nowrap" align="right">CaseManager:</td> <td><select name="CaseManager"> <?php do { ?> <option value="<?php echo $row_case['FirstName']?>" ><?php echo $row_case['FirstName']?></option> <?php } while ($row_case = mysql_fetch_assoc($case)); ?> </select> </td> </tr> <tr> </tr> <tr valign="baseline"> <td nowrap="nowrap" align="right"> </td> <td><input type="submit" value="Insert record" /></td> </tr> </table> <input type="hidden" name="MM_insert" value="form1" /> </form> <p> </p> <p> </p> </body> </html> <?php mysql_free_result($student); mysql_free_result($case); ?> Cheers, Acadia

I figured it out it was to do with my dropdown array. Acadia

Hi All, I am currently trying to select data from a dropdown menu that then populates other fields. In some ways I have done this. 1. It does populate the drop down 2. the other fields only populate if I choose the last option. with all other options in the dropdown list i get variables undefined. Here is the code: <?php include('config.php'); ?> <?php $query = 'SELECT StudentID, FirstName,NZQANumber,Surname, MiddleName,eMail FROM student '; $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error()); $vbCrLf = "\n"; //create selection list $DropDownList = "<select name='student' id='DropDownListId' onchange='javascript:SelectChanged();'> "; $DropDownList .= "<option value=''></option>".$vbCrLf; while($array = mysql_fetch_array($result)) { $heading = $array['FirstName'].$array['MiddleName'].$array['Surname']; $DropDownList .= "<option value=$heading>$heading</option> ".$vbCrLf; $FirstArrayPhp = 'FirstArray["'.$heading.'"]="'.$array['NZQANumber'].'";'.$vbCrLf; $EmailArrayPhp = 'EmailArray["'.$heading.'"]="'.$array['eMail'].'";'.$vbCrLf; } $DropDownList .= "</select> ".$vbCrLf; ?> <html> <head> <title>Test</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script language="Javascript" type="text/javascript"> var FirstArray = new Array(); <?php echo $FirstArrayPhp; ?> var EmailArray = new Array(); <?php echo $EmailArrayPhp; ?> function SelectChanged() { var Student = document.getElementById('DropDownListId').value; document.getElementById('first').value = FirstArray[student]; document.getElementById('email').value = EmailArray[student]; } </script> </head> <body> <form action="" method="post" name="form1" class="style1"> <table width="989" border="1"> <tr> <td>Student Name:</td> <td> <?php echo $DropDownList; ?> </td> <td> </td> </tr> <tr> <td width="150">Student Number:</td> <td width="307"><input type="text" name="first" id="first" class="heading" value="<?php echo $array['first']; ?>"><br /> </td> <!--I need this autopopulated with studentnumber from the database--> <td width="510">This will auto populate after trainer has chosen student for checking it is the right student.</td> </tr> <tr> <td>Email:</td> <td><input type="text" name="email" id="email" class="date"value="<?php echo $array['email']; ?>"></td> <td>Student course they are on(this should auto populate after trainer chooses student</td> </tr> <tr> <td>Time:</td> <td><input name="Time" type="text" id="Time" size="50" maxlength="50"></td> <td> </td> </tr> <tr> <td>Hours:</td> <td><input name="Hours" type="text" id="Hours" size="50" maxlength="50"></td> <td> </td> </tr> <tr> <td>Comment:</td> <td><input name="Password" type="text" id="Password" size="50" maxlength="50"></td> <td> </td> </tr> <tr> <td> </td> <td> </td> <td><input type="submit" name="SubmitNewFacility" id="SubmitNewFacility" value="Submit"></td> </tr> </table> <p> </p> </form> </body> </html> Can you please take a look? Cheers, Chris

Yes Maq its working well now. Thanks for helping. It now displays every changed detail on the output page as it should. Chris