update mysql database doesn't work

clausowitz · July 30, 2011

Hi All,

I have this php code to update my database but it doesn't work.

<?php
// Start_session, check if user is logged in or not, and connect to the database all in one included file
include_once("../scripts/checkuserlog.php");
$q_id=$_GET['id'];
$q_rating=$_GET['rating'];
$sql = mysql_query("UPDATE restaurants SET votes= votes + 1, rating='".$q_ratin."' WHERE id='".$q_id."' LIMIT 1");

?>

The page gets fed by an AJAX call. But I cannot check if any values get to the php page.

<script language="javascript" type="text/javascript">
function vote(x,votes,rating,id) {
			current_rate =x+rating;
		mem = votes+1;
		sum = current_rate/mem;
		result = sum.toFixed(1)
		rating = result * 25;
		document.getElementById('current-rating'+id).style.width = rating+'px';
		document.getElementById('current-rating'+id).innerHTML= 'Thanks for your vote!<br />'
	    var dataString = 'id='+ x + '&rating=' + result;
		$.ajax({
		   type: "POST",
		   url: "score.php?id='+x+&rating='+result+" , // page where insertion to database should have made
		   success: function(msg){
			 alert("Counter updated" );
		   }
		 });
}

</script>

MArco

PFMaBiSmAd · July 30, 2011

You can and should test your php code by manually making a URL in your browser's address bar and submitting it -

http://your_domain.com/score.php?id=123&rating=456

Only after you have completely debugged your php code should you try to submit data to it using Ajax.

You can use a tool like firephp to debug ajax based applications - http://www.firephp.org/

PFMaBiSmAd · July 30, 2011

Edit to the above: I just examined your form code closer and you are using the POST method but you are also building the URL with the data on the end of it, so I am not sure exactly what you are receiving.

clausowitz · July 30, 2011

You are right there.

I was actually in the middle of trying two things.

I was afraid the data: bit wouldn't work so I added the variables after the url.

I took out the data: part.

clausowitz · July 30, 2011

That did the trick for the php code. It's working now.

However the ajax doesn't send the right data somehow.

PFMaBiSmAd · July 30, 2011

If you post your whole form (so that someone doesn't need to create a page to test the code you did post), someone would likely try and find why it is not working.

clausowitz · July 30, 2011

<?php
// Start_session, check if user is logged in or not, and connect to the database all in one included file
include_once("scripts/checkuserlog.php");
include("scripts/check_page_name.php");
?>
<?php
////////////////////////////////////////////////      Member log in double check       ///////////////////////////////////////////////////
if (!isset($_SESSION['idx'])) { 
    $msgToUser = '<br /><br /><font color="#FF0000">Only site members can do that</font><p><a href="register.php">Join Here</a></p>';
    include_once 'msgToUser.php'; 
    exit(); 
} else if ($logOptions_id != $_SESSION['id']) {
$msgToUser = '<br /><br /><font color="#FF0000">Only site members can do that</font><p><a href="register.php">Join Here</a></p>';
    include_once 'msgToUser.php'; 
    exit(); 
}

// DEFAULT QUERY STRING
$queryString = "ORDER BY name";
// DEFAULT MESSAGE ON TOP OF RESULT DISPLAY
$queryMsg = "Showing Restaurants in alfabetical order.";

$status_name = 'checked';
$status_food = 'unchecked';
$status_rating = 'unchecked';

// IF WE HAVE A SESSION THEN FILL $OPTION WITH A VALUE
if(isset($_POST['submit'])) {
   		$_SESSION['order'] = $_POST['status'];
}
if(isset($_SESSION['order'])) {
if($_SESSION['order'] == 'name') { $status_name = 'checked'; }
if($_SESSION['order'] == 'food') { $status_food = 'checked'; }
if($_SESSION['order'] == 'rating') { $status_rating = 'checked'; }
	$what = $_SESSION['order'];
    	$queryString = "ORDER BY $what";
	if($what == 'rating') { $queryString = "ORDER BY $what DESC";
	   }
}  // end of if(isset($_SESSION....
/////////////// END SET UP FOR SEARCH CRITERIA QUERY SWITCH MECHANISMS */////////////

//////////////  QUERY THE MEMBER DATA USING THE $queryString variable's value
$sql = mysql_query("SELECT * FROM restaurants $queryString");  // WHERE email_activated='1' ORDER BY id ASC"); 
//////////////////////////////////// Adam's Pagination Logic ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$outputList = '';
$nr = mysql_num_rows($sql); // Get total of Num rows from the database query
if ($nr){  // if we found any records we will proceed
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
    //$pn = ereg_replace("[^0-9]", "", $_GET['pn']); // filter everything but numbers for security(deprecated)
} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
} 
//This is where we set how many database items to show on each page 
$itemsPerPage = 10; 
// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
    $pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
    $pn = $lastPage; // force it to be $lastpage's value
} 
// This creates the numbers to click in between the next and back buttons
$centerPages = ""; // Initialize this variable
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
$centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
$centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT * FROM restaurants $queryString $limit") ; 
//////////////////////////////// END Adam's Pagination Logic ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////// Adam's Pagination Display Setup ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
    // This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '<img src="images/clearImage.gif" width="48" height="1" alt="Spacer" />';
