• 0

[jQuery] Updating MySQL table with link click


Question

Basically I've been trying for 3 hours to make the table update with a click of a link using jQuery. The link ID is up and the link points to nowhere because I don't want the url changing. I'm wondering if anybody can assist me with how to make the column in the table update (+1 each click).

Thanks,

S2

23 answers to this question

Recommended Posts

  • 0

Ah sorry, I misread your post.

To do that without the page reloading when the link is clicked, you'll need to make an AJAX call to a script on the server which will then alter the table.

So, your javascript would do something like this when your link is clicked;

$.ajax({
   type: "GET",
   url: "the-script.php"
 });

and then the script on the server would do something like

// connect to database
//...

$query = "YOUR SQL QUERY TO UPDATE THE TABLE";
$res = mysql_query($query);

  • 0

Seems you're quite unclear on how most of the technologies you'll need to achieve this even work.

Also you're not giving nearly enough information to the potential people willing to help you.

Your question gives away that you're new at this, and this implies lots of baby steps and holding your hand for whoever wants to help you...

Seriously, sit down and write an extensive description of your database, your html, your javascript skills, AND the exact thing you're trying to achieve, so that someone can guide you using knowledge that you already have.

Here's how something like what you're describing usually works:

1. Browser renders HTML elements, Javascript attaches event listeners for the click events of the desired elements

2. User clicks the element, the listener Javascript function gets executed by the Browser

3. In the function you should achieve an asynchronous call(I see you sort of want to not refresh the page at all?) to the Server, where your only chance of manipulating your database lies

4. your piece of code at your server does the work related to the updating of the database, depending on the data passed by the Browser, and potentially coming back with a result status code

5. potentially, you update the HTML in the Browser depending on the response code/message, for the User to see

I hope this helps

  • 0
  On 15/04/2011 at 13:24, XakepaBG said:
..., sit down and write an extensive description of your database, your html, your javascript skills, AND the exact thing you're trying to achieve, so that someone can guide you using knowledge that you already have.

;)

  • 0

I know I'm not giving the best explanations but I'm sure you can gather what I am after.

I'll explain it here: I have a link that when I press it I want it to update a column in the MySQL table based on the ID of the "post". I don't want the URL to change and it needs to refresh back to the normal page.

Please give me any help you can offer, it's all I really ask.

  • 0

OK, you should be able to figure what this quick example I made for you does. :)

Actually, Neowin's code highlighter/formatting is pretty rubbish. Here's a pastie link: http://pastie.org/1800656

