FRIHOST • FORUMS • SEARCH • FAQ • TOS • BLOGS • COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


AJAX + PHP, UPDATE mysql table nto working.

   Frihost Forum Index -> Scripting -> Php and MySQL
 




ThomasDesigns
First off, I want to list my JavaScript, and my PHP so that you can have that as a reference to the explanation.

JS:
Code:
function GetXmlHttpObject()
{
  var xmlHttp=null;
  try
   {
       // Firefox, Opera 8.0+, Safari
       xmlHttp=new XMLHttpRequest();
    }
  catch (e)
    {
    // Internet Explorer
    try
         {
         xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
         }
    catch (e)
         {
         xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
         }
    }
  return xmlHttp;
}

function ajaxFunction(holderid,barid,postid)
   {
   document.getElementById(holderid).removeAttribute("onmouseout");
   document.getElementById(holderid).removeAttribute("onmousemove");
   document.getElementById(holderid).removeAttribute("onmousedown");   
   var xmlHttp;
   xmlHttp=GetXmlHttpObject()
   if (xmlHttp==null)
     {
        alert ("Your browser does not support AJAX, you will not be able to rate news articles.");
        return;
     }
   var rating = document.getElementById(barid).innerHTML;
   xmlHttp.onreadystatechange = function() {
   if (xmlHttp.readyState==4)
      {
         var _RT = xmlHttp.responseText.split("|");
         var _R = new Number(_RT[0]);
         var barid = _RT[1];
         var holderid = _RT[2];
         var _W = _R*10;      
         document.getElementById(barid).style.width = _W + "%";
         document.getElementById(barid).innerHTML = "Rated -- " + _R;
      }
   }
   var url = "scripts/ratings.php?id=";
   url += postid ;
   url += "&rating=";
   url += rating;
   url += "&barid=";
   url += barid;
   url += "&holderid=";
   url += holderid;
   url += "&sid=" ;
   url += Math.random();
   xmlHttp.open("GET",url,true);
   xmlHttp.send(null);
   }

PHP:
Code:
<?php
$id = $_GET['postid'];
$Urating = $_GET['rating'];
$barid = $_GET['barid'];
$holderid = $_GET['holderid'];

$con = mysql_connect("localhost","FOO","BAR");
   if (!$con)
     {
     die('Could not connect: ' . mysql_error());
     }
   mysql_select_db("FooBar", $con);
   $result = mysql_query("SELECT * FROM news WHERE id = '$id'");
   $Crating = mysql_fetch_row($result);
   $Current = $Crating[6];
   if($Current == null){
      $newrating = $Urating;
   }
   else{
      $newrating = (($Urating + $Current)/2);
   }
   $query = "UPDATE news SET rating = '$newrating' WHERE id = '$id'";
   mysql_query($query);      
   $Response[0]=$newrating;
   $Response[1]=$barid;
   $Response[2]=$holderid;
   echo $Response[0] . "|";   
   echo $Response[1] . "|";   
   echo $Response[2] . "|";
mysql_close($con);
?>


Ok, now here are the problems I am having.

The PHP script's output looks something like this: "5.7|bar75|holder75". If I point my browser to the PHP script and execute it manually, it works. It returns the correct variables, and updates the database as it is supposed to. However, when I call the ajax function from the page that uses it, a couple unexpected things happen.

One, the value returned ($Response[0]) first is not what it is supposed to be. Instead the value returned is the unaltered $Urating value. This would make sense if the value in the database were NULL, however I know for a fact that it is not, and the returned value should be an average of the $Urating and $Current.

Two, the database is not updated. The AJAX function clearly is able to access the output of this file, but the functions in the PHP are not being executed as I would like them to be.

It seems as if the two SQL queries are not being run when accessed by AJAX.

I have been scouring the internet for explanations more descriptive than w3schools's, and I haven't found much of anything. Is it even possible to run SQL UPDATE queries through AJAX and PHP?

Many questions, need some answers. Halp!
Fire Boar
It is entirely possible to do UPDATEs with AJAX. There is something wrong with your javascript - in other words, the PHP file isn't executing. However, being no great shake on javascript I can't really give you much more information than that.
Aredon
Change:
Code:

var url = "scripts/ratings.php?id=";

to:
Code:

var url = "scripts/ratings.php?postid=";


Also don't directly insert a $_REQUEST parameter into your database w/o first escaping it lest your database yield vulnerable to MySQL Injections.

Instead of:
Code:

$result = mysql_query("SELECT * FROM news WHERE id = '$id'");

use:
Code:

$result = mysql_query("SELECT * FROM news WHERE id = '".mysql_real_escape_string($id)."'");


And instead of:
Code:

$query = "UPDATE news SET rating = '$newrating' WHERE id = '$id'";

use:
Code:

$query = "UPDATE news SET rating = '".mysql_real_escape_string($newrating)."' WHERE id = '".mysql_real_escape_string($id)."'";


You might also want to look into PHP's (int) cast for dealing with integers rather than using mysql_real_escape_string on it.
e.g.
Code:

$whatever=(int)$_GET{whatever};
ThomasDesigns
Thanks for your help, I do appreciate it.

I didn't think using the mysql_real_escape_string() method was important in this case since only the AJAX can reach the PHP page. With .htaccess I can easily limit access to the PHP file to server side only (or so I've read, was planning on actually implementing it after it's complete). I suppose it's good practice to make your code as secure as possible, but I'm just curious if .htaccess alone will make it safe enough?

I could see someone trying to use their own AJAX engines to get to my PHP script, but again, if .htaccess only allows access from the web server....or would an AJAX call from anywhere be considered server side?

Intriguing. Please post your thoughts if you have any Smile

Oh and thanks again.
Agent ME
Any AJAX calls are made from the user's computer, not from inside the server. So .htaccess wouldn't help you at all.

You'll want to use mysql_real_escape_string() command.
manav
mysql_real_escape_string() is important to protect ur scripts from sql injection or xss attacks...
Related topics
E-Cards with PHP & MySQL
HTTP AUTH with PHP and mySQL
Make search engine With PHP and mySQL, for your site
php and mysql connection in flash based website
Working with Excel, PHP & MySQL. Any Ideas		[man]Azlo tu mismo ShoutBox en PHP y mysql
Developing a Login System with PHP and MySQL
Flash, PHP and MySQL
Good PHP Books
PHP and MySQL FAQ - Read Before posting!		"Drop Down List Box" Using PHP and MySql
which host do u like put php&mysql?
php and mysql form
What to learn first?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.