need help sorting table

jacko_162 · February 3, 2010

Im a PHP newbie really and i was wondering if anyone can assist me in a feature i would like to add to some code;

i have a simple "notes" style list that pulls information from a table;

DB TABLE:

-- 
-- Table structure for table `notes`
-- 

CREATE TABLE `notes` (
  `id` int(11) NOT NULL auto_increment,
  `content` varchar(500) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=5 ;

and here is the PHP source code to pull the information from database (sorry about the mixed in HTML!)

notes.php:

<div class="box box-75 altbox"><!-- .altbox for alternative box's color -->
				<div class="boxin">
					<div class="header">
						<h3>Notes List</h3>
                            <a class="button" href="addnote.php">Add Item »</a>
					</div>
					<div class="content">
						<table cellspacing="0">
							<thead>
								<tr>
									<th width="20"><div align="left"> ID</div></th>
									<td class="tc"><div align="left">Content</div></td>
								  <td width="50"><div align="right"></div></td>
                      <?
// Query to pull information from the "Notes" Database
$result = mysql_query("select * from $table6 order by id ASC");
while ($row = mysql_fetch_object($result)) {
?>
								</tr>
							</thead>
							<tbody>
								<tr class="first"><!-- .first for first row of the table (only if there is thead) -->
									<th width="20"><div align="left"><? echo $row->id; ?></div></th>
									<td class="tc"><div align="left"><? echo $row->content; ?></div></td>
									<!-- a.ico-comms for comment-like backgrounds -->
								  <td width="50"><div align="right"><a class="ico" id="tooltip" title="Edit This Item" href="editnotes.php?ID=<? echo $row->id; ?>"><img src="Includes/css/img/led-ico/pencil.png" border="0" alt="Edit This news" /></a> <a class="ico" id="tooltip" title="Delete This Item" href="remove.php?ID=<? echo $row->id; ?>&db=notes"><img src="Includes/css/img/led-ico/delete.png" border="0" alt="Delete This news" /></a>
							        <? } ?>
								    </div></td>
								</tr>
							</tbody>
						</table>
		</div></div>
</div>

basically i want to add a new field in the notes table to become "sort_order" then on the main notes.php page i want a small input field to display the current sort order which is editable with a small button at the bottom to "update sort orders" and to list the by sort_order ASC.

can anyone help me please or point me to where i need to look?

Many thanks.

New Coder · February 3, 2010

Why do you want to change the sort order? so that it displays in a sort of random order each time?

if so you can use rand for that:

select * from $table6 order by RAND()

jacko_162 · February 3, 2010

no i try to use the list to display the most important things at the top, at the moment its only displayed by the ID and this is an auto_increment so if i add something new it goes to the top, and i have other things i need at the top.

i usually edit it manually via phpmyadmin but i wanted it to be quicker and easier to edit, mainly from my iphone web browser.

New Coder · February 3, 2010

Why dont you add a field to you table called rank or something and give any new or important stuff the rank number as 1 and less important stuff a number 2. you could then have a query that looks like

select * from $table6 order by ID DESC, rank

that way it will display the most recent most important stuff.

To change the rank id on a web page you could have a change rank button next to each item.

and the change_rank.php could look like:

$rank = $_POST['rank'];

if ($rank == '1')

{

$sql = "UPDATE table_name SET rank = \"2\" WHERE ID = \"$ID\" ";

$rs = mssql_query( $sql, $conn )

or die( "error");

if($rs)

{

header( "Location:notes.php" ); exit();

}

else

{

$sql = "UPDATE table_name SET rank = \"1\" WHERE ID = \"$ID\" ";

$rs = mssql_query( $sql, $conn )

or die( "error");

if($rs)

{

header( "Location:notes.php" ); exit();

}

etc etc

you could also set your rank column to have the default value of 1 that way you never have to back door the data.

jacko_162 · February 3, 2010

Why dont you add a field to you table called rank or something and give any new or important stuff the rank number as 1 and less important stuff a number 2. you could then have a query that looks like

select * from $table6 order by ID DESC, rank

that way it will display the most recent most important stuff.

To change the rank id on a web page you could have a change rank button next to each item.

<form name=\"change_rank\" action=\"change_rank.php\" method=\"post\"><input type=\"hidden\" name=\"rank\" value=\"".$rank."\"><input type=\"submit\" value=\"Change Rank\"></form>

and the change_rank.php could look like:

$rank = $_POST['rank'];

if ($rank == '1')

{

$sql = "UPDATE table_name SET rank = \"2\" WHERE ID = \"$ID\" ";

$rs = mssql_query( $sql, $conn )

or die( "error");

if($rs)

{

header( "Location:notes.php" ); exit();

}

}

else

{

$sql = "UPDATE table_name SET rank = \"1\" WHERE ID = \"$ID\" ";

$rs = mssql_query( $sql, $conn )

or die( "error");

if($rs)

{

header( "Location:notes.php" ); exit();

}

}

etc etc

you could also set your rank table to have the default value of 1 that way you never have to back door the data.

sounds promising, i will have a play with it now and see what i can come up with, i could always unhide the input fields and get it so i can customise it?

jacko_162 · February 3, 2010

i tried your method and it kinda works..

but its not 100% how i wanted it, i need to be able to list it better.

here is what i wanted the final table to look like;

<table cellspacing="0">
							<thead>
								<tr>
									<th width="20"><div align="left"> ID</div></th>
									<td class="tc"><div align="left">Content</div></td>
								    <td width="50">Sort Order</td>
							        <td width="50"> </td>
							        <?
// Query to pull information from the "Notes" Database
$result = mysql_query("select * from $table6 order by id ASC");
while ($row = mysql_fetch_object($result)) {
?>
								</tr>
							</thead>
							<tbody>
                                <form class="fields" id="form" action="sort_order.php" method="post">
								<tr class="first"><!-- .first for first row of the table (only if there is thead) -->
									<th width="20"><div align="left"><? echo $row->id; ?></div></th>
									<td class="tc"><div align="left"><? echo $row->content; ?></div></td>
									<td width="50"><input class="txt" name="name" type="text" value="<? echo $row->sort_order; ?>" size="6" /></td>
									<!-- a.ico-comms for comment-like backgrounds -->
								  <td width="50"><div align="right"><a class="ico" id="tooltip" title="Edit This Item" href="editnotes.php?ID=<? echo $row->id; ?>"><img src="Includes/css/img/led-ico/pencil.png" border="0" alt="Edit This news" /></a> <a class="ico" id="tooltip" title="Delete This Item" href="remove.php?ID=<? echo $row->id; ?>&db=notes"><img src="Includes/css/img/led-ico/delete.png" border="0" alt="Delete This news" /></a>
							        <? } ?>
								    </div>
								  <input class="button altbutton" type="submit" id="submit" name="submit" value="Update Order" /></form></td>
								</tr>
							</tbody>
						</table>

and now my db table looks like;

CREATE TABLE `notes` (
  `id` int(11) NOT NULL auto_increment,
  `content` varchar(500) collate latin1_german2_ci NOT NULL,
  `sort_order` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `notes`
-- 

INSERT INTO `notes` VALUES (18, 'test1', 1);
INSERT INTO `notes` VALUES (12, 'test2', 2);
INSERT INTO `notes` VALUES (8, 'test3', 3);
INSERT INTO `notes` VALUES (1, 'test4', 4);

jacko_162 · February 3, 2010

ok after a bit of searching i came up with: arrays

lol

ok so here is my current code, it pulls information from $table6 just fine, when i click submit it loads up the same .php file instead of running command "Location:notes.php"

<?php require_once('Includes/connect.php');?>
<?php

$sql="SELECT * FROM $table6";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Content</strong></td>
<td align="center"><strong>Sort Order</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="content[]" type="text" id="content" value="<? echo $rows['content']; ?>"></td>
<td align="center"><input name="sort[]" type="text" id="sort" value="<? echo $rows['sort']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $table6 SET content='$content[$i]', sort='$sort[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}
if($result1)
   {
   header( "Location:notes.php" ); exit();
   }   
?>

can anyone spot anything im doing wrong here??

jacko_162 · February 3, 2010

can anyone help me out im lost when it comes to making arrays and loading them up,

the above code makes the array and grabs information from the database, upon submit it changes the values but doesnt reload the page or other pages in the location: part..?!

dmikester1 · February 3, 2010

Your form is not submitting to anywhere. You need to put something in the action I believe. If you want to submit to the same page use: action="<?php echo $_SERVER['PHP_SELF']; ?>"

jacko_162 · February 3, 2010

still no luck.... say if i enter a new sort order number, and click submit it kinda refreshes the page but doesnt update to the new number.

yet it seems to be updating the database, but also doesnt forward me to notes.php

i tried to set the form action to PHP_SELF with no joy either.

anyone else help a poor guy out.. spent all day trying to solve this mystery..

jacko_162 · February 3, 2010

here is a link to my test file;

http://www.real-creative.co.uk/Development/auth/test2.php

hope that can show you what its doing.

here is code for test.php;

<?php require_once('Includes/connect.php');?>
<?php
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$database")or die("cannot select DB");

$sql="SELECT * FROM $table6";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
?>
<table width="500" border="0" cellspacing="1" cellpadding="0">
<form class="fields" name="update" method="post" action="<? echo $PHP_SELF ?>" enctype="multipart/form-data">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0">


<tr>
<td align="center"><strong>Id</strong></td>
<td align="center"><strong>Content</strong></td>
<td align="center"><strong>Sort Order</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><? echo $rows['content']; ?></td>
<td align="center"><input name="sort[]" type="text" id="sort" value="<? echo $rows['sort']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="3" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $table6 SET sort='$sort[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:notes.php");
}
mysql_close();
?>

Sign In

need help sorting table

Recommended Posts

jacko_162

Link to comment

Share on other sites

New Coder

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

New Coder

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

dmikester1

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information