Re: anyone using subversion to sync mysql dbs ?
yep, your're right thanks for clearing that up :) How do most mysql folks sync live and development databases ? As an alternative, I could use a PHP script on a late-night crontab. g On Sep 13, 2006, at 1:11 AM, Chris wrote: Graham Anderson wrote: Is anyone using subversion to sync live and development databases? If so, how? Is this desired or a best practice? Everything except my databases are under version control. In theory, I would like my databases to sync with the same subversion 'svn update' command. That way, all web content updates with one command. You mean schemas or the actual data? I wouldn't do that for data - if you create a page in your cms on your live system, you'd kill it off by doing an update. Doing a schema, sure, but data - no way. You'd need a script of some sort to handle database changes (eg adding/removing columns/indexes), no version control system will do this for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
anyone using subversion to sync mysql dbs ?
Is anyone using subversion to sync live and development databases? If so, how? Is this desired or a best practice? Everything except my databases are under version control. In theory, I would like my databases to sync with the same subversion 'svn update' command. That way, all web content updates with one command. many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3 Table Join question
Solved it with Union :) SELECT images.id,images.name, playlist.title FROM images,playlist WHERE playlist.image_id = images.id UNION SELECT images.id,images.name, media.title FROM images,media WHERE media.image_id = images.id ORDER BY id ASC On Jun 23, 2006, at 6:44 PM, Graham Anderson wrote: I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3 Table Join question
I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Noob: Converting to Inner Join
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 -- 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
many thanks Chris :) g On May 24, 2006, at 1:19 AM, Chris Sansom wrote: 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]
Making ORDER BY RAND() more random [resend]
is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.spanish as trackName, media.path, media.quality, mediaType.id as mediaType FROM artist, media, playlistItems, mediaType WHERE playlistItems.playlist_id = $myID AND playlistItems.media_id = media.id AND media.artist_id = artist.id AND media.mediaType_id = mediaType.id ORDER BY RAND() LIMIT 0, 30; the result seems to be pretty predictable is there a way to improve RAND() or is there something better ? Should I be using php to randomize the found set instead ? many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making ORDER BY RAND() more random [resend]
In the below query, the results first row is usually the same 3-4 tracks out of 30. so should I use PHP to generate a random seed, $r, and pass that to the query ? ORDER BY RAND($r) or, can it be done just with mysql many thanks g On Oct 3, 2005, at 10:15 AM, Michael Stassen wrote: Graham Anderson wrote: is there a way to make ORDER BY RAND() a bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.spanish as trackName, media.path, media.quality, mediaType.id as mediaType FROM artist, media, playlistItems, mediaType WHERE playlistItems.playlist_id = $myID AND playlistItems.media_id = media.id AND media.artist_id = artist.id AND media.mediaType_id = mediaType.id ORDER BY RAND() LIMIT 0, 30; the result seems to be pretty predictable is there a way to improve RAND() or is there something better ? Should I be using php to randomize the found set instead ? many thanks g I don't think anyone can answer this, as is. RAND() is meant to produce a pseudo-random sequence that is not truly random in the mathematical sense, but which is usually good enough for what you appear to be doing. The manual http://dev.mysql.com/doc/mysql/en/mathematical-functions.html puts it this way, RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that is portable between platforms for the same MySQL version. What is your standard for randomness? What do you mean by the result seems to be pretty predictable? Put another way, what are you expecting, and what are you getting? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making ORDER BY RAND() more random
is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.spanish as trackName, media.path, media.quality, mediaType.id as mediaType FROM artist, media, playlistItems, mediaType WHERE playlistItems.playlist_id = $myID AND playlistItems.media_id = media.id AND media.artist_id = artist.id AND media.mediaType_id = mediaType.id ORDER BY RAND() LIMIT 0, 30; the result seems to be pretty predictable is there a way to improve RAND() or is there something better ? g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql random function strangeness
I have an ORDER statement like: ORDER BY playlist.order_id ASC,RAND(playlistItems.playlist_order) I am trying to get a random result very time the query is run ... Unfortunately, this statement gives the SAME random result each time how can I get mysql to randomly jumble the playlist items for a reasonably different result each time ? many thanks to a great list -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql random function strangeness
got it to work thanks On May 10, 2005, at 7:22 PM, Graham Anderson wrote: I have an ORDER statement like: ORDER BY playlist.order_id ASC,RAND(playlistItems.playlist_order) I am trying to get a random result very time the query is run ... Unfortunately, this statement gives the SAME random result each time how can I get mysql to randomly jumble the playlist items for a reasonably different result each time ? many thanks to a great list -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie:number of hits per day query
how do I get the average number of hits per day I have a table like: DateTimeid ip 20050506190723 1 121.198.262 what I have so far SELECT DateTime , count( * ) FROM userLog GROUP BY DateTime LIMIT 0 , 30 I assume that DateTime would have to be converted to a specific day like 2005-3-7 and somehow I would need to average them can a kind soul point me in the right direction g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to use row total in aggregate query ?
I have a query: I am trying to get a percentage of the total for each city SELECT userLog.city,userLog.region, COUNT(*), /* Count(userLog.id) is always 1 was hoping to get the total row returned */ COUNT(*)/COUNT(userLog.id), media.name,artist.name FROM userLog,media,artist WHERE userLog.media_id=media.id And userLog.city != '' AND media.artist_id = artist.id GROUP BY userLog.city I was hoping Count(userLog.id) would give me the total number of returned rows in the query...so I could return a percentage of the total what would be the correct syntax to include the row total ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: Help with Match without using a subQuery
many thanks
that was exactly what I needed.
Building upon the initial question:
Is there a way to get Match statements to do partial word
searches...like %LIKE% ?
For example, if the below Match Against query with 'chris' does not
bring up anything...
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
Query the database again with 'chris*' ? Is querying the database(if
the first query fails) twice the best way to do this?
AND MATCH (
media.name, media.product
)
AGAINST (
'chris*'
)
Is there a standard way of doing this ?
thanks Shawn :)
g
On Apr 13, 2005, at 11:00 AM, [EMAIL PROTECTED] wrote:
Graham Anderson [EMAIL PROTECTED] wrote on 04/13/2005 01:46:35 PM:
Hi
I am trying to set up my Match statements to filter the result of the
main query [which works]
If 'chris' does not exist in the first MATCH statement [AND MATCH
(media.name, media.product)], then the results get a bit screwed up :(
If the first match statement finds something, then the query works
fine
What would be a better way to structure this...without using a
subquery
as I am on MYSQL 3.23
SELECT media.id, media.product AS product, media.name AS name,
Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id
AS
mediatype, mediaType.name, media.product, media.path
FROM media, artist, mediaType
WHERE media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
OR MATCH (
artist.fname, artist.lname
)
AGAINST (
'chris'
)
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
it's a parentheses issue. Your query looks like this in the where
clause
WHERE artist_ID AND mediaType_ID AND first match OR second match
Which gets evaluated like
WHERE (artist_ID AND mediaType_ID AND first match) OR second match.
Any record that matched your second match condition also satisfied your
WHERE clause. Because you wanted to match on artist_Id and mediaType_ID
plus one of the match conditions, you needed to put a set of
parentheses
around BOTH of your match conditions so that your WHERE clause looked
like:
WHERE artist_ID AND mediaType_ID AND (first match OR second match)
Here is an updated version of your original query (I also changed your
implicit inner joins to explicit ones (it's a pet peeve)):
SELECT media.id, media.product AS product
, media.name AS name
, Concat_WS( ' ', artist.fname, artist.lname ) AS artist
, mediaType.id AS mediatype
, mediaType.name
, media.product
, media.path
FROM media
INNER JOIN mediaType
ON media.mediaType_id = mediaType.id
INNER JOIN artist
ON media.artist_id = artist.id
WHERE MATCH (media.name, media.product)
AGAINST ('chris')
OR MATCH (artist.fname, artist.lname)
AGAINST ('chris')
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Help with Match without using a subQuery
Hi I am trying to set up my Match statements to filter the result of the main query [which works] If 'chris' does not exist in the first MATCH statement [AND MATCH (media.name, media.product)], then the results get a bit screwed up :( If the first match statement finds something, then the query works fine What would be a better way to structure this...without using a subquery as I am on MYSQL 3.23 SELECT media.id, media.product AS product, media.name AS name, Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id AS mediatype, mediaType.name, media.product, media.path FROM media, artist, mediaType WHERE media.artist_id = artist.id AND media.mediaType_id = mediaType.id AND MATCH ( media.name, media.product ) AGAINST ( 'chris' ) OR MATCH ( artist.fname, artist.lname ) AGAINST ( 'chris' ) ORDER BY product, media.name, artist ASC LIMIT 0 , 30 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: MYSQL nested query question
strangely, the query works intermittently :( SELECT ( SELECT City FROM Cities WHERE CityId = N.CityId ), N.Distance FROM Cities C JOIN Nbc N ON C.CityId = N.PrimaryCityId WHERE C.City = 'Los Angeles' AND N.Distance 20 sometimes it works...other times it gives the mysql query error: show keys from tbl_properties.php: Missing parameter: table huh ? On Apr 1, 2005, at 2:21 PM, Graham Anderson wrote: In the simple query... the city field showed the result 'Los Angeles' in every row the distance field showed incorrect results to :( City| Distance Los Angeles 18 Los Angeles 5 Los Angeles 7 ... On Apr 1, 2005, at 1:59 PM, Peter Brawley wrote: What was wrong with Graham's simpler query? PB - Graham Anderson wrote: I upgraded my local mysql version to 4.1.10a and the below query finally works :) How can I now amend the query so it works on my remote server running mysql 3.23.58 ? From one headache to another ;) SELECT ( SELECT City FROM Cities WHERE CityID = N.CityID ), N.Distance FROM Cities C JOIN Nbc N ON C.CityID = N.PrimaryCityID WHERE C.City = 'Los Angeles' AND N.Distance 20 many many thanks to all those that replied :) g On Mar 31, 2005, at 11:49 PM, Philip M. Gollucci wrote: Graham Anderson wrote: What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId =ci N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) Unless I missed something... Why did you make it so hard ? SQL is meant to be easy :) SELECT c.city, n.distance FROM Cities c, Nearbycities n WHERE c.cityid = n.primarycityid AND c.city = 'Los Angeles' AND n.distance 20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: MYSQL nested query question
What is the proper way to say this ? SELECT C.City, N.Distance FROM Cities C JOIN Nearbycities N ON C.CityId = N.PrimaryCityId WHERE N.CityId = (SELECT Cities.CityId FROM Cities WHERE Cities.city = 'Los Angeles') AND N.distance 20 I am trying to enter in a city and get all the nearby cites with 20 miles Somehow, I need to join NearbyCities.PrimaryCityId, Cities.CityId, and Cities.city learning :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Average Time on Server query
I am trying to determine the average time that a Distinct IP address is using the server If I have 15 thousand records of ip addresses and access times like: IP Now() media.id -- 10.1.7.205 20050329121645 67 68.252.32.7620050329095923 72 And, I want to set the cut-off time to 15 minutes... Basically, if the user has not requested media on the server in last 15 minutes, the user has logged off can someone point me in the right direction as I am very new to more advanced mysql queries many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax problem: mysql 3.23 vs 4.13
this sql works on mysql version 3.23.58...my remote server SELECT c.City, r.Region, co.Country FROM subnets s, cities c, regions r, countries co WHERE c.CityId = s.CityId AND c.RegionID = r.RegionID AND c.CountryID = co.CountryId AND s.SubNetAddress = '24.24.172' LIMIT 0 , 30 but the same syntax fails on mysql version 4.1.3-beta ...my local computer When I EXPLAIN the sql, I get the error: Impossible WHERE noticed after reading const table... anyone know what this could be ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Searching for empty fields
how do you search for an empty field ? I want to filter out records where the ipAddress field is empty SELECT * FROM 'userLog' WHERE 'ipAddress' IS [empty] ? many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newvbie:TO_DAYS Question
stupidly My DateTime field is in this format: March 23, 2005, 3:49 pm If I want to run this sql SELECT TO_DAYS(MAX(DateTime)) - TO_DAYS(MIN(DateTime)) AS record FROM userLog I gather I need some extra function like.. SELECT TO_DAYS(MAX(Convert _Function(DateTime))) - TO_DAYS(MIN(Convert _Function(DateTime))) AS record FROM userLog what is that magic function ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: is this Query Reasonably Efficient ?
I have 3 different tables I need data from And, the tables have the potential to get fairly large I am using mysql 4.1.3 This working query below pulls up all media requests for 'Yolanda Perez' in Los Angeles Is there a appreciably speedier way to say the below ? I have begun dipping my toe into more complicated queries Select artist.name,media.name,userLog.city,userLog.DateTime From userLog,media,artist Where city = 'Los Angeles' And userLog.media_id = media.id And media.artist_id=artist.id And artist.name = 'Yolanda Perez' many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: mysql syntax error question
My server has mysql: version 3.23.58 // Performing SQL query SELECT cities.city, regions.region, countries.country FROM cities JOIN subnets on subnets.cityid=subnets.cityid JOIN regions on subnets.regionid=regions.regionid JOIN countries on subnets.countryid=countries.countryid where subnets.subnetaddress='24.24.172' this gives a syntax error: #1064 - You have an error in your SQL syntax near 'on subnets.cityid=subnets.cityid JOIN regions on subnets.regionid=regions.regi' at line all the tables seem proper named and populated. what could this be ? g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: making a proper subquery
I am trying to make a proper subqueryjust 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]
Re: Newbie: How to deal with multiple languages
thanks for all the help this will help as I think the client wants this project in 3 languages On Nov 28, 2004, at 7:25 AM, Rhino wrote: - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 27, 2004 5:36 AM Subject: Re: Newbie: How to deal with multiple languages Hello. You can find an answer here: http://dev.mysql.com/doc/mysql/en/Charset.html MySQL supports column character sets on columns of some types (char,varchar,text). Probably if I were you I would use Unicode in my application. Graham Anderson [EMAIL PROTECTED] wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... You *could* alter your design to do something like this: create table artist (artist_id [column type] not null, artist_name [column type] not null, artist_pictlink [column type], artist_purchaseLink [column type], artist_bio_code int, primary key (artist_id) foreign key artist_bio_code references artist_bio on delete restrict) Type=InnoDB; create table artist_bio (artist_bio_code int not null, artist_bio_Spanish [column type], artist_bio_English [column type], artist_bio_German [column type], primary key(artist_bio_code)) Type=InnoDB; You would then have to join to get the artist_bio information in the desired language(s) but, of course, you wouldn't have to do the join unless you needed the bio. The dramatically smaller size of your artist table could help your performance for those queries where you don't need the bio. Naturally, queries that need the bio will have a bit more work to do to get the bio. Both designs lend themselves to supporting additional languages if that should become necessary. I think that is very important because I can easily imagine having to increase the number of languages. I haven't done any work with character sets in MySQL so I don't know if there would be any advantage to having the foreign character data separated into their own tables so that 'main' tables like 'Artist' would have only standard characters. You should probably read the chapter on character sets that Gleb cited to try to figure that out. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] graham anderson 310.402.3980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: How to deal with multiple languages
I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change field names in a query
what is the proper mysql syntax to change field names after the query is done if I have a table with: id englishText spanishText picture And I query the table with: select id, spanishText from myTable limit 30 how do I change the 'spanishText' field name to say 'language' ? many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change field names in a query
many thanks :) that was exactly what I needed g On Nov 26, 2004, at 4:27 PM, Rhino wrote: - Original Message - From: Graham Anderson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 26, 2004 6:28 PM Subject: change field names in a query what is the proper mysql syntax to change field names after the query is done if I have a table with: id englishText spanishText picture And I query the table with: select id, spanishText from myTable limit 30 how do I change the 'spanishText' field name to say 'language' ? many thanks g If I understand your question correctly, you need to use an 'as' expression. For example: select id, spanishText as 'language' from myTable limit 30; This example tells MySQL to display the contents of the columns named 'id' and 'spanishText'. The column headings in the result set will be 'id' for the 'id' column (if you don't supply an 'as' expression, the original column name is used most of the time) and 'language' for the 'spanishText' column since that is the column heading you preferred. There are some limitations on the aliases that you supply via the 'as' expression but you'd have to look them up in the MySQL manual to be sure what they are; if the MySQL rules are like rules for DB2, there is a length limit for aliases and aliases that contain embedded blanks need to be in quotes. Rhino graham anderson 310.402.3980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy/Paste text from Ms Word into mysql
I am copy/pasting text from a microsoft word doc into phpmyadmin are there any special tricks to avoid strange characters...most of my text is in Spanish The only thing that seems to work is converting the text to ASCII which ditches all the Spanish formatting :( is there some process to make this a painless process ? g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
