COUNT Causing Problems.


Hi all

What I have below works good and returns all the reviews depending on the venue_id selected.
Code SQL: SELECT comments, DATE_FORMAT(review_date, '%M %D %Y') AS subdir, publisher, rating, FROM tbl_reviews WHERE venue_id='"...
Example return
comments: some comments about the venue
date: 12th dec 12
rating: 4

comments: some comments about the venue
date: 13th dec 12
rating: 2

and so on...

But now I need to COUNT the number of reviews for that particular venue_id.

Code SQL: SELECT comments, DATE_FORMAT(review_date, '%M %D %Y') AS subdir, publisher, rating, COUNT(venue_id) AS VenueReviewTotal FROM tbl_reviews WHERE venue_id='"...
Example return
comments: some comments about the venue
date: 12th dec 12
rating: 4

and stops, only shows one review even if there are 15

The review count is correct
VenueReviewTotal 15

What is happening, why does this happen when I introduce the COUNT function

Posted On: Monday 31st of December 2012 01:05:17 AM Total Views:  382
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




What kind of speed might I expect with request a count of selected items?

I realize my question is vague because the machine specs matter. Let's say it's a reasonably fast Mac OS X or Linux machine with 3 GB of RAM and a 5200 rpm HD. A typical machine. Just for an example, suppose there are 200,000 user records in the users table. Each record has an integer column called "counter." I want to know how many user records have a counter greater than zero. I still am studying SQL so my request syntax might not be right, but I think the request is something like: SELECT COUNT(user) FROM users WHERE counter > 0; In this case the COUNT parameter itself probably doesn't matter as long as it is some column in users, right Anyway, the reason I'm asking is because while I'm posting this I have a while loop executing in a different, non-SQL database. It's counting this one-by-one for all users and it looks like it will take maybe 3 hours to finish. With a MySQL request such as this, approximately how long would I have to wait I don't need exact numbers, just orders of magnitude. Like would you expect such a request to take seconds, tens of seconds, minutes, hours Also, what if the "counter" property was in a separate counters table and I did the same kind of request with an INNER JOIN like: SELECT COUNT(user) FROM users INNER JOIN counters ON id = counters.userid WHERE counter > 0; Would that take much longer than if the request was in one table
VIEWS ON THIS POST

136

Posted on:

Thursday 25th October 2012
View Replies!

Set a counter and test condition in an sql select statement? Possible?

Instead of executing 2 slightly different sql statements to get a total of 10 results can it be done with just one select Can I set a variable like a counter in a Mysql statement Code MySQL: # get 3 results where c can be 2000 chars select a , b , c FROM mytable where condition = true LIMIT 3 # get the 1st 100 chars of c for the next 7 results select a , b , LEFT (c, 100) as c FROM mytable where condition = true LIMIT 3, 10
VIEWS ON THIS POST

142

Posted on:

Thursday 25th October 2012
View Replies!

adding 2 counts to an existing query

i need to add the following 2 queries: PHP Code: $query=mysql_query("SELECTCOUNT(*)FROM`peers`WHERE`hash`=RTRIM('$hash)AND`left`=0AND`expire_time`>NOW()"); $query=mysql_query("SELECTCOUNT(*)FROM`peers`WHERE`hash`=RTRIM('$hash)AND`left`>0AND`expire_time`>NOW()"); to this query: PHP Code: $sql=mysql_query("selectt.id,t.uploader_id,s.memberName,t.category_id,c.name,t.hash,t.title,t.file_name,t.file_size,t.rating,t.date_uploaded \t\t\t\t\t\t\t\t\t\tfromthefilesast,smf_membersass,categoriesasc \t\t\t\t\t\t\t\t\t\twheret.uploader_id=s.ID_MEMBERandt.category_id=c.id \t\t\t\t\t\t\t\t\t\torderbyt.iddesc \t\t\t\t\t\t\t\t\t\tlimit50")ordie(mysql_error()); i created that query myself, but i have no idea how to add the counts to it so its all in 1 nice query. can a guru help
VIEWS ON THIS POST

116

Posted on:

Thursday 25th October 2012
View Replies!

Can't figure out how to create complex query with group by and count

I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: usertagid | usertagsiteid | usertagtext 1 | 1 | 'search' 2 | 1 | 'search engine' 3 | 2 | 'news' If i feed the query the usertagtext 'search' it should list: 'search engine', 1 If i feed the query the usertagtext 'search engine' it should list: 'search', 1 If i feed the query the usertagtext 'news' it should not return any results. Here is what I have so far but it doesn't accept the usertagtext input into it: Code: SELECT DISTINCT usertagtext, COUNT(usertagtext) AS usertagsum FROM usertags GROUP BY usertagtext ORDER BY usertagsum DESC LIMIT 20 Let me know if that still doesn't make sense, I'm having a hard time trying to describe it. Hopefully you can figure out what I am looking for based on my examples.
VIEWS ON THIS POST

131

Posted on:

Thursday 25th October 2012
View Replies!

mysql + pid / process count + increases

