Hello I have a form and what I want to do is to add new data in my database and also update a selected data. My problem is that when I am adding the first time a data which has id=1 then when I am going to add a new data it doesnt add the data but it updates the already stored data and I cant find why it does this. My code is the below...
<?php
error_reporting(E_ALL ^ E_NOTICE);
$db = new PDO('mysql:host=localhost;dbname=cvtool;charset=utf8', 'root', 'smogi');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
// set defaults to the post vars
// if none, they will just be null and filled in later by one of our catches
$idw = isset($_POST['idWork']) ? $_POST['idWork']: null;
$job_title = isset($_POST['job_title']) ? $_POST['job_title'] : null;
$company =isset( $_POST['company']) ? $_POST['company'] : null;
$website = isset($_POST['website']) ? $_POST['website'] : null;
$startDateWork = isset($_POST['start_date']) ? $_POST['start_date'] : null;
$endDateWork =isset( $_POST['end_date']) ? $_POST['end_date'] : null;
$startYearWork =isset( $_POST['start_year']) ? $_POST['start_year'] : null;
$endYearWork =isset( $_POST['end_year']) ? $_POST['end_year'] : null;
$work_history =isset( $_POST['work_history']) ? $_POST['work_history'] : null;
function getWorkIdsAsLinks($db, $table, $user){
// PDO will sanatize most vars automatically
// however Table and Column names cannot be replaced by parameters in PDO.
// In this case we will simply want to filter and sanitize the data manually.
// By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.
// http://ift.tt/1qeFlr8
switch($table){
case 'work':
$tbl = 'work';
break;
}
$sql = 'SELECT id FROM '.$tbl.' WHERE username=? ORDER BY id'; // "?"s here will get replaced with the array elements belowlinkslinks
$stmt = $db->prepare($sql);
$stmt->execute(array($user)); // these array elements will replace the above "?"s in this same order
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// check for errors
if($stmt->errorCode() == 0) {
// no errors
$links=array();
foreach($rows as $row) {
$idw = $row['id'];
array_push($links,'<a style="color:#F87F25; font: bold 16px Tahoma;" href="#" data-row-idw="'.$idw.'">' .$idw. '</a>');
}
return join(" ",$links);
} else {
// had errors
$errors = $stmt->errorInfo();
return $errors[2];
}
}
// a function that deletes records
// $table is the table to delete from
// $user is the current username
// $rowId is the row id of the record to be deleted
// if $rowId is passed as the string "all",
// all matching records will be deleted
function deleteFromWorkWhere($db,$table,$user,$rowId){
switch($table){
case 'work':
$tbl = 'work';
break;
}
if($rowId=='all'){ // delete all records
$sql = 'DELETE FROM '.$tbl.' WHERE username=?'; // "?"s here will get replaced with the array elements below
$stmt = $db->prepare($sql);
$stmt->execute(array($user)); // these array elements will replace the above "?"s in this same order
// check for errors
if($stmt->errorCode() == 0) {
// no errors, show alert and refresh page
return '<script type="text/javascript">alert("All work history was successfully cleared!"); window.location="CV.php"; </script>';
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error deleting work history!: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
elseif($rowId){ // delete specified row
$sql = 'DELETE FROM '.$tbl.' WHERE username = ? AND id = ?'; // "?"s here will get replaced with the array elements below
$stmt = $db->prepare($sql);
$stmt->execute(array($user,$rowId)); // these array elements will replace the above "?"s in this same order
$affected_rows = $stmt->rowCount(); // get the number of rows affected by this change
return $affected_rows.' row deleted.';
// check for errors
if($stmt->errorCode() == 0) {
// no errors, show alert and refresh page
return '<script type="text/javascript">alert("Selected work history was successfully cleared!"); window.location="CV.php"; </script>';
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error deleting work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
else{ /// return error
}
}
if(isset($_POST['clear_work'])){
deleteFromWorkWhere($db,'work',$_SESSION['username'],'all');
}
// the below inserts a record
// if the user pressed submitWork to get here
if(isset($_POST['submit_workHistory'])){
// check if an id is present,
// if so this will be an update
if($_POST['idWork']){
// these array elements will replace the above "?"s in this same order
$stmt = $db->prepare("UPDATE work SET job_title=?, company=?, website=?, start_date=?, end_date=?, start_year=?, end_year=?, work_history=? WHERE username=? AND id=?");
$stmt->execute(array( $_POST['job_title'],
$_POST['company'],
$_POST['website'],
$_POST['start_date'],
$_POST['end_date'],
$_POST['start_year'],
$_POST['end_year'],
$_POST['work_history'],
$_SESSION['username'],
$_POST['idWork']));
// check for errors
if($stmt->errorCode() == 0) {
// updated record with no errors, load next record for this user
//now get the next record
$stmt = $db->prepare("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history,id FROM work WHERE username=? AND id>? ORDER BY id LIMIT 1");
$stmt->execute(array($_SESSION['username'],$_GET['idWork']));
$row_count = $stmt->rowCount();
// check for errors
if($stmt->errorCode() == 0) {
// no errors
// if a record is found, set the vars
if($row_count>0){
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$idw = $row['id'];
$job_title = $row['job_title'];
$company = $row['company'];
$website = $row['website'];
$startDateWork = $row['start_date'];
$endDateWork = $row['end_date'];
$startYearWork = $row['start_year'];
$endYearWork = $row['end_year'];
$work_history = $row['work_history'];
}
}
else{ // if there is no next record, display the first
// updated record with no errors, load next record for this user
$stmt = $db->prepare("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history,id FROM work WHERE username=? ORDER BY id LIMIT 1");
$stmt->execute(array($_SESSION['username'],$_GET['idWork']));
$row_count = $stmt->rowCount();
// check for errors
if($stmt->errorCode() == 0) {
// no errors
// if a record is found, set the vars
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$idw = $row['id'];
$job_title = $row['job_title'];
$company = $row['company'];
$website = $row['website'];
$startDateWork = $row['start_date'];
$endDateWork = $row['end_date'];
$startYearWork = $row['start_year'];
$endYearWork = $row['end_year'];
$work_history = $row['work_history'];
}
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error displaying the first work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error loading next record for work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error updatiing work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
else{ // if no id is present this will be an insert
// "?"s here will get replaced with the array elements below
$sql = 'INSERT INTO work ( job_title,company,website,start_date,end_date,start_year,end_year,work_history,username ) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ? )';
$stmt = $db->prepare($sql);
$stmt->execute(array( $_POST['job_title'],
$_POST['company'],
$_POST['website'],
$_POST['start_date'],
$_POST['end_date'],
$_POST['start_year'],
$_POST['end_year'],
$_POST['work_history'],
$_SESSION['username']));
// check for errors
if($stmt->errorCode() == 0) {
$idw = $db->lastInsertId(); // get the id form the inserted record, not really needed here since we are about to refresh the page but I put it here to show you how
// no errors, show alert and refresh page
echo '<script type="text/javascript">alert("Work history was successfully added!"); window.location="CV.php";</script>';
} else {
// had errors
$errors = $stmt->errorInfo();
echo '<script type="text/javascript">alert("Error adding work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
}
elseif(isset($_GET['idWork'])){
// user clicked on one of idWork links to get here
// set the id the value of the GET parameter for key "idWork"
$idw = $_GET['idWork'];
// first load of the page in this session, initialize by getting the first id for this user
$stmt = $db->prepare("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history,id FROM work WHERE username=? AND id=?");
$stmt->execute(array($_SESSION['username'],$_GET['idWork']));
// check for errors
if($stmt->errorCode() == 0) {
// no errors, show alert and refresh page
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$idw = $row['id'];
$job_title = $row['job_title'];
$company = $row['company'];
$website = $row['website'];
$startDateWork = $row['start_date'];
$endDateWork = $row['end_date'];
$startYearWork = $row['start_year'];
$endYearWork = $row['end_year'];
$work_history = $row['work_history'];
}
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error getting work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
//user clicked the 'next' button to get here, display the next record for this user or, if this was the last record display the first one
elseif(isset($_POST['view_next_work'])){
$stmt = $db->prepare("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history,id FROM work WHERE username=? AND id>? ORDER BY id LIMIT 1");
$stmt->execute(array($_SESSION['username'],$_POST['idWork']));
$row_count = $stmt->rowCount();
// check for errors
if($stmt->errorCode() == 0) {
// no errors
// if a record is found, set the vars
if($row_count>0){
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$idw = $row['id'];
$job_title = $row['job_title'];
$company = $row['company'];
$website = $row['website'];
$startDateWork = $row['start_date'];
$endDateWork = $row['end_date'];
$startYearWork = $row['start_year'];
$endYearWork = $row['end_year'];
$work_history = $row['work_history'];
}
}
else{ // if there is no next record, display the first
// updated record with no errors, load next record for this user
$stmt = $db->prepare("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history,id FROM work WHERE username=? ORDER BY id LIMIT 1");
$stmt->execute(array($_SESSION['username']));
$row_count = $stmt->rowCount();
// check for errors
if($stmt->errorCode() == 0) {
// no errors
// if a record is found, set the vars
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$idw = $row['id'];
$job_title = $row['job_title'];
$company = $row['company'];
$website = $row['website'];
$startDateWork = $row['start_date'];
$endDateWork = $row['end_date'];
$startYearWork = $row['start_year'];
$endYearWork = $row['end_year'];
$work_history = $row['work_history'];
}
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error displaying the first work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error loading next record for work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
else{
// first load of the page in this session, initialize by getting the first id for this user , if there is one
$stmt = $db->prepare("SELECT job_title,company,website,start_date,end_date,start_year,end_year,work_history,id FROM work WHERE username=? ORDER BY id LIMIT 1");
$stmt->execute(array($_SESSION['username']));
// check for errors
if($stmt->errorCode() == 0) {
// no errors, show alert and refresh page
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$idw = $row['id'];
$job_title = $row['job_title'];
$company = $row['company'];
$website = $row['website'];
$startDateWork = $row['start_date'];
$endDateWork = $row['end_date'];
$startYearWork = $row['start_year'];
$endYearWork = $row['end_year'];
$work_history = $row['work_history'];
}
} else {
// had errors
$errors = $stmt->errorInfo();
return '<script type="text/javascript">alert("Error getting first work history: '.$errors[2].'"); window.location="CV.php"; </script>';
}
}
?>
Aucun commentaire:
Enregistrer un commentaire