Why does my pg_query update method skips row?
I'm just a beginner with php, even more so on PostgeSQL. I can say that my knowledge over php, json and sql is very limited.
I have created an update method to refresh my returned data whenever I create or delete a record from database. But sometimes, when I create a new record, the new record doesn't return from php until I use the refresh function twice. Sometimes, same happens too when I delete a record, I receive results one click late. This happens randomly, sometimes on first click, sometimes on 4th click.
Example: Let's say I have an existing record on table and added a new record. On by chance, it returned [["6","1","Jason Smith","&id","89"] when it should have returned [["6","1","Jason Smith","&id","89"][["6","1","King Sczhult","&id","90"], but when I check database or refresh the page by browser, the record is there.
//The function that calls php
function refresh() {
$(".scell").html("");
$("#holidayCell").html("");
var updateTable = {
semSchedule: $("#semesterselect").val(),
operation: "updateTable"
}
$.post( "scheduleengine.php", updateTable).done(function( response ) {
if (response.val != 0){
//This is where I decode returned table array
}else {
};
});
};
//This is the php switch-case.
switch($_POST["operation"]){
case "updateTable":
echo updateTableFunc(post("semSchedule"));
break;
//Update Function
function updateTableFunc($semID = null){
$result = "";
$scqrysql = "SELECT id, tutor, hour, day FROM schedule WHERE semester='$semID'";
$scqry = pg_query($scqrysql) or die(pg_result_error());
while ($row = pg_fetch_array($scqry)) {
$scTutorID = $row['tutor'];
$tqry = pg_query("SELECT id, tname, tsurname FROM tutors WHERE id='$scTutorID'") or die(pg_result_error());
while($tutorrow=pg_fetch_array($tqry)){
$scTutor = $tutorrow['tname'] . " " .$tutorrow['tsurname'];
};
$scHourRow = $row['hour'];
$scDay = $row['day'];
$scID = $row['id'];
$scHour = substr($scHourRow, 2, 1);
$scDayNameArray = array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday");
$scDayNumberArray = array("2", "3", "4", "5", "6");
$scDayNameReplace = str_replace($scDayNameArray, $scDayNumberArray, $scDay);
$resultArrays = array($scDayNameReplace, $scHour, $scTutor, "&id", $scID);
$result[] = $resultArrays;
};
if(is_null($result)){
$result = "";
echo json_encode($result);
}else{
$json = json_encode($result);
echo $json;
};
};
That's the problem right there. Looks like you're using jquery to call both things, and you're doing it asynchronously-- meaning both kick off nearly instantly and either one could finish first (race condition). When "A" wins the race, you're golden.. when B wins the race you have to refresh again. What you need is to call "A" (update query) then have it call "B" when it's finished.