Re: Maybe a bit OT: MySQL forum recommendation?
At 19:19 -0500 3/12/07, David T. Ashley wrote: You might check out vBulletin. They might waive the license fee if you ask nicely, or they may also have a policy when it is in support of open-source software. Hmmm - thanks for the suggestion, but this is strictly speaking a commercial site, albeit an extremely low budget one! I'll go for the free options first, I think. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Half this game is ninety percent mental. -- Philadelphia Phillies manager, Danny Ozark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Customer Survey :: an exercise in frustration
At 13:19 -0800 3/12/07, Jeremy Cole wrote: I just finished it. There were 56 questions, not counting the final give us your name stuff for the prize. Most of the questions were mandatory. This seems to be a fill out the form type of survey system rather than a self-adjusting system based on the answers to your previous questions. Many of the questions asked were for things I had already said I didn't use... I didn't have any intention of doing this survey, but my interest was piqued by this little firestorm that's flared up around it. I was curious to have at least a quick look at it, so I did this... To take this survey (please note that Zoomerang is the tool we use for our surveys), please go to: http://www.zoomerang.com/survey.zgi ...which is immediately redirected to the rather incomplete looking http://www.zoomerang.com/survey-start.zgi?p= which, sure enough, simply returns the message: 'Cannot take survey, invalid URL, please check that the URL is correct and try again.' This has happened in two different browsers on my Mac (OmniWeb and Firefox) and that's enough for me - and I'm /definitely/ not firing up my Windows box just for this purpose! It does all seem a little unsatisfactory, doesn't it? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Anyone who is disturbed by the idea of newts in a nightclub is potentially dangerous. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maybe a bit OT: MySQL forum recommendation?
My apologies in advance if this is a bit off topic, but... On a rather old site we have a dreadful old bulletin board system based on Matt Wright's WWWBoard - all horrid text files and ancient Perl code. We want to replace that with a decent forum system based on MySQL and PHP, but there's no money so it'll have to be a free one. We also need to be able to roll it into our existing database by adding more tables, rather than adding a separate database. Naturally, the easier it is to set up the better. And the real icing on the cake would be to find a script for converting between WWWBoard and whichever one we choose, though I suspect that might be too much to ask. I should be able to roll my own converter script if need be. The host we're using has PHP 4.3.10 and MySQL 3.23.56, so that rules out some I've found, but these seem promising from a quick look: FUD Forum miniBB phpBB PunBB SEO-Board Simple Machines Forum (SMF) Vanilla Any recommendations from among these or others? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Anyone who is disturbed by the idea of newts in a nightclub is potentially dangerous. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to find foo within (foo)
At 19:34 +0200 19/9/07, thomas Armstrong wrote: I've got this table in mySQL: item 1: -- firstname: John (Johnie) -- phone: 555-600-200 item 2: -- firstname: Peter -- phone: 555-300-400 I created this SQL query to find 'johnie': SELECT friends.id FROM friends WHERE ((friends.firstname LIKE '% johnie %' OR friends.firstname LIKE 'johnie %' OR friends.firstname LIKE '% johnie' OR friends.firstname LIKE 'johnie' OR friends.phone LIKE '% johnie %' OR friends.phone LIKE 'johnie %' OR friends.phone LIKE '% johnie' OR friends.phone LIKE 'johnie')) ORDER BY friends.firstname LIMIT 0, But it doesn't match anything, because it's considers (johnie) as a single word. Is there any way to make mySQL consider (johnie) as johnie. Well I'm hardly the world's greatest expert, but I'm curious as to why you're always separating '%' from 'johnie' with a space, because that way it will only find Johnie if he has a space before or after him or both. Hmmm... and why the double parentheses? In fact, why any parentheses at all? This oughta do it: SELECT friends.id FROM friends WHERE friends.firstname LIKE '%johnie%' ORDER BY friends.firstname LIMIT 0, That should find 'johnie' or 'Johnie' with absolutely any characters before and/or after him. ... and if you want to simplify your queries as much as possible you don't need to specify the table every time unless ambiguities might arise (which they only will if there's more than one table involved), so try: SELECT id FROM friends WHERE firstname LIKE '%johnie%' ORDER BY firstname LIMIT 0, ... and unless you've really got more than friends that limit clause is redundant too. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't fetch result twice in a PHP script
At 16:35 -0500 15/9/07, Mahmoud Badreddine wrote:
In one of my php scripts I make 2 successive calls of mysql_fetch_row using
the same Mysql Result.
In the first call the desired result is achieved, but in the second one it
isn't.
I have something like
while($someArr=mysql_fetch_row($result))
{
...some code.
}
The first time I call mysql_fetch_row , the code inside the while loop gets
executed, but not the second time. What is the reason behind that.
Sounds more like a PHP question really, but have you thought about
trying mysql_fetch_array() instead? I can't see why it should be any
different, but it might be worth a shot - always works for me anyway.
:-)
But also: are you confident there's more than one row to be fetched?
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
He who slings mud, usually loses ground.
-- Adlai Stevenson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 13:34 -0400 10/9/07, Baron Schwartz wrote: Looks like you've found the solution you need. The only other suggestion I have is to use UNION ALL if you don't need to eliminate duplicate rows in the UNION, because there's some overhead for checking for them. Hi Baron Thanks for this, and I did try it, but the difference in time taken to execute the query was negligible (I tested it multiple times) - it was around 0.02 seconds whichever way I did it, and when I used EXPLAIN, the results were identical except for one detail: The number of rows in the first row of the EXPLAIN result was lower with plain UNION than if I used UNION ALL. As far as I can tell from my relatively limited experience with all this, the first row refers to my outer 'wrapper' select from the derived table (the table in the first row is given as 'derived2' and the Extra column shows 'Using temporary'). For a given query, with UNION ALL that has 45 rows, with UNION it's 31. So I guess I'll stick to plain UNION. As far as my desire to cope with multiple search terms is concerned, I realise now that fulltext handles that anyway! So I've changed the few non-numeric fields that weren't indexed that way (fore, sur and topic) to fulltext and bingo! Not only that, but it all happens fully FOUR TIMES as quickly! So many thanks, Baron - mainly due to you, yesterday was a very good MySQL day for me. It's not often I get two 'lightbulb moments' on the same day! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Good people will do good things, and bad people will do bad things. But for good people to do bad things - that takes religion. -- Steven Weinberg, physicist and Nobel Laureate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Big SELECT: ordering results by where matches are found
I'm sure there must be an accepted technique for this, but it's something I haven't tried before, so if anyone can point me in the right direction I'd be grateful. I'm writing a search facility for a site where the data is stored in several tables - let's say 5 for this example - and I want to order my results according to where (if anywhere) matches are found. So... Let's say I have tables 'speakers', 'topics', 'speakers_topics', 'articles', 'other'. 'speakers' is a table of speakers, with id, name and some text fields. 'topics' is a list of topics they address 'speakers_topics' relates the above two by pairs of id numbers 'articles' and 'other' are further tables of text data with possibly more than one row for some speakers, identified by id. I want to search the data in the following order: name from 'speakers' topics text data from 'speakers' text data from 'articles' and 'other' ...and order the results according to where in that hierarchy a match is found. So, if the user's search term matches one speaker's name field, another's topic and someone else's text data, that's the order in which the results should be ordered. Also, if the same person is matched from, say, both name and text fields (which is very likely, as their name will almost certainly appear in some of the text), the name should take precedence in the ordering. To complicate matters further, I'd like if possible to extend this to an and/or situation. If the user enters two or more words, any results that match all the words should be ordered above those that match only some of the words. I can probably do this relatively easily with a series of separate queries (I'm doing all this from PHP, by the way), but that strikes me as inefficient. Can it all be done in one big query, perhaps with subqueries? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Revolution: an abrupt change in the form of misgovernment. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 11:01 -0400 10/9/07, Baron Schwartz wrote: I've built similar systems with a series of UNION queries. Each UNION has a column for relevance, which can be a sum of CASE statements, such as IF(name matches, 1, 0) + IF(text matches, 1, 0) AS relevance... The entire UNION can then be ordered by relevance. You could also just add in an arbitrary number in each UNION, to get the effect of ordering by where in the hierarchy the match is found. Oo-er. This sounds marvellous, and I /think/ I see what you're getting at, but it's a bit beyond anything I've done before - never used UNION for instance. Can you perhaps go into a little more detail? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The nice thing about standards is that there are so many of them to choose from. -- Andrew S. Tanenbaum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 11:01 -0400 10/9/07, Baron Schwartz wrote:
The entire UNION can then be ordered by relevance. You could also
just add in an arbitrary number in each UNION, to get the effect of
ordering by where in the hierarchy the match is found.
Actually, your pointing me towards UNION may have done the trick. I
read up on it on the MySQL docs site and I've ended up with this,
which actually covers more tables and fields than in my original post:
---
select distinct tb.speaker_id, tb.fore, tb.sur, tb.division from
(
(
select 1 as relevance, speaker_id, fore, sur, division
from speakers
where fore like '%education%' or sur like '%education%')
union
(
select 2 as relevance, s.speaker_id, fore, sur, division
from speakers s, speakers_topics st, topics t
where st.speaker_id = s.speaker_id and t.topic_id = st.topic_id and
topic like '%education%'
)
union
(
select 3 as relevance, speaker_id, fore, sur, division
from speakers where match (strap, shortbio, longbio) against ('education')
)
union
(
select 4 as relevance, s.speaker_id, fore, sur, division
from speakers s, articles a
where s.speaker_id = a.speaker_id and match (title, article) against
('education')
)
union
(
select 5 as relevance, s.speaker_id, fore, sur, division
from speakers s, other o
where s.speaker_id = o.speaker_id and match (title, article) against
('education')
)
union
(
select 6 as relevance, speaker_id, fore, sur, division
from speakers, books
where speaker_id = author and match (title, description) against ('education')
)
order by relevance, division, sur, fore
) as tb
---
First, I did it without the outer select, and I got speakers repeated
if they were matched in more than one block. One of the comments on
the MySQL docs site suggested the 'wrapper', which I did initially
like this:
select distinct speaker_id, fore, sur, division from... with nothing
after the final ')'. This gave me an error to the effect that derived
tables must always have an alias. What the hey, let's just try it
like this (the above)... and to my astonishment it worked!
So before I sign off on this thread, can you see any way I could improve this?
Naturally, I haven't yet incorporated the treatment of more than one
search term, but I'll try and work that out for myself. :-)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Justice is incidental to law and order.
-- J. Edgar Hoover
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Database from PC to Apple
At 15:01 -0700 7/10/06, David Blomstrom wrote: Thanks. Is this something I can do through phpMyAdmin? Yes! Easy: First, create the database - just the database, no tables or anything - on the Mac. Next, go to the database on the PC in phpMyAdmin and without selecting a table in the sidebar, click the Export tab. In the 'export as' part of the page (which varies wildly between phpMyAdmin versions), SQL is probably selected as the default. If so, leave it; if not, select it. Also make sure both Structure and Data are selected in the 'what to export' part. Then click the Go button. This will display all your database as SQL commands in text format. Select All, copy it into a text file and save it. Warning: in some older versions of phpMyAdmin, you'll get a line saying something like 'Database xxx running on yyy' at the top, as an html h1, above the comment lines starting with #. This line will be included in the 'select all', so you'll have to delete it before you import... which comes next: Having transferred the text file to your Mac, go into phpMyAdmin, to the new database you have created, and click SQL among the tabs along the top. If there's anything in the textarea that appears, delete it, then copy and paste the entire contents of the text file into there (tip: if you get hold of the OmniWeb browser for Mac OS X, you can open a textarea into a nice big editing window, which will make it easier to see what you're doing). Click Go and, to quote the immortal Mr Jobs, 'Boom! You're done.' -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Outside of the killings, Washington has one of the lowest crime rates in the country. -- Mayor Marion Barry, Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many open processes??
At 17:37 -0700 7/10/06, Cabbar Duzayak wrote: I am using mysql_pconnect from PHP to connect to our mysql server. ... Is there a way to configure mysql so that it will kill a process after a certain period of idle time, just like Apache does? I may be barking up the wrong tree here, but as I understand it (which is hazily :-) ), mysql_pconnect creates a persistent connection, which may not be what you want. Try plain mysql_connect instead and see what happens. It's what I always use and it's never caused any problems, but then I only deal with small databases (certainly compared with some on this list!)... -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ It isn't necessary to imagine the world ending in fire or ice - there are two other possibilities: one is paperwork, and the other is nostalgia. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fields separators
Pe 27 Sep 2006, la 10:29, Chris [EMAIL PROTECTED] a scris:
Did you try using a space (' ') as the separator? Did you get an error?
And at 7:41 + 27/9/06, [EMAIL PROTECTED] wrote:
Yes, I did.
...
So, I need to specify somehow that the fields are delimited by any
number of spaces...
One answer of course is grep. However, as far as I can determine,
MySQL can only apply grep in the context of a LIKE clause, So...
Do it on your text file before importing, if you have a text editor
that can handle regular expressions. Just search for / +/ and replace
with ' '. Then import using a single ' ' as the 'enclosed by' string.
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Whenever I watch TV and see those poor starving kids all over the
world, I can't help but cry. I mean I'd love to be skinny like
that, but not with all those flies and death and stuff.
-- Mariah Carey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Requesting help with subquery
At 11:40 -0400 26/9/06, Zembower, Kevin wrote: IF(ISNULL(SELECT lv.langversionid FROM langversion AS lv JOIN baseitem AS b3 ON lv.baseitemid = b3.baseitemid WHERE lv.langid = 1 AND b.baseitemid = lv.baseitemid )), 'Y', 'N') AS Lang Avail Looks to me as if your parentheses don't balance here - you have an extra ')' in that last line. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I once preached peaceful coexistence with Windows. You may laugh at my expense - I deserve it. -- Jean-Louis Gassé, former Apple executive ( Be CEO) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A censor is a man who knows more than he thinks you ought to. -- Laurence J. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: anyone using subversion to sync mysql dbs ?
No, I don't generally go along with underhand political activity. :-) (but I expect that's an old joke - I haven't been MySQLing all that long, you see...) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Any inaccuracies in this index may be explained by the fact that it has been sorted with the help of a computer. -- Donald Knuth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Probably naive update question
I have a field representing the chances the user has to get a password right, which is initially 3. I would like, if possible in a single query, to be able to decrement it if it's still 0 and return the value. Something like this: UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx SELECT chances FROM table WHERE id = xxx Is there some tidy way to do that with, say, a subquery (something to which I'm still quite new, having been stuck with MySQL 3 until recently)? I don't even know for certain that I have the IF syntax right, but I think I have. I'm using MySQL 5, btw. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Never trust a man who, when left alone in a room with a tea cosy, doesn't try it on. -- Billy Connolly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Probably naive update question
At 8:49 -0500 9/8/06, Dan Buettner wrote: Chris, I'm not aware of a way to use ordinary SQL (insert, update) for this, but the use of a stored procedure would work for you. I've not done it with MySQL (never had a need) but did things like this extensively with Sybase. In rough terms: CREATE PROCEDURE sp_chances(IN xxx INT) RETURNS INT BEGIN UPDATE table SET chances = IF(chances 0, chances - 1, 0) WHERE id = xxx; SELECT chances FROM table WHERE id = xxx; END; Then you would execute this SQL: CALL sp_chances(xxx) and it should return the number of chances left for user id xxx, having decremented the counter as well (if 0). Hi Dan Thanks for this, but it's clear to me that all the stored procedure is doing is running the two queries I was running otherwise. It would save me a couple of lines of PHP code, but is it really any more efficient? Especially as this will be on a /very/ small database (at least, by the standards of some of the people on this list!) and won't happen particularly often. It'll only get called of the user doesn't get the password right first time, which most of them will do - and there won't be many anyway, at least not at first. What I was really hoping for was some equivalent of mysql_insert_id(), but returning some other value from the last query. Not to worry - two quick queries it is... or maybe it would do me good to start learning about stored procedures. :-) Thanks again! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ 10 percent of computer users are Mac users, but remember, we are the top 10 percent. -- Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by
Yes, I have looked at the docs and can't find what I'm looking for. I'm doing a very simple query: SELECT [fields] FROM [table] WHERE id IN (id1,id2,id3...) Is there a way to return the results in the order they appear in the IN list? I'm sure there's something obvious and simple, but as a relative novice... you know how it is. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Justice is incidental to law and order. -- J. Edgar Hoover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by
At 1:00 +0200 4/8/06, Johan Höök wrote: what you can do is: SELECT [fields] FROM [table] WHERE id IN (id1,id2,id3...) ORDER BY FIELD([field],value1,value2,value3,...) Ooh - so I can. I didn't know that wrinkle for order by - though I did wonder if something like that should be possible. Thanks very much! Problem solved. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Consider for a moment any beauty in the name Ralph. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with WHERE clause
At 11:10 +0200 30/7/06, Jørn Dahl-Stamnes wrote: select s.id,s.name,sum(p.fee) as fee from serie as s inner join race_serie as rs on (rs.serie_id=s.id) inner join races as r on (r.id=rs.race_id) inner join participants as p on (p.race_id=r.id) where s.receipt=1 and p.rider_id=236 and fee 0 group by s.id order by s.f_date; which gives me the error: ERROR 1052 (23000): Column 'fee' in where clause is ambiguous It looks as if you already have a column called fee in the participants table - I'm looking at sum(p.fee) - so you're in trouble if you use the same name for the sum: sum(p.fee) as fee, then MySQL doesn't know which 'fee' to look at for fee 0. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Remember there's a big difference between kneeling down and bending over. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
At 17:31 -0700 23/7/06, [EMAIL PROTECTED] wrote: The logic is that it follows the natural spoken format, i.e. July 23, 2006; which became the written standard; which... Hmmm. Is 'July the 23rd, 2006' any more natural to say than 'the 23rd of July, 2006'? I think we probably say either, equally. Sorry - I don't buy that. :-) Myself: I've never let local standards stand in the way of my using international ones. Excellent. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ To see tomorrow's PC, look at today's Macintosh -- Byte 1995 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
At 15:43 +0100 23/7/06, John Berman wrote: I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ Why? If you're storing the date in this format you can only be storing it as a string (char, varchar or text), so no wonder you're having trouble with it, when MySQL has a perfectly good date storage type in the form -mm-dd. I want to display all records for 7 days only from their approved date Assuming you've changed the way you store your dates: SELECT * FROM submissions WHERE DATE_ADD(approvedate, INTERVAL 7 DAY) = NOW () For what it's worth, the standard American date format of mm/dd/ has always mystified me, as it's the least logical possible way to do it. The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. Over here in Europe we at least use dd/mm/ (increasing unit size order), which is the next most logical, but to start with the middle-sized unit, put the smallest unit in the middle and end with the largest is just... weird! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Marriage has driven more than one man to sex. -- Peter de Vries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting TEXT to BLOB with special chars
At 13:40 +0200 18/7/06, Mike van Hoof wrote: Well.. gonna try some text-converting in php then... And yeah, it really needs to be a blob field... not my choice, but we got a CMS which operates on field types from MySQL... and a blob field wil generate an WYSIWYG field... but i think i am going to have a chat with my boss... You could, it seems to me, get round all this by doing everything, from soup to nuts, in utf-8. Is there a reason why it can't all be in utf-8? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ ...but discriminating against Windows users - isn't that prohibited under the Americans With Disabilities Act? -- Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting TEXT to BLOB with special chars
At 14:16 +0200 18/7/06, Mike van Hoof wrote:
And for the everything in utf8... will try this next time i get this
kind of a problem...
When you do, you'll need to send out the proper header from your php scripts:
header ('Content-type: text/html; charset=UTF-8');
and put this in your MySQL connect script after connecting to the db:
mysql_query (set names 'utf8');
or, better:
if (!mysql_query (set names 'utf8')) {
// handle the error
}
I hope that helps!
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Computers in the future may weigh no more than 1.5 tons.
-- 'Popular Mechanics', 1949
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date comparisons
I've found something that works (in MySQL 5, anyway), but I don't know whether it's accepted practice. If I want to find all records with a date in, say, March 2006, it works if I use datefield like '2006-03%' because it's a string. This seems kind of obvious and a lot tidier than doing datefield = '2006-03-01' and datefield = '2006-03-31', but are there pitfalls I should know about? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Is there something that sticks out that makes you an exceptional pole-vaulter? -- Adrian Chiles (to Sergei Bubka), BBC Radio 5 Live -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order desc problem
At 20:27 +0800 9/7/06, M B Neretlis wrote:
the order comes out of sequence showing 10.11.12.13 etc before the number 2---
Can anyone help me out
?php
//get user tips
$query = @mysql_query(SELECT * FROM tips WHERE user_id = $user_id
AND comp_id = $comp_id ORDER by round DESC);
while ($result = @mysql_fetch_array($query)) {
?
Coo - something I actually know!
What column type is round? I bet it's a varchar or some other
non-numeric type. If I'm right, it's sorting lexically, so 1 comes
before 11, comes before 2, etc.
Change it to a some flavour of int and it should work.
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
But what ... is it good for?
-- Engineer at the Advanced Computing Systems Division of IBM,
commenting on the microchip, 1968
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting unique values
At 22:04 +0100 28/6/06, I wrote: I have two tables: pix and sections, the relevant bits of which are: pix (2,421 rows): picid varchar(7) not null sectionid smallint(5) unsigned not null caption text null picid and sectionid are a joint primary key caption is full text indexed sections (a mere 152 rows): sectionid smallint(5) unsigned not null title varchar(63)not null blurb text null sectionid is primary key (auto increment) title and blurb are full text indexed ... I want to find the first instance of each picid that matches the text anywhere in caption, title or blurb, and get some other info at the same time. Oh, and for the time being it needs to be possible in MySQL 3.23.x. Solved! (Just as well as no-one replied :-) ) Quite by chance, Tanner Postert's question about the same time had the answer for me: At 14:27 -0700 28/6/06, Tanner Postert wrote: select text, dt, item_id from table where group by item_id order by dt DESC It had never occurred to me that there was any validity in using 'group by' without a summary function of some sort, but this does of course solve my problem at a stroke. It doesn't solve Tanner's of course because he wants a /particular/ instance returned, whereas I just want any one. So now it's all done with one tidy query, instead of a new query for each result from the first query. Thanks, Tanner! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ When you've seen one shopping centre, you've seen a mall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting unique values
At 16:13 +0100 29/6/06, Pooly wrote: If you know the picid previously retrieved, then the clause (caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%') is redundant, isn't it ? No, because those details may well be different for different instances of the same picid. Like I said - it wasn't an ideal setup in the first place, but anyway it seems to be solved now. See my post earlier today. Thanks for the interest though. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Everything Is More Difficult Than It Appears -- Adam C Engst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting unique values
I'm sure this is an elementary problem, but I can't get my head round it. I have two tables: pix and sections, the relevant bits of which are: pix (2,421 rows): picid varchar(7) not null sectionid smallint(5) unsigned not null caption text null picid and sectionid are a joint primary key caption is full text indexed sections (a mere 152 rows): sectionid smallint(5) unsigned not null title varchar(63)not null blurb text null sectionid is primary key (auto increment) title and blurb are full text indexed In pix, there may well be several instances of the same picid, but always with a different sectionid (obviously). The trouble is, this was originally set up with no intention of actually searching the tables, but now I want to. And I want to find the first instance of each picid that matches the text anywhere in caption, title or blurb, and get some other info at the same time. Oh, and for the time being it needs to be possible in MySQL 3.23.x. So far I'm doing a very simple: SELECT DISTINCT picid FROM pix AS p INNER JOIN sections AS s ON p.sectionid = s.sectionid WHERE caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%' ORDER BY picid then as I loop through the results I'm more or less repeating the process to get the other information: SELECT p.sectionid, caption, title FROM pix AS p INNER JOIN sections AS s ON p.sectionid = s.sectionid WHERE (caption LIKE '%searchterm%' OR title LIKE '%searchterm%' OR blurb LIKE '%searchterm%') AND p.picid = 'picid' LIMIT 1 Even on the small scale on which I'm operating, I'm aware that this is horribly inefficient, but being the relative beginner I am I can't see how to get the sectionid, caption and title from the first query - though I daresay it'll be obvious to the experienced SQLers out there! I'm sure it's also a case of If I were you, I wouldn't start from here, but it's what I'm stuck with. And - not wishing to push my luck, /but/ - if there's a nifty MySQL 5-type answer I'd be interested in that too. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The less a statesman amounts to, the more he loves the flag. -- Kin Hubbard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect
At 15:47 +0200 23/6/06, Jørn Dahl-Stamnes wrote: Yes, I forgot to say that I was using PHP... Oh, I think the clue was in the subject line. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Revolution: an abrupt change in the form of misgovernment. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to the group
At 14:38 -0500 22/6/06, mos wrote: If you want a more thorough book on PHP MySQL there is: PHP and MySQL Web Development (3rd Edition) (Developer's Library) (Paperback) by Luke Welling, Laura Thomson I can't speak about the third edition, as I got started using what appears to be the first. It was indeed a nice quick way to get going, but I quite quickly realised that if you took it literally you ended up with some rather sloppy code - not so much sloppy PHP as PHP that generated sloppy HTML. I hope this has changed in subsequent editions, but it may be something to look out for. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Some speakers electrify their listeners, others only gas them. -- Sydney Smith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BBEdit tip (was: Differences between MySQL 4 and 5 for scripts)
At 23:00 +0100 14/6/06, Graham Reeds wrote: 1) You may have a bogus hidden character in your SQL file. If you look at it with a text editor (BBEdit, TextWrangler, etc), with the show invivisbles feature on, do you see unusual stuff? Sounds strange but I've seen stranger. Took a brief look over it but didn't see anything that I thought looked untoward. Nor did side by side comparison show up anything. I know your problem is now solved, but a quick tip for users of BBEdit when searching for 'rogue' characters: Sometimes you can't see them at all in the normal display, but if you go to the page preferences menu - the third little square from the left inside the message window - and select Show Invisibles, this can reveal various oddities. You may find, eg, control characters appearing as inverted red ?s, or non-breaking spaces (I /think/ that's what they are) appearing as grey bullets where you expect normal spaces. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Empty warhead found in White House -- Sign carried on New York peace rally, April 2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How To Pronounce MySQL
At 7:38 -0500 8/6/06, Jimmy Guerrero wrote: The official way to pronounce MySQL is My Ess Que Ell (not my sequel), but we don't mind if you pronounce it as my sequel or in some other localized way. Miss Quill? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Among the things money can't buy is what it used to. -- Max Kauffmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search novice
MySQL 5.0.19 running in Apache 2 on Mac OS X 10.4.6 I've been dipping my newbie toe into the murky waters of full text searching, but not with a great deal of success. I have a complex search set up which searches nine tables (potentially a whole bunch more, but for the present purpose...), five of which might contain quite long lumps of text in TEXT fields (biographies, for instance), the other four with shorter stuff in VARCHAR(127) fields. All these fields have full text indices set up. Searching for the word 'olympic', if I use the full text search - match (...) against (...) - I find six people. If I use the tried and trusted like '%...%' method I find seven - the original six plus one more. The only difference between this extra one and the others is that she only has the word 'olympics' in her data, whereas all the others have 'olympic' somewhere. I thought full text searching would cater for this kind of thing, but it appears not? After I first tried it I read the thread about running myisamchk after upgrading across versions. I've just gone from 3.23.x to this version, so I thought maybe that was the answer, but having done all that I find there's no change. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Among the things money can't buy is what it used to. -- Max Kauffmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk (was: name 'Szczech' returns more rows then 'Szczec%')
I wrote: My problem isn't quite the same as the original poster's, but I suspect the solution may be the same. However, I'm having trouble running the myisamchk command. I'm using the Unix Terminal in Mac OS X, with MySQL 5.0.19, and it won't let me get beyond a certain point in the file structure to find exactly where the files are. I can 'drill down' as far as the mysql directory, which has the promisingly-named data directory inside it. However 'cd data' tells me I don't have permission... but 'sudo cd data' simply repeats the same directory! I can't seem to get into data at all. And at 17:45 -0500 4/6/06, [EMAIL PROTECTED] wrote: You must have the server stopped. I'm not sure this is true, as long as no-one's accessing it at the time - and as this is on my local development platform no-one's accessing it if I'm not. I ran myisamchk on some tables before I saw your message - with the server running - and I've just run it again after stopping the server, and got identical results. You must be logged in as root or mysql. sudo does the trick. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Among the things money can't buy is what it used to. -- Max Kauffmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1064 (42000): You have an error in your SQL syntax;
At 0:09 +1000 8/6/06, Mark Sargent wrote: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I'm no great expert myself, but off the top of my head, maybe you need to specify the type even if it's unchanged (I assume all you want to do is rename the column?). So supposing director_id was a SMALLINT(3) UNSIGNED, try: ALTER TABLE actors CHANGE director_id actor_id SMALLINT(3) UNSIGNED; Any good? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If at first you don't succeed, try, try again. Then quit. No use being a damn fool about it. -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: ERROR 1064 (42000): You have an error in your SQL syntax;
At 15:19 +0100 7/6/06, Rob Desbois wrote: With the CHANGE clause of ALTER TABLE statement, you must provide the column definition, so something like this is what you need: ALTER TABLE actors CHANGE director_id actor_id MEDIUMINT UNSIGNED NOT NULL; or whatever your original definition is. Wow! I was right. I'm learning... :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If at first you don't succeed, try, try again. Then quit. No use being a damn fool about it. -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisamchk (was: name 'Szczech' returns more rows then 'Szczec%')
At 14:02 +0300 1/6/06, Remo Tex wrote: If you change the character set when running MySQL, you must run myisamchk -r -q --set-collation=collation_name on all MyISAM tables. Hi My problem isn't quite the same as the original poster's, but I suspect the solution may be the same. However, I'm having trouble running the myisamchk command. I'm using the Unix Terminal in Mac OS X, with MySQL 5.0.19, and it won't let me get beyond a certain point in the file structure to find exactly where the files are. I can 'drill down' as far as the mysql directory, which has the promisingly-named data directory inside it. However 'cd data' tells me I don't have permission... but 'sudo cd data' simply repeats the same directory! I can't seem to get into data at all. So... I tried guessing that the file I want - which is for a table called 'attractions' in the database 'aptguserdb' - might be at data/aptguserdb/attractions.myd (and .myi for the index), but when I try the myisamchk command with the full path to there I get 'file does not exist'. I'm stumped! How exactly do I go about this? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Despite the high cost of living, it remains very popular. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk (was: name 'Szczech' returns more rows then 'Szczec%')
At 18:34 +0100 4/6/06, Chris Sansom wrote: I'm stumped! How exactly do I go about this? It's OK - I sorted it out. Turns out I needed to use upper case for the .MYI. Doh! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Old professors never die; they just lose their faculties. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql GUI
OK, so I suggested phpMyAdmin. Dan Trainor said: I highly suggest staying away from PHPMyAdmin. and, even more helpfully, 'Anthony' wrote: :-\ Fine - from this I gather phpMyAdmin is perhaps not a Good Thing. Now, given that I'm a relative newbie to all this, can someone please tell me why in slightly more coherent terms? So far, I've found it very useful. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If you have to ask what jazz is, you'll never know. -- Louis Armstrong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql GUI
At 19:44 +1000 31/5/06, Logan, David (SST - Adelaide) wrote: I would agree, I have found it useful as well. It does have a few limitations (well the versions I've used) Actually, having just mildly sung its praises, there do seem to be some bugs in the latest version I installed on my local machine (2.8.1). However... eg. It doesn't help with replication, amongst a few others. This is beyond my modest needs at the moment. I use it internally within an intranet so there isn't much of a security issue. Perhaps that was the intent? I'm a bit confused by the responses also. I use it in two situations: on my own Mac (my development platform, if you like, but also the computer I use for everything :-) ), where it's all within my local 192.168.x.y setup, so no security problems there. It's also provided by the two hosts I use as part of their 'control panel' systems (which include general account management, webmail and all the rest), so it's not installed in my web root folder - I certainly wouldn't particularly want to do that. So... what's the problem? OK, it's a bit flaky in places, but perfectly usable, and I've never had it do anything nasty to any of the small databases I manage. Furthermore, I've just downloaded the two suggested packages from mysql.net - MySQL Query Browser and MySQL Administrator - and while I can use them locally they're no use on the main host I use because they require MySQL 4.x and - until they get round to a long promised upgrade - the host's still running 3.23. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ War is only a cowardly escape from the problems of peace. -- Thomas Mann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql GUI
At 10:23 +0200 30/5/06, Anthony wrote: :-\ Very helpful - thanks. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ What contemptible scoundrel has stolen the cork to my lunch? -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql GUI
At 12:31 -0700 29/5/06, AndrewMcHorney wrote: I understand that there is a free gui that will allow an administrator or user to create databases and maybe even add, update and modify rows in tables. It is something like MySQL Controller. Does this still exist and what is the link? Maybe you're thinking of pypMyAdmin? http://www.phpmyadmin.net/ -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I've never had major knee surgery on any other part of my body. -- Winston Bennett, University of Kentucky basketball forward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql GUI
At 14:02 -0700 29/5/06, Dan Trainor wrote: I highly suggest staying away from PHPMyAdmin. Why in particular? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The world is proof that God is a committee. -- Bob Stokes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Noob: Converting to Inner Join
At 23:17 -0700 23/5/06, Graham Anderson wrote: Are there any advantages to converting this 'working' query below to use INNER JOIN ? If so, what would the correct syntax be ? Many thanks SELECT category.name, page.name, content.title, content.body FROM category, page, content WHERE content.page_id = page.id AND page.category_id = category.id AND category.id =1 ORDER BY content.order_id ASC LIMIT 0 , 30 From my limited knowledge (I'm a relative newbie and open to correction!) one syntax would be: SELECT category.name, page.name, content.title, content.body FROM category INNER JOIN page INNER JOIN content WHERE content.page_id = page.id AND page.category_id = category.id AND category.id = 1 ORDER BY content.order_id ASC LIMIT 0 , 30 ...ie., simply replace your commas with 'INNER JOIN'. Or you could do this: SELECT category.name, page.name, content.title, content.body FROM category INNER JOIN page ON page.category_id = category.id INNER JOIN content ON content.page_id = page.id WHERE category.id = 1 ORDER BY content.order_id ASC LIMIT 0 , 30 As for advantages, I'm not sure there are any for this particular query. The advantages would arise if you were to combine it with different JOINs, eg LEFT JOIN, because (in MySQL 5.x anyway) 'INNER JOIN' has a higher syntactical priority than the comma, which is the lowest priority of all. In other words, if you were to put a LEFT JOIN after your comma joins, MySQL would try to execute the LEFT JOIN first, but if you used INNER JOIN, that would be done first. I think that's about right. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I wonder who discovered you could get milk from a cow... and what on _earth_ did he think he was doing? -- Billy Connolly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Noob: Converting to Inner Join
At 23:17 -0700 23/5/06, Graham Anderson wrote: Are there any advantages to converting this 'working' query below to use INNER JOIN ? If so, what would the correct syntax be ? SELECT category.name, page.name, content.title, content.body FROM category, page, content WHERE content.page_id = page.id AND page.category_id = category.id AND category.id =1 ORDER BY content.order_id ASC LIMIT 0 , 30 And at 11:52 -0500 24/5/06, Peter Brawley wrote: Explicit INNER JOINs are easier to read, easier to debug, and since 5.0.12 always preferable in MySQL for reasons given at http://dev.mysql.com/doc/refman/5.1/en/join.html (look for '5.0.12'). SELECT category.name, page.name, content.title, content.body FROM category INNER JOIN content USING (category_id) INNER JOIN page USING (page_id) WHERE category.id = 1 ORDER BY content.order_id ASC LIMIT 0 , 30 Actually, although I've never used the USING clause - I just looked it up - I don't think this would work. Surely the column name has to exist in both tables? Graham is using page.category_id and category.id, content.page_id and page.id, so I think ON (as I posted earlier) is the only way to do this. Willing to be corrected though. :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ It was a woman who drove me to alcohol, I must write and thank her -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find invalid email formats using MySQL query.
At 22:10 +0100 20/5/06, Keith Roberts wrote: Probably the most efficient place to do this sort of field checking would be using javascript in the browser. That would stop the bad addresses even being sent down the line to the server in the first place. Sure, but if you're being conscientious about accessibility you have to allow for users without JavaScript, which means doing the test server-side as well. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ What contemptible scoundrel has stolen the cork to my lunch? -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by error
At 12:29 -0500 16/5/06, Mike Blezien wrote: trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... Just a wild guess, but... you have 'ON c.account_id = a.id', but you're grouping by a.account_id. Do both of those definitely exist, or should you be grouping by a.id? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ It isn't pollution that's harming the environment. It's the impurities in our air and water that are doing it. -- Al Gore, former US Vice President -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More LEFT JOIN newbie fun!
Not long ago, some highly knowledgeable people here kindly helped me out with a fairly complex query... Finding names of people (and other info) where one or more fields match the search string in up to five tables (abstracting somewhat): select distinct id, firstname, lastname, etc... from master_info as r inner join general_info as g left join table_1 as t1 on t1.id = r.id left join table_2 as t2 on t2.id = r.id left join table_3 as t3 on t3.id = r.id left join table_4 as t4 on t4.id = r.id left join table_5 as t5 on t5.id = r.id where g.id = r.id and (t1.blurb like '%searchterm%' or t2.blurb like '%searchterm%' or t3.blurb like '%searchterm%' or t4.blurb like '%searchterm%' or t5.blurb like '%searchterm%') That's all fine and dandy, but now I need to extend this to a further four tables... except it's really eight tables in four pairs. I'll call these table_a and table_ga .. table_d and table_gd. So far, I can get it to work if I add just one pair, in either of two ways: left join table_ga as tga on tga.id = r.id left join table_a as ta on ta.ida = tga.ida or: left join (table_ga as tga inner join table_a as ta) on (tga.id = r.id and ta.ida = tga.ida) in each case adding: or ta.blurb like '%searchterm%' to the where clause. As you'll realise this is because the text has to match the blurb column in ta, which is in turn identified by its own id which has to be matched in tga, which is simply two columns of ids (one of people, one of blurbs). That does, as I say, work, but it does slow things down pretty drastically - from less than half a second to about four seconds (whichever of the two methods I use). And when I add a second pair (table_b and table_gb) it's nearly a minute, so obviously this is going to multiply up very nastily if I add the other two pairs. I've now added full text indices to the blurb columns in table_a and table_b and that's speeded things up a lot - about 7.5 seconds now. However, in this instance there are matches in both table_a and table_b (as well as in some of the 1..5 tables). When I add the remaining two pairs in - where I know there are no matches - well, it's still running after several minutes, and that's after full text indexing those tables too. Obviously, there's a better way of doing this - any ideas? (And I'm now cancelling the last query which still hasn't finished!) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Without music to decorate it, time is just a bunch of boring production deadlines or dates by which bills must be paid. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More LEFT JOIN newbie fun!
At 16:09 +0100 11/5/06, I wrote: Not long ago, some highly knowledgeable people here kindly helped me out with a fairly complex query... ... That's all fine and dandy, but now I need to extend this to a further four tables... What I should have added is that for the moment this has to be possible in MySQL 3.23, so I can't do nice things like match ... against ... -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A censor is a man who knows more than he thinks you ought to. -- Laurence J. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explain explanation (was: More LEFT JOIN newbie fun!)
Following my post about this complex search I'm trying to do... In the initial post I said I'd tried adding: left join table_ga as tga on tga.id = r.id left join table_a as ta on ta.ida = tga.ida or: left join (table_ga as tga inner join table_a as ta) on (tga.id = r.id and ta.ida = tga.ida) to my query, and that both got the same results. Having added two pairs and tried both versions, I find the second one is fractionally (but I mean /really/ fractionally) faster, as reported by phpMyAdmin. However, when I do an Explain on both versions, the first shows values of 375, 17, 3 and a bunch of 1s in the rows column, whereas the second shows 375, 34, 6 and the same lot of 1s. All other details are identical. As I understand it in my naive, newbie way, as a rough rule of thumb you can compare the speeds of queries by multiplying together the rows values... in which case the search that was fractionally slower should have been four times as fast. What's going on here? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I'm on a seafood diet - I see food, I eat it. -- Dolly Parton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
At 9:53 +0100 10/5/06, Critters wrote: A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Interesting - I found just the same on the site I'm developing and I'll put preventative measures in place straight away! It's obviously the OR that does it, because if I just use: 1' or '1 it works, but if I try: 1' and '1 it doesn't. As Sander Smeenk said, it's the logic in the where clause: if you just did: SELECT * FROM members WHERE '1' (or indeed: SELECT * FROM members WHERE 1) it would find all rows. This is in fact the default SQL that's rather irritatingly always there in phpMyAdmin's SQL text areas. All you're doing is ORing your other criteria with the '1', which effectively makes them irrelevant. In fact, taking this one stage further, you could log in with: anyloadofoldgibberish' or '1 Try it! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Never trust a man who, when left alone in a room with a tea cosy, doesn't try it on. -- Billy Connolly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum of counts
Here comes a newbie question... I want to get a total of entries from four tables which all match a particular id. The result for the id I'm testing (21) should be 233. In my naivety, I thought something like this would work: select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 ...but no - I get about eight and a half million! I tried fiddling about with joins but got no better luck. In fact if I replace those +'s with commas I get four equal values of about 2.12 million. In the end I got the correct result like this: select (select count(*) from table_a where id = 21) + (select count(*) from table_b where id = 21) + (select count(*) from table_c where id = 21) + (select count(*) from table_d where id = 21) as total Two questions: 1 Is this the best way to do it? If not, what is? 2 This is fine in MySQL 5 (on my development platform), but 3.23 (on the live platform until the host upgrades us) doesn't support subqueries, so as an interim measure is there any better way than doing four separate queries and adding up the total in the PHP script? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If Yoda so strong in Force is, why he words not right order in put can? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
At 13:28 -0400 9/5/06, Rhino wrote: The reason you are getting so many rows has nothing to do with the way you are using the count(*) function and adding the different count() results together. The problem is that you are doing your joins incorrectly... In your case, I think you need to change the original query to this: select count(a.id) + count(b.id) + count(c.id) + count(d.id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = b.id and b.id = c.id and c.id = d.id and a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 Hi Rhino Many thanks for the very full and frank response, but sadly it didn't work. I do understand exactly what you said, and I even took it further, adding in: and a.id = c.id and a.id = d.id and b.id = d.id ...so that every table is thus related to every other one, but I'm /still/ getting that damned eight and a half million instead of the 233 I expect! I'm baffled by this, though the version I did with subqueries works very nicely (and it's simple enough to do four separate queries and add them together in the script for the older MySQL). -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Outside of a dog a man's best friend is a book. Inside of a dog it's too dark to read. -- Groucho Marx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stumped again by joins
As a relative newbie, and an almost total newbie to the use of left joins, I'm aware that there's some difference in the way joins work between MySQL 3.x and 5.x, but in my ignorance I can't figure out what the heck it is from reading the 'upgrading' pages on dev.mysql.com. When I first joined this list (joined - geddit?), Barry in particular solved a search problem for me by introducing left joins. A simplified version of my query is this (it's a database of tourist guides, where I've entered 'olympic' into the catch-all text field at the bottom): select [fields I want to display] from guides as g left join biography as b on b.guide_id = g.id left join interests as i on i.guide_id = g.id left join tours as t on t.guide_id = g.id left join walks as w on w.guide_id = g.id left join lectures as l on l.guide_id = g.id where show_on_web = '1' and (b.biography like '%olympic%' or i.interests like '%olympic%' or t.tours like '%olympic%' or w.walks like '%olympic%' or l.lectures like '%olympic%') order by ... This worked like a charm (with fulltext indices on the text fields being searched in those five tables) in 3.23.x, but now it falls over and finds nobody at all in 5.0.19. The rest of the search is fine - there are various selects and checkboxes on which you can search and as long as I type nothing into the catch-all it behaves perfectly, but as soon as I do I get a zero result. (The whole bit with the left joins only gets added to the query if there's something in the catch-all.) The other major change is that I'm now using the utf8 charset throughout the database and scripts, whereas before, with 3.23 not supporting it, I was utf8_decode()ing everything that went to MySQL and utf8_encode()ing everything that came out of it. I did try putting back the utf8_decode() round the catch-all search string, but (as I expected) it made no difference. At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. The whole point of having five separate tables for those elements is that guides can record their information in a number of languages, so there's a row per guide per language in each table - or maybe none at all (not so many guides offer lectures, for example). I want users to be able to find text in any of the languages on offer. Where am I going wrong? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ The lead car is absolutely unique, except for the one behind it which is identical. -- Murray Walker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 15:56 +0200 25/4/06, Barry wrote: Updating is always such a bad idea ;P Do you know: never touch a running system? ^_^ Hmmm... And you don't see any misdone queries when you echo them, right? Hope you checked that. Yes, they look just fine - in any case they're unchanged from when it was working perfectly in 3.23 ENCODE = NOT CODED into CODED DECODE = CODED into NOT CODED So encode the input into query and encode it afterwards :) Yes, tried that - no good. In any case... At first I thought the upgrade or utf8 might be having some effect on the way like '%...%' works, but another simpler search uses that and it's fine. ...there's no en/decoding involve there and it works fine. So in simple words. You tried also to query the Table without encoding it first into UTF-8? With and without. But anyway, as I understand it, something like 'olympic' or 'london' (another thing I'm test-searching for because just about every guide mentions London somewhere in their biography - they're London guides, see? :-) ) is the same whether or not it's encoded... no? There are various, and the main spot here is the ENCODING of UTF-8. That's more or less the conclusion I've come to as well, but I can't seem to make a difference whichever way round I do it. I'm also wondering now if it might be a PHP issue after all - something I've missed about form input, but I realise this list isn't the right place to follow that up. More infos will be great. It surely is tricky. Yep. I am not quite sure but using biography.guide_id instead of b.guide_id would probably solve the problem. I know that using aliases in WHERE clauses don't work really good, so try this also please. OK, I have, and it still makes no difference. Anyway, I reiterate: it worked perfectly in 3.23.x - surely something basic like this couldn't have got /worse/ through the version upgrades? And for what it's worth I tried taking the 'as' out too, which I gather is now optional - no diff. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Star Wars won't work. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 15:56 +0200 25/4/06, Barry wrote: And you don't see any misdone queries when you echo them, right? Hope you checked that. Hi Barry I was wrong about its being a PHP issue: it's definitely a MySQL error. I realised I hadn't handled the error in such a way that I could see what it was, but now I have, so... The full query, in all its hideousness (but prettied up a bit in the formatting :-) ) is: -- select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') -- and the error I get back is: Unknown column 'r.uid' in 'on clause' ...but I can assure you there is definitely a 'uid' column in aptg_guides_restricted. If I take out the 'r.' from those left joins (there's no uid in any other table mentioned here) I get basically the same error: Unknown column 'uid' in 'on clause'. And if I spell out 'aptg_guides_restricted.uid' in the joins I /still/ get the error: Unknown column 'aptg_guides_restricted.uid' in 'on clause' So what /is/ the problem here? I say again: this and /exactly/ this worked perfectly in MySQL 3.23, so there's obviously some change in syntax handling or whatever between versions. In fact, this is a preliminary query to establish the total. If there is a total, I then run this: -- select distinct uid, firstname, lastname, year_qualified, other_qualifications, guide_driverguide, guide_photo_1 from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') order by from_unixtime(unix_timestamp(guide_last_updated)) * (rand(1569933185) + ((length(guide_photo_1) 1) / 3)) desc -- ...and if I run that directly in the SQL window in phpMyAdmin, I get the same error: Unknown column 'r.uid' in 'on clause'. ¿Qué? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I'm on a seafood diet - I see food, I eat it. -- Dolly Parton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
At 11:10 -0500 25/4/06, gerald_clark wrote: Yes. 3.23 was not correct in the order of precedence. This has been answered many times here. Sorry - I haven't been on the list all that long. You need to change your comma join to an inner join. Lovely! That's it - many thanks. At 17:15 +0100 25/4/06, Philippe Poelvoorde wrote: in 5.0.12 comma precedence was changed : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html I did look at this but, my understanding of joins still being somewhat shaky, I didn't take in its full implications. so try with parenthesis, your implicit join and left join should then works correctly. In fact it's easier in my script to use inner join as Gerald suggested. Many thanks for your patience though. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF8 conversion
Having done my upgrade from version 3 to 5, I'm looking forward to the benefits of language support beyond the confines of Western Europe. However, it seems I need to convert the database I have now. We have material in the database at the moment in English, Dutch, Italian, Portuguese and Spanish. All the pages and PHP scripts are in UTF8, and I've been using the PHP function utf8_decode() before recording strings in the db because MySQL 3 doesn't support UFT8. Similarly I've been using utf8_encode() after reading them and outputting them to the web. It's become clear (from removing utf8_encode() from a script that reads the data) that simply setting the table's collation to utf8_general_ci isn't enough, and that the data itself is not utf8 encoded. I've looked in the docs but can't find a procedure to follow to convert my existing data to utf8. Can anyone point me to the right page? Or is the only way simply to knock up a utility script in php to read it all out, encode it, then write it back? Easy enough to do, but a bit of a pain! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doh! Ignore last post...
I've just found convert()... -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
At 19:14 +0100 14/4/06, Philippe Poelvoorde wrote: Have a serious look at : http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html There is many things likely to break, CONCAT, display of Timestamp, default values for timestamp, precedence for left join, and so on... Thanks for this advice. I've now successfully upgraded my Mac to run Apache 2.2.0, PHP 5.1.2 and MySQL 5.0.19. I've ironed out one or two minor hiccups resulting from obvious things like having columns named the same as new reserved words, and everything seems to be running pretty smoothly. As I suspected, most of the stuff detailed in those three pages goes beyond my so far fairly primitive usage of MySQL, though I am checking out a couple of PHP routines that refer to timestamp values. My use of joins has so far been minimal, and I also use very few MySQL functions so far, using PHP to do that kind of processing (though of course I appreciate the difference and realise the huge power of MySQL that I've yet to tap :-) ). I don't, for example, seem to have used CONCAT (about which I had dire warnings) at all yet! I did dump all my databases before upgrading, then run the dumps to recreate the databases, all with no fuss at all. I also ran mysql_upgrade and got a clean bill of health, for what that's worth. Thanks again to all those who provided upgrading tips. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Old professors never die; they just lose their faculties. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
At 19:14 +0100 14/4/06, Philippe Poelvoorde wrote: Have a serious look at : http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I will definitely do this. Meanwhile though, it seems to me that my best option is to set up a similar system on my local machine - where I do all my development - as soon as I can. So maybe this is where it gets /slightly/ off-topic... Can anyone recommend - bearing in mind that I'm no great Unix expert but can follow instructions slavishly! - the best combination of MySQL 5, PHP 5 and Apache 2 for Mac OS X 10.4? I do currently run PHP 5, but only Apache 1.3 and MySQL 4.1. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A professor is one who talks in someone else's sleep. -- W.H. Auden -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 3 to 5 upgrade
At 8:32 -0700 13/4/06, paul rivers wrote: Going from 3 to 5 can break a number of important things. For example, join syntax semantics and precedence rules have changed since 3, and it is certainly possible this could break your code in important and dramatic ways. You should plan on spending time checking out all the SQL. Just as important, your MySQL host should really provide a 5.x playground for you to check your app out in for at least several weeks prior to the upgrade. Just waking up one morning with the database upgraded is almost surely going to be a mess. Yeah - I think the playground is unlikely to happen. They certainly haven't said anything about providing such a facility. What I'm banking on is that my own usage of MySQL so far (I'm learning, I'm learning!) is sufficiently primitive that it won't be upset too much by the change! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3 to 5 upgrade
Our web host is currently running MySQL 3.23.something, but we're shortly to be upgraded to MySQL 5. Can I be sure that this is absolutely backwards compatible? Are there any nastinesses lurking that I should know about that might cause my databases to collapse in a heap? My use of MySQL (as my previous question will attest!) is comparatively limited so far, and I tend to do everything either via phpMyAdmin or my own PHP scripts. Any warnings would be gratefully received! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Star Wars won't work. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
At 11:56 +0200 13/4/06, Barry wrote: Make a real downgradeable SQL Dump (without collations n stuff) and have it saved. Upgrade to MySQL 5.x and execute that sql dump. Be warned that for example CONCAT behaves in a different way than in 3.x. If you have PHP scripts with some functions in their sql queries you should check them all. Data should be safe and beeing able to be inserted into the new SQL environment. Thanks - sounds like good sounds advice. :-) I'll look into CONCAT. The only thing is that, judging by past experience, the host will only give us an approximate idea of when this might happen, so I may well be presented with a fait accompli! It's likely to happen in the middle of the night, so I just hope I don't wake up one day to dozens of emails saying the whole thing's broken. I'll just have to keep my fingers crossed. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A censor is a man who knows more than he thinks you ought to. -- Laurence J. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with 'or' in 'where' clause? (longish)
First I should say I'm using MySQL 3.23.x because that's what's currently available on our host's server. An upgrade to 5.x is promised any time now, but I'm not holding my breath! So, with that in mind... I'm trying to do quite a sophisticated search across several tables and am running into trouble, maybe because of trying to use 'or', maybe because of records not present, I dunno. Whatever it is, there's obviously a flaw in my logic that I can't find. Simplifying it to the relevant bits, I'm searching six tables of data relating to people. For the present purpose I'll call the tables T1..T6. T1 contains a record for each person, including lots of basic information and an ID field that the other tables refer to. Tables T2 to T6 each have three fields: that same id, a field for a two-letter language code (en, fr, de, etc.) and a lump of text. The important thing is that in T2 to T6, any one person might have several rows if they've provided information in several languages - or none at all (relatively few have any data in T6, for example). I want to be able to enter a bit of text in the search form and find anyone in the db with the search term anywhere in T2..T6, in any language. My first instinct was this: select [fields I want to display from t1] from t1, t2, t3, t4, t5, t6 where (t2.text like '%search_term%' and t2.id = t1.id) or (t3.text like '%search_term%' and t3.id = t1.id) or (t4.text like '%search_term%' and t4.id = t1.id) or (t5.text like '%search_term%' and t5.id = t1.id) or (t6.text like '%search_term%' and t6.id = t1.id) However, that sent the system into what was obviously some huge loop which, if I waited long enough, would have produced thousands upon thousands of results. It also sent my UPS into overload, so I interrupted it! The logic looks right to me, but obviously there's something wrong: as a friend agreed, 'or' often doesn't seem to do what you expect in SQL! So then I tried this: select [fields I want to display from t1] from t1, t2, t3, t4, t5, t6 where t2.id = t1.id and t3.id = t1.id and t4.id = t1.id and t5.id = t1.id and t6.id = t1.id and (t2.text like '%search_term%' or t3.text like '%search_term%' or t4.text like '%search_term%' or t5.text like '%search_term%' or t6.text like '%search_term%') That produced no results at all, and I quickly saw why: if a person has no rows at all in any of T2..T6 they'll be excluded. Once I removed T6 from the search, one person reappeared. She didn't have the search term in all the other tables, but she does at least have entries there, but none in T6. The first solution definitely seems more logical and elegant, but where have I gone wrong, O MySQL gurus? I've investigated left joins (which I've so far had no use for, strangely enough) but can't see how to apply them in this case. Maybe subqueries, which I know we'll get in MySQL 5, are the answer? A workaround would be to force a row for every person in each of the multi-lingual text tables - for English, say - even if the text field is empty, but I don't like introducing redundant non-data like that. At least the second search would work though. Another possibility would be to use several queries, but that would mean some fairly hefty rewriting of the php that puts this stuff together, so I'd prefer to avoid it if possible. Surely it /must/ be possible in one query? (Before we decided to make this thing multi-lingual it was simple of course, because the five lumps of text were all in T1.) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'or' in 'where' clause? (longish)
At 9:28 -0500 11/4/06, mos wrote: If you keep it in 5 different tables, the search will be as slow as molasses in January because of the joins. I'd recommend using FullText search on the text field. Hi Mike Thanks for the rapid response! OK - I've set all those text fields as FullText indices... You *may* be able to do a Merge table on the 5 tables so MySQL sees it as 1 table. I'm not sure which of these features are available in 3.23 From 3.23.25 according to the docs, and it's 3.23.58 on the server, so I should be able to do this. Either that or limit each table to about 10 rows.g No can do, unfortunately! The other alternative that just occurred to me is to do 5 separate searches, each on only 1 table using a full text index. Then join the results. That could be faster than what you're doing now. Hmmm... this is getting a bit beyond my relatively short experience. How do I go about joining the results without subqueries? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ What contemptible scoundrel has stolen the cork to my lunch? -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'or' in 'where' clause? (longish)
At 16:37 +0200 11/4/06, Barry wrote: select [what you want] from t1 LEFT JOIN t2 ON t2.id = t1.id LEFT JOIN t3 ON t3.id = t1.id LEFT JOIN t4 ON t4.id = t1.id LEFT JOIN t5 ON t5.id = t1.id LEFT JOIN t6 ON t6.id = t1.id where t2.text like '%search_term%' OR t3.text like '%search_term%' OR t4.text like '%search_term%' OR t5.text like '%search_term%' OR t6.text like '%search_term%' ORDER BY t1.id ASC; That's the one! Thanks so much, Barry - looks like I've a lot still to learn. :-) I had a sneaking feeling the answer might lie in left join, but nowhere in my otherwise excellent MySQL book (it's the one for version 3 by Paul DuBois), nor in any online docs I could find, could I see how to combine more than one. This works like a charm anyway - thanks again. Not only that, but I /think/ I understand it. ;-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ What contemptible scoundrel has stolen the cork to my lunch? -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'or' in 'where' clause? (longish)
At 17:05 +0200 11/4/06, Barry wrote: Once you get a hang on JOINs you will love it =) Yeah - it certainly seems promising. Better do some more reading! Just remember: everytime you do something like this: WHERE table1.id = table2.id You will be safer and faster to use JOINs because that's what ON is for: LEFT JOIN table2 ON table1.id = table2.id LEFT join puts the WHOLE table2 to the right of the LEFT JOINED table1. example: (Hi, i'm table 1 with all my content) LEFT JOIN (Hi, i'm table 2 with all my content) if you use Where: (Hi, i'm table 1 with all my content) WHERE t2.id = t1.id (Hi, i'm table 2 but only giving you the content you wanted to see with your WHERE clause, i keep the rest for myself!!) Hmmm - now you've confused me a bit. Quoting from the section in the DuBois book: ...a LEFT JOIN forces the result set to contain a row for every row in the left side table, whether or not there is a match for it in the right side table ...which isn't necessarily what I want in all cases. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I've had a wonderful evening - but this wasn't it. -- Groucho Marx to his hostess on leaving a party -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 'or' in 'where' clause? (longish)
At 17:20 +0200 11/4/06, Barry wrote: Japanese say here: Ganbatte! (Do your best!) Oh, so true! DYB! DYB! DYB! Hmmm - now you've confused me a bit. Quoting from the section in the DuBois book: ...a LEFT JOIN forces the result set to contain a row for every row in the left side table, whether or not there is a match for it in the right side table ...which isn't necessarily what I want in all cases. Well Internally he does that, but with additional WHERE clauses you can only have those columns LEFT JOINed you necessarily want. Without WHERE he would exactly do what Mr. DuBois said. Yeah - I think I see. I'll definitely read up again and get my head round this stuff properly. Meanwhile, I've translated it back into the PHP that generates the query string and it all works very nicely indeed with the addition of a judicious 'distinct' in one place! So... thanks again, Barry! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I've had a wonderful evening - but this wasn't it. -- Groucho Marx to his hostess on leaving a party -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
