http___programlama.venus.gen.tr_kategori_asp_1.xml
1.1 2.1 3.1 3.2 4.1 4.2 5.1 5.2 6.1 7.1 8.1 9.1 9.2 10.1 11.1 11.2 12.1 13.1 14.1 15.1 15.2 16.1 17.1 18.1 19.1 19.2 19.3 19.4 20.1 21.1 22.1 23.1 24.1 24.2 25.1 25.2 26.1 27.1 28.1 29.1 30.1 30.2 31.1 32.1 32.2 33.1 33.2 34.1 34.2 35.1 36.1 37.1 37.2 38.1 38.2 39.1 39.2 39.3 40.1 41.1 42.1 42.2 42.3 44.1 45.1 46.1 46.2 47.1 47.2 47.3 48.1 48.2 49.1 50.1 50.2 50.3 51.1 52.1 52.2 53.1 54.1 55.1 56.1 57.1 58.1 59.1 60.1 61.1 62.1 62.2 63.1 64.1 65.1 65.2 66.1 66.2 67.1 67.2 67.3 68.1 69.1 69.2 70.1
Asked recently on Formspring: how to increment count in database on clicking radio button There are a few ways to go about this. I’ll demonstrate two: a traditional, PHP / MySQL only, postback approach, and a jQuery version that uses AJAX to asynchronously record and update the counts. Just to be clear: In order to complete this solution, we have to use both JavaScript and a server-side scripting language. We use JavaScript to intercept the user clicking the radio button, but process the fact that the button was clicked on the server. Also, for the purpose of this tutorial, I’ll assume that the radio button involved is part of a group. That is, we have several radio buttons, all with the same name, but different values, e.g.: Select a color:    Red  ( 0 ) |   Green  ( 0 ) |   Blue  ( 0 ) |   Black  ( 0 ) Example 1: PHP / MySQL Postback The easiest way to process a click is right on the page that contains the radio button, via a simple postback. We begin with a MySQL table that will hold our count. It has two columns: color_name, which will contain unique values and thus can act as our table’s primary key; and color_count, the number of times that color has been clicked. CREATE TABLE IF NOT EXISTS `colorcounter` ( `colorname` varchar(5) NOT NULL, `colorcount` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`colorname`) ) Next, we query the database for the current counts, which we’ll display in our form, later. We’ll dump the results set into an associative array, which will make outputting the counts in our form a little easier. $link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server'); mysql_select_db('database') or die('Cannot select database'); // click-count increment routine will go here; more on that shortly ... // get current color click counts $rs = mysql_query("SELECT * FROM colorcounter") or die ('Cannot process SQL count totals query'); if(mysql_num_rows($rs) > 0) { while($row = mysql_fetch_array($rs)) { $count[$row['colorname']] = $row['colorcount']; } } We now need to set up our form to be ready to process the user click, and to display the click count records we previously received. - We set the form to submit to itself. - We add an onclick event to each radio button, instructing it to submit the form. (The logic that will process the click follows shortly; we’re just assuming that every time the form is submitted, we need to increment a counter.) - We display the current click counts after each radio button. ">  Select a color:    Red  (   ) |   Green  (   ) |   Blue  (   ) |   Black  (   ) Finally, we need to add some code to the page that will see if the form has been submitted, and increment the appropriate count. // if this is a postback ... if(isset($_POST['color_name'])) { // create array of acceptable values $ok = array('red', 'green', 'blue', 'black'); // if we have an acceptable value for color_name ... if(in_array($_POST['colorname'], $ok)) { // update the counter for that color $q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_POST['color_name'] . "'") or die ("Error updating count for " . $_POST['color_name']); } } Note that this code block should appear before the code block that gets the current click counts (where I included the comment in the first block of PHP code), in order to ensure the counts are up-to-the-minute. You can see a working demo here: http://demo.dougv.com/ajax_radio_clickcount/index.php Example 2: Using AJAX The previous example has a number of drawbacks, not the least of them being that each time you click on a radio button, the entire form posts back to the server. That limits its usefulness quite a bit. We could work around this problem a few ways; perhaps making sure we repopulate our form fields following the postback, and only performing final processing when the submit button is clicked (by adding an onclick event to that button, which hands off the results to a JavaScript function or a different processing page). That’s an awful lot of work for very little benefit. It makes way more sense to leverage AJAX, and update our click counts without having to post the form itself back to the server. JavaScript — more specifically, jQuery — to the rescue! To me, it makes sense to encumber the jQuery library whenever you create an AJAX script, rather than reinventing the wheel with an old-school XMLHTTPRequest object that has to be written several ways to ensure it works in whatever browser you encounter. To get our jQuery solution to work, we need to create two PHP “helper” pages: one that will update our click counts whenever a radio button is selected, and the other that will get the current click counts, both on initial page load and following a click. The get_counts.php Helper Page Let’s start by calling the helper page that gets our click counts get_counts.php. It works almost exactly like the code block that gets the counts in our PHP postback example, except this page is going to output our results in JSON. We’re putting the results out that way because it’s very easy for jQuery to process data in JSON format. Or, to put it even another way, think of JSON as the JavaScript version of an XML file. It’s just a way to organize data into columns and rows. JSON is actually a bit more complex than that, but the way we’re using it, JSON is just a recordset, exactly like we would get from a MySQL query. It’s just written differently. Let’s look page will expect to see a querystring variable named “color,” which indicates which value needs to be incremented. The page code itself will be very similar to the processing code in our postback version. $link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server'); mysql_select_db('database') or die('Cannot select database'); // get new count totals, pass as JSON $rs = mysql_query("SELECT * FROM colorcounter") or die('Cannot get updated click counts'); if(mysql_num_rows($rs) > 0) { $out = "{ "; while($row = mysql_fetch_array($rs)) { $out .= "\"$row[colorname]\" : $row[colorcount], "; } $out = substr($out, 0, strlen($out) - 2); $out .= " }"; header("Content-type: application/json"); echo $out; } The increment_counter.php Helper Page Now that we have a way to get the counts, we need a way to increment them. Once again, we make a PHP helper page — increment_counter.php — that works fundamentally the same as the code block that increments the click counts in our PHP postback page, except that it calls upon GET, instead of POST. (Yes, you can use jQuery to make POST and GET AJAX requests. When I’m sending a single variable that just needs to meet a range, I prefer to use GET.) $link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server'); mysql_select_db('database') or die('Cannot select database'); // if this is a postback ... if(isset($_GET['color'])) { // create array of acceptable values $ok = array('red', 'green', 'blue', 'black'); // if we have an acceptable value for color_name ... if(in_array($_GET['color'], $ok)) { // update the counter for that color $q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_GET['color'] . "'") or die ("Error updating count for " . The Form And JavaScript With our helper pages out of the way, we can proceed to work on the form and the jQuery JavaScript needed to make this work. As usual, our first step is to procure and include a copy of jQuery on our page. Lately, I prefer to simply link to the version hosted by the Google Libraries API, but you can download jQuery directly and run it off your server if you prefer. Either way, call it somewhere in your page’s head section: Next, I want to strip the form back to its basic components. Select a color:    Red  ( 0 ) |   Green  ( 0 ) |   Blue  ( 0 ) |   Black  ( 0 ) Notice that I no longer have onclick events assigned to each radio button. That’s because I’ll wire up that event via $(document).ready. More on that shortly. First, I want to declare a global variable, lastChecked. It will keep track of the value of the last radio button that was checked. This will keep our page from recording multiple clicks on the same radio button, over and over again; clicks won’t be recorded unless, and until, a new value is clicked. In other words, if I just keep clicking Red, it will only be recorded once; if I then click Blue, that will be incremented, and clicking Red again will increment its value, once again. var lastClicked = ''; I next want to write JavaScript functions for each of my helper pages. The first, getTotals(), will — as its name suggests — send an AJAX request to get the current click totals. It will then assign those totals to the labels after each radio button. function getTotals() { // function to get click counts as JSON from helper page // expects get_count.php to be in same directory level $.ajax({ type: "GET", url: "get_count.php", dataType: "json", error: function(xhr, status, msg) { alert("Failed to get click counts: " + msg); } }) .done(function(data) { // loop through JSON variables, assign to count labels $.each(data, function(key, value) { var tmp = "#c_" + key; $(tmp).text(value); }); }); } I can now write the JavaScript function that will increment click counts. It’s named, quite cleverly, processClick, and takes as its argument the radio button (obj, for “object”) that was clicked. function processClick(obj) { // function to increment click count via ajax // expects increment_count.php to be in same directory level if(lastClicked != obj.val()) { // don't count clicks on currently active radio button lastClicked = obj.val(); // set currently clicked radio button to this one var qs = "color=" + obj.val(); // set query string value $.ajax({ type: "GET", url: "increment_count.php", data: qs, error: function(xhr, status, msg) { alert("Failed to process click count: " + msg); } }) .done(function() { getTotals(); // update totals on successful processing }); } } Finally, we want to use $(document).ready to initially populate the click total labels, and to assign processClick as the event handler for clicks on all the radio buttons on the page. $(document).ready(function() { getTotals(); // get click totals on initial page load $(document).ready(function() { // add click incrementing event handler to all radio buttons on page $('input:radio').click(function() { processClick($(this)); }); }); }); You can see a working example here: http://demo.dougv.com/ajax_radio_clickcount/example2.htm Notes And Code A few notes about this project: It’s not overly difficult for someone to click spam what I have here, either by simply clicking a radio button multiple times, or by calling the increment_count.php helper page multiple times. A way around that would be to create single-use tokens for each iteration of the form, so that only one click per rendering of the form would be allowed. You could do that by creating a session variable or cookie, if you’re using the PHP postback example; setting a Boolean in that session / cookie to be true the first time the form is submitted; and refusing to process the click if that session / cookie Boolean is true. Or, for more security, you could add another MySQL table, which counts the number of times your form has been rendered. It would consist of an autoincremented primary key, which would act as an identifier for this instance of the form; and an tinyint column for recording a Boolean, which would indicate if the form had been submitted. Every time your form is rendered, you would insert a new record in your form-rendering table, with a Boolean value of false indicating that the form has not yet been processed, then use mysql_insert_id to get the primary key value of the record you just created. Using mcrypt, you would then create a single-use token by encrypting that record ID with a secret key only you know, and pass that to whatever method you are using to increment the counter. That processing code would decrypt the token, make sure the record with that ID has a Boolean false as its “form has been submitted” value. If not, the code would not process the click; but if so, the code would process the click, then mark the form’s Boolean value as true. An added benefit of this method is that it also inhibits forgeries and cross-site scripting attacks, since the only way to create legitimate tokens is via your secret key. (Theoretically, a hacker could crack your key, but use a sufficient passphrase and a good crypto algorithm, and the chances of anyone even taking a crack at it, nonetheless succeeding, are infinitesimal.) Sounds more complex than it is. I’ve got demonstrating how to make a single-use token like this in Evernote blog column ideas. If significant interest is expressed, I’ll move it to the top of the list. Code on github: https://github.com/dougvdotcom/js_radio_clickcount I distribute code under the GNU GPL. See Copyright & Attribution for details. All links in this post on delicious: http://delicious.com/dougvdotcom/how-to-increment-a-counter-in-mysql-based-on-a-radio-button-click
1.1 2.1 3.1 4.1 5.1 6.1 7.1 8.1 9.1 10.1 11.1 12.1 13.1 14.1 15.1 16.1 17.1 18.1 19.1 20.1
Bu yazımızda MySQL veritabanına ASP ve PHP’de nasıl bağlantı sağlanacağımızı ve veri çekebileceğimizi öğreneceğiz. Öncelikle ASP ile başlayalım. ASP’de bir MySQL bağlantısı için ADODB objesinden yararlanırız. Set Baglanti = Server.CreateObject("ADODB.Connection") Baglanti.Open "Driver={MySQL ODBC 3.51 Driver};Server=sunucu_adres;Database=db_adi;Port=3306;Uid=kullanici;Password=sifre;" Set Kayitlar = Baglanti.Execute( "SELECT * FROM tablo_adi ORDER BY id DESC LIMIT 0, 10;" ) Do While Not Kayitlar.Eof Response.Write Kayitlar("alinacak_veri") + " " Kayitlar.MoveNext Loop Baglanti.Close Yukarıda geçen “Baglanti” ve “Kayitlar” birer değişken olup. Biri bize bağlantımız hakkında bilgi verir, diğeri ise Baglanti.Execute() ile istediğimiz kayıtları barındırır. Bağlantıyı Baglanti.Close() ile kapatmayı unutmayın. Yukarıda yer alan Kayitlar.EOF bir mantıksal değer olup ancak kayıt listelemeleri bittikten sonra true (doğru) karşılığını verir. Kayitlar.MoveNext ise bir sonraki kaydı istemek için kullanacağımız koddur. Yukarıda bağlantıyı sağlarken kullanılan sunucu_adresi, db_adi, kullanıcı, şifre bilgilerini doğru yazmazsanız bağlantı sağlanamaz. Bunlar MySQL’e bağlantı için gereklidir. Peki PHP’de MySQL’e nasıl bağlanırız? Yukarıda yazdığımız ASP kodunu birebir PHP’ye dönüştürecek olursak aşağıdaki gibi bir kodlamaya gideriz. mysql_connect('sunucu_adresi','kullanici','sifre'); mysql_select_db('db_adi'); $sorgu = mysql_query( "SELECT * FROM tablo_adi ORDER BY id DESC LIMIT 0, 10;" ); while( $kayitlar = mysql_fetch_array( $sorgu ) ) echo $kayitlar['alinacak_veri'] . ' '; mysql_close(); Yukarıda göreceğimiz gibi bağlantımızı mysql_connect() ile sağlıyoruz. Parantez içine sırasıyla sunucu adresi, kullanıcı adı ve şifresi yazılır. mysql_select_db() ile hangi veritabanını kullanacağımızı seçtikten sonra bağlantıyı sağlamış oluruz. Eğer birden fazla veritabanı ile işlem yapacaksanız mysql_select_db’den sonra bir de bağlantı için atayacağınız bir değişken kullanmalısınız. Sorgu cümlemizi mysql_query() komutuyla yazdıktan sonra kayıtları mysql_fetch_array ya da mysql_fetch_assoc ile alırız. Sıralama için kolaylık olması açısından while() döngüsü kullandık. Burada başka bir değişken olarak kayıtların depolanacağı arraylist (dize) yi belirttik ($kayitlar) ve finalde mysql_close() ile veritabanı bağlantısını kapattık. (NOT: Bir PHP belgesi mutlaka “ ” ile biterken, ASP belgesi “ ” ile biter. Kodlar ile ilgili alıştırma yaparken bunu dikkate almayı unutmayın.