ansaurus

Question

Update data into mysql database by clicking on a link

Answer 1

A:

Sure, create a PHP scripts that updates the database as if a regular form had been submitted and then use Javascript to make a request to that script when the link is clicked.

See AJAX Introduction.

Artefacto 2010-07-11 10:31:38

Answer 2

A:

Use AJAX (with one of the available frameworks jQuery, mootools, Dojo,...): whenever a user clicks on a link, an attached action get's triggered and does the "magic".

Mostly this will be sending an AJAX request to the server with the comment id and/or any other data needed and the script will update the database.

SInce you already tagged the question with jQuery, http://api.jquery.com/category/ajax/ is a good starting point.

DrColossos 2010-07-11 10:32:45

Answer 3

A:

In HTML:

<a href="update.php?answer_id=1">vote</a>

In PHP:

$Q = 'UPDATE `votes` SET `count` = `count` + 1 WHERE `answer_id` = ?';
$db_connection->prepare($Q);
$db_connection->bind_param('i', $_GET['answer_id']);
$db_connection->execute();

When you have this ready, use AJAX to do it without reloading a page. Bind a function to voting links and send the request to a webpage in a same way but asynchronously.

In jQuery use something like:

$.get('update.php?id=1', function(data) {
  alert('Thanks for voting');
});

dwich 2010-07-11 10:39:36

You should use POST instead.

Artefacto 2010-07-11 11:24:53

What would the html code look like for jQuery?

Levani 2010-07-11 12:01:05

Same. Just add class="vote" to ahref and then use techique called http://en.wikipedia.org/wiki/Unobtrusive_JavaScriptUse jQuery to bind a function to all ahrefs with class=vote

dwich 2010-07-11 12:40:16

@Artefacto: Why should you use POST? Just interested as I would think that it shouldn't really matter either way.

Lucanos 2010-07-11 16:39:51

@Lucanos RFC 2616 9.1.1 " In particular, the convention has been established that the GET and HEAD methods SHOULD NOT have the significance of taking an action other than retrieval."

Artefacto 2010-07-11 19:18:52

That's true but in this case it's irrelevant. He's asking how to update his database by clicking a link. If you click a link, you have to use a GET method. Author should redesign hes poll to allow voting using form and POST method.

dwich 2010-07-11 19:22:05

@dwich No, you can generate a POST request by clicking on a link with Javascript.

Artefacto 2010-07-11 19:25:12

@Artefacto: Learn something new every day. Mind you, I believe that this RFC would have to be one of the most often overlooked/ignored, especially with the number of APIs out there which only offer GET interactions, whether retrieving or adding data. If you are a proponent for standards, you might want to submit a postJSON() function for jQuery.

Lucanos 2010-07-12 05:23:37

@Artefacto: (PS). What @dwich is saying is that a pure POST action cannot be submitted by clicking on a vanilla `<a href="">link</a>`, which is true. Whilst that functionality can be added, as you say, with javascript, it makes things difficult when trying to create graceful degradation.

Lucanos 2010-07-12 05:25:19

@Lucanos with vanilla anchors, even worse -- not only are you using a method that's conceptually wrong, now the web crawlers will also vote.

Artefacto 2010-07-12 10:11:31

@Artefacto: That's what I wrote: "Author should redesign his poll". Use form (POST) instead of links. We should comment author's question directly to notify him :)

dwich 2010-07-12 10:38:01

@Artefacto: Fair point, hadn't thought of that.

Lucanos 2010-07-12 15:59:38

Answer 4

A:

HTML

<form method="post" action="/vote.php">
  <input type="hidden" name="item" value="999">
  <input type="submit" name="submit" value="Vote Up">
</form>

PHP - vote.php

<?php
if( !isset( $_POST['item'] ) ) {
  header( 'HTTP/1.0 404 Not Found' );
  die( '<html><body>Missing <i>item</i> parameter</body></html>' );
}
$item = (int) $_POST['item'];
if( @mysql_query( "UPDATE `items` SET `vote`=`vote`+1 WHERE `id`=$item" ) ) {
  if( isset( $_POST['format'] ) && $_POST['format']=='json' )
    die( json_encode( array( 'result'=>true ) ) );
  die( '<html><body>Score for item #'.$item.' incremented</body></html>' );
}
header( 'HTTP/1.1 500 Internal Server Error' );
if( isset( $_POST['format'] ) && $_POST['format']=='json' )
    die( json_encode( array( 'result'=>false ) ) );
die( '<html><body>Score for item #'.$item.' FAILED to incremented</body></html>' );

jQuery

$( document ).ready( function() {
  $( 'form[action="/vote.php"]' ).each( function() {
    $( this ).replaceWith( '<span class="vote" parameter="item='+$( this ).find( 'input[name="item"]' ).attr( 'value' )+'">Vote Up</span>' );
  } );
  $( 'span[class="vote"]' ).click( function() {
    var $this = $( this );
    $.ajax( {
      type     : 'POST' ,
      url      : '/vote.php' ,
      data     : $( this ).attr( 'parameter' )+'&format=json' ,
      dataType : 'json' ,
      success  : function( data , status , XHR ) {
        if( data.result===true )
          alert( 'Voted Up' );
        else
          alert( 'Something Unexpected Borked' );
      } ,
      error    : function( XHR , status , error ) {
        alert( 'Something Borked' );
      }
    } );
  } );
} );

Note: This is an untested solution, but would be what I would consider a starting point to commence debugging and testing.

UPDATED: Changed behaviour to POST based on advice from @Artefacto.

Lucanos 2010-07-11 14:53:13

ansaurus

tags:

views:

answers:

Update data into mysql database by clicking on a link

related questions