MYSQL :: Command Used In Avoid Retyping

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.

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?

Command To Use
Is there a command that I can use to select a row that contains a particular word in a series of words?

For eg. how do I select the row that contains "I am Neo and I hate Smith" in a column if I just search with "Neo"?

Command
I moved my forum to another folder and now all the references in the database still point to the old folder. What command can I use to update all the URL's in the database to the new folder?
EDIT: Nevermind, I figured it out.

++ Command In SQL
I am developing a website in MySQL and PHP at www.zakirium.com. The site is supposed be a listing of the best freeware on the web. I'd like to be able to keep track of how many times each piece of software is downloaded. Is there a type of ++ command that could be used with an UPDATE command in PHP that would increase the number in the database column by one, so every time there was a download, the number would increase by one.

Best Command
i would like to know the best command to do backuping of the tables in the databases.......

i'm using MySQL 4.0 on a solaris 9 OS .....

SQL Command
If in my data I have for title the values Marketing Manager, Manager, Database Manager and I use LIKE 'Manager' I would want to return all titles with the word Manager or Managers etc. My code only returns an exact match. Can this be easy to do?

Code:

SELECT * FROM jobtitles j WHERE `title` LIKE 'Marketing Manager';

Where Command
If I want to update multiple rows and set the same column in each to the same thing, how would I go about doing that?

UPDATE gfx SET creator = 202 WHERE id='1,2,3,4,5'

Obviously that doesnt work, can I make an array somehow? Or do I have to use a bunch of or s?

Sql-command
why doesn't work this sql-command? it gives me a syntax-error:

SELECT *, IF((SELECT COUNT(*) FROM A, B WHERE A.pk = B.fk) > 0, 'true', 'false') FROM A;

the inner sub-selection itself works fine:
(SELECT COUNT(*) FROM A, B WHERE A.pk = B.fk;

also this would work:
SELECT *, IF(1 == 1, 'true', 'false') FROM A;

I'm using 4.0.21 - might it be too old for this command? if yes, how could I get it otherwise?

Sum Command
This may be a silly question but my knowledge is very very limited.
If I have 6 columns, say a,b,c,d,e,f and i have a entry where values were

a=1
b=2
c=3
d=1
e=2
f=3

If I wanted a sum of cells a,b,c and output a new column called abc and then a sum of cell d,e,f and output a new column called d.e.f how do I go about it ?

select
sum('a,b,c') $abc
sum('d,e'f) $def
from detailsas said, knowledge is very limited,

Command Line Help?
Say I have a column 'apples' with entries:

green apples
red delicious apples
granny smith apples
golden delicious apples
McIntosh apples
Jonathan apples

is it possible to run a query looping through all entries (like 1000s of entries) taking out the redundant ' apples' from each entry from the command line?

How Do I Get To The Command Line?
I read an entire book on writing MySQL and PHP without testing everything on the computer at the same time. The code all seemed to make sense to me while I was reading it, but when I finally sat at a computer to enter the code I realized that I had no idea where to begin entering it! How do I get to the "command line" to start using MySQL on Windows XP?

Command Line
If I am running MySQL as a Windows Service, how do I obtain the command line?

Use Command In Mysql
Is there any command in mysql to unselect the database, opposite command of use.

CHECK Command
Could I use the check command to see if a certain identifier will never exceed a default value when data is put into the table.

capacity INTEGER NOT NULL DEFAULT 10,
CHECK(capacity<=10)

GROUP BY Command
I've got a table with following fields and data:

int int double
article,userid,price
1 1 10
1 2 15
1 1 20
2 1 10
2 2 20
3 2 100
3 1 200

What I want to know is, which user pays the highes price for every
article. And this in one sql-command. The Result have to look like
this:

int int double
article,userid,price
1 1 20
2 2 20
3 1 200

Any ideas?

Difference Command
I was wondering has anybody ever used a 'Difference' command in SQL*Plus. I want to compare a table and a view and return the data on one and not on the other. Any ideas

Delete Command
I am trying to delete rows from a table when columnA = 1 and columnB =2. I cannot figure out the command.

Source Command?
I have a users.sql file that I want to import into mysql. When I tried this
source F:gusers.sql

It gives me an error like this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n
ear 'source F:' at line 1

ERROR 1102 (42000): Incorrect database name 'sers.sql'

but if I copy this file into F:data and use the command source F:datausers.sql
it worked fine.

Cannot Log On Via Command Prompt.
Our entertainment division uses MySQL as the database of choice for their various websites. They are not command-line-type people (who would want to that? i mean I like command line OK, but I'm weird). Using their PHP Admin program, they administer the databases quite efficiently.

All of us can log on fine via the PHP interface. Additionally, I've installed the MySQL ODBC in a few places and it works fine. We all use the username root and password abc. No problems.

My problem is that I can't access the MySQL server via the command prompt.

I do this:
CODEc:mysqlinmysql -u root -p

Using Source Command
I'm using mysql 5.0 on a WinXP system.

I thought I could use the source command from the mysql> prompt to read text files containing sql statements to do things like create tables, but no matter what way I type my file name the source command says it can't open the file.

ERROR:
Failed to open file'"foo.txt"', error: 22

I've tried quotes, no-quotes, full path, 's, /'s.

SQL Insert Command
Have a small database up and running (downloaded mysql) works fine. can select and even insert more rows. Made my sql file from notepad. However when i connect and insert a new row it works till i shut down the database.

Once i run it again ( reboot) it is not updated or not there anymore.

I tried the commit; command as well but i can not get it to save my new rows on the harddrive.

Addition Within Sql-command
given i have a TABLE1 with a FIELD1. FIELD1 contains an integer number. Now I'd like to increase that number by 1 (number ++). Can I do this within one mySQL-statement?

Describe Command
I want to retrieve information regarding table columns .I tried using the describe command but the sql server stops on execution of the same .

When I try the query "Show columns from tablename" I get the same response.

I am using MySql version 5.0 .
Could anybody please let me know whats the problem with these commands and is there any alternative to them .

Using The Mysqldump Command
I've been doing some research into creating a backup script for my PHP website. Using the mysqldump command looks like the best way to go. I am unsure on one thing though - where does it save the file to?

Like if I don't specify a path to the file, just something like 'dump.sql', then where will it save the file to? And how do I make it save to, for example, the root folder of a users website? My script may be on different websites so the path to the root folder of the website may change.

Command Editing
I'm running mysql 4.1 on a Mandrake 10.1 machine. When I try to edit commands using the arrow keys, many keys don't work properly, most notably the delete key.

That is, if I make a typo at eh beginning of a long command, I have to way to correct it. Entering the delete key just echos tilde characters in the console.

The bash prompt itself recognizes all these keys correctly. This sort of command editing seem to work fine under Win2K at the command prompt.

Mysql BETWEEN Command
i just want to know on how can i query certain part of data using the date.what i mean is, i want to query for example the data from 2005-04-25 till 2005-04-30.i've try the sql code below,but it does'nt work.can anyone give me an example on how can i query using mysql??plz help.tq

rsLetterDate.Open " select * from letter BETWEEN letter.letterdate = '2005-04-01' AND '2005-05-31'", "DSN=Data", adOpenStatic, adLockOptimistic

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.