FooKelvin

$date = new DateTime(); // now $dateto = $date->format('Y-m-01'); $datefrom = $date->sub(new DateInterval('P1Y'))->format('Y-m-01'); // minus 1 year // initialize array $dp = new DatePeriod($date, new DateInterval('P1M'),12); $data = array(); foreach ($dp as $d) { $data[$d->format('F')] = array( 'reg' => 0, 'app' => 0 ); } //var_dump($data); $sql = "SELECT id,registerdate,approvedate FROM [A_Sys].[dbo].[member] WHERE registerdate >= '2016-11-22' AND registerdate < '2016-12-22' ORDER BY registerdate"; $stmt = sqlsrv_query($conn, $sql,$datefrom,$dateto); while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ){ if ($row['registerdate']) { $rm = (new DateTime($row['registerdate']))->format('F'); $data[$rm]['reg']++; } if ($row['approvedate']) { $am = (new DateTime($row['approvedate']))->format('F'); $data[$am]['app']++; } } // assemble the output $tabledata = ''; foreach ($data as $month=>$vals) { $tabledata .= "<tr><td>$month</td><td>{$vals['reg']}</td><td>{$vals['app']}</td></tr>\n"; } ?> <table> <thead> <tr><th>Month</th><th>Registered</th><th>Approved</th></tr> </thead> <tbody> <?=$tabledata?> </tbody> </table> The table head wont display when i put in ->format('F');

Hi Barand, Thanks. I am stuck with this code. $newarray = array(11 => array(), 12 => array(), 1 => array(), 2 => array(), 3 => array(), 4 => array(), 5 => array(), 6 => array(), 7 => array(), 8 => array(), 9 => array(), 10 => array()); $data = array(); $sql = "SELECT id,registerdate,approvedate FROM [A_Sys].[dbo].[member]"; $stmt = sqlsrv_query($conn, $sql); if ($stmt === false) { die(print_r(sqlsrv_errors(), true)); } while (list($id,$rd,$ad) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$id])) { $data[$id]['id'] = $id; $data[$id]['arr'] = $newarray; } if ($rd) { //register date month $item = date_format(($rd), 'm'); //m/d/y $data[$id]['arr'][$m][] = $item; var_dump($item); } if ($ad) { //approve date month $item2 = date_format(($ad), 'm'); //m/d/y $data[$id]['arr'][$m][] = $item2; var_dump($item2); } } ?> I var_dump item & item2. string(2) "11" string(2) "12" string(2) "11" string(2) "12" string(2) "12" string(2) "12" string(2) "12" string(2) "12" string(2) "11" string(2) "11" //here is my database id member registerdate approvedate 1 mem1 2016-11-22 2016-12-22 2 mem2 2016-11-22 2016-12-22 3 mem3 2016-12-22 2016-12-22 4 mem4 2016-12-22 2016-12-22 5 mem5 2016-11-22 2016-11-22

Hi All, how to display php table like attachment? it mean that, there is 2 members registered in November, but it have 0 member get approve. There is 3 members registered in December, and total have 5 members get approved (2 from November + 3 from December). Each Register and Approve have their individual dates. I get all my thing wrong as i can loop only by either Register date or Approved date. example. member1 ->Registered date : 21/11/2016 (Fall under November) ->Approved date : 2/12/2016 (Fall under December) member2 ->Register date : 23/11/2016 (Fall under November) ->Approved date: 3/12/2016 (Fall under December) Thanks in advance.

Is like when the Description column data type = Short Text.. my output display perfectly.. When i change the data type to = Long Text.. my output show empty..

$sql = "SELECT * FROM (requestorInfo i LEFT JOIN requestStatus s ON i.RequestID = s.RequestID) INNER JOIN requestRoomInfo ri ON ri.RequestID = i.RequestID"; $rs = odbc_exec($conn, $sql); PHP CODE echo "<tbody>"; while (odbc_fetch_row($rs)) { $desc = odbc_result($rs, "Description"); $tbody = '<tr><td>'.$desc.'</td> echo $tbody . ''; echo '</tr>'; } echo '</table>'; The $desc display out when the data type in Ms Acess is Short Text. Once i change to Long Text, it display empty.

Hi All, I facing a problem with Ms Access. When i set my datatype to "Short Text", the data display perfectly. just because of limitation of "Short Text", it can only handle 255. So i decided to change the datatype to "Long Text", but the text wont display our. FYI, im using for web base data and i am using PHP language. Thanks

Hi All, I not sure what is the function call. Is something like a button at my original page, when i click the button, in will prompt up a new page, the new page consist of a form. eg: Email and Entitlement. So i can have multiple email in that form. After i click on ok, the email will be append in the original page. is something similar with this function, but im not sure what is the function call. Please refer to my picture. Thanks for your help.

Hi Mac, Thanks for your help. In case somebody need it. $sql2 = "SELECT DISTINCT [question_id],q_text FROM [RSA].[dbo].[feedbug] ORDER BY [question_id]"; $ques = array(); $stmt2 = sqlsrv_query($conn, $sql2, $ques); while ($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) { $ques[$row['question_id']] = $row['q_text']; } $sql = "SELECT [question_id], [Expr3],[Expr2] FROM [RSA].[dbo].[feedbug] ORDER BY [Expr2]"; $data = array(); $stmt = sqlsrv_query($conn, $sql); while (list($c, $q,$p) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$p][$c])) { $data[$p][$c]= $newArray2; } $data[$p][$c][]= $q; } This is working perfectly using Mac's method.