// If we are not on page 1 we can place the Back button
    if ($pn != 1) {
    $previous = $pn - 1;
	$paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
    } 
    // Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
    // If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
	$paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
    } 
}

///////////////////////////////////// END Adam's Pagination Display Setup ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Build the Output Section Here
$status = '';

$i = 0;
while($row = mysql_fetch_array($sql2)) { 

$id = $row["id"];
$name = $row["name"];
$food = $row["food"];
$address = $row["address"];
$telephone = $row["telephone"];
$rating = $row["rating"];
$votes = $row["votes"];

///////  Mechanism to Display Pic. See if they have uploaded a pic or not  //////////////////////////
$check_pic = "images/restaurants/$id.jpg";
if (file_exists($check_pic)) {
    $user_pic = "<img src=\"$check_pic\" width=\"150px\" heigth=\"100px\" border=\"0\" />"; // forces picture to be 120px wide and no more
} else {
$user_pic = "<img src=\"images/no_map.png\" width=\"150px\" heigth=\"100px\" border=\"0\" />"; // forces default picture to be 120px wide and no more
}
$i++;	//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Assign star image
if ($rating <= 0  ){$rater_stars = "0px";}
if ($rating >= 1  ){$rater_stars = "25px";}
if ($rating >= 2  ){$rater_stars = "50px";}
if ($rating >= 3  ){$rater_stars = "75px";}
if ($rating >= 4  ){$rater_stars = "100px";}
if ($rating >= 5  ){$rater_stars = "125px";}

$star = '<ul class="star-rating">
<li class="current-rating" id="current-rating'.$id.'" style="width: '.$rater_stars.'"></li>
<li><a href="javascript:void(0)" onclick="vote(1,'.$votes.','.$rating.','.$id.'); return false;" 
           title="1 star out of 5" class="one-star"></a></li>
<li><a href="javascript:void(0)" onclick="vote(2,'.$votes.','.$rating.','.$id.'); return false;" 
           title="2 star out of 5" class="two-stars"></a></li>
<li><a href="javascript:void(0)" onclick="vote(3,'.$votes.','.$rating.','.$id.'); return false;" 
           title="3 star out of 5" class="three-stars"></a></li>
<li><a href="javascript:void(0)" onclick="vote(4,'.$votes.','.$rating.','.$id.'); return false;" 
           title="4 star out of 5" class="four-stars"></a></li>
<li><a href="javascript:void(0)" onclick="vote(5,'.$votes.','.$rating.','.$id.'); return false;" 
           title="5 star out of 5" class="five-stars"></a></li>
</ul><div id="current-rating-result"></div>';

if ($i&1) { $outputList .= '<table width="100%" cellpadding="4" style="background-color: #FFFFFF; border="0">'; }
else { $outputList .= '<table width="100%" cellpadding="4" style="background-color: #E7F3FE; border="0">';  }
$outputList .= '<tr>
<td>  </td>
</tr><tr>
   <td width="12%" rowspan="6"><div style=" height:125px; overflow:hidden;">' . $user_pic . '</div></td>
   <td width="10%" class="style7"><div valign="right">Name:  </div></td>
   <td colspan="2"><font color="#3300CC"><b>' . $name . '</b></font></a> </td>
</tr>
<tr>
   <td width="10%" ><div valign="right">Food:</div></td>
   <td width="48%" valign="left">' . $food . ' </td>
   <td width="28%" valign="left"><div align="right">'.$star.'
   </div></td>
   <td width="2%" > </td>
</tr>
<tr>
   <td width="10%" ><div valign="right">Address:</div></td>
   <td colspan="2" width="48%" valign="left">' . $address . ' </td>
</tr>
<tr>
   <td width="10%" ><div valign="right">Telephone:</div></td>
   <td width="48%" valign="left">' . $telephone . ' </td>
</tr><tr>
<td>  </td>
</tr>
</table>
';
} 
}

