Dev Shed Forums - MySQL Help http://forums.devshed.com/ MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS). en Sun, 22 Oct 2017 01:37:52 GMT vBulletin 60 http://forums.devshed.com/images/misc/rss.png Dev Shed Forums - MySQL Help http://forums.devshed.com/ How can I update this query so not only I get unique ip, I also get unique email http://forums.devshed.com/mysql-help/979259-update-query-unique-ip-unique-email-new-post.html Mon, 16 Oct 2017 02:06:16 GMT Hi; Basically, I want to remove duplicate emails from the results. How can I update this query so not only I get unique ip, I also get unique email? Thanks Code: --------- SELECT * FROM analytics Hi;

Basically, I want to remove duplicate emails from the results. How can I update this query so not only I get unique ip, I also get unique email?

Thanks
Code:

SELECT  *
FROM    analytics
WHERE    email NOT IN (list of some emails)
AND      plc_url = 'plc1'
GROUP BY ip

Code:

CREATE TABLE `pum_sequence` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `date_added` date DEFAULT NULL,
  `passed_to_ontraport` int(11) DEFAULT '0',
  `ontraport_user_id` int(11) DEFAULT NULL,
  `opt_in_for_this_offer` int(11) DEFAULT '0',
  `plc_1_visited` int(11) DEFAULT '0',
  `plc_2_visited` int(11) DEFAULT '0',
  `plc_3_visited` int(11) DEFAULT NULL,
  `plc_1_number_of_clicks_from_email` int(11) DEFAULT '0',
  `plc_2_number_of_clicks_from_email` int(11) DEFAULT '0',
  `plc_3_number_of_clicks_from_email` int(11) DEFAULT NULL,
  `plc_1_tagged_active` int(11) DEFAULT '0',
  `plc_2_tagged_active` int(11) DEFAULT '0',
  `plc_3_tagged_active` int(11) DEFAULT NULL,
  `plc_1_number_of_clicks_from_top_links` int(11) DEFAULT NULL,
  `plc_2_number_of_clicks_from_top_links` int(11) DEFAULT NULL,
  `plc_3_number_of_clicks_from_top_links` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) EN

]]>
MySQL Help English Breakfast Tea http://forums.devshed.com/mysql-help-4/update-query-unique-ip-unique-email-979259.html
Storing medical ailments in a database Databases http://forums.devshed.com/mysql-help/979193-storing-medical-ailments-database-databases-new-post.html Sat, 07 Oct 2017 21:52:03 GMT Hello guys I am trying to find the best method to store medical ailments , like Diabetes, Hypertension, Cancer etc in a database. My form will list over 200 ailments and the user will check those that affect him. I could serialize those selected and then store it in a database but I want to be able to use the information at a later stage. For example I want to know what ailments are... Hello guys

I am trying to find the best method to store medical ailments , like Diabetes, Hypertension, Cancer etc in a
database.

My form will list over 200 ailments and the user will check those that affect him. I could serialize those
selected and then store it in a database but I want to be able to use the information at a later stage.
For example I want to know what ailments are selected with Diabetes and Hypertension.
Does this mean I will have to create a field for each ailment in my database ?

I am confused here.

I would appreciate any assistance.

Thanks
Kimmy ]]>
MySQL Help nivashni1234 http://forums.devshed.com/mysql-help-4/storing-medical-ailments-database-databases-979193.html
Suggested settings for MySql http://forums.devshed.com/mysql-help/979191-suggested-settings-mysql-new-post.html Sat, 07 Oct 2017 13:25:37 GMT Hi. All Was hoping to get some suggestions as to MySql variable settings based on the following: 1. System Ram: 2G, Database Engine: ISAM 2. Tables: a: 20,000 rows, Size on disk: 250MB b: 20,000 rows, Size on disk: 13MB The entire DB will fit in memory 7-8 times over............ Hi. All

Was hoping to get some suggestions as to MySql variable settings based on the following:

1. System Ram: 2G, Database Engine: ISAM
2. Tables:
a: 20,000 rows, Size on disk: 250MB
b: 20,000 rows, Size on disk: 13MB

The entire DB will fit in memory 7-8 times over............

I run an update script daily which adds about 20K transactions to the respective rows of Table a, and updates various fields in Table b.

There are only maybe 20 users who access this database during the day.