I have mysql 4.1.22 compiled on RHEL 4 and running fine. Over the time the threads / pids of mysql keep on increasing. For eg : ps -ef |grep mysql |wc -l 63 ps -ef |grep mysql |wc -l 101 The system is Intel Dual Core, RHEL4, 300 GB SATA HDD with 16GB RAM. Also I have observed that I cannot login into mysql shell when the process count goes higher beyond 100 meaning that it takes lots of time to enter into the mysql> shell I am using the my-innodb-heavy-4G.cnf as my.cnf with couple of modifications like max_connections=1000 skip-name-resolve innodb_file_per_table innodb_flush_method=O_DIRECT lower_case_table_names=1 expire_logs_days = 10 net_read_timeout=60 net_write_timeout=120 max_connect_errors = 100 innodb_locks_unsafe_for_binlog=1 innodb_flush_log_at_trx_commit=2 innodb_lock_wait_timeout=300 myisam_repair_threads = 10 innodb_file_io_threads = 20 innodb_thread_concurrency = 16 innodb_table_locks = 0 wait_timeout = 480 Rest of the settings remain same. The configure command used for compiling mysql is CFLAGS="-static -O3 -march=nocona -funroll-loops" \ CXX=gcc CXXFLAGS="-static -O3 -march=nocona -pipe -mmmx -msse -msse2 -mfpmath=sse,387 -funroll-loops -fno-exceptions -fno-rtti -fomit-frame-pointer -felide-constructors -mtune=nocona" \ ./configure \ --prefix=/usr/local/mysql \ --with-mysqld-user=mysql \ --libexecdir=/usr/sbin \ --sysconfdir=/etc \ --mandir=/usr/share/man \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static \ --with-thread-safe-client \ --with-unix-socket-path=/tmp/mysql.sock \ --localstatedir=/usr/local/mysql \ --enable-assembler \ --disable-shared \ --without-readline \ --without-debug \ --without-docs \ --without-bench and then make, make install and strip /usr/sbin/mysqld What am I doing wrong and why the process count is increasing over the time
VIEWS ON THIS POST

264

Posted on:

Thursday 25th October 2012
View Replies!

mysql count query

Hi I'm tying myself in knots trying to figure out a single query that returns elements from one table (a category) and the number of elements related to it (elements with said category id) within another table. I have not introduced foreign keys in this instance. cats table id | catName -------------- 1 | cat1 2 | cat2 3 | cat3 4 | cat4 items table id | name | catId -------------------- 1 | item1| 1 2 | item2| 2 3 | item3| 2 4 | item4| 1 I'd like something returned like: cats.name | instances of cats.id in items Hopefully that is clear, I'd be very appreciative of any help .
VIEWS ON THIS POST

228

Posted on:

Thursday 25th October 2012
View Replies!

Select items from table 1, count related in table 2 - how?

Here're the tables: IMAGE ---------- image_id title date_posted ..... COMMENTS ---------- commnet_id image_id .... I want to select the 10 most recent posts (date_posted) and get the count of post_id in COMMENTS for each one. The deed output would be: Image title1 (3 comments) Image title2 (0 comments) Image title3 (7 comments) ... I know this is probably an extreme newb questions by my head hurts already.
VIEWS ON THIS POST

113

Posted on:

Thursday 25th October 2012
View Replies!

MySQL Proxy least_count

As i read in proxy manual that it works "send new connections to the server with the least number of open connections" but in my case it works in round robin means sending connections to backends one by one. It's not sending the connection by the counting the no. of connections to mysql server. I used three backends and balance.lua script without any changes provided by default in installation. Can any one provide me the script to work on "least_count" as i don't know the lua script in great details. Thank's in advance.
VIEWS ON THIS POST

60

Posted on:

Thursday 25th October 2012
View Replies!

Efficient way to count groups of records

I have a database that has items and rank and I want to get the count of items for all of the ranks. So the data is like... item1 rank 0 item2 rank 0 item3 rank 0 item4 rank 1 item5 rank 1 item6 rank 1 item7 rank 2 item8 rank 2 Where there are about 500 items and ranks 0-10. What would be the best SQL query to get the following output rank0 - 3 rank1 - 3 rank2 - 2 I know I could do a count when looking for rank0, but I prefer not to call it 11times.
VIEWS ON THIS POST

107

Posted on:

Thursday 25th October 2012
View Replies!

sorted list of cities in countries (was "What is the best way to do this?")

, I have database with cities, countries, regions etc. I need to take list of countries from one table, then I need to take list of cities of each country from previous list and sort ALL cities of all countries by some value. What is the best (easiest, fastest) way to do that
VIEWS ON THIS POST

193

Posted on:

Thursday 25th October 2012
View Replies!

why does this count return 1 instead of 0?

I don't understand why this count will return 1, when it should return 0. If I enter the resource_id 1, it will return 2, which is correct. But if I enter any other resource_id, all of which do not exist in the 3 rows of data I have, it returns 1. Code: SELECT COUNT(id) FROM bookings WHERE ('2007-05-17 07:00:00' BETWEEN `start` AND `finish`) OR ('2007-05-17 17:00:00' BETWEEN `start` AND `finish`) AND resource_id = 1 What am I missing here I've used count before on different looking queries and it always returns 0 if its meant to.
VIEWS ON THIS POST

119

Posted on:

Thursday 25th October 2012
View Replies!

How to upgrade the user account

Hi every body.. I have this query: Code: update `o_subscription` set cdlevel=02 WHERE icactive = 'X' and dtexpire >= '2006-01-01' and cdlevel= 01 i want to upgrade the user whose account is expired in 2006-01-01 and greater date than 2006-01-01 Code: and dtexpire >= '2006-01-01' Will this query work.. i also want that upgrade the user level to coming level example is level 2 to level 3. i use this query with date .. >= what will this operators do.
VIEWS ON THIS POST

