Today I am going to show one more demo which is for to swap database table row like in Joomla for sorting or ordering a table.Here I am going to show you how to swap the id’s of rows of the particular table.
Basically, we are doing swapping to SWAP values of two variables with using a third variable so with the use this basic concept, I have created one demo which swap database rows with PHP coding.In order to switch the position of rows, you will need to set the Order(ASC, DESC) value of each row and then you can update rows with swap code.a
Let’s have a look into coding done by me so you get the idea how swapping of rows is possible.
Also READ: To use Flickr API with PHP
First of all create one table swap rows,
1 2 3 4 5 6 7 8 | CREATE TABLE `swaprows` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `FirstName` VARCHAR( 255 ) NOT NULL , `MiddleName` VARCHAR( 255 ) NOT NULL , `LastName` VARCHAR( 255 ) NOT NULL |
After creating table create one file swaprow.php and write a query to fetch records from the particular table you want to swap its row using MySQL functions.
1 2 3 4 5 6 | $connection=mysql_connect("localhost","username","password") or die("Database Path is not valid"); mysql_select_db("Database_name",$connection) or die ("Database not found"); $query="select * from swaprows order by id ASC"; $result=mysql_query($query); |
After select records from the database, It is a time to use records and for that you can create HTML as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | $cnt = 1; while($row=mysql_fetch_array($result)) { $last = mysql_num_rows($result); $cnt++; } <table width="397" border="0"> <tbody> <tr bgcolor="#999999"> <td width="261"> <div align="center">ID</div></td> <td colspan="2" width="261"></td> <td width="261"> <div align="center">First Name</div></td> <td width="261"> <div align="center">Middle Name</div></td> <td width="50"> <div align="center">Last Name</div></td> </tr> <tr bgcolor="#CCCCCC"> <td><?php echo $row['id'];?></td> <td><?php if($cnt != '1') { ?> <a href="validSwapID.php?previd=<?php echo $row['id']?>"> </a><?php } ?></td> <td> <?php if($last != $cnt) { ?> <a href="validSwapID.php?nextid=<?php echo $row['id']?>"> </a><?php } ?></td> <td><?php echo $row['FirstName'];?></td> <td><?php echo $row['MiddleName'];?></td> <td><?php echo $row['LastName'];?></td> </tr> </tbody> </table> |
Next is to create one file that is validSwapID.php and the main important thing is to create one function which swap the row.Here I have written one function row_swap which swap database:
Recommended READ:
To Unzip file and move to destination in PHP
To grab content from nested tags in PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | //To get Previous row id $pid = $_GET['previd']; //To get Next row id $nid = $_GET['nextid']; if(isset($pid)) { $query="select * from swaprows where id <'".$_GET["previd"]."' ORDER BY id DESC LIMIT 1"; $result = mysql_query($query); $row = mysql_fetch_array($result); row_swap('registration',$pid,$row['id']); } if(isset($nid)) { $query="select * from swaprows where id >'".$_GET["nextid"]."' ORDER BY id ASC LIMIT 1"; $result = mysql_query($query); $row = mysql_fetch_array($result); row_swap('registration',$nid,$row['id']); } function row_swap($table, $id1, $id2) { mysql_query("UPDATE $table SET id='999' where id='$id2'"); // 999 is the third variable for swapping a two variable values. mysql_query("UPDATE $table SET id='$id2' where id='$id1'"); mysql_query("UPDATE $table SET id='$id1' where id='999'"); } |
Here above code, You will get previd and nextid. Using previd and nextid find out the previous and next row from swap rows Table.And after fetching the id, call the row_swap function which will update the row into the table and your data will be updated.
That’s it. Now you are ready to swap rows from the database.Have a look at the working Demo here.
I hope you have understood the concept and it has been helpful to you.
Comments (3)