Okay, i found an error in the SQL. Here is the latest version: $sql = "SELECT [question_id], [Expr3],[Expr2] FROM [RSA].[dbo].[feedbug] ORDER BY [Expr2]"; $data = array(); $stmt = sqlsrv_query($conn, $sql); while (list($c, $q,$p) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$p][$c])) { $data[$p][$c]= $newArray2; } $data[$p][$c]= $q; } print_r($data); ?> Output: Array ( [M01] => Array ( [1] => Yes [02A ] => Yes [02B] => [3] => Newly Open [4] => Bad service [5] => Chicken Chop [6] => No [7] => [8] => No [9] => Yes ) [M02] => Array ( [1] => Yes [02A ] => Yes [02B] => [3] => Newly Open [4] => Dirty [5] => The Mushroom soup [6] => No [7] => [8] => No [9] => Yes ) ) I hope i am doing the correct thing..

Thanks. Is working now. The second part of array, am i doing thing correctly? it seem that nothing to print out. Here is my code. $sql = "SELECT [question_id], [Expr3],[Expr2], FROM [RSA].[dbo].[feedbug] ORDER BY [Expr2]"; $stmt1 = sqlsrv_query($conn, $sql); $data = array(); while (list($qid,$e3,$e2,$fb) = sqlsrv_fetch_array($stmt1, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$qid][$e3])) { $data[$qid][$e3]= $newArray; } $data[$qid][$e3][$e2]= $fb; print_r($fb); }

Hi Mac, Thanks. I tested the array part. But I am stuck in the array id. I print_r your array and this is what i get: Array ( [1] => q1 [2A] => q2 a [2B] => q2 b [3] => q3 [4] => q4 mult ) This is my print_r: Array ( [0] => Do you like our signature Noodles? [1] => If No,Why? [2] => Do you love our food? [3] => What caused you enter to our caf�? [4] => Why have you decided to not dine in our caf�? [5] => What did you like most about our food? [6] => What did you dislike most about our food? [7] => Which caf� you prefer to go other than us? [8] => Do you have any further comments? [9] => Would you consider returning to our caf� in next 2 weeks? ) here is my source code: $sql2 = "SELECT DISTINCT [question_id],q_text FROM [RSA].[dbo].[feedbug] ORDER BY [question_id]"; $ques = array(); $stmt2 = sqlsrv_query($conn, $sql2, $ques); while ($row = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) { $ques[] = $row['q_text']; } print_r($ques);

Hi All, I have tried few weeks. but I still unable to get my expected answer. Please Help. As attachment are my data and expected table to be shown in HTML format. loopanswer.zip

Thanks Barand!

Hi Guys, i have a problem regarding on joining multiple tables, i was trying up for few days, but still can't get it done. This database is to join employee survey question regarding to one of the cafe. Below are my current non-working query. SELECT question.*, question_options.*, answer.*,txtanswer.* FROM question LEFT JOIN question_options ON question.question_id = question_options.question_id LEFT JOIN answer ON answer.option_id = question_options.qtn_option_id JOIN txtanswer ON txtanswer.qtn_option_id= question_options.qtn_option_id WHERE answer.empid = 'EMP8969' This query show no result when i join to the txtanswer table. Please download the attachment to view my table data. Thanks for your help. Jointable.zip

