SET and ORDER by name

[dreamwest]

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());



?>

[corbin]

So you want to order them alphabetically?

[dreamwest]

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

[corbin]

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.

[dreamwest]

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

[dreamwest]

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";