// close while //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////// END QUERY THE MEMBER DATA & Build the Output Section ////////////////////////////

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Catering, Restaurants and Entertainment</title>
<link href="style/stars.css" rel="stylesheet" type="text/css" />
<link href="style/main.css" rel="stylesheet" type="text/css" />
<link rel="icon" href="favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="favicon.ico" type="image/x-icon" />
<script src="js/jquery-1.4.2.js" type="text/javascript"></script>
<style type="text/css">
<!--
.pagNumActive {
color: #000;
border:#060 1px solid; background-color: #D2FFD2; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:link {
color: #000;
text-decoration: none;
border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:visited {
color: #000;
text-decoration: none;
border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:hover {
color: #000;
text-decoration: none;
border:#060 1px solid; background-color: #D2FFD2; padding-left:3px; padding-right:3px;
}
.paginationNumbers a:active {
color: #000;
text-decoration: none;
border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
}
-->
</style>
<script language="javascript" type="text/javascript">
function vote(x,votes,rating,id) {
			current_rate =x+rating;
		mem = votes+1;
		sum = current_rate/mem;
		result = sum.toFixed(1)
		rating = result * 25;
		document.getElementById('current-rating'+id).style.width = rating+'px';
		document.getElementById('current-rating'+id).innerHTML= 'Thanks for your vote!<br />'
	    var dataString = 'id='+ x + '&rating=' + result;
		$.ajax({
		   type: "POST",
		   url: "score.php?id='+x+&rating='+result+" , // page where insertion to database should have made
		   success: function(msg){
			 alert("Counter updated" );
		   }
		 });
}

</script>
</head>
<body>
<?php include_once "header_template.php"; ?>
<table width="900" border="0" style="background-color: #F2F2F2; border:#CCC 1px solid;" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td width="738" valign="top"><div><?php include_once "leaderBoardAd.php"; ?></div>
      <div style="margin-left:64px; margin-right:64px;">
        <h2><span class="textsize15">List of <?php echo $nr; ?> restaurants.</span><br /><br />
      <table width="105%" align="center" border="0" cellpadding="6">
      <tr>
        <td bgcolor="#F2F2F2" width="30%"><form id="form1" name="form1" method="post" action="restaurants.php">
          <input name='status' type='radio' id='choose' value='name' <?PHP print $status_name; ?> />Order by Name 
        </td>
        <td bgcolor="#F2F2F2" width="29%">
          <input name='status' type='radio' id='choose' value='food' <?PHP print $status_food; ?>/>Order by Food
        </td>
        <td bgcolor="#F2F2F2" width="41%">
          <input name='status' type='radio' id='choose' value='rating' <?PHP print $status_rating; ?>/>
          Order By Rating                 
          <input name="submit" type="submit" id="submit" value="Go" />
        </form></td>
      </tr>
    </table> 
      </h2>
      </div>
      <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>
      <table width="80%" align="center" cellpadding="6">
        <tr>
          <td><?php //echo "$queryMsg"; ?><br /><br />
<?php echo "$outputList"; ?></td>
        </tr>
      </table>
      <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>
<br /><br /><br /></td>
    <td width="160" valign="top"><?php include_once "right_AD_template.php"; ?></td>
  </tr>
</table>
<?php include_once "footer_template.php"; ?>
</body>
</html>

clausowitz · July 31, 2011

It doesn't seem that someone can help me does it?

PFMaBiSmAd · July 31, 2011

LOL, if you use the following code in the end of your vote() function, the data will be available in score.php as $_POST['id'] and $_POST['rating'] -

		    var dataString = "id="+id+"&rating="+result;
		$.ajax({
			type: "POST",
			url: "score.php",
			data: dataString,
			success: function(msg){
				alert("Counter updated" );
			}
		});

clausowitz · July 31, 2011

PFMaBiSmAd,

that is what I have been trying all along. except my first line was:

var dataString = 'id='+ id + '&rating=' + result;

It doesn't work however.

When I add the variables in the URL like you suggested the database gets updated but not through the ajax function.

I have no clue,

MArco

PFMaBiSmAd · July 31, 2011

Are you sure you reloaded your page to get the changed javascript code to be loaded and changed your php code to use $_POST variables?

clausowitz · July 31, 2011

I did reload the pages.

With the POST you mean in the target php page I guess.

<?php
// Start_session, check if user is logged in or not, and connect to the database all in one included file
include_once("scripts/checkuserlog.php");
$q_id=$_POST['id'];
$q_rating=$_POST['rating'];
$sql = mysql_query("UPDATE restaurants SET votes= votes + 1, rating='".$q_rating."' WHERE id='".$q_id."' LIMIT 1");

?>

clausowitz · July 31, 2011

Also I am not getting the success alert message anymore.

clausowitz · July 31, 2011

OK now I feel absolutely stupid.

After installing firebug I found out that the link to my jquery-1.4.2.js file was not right.

That way we could have tested until the end of days.

Thanks for all the help.

Marco

Sign In

update mysql database doesn't work

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information