fenway

Why the collation stuff?

This is what's known as a "transaction" -- MyISAM tables don't support this, but InnoDB (and others) do. If anthing "inside" fails, you simply rollback, and then it's like it's never happened (though you still need to handle user notification). Inserting won't "fail" unless there's a DB error (e.g. server crashes), you're statement isn't valid (shouldn't ever happen on a production server), etc....

Then you should have a field called "date_added", store NOW() in their when you run the insert, and then order by date_added ASC when you issue your queries.

Well, if you take Barand's example, and drop the group concat, you'll get something quite close... you can use this to join back to the original table.

As long as you have root access, you can update the users table.

Just add a paramater that you change daily (like the date): src=http://..../image1.gif?nocache=20071205 Whenever you change this param, the browser will be forced to get the new image.

There's no "beginning" or "end"... records are simply in a mathematical set, a grouping of sorts. Ask for your data sorted however you'd like. Why do you care how mysql stores the data internally?

It's not broken, there's nothing to fix -- you had rows there with the UIDs in between, then you deleted them... so what? Leave this number alone, nothing outside the application should ever see it, and you should never be exposing it.

Obviously that would be quite the performance hit, though in principle you could do it memory (though maybe not for a 10k set). I guess you could have a trigger update such a summary table all the time.... The real issue is that it's non-trivial to convert column values into "fields".

Hmm... how about making a temporary table before you're going to run the report -- i.e. once all of the possible fields are known -- and then simply inserting/updating each row from the existing name/value table? That is, with insert into... on duplicate key update.... ?

Post the actual query... I can't guess why it's not working.

You can't call your table "table".

If that's the case, then just run the nested replace()... that's the best you can do... unless you write a UDF and run it on insert/update and store the cleaned value in another field.

Acutally, you're doing in the hard way... but I won't say I told you so.

Shouldn't be too much legwork, you're just building a hash. And I don't know that I'm convince that it's "required"... but work with what you've got.

You'd still have to list all 15 old/new values... there is no "real" solution, mysql wasn't meant to do string manipulation. Why do it in mysql anyway? HTML Tidy can handle this for you.

It will display zero counts for any genres that are in the table... it doesn't know what "all" means... you could work around this, by why?

If only mysql supported proper regex capturing parentheses... in the meanwhile, you'll have to nest your replaces: replace( replace( col1, 'old', 'new' ), 'old2', 'new2' )

Just be careful that it doesn't match part of a larger string by accident.

It will replace all instances of this matching string.

You need "GROUP BY shops.id, genre.id".

Give specific examples.

They're all in the same column?!?! Oh boy.... you need to look into normalization; take a look at the relevant post that I've stickied. You'll need another table.

Name/value tables cause all sorts of problems -- this is one of them. It would be quite difficult -- and extremely inefficient -- to have mysql generate the result you're interested in. These types of tables are explicitly desgined for cases where you're earching by a known name, not trying to collect them all. The latter needs to be done in application code. In the boundary case, you'd only have a single table in your database with every field as a name... this is extreme denormalization.

You can't have everything "on its own row" and use GROUP_CONCAT().