<?php
if('POST' === $_SERVER['REQUEST_METHOD']){
  printf(
    "You sent me the number %d, at %s.",
    empty($_POST['number']) ? 0 : $_POST['number'] ,
    date('r')
  );
  exit;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">;html xmlns="http://www.w3.org/1999/xhtml">lt;head>
				<meta http-equiv="content-type" content="text/html; charset=utf-8" />
				<link rel="stylesheet" type="text/css" media="screen" href="http://yui.yahooapis.com/3.3.0/build/cssreset/reset-min.css" />
				<link rel="stylesheet" type="text/css" media="screen" href="http://yui.yahooapis.com/3.3.0/build/cssbase/base-min.css" />
				<link rel="stylesheet" type="text/css" media="screen" href="http://yui.yahooapis.com/3.3.0/build/cssfonts/fonts-min.css" />
				<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>
				<style type="text/css">
				</style>
				<title>
						Demo
				</title>
		</head>
		<body>

      <?php foreach(range(1, 15) as $number): ?>
        <span class="clickable"><?php echo $number; ?></span>
      <?php endforeach; ?>

      <div id="response"></div>

      <script type="text/javascript">
        //<![CDATA[
        $(document).ready(function(){
          $('span.clickable').each(function(){
            $(this).click(function(){
                $.ajax({
                  type: 'POST',
                  url: './index.php',
                  data: 'number=' + $(this).html(),
                  success: function(response){
                    $('#response').html(response);
                  }
                });
            });
          });
        });
        //]]>
      </script>

		</body>
</html>

  • 0
  On 16/04/2011 at 13:22, Shazer2 said:

Anyone? Could someone else give me a hand with this. I don't really know what URL to give the link, because I wanted it updated without the page URL having to change, but do the same as the form submission but update the MySQL table. Ideas?

XakepaBG made some good points; You really need to provide more information - what form submission are you talking about?

My previous post outlined what you need to do to update the database without reloading the page.

There's varying opinions on what you should use for the href attribute for javascript-only links. href='#' is popular, but will require the onclick (or jquery bound) event to return false or run preventDefault() on the event object so that the browser doesn't try navigating to # (which won't reload the page, but will scroll it. Another approach is to use href='javascript:void(0)'. You won't have to use javascript in your click handler to cancel the event's default behaviour using this approach - so it's probably better than the # approach.

The best approach is to have the link actually go to a valid URL but have javascript override that behaviour to provide a better user experience. This way, users without javascript enabled can still click the link to make the database change (but the browser will have to (re)load a page) and users with javascript enabled get the enhanced experience of being able to do exactly the same thing but without the browser reloading the page.

Using my previous code as an example and this HTML;

<a id='link' href="the-script.php">Click me</a>

$('#link').click(function(){
    // Make the call to the script that performs the database update asynchronously (ie, by not reloading the page)
    $.ajax({
        type: "GET",
        url: "the-script.php"
    });
    return false; // Prevent the browser from navigating to the-script.php
});

Edit: Regarding AnthonySterling's solution; you will need to write the code that updates the database yourself. We're not psychic, we have no idea what changes you want to be made in the database. Put an SQL query that does what you want in the if statement at the top. Similarly, you'll need to generate the actual links with correct post IDs.

  • 0

Thanks for that code, Mouldy Punk. I believe we ARE getting places, but what I need to do is send the ID of the "post" so a part of it can be updated in the table. How can I send the ID as a value to the the-script.php to be used to update in the DB.

Not sure how to go about this, I'm calling this jQuery in a mysql_fetch_array loop too, because that's how I pull the "post" data from the database.

  • 0

Without seeing your existing code, it'll be tricky for us to give you more help really. You'll need to somehow make the post ID accessible to the click event of each link.

For example, if you had a series of links (one for each post) that went along the lines of;

<a href='#' onclick="javascript:clickHandler(1); return false;">Click me</a>

where the parameter of clickHandler is different for each link (it's the post ID)

The you could have the javascript function that'll post that post id to the PHP script on the server;

function clickHandler(post_id){
     $.ajax({
                  type: 'POST',
                  url: 'the-script.php',
                  data: 'post_id=' + post_id
      });
}

Then the PHP script on the server would take that post id and do a mysql query using it;

&lt;?php
$post_id = $_POST['post_id'];
// connect to db
mysql_query('do something with $post_id');
// etc.
?&gt;

  • 0

Alright, I'm getting a better idea of what I need to do, but I can't seem to get it to execute the clickHandler function. Here is my code, see if you can help me from there.

&lt;?php
            $sql = mysql_query("SELECT * FROM `posts` ORDER BY up DESC") or die(mysql_error());
            while ($posts = mysql_fetch_array($sql)) {
                echo "&lt;b&gt;".$posts['user']."&lt;/b&gt; (&lt;a href=\"inc/up.php\" id=\"up\" onclick=\"javascript:clickHandler(1); return false;\"&gt;&lt;img src=\"inc/images/up.png\"></a>.$posts['up']." &lt;img src=\"inc/images/down.png\">.$posts['down'].")&lt;br /&gt;&lt;br /&gt;";
                echo "&lt;i&gt;\"".$posts['np']."\"&lt;/i&gt;&lt;hr /&gt;";

                echo "
                function clickHandler(post_id){
                    $.ajax({
                        type: 'POST',
                        url: 'inc/up.php',
                        data: 'post_id=' + post_id
                    });
                }
                &lt;/script&gt;";
            }
            ?&gt;

  • 0
  On 16/04/2011 at 06:29, Shazer2 said:

Xakepa, I'm obviously a beginner at this. I didn't ask for you to spoonfeed me, don't treat me like that. I learn off examples. From what Mouldy Punk showed me I think I can achieve this, I'll try me best and make an edit if I can get it.

Lol, you post a thread showing your complete ignorance of the subject and when somebody tries to help you get bitchy. :rolleyes:

  • 0

jQuery and PHP code to pull posts from database, display a a link to put +1 onto it.

$sql = mysql_query("SELECT * FROM `posts` ORDER BY up DESC") or die(mysql_error());
            while ($posts = mysql_fetch_array($sql)) {
                echo "&lt;b&gt;".$posts['user']."&lt;/b&gt; (&lt;a href=\"inc/up.php\" id=\"up\" onclick=\"javascript:clickHandler(1); return false;\"&gt;&lt;img src=\"inc/images/up.png\"></a>.$posts['up']." &lt;img src=\"inc/images/down.png\">.$posts['down'].")&lt;br /&gt;&lt;br /&gt;";
                echo "&lt;i&gt;\"".$posts['np']."\"&lt;/i&gt;&lt;hr /&gt;";

                echo "
                &lt;script type=\"text/javascript\"&gt;
                $('#up').click(function() {
                    $.ajax({
                        type: 'POST',
                        url: 'inc/up.php',
                        data: 'id=' + ".$posts['id']."
                    });
                });
                &lt;/script&gt;";
            }