The access consists mainly of sequential searches of the tables, using indexes on the tables.

There are no actual searches that would look for particular values in the tables, it's all indexed...there are a lot of sequential reads based on the indexs.

On a DS I had earlier, the update script would run in 28 seconds.
On my current one, it runs 2:43 .....almost 3 minutes.

I've been playing around with various settings, but can't get the processing time down.

Here's my current settings in my.cnf:

[mysqld]
performance-schema=0
innodb_file_per_table=1
max_allowed_packet=268435456

# Made it 500. Was 10000
open_files_limit=500

default-storage-engine=MyISAM
# My Adds
bind-address=127.0.0.1
query-cache-type=1
query_cache_size=64M
query_cache_limit=32M

tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=512M

#Tried these at 64 and ran out of memory
read_buffer_size=8M
read_rnd_buffer_size=8M

sort_buffer_size=32M
thread_cache_size=10


Be forever grateful for any advise/help anybody has

Thank you,
z ]]>
MySQL Help Zardiw1 http://forums.devshed.com/mysql-help-4/suggested-settings-mysql-979191.html
Select from 3 rows, 2 tables into one result http://forums.devshed.com/mysql-help/979146-select-3-rows-2-tables-result-new-post.html Mon, 02 Oct 2017 12:00:39 GMT Hi all,

sorry for the crappy title, but I couldn't find a better one.

Here's my problem.

table 1 - article table
Code:

id, title, description
1, 923, 813

table 2 - translation table
Code:

id, value
923, article title
813, article description lorem ipsum...

I want to select everything from my article database, but with its' title and description translated.
I cannot use joins can I? Afaik I cannot join the same table more than once.

Unfortunately I don't even have a clue how to solve it.

Thanks in advance for any hint or solution!

kind regards

Sven ]]>
MySQL Help winddancer http://forums.devshed.com/mysql-help-4/select-3-rows-2-tables-result-979146.html
SQL Inner Joins Help again http://forums.devshed.com/mysql-help/979131-sql-inner-joins-help-new-post.html Fri, 29 Sep 2017 16:08:31 GMT Hi All,

I'm sorry but again I'm struggling to get the SQL script below to generate the results I'd expect. There is one result I would expect to be returned, but nothing is being returned.

Code:

SELECT e.Forenames, e.Surname, h.Horse_ID , h.HorseName , eh.EmployeeHorse_ID , eh.StartDate , eh.EndDate , eh.EmployeeRole FROM tblHorse AS h
INNER JOIN tblEmployee_Horse AS eh ON eh.Horse_ID = h.Horse_ID
INNER JOIN tblEmployee AS e ON e.Employee_ID = eh.Employee_ID
WHERE e.Employee_ID = 18;

Basically, I've got a table of employees, a table of horses, and a 3rd table adjoining them, detailing which employees are currently loaning horses.

the Employee ID 18 should return one record from the joining table.

Any assistance would be most appreciated.

Many Thanks,
Graham ]]>
MySQL Help Graham_K http://forums.devshed.com/mysql-help-4/sql-inner-joins-help-979131.html
Request years that exist http://forums.devshed.com/mysql-help/979100-request-exist-new-post.html Tue, 26 Sep 2017 15:20:52 GMT I feel this is a really basic request via a GROUP BY, but I must just be overlooking it... All I want to do is have a set of years returned that exist in a DATETIME column. This list will help PHP generate a drop-down to help the client choose what table to build/view. The basic of what I have... (`date` is the DATETIME column) Code: --------- SELECT `date` FROM `invoice` GROUP BY... I feel this is a really basic request via a GROUP BY, but I must just be overlooking it...

All I want to do is have a set of years returned that exist in a DATETIME column. This list will help PHP generate a drop-down to help the client choose what table to build/view.

The basic of what I have... (`date` is the DATETIME column)
Code:

SELECT `date` FROM `invoice` GROUP BY `date`;
Assuming there's hundreds of rows, I may want it to return a list of 3. Lets say 2015, 2016, 2017. I can always ORDER BY via MySQL/PHP...

EDIT: Yep. My mind just clicked after I submitted...
Code:

SELECT YEAR(`date`) FROM `invoice` GROUP BY YEAR(`date`);
]]>
MySQL Help Triple_Nothing http://forums.devshed.com/mysql-help-4/request-exist-979100.html