URGENT! up2date -u deleted mysql...safest way to recover on production server

2008-05-04 Thread Ian M. Evans
Decided to run up2date -u I noticed that our website was tossing off mysql errors. Quickly realized that mysql was down. Went to restart but it couldn't find mysqld_safe, mysqld, mysqladmin, etc. I used locate and it couldn't find the binaries anywhere...it appears that up2date -u had

Re: URGENT! up2date -u deleted mysql...safest way to recover on production server

2008-05-04 Thread Ian M. Evans
Glyn Astill wrote: I'd back up the data directories then try and then re-install mysql (sorry, I know little about red hat and it's package management). It shouldn't overwrite your data if it's already present anyway. Great. Thought that would be the case, but without sleep, I wasn't sure.

Slow query not using index

2007-11-14 Thread Ian M. Evans
Bad news: I have a slow query that doesn't appear to be using an index even if I force it. Good news: the forehead shaped dent in my desk is really progressing well. Here's the query: SELECT DISTINCT poster_data.* FROM poster_data, poster_prodcat, poster_categories WHERE

Re: Slow query not using index

2007-11-14 Thread Ian M. Evans
One of the list readers (thanks Brent!) suggested using a full text index on the category names field. Queries dropped from 10-49 seconds down to 0.0085 Thanks for the emails folks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Need ideas on handling aliases and a.k.a.s

2007-10-19 Thread Ian M. Evans
I'm trying to wrap my head around dealing with people in a table that have multiple names or akas. I run an entertainment news site and have to deal with people like Pamela Anderson (who was once Pamela Lee, Pamela Anderson Lee, Pamela Denise Anderson), Eva Longoria (who's now Eva Longoria

RE: Weighted average

2002-07-16 Thread Ian M. Evans
What version of MySQL are you using? Our host, Interland, is running a pre-3.23 version. back, 3.23.x supports them), then just issue a similar query and store the result yourself... Should've thought of that myself. Thanks.

Weighted average

2002-07-15 Thread Ian M. Evans
Not wanting to reinvent the wheel here, I wonder if anyone else has ever done a rating system. There'll be two columns: title_id for the title of the movie being rated and then a rating from one to ten. What would be the most efficient SQL statement to find a title's weighted average? Many

RE: Weighted average

2002-07-15 Thread Ian M. Evans
[snip] What do you mean by weighted? Taking into account the number of votes for each and then weighting them against each other? [/snip] Yes, essentially each individual movie will show its raw average (people think this movie is a 7/10) while an overall top rated list will take into affect

RE: Weighted average

2002-07-15 Thread Ian M. Evans
-Original Message- From: Andrew K-C [mailto:[EMAIL PROTECTED]] you might be able to do a ORDER BY weighted DESC to get them to come out in order of weight Thanks...I'll give that a try... Hmm, the only problem with that query is that it doesn't get it quite

Re: Weighted average

2002-07-15 Thread Ian M. Evans
snip SELECT @totalVotes:=COUNT(titleid) FROM movieratings /snip Thanks for the reply. Alas, the above query syntax gives me an error 1064. -- Ian Evans Chairman Executive Producer Digital Hit Entertainment http://www.digitalhit.com

RE: WHERE-ing an alias...

2002-06-06 Thread Ian M. Evans
[snip] WHERE ap_type='Original Movie Poster' ORDER BY sort_title ASC HAVING sort_title LIKE 'G%' ORDER BY sort_title ASC You may have to swap the HAVING and ORDER BY lines of the query, [/snip] Thanks so much. There was a slight syntax change, replacing your first ORDER BY with a GROUP BY, but

WHERE-ing an alias...

2002-06-05 Thread Ian M. Evans
Ah, after I write my SQL SELECT query I discover that you can't use WHERE with an alias. Here's what I'm trying to do: SELECT posterdata.*, IF (aptitle LIKE 'The %',SUBSTRING(aptitle,5), IF (aptitle LIKE 'A %',SUBSTRING(aptitle,3),IF (aptitle LIKE 'An %',SUBSTRING(aptitle,4),aptitle))) AS

join dilemma

2001-11-04 Thread Ian M. Evans
I have a SQL statement that I use to grab movie profiles. When I constructed the input form, I decided I didn't want to use blank entries for tables that didn't have a entry for that particular film e.g. a poster wasn't available for sale. This SQL statement in PHP works fine when EVERY table

RE: join dilemma

2001-11-04 Thread Ian M. Evans
Thanks for your pointer. This SQL statement ended up working: Thanks for your pointer! This ended up working for me: SELECT * FROM profiles LEFT JOIN allposters ON (profiles.titlesid = allposters.titlesid) LEFT JOIN officialsites ON (profiles.titlesid = officialsites.titlesid) LEFT JOIN titles