Hi Guys, I have a application, once the button is trigger, the php mailer will send outlook meeting to participant and block their calendar. Like This: My question is, how can i attach excel spreadsheet in this calendar? This is the expected result from user end: As you can see there is a excel spreadsheet attached in the appointment email. Above are my codes: $text = "BEGIN:VCALENDAR VERSION:2.0 PRODID:-//Microsoft Corporation//Outlook 11.0 MIMEDIR//EN METHOD:REQUEST BEGIN:VTIMEZONE TZID:Singapore Standard Time BEGIN:STANDARD TZOFFSETFROM:+0800 TZOFFSETTO:+0800 END:STANDARD END:VTIMEZONE BEGIN:VEVENT UID:" . md5(uniqid(mt_rand(), true)) . "example.com DTEND:". $date . "T" . $endTime . "00 DTSTAMP:" . gmdate('Ymd') . 'T' . gmdate('His') . "Z DTSTART:". $date . "T" . $startTime . "00 SUMMARY:" . $subject . " ORGANIZER;CN=" . $organizer . ":mailto:" . $organizer_email . " LOCATION:" . $location . " DESCRIPTION:" . $desc . " TRANSP:TRANSPARENT BEGIN:VALARM TRIGGER:-PT30M ACTION:DISPLAY DESCRIPTION:Reminder END:VALARM END:VEVENT END:VCALENDAR"; I have tested this line of code: ATTACH;ENCODING=BASE64;VALUE=BINARYXFILENAME=iq.xlsx:????? //<-- Not sure what to code here. But i cant get what i want. Please Help.

Hi Guys, I have a dynamic input field, consists of requirement details. Each input field have their own sub amount. at the end, the total will sum all the sub amount. But i have stuck, i do not know how to count the amount, since it's dynamic, how can i get the dynamic id and count it as sub total or grand total? Please refer to my codes above: javascript: $(document).ready(function () { var max_fields = 10; //maximum input boxes allowed var wrapper = $(".input_fields_wrap"); //Fields wrapper var add_button = $(".add_field_button"); //Add button ID var x = 1; //initlal text box count $(add_button).click(function (e) { //on add input button click e.preventDefault(); if (max_fields > x) { //max input box allowed x++; //text box increment $(wrapper).append('<div><table><tr><td style="width:272px">Notice Period - '+x+'</td><td style="width: 14%;"><input name="txt_pe_start[]" type="text" value="" class="input_class start" style="width: 100px; vertical-align: top;"/></td><td style="width: 165px;display: inline;padding: 0 5px 0 5px;">Consists of <input name="consits[]" value="" style="width: 25px;">Day(s)</input></td><td style="width: 165px;display: inline;padding: 0 5px 0 5px;">Short Notice <input name="shortNotice[]" class="sndays" id="sndays_'+x+'" value="" style="width: 25px;">Day(s)</input></td><td style="width: 165px;display: inline;padding: 0 5px 0 5px;">Amount Due<input id="amountdue_'+x+'" name="amountdue[]" style="width: 100px;"></input></td></tr></table></div>'); //add input box } });// JavaScript source code html: <div class="input_fields_wrap"> <div> <table> <tr> <td style="width:272px">Notice Period - 1</td> <td style="width: 14%;"><input name="txt_pe_start[]" type="text" value="" class="input_class start" style="width: 100px; vertical-align: top;"/></td> <td style="width: 165px;display: inline;padding: 0 5px 0 5px;">Consists of <input name="consits[]" style="width: 25px;">Day(s)</input></td> <td style="width: 165px;display: inline;padding: 0 5px 0 5px;">Short Notice <input name="shortNotice[]" class="sndays" id="sndays_1" style="width: 25px;">Day(s)</input> </td> <td style="width: 165px;display: inline;padding: 0 5px 0 5px;">Amount Due<input id="amountdue_1" name="amountdue[]" style="width: 100px;"></input></td> </tr> </table> </div> </div> <button class="add_field_button">Add More Fields</button> Total:<div></div>

Hello, I currently have two tables. question and question_option table. each question might have more than one question_option. Here is my excel sheet. https://onedrive.live.com/edit.aspx?cid=b28ee3cd15a16520&page=view&resid=B28EE3CD15A16520!1937&parId=B28EE3CD15A16520!1932&app=Excel So, here is my php code: $conn = sqlsrv_connect($serverName, $connectionInfo); $data = array(); $trows = ''; $sql = " SELECT q.[question_id], q.[q_text],qopt.[option_label],qopt.[opt_text] FROM [RSA].[dbo].[question] q LEFT JOIN [RSA].[dbo].[question_options] qopt ON q.[question_id]=qopt.[question_id] ORDER BY [question_id] ASC"; $stmt = sqlsrv_query($conn, $sql); if ($stmt === false) { die(print_r(sqlsrv_errors(), true)); } while (list($qid, $qtxt, $qoptlabel, $qopttxt) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) { if (!isset($data[$qid])) { $data[$qid]['question_id'] = $qid; $data[$qid]['q_text'] = $qtxt; //$data[$qid]['option_label'] = $qoptlabel; //$data[$qid]['opt_text'] = $qopttxt; } } foreach ($data as $qid => $qdata) { $trows .= "<tr><td class='cn'>{$qdata['question_id']}</td>" . "<td>{$qdata['q_text']}</td>"; //. "<td>{$cdata['option_label']}</td>" //. "<td>{$cdata['opt_text']}</td>"; foreach ($qdata as $option) { $trows .= "<td class='dt'>{$option['$qopttxt']}</td>"; } $trows .= "</tr>\n"; } ?> <table border='1'> <?php echo $trows?> </table> I able to for each all the question, but i have stuck over the option value there. The option value most of them is check boxes.

