Arrow

Posts on this site are never sponsored.

How to update multiple rows in mysql with php

Here comes my first geeky PHP post. For people who have basic knowledge in updating MySQL information through a PHP form, you’ll notice that almost all of the tutorials only tell you how to update ONE record at a time. Well, I figured it must be a simple tweak to update multiple records. Unfortunately, I had no knowledge of arrays (which are the keys to the solution) and ended up Google-searching for many hours.

Well, here’s the solution! It isn’t elegant, as I am quite the amateur coder, but it works and contains all the basics that you’ll need.

Note that there is no data validation in this script. For example, if you know that a field should contain integers, you should use something like intval($fieldvalue) when processing the form. Most text fields can be displayed with something like htmlentities($fieldvalue,ENT_QUOTES). If your server does not have the “magic quotes” featured turned on, text fields should be processed with something like mysql_real_escape_string($fieldvalue).

Let’s assume you’re updating book information about different courses (as I was trying to do).

Here is the file to display and edit the book information:

<?php
// connect to the database
mysql_connect(hostname,username,password);

// select the database
mysql_select_db(database) or die("Unable to select database");

// run the query and put the results in an array variable called $result
$result = mysql_query("SELECT * FROM table ORDER BY course");

// start a counter in order to number the input fields for each record
$i = 0;


// open a form
print "<form name='namestoupdate' method='post' action='update.php'>\n";

// start a loop to print all of the courses with their book information
// the mysql_fetch_array function puts each record into an array. each time it is called, it moves the array counter up until there are no more records left
while ($books = mysql_fetch_array($result)) {

// assuming you have three important columns (the index (id), the course name (course), and the book info (bookinfo))
  // start displaying the info; the most important part is to make the name an array (notice bookinfo[$i])
  print "<input type='hidden' name='id[$i]' value='{$books['id']}' />";
  print "<p>{$books['course']}: <input type='text' size='40' name='bookinfo[$i]' value='{$books['bookinfo']}' /></p>\n";


// add 1 to the count, close the loop, close the form, and the mysql connection
++$i;
}
print "<input type='submit' value='submit' />";
print "</form>";
mysql_close();
?>

Here is the file to update the information (called update.php as referred to in the previous file:

<?php
// connect to the database and select the correct database
mysql_connect(hostname,username,password);
mysql_select_db(database) or die("Unable to select database");

// find out how many records there are to update
$size = count($_POST['bookinfo']);

// start a loop in order to update each record
$i = 0;
while ($i < $size) {
// define each variable
$bookinfo= $_POST['bookinfo'][$i];
$id = $_POST['id'][$i];

// do the update and print out some info just to provide some visual feedback
// you might need to remove the single quotes around the field names, for example bookinfo = '$bookinfo' instead of `bookinfo` = '$bookinfo'
$query = "UPDATE table SET `bookinfo` = '$bookinfo' WHERE `id` = '$id' LIMIT 1";
mysql_query($query) or die ("Error in query: $query");
print "$bookinfo<br /><br /><em>Updated!</em><br /><br />";
++$i;
}
mysql_close();
?>

Access your custom MSN emoticons

Thumbs up emoticonSlap emoticonHeart emoticon

Frustrated emoticon

Got fun custom MSN emoticons that you want to use outside of the MSN environment? You can find them here (well, that’s where they are in Windows XP):

c:\Documents and Settings\yourusername\Application Data\Microsoft\MSN Messenger\yourMSNaccountnumber\CustomEmoticons

They have the extension .dat, so just copy them elsewhere and rename them to either .gif or .jpg and you’re good to go.
I would reference the webpage where I found this information, but I don’t actually remember what page it was.

Download the original Jezzball

The original Jezzball

Remember this game? I think I last saw it on Windows 95 (I could be wrong). Now it has all but disappeared. Anyway, try and eliminate space on the playing area without getting hit by the bouncing balls.

For those with Windows XP who are having troubles, it might be because XP has a built-in zip viewer. Try unzipping all of the files to a separate folder before running Jezzball.