MYSQL :: Avoid Adding More Than Once

Avoid Self-joins
I have a table that has values of variables for certain entities. The
columns of interest are targetID, variableID, and valueID. A row (1, 5,
9) means that target number 1 has a value of 9 for variable 5. Being
denormalized, target number one will have many possible rows in this
table, one for each variable for which it has a value.

My problem occurs when I want to find out what targets match a certain
set of variable values. For instance, I want to find out what targets
have a value of 9 for variable 5 and a value of 25 for variable 10. I'm
thinking that this can be a simple self-join:

SELECT mya.targetID from mytable as mya
LEFT JOIN mytable as myb
ON mya.targetID=myb.targetID
WHERE (mya.variableID=5 AND mya.valueID=9)
AND (myb.variableID=10 AND myb.valueID=25)

Does this make sense so far? The problem is that this doesn't scale.
When I have more than 31 variables and I need to evaluate them all,
MySQL breaks: I can't do more than 31 joins.

My design calls for perhaps 80-100 variables, so even 64-bit
architecture with a limit of 64 joins won't get me there. This is NOT
an architecture or platform issue - I need a design and a data
structure that will scale to lots of variables.

I need another data structure that won't get me stuck on too many
joins.

How To Avoid Race Condition?
How do I lock a table for one of my insert (followed by a read) queries on a table such that other simultaneous insert/read queries on that table are put off until the first one is complete? I am trying to avoid a potential race condition.

Count(*) To Avoid Duplicates
This is for (2) seperate sites that share the same login table (same username/passwords will access both sites).

Users may register at either site, if they choose, then they should login at the other site to "complete" the last phase of registration at the other site to insert remaining needed values.

Problem is with duplicates. When some users won't login, as they should, "complete" the process at the other site. They may bypass and register anew like anyone else at the site for the first time. (Even though there is a pre-registration page at both sites -- asking if users have already registered at the other site prior to arriving at the second -- directing a login to complete the process.)

Reason this is a concern is that one site has a (6) page registration process, inserting to (8) tables.

My idea (aside from aleady checking for unique inserts on usernames/passwords/emails) is to check on the complete phone number.
To be used on a conditional show region on the second page of registration -- if the latest registrant entered the same phone number asmay aleady exist in the table, (previously enterered while registering on the other site and forgot about it) -- this 2nd registration page will not show due to the: count(*)

Starting with 'Area' code, is this practical/how to combine all three?
Anyone have better suggestions or experience on handling two site duiplicates?

Would there be any benefit in having ALL USERS (both new and prior from the "other" site) fill out the the 1st registration form (on the (6) page registration site) where they are then directed to login to complete the process?

'AREA' 'PRE' 'PHONE'

SELECT *
FROM `Members`
WHERE `Area`
IN (
SELECT `Area`
FROM 'Members`
GROUP BY `Area`
HAVING count(*)>1
)

note: some registrants have same company names fir satellite/regional offices

Avoid Zero In Interger Datatype
Is there any way to avoid zeros which automatically entered in database in those column those have integer data type.

or i want to enter - instead of those zero

How To Avoid Previous Results
I want to omit all results(id) from the first query in 2nd query , without using sub query...

How To Avoid Duplicate Records
I need to filter out duplicates for every 30 seconds. say i have two duplicate records within the 30 seconds limit. I need to show up only one. If there are identical records but with a different time settings(say above 30 seconds) then i need to display it. I need to restrict duplicate records within 30 seconds.

How To Avoid NOT EXISTS In MySQL 4.0.26
I tried to use NOT EXISTS but recently found out that it is supported since 4.1. Now I'm trying to avoid it but I can't figure out how to do this. The Problem is that I have a table with (among outhers) a column SessionId and action.

Action may be opened and closed. Now I want to get all those sessions which are in state open, i.e. which have no line with action = closed. My first attempt was:

select sessionId
from audit AS a
where action = 'opened' AND
NOT EXISTS (SELECT * FROM audit AS b WHERE b.sessionId = a.sessionId AND b.action = 'closed')

Could anybody give me a hint how to get an equivalient query without a subquery?

How To Avoid Repeat Typing
I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma. I use windows 98.

Avoid Repeat Typing
I am a newbie in MySQL. I would like to know how to avoid repeating
typing an SQL statement when an error occurs after execution. That is,
if an error occurs I should be able to retrieve the statement that I
had written and correct the mistake. It is agonizing to keep on
repeating a statement that can take five minutes to write just because
one mispelled a word or missed a comma.

Command Used In Avoid Retyping
I don't want to retype a command once I wrote it. Does anyone knowa about an option that would just that like in a dos shell where you can use arrows to get back any command you have entered.