Hi Barand, i try to understand the data model, so i try to list down what kind of data will store into database. I have listed down the purpose of the table and also the questions i have in the word file above. Please have a look and please correct me if i am wrong. Sorry about you have to redirect to my word file, because i try to paste in here, the align seem to be run. https://onedrive.live.com/redir?resid=B28EE3CD15A16520!1933&authkey=!ANInHOvg10zD6I4&ithint=file%2cdocx By the way, how if employee select "other" as their choice, where should i store the "other" input?

Hi Barand, Thank You. A bit confuse, the data model also included questionnaire question? So when i create a html, i can loop the question and the check box option? Am i right?

Do you mean i cannot store those check box value with comma in a same row?

Hi Guys, I have created a html which consist of a list of check box and open text comment. <html> <body> <form action="" method="post" enctype="multipart/form-data"> <div style="width:200px;border-radius:6px;margin:0px auto"> <table border="1"> <tr><td>Name</td><td><input name="name"/></td></tr> <tr> <td colspan="2">Select Technolgy:</td> </tr> <tr> <td>PHP</td> <td><input type="checkbox" name="techno[]" value="PHP"></td> </tr> <tr> <td>.Net</td> <td><input type="checkbox" name="techno[]" value=".Net"></td> </tr> <tr> <td>Java</td> <td><input type="checkbox" name="techno[]" value="Java"></td> </tr> <tr> <td>Javascript</td> <td><input type="checkbox" name="techno[]" value="javascript"></td> </tr> <tr> <td>Comments</td> <td><input name="comment"/></td> </tr> <tr> <td colspan="2" align="center"><input type="submit" value="submit" name="sub"></td> </tr> </table> </div> </form> </body> </html> I manage to store the check box and comment in a database. I store each submit check box in a same column of data and i separated the multiple check box value with comma. My question is, how to turn those data, how can i compare those value and generate a report like this?

This is a format that i need to transform into a web form. just i not sure how should i start, which database structure should i use. i need some advice. 1. Has your manager spoken to you with regards to your resignation? Yes/No Comment: _______________________________________________________________________________________ 2a. Have you considered other jobs in ABC Company before making this decision? Yes/No 2b. If No, is there any particular area of interest that you would like to consider? (HR can assist with job matches) ________________________________________________________________________________________________ 3. Which factors attracted you to join ABC Company? (check all that apply) A. Interesting Job C. Working environment B. Opportunities for training, advancement, career growth D. Pay & Benefits E. Other ________________________________ 4. What are your reasons for leaving ABC Company? (check all that apply) A. Change in career H. Job Nature, Work load or work hours I. Quality of supervision B. Family or personal needs J. Lack of recognition for work C. Moving from area K. Lack of advancement opportunities D. To further education E. Pay dissatisfaction F. Benefits dissatisfaction G. Work environment

Hi All, I have a set of question to bring out. I will be need to setup a questionnaires page to let user to fill in. The questions is statics, so i don't think i will setup a module to let admin to setup questions/ update questions. So, the thing i need to focus is to create a questionnaires form to get all the inputs and store into the database. 1. Has your manager spoken to you with regards to your resignation? Yes/No Comment: _______________________________________________________________________________________ 2a. Have you considered other jobs in ABC Company before making this decision? Yes/No 2b. If No, is there any particular area of interest that you would like to consider? (HR can assist with job matches) ________________________________________________________________________________________________ 3. Which factors attracted you to join ABC Company? (check all that apply) A. Interesting Job C. Working environment B. Opportunities for training, advancement, career growth D. Pay & Benefits E. Other ________________________________ 4. What are your reasons for leaving ABC Company? (check all that apply) A. Change in career H. Job Nature, Work load or work hours I. Quality of supervision B. Family or personal needs J. Lack of recognition for work C. Moving from area K. Lack of advancement opportunities D. To further education E. Pay dissatisfaction F. Benefits dissatisfaction G. Work environment So if i create a html form and pass all the value to database, im not sure how to store the check box value, have a look to the attachment. is it not a good practice to store table in a row right? but how could i store a same form in a different row of data? how to store the check box value? i need to create an individual column for them? what is the value should i store when click all the value? this form will generate report in future, i still dun have the template currently, but it should be something like average how many people choose the answer. Need Help..~!

Hi All, Both of you a perfect !!! Thank you so much!