there's a database. It's not very complicated, it has two collumns, like this:

 

UID | Some Text

 

That's all. there's a few thousand records, but the first 20 records have been removed, meaning that the UID column starts at 21. Is there a way to automatically renumber the rows so that they start at 1 again? it's not really that important, and certainly not important enough to do it by hand, but if there's a simple SQL "MAKE THESE NUMBERS OCD COMPLIANT" command that'd be sweet.

update table set UID = UID - 20 ?

If the UID is auto-incrementing you could do something like this:

CREATE TABLE new_table LIKE original_table;
 
INSERT INTO new_table (text_column) SELECT text_column FROM original_table;
 
RENAME original_table TO backup_table;
 
RENAME new_table TO original_table;

This will reset the auto-increment to the next real value, so the next record inserted into it won't be the max + 1 of the old table.

So simple (fail)

 

I'm helping out a mate who's trying to fix some stuff for his work (their "IT guy" did a runner with the bosses wife :'D ). There was a very nervous pause as the machine froze up for about 20 seconds while it updated the 60-odd thousand rows.

 

THANK YOU PAETER!

Did you log into the right account? I'd normally expect yours and PBs answers to be the other way round. The simple method worked grand!

I almost gave Matt's answer instead/as well, but something made me say "fuck it" and just give the short one. :)

Kenny must be tweaking your code.

 

I am now trying to learn some basic SQL super-quick, and then plug it into some PHP. I really should learn to not offer to help people, especially when you don't have a clue what you're doing :S

Well I don't know if it simplifies things or makes them more complicated, nor how well supported it might be with PHP, but in the CFML world a lot of people have been getting into ORM (e.g. Hibernate), which is supposed to let you avoid writing the majority of SQL, at least for simpler applications.

I don't really do direct database stuff at the moment, so haven't bothered learning much about it, but it might be worth investigating in case it can save you time/effort.

Probably not worth the effort, as it's only a handful of queries/pages that need fixing.

 

It's basically selecting 6 random rows from the database and then printing them out one at a time at various points on a webpage.

 

I think I've almost got it sorted, not knowing what you're doing really makes it quite interesting though :D

Heh, that's almost what I've been working on recently, though my stuff selects random files from a directory instead - much nicer to work with. :)

Disadvantage of Pete's is that if it's an auto-increment it will continue from the old maximum value, not from the next available one.

If you had records:

10 Hello,
11 World

And you used Pete's code to change them to:

1 Hello
2 World

The next value you insert into the table will have the UID of 11 and not 3.

I noticed in PHPMyAdmin that you can reset the auto-increment counter, would taht solve that? Or would it make things start at 0 again :S

Depends if you can reset it or change it to a specific value.

It's change, not reset. I've changed it to 1 after the current last entry, hopefully that's it sorted.

Anyone want to help me with this? And by help, i mean tell me exactly what to type :$

 

I have a table with around 100 entries, each one is a vetted customer testimonial type thing and the data isn't likely to change/grow much.

 

What I need is:

 

Select 6 of them at random, then they'll be displayed at various points on a page.

 

I've got an SQL query working for it (I know it's not the most efficient/fastest way of doing it, but it seemed the most straightforward way to get 6 unique random rows):

 

SELECT text FROM `content` ORDER BY RAND() LIMIT 0,6

 

Now how do i get that into an array so I can print each row result individually? All the stuff online seems to be about finding individual rows and adding them to an array, not finding 6 rows and adding each row to an array.

 

Or should I bite the bullet and learn some PHP so I can do it myself? Just sees like a waste as I'm unlikely to ever need it again after this :$

Learn some PHP - you would have also said you're unlikely to ever need it before finding out about this, right?

If "all the stuff online" is telling you how to deal with single rows, then you're not looking at all the stuff (or are misunderstanding it, thus learning a bit would be beneficial).

In any case, Stack Overflow has this question asked/answered several times. Like here.

But why learn when I can get you to find the answer for me?! (kiss)

Your solution doesn't work :(

You must be doing it wrong.

$query = mysql_query("SELECT text FROM `content` ORDER BY RAND() LIMIT 0,6");
$hooray = array();
while($result = mysql_fetch_array($query)) {
    $hooray[] = $result;
}
 
print_r($hooray);

(last line just prints the array)

Ah-hah. The problem is with how I'm trying to print the array elements.

 

If I use "print" it just outputs "Array". print_r prints the content, but does it like this:

 

Array ( [0] => The service was excellent. [text] => The service was excellent. )

 

How on earth do i just get the bit of text I need out of it?! This shit was so easy in QBASIC :(