103

Posted on:

Thursday 25th October 2012
View Replies!

View counter

How can i count a view in MS ACCESS database, by that i mean when i create a custom view, every row to have Incremental Counter For example if i have table like ID:Autonumber, Text:text 1, foo 2, boo 3, goo 4, doo 5, hoo And use SQL: SELECT (!!\tRuntimeID!!\t), ID, Text FROM TableName Where ID > 2 To get something like this: RunTimeID, ID, Text 1, 3, goo 2, 4, doo 3, 5, hoo how can i make that RunTimeID column\t
VIEWS ON THIS POST

104

Posted on:

Thursday 25th October 2012
View Replies!

category item counting performance

Say you have 3 tables _categories cat_id title parent _articles article_id title description _article2category article_id category_id Note: articles can have multiple categories Now say you have 1000 categories 3-4 levels deep in the _categories table. Lets say there are 100,000 articles having an average of 3 categories each making _articles have 100,000 records. That makes _article2category have 300,000 records. Now, on one page we want to display a list of categories, and their immediate subcategories. This could be root categories or simply starting from a certain cat_id. I understand how to query to get this category tree. However say you want to display it like.. Category 1 (Articles: 25) Subcategory 1 (Articles: 10) Subcategory 2 (Articles: 5) Subcategory 3 (Articles: 10) Category 2 (Articles: 342) Subcategory 1 (Articles: 200) Subcategory 2 (Articles: 142) Notice the counts. With large amounts of data how can you query to get these counts efficiently for the entire tree of data without suffering a performance hit Because right now to get the count of articles in a category you need to count according to _article2category and use recursion to get the count for the categories children correct
VIEWS ON THIS POST

115

Posted on:

Thursday 25th October 2012
View Replies!

Getting counts of several joins without subqueries

Hi all, Suppose I have the following tables: Code: CREATE TABLE superheroes ( id UNSIGNED INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (100) ); CREATE TABLE teams ( id UNSIGNED INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (100) ); CREATE TABLE team_membership ( superhero_id UNSIGNED INT, team_id UNSIGNED INT, PRIMARY KEY (superhero_id, team_id) ); CREATE TABLE villains ( id UNSIGNED INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (100) ); CREATE TABLE battles ( superhero_id UNSIGNED INT, villian_id UNSIGNED INT, PRIMARY KEY (superhero_id, villian_id) ); And suppose I have the following data: Code: Superheros 1 - Wolverine 2 - Spider-Man 3 - Cyclops Teams 1 - X-Men 2 - X-Force 3 - Avengers 4 - Fantastic Four Team Memberships 1 - 1 1 - 2 1 - 3 2 - 3 2 - 4 3 - 1 Villains 1 - Dr. Doom 2 - Kang 3 - Apocalypse 4 - Doc Ock 5 - Magneto Battles 1 - 1 1 - 2 1 - 3 1 - 5 2 - 2 2 - 4 3 - 3 3 - 5 I want to write a query that would produce the following results: Code: Superhero | Num Teams | Num Battles Wolverine | 3 | 3 Spider-Man | 2 | 2 Cyclops | 1 | 2 It seems like this would be really easy to do using subqueries which is the way I'm doing it now: Code: SELECT s.name, (SELECT COUNT(*) FROM team_membership ts WHERE superhero_id = s.id) AS num_teams, (SELECT COUNT(*) FROM battles b WHERE superhero_id = s.id) AS num_battles FROM superheros s; But with a really large dataset, that takes a while to run. It seems like it would run faster if I could join the data together but I'm not sure how I could get the counts that way. Does anyone have a better way to do it
VIEWS ON THIS POST

116

Posted on:

Monday 29th October 2012
View Replies!

SQL-Query for updating a value based on timestamp of another table

