Jump to content

cainam29

Members
  • Content count

    75
  • Joined

  • Last visited

Community Reputation

0 Neutral

About cainam29

  • Rank
    Regular Member
  1. I have a login session where it checks the user name and displays it to the form (it displays FirstName LastName). That username is also being used as a variable to pull up data in db. Now I also have another page where user's can update the db, I dont have a problem if the user will update it with a complete FirstName LastName entry because it will just be the same as the one's being used by the login session, but sometimes they just update it with FirstName. The problem starts when I have values in TEST db under Tester column and in USERS db under User column that is of different values. It would have two diff values when a user did not enter the full FirstName LastName. For example, FirstName1 LastName1 is the value in USERS db User column - this is fixed and is being used in a login session User updated Tester column in TEST db with just FirstName1 - this is different from the User column above Here is what I am trying to do, Getting list of tickets from the TEST db where datefrom and dateto and using a variable for the values that is in User column under USER DB If User column under USERS db = Tester column under Test db which is FirstName1 LastName1 - it will be good as I will be able to get tickets under FirstName1 LastName1. But I will not be able to get ticket which is still assigned to that same person because the value in Tester column under Test db is just FirstName1. If User column under USERS db (FirstName1 LastName1) is not equal to Tester column under Test db which is just FirstName1 - I will not get tickets assigned to FirstName1 as my variable is equal to FirstName1 LastName1. I hope that I explained it clearly, here is my code, Here is the variable that I am posting, $uid = false; if(isset($_POST['uid'])){ $uid = $_POST['uid']; } And here is the query, $sql = 'SELECT `id`, `date_implemented`, `tester`, `comments` FROM `tracker` WHERE `tester` = :uid AND `scheduled_start_date` BETWEEN :d1 AND :d2'; $stmt = $conn->prepare($sql); $stmt->bindParam(':uid', $uid, PDO::PARAM_STR); $stmt->bindParam(':d1', $date['from'], PDO::PARAM_STR); $stmt->bindParam(':d2', $date['to'], PDO::PARAM_STR); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); I would need help in passing that variable (uid) such that I can use `WHERE tester LIKE uid`. In that case whether the tester column just contains FIRSTNAME1 or a complete FIRSTNAME1 LASTNAME1, I would be able to get all tickets assigned to FIRSTNAME1.
  2. cainam29

    PHPMail always sending when page is refreshed

    Yes you are correct, the first time the submit button is clicked to load the page and shows the data, it is sending an email as well which it should not be doing. it should just be sending an email when i click on the submit button the second time, that is after i edit the data and when i wanted to update the db and send an email (this is the part where i wanted it to send an email).
  3. While I'm just trying to pull up data from the db, as soon as the page shows up with the data it is already sending an email. How can I prevent it from sending the email right away. What I want to happen is I retrieved data from the db > shows it to a page > edit the page > update the db and send an email. This is the only point where I want an email to be sent out. I am just using a single Submit button to First retrieved the data from db (it is already sending an email here which I don't want) and Second to update the db with new values and send an email (it is here that I want it to send an email). Here is the code that I am using, $update = filter_input_array(INPUT_POST, $update_args); $date = filter_input_array(INPUT_POST, $date_args); $result = NULL; $colcomments = NULL; $dsn = 'mysql:dbname='.DBname.';host='.DBhost.';port='; try { $conn = new PDO($dsn, DBuser, DBpswd); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); if($date['from'] !== NULL && $date['to'] !== NULL){ // get table data if(isset($_POST['submit'])) { if(!isset($_SESSION['update'])) { $sql = 'SELECT `id`, `changeid`, `taskid`, `summary`, `type`, `reviewed_approved_by`, `scheduled_start_date`, `implemented_by` FROM `tracker` WHERE `scheduled_start_date` BETWEEN :d1 AND :d2'; $stmt = $conn->prepare($sql); $stmt->bindParam(':d1', $date['from'], PDO::PARAM_STR); $stmt->bindParam(':d2', $date['to'], PDO::PARAM_STR); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); // get column comments $sql = "select t.column_name, t.column_comment from information_schema.columns t where t.table_name = 'tracker'"; $stmt = $conn->prepare($sql); $stmt->execute(); $colcomments = $stmt->fetchAll(PDO::FETCH_KEY_PAIR); }else { unset($_SESSION['update']); function two_dim_array_to_html_table($arr, $colcomments){ $ret = "<table border='1' width='auto' cellpadding='1px' cellspacing='0px' align='center'>\n"; $ret .= "\t<tr>\n"; foreach($arr[0] as $key => $val){ $ret .= "\t\t<th>".$colcomments[$key]."</th>\n"; } $ret .= "\t</tr>\n"; foreach($arr as $row){ $ret .= "\t<tr>\n"; foreach($row as $column){ $ret .= "\t\t<td>".$column."</td>\n"; } $ret .= "\t</tr>\n"; } $ret .= "<table>\n"; return $ret; } if($result) { $Body = "<html>\n" . "<head>\n" . "</head>\n" . "<body>\n" . two_dim_array_to_html_table($result, $colcomments) . "</body>\n" . "</html>\n"; //Setting up Mail $mail = new PHPMailer(); if (EMAIL_USE_SMTP) { // Set mailer to use SMTP $mail->IsSMTP(); //useful for debugging, shows full SMTP errors //$mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only // Enable SMTP authentication $mail->SMTPAuth = EMAIL_SMTP_AUTH; // Enable encryption, usually SSL/TLS if (defined(EMAIL_SMTP_ENCRYPTION)) { $mail->SMTPSecure = EMAIL_SMTP_ENCRYPTION; } // Specify host server $mail->Host = EMAIL_SMTP_HOST; $mail->Username = EMAIL_SMTP_USERNAME; $mail->Password = EMAIL_SMTP_PASSWORD; $mail->Port = EMAIL_SMTP_PORT; } else { $mail->IsMail(); } $mail->From = EMAIL_FROM_ADDRESS; $mail->FromName = EMAIL_FROM_NAME; $mail->AddAddress('test.test@domain.COM'); $mail->Subject = 'Daily Tasks - "'.date('d-m-Y').'"'; $mail->WordWrap = 100; $mail->IsHTML(true); $mail->Body = $Body; $mail->Send(); } if(isset($update['id']) && is_array($update['id']) && !empty($update['id'])){ $sql = "UPDATE `tracker` SET `changeid` = :bv_changeid ,`taskid` = :bv_taskid ,`summary` = :bv_summary ,`type` = :bv_type ,`reviewed_approved_by` = :bv_reviewed_approved_by ,`scheduled_start_date` = :bv_scheduled_start_date ,`implemented_by` = :bv_implemented_by WHERE `id` = :bv_id "; if($stmt = $conn->prepare($sql)){ $stmt->bindParam(':bv_changeid', $changeid, PDO::PARAM_INT); $stmt->bindParam(':bv_taskid', $taskid, PDO::PARAM_INT); $stmt->bindParam(':bv_summary', $summary, PDO::PARAM_STR); $stmt->bindParam(':bv_type', $type, PDO::PARAM_STR); $stmt->bindParam(':bv_reviewed_approved_by', $reviewed_approved_by, PDO::PARAM_STR); $stmt->bindParam(':bv_scheduled_start_date', $scheduled_start_date, PDO::PARAM_STR); $stmt->bindParam(':bv_implemented_by', $implemented_by, PDO::PARAM_STR); $stmt->bindParam(':bv_id', $id, PDO::PARAM_INT); $updateRowCount = 0; // update multiple rows - all of selected in form foreach($update['id'] as $key => $val){ $changeid = $update['changeid'][$val]; $taskid = $update['taskid'][$val]; $summary = $update['summary'][$val]; $type = $update['type'][$val]; $reviewed_approved_by = $update['reviewed_approved_by'][$val]; $scheduled_start_date = $update['scheduled_start_date'][$val]; $implemented_by = $update['implemented_by'][$val]; $id = $val; $stmt->execute(); $updateRowCount += $stmt->rowCount(); } if($updateRowCount > 0){ $message['info'][] = "Updated ".$updateRowCount." row/s"; } else { $message['warning'][] = "CRQ Tracker db not updated."; } } else { $message['error'][] = "Prepare error!!!"; } } } }else { if(is_array($result)){ echo ' <fieldset> <legend>Assign Ticket</legend> <div>Changes will affect updated rows only.</div> <p></p> <table width=auto cellpadding=1px cellspacing=0px border=1 align=center id=assign> <thead> <tr>'; // column comment from DB as column header foreach($result[0] as $key => $val){ echo '<th align=center>'.$colcomments[$key].'</th>'; } echo ' </tr> </thead> <tbody>'; foreach($result as $row => $info){ echo '<tr>'; foreach($info as $key => $val){ if($key=='id'){ echo '<td title="'.$colcomments[$key].'">'.$val.'.<input type="hidden" name="'.$key.'['.$info['id'].']" value="'.$val.'" id="rowid_'.$val.'" /></td>'; } else { echo '<td title="'.$colcomments[$key].'"><input type="text" name="'.$key.'['.$info['id'].']" value="'.$val.'" /></td>'; } } echo '</tr>'; } echo ' </tbody> </table> </fieldset>'; } } } } } catch(PDOException $e){ $message['error'][] = $e->getMessage(); } This is the code for the Submit button, <div> <form action="assign_test1.php" method="post"> <p></p> <fieldset> <legend>Select Date</legend> <div>Select Date from and Date to</div> <p></p> <input type="date" name="from" id="from" value="<?=$date['from']; ?>" /> <input type="date" name="to" id="to" value="<?=$date['to']; ?>" /> <div><input type="submit" value="Submit" id="submit"/></div> </fieldset> </form> </div>
  4. cainam29

    PHPMail always sending when page is refreshed

    I think I know where my real issue is by doing several tests, so when I access the page "assign.php" it will load the page where I can select "date from" and "date to" which then would load the fetch data after hitting submit button but an email is being sent as well as soon as the page loaded with the fetch data. That should not be sending an email and what i would exactly want to happen is the mail would just be triggered if the assign column is already filled up or the db is updated by the values in "assign" column. The entire code is posted above. I dont now how to fix this as when i tried to use "if(isset($_POST['submit']))" but phpmail gets triggered right away even if the purpose of clicking submit is just to get the data. I dont know how to fix this.
  5. cainam29

    PHPMail always sending when page is refreshed

    Yeah, sorry my explanation is misleading...my real issue here is that as soon as you access the page and it loads for the first time, it is already sending an email. That part where you refresh the page, I am getting that Confirm box to either Resend or Cancel. So I have the option to cancel that but when the page loads for the first time and is sending email right away that is an issue.
  6. cainam29

    PHPMail always sending when page is refreshed

    I dont know which part is essential so I'm pasting the entire code <div id="mainContent"> <table width="1198" height="24" border="0"b align="center"> <tr> <td width="667" align="left">Hello <?php echo $_SESSION['myusername']; ?></td> </tr> </table> <?php foreach($message as $key => $val){ // show error, warning and info messages if(!empty($val)){ echo '<div style="margin:4px;background:'.$bgcol[$key].';border:2px solid grey;border-radius:8px;">'; foreach($val as $item){ echo '<div style="margin:8px;">'.$item.'</div>'; } echo '</div>'; } } ?> <div> <form action="assign.php" method="post"><?php if(is_array($result)){ echo ' <fieldset> <legend>Assign Ticket</legend> <div>Changes will affect updated rows only.</div> <p></p> <table width=auto cellpadding=1px cellspacing=0px border=1 align=center id=assign> <thead> <tr>'; // column comment from DB as column header foreach($result[0] as $key => $val){ echo '<th align=center>'.$colcomments[$key].'</th>'; } echo ' </tr> </thead> <tbody>'; foreach($result as $row => $info){ echo '<tr>'; foreach($info as $key => $val){ if($key=='id'){ echo '<td title="'.$colcomments[$key].'">'.$val.'.<input type="hidden" name="'.$key.'['.$info['id'].']" value="'.$val.'" id="rowid_'.$val.'" /></td>'; } else { echo '<td title="'.$colcomments[$key].'"><input type="text" name="'.$key.'['.$info['id'].']" value="'.$val.'" /></td>'; } } echo '</tr>'; } echo ' </tbody> </table> </fieldset>'; } function two_dim_array_to_html_table($arr, $colcomments){ $ret = "<table border='1' width='auto' cellpadding='1px' cellspacing='0px' align='center'>\n"; $ret .= "\t<tr>\n"; foreach($arr[0] as $key => $val){ $ret .= "\t\t<th>".$colcomments[$key]."</th>\n"; } $ret .= "\t</tr>\n"; foreach($arr as $row){ $ret .= "\t<tr>\n"; foreach($row as $column){ $ret .= "\t\t<td>".$column."</td>\n"; } $ret .= "\t</tr>\n"; } $ret .= "<table>\n"; return $ret; } if($result) { $Body = "<html>\n" . "<head>\n" . "</head>\n" . "<body>\n" . two_dim_array_to_html_table($result, $colcomments) . "</body>\n" . "</html>\n"; //Setting up Mail $mail = new PHPMailer(); if (EMAIL_USE_SMTP) { // Set mailer to use SMTP $mail->IsSMTP(); //useful for debugging, shows full SMTP errors //$mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only // Enable SMTP authentication $mail->SMTPAuth = EMAIL_SMTP_AUTH; // Enable encryption, usually SSL/TLS if (defined(EMAIL_SMTP_ENCRYPTION)) { $mail->SMTPSecure = EMAIL_SMTP_ENCRYPTION; } // Specify host server $mail->Host = EMAIL_SMTP_HOST; $mail->Username = EMAIL_SMTP_USERNAME; $mail->Password = EMAIL_SMTP_PASSWORD; $mail->Port = EMAIL_SMTP_PORT; } else { $mail->IsMail(); } $mail->From = EMAIL_FROM_ADDRESS; $mail->FromName = EMAIL_FROM_NAME; $mail->AddAddress('Tony.Bodreau@TELUS.COM'); $mail->Subject = 'Daily Tasks - "'.date('d-m-Y h:m:s').'"'; $mail->WordWrap = 100; $mail->IsHTML(true); $mail->Body = $Body; $mail->Send(); } ?> <fieldset> <legend>Select Date</legend> <div>Select Date from and Date to</div> <p></p> <input type="date" name="from" id="from" value="<?=$date['from']; ?>" /> <input type="date" name="to" id="to" value="<?=$date['to']; ?>" /> <div><input type="submit" value="Submit" /></div> </fieldset> </form> </div> </div>
  7. cainam29

    PHPMail always sending when page is refreshed

    I'm not using GET anywhere in my code.
  8. Hi I'm new to PHP and I have a code that send an email but how I can prevent it from always sending everytime my page refreshes. function two_dim_array_to_html_table($arr, $colcomments){ $ret = "<table border='1' width='auto' cellpadding='1px' cellspacing='0px' align='center'>\n"; $ret .= "\t<tr>\n"; foreach($arr[0] as $key => $val){ $ret .= "\t\t<th>".$colcomments[$key]."</th>\n"; } $ret .= "\t</tr>\n"; foreach($arr as $row){ $ret .= "\t<tr>\n"; foreach($row as $column){ $ret .= "\t\t<td>".$column."</td>\n"; } $ret .= "\t</tr>\n"; } $ret .= "<table>\n"; return $ret; } if($result) { $Body = "<html>\n" . "<head>\n" . "</head>\n" . "<body>\n" . two_dim_array_to_html_table($result, $colcomments) . "</body>\n" . "</html>\n"; //Setting up Mail $mail = new PHPMailer(); if (EMAIL_USE_SMTP) { // Set mailer to use SMTP $mail->IsSMTP(); //useful for debugging, shows full SMTP errors //$mail->SMTPDebug = 1; // debugging: 1 = errors and messages, 2 = messages only // Enable SMTP authentication $mail->SMTPAuth = EMAIL_SMTP_AUTH; // Enable encryption, usually SSL/TLS if (defined(EMAIL_SMTP_ENCRYPTION)) { $mail->SMTPSecure = EMAIL_SMTP_ENCRYPTION; } // Specify host server $mail->Host = EMAIL_SMTP_HOST; $mail->Username = EMAIL_SMTP_USERNAME; $mail->Password = EMAIL_SMTP_PASSWORD; $mail->Port = EMAIL_SMTP_PORT; } else { $mail->IsMail(); } $mail->From = EMAIL_FROM_ADDRESS; $mail->FromName = EMAIL_FROM_NAME; $mail->AddAddress('DENNIS.TOMBOC@TELUS.COM'); //$mail->AddAddress('dlTIP-ITOPMiddleware@telus.com'); $mail->Subject = 'Daily MW ITSM Tasks - "'.date('d-m-Y h:m:s').'"'; $mail->WordWrap = 100; $mail->IsHTML(true); $mail->Body = $Body; $mail->Send(); } I tried using this but it does not work, if(!$mail->Send()) { header("Location: http://www.example.com"); exit; }
  9. cainam29

    Multiple update of rows in a single column

    Okay...thanks Barand. I can get the page loaded now with the retrieved data but when I update values in implemented_by column by hitting submit. It just refresh the page and the table disappear as if it refreshed the entire page. Here is the updated code, <html> <head> </head> <body> <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "trackerdb"; // check data before use it and convert from string to expected type, use try, not like here: $date = filter_input(INPUT_POST, 'date'); $date1 = filter_input(INPUT_POST, 'date1'); // use valid data to select rows try { //1. connect to MySQL database $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); //2. set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //3. create query string (here is answer on your question) $sql = 'SELECT id, changeid, taskid, summary, type, reviewed_approved_by, scheduled_start_date, implemented_by FROM crqtracker WHERE scheduled_start_date BETWEEN :d1 AND :d2'; //4. prepare statement from query string $stmt = $conn->prepare($sql); //5. bind optional parameters //if ($status != 'All') $stmt->bindParam(':st', $status); //6. bind parameters $stmt->bindParam(':d1', $date); $stmt->bindParam(':d2', $date1); //7. execute statement $stmt->execute(); //8. returns an array containing all of the result set rows $result = $stmt->fetchAll(PDO::FETCH_ASSOC); //get count of rows $numrow = count($result); //print array - there is many solution to print array, //to debug you can do: //print_r($result); } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; if($numrow == 0) echo "No results found."; else echo "CRQ Count: $numrow</br>"; { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>--> <th align='center'><strong>Change ID</strong></th> <th align='center'><strong>Task ID</strong></th> <th align='center'><strong>Summary</strong></th> <th align='center'><strong>Type</strong></th> <th align='center'><strong>Reviewed/Approved By</strong></th> <th align='center'><strong>Scheduled Start Date</strong></th> <th align='center'><strong>Implementer</strong></th> </tr>"; foreach ($result as $row => $info) { echo "<form action='crqretrieve_status.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['changeid'] . "<input type=hidden name=changeid value=" . $info['changeid'] . " /></td>"; echo "<td align='center'>" . $info['taskid'] . "<input type=hidden name=taskid value=" . $info['taskid'] . " /></td>"; echo "<td align='center'>" . $info['summary'] . "<input type=hidden name=summary value=" . $info['summary'] . " /></td>"; echo "<td align='center'>" . $info['type'] . "<input type=hidden name=type value=" . $info['type'] . " /></td>"; echo "<td align='center'>" . $info['reviewed_approved_by'] . "<input type=hidden name=reviewed_approved_by value=" . $info['reviewed_approved_by'] . " /></td>"; echo "<td align='center'>" . $info['scheduled_start_date'] . "<input type=hidden name=scheduled_start_date value=" . $info['scheduled_start_date'] . " /></td>"; echo "<td align='center'><input name='implemented_by[" . $info['id'] . "]' value='' /> </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; ?> </body> </html> <?php /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $changeid = htmlentities($_POST['changeid'], ENT_QUOTES); $taskid = htmlentities($_POST['taskid'], ENT_QUOTES); $summary = htmlentities($_POST['summary'], ENT_QUOTES); $type = htmlentities($_POST['type'], ENT_QUOTES); $reviewed_approved_by = htmlentities($_POST['reviewed_approved_by'], ENT_QUOTES); $scheduled_start_date = htmlentities($_POST['scheduled_start_date'], ENT_QUOTES); $implemented_by = htmlentities($_POST['implemented_by'], ENT_QUOTES); // check that fields are not empty if ($changeid == '' || $taskid == '' || $summary == '' || $type == '' || $reviewed_approved_by == '' || $scheduled_start_date == '' || $implemented_by == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, $error, $id); } else { // if everything is fine, update the record in the database $placeholders = []; $data = []; foreach ($_POST['implemented_by'] as $id => $implemented_by) { if (trim($implemented_by) != '') { $placeholders[] = '(?, ?)'; array_push($data, $id, $implemented_by); } } if ($sql = "INSERT INTO crqtracker (id, implemented_by) VALUES " . join(',', $placeholders) . " ON DUPLICATE KEY UPDATE implemented_by = VALUES(implemented_by)" ) { $stmt = $db->prepare($sql); $stmt->execute($data); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: list.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT * FROM crqtracker WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by); $stmt->fetch(); // show the form renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: list.php"); } } } ?>
  10. cainam29

    Multiple update of rows in a single column

    Hi Barand, Here is my Edit Record part but for some reason I'm getting this error: That is when I tried to incorporate your PDO code to my edit record, <?php /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $changeid = htmlentities($_POST['changeid'], ENT_QUOTES); $taskid = htmlentities($_POST['taskid'], ENT_QUOTES); $summary = htmlentities($_POST['summary'], ENT_QUOTES); $type = htmlentities($_POST['type'], ENT_QUOTES); $reviewed_approved_by = htmlentities($_POST['reviewed_approved_by'], ENT_QUOTES); $scheduled_start_date = htmlentities($_POST['scheduled_start_date'], ENT_QUOTES); $implemented_by = htmlentities($_POST['implemented_by'], ENT_QUOTES); $placeholders = []; $data = []; // check that fields are not empty if ($changeid == '' || $taskid == '' || $summary == '' || $type == '' || $reviewed_approved_by == '' || $scheduled_start_date == '' || $implemented_by == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, $error, $id); } else { // if everything is fine, update the record in the database foreach ($_POST['implemented_by'] as $id => $implemented_by) { if (trim($implemented_by) != '') { $placeholders[] = '(?, ?)'; array_push($data, $id, $implemented_by); } } $sql = "INSERT INTO crqtracker (id, implemented_by) VALUES " . join(',', $placeholders) . " ON DUPLICATE KEY UPDATE implemented_by = VALUES(implemented_by)"; $stmt = $db->prepare($sql); $stmt->execute($data); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: list.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT * FROM crqtracker WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by); $stmt->fetch(); // show the form renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: list.php"); } } ?>
  11. cainam29

    Multiple update of rows in a single column

    The reason for having the other columns fetch as well is so that we have a view of the details of the ticket before we update and eventually send only the updated value to db. I'll be honest that i do not know how to implement this with my code above. INSERT INTO sample_table (ID, col3) VALUES (1,2), (5,1), (6,3) ON DUPLICATE KEY UPDATE col3 = VAUES(col3);
  12. cainam29

    Multiple update of rows in a single column

    Hi Barand, yes the ID is the primary key and i want to know if the values that you provided in column3 is it arbitrary, can I use that code directly? I actually managed to change my code but nothing happens when I click on submit. Sorry got really stuck here, dont know how to proceed, this code is used to call my main code $(document).ready(function(){ $("#RetrieveList").on('click',function() { var status = $('#status').val(); var date = $('#Date').val(); var date1 = $('#Date1').val(); $.post('retrieve_status',{status:status, date:date, date1:date1}, function(data){ $("#results").html(data); }); return false; }); Start Date: <input type="text" name="Date" id="Date" size="8"/> End Date: <input type="text" name="Date1" id="Date1" size="8"/> <input name="action" type="submit" id="RetrieveList" value="Retrieve List" /> <input name="Clear" type="reset" id="Clear" value="Clear" onclick="window.location.reload()" /> <input type="submit" name="submit" value="Submit" /> Then this is the main code that I am using to process the data, <html> <head> </head> <body> <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "trackerdb"; // check data before use it and convert from string to expected type, use try, not like here: $date = $_POST['date']; $date1 = $_POST['date1']; // use valid data to select rows try { //1. connect to MySQL database $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); //2. set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //3. create query string (here is answer on your question) $sql = 'SELECT id, changeid, taskid, summary, type, reviewed_approved_by, scheduled_start_date, implemented_by FROM crqtracker WHERE scheduled_start_date BETWEEN :d1 AND :d2'; //4. prepare statement from query string $stmt = $conn->prepare($sql); //5. bind optional parameters //if ($status != 'All') $stmt->bindParam(':st', $status); //6. bind parameters $stmt->bindParam(':d1', $date); $stmt->bindParam(':d2', $date1); //7. execute statement $stmt->execute(); //8. returns an array containing all of the result set rows $result = $stmt->fetchAll(PDO::FETCH_ASSOC); //get count of rows $numrow = count($result); //print array - there is many solution to print array, //to debug you can do: //print_r($result); } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; if($numrow == 0) echo "No results found."; else echo "CRQ Count: $numrow</br>"; { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>--> <th align='center'><strong>Change ID</strong></th> <th align='center'><strong>Task ID</strong></th> <th align='center'><strong>Summary</strong></th> <th align='center'><strong>Type</strong></th> <th align='center'><strong>Reviewed/Approved By</strong></th> <th align='center'><strong>Scheduled Start Date</strong></th> <th align='center'><strong>Implementer</strong></th> </tr>"; foreach ($result as $row => $info) { echo "<form action='crqretrieve_status.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['changeid'] . "<input type=hidden name=changeid value=" . $info['changeid'] . " </td>"; echo "<td align='center'>" . $info['taskid'] . "<input type=hidden name=taskid value=" . $info['taskid'] . " </td>"; echo "<td align='center'>" . $info['summary'] . "<input type=hidden name=summary value=" . $info['summary'] . " </td>"; echo "<td align='center'>" . $info['type'] . "<input type=hidden name=type value=" . $info['type'] . " </td>"; echo "<td align='center'>" . $info['reviewed_approved_by'] . "<input type=hidden name=reviewed_approved_by value=" . $info['reviewed_approved_by'] . " </td>"; echo "<td align='center'>" . $info['scheduled_start_date'] . "<input type=hidden name=scheduled_start_date value=" . $info['scheduled_start_date'] . " </td>"; echo "<td align='center'><input name='implemented_by[" . $info['id'] . "]' value='' /> </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; ?> </body> </html> <?php /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $changeid = htmlentities($_POST['changeid'], ENT_QUOTES); $taskid = htmlentities($_POST['taskid'], ENT_QUOTES); $summary = htmlentities($_POST['summary'], ENT_QUOTES); $type = htmlentities($_POST['type'], ENT_QUOTES); $reviewed_approved_by = htmlentities($_POST['reviewed_approved_by'], ENT_QUOTES); $scheduled_start_date = htmlentities($_POST['scheduled_start_date'], ENT_QUOTES); $implemented_by = htmlentities($_POST['implemented_by'], ENT_QUOTES); // check that fields are not empty if ($changeid == '' || $taskid == '' || $summary == '' || $type == '' || $reviewed_approved_by == '' || $scheduled_start_date == '' || $implemented_by == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE crqtracker SET implemented_by = ? WHERE id=?")) { $stmt->bind_param("si", $implemented_by, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: list.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT * FROM crqtracker WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by); $stmt->fetch(); // show the form renderForm($changeid, $taskid, $summary, $type, $reviewed_approved_by, $scheduled_start_date, $implemented_by, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: list.php"); } } } ?>
  13. I am trying to retrieved data from database and update a single column. Here is a sample of how my page would look like, first part is when we initially upload it, this is during the approval process column1 | column2 | value1 | value1 | value2 | value2 | Now the second part is when we try to retrieved the data from the db with the added column3 because we want to assign it to someone, thus adding their name before we re-upload it to the db column1 | column2 | column3 value1 | value1 | ------- value2 | value2 | ------- <=== this column3 rows does not have value in database yet as it is not part of the ... page I used when we initially uploaded the data, so it will be blank when I retrieved the data I am trying to retrieved multiple rows here with column3 being editable so we can add different values to it. Then I want to re-upload those different values in column3 alone. Here is how I'm fetching the data, <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "sample_db"; // check data before use it and convert from string to expected type, use try, not like here: $date = $_POST['date']; $date1 = $_POST['date1']; // use valid data to select rows try { //1. connect to MySQL database $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); //2. set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //3. create query string (here is answer on your question) $sql = 'SELECT column1, column2, column3 FROM sample_table WHERE scheduled_start_date BETWEEN :d1 AND :d2'; //4. prepare statement from query string $stmt = $conn->prepare($sql); //5. bind optional parameters //if ($status != 'All') $stmt->bindParam(':st', $status); //6. bind parameters $stmt->bindParam(':d1', $date); $stmt->bindParam(':d2', $date1); //7. execute statement $stmt->execute(); //8. returns an array containing all of the result set rows $result = $stmt->fetchAll(PDO::FETCH_ASSOC); //get count of rows $numrow = count($result); //print array - there is many solution to print array, //to debug you can do: //print_r($result); } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; if($numrow == 0) echo "No results found."; else echo "Count: $numrow</br>"; { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <!--<th align='center'><input id=checkall name=checkall id=checkall type=checkbox value='' /></th>--> <th align='center'><strong>Column1</strong></th> <th align='center'><strong>Column2</strong></th> <th align='center'><strong>Column3</strong></th> </tr>"; foreach ($result as $row => $info) { echo "<form action='crqretrieve_status.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>"; echo "<td align='center'>" . "<input name=column3 value='' </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; ?> Now, I have a code here which I am using to update status but it is only applicable for single row update which is based on the "ID". How can i use this for multiple row update? I've read about using a "case" but i dont know how to implement it using the code below. Here is the code to retrieved data from db and update row but only for single ID, i cant make it to work when retrieving multiple ID's and updating multiple ID's after clicking a Submit button. <?php /* Allows the user to both create new records and edit existing records */ // connect to the database include('include/connect-db.php'); // creates the new/edit record form // since this form is used multiple times in this file, I have made it a function that is easily reusable function renderForm($column1 = '', $column2 = '', $column3 = '', $column4 = '', $error = '', $id = '') { ?> <html xmlns="http://www.w3.org/1999/xhtml"> <body class="oneColFixCtrHdr"> <div id="container"> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <div id="mainContent"> <form action="" method="post"> <table width="450" border="0" align="center"> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID: <?php echo $id; ?></p> <?php } ?> <tr> <td>A1:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column1" value="<?php echo $column1;?>"/></td> </tr> <tr> <td>A2:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column2" value="<?php echo $column2; ?>" readonly="readonly"/></td> </tr> <tr colspan="3"> <td>A3:<span style="color: #ff0000;"><strong>*</strong></span></td> <td colspan="2"><input type="text" name="column3" id="column3" value="<?php echo $column3; ?>"/></td> </tr> <tr> <td><input type="submit" name="submit" value="Submit" /></td> </tr> </table> </form> <!-- end #mainContent --></div> <!-- end #container --></div> </body> </html> <?php } /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $column1 = $_POST['column1']; $column2 = htmlentities($_POST['column2'], ENT_QUOTES); $column3 = htmlentities($_POST['column3'], ENT_QUOTES); // check that fields are not empty if ($column1 == '' || $column2 == '' || $column3 == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($column1, $column2, $column3, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE sample_table SET column1 = ?, column2 = ?, column3 = ?, WHERE id=?")) { $stmt->bind_param("sssi", $column1, $column2, $column3, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: list.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the record from the database if($stmt = $mysqli->prepare("SELECT column1, column2, column3 FROM sample_table WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($column1, $column2, $column3); $stmt->fetch(); // show the form renderForm($column1, $column2, $column3, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: list.php"); } } } // close the mysqli connection $mysqli->close(); ?>
  14. Thanks Barand. That actually did the trick, I never had thought that there was an All vs ALL issue in the code.
  15. Thanks Barand, So here would be the updated code to fetch data, <?php$servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; // check data before use it and convert from string to expected type, use try, not like here: $status = $_POST['status']; $date = $_POST['date']; $date1 = $_POST['date1']; // use valid data to select rows try { //1. connect to MySQL database $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); //2. set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //3. create query string $sql = 'SELECT column1, column2, status FROM tracker WHERE 1=1'; if ($status != 'ALL') $sql .= ' AND status = :st'; $sql .= ' AND scheduled_start_date BETWEEN :d1 AND :d2'; //4. prepare statement from query string $stmt = $conn->prepare($sql); //5. bind optional parameters if ($status != 'ALL') $stmt->bindParam(':st', $status); //6. bind parameters $stmt->bindParam(':d1', $date); $stmt->bindParam(':d2', $date1); //7. execute statement $stmt->execute(); //8. returns an array containing all of the result set rows $result = $stmt->fetchAll(PDO::FETCH_ASSOC); //get count of rows $numrow = count($result); //print array - there is many solution to print array, //to debug you can do: //print_r($result); } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; if($numrow == 0) echo "No results found."; else echo "Count: $numrow</br>"; { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <th align='center'><strong>Column1</strong></th> <th align='center'><strong>Column2</strong></th> <th align='center'><strong>Status</strong></th> </tr>"; foreach ($result as $row => $info) { echo "<form action='crqretrieve_status_test1.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>"; echo "<td align='center'>" . $info['status'] . "<input type=hidden name=status value=" . $info['status'] . " </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; ?> And Select Options 1-4 works when selected individually but not when selecting ALL (which means selecting all options at the same time), <select name="status" id="status" style="width: 224px;"> <option value="" selected="selected">Please select...</option> <option value="All">All</option> <option value="Option1">Option1</option> <option value="Option2">Option2</option> <option value="Option3">Option3</option> <option value="Option4">Option4</option> </select> I tried to print the result and I am just getting Status select options 1-4 must be showing in the table If I filter Status to ALL but I am not getting anything. I wonder where the issue is now.
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.