Avoid Couples In The Resultset
I use a query like this:
select t1.id_topics, i1.id_indices , t2.id_topics from indices i1, indices i2, topics t1, topics t2
where (i1.ind ='test' and i1.id_urls=t1.id_urls and i2.id_urls=t2.id_urls and i1.ind=i2.ind and t1.id_topics<>t2.id_topics)

it gives results like those:
"id_topic1","id_indices","id_topic2"
36,682,34
37,682,36
36,682,37
37,682,34

I would like to eliminate the "inverted" mates of couples in the resultset. That means in the example
"36,682,37" should be eliminated since "37,682,36" is already part of the resultset. Is it possoble to express this request in the query?

Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

Trying To Avoid Using Query Inside While Loop
I've done a lot of reading on here and I learned from some of Rudy's posts that it's a bad idea to do a query inside a while loop. I don't know why this is but he seems like an expert so I'll listen

What I am trying to do is take the top ten points from a player and display them. First I will post the tables then the code.

CREATE TABLE `tournament` (
`gameid` int(11) unsigned NOT NULL auto_increment,
`gametype` tinyint(2) unsigned NOT NULL default Ɔ',
`gamedate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
`leagueid` int(11) unsigned NOT NULL default Ɔ',
`seasonid` int(11) unsigned NOT NULL default Ɔ',
`roomid` int(11) NOT NULL default Ɔ',
`gamename` varchar(50) NOT NULL default '',
`cost` mediumint(4) NOT NULL default Ɔ',
`seats` mediumint(4) NOT NULL default Ɔ',
`notes` tinytext NOT NULL,
PRIMARY KEY (`gameid`)
) ;

CREATE TABLE `tournament_results` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`gameid` int(11) unsigned NOT NULL default Ɔ',
`memberid` int(11) NOT NULL default Ɔ',
`place` smallint(4) NOT NULL default Ɔ',
`earnings` smallint(5) default Ɔ',
`bounties` float unsigned default Ɔ',
`points` float NOT NULL,
PRIMARY KEY (`id`)
);
Now the follow code would be what I would use if I just wanted to add all the points and not limit it

PHP

mysql_query("SELECT
members.firstname,
members.lastname,
SUM(tournament_results.points) AS tpoints,
COUNT(*)as numgames,
AVG(tournament_results.points) AS apoints,
members.memberid AS membersid
FROM
members,
tournament_results,
leaguemembers, tournament
WHERE members.memberid = tournament_results.memberid
AND leaguemembers.memberid = members.memberid
AND leaguemembers.leagueid = '$lid'
AND tournament_results.gameid = tournament.gameid
AND tournament.seasonid = '$sid'
GROUP BY members.memberid
ORDER BY tpoints DESC");

That will total all of their games but I want to limit it their top 10 scores how can I do this without introducing a query inside of the while loop?

How To Avoid Having Thousands Of Records In A Many-to-many Relation
I'm building a web administration system for my company. We keep all our contacts from other organizations in this system (stored in a MySQL database): name, addres, telephone etc.

One feature of the system is that you can select a number of contacts and collectively send them an email. This works fine.

But: some of my co-workers need to know which of the contacts has received a specific email from the system.
So I was considering a setup like the following:

Table one: contacts (name, addres etc.)
Tabel two: emails (subject, text, creation day, day of delivery etc.)
Table three: many-to-many table holding one row for each email that has been send to a specific contact (autonum ID, ID of email and ID of contact).

My problem is that I can predict that this table will have LOTS of records in no time, as my co-workers are sending out many emails to a lot of contacts.

So: are there any better ways?
I thought about storing all contact IDs that has received a specific email in a text field in that email's record in the database - but then I'm not sure of the performance when I have to find out if someone specific has gotten a specific email etc.

Cleaning Up MySQL Connections To Avoid 1040
Well, I'm trying to run
PHP

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
    $process_id=$row["Id"];
    if ($row["Time"] > 200 ) {
        $sql="KILL $process_id";
        mysql_query($sql);
    }
}

To clean up my connections, as I'm getting a 1040 error "too many connections". Of course, I can't run this until I can actually connect, unless there is a way around somehow. I don't have any admin rights, I just have a web-based "php my-admin" module to run the db.

Every page people access opens a mysql connection, and then it is closed with
PHP

mysql_close($connection);

. Would putting in
PHP

<?php mysql_close($connection); $NASI_connection = null;?>

Mysql Running All Queries Double ? How To Avoid ?
Sometimes, when my database server has been slow and building up hundreds of queries, I have the impression that mysql is running 'double' of triple . It's hard to explain, but when I look at mtop (a tool to see what queries are active and what time they take to finish) , I see a lot of queries that seem to hang and that are present more then once. Some of them have unique data in them that can not happen just by refreshing a form. When the peak moment is over, and I check my site, then I sometimes see for example the exact same forum post 3 or 4 times in the same thread. Needless to say, this causes a lot of extra load on my database.

I'm not sure what really happens, but it seems like mysql server is just piling up queries wich it can not process fast enough, and those queries seem to come in several times again. I use seperate webservers, but even if I reboot them all, the extra queries still come in as soon as the webserver is back up and php scripts start to work again. The only way to stop it all, is to restart mysql, but that is something that I can not just do very easily. I rely on several heap tables for my site and they need to be refilled with data from normal tables. So restarting mysql is something I only like to do at night, with very few visitors online.

I hope I'm explaining my problem well enough. And I hope there is a way for me to check what really is going on, and if there is a way to stop mysql from 'running double' ?

What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here?

I've got a table in mysql with all 5 fields defined
as tinytext

Problem is when I export this to to a text file for notepad
each field is padded out by several blank spaces,
and i think my eamil program doesnt like this type of structue :
field1 , field2 , field3 , field4

Scheduling Replication To Avoid Bottle-neck Updates
We have a circular master-slave setup where any one of the 2 servers
can become master at any time (by human decision). The two servers are
placed at geographically different sites. The servers contain à number

of databases which are all replicated both ways.

When we have full usage of one master ~500 inserts/updates per second,
the bandwidth between our sites becomes a significant bottle-neck. This

we can accept at database level not on server level, ie
- if database A on site B has a lag of 30min because of important
activity on database A on site A, it is acceptable.
- if database B on site B has a lag of 30min because of important
activity on database A on site A, it is not acceptable.

Is there a work-around? We never have updates concerning 2 databases in
the same query.

Creating multiple mysql servers at each site could be one, but that
means some 10-50 servers on every physical computer. What side-effects
does that create?

Adding A Value +
This is what I am trying to do in my PHP code:

mysql_query("UPDATE users SET tourneys_1st='tourneys_1st+1' WHERE id='$row[p1]'");

I've tried it both with and without the quotes, but neither work... does this not work at all or do I just have something slightly wrong?

Adding
How could I add 30 onto 'PayDuration' where `Account` = 'neopian', in the fastest possible way using mysql queries.

Adding
I am trying to figure out how exactly to display that someone has signed up for a bunch of specific projects.How would I need to set my tables up?
I would need one for users and one for projects. To add multiple signed up projects a person, would I have to create a new column in the users table for each project or could I store it all in one field somehow?

Adding A Value
I want to add a value to a value currently in the database... I have

INSERT INTO products(quantity) VALUES ((select quantity from products where ProductId = '1')+10) ;

Adding 1
i cant find this anywhre.all i need to do is add 1 to a INT column. (so if the number is 10, i want it to be 11).
sure, i can select from database, add 1, then update the database. but figure there has to be a better more effecient way of doing this.

Adding Images
How can i get people to add images for me automaticaly? I have a site that i wish people to have the ability to add images to They need to be in two different sizes. Would i get them just to send in an email and sent to me, or i htere a way of getting the, to do it?

Adding To Value Of Column
Hi I am new to mysql. Is there a way to add to a field instead of just replacing it. I want to create a query that adds an input to what is already in the table. So for instance if I already have 2 of a product in stock and get 3 more of that item in i want my the stock column of my table to equal 5.

this is what i have now:

db_query ("UPDATE products SET stock='$newstock' WHERE productid='$pid');

Adding Birthdate Via Php
I'm playing around in the dark. I've created the necessary columns in MySQL and one is "birthdate" and so far I can only get the year entered and not the month or day. I've searched some sites and come up with the variation below and I don't know if I'm on the right track. If it is wrong can somebody correct it please? ....

Adding To A Where Statement
I have a select query that gets put together by use checkbox selection. That works okay, however I am now trying to add between two dates in the query.

//$from_date = "2006-01-01";
        //$to_date = "2006-09-24";

        //$where=$where AND date_added BETWEEN '".$from_date."' AND ".$to_date."'";

I cannot seem to get the correct syntax for the above to work. Tried many permutaions but cannot get there. Anyone know how to do it.

Adding A New Column
To ADD a new column I can say that I want it AFTER some named column.

But what if I want this new column to be the first, please, Experts?

Adding Values?
How can i add the values of different rows in mysql using php?

ie:

id field!
1 3
2 2
3 4
4 2
5 6
6 20
7 7
8 5
n 1
-----------------
total = value of id#1 upto id# n.

for this example total should be = 50

Adding New Column
After i added a new column to my existing table, i cant insert any information using the same form i was using all along. if i remove the column (drop) then i can use the form fine..

Adding New Users
I can't seem to add a new user to my database! I've tried it through phpMyAdmin (under privileges) and also running the query

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

which returned no errors and according to phpMyAdmin the user is entered into the `users` table. But when I try to login with that name I get

Access denied for user: 'myusername@localhost' (Using password: YES)

So all I can use to login is 'root' with no password. Not very secure!

Adding Two Fields Together
I have two fields, on called Price one called Qty, i want the Third Field to be Price * Qty.

Is there any way of doing this.

Adding 1 To An INT Fields Value
What is the SQL command for adding 1 to a specific field. This all that I can come up with:

UPDATE poll SET av1 += 1.

Adding Entries
I've got a database with approx 300 entries but i've got another 1000 to input and was wondering if anybody knows of a way (apart from using Microsoft Access) of entering the data into the table without having to complete each entry individually as I have all the information on my hard drive.

Adding To A Field
I need a MySQL script that will add a number to a field. Every time you click a button or something, in database "Database1" and table "Player1", the field "Test1" will then add a "5, " to it. So the first time it is "5," then "5,5," then "5,5,5," . I can't seem to get it to work.

Error Adding
I just finished installing MySQL 5.0 and MySQL Administrator v1.0.21. Everytime when I add new user, I received teh error "Error While Storing the User Information. The user might have been deleted. Please refresh the user list."

Adding Images
I'm looking at making an inventory of heritage buildings in my town and storing the information in a mysql database. I've managed to figure out mostly everything up to this point, except the ability to add photos of each building to the database. Anyone know how I might go about doing this in phpmyadmin?

Adding Users
I can add users but how do I make it so the users can not see all the DB, I know I can restrict the user so they cannot veiw the data but I dont want them to even see the other DB's

Adding New Data
I have a MySql database which i access using phpmyadmin. I have many records which i need to insert, i can add records using phpmyadmin but this only allows me to add one at a time. I can access the and change the .SQL file but the chance of typos is to high.

How can i add data which is currently in MS excel Format? I have tried to save in CVS but phpadmin shouts error at me.

ADDING NEW USERS
I have set up a linux machine running ubuntu. I finally was able to login to mysql with root, but I am unable to add other users. I use this command

mysql> grant select, insert, update, delete, index, alter, create, drop
-> on books.*
-> to bookorama identified by 'bookorama123';

and the response I get is:

QUERY OK 0 ROWS affected

I have no clue as to what is wrong. I tried logging into the user I thought I created, but it acts like it's not there.

Mulipal Adding Up
this is what i have,

SELECT addname, addsurname, addpostcode, clclientid, clname, clsurname, jobid, SUM(jobtotal, jobvat) AS TOTAL, SUM(paidpaid) AS TOTAL_PAID
FROM clientcard c, jobcard j, address a, paid p
WHERE c.clcompanyid = $companyid
AND c.clclientid = a.addclientid
AND a.addaddressid = j.jobaddressid
AND j.jobid = p.paidjobid
AND TOTAL != TOTAL_PAID
GROUP BY addname, addsurname, addpostcode, addtelephone, clclientid, clname, clsurname, jobid
it is a type of made up script that I have done making many assumptions like TOTAL !+= TOTAL_PAID and having to sums. basically TOTAL_PAID is made up of many rows, where as TOTAL is made up of just two numbers, I am trying to display all the rows that have unpaid bills, is this even possible, I have found using if statements in php to be unreliable

Adding An Item
I don’t know how to add one new item in ENUM field. (Is it possible anyway? )
Any hint.

Adding An Index
I've added indexes to the master server before but only after bring down all our JBoss apps, etc. and stopping replication.The main question now is, is it okay to add in index to a table in a live enviroment? Or, is there a possibility that I can mess up the table/DB/customer queries, etc.?

Adding Days
How can i add 7 days to a date type? Ive tryed simple now() + 7 to get seven days from now but when i checked the database it was still just today.

Adding A Primary Key
I removed all the duplicate rows from my database. Now I need to create a primary key. I would like for it to be a number that autoincrement. How can I do this and still get the rows I all ready have in the database to be incremented?

Adding Search
I hope someone can help me please. I would like to add a search engine to a website. I want the user to be able to enter a search string that will search the db and output results. I am using InnoDB tables so i am unable to do a full text search. I have tried using a SELECT statement:

SELECT * FROM Table where name LIKE 'searchstring%', but all i get is all the records displayed. I hope someone can help and thanks for any help, advice and suggestion given in advance.

Avoid Adding More Than Once

im trying to do what i think is a fairly easy query but im having some problems. basically i want to only include a certain row of data in the results if it has previously met a certain criteria. however if it has met the criteria more than once i still only want it to return once.

im trying to do what i think is a fairly easy query but im having some problems.

basically i want to only include a certain row of data in the results if it has previously met a certain criteria. however if it has met the criteria more than once i still only want it to return once.