To answer your question, we need to pretend to be your MySQL server for a
second. You appear to be posing the following question to your server
SELECT playlist.name as playlist_name
, artist.name as artist
, artist.'.$language.' as bio
, artist.purchaseLink
, media.'.$language.' as trackName
, media.path
,media.quality
,mediaType.id as mediaType
FROM playlist, artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = playlist.id
AND playlistItems.media_id = media.id'
AND media.artist_id = artist.id'
And media.mediaType_id = mediaType.id'
AND media.'.$language.' Like "%'.$query.'%"'
OR artist.'.$language.' Like "%'.$query.'%"'
OR artist.name Like "%'.$query.'%"'
ORDER BY playlist.id ASC,playlistItems.order ASC LIMIT 0, 60
of course the PHP will have substituted actual values as appropriate.
Look at your where clause. See the first OR? that makes all of the other
ANDs that come before it OPTIONAL. The query must either satisfy that
whole long list of things (all ANDed together) OR any other 'or'-ed
condition.
What you need will either be parentheses around your search conditions or
a reformatted query. My preference is to reformat using EXPLICIT INNER
JOIN clauses as it limits the probability of accidentally excluding one of
your JOIN conditions (instead of being one of a list of conditions in the
WHERE statement, they become their own ON conditions).
SELECT playlist.name as playlist_name
, artist.name as artist
, artist.'.$language.' as bio
, artist.purchaseLink
, media.'.$language.' as trackName
, media.path
, media.quality
, mediaType.id as mediaType
FROM playlist
INNER JOIN playlistItems
ON playlistItems.playlist_id = playlist.id
INNER JOIN media
ON media.id = playlistItems.media_id
INNER JOIN artist
ON artist.id = media.artist_id
INNER JOIN mediaType
ON mediaType.id = media.mediaType_id
WHERE media.'.$language.' Like "%'.$query.'%"'
OR artist.'.$language.' Like "%'.$query.'%"'
OR artist.name Like "%'.$query.'%"'
ORDER BY playlist.id ASC,playlistItems.order ASC LIMIT 0, 60
This makes your "basic query" equivalent to everything before the WHERE
clause (from the SELECT through all of the JOIN statements) and your query
information becomes the WHERE clause itself.
$sql = 'SELECT playlist.name as playlist_name, artist.name as artist,'
. ' artist.'.$language.' as bio,artist.purchaseLink,'
. ' media.'.$language.' as trackName,
. ' media.path,media.quality,mediaType.id as mediaType'
. ' FROM playlist'
. ' INNER JOIN playlistItems ON playlistItems.playlist_id = playlist.id '
. ' INNER JOIN media ON media.id = playlistItems.media_id'
. ' INNER JOIN artist ON artist.id = media.artist_id'
. ' INNER JOIN mediaType ON mediaType.id = media.mediaType_id';
$sql = $sql. ' WHERE media.'.$language.' Like "%'.$query.'%"'
. ' OR artist.'.$language.' Like "%'.$query.'%"'
. ' OR artist.name Like "%'.$query.'%"'
. ' ORDER BY playlist.id ASC,playlistItems.order ASC LIMIT 0, 60';
OR
you could reformat your query information like:
$sql = $sql. ' AND (media.'.$language.' Like "%'.$query.'%"'
. ' OR artist.'.$language.' Like "%'.$query.'%"'
. ' OR artist.name Like "%'.$query.'%")'
. ' ORDER BY playlist.id ASC,playlistItems.order ASC LIMIT 0, 60';
(I added parentheses to the first and 3rd lines. )
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Graham Anderson <[EMAIL PROTECTED]> wrote on 11/30/2004 09:57:04 PM:
> I am trying to make a proper subquery....just not sure bout the syntax
>
>
> I use this this basic query to connect all of my tables:
>
> # here is the basic query that connects all the tables..this works :)
> $sql = 'SELECT playlist.name as playlist_name, artist.name as artist,'
> . ' artist.'.$language.' as bio,artist.purchaseLink,'
> . ' media.'.$language.' as trackName,
> media.path,media.quality,mediaType.id as mediaType'
> . ' FROM playlist, artist, media, playlistItems, mediaType'
> . ' WHERE playlistItems.playlist_id = playlist.id'
> . ' AND playlistItems.media_id = media.id'
> . ' AND media.artist_id = artist.id'
> . ' And media.mediaType_id = mediaType.id';
>
>
>
> After I connect all my tables, I tried:
>
> # if there is a query string, add it to the above $sql string
> if(strcmp($query,"" )!= 0){
> $sql = $sql. ' AND media.'.$language.' Like "%'.$query.'%"'
> . ' OR artist.'.$language.' Like "%'.$query.'%"'
> . ' OR artist.name Like "%'.$query.'%"'
> . ' ORDER BY playlist.id ASC,playlistItems.order ASC LIMIT 0, 60';
>
> well, for probably obvious reasons, this does not give the right result
> Basically I am searching in every relevant field for the query string.
>
> what would would be the correct syntax for this kind of thing ?
>
> many thanks
> g
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