?&gt;

up.php that processes the click:

include("connect.php");

$id = $_POST['id'];
$id = mysql_real_escape_string($id);

mysql_query("UPDATE `posts` SET up += 1 WHERE id='$id'") or die(mysql_error());

Just updating so I could gather more help, it still isn't posting an ID so if you know why, please help.

  • 0

Upload this somewhere, in a folder of its own and name it index.php. Once it's live, have a play around with it, if you have any specific questions, let me know.

&lt;?php
function records(){
  return array(
    array(
      'title' =&gt; 'PHP is cool',
      'id'    =&gt; 1,
      'votes' =&gt; rand(1, 10)
    ),
    array(
      'title' =&gt; 'jQuery is cool',
      'id'    =&gt; 2,
      'votes' =&gt; rand(1, 10)
    ),
    array(
      'title' =&gt; 'AJAX is cool',
      'id'    =&gt; 3,
      'votes' =&gt; rand(1, 10)
    ),
  );
}

if('POST' === $_SERVER['REQUEST_METHOD']){

  list($direction, $id) = explode('-', $_POST['data']);

  $filter = array(
    'up'    =&gt; 'up = up + 1',
    'down'  =&gt; 'down = down + 1'
  );

  if(array_key_exists($direction, $filter)){
    $sql = sprintf(
      "UPDATE table SET %s WHERE id = %d LIMIT 1;",
      $filter[$direction],
      $id
    );

    echo $sql;
    exit;
  }

}
?&gt;
&lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">;html xmlns="http://www.w3.org/1999/xhtml">lt;head&gt;
    &lt;meta http-equiv="content-type" content="text/html; charset=utf-8" /&gt;
    &lt;link rel="stylesheet" type="text/css" media="screen" href="http://yui.yahooapis.com/3.3.0/build/cssreset/reset-min.css" /&gt;
    &lt;link rel="stylesheet" type="text/css" media="screen" href="http://yui.yahooapis.com/3.3.0/build/cssbase/base-min.css" /&gt;
    &lt;link rel="stylesheet" type="text/css" media="screen" href="http://yui.yahooapis.com/3.3.0/build/cssfonts/fonts-min.css" /&gt;
    &lt;script src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"&gt;&lt;/script&gt;
    &lt;style type="text/css"&gt;
      td.votable{
        text-align: center;
        cursor: pointer;
      }
    &lt;/style&gt;
    &lt;title&gt;
      Demo
    &lt;/title&gt;
  &lt;/head&gt;
  &lt;body&gt;

    &lt;table&gt;
      &lt;thead&gt;
        &lt;tr&gt;
          &lt;th&gt;Title&lt;/th&gt;
          &lt;th&gt;Votes&lt;/th&gt;
          &lt;th&gt;Vote Up&lt;/th&gt;
          &lt;th&gt;Vote Down&lt;/th&gt;
        &lt;/tr&gt;
      &lt;/thead&gt;
      &lt;tbody&gt;
      &lt;?php foreach(records() as $record): ?&gt;
        &lt;tr&gt;
          &lt;td&gt;&lt;?php echo $record['title']; ?&gt;&lt;/td&gt;
          &lt;td&gt;&lt;?php echo $record['votes']; ?&gt;&lt;/td&gt;
          &lt;td class="votable" id="up-&lt;?php echo $record['id']; ?&gt;"&gt;Up&lt;/td&gt;
          &lt;td class="votable" id="down-&lt;?php echo $record['id']; ?&gt;"&gt;Down&lt;/td&gt;
        &lt;/tr&gt;
      &lt;?php endforeach; ?&gt;
      &lt;/tbody&gt;
    &lt;/table&gt;

    &lt;script type="text/javascript"&gt;
    //&lt;![CDATA[
      $(document).ready(function(){
        $('td.votable').each(function(){
          $(this).click(function(){
            $.ajax({
              type: 'POST',
              url: './index.php',
              data: 'data=' + $(this).attr('id'),
              success: function(response){
                alert(response);
              }
            });
          });
        });
      });
    //]]&gt;
    &lt;/script&gt;

  &lt;/body&gt;
