I got this table in my MySQL database, 'users'. It has the fields 'id' and 'value'. Now, I want to update lots of rows in this table with a single SQL query, but many rows should get a different value. Currently, I'm using this: UPDATE users SET value = CASE id WHEN 1 THEN 53 WHEN 2 THEN 65 WHEN 3 THEN 47 ...
Hello everyone, This is a "web dev best practice" question. I am envisioning a website where users would sign up with a username they would select (revolutionary, I know). They would also enter a valid email address. I would test for the uniqueness of those in the db before allowing the sign-up. The site is non-commercial in nature,...
I have two mysql tables: Item containing items that one can buy: CREATE TABLE `item` ( `itemid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`itemid`) ) ENGINE=InnoDB; Purchase containing all purchases: CREATE TABLE `purchase` ( `purchaseid` int(11) NOT NULL AUTO_INCREMENT, `date` date DEFAU...
Are MySQL operations multithreaded? Specifically, on running a select, does the select (or join) algorithm spawn multiple threads to run together? Would being multi-threaded prevent being able to support a lot of concurrent users? ...
I am creating a social network and want to have a similar photo gallery to that of facebook. Im guessing I need to use AJAX but I would like to have a comments section for each photo. I was just wondering what the best way would be to design a database around this. Do I just make a table of comments? Would I run into performance issu...
I'm learning ASP.NET MVC but I prefer to use OS X and MonoDevelop(especially Mono 2.8 is out now, supports ASP.NET MVC 2). Is there an ASP.NET MVC book that also teach how to use NHibernate and Postgresql or MySQL? (No Sql Server, as that would entail me installing vmware/parallels in OS X) ...
If I have a table on my DB called product_tags with 2 fields: tag_id and tag_name Here is the schema: CREATE TABLE `product_tags` ( `tag_id` int(11) NOT NULL auto_increment, `tag_name` varchar(255) NOT NULL, PRIMARY KEY (`tag_id`), UNIQUE KEY `tag_name` (`tag_name`) ) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=utf8 Say here...
I am trying to find out where this particular player ranks among shooting guards in the NBA. I am using this post on stackoverflow for a guide. I get the error "Invalid use of group function". SELECT first, last, team, pos, SUM(points) AS scoresum, ROUND(AVG(points), 2) AS avgpoints, (SELECT COUNT(*) FROM nba...
We manage a website which gives agents the ability to enroll members across the country. At times, the member or agent will enter a bad email address during the enrollment process and important PDF'd information cannot be delivered to the member. Similarly, an agent may try and recruit a sub-agent and have the same issue. Is there any...
I have a Mysql Recordset that I have put into an associative array so that I can reuse it over and over. I used this function to put the values into the array: while(($Comments[] = mysql_fetch_assoc($rsComments)) || array_pop($Comments)); Here is what the print_r($Comments) displays Array ( [0] => Array ( [CommentID] => 10 [Comment]...
I'm sorting through emails looking for specific instances of strings and it is taking too long. I would like to get the time down to half a second per email, and it's currently taking about 2-2.5 seconds per email. I'm worried I'm doing something really dumb that's slowing this down - probably with mysql or zend_email. What this cod...
I have a mysql database with several tables. I have an input that makes ajax calls for every character. Is there a way to load balance by distributing to other domains etc? Estimated statistics: ~1000-2000 hits a day. Average site time per user ~30-60 secs. ...
Is there a way to accomplish this? INSERT IGNORE INTO some_table (one,two,three) VALUES(1,2,3) ON DUPLICATE KEY (INSERT INTO audit_table VALUES(NOW(),'Duplicate key ignored') I really don't want to use PHP for this :( Thanks! ...
Basically what happens is this: A person goes to a specific gallery, say GalleryID=42. I do a query to grab all of the images in that gallery (with the value of GalleryID=42), and do a separate query to grab all of the comments associated with that gallery (for example GalleryID=42). There may only be 4 comments total on 3 different p...
Hi, I'm currently debugging a huge MySql call which joins a large amount of tables which share column names such as id, created_at, etc. I started picking it apart but I was wondering if there was a way to do something like: SELECT * AS table.column_name FROM table1 LEFT JOIN etc etc etc... In place of having to individually name col...
I have been trying to figure out a different way to complete this task on another question on this website, but think maybe I am making it too difficult. Here is what I have: Table with, and ImageID, ImageName, GalleryID Another Table with Comments, Author, Date, ImageID What I want to do is do a query where I find all of the Images th...
Hello, Lets say, i have the following table | start | end | activity +---------------------+---------------------+--------- | 2010-07-26 09:10:00 | 2010-07-26 09:21:00 | 6 | 2010-07-26 09:35:00 | 2010-07-26 09:47:00 | 5 | 2010-07-26 10:05:00 | 2010-07-26 10:45:00 | 5 | 2010-07-26 10:50:00 | 2010-07-26 11:2...
In the sql below I have the last 5 posts. But sometimes these posts are from the same author. I want to select the last 5 but just one per author. SELECT `e` . * , `f`.`title` AS `feedTitle` , `f`.`url` AS `feedUrl` , `a`.`id` AS `authorId` , `a`.`name` AS `authorName` , `a`.`about` AS `authorAbou...
So, I have a table with column "ABC" is the timestamp, "BCD" is datetime. If I do this: SELECT * FROM myTable WHERE ABC > BCD is it bad? and will it affect performance? also, what is the performance between timestamp and datetime? ...
I have a situation where I'm building a new list based on sort values on another column. For example table: products id, name, sort_order, expiration_date table: lists id, product_id Let's say I want to always have 15 products in the lists table and in order. However, products expire, and when this happens they are being removed from...