I have the following problem: I want to update the column "UPDATE_VALUE" of Table A with values from the column "SOURCE_VALUE" from anotherTable B. Both tables have a timestamp column, and the value "update_value" that should be updated inTable A should be updated with the value "source_value" of the row of Table B with the highest timestamp smaller/equal than the timestamp of the row of table A. Here is my example: Code: create table if not exists TableA (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),time_a DATETIME DEFAULT NULL, update_value VARCHAR(39)); insert into TableA(time_a) value ('2010-09-23 10:00:00'); insert into TableA(time_a) value ('2010-09-25 05:20:00'); insert into TableA(time_a) value ('2010-09-25 18:10:30'); insert into TableA(time_a) value ('2010-09-27 07:04:30'); create table if not exists TableB (id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),time_b DATETIME DEFAULT NULL, source_value VARCHAR(39)); insert into TableB(time_b,source_value) value ('2010-09-23 00:00:00','VAL_1'); insert into TableB(time_b,source_value) value ('2010-09-24 12:30:00','VAL_2'); insert into TableB(time_b,source_value) value ('2010-09-24 04:50:00','VAL_3'); insert into TableB(time_b,source_value) value ('2010-09-25 12:00:00','VAL_4'); insert into TableB(time_b,source_value) value ('2010-09-25 12:20:00','VAL_5'); insert into TableB(time_b,source_value) value ('2010-09-26 01:50:00','VAL_6'); insert into TableB(time_b,source_value) value ('2010-09-26 02:00:00','VAL_7'); insert into TableB(time_b,source_value) value ('2010-09-27 14:00:00','VAL_8'); insert into TableB(time_b,source_value) value ('2010-09-28 22:00:00','VAL_9'); select * from TableA; +----+---------------------+--------------+ | id | time_a | update_value | +----+---------------------+--------------+ | 1 | 2010-09-23 10:00:00 | NULL | | 2 | 2010-09-25 05:20:00 | NULL | | 3 | 2010-09-25 18:10:30 | NULL | | 4 | 2010-09-27 17:04:30 | NULL | +----+---------------------+--------------+ select * from TableB; +----+---------------------+--------------+ | id | time_b | source_value | +----+---------------------+--------------+ | 1 | 2010-09-23 00:00:00 | VAL_1 | | 2 | 2010-09-24 12:30:00 | VAL_2 | | 3 | 2010-09-24 04:50:00 | VAL_3 | | 4 | 2010-09-25 12:00:00 | VAL_4 | | 5 | 2010-09-25 12:20:00 | VAL_5 | | 6 | 2010-09-26 01:50:00 | VAL_6 | | 7 | 2010-09-26 02:00:00 | VAL_7 | | 8 | 2010-09-27 14:00:00 | VAL_8 | | 9 | 2010-09-28 22:00:00 | VAL_9 | +----+---------------------+--------------+ So in row 1 of table A the I want VAL_1 of table B as the timestamp of this row is the highest of table B that is smaller/equal than the timestamp of the row in table A. For row 2 of table A I want row 3 of table B and so on. The complete result should look like this: Code: +----+---------------------+--------------+ | id | time_a | update_value | +----+---------------------+--------------+ | 1 | 2010-09-23 10:00:00 | VAL_1 | | 2 | 2010-09-25 05:20:00 | VAL_2 | | 3 | 2010-09-25 18:10:30 | VAL_5 | | 4 | 2010-09-27 17:04:30 | VAL_8 | +----+---------------------+--------------+ I have tried the following: Code: update TableA set update_value = (select source_value from TableB where TableA.time_a
VIEWS ON THIS POST

204

Posted on:

Monday 29th October 2012
View Replies!

Problem With MySQL Privileges

i am really fustrated over that issue..Here is the deal .. I log in as root at mysql command prompt ( for password i just hit enter ) , then i create USER A .. Then i create DATABASE A , Then i GRANT ALL PRIVILEGES ON DATABASE A TO USER A ... However when i login to phpmyadmin as USER A it says that i have no database privileges ... There is no way i can fix that i m trying 2 days now ... Can someone plz help me out Plz be detailed as possible. Thank you for your time BELOW IS A PHP FORUM ERROR WHEN TRYING TO INSTALL A fatal and unrecoverable database error has occurred. This may be because the specified user does not have appropriate permissions to CREATE TABLES or INSERT data, etc. Further information may be given below. Please contact your hosting provider in the first instance or the support forums of phpBB for further assistance. install_install.php [ 1193 ] SQL : CREATE TABLE phpbb_attachments ( attach_id mediumint(8) UNSIGNED NOT NULL auto_increment, post_msg_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, in_message tinyint(1) UNSIGNED DEFAULT '0' NOT NULL, poster_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, is_orphan tinyint(1) UNSIGNED DEFAULT '1' NOT NULL, physical_filename varchar(255) DEFAULT '' NOT NULL, real_filename varchar(255) DEFAULT '' NOT NULL, download_count mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, attach_comment text NOT NULL, extension varchar(100) DEFAULT '' NOT NULL, mimetype varchar(100) DEFAULT '' NOT NULL, filesize int(20) UNSIGNED DEFAULT '0' NOT NULL, filetime int(11) UNSIGNED DEFAULT '0' NOT NULL, thumbnail tinyint(1) UNSIGNED DEFAULT '0' NOT NULL, PRIMARY KEY (attach_id), KEY filetime (filetime), KEY post_msg_id (post_msg_id), KEY topic_id (topic_id), KEY poster_id (poster_id), KEY is_orphan (is_orphan) ) CHARACTER SET `utf8` COLLATE `utf8_bin` CREATE command denied to user 'administrator'@'localhost' for table 'phpbb_attachments'
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!

Inner joins or sub-querys, syntax help needed

ok i have this query which is working that select data from 3 tables SELECT user_id, matric_no, course_id, name, email, supervisor, project_id, status FROM (jos_main_students LEFT JOIN jos_users ON user_id = jos_users.id) LEFT JOIN jos_main_allocations ON student_id = jos_users.id; However course_id is the PK of another table called _main_course and id like to select a field called title from _main_course and have the title of the course display in place of just its course_id. I would also like to do the same with supervisor which is actually the id of another user in the jos_users table so id like their name to be displayed in place of just the id. I cant quite firgure out the complex synatax needed to do the inner joins or sub_querys required here. cheers
VIEWS ON THIS POST

179

Posted on:

Monday 29th October 2012
View Replies!

Joins on Large Table

I currently have 2 tables that are used for a select query with a simple join. The first table houses around 6-9 million rows, and this gets used as the join. The primary table is anywhere from 1mil to 300mil rows. However, I notice when I join above 10mil rows on the primary table the select query goes from instant to very slow (3+ seconds and grows). Here is my table structure and queries. CREATE TABLE IF NOT EXISTS `links` ( `link_id` int(10) unsigned NOT NULL, `domain_id` mediumint(7) unsigned NOT NULL, `parent_id` int(11) unsigned DEFAULT NULL, `hash` int(10) unsigned NOT NULL, `url` text NOT NULL, `type` enum('html','pdf') DEFAULT NULL, `processed` enum('N','Y') NOT NULL DEFAULT 'N', UNIQUE KEY `hash` (`hash`), KEY `idx_processed` (`processed`), KEY `domain_id` (`domain_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; CREATE TABLE IF NOT EXISTS `domains` ( `domain_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(170) NOT NULL, `blocked` enum('N','Y') NOT NULL DEFAULT 'N', `count` mediumint(6) NOT NULL DEFAULT '0', `mcount` mediumint(3) NOT NULL, PRIMARY KEY (`domain_id`), KEY `name` (`name`), KEY `blocked` (`blocked`), KEY `mcount` (`mcount`), KEY `count` (`count`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10834389 ; Query: (SELECT link_id, url, hash FROM links, domains WHERE links.domain_id = domains.domain_id and mcount > 1 and processed='N' limit 200) UNION (SELECT link_id, url, hash FROM links where processed='N' and type='html' limit 200) Explain select: Code: +----+--------------+------------+-------+-------------------------+---------------+---------+---------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+------------+-------+-------------------------+---------------+---------+---------------------------+---------+-------------+ | 1 | PRIMARY | domains | range | PRIMARY,mcount | mcount | 3 | NULL | 257673 | Using where | | 1 | PRIMARY | links | ref | idx_processed,domain_id | domain_id | 3 | crawler.domains.domain_id | 1 | Using where | | 2 | UNION | links | ref | idx_processed | idx_processed | 1 | const | 7090017 | Using where | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+------------+-------+-------------------------+---------------+---------+---------------------------+---------+-------------+ Right now, I'm trying a partition with 20 partitions on links using domain_id as the key. Any other options would be greatly appreciated.
VIEWS ON THIS POST

55

Posted on:

Monday 29th October 2012
View Replies!

Index information in query results?

, Basically I'm trying to get an idea of how effective an index on a table will be versus the memory it consumes. What I'm interested in getting is some kind of data back about how the index was used when I do certain queries. For example, if I do a select for table A on column B, I'd like to see that the index was used to find all table A rows with a column B value of X. Preferably I'd like to be able to see what portion of a query's execution time is spent performing index look-ups, or the cost of altering the index when inserting/updating a row. Is it possible to do this in MySQL Currently the only method I can think of is just creating a set of test-queries and measuring the time with or without a particular index myself, to see what kind of difference it makes. This isn't really preferred though as it's quite complicated for me to set such a thing up at the moment. As a side-question for my particular case; if I have a primary key index defined for columns A, B, and C of a table, will that help to optimise queries that are matching only on column B, or is a separate index required to improve that case
VIEWS ON THIS POST

129

Posted on:

Monday 29th October 2012
View Replies!

\ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

just five minutes before mysql was working fine... in directory /var/lib/mysql i renamed a database. now it is giving the following error... \ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Please somebody help.... Im working in Fedora 8
VIEWS ON THIS POST

199

Posted on:

Monday 29th October 2012
View Replies!

Datetime

I issued the following queries: Quote: mysql> INSERT INTO absence (`tabsstart`) VALUES('2008-09-09 12:06:00'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM `absence` WHERE date(`tabsstart`) = '2008-09-09'; Empty set (0.10 sec) This is just a great mystery. The field tabsstart is a datetime field and I cannot select based on a date. I also tried Quote: SELECT * FROM `absence` WHERE date (`tabsstart`) = STR_TO_DATE('2008-09-09', '%Y-%m-%d') Any suggestions
VIEWS ON THIS POST

103

Posted on:

Monday 29th October 2012
View Replies!

Replace queried mysql results with other values

This query will replace values in a single column with pre-defined values: PHP Code: selectUser_ID,casewhenAnswer_Number_1=1then'cat'whenAnswer_Number_1=2then'dog'whenAnswer_Number_1=3then'fish'endasanswer fromtablenamewhereUser_ID='whatever'; That works great for 1 column, Answer_Number_1. But, if I have 25 Answer_Number_# columns, it doesn't work so well. Every value for an Answer_Number column will be a 1,2,or 3 so I need something that will account for all the columns and output cat, dog, or fish instead of 1,2, or 3. Any suggestions would be greatly appreciated.
VIEWS ON THIS POST

164

Posted on:

Monday 29th October 2012
View Replies!

Must this join be done twice?

Do I really need to inner join the "profiles"-table twice to get each username, or is there a more elegant solution SELECT m.msg_owner, m.msg_from, p1.usr_userid, m.msg_to, p2.usr_userid FROM mail2_inbox AS m INNER JOIN profiles AS p1 ON p1.usr_id = m.msg_from INNER JOIN profiles AS p2 ON p2.usr_id = m.msg_to "mail2_inbox"-table contains the owner, from and to for the message, the corresponding usernames is retrieved from the "profiles"-table.
VIEWS ON THIS POST

72

Posted on:

Monday 29th October 2012
View Replies!

Faster way to migrate database

Greetings, I am migrating a MySQL database to a new server, and currently using Navicat to transfer the database directly from the old server to the new server. However, this is taking way too long because it took more than 24 hours to transfer one table with the size of 9.2 MB. Do anyone of you have any suggestion on how can i migrate the database in a faster way The size of the database if only 2.4 GiB. Thank you for your help in advance.
VIEWS ON THIS POST

109

Posted on:

Monday 29th October 2012
View Replies!

USA zip, longitude and latitude table

Hi is there any table available for FREE which has USA zip, longitude and latitude values I have one but it has many missing values of lon and lat for many zip codes so wanted to get a reliable one. so please let me know if you know any such table or xml file etc from where I can get these values. thanks , thanks for your replies.... I have downloaded one from site which was gathered in 2004.. it is working however it has some lon lan values missing for few cities... but it is FREE
VIEWS ON THIS POST

89

Posted on:

Monday 29th October 2012
View Replies!

how to install PHPbb2 on mysql 5+ (was "Grrrr")

I am sooooo new to the net world I am runnin WAMP Server. I need to know how to install PHPbb2 on mysql 5+ The problem i am haveing is that PHPmyAdmin is saying that i have a database but no tables..... when i trie to install phpbb2 on this data base it says i do not have access to the database.... can anyone help me.... all this is on win 32x
VIEWS ON THIS POST

135

Posted on:

Monday 29th October 2012
View Replies!

Mysql timestamp randomly set to all zero's

, I'm using MySQL 4.0.24 I have three tables in a database that has a timestamp field as such: Field Type Length unsigned zerofill binary Null Key time timestamp 14 not set not set not set YES blank Default Extra NULL None This has been working fine for the last few months, with a Java Applet using the field via max(time) with no problems. In the last few days, users have been using the applet which inserts records into these tables, and on random occasions the timestamp is set to all zeros, so instead of 20060524123455 I get 00000000000000. Now the max(time) shouldt access these records, but they get pulled out and the wrong records are being displayed to the wrong people, even though I have other checks in place such as username and usergroup confirmed. So my question is; 1) Is this a known server-side problem with SQL. Are zero's set for illegal timestamp entries 2) Is there anything on the server i can change to prevent this 3) Or will i have to change my code to insert a java time into a seperate column and not use timestamp This is critical as users can view other people's data on random occassions.
VIEWS ON THIS POST

97

Posted on:

Monday 29th October 2012
View Replies!

No Database selected why?

The code below says no database selected but there is what am I doing wrong PHP Code: $db=mysql_connect("localhost","user","pass");\t mysql_select_db('data');\t $result=mysql_query("INSERTINTOtable(fname,lname,address, city,province,postal,telephone,email,manufacturer,model,year, kilometers,description,price,username,password)\t \t\t\t\t\t\t\t\tVALUES('$fname','$lname','$address','$city', '$province','$postal','$telephone','$email','$manufacturer', '$model','$year','$kilometers','$description','$price', '$username','$password')");\t if(!$result)\t {\t \techo"Somethingnotadded:",mysql_error();\t \texit;
VIEWS ON THIS POST

107

Posted on:

Monday 29th October 2012
View Replies!

Need help understanding foreign keys

I am confused about inserting values into tables with foreign keys. When i have 2 foreign keys i don't know if i have to add numbers in or do i leave it blank. Also do i also need to create tables that the foreign keys are related to first eg of what i have done so far is: actorid int(5) not null auto-increment, actorname varchar(255), filmid int(5), genreid int(5), preferenceid int(5), primary key (actorid), foregin key (filmid) references actor(filmid), foregin key (genreid) referenced director(genreid), foregin key (peferenceid) referenced director(preferenceid) ); insert into actor (actorid, actorname, filmid, genreid, preferenceid) values ( any help is appreciated
VIEWS ON THIS POST

90

Posted on:

Monday 29th October 2012
View Replies!

Proper way to handle old or disabled data

there. I have a table of profiles that users create (they can create more than one). At some point, they may want to delete that profile, however, we don't want to delete the data, we'd rather disable it. (the info will be important to us later down the line). I'm not sure which is the better option: A. add a status column to the 'profileList' table and when they want to "delete" the alert profile, I just set the status to: 0 B. create a second table with the same structure called "profileList_Archive" and insert the disabled row into that table first then delete it from the live table Just wondering if all those disabled alerts in the same table as the active alerts will cause performance issues down the line. This table will be probably(hopefully) have grow very large over time.
VIEWS ON THIS POST

91

Posted on:

Monday 29th October 2012
View Replies!

Right Join

Code: $sql="SELECT MAX(mr.messageId) AS maxMessageId, mr.threadId FROM messages_recipients AS mr RIGHT JOIN thread_recipients AS tr ON tr.threadId=mr.threadId WHERE mr.recipientUserId='2' GROUP BY mr.threadId"; If the above SELECT should find a row in message_recipients with a threadId of 1 and thread_recipients has multiple rows with threadId=1.. then because I have a RIGHT JOIN, I would expect it to return as many rows as the number of rows in thread_recipients where threadId=1 It is however only returning one row regardless... Can you tell me why it is doing this... Kind regards J
VIEWS ON THIS POST

60

Posted on:

Monday 29th October 2012
View Replies!

Sort of Like Distinct

Is there a way to retrieve a set number of duplicate results For example, I have a calendar application and I want to retrieve only 4 results per day. The data is like this: Woo, 6 Yes, 6 Foo, 6 Bar, 6 Yay, 6 where those 5 entries are from the 6th of the month, so basically I want to limit each distinct date to 4 results. Any clues
VIEWS ON THIS POST

61

Posted on:

Monday 29th October 2012
View Replies!

Selecting a set of unique values

I've tried a couple google searches but can't seem to find my answer. I need a select query that returns a set of the unique values in a given column. Example, I have table job and column sales_rep_id and I want to select all of the unique values for sales_rep_id. So if there are 10 sales reps who have jobs in the table I just want a set of the sales reps ids.
VIEWS ON THIS POST

233

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Slow queries (40+ seconds)

I have done some homework on DRBD. I have set 'syncer' to 100M in my config. At this page: http://www.drbd.org/users-guide/s-configure-syncer-rate.html They recommend 33M for a gigabit connection. Since I only have 100Mbit, I would set this to 3M right Maybe this is the problem... hmm Edit: it didn't help. I'm out of ideas , I got recommended 'iperf' to debug network speed, and got this result: Code: [ ID] Interval Transfer Bandwidth [ 3] 0.0-10.0 sec 115 MBytes 96.0 Mbits/sec But with DRBD I only get 69 Mbit Could that be correct I have talked to the hosting company and they didn't have the nic's in stock so I have to wait with that solution. I'll try changing the parameters, get back soon with results
VIEWS ON THIS POST

85

Posted on:

Monday 29th October 2012
View Replies!

Backing up databases

I am new to all this, so I am using webmin for the moment. Just want to backup all my SQL databases before I go and reformat all the tables and probably make a mess of them. So I go to MySQL Database Server - MySQL version 5.0.45 - and choose 'backup databases'. On this page I select the target directory, a compression method (I have tried both gzip and none) and click 'save and backup now'. I get: Successfully backed up 1223 bytes from database information_schema to file //information_schema.sql. Successfully backed up 126351 bytes from database forums to file //forums.sql. Successfully backed up 1209 bytes from database test to file //test.sql. Scheduled backup for database left disabled. This all comes up v. quickly and the contents have the error msg: mysqldump: Got error: 1016: Can't open file:***** (errno: 24) when using LOCK TABLES - Looked around and it seems that this issue has something to do with open_files_limit setting but when I try to change that I get: SQL set global open_files_limit = 2048 failed : Unknown system variable 'open_files_limit' TIA Ed
VIEWS ON THIS POST

206

Posted on:

Monday 29th October 2012
View Replies!

Select / join question

! i have "methods" and "projects", the two tables are m-to-m related, via the relational table "methods_projects". i would like to have the full methods table selected, and for each methods, the row(s) from the relational table matching a specific project_id value. I tried this: Code: SELECT m.method_id, m.title, cp.method_project_id AS target_id, cp.application_title, cp.project_id FROM m2_methods AS m LEFT JOIN m2_methods_projects AS cp ON ( m.method_id = cp.method_id AND cp.project_id = '1' ) it kind of does what i want, but it only lists one of the relational entries per method, instead of listing them all. what am i doing wrong
VIEWS ON THIS POST

105

Posted on:

Monday 29th October 2012
View Replies!

acess another sql server

How can I access another sqlserver database table with a SQL statement In the same database, one can access by ..
VIEWS ON THIS POST

79

Posted on:

Wednesday 7th November 2012
View Replies!

Selecting the results of an INSERT command

Just as the title says. How do I get values of fields of an INSERT command i.e I perform an INSERT command, and then I want to find the value of the ID in that field which is processed by the server (it's an AutoNumber field). I've looked on http://www.w3schools.com/sql and it doesn't say anything about that. Please Help!
VIEWS ON THIS POST

76

Posted on:

Wednesday 7th November 2012
View Replies!

Add a new record based upon the last record in the same table

OK I need a stored procedure that allows the user to get the values from the last record in a table and then insert a new record in the same table using those values. This will allow the user to use the values as a template for the next record and then they can change some of the values later. So when the user presses use the last data it populates the new table row with the data from the last record the table structure is like Id int (auto increment) value1 value2 value3 value4 .... value60
VIEWS ON THIS POST

92

Posted on:

Saturday 10th November 2012
View Replies!

Select multiple tables..

PHP Code: details="SELECT\tweeklyreport.Supp_name,weeklyreport.PartNumber,weeklyreport.Description,cbom_filtered.comCode,cbom_filtered.UploadTime,supplier_feedback2.DateNowfromweeklyreport,cbom_filtered,fa,supplier_feedback2whereProjectName='"&pro&"'" error on the ProjectName is ambiguous... how do i select the value from those 4 tables based on the projectName\t
VIEWS ON THIS POST

112

Posted on:

Saturday 10th November 2012
View Replies!

Strage qurey needed

I need to come up with a qurey that im having trouble with. I need to pull all emails of users who are not kicked out. There are two tables one is ws_member the other is banned. I dont know why the head programer set it that way but thats what i got to work with. I need a qurey that will pull all the emails that are in ws_member that are not in banned. Any suggustions
VIEWS ON THIS POST

130

Posted on:

Saturday 10th November 2012
View Replies!

Clean a transaction log file

Originally posted by : Alex (AlexLut@aol.com),I need to clear a log file in SQL Server 2000. I know that I can shrink and remove any unused space and etc. My problem is that a log file has a lot of old transactions and I would like to not only shrink the file but also clean it. Let's say my file right now is 20mb and I want to make it back to 2mb. Thank you in advance.
VIEWS ON THIS POST

79

Posted on:

Sunday 11th November 2012
View Replies!

Multiple values in queries - please help!

A couple of questions... I'm using ASP VBScript with MS Access. I have a company table of food producers. Each producer belongs to various product categories, not just one. I need to create a "search" form where the user can ctrl+click many categories from a listbox and then receive all the companies that belong to those categories, on the response page. I've tried it, but I get this error msg: "[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'CompanysCategories.SubCategoryID = 11, 3'." This is the results of clicking two categories from the listbox. And second - what do I need to do when I want to INSERT a company INTO the database with multiple product categories (= many selections from a listbox) The tables I've got are basically like this: Companies (CompanyID, and the details etc.) CompanysCategories (CCid, CompanyID, SubCategoryID) SubCategories (SubCategoryID, and the details etc.) The SQL Query is here: (note! I've also got other fields in the search form, but this is just for testing the listbox thing) Code: SELECT CompanysCategories.CompanyID, CompanysCategories.SubCategoryID, etc FROM CompanysCategories INNER JOIN Companies ON CompanysCategories.CompanyID = Companies.CompanyID WHERE CompanysCategories.SubCategoryID = fromform the fromform is obviously Request.Form("prodlistbox").
VIEWS ON THIS POST

171

Posted on:

Monday 19th November 2012
View Replies!

DISTINCT truncating a memo field

, I've created an .asp page that returns results from a memo field...I need to use Select DISTINCT, but when I do, the memo field gets truncated. When I remove the DISTINCT, the full memo field is displayed, but of course, I get too many records. Has anyone encountered this before
VIEWS ON THIS POST

66

Posted on:

Tuesday 20th November 2012
View Replies!

DSN, Storing variable permanently

Originally posted by : thunderain (lbrennan1@sympatico.ca)I have Windows 98. My two databases is set up in Control Panel, ODBC Data Sources, User DSNMy program will be using two databases. I am using a drop down menu for the user to pick which database to work with. Once the database is picked, they will be using it on all other pages. After selecting the database, I need to store the variable somewhere to access it on all other pages. I have used global.asa to store variables that are hardcoded in and that works find. Reading up on it, i don't see how to send a variable to global.asa to store it, or for that matter if you can.Can you send a varible to global.asa to be stored and used in other pagesIf yes, howShould I put it in an asp include, I am going in that direction Here is my code.------selectdatabase.asp-------- Database: GateKeeper GKAdmin ----Choose One------ ! ----------database-var.asp----------1/ It seens to be selecting the database ok. There are no errers, but I am not getting anything from Response.write username2/ When the database is selected, how do i store it permanently while the user goes to other pages and call it from other pagesThank you, Larry
VIEWS ON THIS POST

67

Posted on:

Tuesday 20th November 2012
View Replies!

Problems calling stored proc's through an ole-db drivers.

Originally posted by : Gerry...Problems calling stored proc's through an ole-db drivers. Gives me errors..
VIEWS ON THIS POST

87

Posted on:

Tuesday 20th November 2012
View Replies!

sql syntax error.. dont know why.. help

this is my stored procedure.. when i check the syntax in the stored procedure window it tells me syntax ok... Code: CREATE PROCEDURE sonia @OptionSelected varchar (500) AS DECLARE @SQL varchar(600) DECLARE @Live varchar(40) SET @Live = Convert(varchar,GETDATE(),110) SET @SQL = 'CREATE VIEW (@Live) AS SELECT dbo._students.*, ( ' + @OptionSelected + ')as test, dbo.studentSurveyValues.* FROM dbo._students INNER JOIN dbo.studentSurveyValues ON dbo._students.sid = dbo.studentSurveyValues.studentid' EXEC(@SQL) GO when i run it in query analyzer with the syntax below Code: execute sonia 'modegender' i get this error message in the query analyzer pane.. Code: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@Live'. as you can see i am trying to create a view and i want to add the date to the name can someone tell me why
VIEWS ON THIS POST

222

Posted on:

Monday 26th November 2012
View Replies!

PostgreSQL - what are the best resources on the net?

PostgreSQL -what are the best resources on the net Can anybody submit good links, tutorials, links to references, and recommended books
VIEWS ON THIS POST

157

Posted on:

Sunday 30th December 2012
View Replies!

Update MySQL table from another table

I inherited a database from an old project and would like to make the data more robust. Currently it stores a group of Jobs and each job has a customer listed, however the customer name is stored as a string value and there is a separate "Customer" table that I would prefer to instead of a join of the customer number rather than their name as this causes the data to not be as clean as the user could enter in whatever they like so we could have jobs listed under "ABC Widgets" and "ABC Widgets, Co" and they would appear to be 2 different customers. I've created a new field in the Jobs table to store the customer ID from the customer table, what I am now trying to figure out is how best to build the query to populate this new field for all the existing jobs (I'll be rewriting the other parts of the code to utilize customer numbers for additions/updates). So from my PHP Admin what would the query be to place the customer ID from the Customers table into the Jobs table UPDATE 'Jobs' SET CustNum = Customer.ID WHERE Customer.Name = Jobs.CustomerName is where I am starting, but I think I am missing something. Most likely a JOIN (which I am still having some trouble fully grasping). Or is a JOIN even required since I can just call from the different tables I want this to iterate through all the jobs and if it can't find a match, then just leave the CustNum field blank so I can manually resolve those. Greg
VIEWS ON THIS POST

84

Posted on:

Monday 31st December 2012
View Replies!