&lt;/html&gt;

http://pastie.org/1803131

I've uploaded the script above to my server here: http://dev.anthonysterling.com/neowin/

It won't be there indefinitely though. :)

  • 0
  On 17/04/2011 at 00:14, Shazer2 said:

jQuery and PHP code to pull posts from database, display a a link to put +1 onto it.

$sql = mysql_query("SELECT * FROM `posts` ORDER BY up DESC") or die(mysql_error());
            while ($posts = mysql_fetch_array($sql)) {
                echo "&lt;b&gt;".$posts['user']."&lt;/b&gt; (&lt;a href=\"inc/up.php\" id=\"up\" onclick=\"javascript:clickHandler(1); return false;\"&gt;&lt;img src=\"inc/images/up.png\"></a>.$posts['up']." &lt;img src=\"inc/images/down.png\">.$posts['down'].")&lt;br /&gt;&lt;br /&gt;";
                echo "&lt;i&gt;\"".$posts['np']."\"&lt;/i&gt;&lt;hr /&gt;";

                echo "
                &lt;script type=\"text/javascript\"&gt;
                $('#up').click(function() {
                    $.ajax({
                        type: 'POST',
                        url: 'inc/up.php',
                        data: 'id=' + ".$posts['id']."
                    });
                });
                &lt;/script&gt;";
            }
?&gt;

up.php that processes the click:

include("connect.php");

$id = $_POST['id'];
$id = mysql_real_escape_string($id);

mysql_query("UPDATE `posts` SET up += 1 WHERE id='$id'") or die(mysql_error());

Just updating so I could gather more help, it still isn't posting an ID so if you know why, please help.

The reason why I was using the onclick attribute instead of using a jquery selector's click event is because you'd need a unique ID for every link. You've got <a id='up'> being generated multiple times in a while loop. HTML ids should be unique on a page, there should be no duplicates. Look at the final output (view source in your browser), the code you generate makes no sense - there's multiple $('#up').click() binds and multiple <a id='up'> elements - the browser has no way of telling which click function goes with which <a> element.

