2 replies to this topic

[bernie west]

Hi. Is it possible to use Ajax with php to delete/update some database entries? I have a form with some database records formed into a table, and I want to be able to select one or more records and then click a button and have the delete/update performed on the server at that point. I've written up all the javascript and php, but can't seem to get it to work (I pretty much cut and paste the php-ajax database example from the w3schools website). The javascript isn't throwing any errors, so i'm not sure what's wrong. I'll post my code in a following post in a sec...
Cheers.

[WingedPanther]

Yes, it's possible. Once PHP receives the AJAX call, it can do anything you can do with PHP, including communicate with databases.

[bernie west]

Here's my code -
Javascript:

function ajax() {

	var select = document.getElementById("selectAction");

	var selectedValue = select.options[select.selectedIndex].value;

	var arrID = new Array();		

	

	arrID = getCheckedBoxIDs();

	if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari

	  xmlhttp=new XMLHttpRequest();

	}

	else {// code for IE6, IE5

	  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");

	}

	xmlhttp.onreadystatechange=function() {

	  if (xmlhttp.readyState==4 && xmlhttp.status==200)	{

		alert(xmlhttp.responseText);

		}

	}

	alert("actionMarked.php?q=" + arrID + "&action=" + selectedValue);

	xmlhttp.open("GET","actionMarked.php?q=" + arrID + "&action=" + selectedValue,true);

	xmlhttp.send();

}


function getCheckedBoxIDs() {

	var arrID = new Array();

	var tblTable = document.getElementById("tblResults");

	var noCheckboxes = tblTable.rows.length; 

	

	for (i=1; i<noCheckboxes; i++) { // the first row holds the column headings

		var checkbox = tblTable.rows[i].cells[0].childNodes[0];

		if (checkbox.checked)

			arrID.push(tblTable.rows[i].cells[7].innerHTML); // hidden cell in each row containing the record ID associated with each row.

	}

return arrID;

}

php file - "actionMarked.php":

<?php

	$q = $_GET["q"];

	$action = $_GET['action'];

	

	switch ($action) {

		case 'reviewed':

			reviewRecords($q);

			break;

		case 'accepted':

			acceptRecords($q);

			break;

		case 'delete':

			deleteRecords($q);

			break;

	}


	function reviewRecords($arr) {

		$count = 0;

		$sql = "UPDATE tblagendapoints SET Reviewed = 1 WHERE ID = $arr[0]";

		foreach($arr as $item) {

			if ($count > 0 )

				$sql .= " and ID = $arr[$count]";

		}

		

		$conn = dbConnect();

		mysqli_query($sql);

		mysqli_close($conn);		

	}

	

	function acceptRecords($arr) {

		$count = 0;

		$sql = "UPDATE tblagendapoints SET Accepted = 1 WHERE ID = $arr[0]";

		foreach($arr as $item) {

			if ($count > 0 )

				$sql .= " and ID = $arr[$count]";

		}

		

		$conn = dbConnect();

		mysqli_query($sql);

		mysqli_close($conn);	

	}

	

	function deleteRecords($arr) {

		$count = 0;

		$sql = "DELETE FROM tblagendapoints WHERE ID = $arr[0]";

		foreach($arr as $item) {

			if ($count > 0 )

				$sql .= " and ID = $arr[$count]";

		}

		

		$conn = dbConnect();

		mysqli_query($sql);

		mysqli_close($conn);	

	}

	

	function dbConnect() {

		return new mysqli('localhost','username','password','databasename') or die('Cannot open database');

	}

		

?>

Just ignore the use of the $count variable in there, as I haven't implemented that yet.

When I call the ajax() function from my main php page (code not shown), the javascript code functions without error and correctly obtains the ID's of the checked boxes. It correctly (as far as I can tell) creates the url with query strings ok, but this alert: alert(xmlhttp.responseText); never comes up, and the php file doesn't function to delete/update the database. So I figure there is either something wrong in my php file, or perhaps my query string is wrong somehow.