Problem with characters
I have a new setup with mySql version 4.1 and myODBC version 3.51 running on Windows 2k3 standard In the database we have something like And this is £200 and when we write this out in ASP we get And this is ?200 The same happens for some other symbols like the copy write symbol (c in a circle) Any ideas? I have googled this for almost an hour now and never had this problem on other installs. -- Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left join is not doing what I thought it should do.
I have 3 tables A users table (userID, userName) A leaderboard table (userID, score) A friends table (userIDA, userIDB) I would like to produce the following result: userName, score, userIDA Dave, 100, 1 Simon, 200, 5 Paul, 300, NULL The 3rd record is NULL as there is no record in friends with a userIDB matching users (or leaderboard) userID I have tried this: SELECT users.username, gameLeaderboards.playerpoints, friends.userA FROM gameLeaderboards JOIN users ON gameLeaderboards.userID = users.ID LEFT JOIN friends ON gameLeaderboards.userID = friends.userB WHERE friends.userA = 79760 The where is so there is only a value in the userIDA column if the user is friends with userID 79760 But what I get instead of lots of records with 79760 and NULLs is just records from the leaderboard table that have a matching userID in the friend table, hmmf Any ideas? Thanks - Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join is not doing what I thought it should do.
It works if I do AND instead of WHERE Go figure LEFT JOIN friends ON gameLeaderboards.userID = friends.userB AND friends.userA = 79760 -- Dave Jerry Schwartz wrote: I think your problem is that you can't have a missing friends record that also has a non-null value for friends.userA. If friends.userA = 79760, then you've found a record. You can have records where userA is something valid and UserB is null, but then you can't join on UserB. Does that help? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Critters [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 22, 2007 12:23 PM To: MySQL General Subject: Left join is not doing what I thought it should do. I have 3 tables A users table (userID, userName) A leaderboard table (userID, score) A friends table (userIDA, userIDB) I would like to produce the following result: userName, score, userIDA Dave, 100, 1 Simon, 200, 5 Paul, 300, NULL The 3rd record is NULL as there is no record in friends with a userIDB matching users (or leaderboard) userID I have tried this: SELECT users.username, gameLeaderboards.playerpoints, friends.userA FROM gameLeaderboards JOIN users ON gameLeaderboards.userID = users.ID LEFT JOIN friends ON gameLeaderboards.userID = friends.userB WHERE friends.userA = 79760 The where is so there is only a value in the userIDA column if the user is friends with userID 79760 But what I get instead of lots of records with 79760 and NULLs is just records from the leaderboard table that have a matching userID in the friend table, hmmf Any ideas? Thanks - Dave -- 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]
A select for a game ranking page.
Hi I have a table with: player_name, top_score, number_of_plays When I list them out I ORDER BY top_score DESC, number_of_plays DESC, player_name to help give some sort of order to the people with the same scores. What I would like to do is find out a players position without looping through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays 10 is no good as some players have higher scores but lower plays but should be counted as been higher ranked. I don't want to loop through the scores, that's not very elegant. Also creating a temp table where the scores are in order and then counting on that would also be overkill? I hope this makes sense and that there is a solution. -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Type Mismatch
Hi,
I hope someone can help me with my problem, something that has come up
when moving code and DB to a new server:
Connection:
driver={MySQL ODBC 3.51
DRIVER};server=localhost;uid=xx;pwd=xx;database=xx;option=16387
SQL:
SELECT (sum_score/sum_votes) AS 'score' FROM xx WHERE id =
xx
Value of score:
6.2153
ASP:
%=int(RS(score)*25)-20%
Error:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
Any help appreciated, I did not have this problem when I had the same
set-up but on a server running an earlier version of MySQL and the ODBC
driver.
--
David Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type Mismatch
Thanks for responding.
If I just response.write score I get 6.5714
I got it working by doing this: cast(sum_score/sum_votes as signed) AS
'score'
Which returns 7. So it is a MySQL error? I would prefer to do the
rounding in ASP and not have to update other scripts giving the same
problems.
--
Dave
J.R. Bullington wrote:
This is an ASP error, not a MySQL error.
However, try doing a
response.write rs(Score)
response.flush
Then you will see why you are getting the mismatch error. It is probably the fact that
rs(Score) is not returning an integer or number of any kind (i.e. if
rs(score) is null).
HTH!
From: Critters [EMAIL PROTECTED]
Sent: Tuesday, June 19, 2007 7:44 AM
To: MySQL General mysql@lists.mysql.com
Subject: Type Mismatch
Hi,
I hope someone can help me with my problem, something that has come up
when moving code and DB to a new server:
Connection:
driver={MySQL ODBC 3.51
DRIVER};server=localhost;uid=xx;pwd=xx;database=xx;option=16387
SQL:
SELECT (sum_score/sum_votes) AS 'score' FROM xx WHERE id =
xx
Value of score:
6.2153
ASP:
%=int(RS(score)*25)-20%
Error:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
Any help appreciated, I did not have this problem when I had the same
set-up but on a server running an earlier version of MySQL and the ODBC
driver.
--
David Scott
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stored procedure not working in legacy ASP
Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure not working in legacy ASP
The stored procedure is in MySQL, but when called using ASP it fails to return more than the first record. Anyone? -- Dave Michael Dykman wrote: Surely, you don't have legacy stored procedure in ASP under MySQL? are you sure this is the right list to be asking? - michael On 5/30/07, Critters [EMAIL PROTECTED] wrote: Hi How do you get multiple record sets from a stored procedure in legacy ASP? It doesn't seem to work for us. The question is how to return multiple record sets from a single stored procedure which myodbc doesn't seem to support? set rs = connection.execute(strSQLsp) If not rs.EOF then response.write rs(1) End if set rs = rs.NextRecordset If not rs.EOF then response.write rs(2) End if We only get the first response.write Thanks -- David Scott -- 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]
A join I can not figure out
Hi, I am certain this is possible with a single query, but I have had not joy looking on google or using trial and error in MySQL... Table1: id, name Table2: id,member1,member2 In Table2 the member1 and member2 are the ID's from Table1 Table1: 1, Dave 2, Bob 3, Simon Table2: 1,1,2 2,2,3 3,1,3 Result wanted: 1,Dave,Bob 2,Bob,Simon 3,Dave,Simon There are a whole bunch of fields I would want to pull from table1, but for this example I have just used name. Can anyone point me in the right direction? -- Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I think I need a join
I think the following could be done with some sort of JOIN, but I am now sure how: [country]: id, country, number 1, Germany, 27 2, Japan, 30 3, United States, 18 [days] id, day, countryA, countryB 10, monday, 1, 3 11, tuesday, 2, 3 12, wednesday, 1, 2 [result I want] 10, monday, Germany, 27, United States, 18 11, tuesday, Japan, 30, United States, 18 12, wednesday, Germany, 27, Japan, 30 Hope that makes sence :) I can get it to join on either countryA or countryB but not both :| -- David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1' and '1' or '1
Hi 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. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar hack Appreciate any feedback. -- Dave
Re: 1' and '1' or '1
Tahnks all for your responses (so many) I am reading up on it now -- Dave - Original Message - From: Johan Lundqvist [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 10, 2006 10:26 AM Subject: Re: 1' and '1' or '1 Hi Dave, 1st: Never, never, never store passwords in plain text!! Just don't do it. Store a hash of the password (ie md5 or something else). 2nd: Never pass any input from the Internet directly into a query without first checking it for sql injection. Take a look at Wikipedia article for a brief explanation and several links to further info. http://en.wikipedia.org/wiki/SQL_injection /Johan Critters wrote: Hi 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. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar hack Appreciate any feedback. -- Dave -- 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]
Group By over many colums
Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott
Re: Group By over many colums
The actual table is called sends and the data is like this: | id | f1 | f2 | f3 | | 3 | foo.com | yahoo.com| | | 4 | dsl.pipex.com | foo.com| foo.com| | 5 | vodafone.com| btinternet.com| co-op.co.uk | I tired: SELECT domain, count(*) FROM ( (SELECT f1 as domain from sends) union all (SELECT f2 as domain from sends) union all (SELECT f3 as domain from sends) ) GROUP BY domain But I get: [localhost] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT f1 as domain from sends) union all (SELECT f2 as domain Can you spot where I am going wrong? - David Scott - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Critters [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:34 PM Subject: Re: Group By over many colums Hi, To this on I just see a solution, that depends on sub-selects, so it's available from Mysql 4.1 forward: SELECT name,count(*) from ((SELECT name1 name FROM tablename) UNION ALL (SELECT name2 name FROM tablename) UNION ALL (SELECT name3 name FROM tablename)) tab GROUP by name; Hope this solves you problem. mpneves On Thursday 19 January 2006 15:16, Critters wrote: Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- 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: Group By over many colums
Thanks for the replies Marco... mysql Ver 12.22 Distrib 4.0.21 So that could be it? By the way... (SELECT f1 as 'domain' from sends) union (SELECT f2 as 'domain' from sends) union (SELECT f3 as 'domain' from sends) union (SELECT f4 as 'domain' from sends) Works, and returns a list where f1, f2, f3 and f4 are all in the column domain But as soon as I add GROUP BY domain to the end it fails. I have also tried GROUP BY 'domain' -- David Scott - Original Message - From: Marco Neves [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Critters [EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:34 PM Subject: Re: Group By over many colums Hi, To this on I just see a solution, that depends on sub-selects, so it's available from Mysql 4.1 forward: SELECT name,count(*) from ((SELECT name1 name FROM tablename) UNION ALL (SELECT name2 name FROM tablename) UNION ALL (SELECT name3 name FROM tablename)) tab GROUP by name; Hope this solves you problem. mpneves On Thursday 19 January 2006 15:16, Critters wrote: Hi I have a table setup like this: id, name1, name2, name3 Which has data like this: 1, Dave, Bob, Simon 2, Joe, Tim, Dave 3, Dave, Bob, Tom I can run SELECT name, count(id) FROM tablename GROUP BY name1 ORDER BY count(id) DESC Which would give me: Dave, 2 Joe, 1 But how would I go about getting the following result: Dave, 3 Bob, 2 Tom, 2 Joe, 1 Simon, 1 Where it groups by name1, name2 and name3? Is it possible? - David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- 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: Group By over many colums
Thanks! I wrote: DELETE FROM t_sends; CREATE TEMPORARY table IF NOT EXISTS t_sends (SELECT f1 as 'domain' from sends WHERE gameID = 1) union all (SELECT f2 as 'domain' from sends WHERE gameID = 1) union all (SELECT f3 as 'domain' from sends WHERE gameID = 1) union all (SELECT f4 as 'domain' from sends WHERE gameID = 1) ; SELECT MID(domain,INSTR(domain,'@')+1) AS 'domain' , count(*) from t_sends GROUP by 'domain' And that does the trick Is there any way to destroy the t_sends table? -- Dave - Original Message - From: Marco Neves [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, January 19, 2006 4:20 PM Subject: Re: Group By over many colums Hi Critters, The problem is that as your MySQL is 4.0.21 don't suport the subselect you would need to do the group. I was thinking and you have another alternative: CREATE TEMPORARY table tdata (SELECT f1 as 'domain' from sends) union all (SELECT f2 as 'domain' from sends) union all (SELECT f3 as 'domain' from sends) union all (SELECT f4 as 'domain' from sends); -- This would create an temporary table with all the data SELECT domain,count(*) from tdata GROUP by domain; -- This sould do the trick. mpneves On Thursday 19 January 2006 16:14, Critters wrote: Thanks for the replies Marco... mysql Ver 12.22 Distrib 4.0.21 So that could be it? By the way... (SELECT f1 as 'domain' from sends) union (SELECT f2 as 'domain' from sends) union (SELECT f3 as 'domain' from sends) union (SELECT f4 as 'domain' from sends) Works, and returns a list where f1, f2, f3 and f4 are all in the column domain But as soon as I add GROUP BY domain to the end it fails. I have also tried GROUP BY 'domain' -- David Scott -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- 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]
Wide eyes
Hi The company I work for is putting together a quote for a site, the client has wide eyes and is proposing 5,000,000 users and other large figures for elements which we intend to use MySQL for. So the questions: Is a table with say 5,000,000+ records possible? What are the things to look out for with this amount of data? Could the database be split over several database servers? Is there anywhere on the mySQL site about huge databases? Thanks in advance for any help. -- David Scott
Re: Wide eyes
Thankyou very much, at this stage we just wanted to know it *can* be done. Thanks again -- David Scott - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, May 23, 2005 3:39 PM Subject: RE: Wide eyes [snip] Is a table with say 5,000,000+ records possible? What are the things to look out for with this amount of data? Could the database be split over several database servers? Is there anywhere on the mySQL site about huge databases? [/snip] Yes. We have several tables with well over 100 million records weighing as much as 115 Gb. Proper indexing. Sure, using clustering. Hmmm, not sure. -- 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]
A question of joining...
Hi, I am having problems with the JOIN function. MESSAGES memberID_1, memberID_2, Message MEMBERS id, name I can only manage to replace the memberID_1 in MESSAGES with the name in MEMBERS, I can not replace both memberID_1 and memberID_2 with name. Please can someone tell me what I should be looking for in the help documents. -- David Scott
Re: A question of joining...
Thankyou, That worked a treat! Thankyou so very much -- David Scott - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 17, 2005 5:01 PM Subject: Re: A question of joining... Critters wrote: Hi, I am having problems with the JOIN function. MESSAGES memberID_1, memberID_2, Message MEMBERS id, name I can only manage to replace the memberID_1 in MESSAGES with the name in MEMBERS, I can not replace both memberID_1 and memberID_2 with name. Please can someone tell me what I should be looking for in the help documents. -- David Scott You need a join for each lookup. Something like: SELECT mem1.name, mem2.name, mess.Message FROM messages mess JOIN members mem1 ON mess.memberID_1 = mem1.id JOIN members mem2 ON mess.memberID_2 = mem2.id WHERE ...; If it is possible that either memberID_1 or memberID_2 is NULL, or doesn't point to a valid member row, then you should replace JOIN with LEFT JOIN. Michael -- 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]
Updating one table with results from another..
Hi, I have alot of data and im trying to speed things up by making some summary tables. My summary_totals table has: id, websiteid, hits, visitors This will contain the websiteid, total hits and total visitors. My hits table has (there are many more fields, but they are not relevant to this) id, websiteid My visitors table has (there are many more fields, but they are not relevant to this): id, websiteid So this works: INSERT INTO summary_totals (websiteid, hits) SELECT websiteid, count(id) FROM hits GROUP BY websiteid; And gives me a new table with the websiteid and total hits for each websiteid but how do i add the visitors total? To get the visitors total I do: SELECT websiteid, count(id) FROM visitors WHERE websiteid = X How can I get this total into the summary table? Using an update somehow? or as part of the insert?
A question of negative numbers..
Hi, I have a query that returns a list of numbers ranging from -10 to +10 I would like to be able to have a 2nd column where a result of 5 is 5 but -5 is also 5, so in effect all the negative (and only the negative) results are made positive to find the deviation from zero. so 5, 4, -3, 4, -1, 0 would become 5, 4, 3, 4, 1, 0 I have been searching for if then else in google so I could do if a 0 then a = 0-a but no joy. is there a function to make negative numbers positive?
RE: A question of negative numbers..
Found answer to my own question: Returns the absolute value of X: mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. MySQL Reference Manual (C) 2002 MySQL AB
slow select... where... order by
Hi I have a database with just over 10,000 records. with the following structure: id, compid, name, score, and about 10 other fields I have indexed id, compid, score about 10 records out of the 10,000 have a compid of 2 when i do select * from table where compid = 2 it was slow until i indexed the compid and now is 1 second, but select * from table where compid = 2 order by score takes around 15 seconds and has alot of HDD activiry. Any way to speed this up? its driving me nuts. -- Dave