The method I mentioned previously would mean that the javascript function is output just once. Each of the onclick javascript calls give that single function different parameters (post IDs) making it send the right post ID. It is much simpler to use vanilla javascript, like I mentioned, to set up the function call than try and bind multiple event listeners using jQuery.

  • 0
  On 17/04/2011 at 08:55, AnthonySterling said:

Upload this somewhere, in a folder of its own and name it index.php. Once it's live, have a play around with it, if you have any specific questions, let me know.


http://pastie.org/1803131

I've uploaded the script above to my server here: http://dev.anthonysterling.com/neowin/

It won't be there indefinitely though. :)

That's really handy. Thanks a lot.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • They are shifting into AI now. Don't you see?
    • Exactly. No need to pay to rent a license. I'd rather own it.
    • Weekend PC Game Deals: Automation fests, Civilization for free, charity specials, and more by Pulasthi Ariyasinghe Weekend PC Game Deals is where the hottest gaming deals from all over the internet are gathered into one place every week for your consumption. So kick back, relax, and hold on to your wallets. The Epic Games Store unlocked a big strategy game giveaway earlier this week: Civilization VI: Platinum Edition. Coming in from Firaxis Games, the turn-based 4X experience has you starting world-conquering campaigns to explore, expand, exploit, and exterminate everything in your empire's reach. PvP and co-op multiplayer are also options if the various forms of AI prove to be too easy or even too troublesome. The Sid Meier’s Civilization VI: Platinum Edition giveaway is live until July 24, and it comes with two massive expansions as well as six DLC packs with extra scenarios, leaders, and more. Next week, tower defense title Legion TD 2 will become the latest freebie on the Epic Games Store. The Humble Store brought a new bundle for action game fans this weekend, and it's all about the Devil May Cry franchise. The Devil Trigger Collection begins with DmC: Devil May Cry and Devil May Cry HD Collection for $10. If you want to complete the bundle, it will set you back $20, which gets you Devil May Cry 4 Special Edition as well as the most recent entry, Devil May Cry 5, as well as its Vergil DLC. This bundle has two weeks left on its counter before it goes away. Big Deals Most publishers are returning to their usual weekend specials after the massive summer sales, so there are plenty of discounts to go around. There's even a special Make a Wish charity sale running on Steam with some discounted viral hits. With all those and more, here's our hand-picked big deals list for the weekend: Satisfactory – $27.99 on Steam Captain of Industry – $24.49 on Steam No Man's Sky – $23.99 on Steam Persona 5 Royal – $23.99 on Steam No More Room in Hell 2 – $22.49 on Steam FOUNDRY – $20.99 on Steam Banishers: Ghosts of New Eden – $19.99 on Steam SULFUR – $19.99 on Steam Assassin's Creed Mirage – $19.99 on Steam Alan Wake 2 – $19.99 on Epic Store Grand Theft Auto V Enhanced – $19.79 on Steam Norland – $19.49 on Steam Stray – $17.99 on Steam V Rising – $17.49 on Steam Dyson Sphere Program – $15.99 on Steam The Outlast Trials – $15.99 on Steam Warhammer 40,000: Darktide – $15.99 on Steam The Outlast Trials – $15.99 on Steam Red Dead Redemption 2 – $14.99 on Steam Turing Complete – $13.99 on Steam Eden Crafters – $13.99 on Steam Core Keeper – $13.99 on Steam Thank Goodness You're Here! – $12.99 on Steam Opus Magnum – $9.99 on Steam Autonauts – $9.99 on Steam EXAPUNKS – $9.99 on Steam DRAGON BALL XENOVERSE 2 – $9.99 on Steam Superliminal – $9.99 on Steam Heaven's Vault – $9.99 on Steam RAILGRADE – $9.89 on Steam Goat Simulator 3 – $9.89 on Steam Tchia – $9.89 on Steam ACE COMBAT 7: SKIES UNKNOWN – $9.59 on Steam PAYDAY 3 – $8.99 on Steam Assassin's Creed Origins – $8.99 on Steam Viewfinder – $8.74 on Steam Escape Academy – $7.99 on Steam Pit People – $7.99 on Steam Skull and Bones – $7.99 on Steam Immortals Fenyx Rising – $7.99 on Steam Imperator: Rome – $7.59 on Steam SHENZHEN I/O – $7.49 on Steam Tom Clancy’s The Division 2 – $7.49 on Steam Bassmaster Fishing – $7.49 on Steam Let's Build a Zoo – $6.99 on Steam The Forgotten City – $6.24 on Steam Control Ultimate Edition – $5.99 on Steam Bramble: The Mountain King – $5.99 on Steam Assassin’s Creed Rogue – $5.99 on Steam RoboCop: Rogue City – $4.99 on Steam Kingdom Two Crowns – $4.99 on Steam Scott Pilgrim vs. The World: The Game – $4.94 on Steam Castle Crashers – $4.49 on Steam BattleBlock Theater – $4.49 on Steam TOEM: A Photo Adventure – $3.99 on Steam Supraland – $3.99 on Steam Vampire Survivors – $3.99 on Steam Darkwood – $3.74 on Steam Valiant Hearts: The Great War – $3.74 on Steam TIS-100 – $3.49 on Steam PAYDAY 2 – $3.29 on Steam Cake Bash – $2.99 on Steam Ragnarock – $1.99 on Steam Alan Wake – $1.49 on Steam Civilization VI Platinum Edition – $0 on Epic Store DRM-free Specials Lastly, here are some highlights from the DRM-free discounts available on the GOG store this weekend: Age of Wonders 4 - $29.99 on GOG Pathfinder: Wrath of the Righteous - Game of the Year Edition - $19.99 on GOG Tomb Raider IV-VI Remastered - $19.49 on GOG The Thaumaturge - $19.24 on GOG Chained Echoes - $13.74 on GOG Tyranny - Gold Edition - $12.49 on GOG Tomb Raider I-III Remastered Starring Lara Croft - $11.99 on GOG Baldur's Gate: Enhanced Edition - $9.99 on GOG Baldur's Gate II: Enhanced Edition - $9.99 on GOG Neverwinter Nights: Enhanced Edition - $9.99 on GOG Old World - $9.99 on GOG Icewind Dale: Enhanced Edition - $9.99 on GOG Neverwinter Nights: Doom of Icewind Dale - $7.99 on GOG Kingdom Come: Deliverance - $5.99 on GOG Might and Magic 6-pack Limited Edition - $4.99 on GOG Heroes of Might and Magic 3: Complete - $4.99 on GOG Blood Omen: Legacy of Kain - $3.49 on GOG Might and Magic 8: Day of the Destroyer™ - $2.99 on GOG Worms Armageddon - $2.99 on GOG ATOM RPG: Post-apocalyptic indie game - $2.99 on GOG Keep in mind that availability and pricing for some deals could vary depending on the region. That's it for our pick of this weekend's PC game deals, and hopefully, some of you have enough self-restraint not to keep adding to your ever-growing backlogs. As always, there are an enormous number of other deals ready and waiting all over the interwebs, as well as on services you may already subscribe to if you comb through them, so keep your eyes open for those, and have a great weekend.
  • Recent Achievements

    • One Month Later
      Helen Shafer earned a badge
      One Month Later
    • One Month Later
      ambani880 earned a badge
      One Month Later
    • Week One Done
      ambani880 earned a badge
      Week One Done
    • First Post
      artistro08 earned a badge
      First Post
    • First Post
      paul29 earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      498
    2. 2
      ATLien_0
      223
    3. 3
      Michael Scrip
      196
    4. 4
      Xenon
      161
    5. 5
      +FloatingFatMan
      138
  • Tell a friend

    Love Neowin? Tell a friend!