Jump to content

SET and ORDER by name


dreamwest

Recommended Posts

I need to sort categories by  names ascending as well as set a new value based on the name order

 

table is "categories" columns are "sort_order" 1,2,3,4etc.... and "category_name"

 

here what ive come up with but its not right....im stumped

 

<?php

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("name") or die(mysql_error());

$result = mysql_query("SELECT * FROM categories") 
or die(mysql_error());

$sql = "UPDATE categories SET sortorder = NULL  WHERE category_name ORDER BY ASC ;";
mysql_query($sql) or die(mysql_error());



?>

Link to comment
Share on other sites

So you want to order them alphabetically?

 

Yes. and also number them starting at 1:

 

Example:

 

column name | columnn sort_order (currently set to 0 for all rows, but for this example i will number them showing what it will look like afetr update)

 

bananna      | 2

carrot          | 3

apple          | 1

Link to comment
Share on other sites

I guess you could use a variable or something...

 

 

@x = 0;

UPDATE table SET column = (@x:=@x+1) ORDER BY name DESC;

 

 

 

That might not work though....  I don't remember how MySQL variables work exactly.

 

Maybe this would work better:

 

mysql_connect("localhost", "user", "Pass") or die(mysql_error());
mysql_select_db("name") or die(mysql_error());

$result = mysql_query("SELECT * FROM category ORDER BY name ASC") 
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {

$sql = "UPDATE channel SET id='NULL' WHERE name='".$row['name']."' ";
mysql_query($sql) or die(mysql_error());
}
echo "done";

?>

 

But i cant seem to get it to count up from 0, ive set id to NULL

 

column "id" is the primary key with auto_increment and null = not null

Link to comment
Share on other sites

solved!

 

Just added counter variable to while loop:

 

while($row = mysql_fetch_array( $result )) {
$counter <= $row['id']; 
$sql = "UPDATE category SET id='".$counter."' WHERE name='".$row['name']."' ";
mysql_query($sql) or die(mysql_error());
$counter = $counter + 1;
}
echo "done";

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.