i have table returned by select,how to calculate sum of field2,where f1 is simillar?
f1 f2 guest 100 tester 0 user 200 user 200 desired result: f1 f2 guest 100 user 400 tester 0 ...
mysql
how to analyze Query CPU time
Hello, How can I analyze the CPU time consumed by my Innodb queries? I have innotop, but I cant seems to find where it is. Thanks. ...
What's wrong with this MySQL Stored Function?
Having trouble getting this to apply in MySQL Workbench 5.2.15 DELIMITER // CREATE DEFINER=`potts`@`%` FUNCTION `potts`.`fn_create_category_test` (test_arg VARCHAR(50)) RETURNS int BEGIN DECLARE new_id int; SET new_id = 8; RETURN new_id; END// The actual function will have a lot more between BEG...
Use :include statement to find records
I'm working on Ruby On Rails 2.3.2 and I'm learning how to use the :include statement. I'd like to get all the announcements order by their rate. I've got two models and tables for that: Announcement and Rate. The models look like this: class Announcement < ActiveRecord::Base belongs_to :rate end class Rate < ActiveRecord::B...
mysql php connection problem in windows 7
i install windows7 ultimate and php 5.3.1 and mysql server 5.1 mysql server work fine from mysql> and if see phpinfo(); i get the following table mysql MySQL Support enabled Active Persistent Links 0 Active Links 0 Client API version mysqlnd 5.0.5-dev - 0811...
mysql 'WHERE' with 500 'OR's ?
Is it ok to have a WHERE statement with a lot of OR's? I imagine the most could ever be 500. I am dealing with a facebook app. Understandably (but annoyingly) they do not allow app developers to store friend relationships. As a result I can not do a sensible JOIN tblfriend or something along those lines. To get a list of friends you ca...
Transaction rollback doesn't work
Hi all! I have made a database wrapper with extra functionality around the PDO system (yes, i know a wrapper around a wrapper, but it is just PDO with some extra functionality). But i have noticed a problem. The folowing doesn't work like it should be: <?php var_dump($db->beginTransaction()); $db->query(' INSERT INTO test (data) VA...
Web app stress test
I am using pylot to stress test a web app I created. The test simulates 100 users logging in the app and the test lasts 200 secs. On average, approx. 4800 requests are made to the server and same number of connections to MySql, each of which executes 5 queries so we are talking about 24K queries during the test. The queries are simple a...
Can I create view with parameter in MySQL?
I have a view like this: CREATE VIEW MyView AS SELECT Column FROM Table WHERE Value = 2; I'd like to make it more generic, it means to change 2 into a variable. I tried this: CREATE VIEW MyView AS SELECT Column FROM Table WHERE Value = @MyVariable; But mysql doesn't allow this. I found an ugly workaround: CREATE FUNCTION Ge...
MySQL Get last date in each month from column of dates
An example is shown below; imagine each commar separated date is a row in the database Input: - 2010-01-11, 2010-01-18, 2010-01-25, 2010-02-01, 2010-02-08, 2010-02-15, 2010-02-22, 2010-03-01 it should return Ouput: 2010-01-25, 2010-02-22, 2010-03-01 The output is derived by getting the last date in the month, note for March there is o...
MySQL fulltext search - Only results that contain all words.
Hello, With the following query I get results that contain the words "International" AND "Shipping" and I also get results that contain "International" OR "Shipping". What can I do to ensure that the results contain both words and not just one of them? Any help would be greatly appreciated, thanks! SELECT client_company,client_descri...
Schema for many to many in MySQL?
Hello, Now i have 3 tables which are: CREATE TABLE IF NOT EXISTS experience( experience_id int(11) NOT NULL AUTO_INCREMENT, salarie_id int(11), consultant_id int(11), post varchar(255), entreprise_name varchar(255), start_time varchar(255), end_time varchar(255), description varchar(10000), PRIMARY KEY(experi...
App Java and hosting mysql
I have a Java application and I have to connect to a MySQL DB host in aruba.it. If I make a connection, aruba.it refuses that. How to solve this? ...
MySQL - Getting summary of multiple grouped rows ?
2 tables: owners & cars An owner can have many cars. A car can be marked as usable_offroad, usable_onroad, or both. The cars table has usable offroad and usable_onroad fields which can be set to 0 or 1 (no or yes) Consider the following query: SELECT * FROM owners LEFT JOIN cars on cars.owner_id = owners.id GROUP BY owners.id ORDER ...
mysql order by count
I have a table with store IDs and the city where the store is located. I want to list all the stores starting with the stores that are in the city where there is the most stores. TABLE ID CITY 1 NYC 2 BOS 3 BOS 4 NYC 5 NYC The output I want is the following since I have the most stores in NYC, I want all the N...
MySQL working with 127.0.0.1 but not working with localhost?
I have a strange problem in php MySQL: php connects with 127.0.0.1 but not with localhost. How to solve this issue? ...
How should I ethically approach user password storage for later plaintext retrieval?
As I continue to build more and more websites and web applications I am often asked to store user's passwords in a way that they can be retrieved if/when the user has an issue (either to email a forgotten password link, walk them through over the phone, etc.) When I can I fight bitterly against this practice and I do a lot of ‘extra’ pr...
MySQL - Reporting using a week to represent all rows with a datestamp in that week
I am trying to build a table of data where each row represents a week (i.e., 09/23-09/30). Each row is built from data from several different rows that have datestamps within that week period. The rest of the columns are subselects that typically give averages of all the data in all the rows in the week period. Logically, what is the be...
Mysql Real Escape String PHP Function Adding "\" to My Field Entry
Hello, I am submitting a form to my mySql database using PHP. I am sending the form data through the mysql_real_escape_string($content); function. When the entry shows up in my database (checking in myPhpAdmin) all of my double quotes and single quotes are escaped. I'm fairly certain this is a PHP configuration issue? so: $c...
Need a zip locator feature on a site. Where to start?
Hey guys! So I have been tasked to create a site which has a dentist locator based on the zip code inputted. There will be 8 dentists that will be available. I will probably set up a table in a mysql database to hold the information on the dentists (more will be added in the future). I just don't really know how to get started. I kno...