* Wilbert Enserink
> I'm using this query:
>
> CREATE temporary TABLE temptable SELECT * FROM gallery INNER JOIN
> designers
> ON (designers.designersID=gallery.designersID) WHERE
> (LOWER(gallery.omschrijving) LIKE '%$search%' OR
> LOWER(gallery.productnaam)
> LIKE '%$search%' OR LOWER(voornaam) LIKE '%$search%' OR LOWER(achternaam)
> LIKE '%$search%')
>
> now I get the result back: Duplicate column name 'designersID'
> DesignersID is a column in table gallery and in table designers-> which
> explains my LEFT JOIN.
> So I understand that MySQL is trying to build a table with 2 columns with
> the same name: designersID...
>
> anybody knows how to overcome this problem?
Don't use SELECT * ... specify the columns you want.
It would probably be faster if you split this into multiple queries, because
that will allow the use of indexes (if they are defined). Indexes will
however _not_ be used if you use LIKE "%$search%", only if you use LIKE
"$search%". This means "ilbert" will not be found, but that is normally
ok... and "Wilbert" or just "Wil" will be found a lot faster than if you use
LIKE "%$search%".
(If you have a very small database you may disregard this, keep your
"%$search%" and a single query.)
CREATE TEMPORARY TABLE temptable
SELECT
designers.designersID,
gallery.omschrijving,
gallery.productnaam,
designer.voornaam,
designer.achternaam
FROM gallery INNER JOIN designers ON
(designers.designersID=gallery.designersID)
WHERE
gallery.omschrijving LIKE '$search%';
INSERT INTO temptable
SELECT
designers.designersID,
gallery.omschrijving,
gallery.productnaam,
designer.voornaam,
designer.achternaam
FROM gallery INNER JOIN designers ON
(designers.designersID=gallery.designersID)
WHERE
gallery.productnaam LIKE '$search%';
INSERT INTO temptable
SELECT
designers.designersID,
gallery.omschrijving,
gallery.productnaam,
designer.voornaam,
designer.achternaam
FROM gallery INNER JOIN designers ON
(designers.designersID=gallery.designersID)
WHERE
designer.voornaam LIKE '$search%';
INSERT INTO temptable
SELECT
designers.designersID,
gallery.omschrijving,
gallery.productnaam,
designer.voornaam,
designer.achternaam
FROM gallery INNER JOIN designers ON
(designers.designersID=gallery.designersID)
WHERE
designer.achternaam LIKE '$search%';
There is no need to use LOWER(), unless your columns are defined as BINARY.
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
