MYSQL :: Avoid Zero In Interger Datatype

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

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?

Int Datatype
In mySQL, I found the date type has smallint, int and bigint. What are their
differences? I set a field with auto_increment as int type, what will happen
when the number reaches to the limit?

Set Datatype And Php
I'm using the set datatype but when I run a query from php I get a string value like "isHotel,isRestaurant,isOffice,isAsia" instead of the actual number (byte value). The string value is great ofcourse but I actually need the value, not the string. What's the best way of getting it without writing some elaborated function?

SET Datatype
As I was perusing the MySQL documentation, I noticed that there is a
lack of documentation regarding the MySQL SET datatype and the queries
used to manipulate it. In fact, the comments are longer than the
documentation. A google search shows a similar lack up information
regarding SET. I have therefore added a new article to my site for those
interested in the MySQL SET datatype, which can be found at: Code:

Which Datatype?
I'm inserting informations to the table.

Information is this kind: (HH:MM:SS) example: 16:12:01
Which kind of datatype I should use ?

If I use datetime or timestamp the record looks like this: 0000-00-00 00:00:00

I dont want to use varchar, because I maybe want to count times.

Example: StartTime - EndTime etc..

SET Datatype
I understood that SET datatype is kind of like ENUM but offers the option of choosing more than value.
The question is, what if I have more than two values: let's say i have a column of colors and the options are: "blue" "red" and "green".
What will the user be avle to choose:
1. blue
2. red
3. green
4. Now what ? all the combinations of 2 of them, or maybe all three of them.

Datatype
I have a field in my databse that stores comments submitted from a web
form. For some reason the data is being truncated when re-displayed. I
have the settings as follows:

varchar(255) is this the longest field length for varchar?

Datatype Problems
I am attempting to convert a existing database to mysql. My current primary keys are BIT(128) with a mixture of # and chars. As far as I can tell there is no equivalent to this in mysql. Any ideas as to how to get around this.

SET Datatype Performance
I have a â€œlittleâ€ performance problem using the SET datatype.

I have a database with over 800,000 rows which have 34 columns which are enum(â€˜0â€™,â€™1â€™) to describe various categories.

So when I search for a certain category or categories the search would be like:

Select count(id) from my_table where cat1=â€™1â€™ and cat10=â€™1â€™ and cat25=â€™1â€™;

If cat1 is indexed and cat10 is not.

Select count(id) from my_table where cat=â€™1â€™;

would return data in less than a sec while

select count(id) from my_table where cat10=â€™1â€™

might take over 5-6 secs to return the value.

Since I canâ€™t index all 34 category columns, due limitations of how many indexes I can create (there are other fields that required index besides the categories) I looked into how to do this more efficiently. I came up with the idea if using the set datatype. I created a test table

CREATE TABLE set_test(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cats SET('cat1','cat2','cat3',â€¦â€¦'cat34')
);

I loaded it up with 800,000 values from my main table and searches like

Select count(id) from set_test where cats&1;

Would return the count in less than a second.

So far so good. So I added a new column to the main database with the â€˜catsâ€™ column, containing the same info I had in the set_test table. To my surprise:

Select count(id) from my_table where cats&1;

Would take over 6 secs to return the value.

Or course the table has 45 columns (including the old 34 category columns) instead of two, but does it really make that much of a difference? If so, what can I do to get a decent performance?

Real Datatype
I am setting up a table and want to have a real data type for one of my column.

I have read the manual and it seems I need the decimal type, however this does not work, if I insert 37.5 or 37,5 it inserts as just 37.

There also seems to be a numeric datatype in the manual but myphpadmin does not offer this as an option when I modify the table.

Text Datatype
I am managing a small database and some of the tables
need to be strings of an average length of about 2,000
characters.

I decided to use the text datatype to store this
information knowing that the upper limit is about
65,000 characters.

However, the system crashes when the user tries to
enter about 2,000 characters. I checked the
max_allowed_package and net_buffer_size variables and
they are both set to 1MB.

Could you please advice me on what to look for or what
to change in order for the system to work as it is
supposed to be working?

Datatype For Currency
What data type should I use for currency. I will need them to list items based on prices.

String Datatype
I have a column datatype is varchar(255), I want to put in <img src="..." width=xx height=yy> to stored in this column, it won't let me, if I put in img src="..." width=xx height=yy (without the greater, less than symbol) it is OK, wondering I have to change this column datatype to something else, maybe text?

Datatype Encrypted
pls i need some help here, i have a microsoft access database file that was created with the 97 version, one of the two tables in the database has 2 columns with MEMO DATATYPE. But i cant view the content cos it looks somehow encrypted.
i have tried converting to MySQL but it still shows the same.
is there an SQL statement to select from the table, there by converting the data to real text, kinda decrypting it or is there a program to view its real content

Custom Datatype
Is there any wat to create the Custom Datatype in MySQL?

To create the Customdata Type in Oracle, I am using the Following Syntax.
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>,
<column_name> <data_type>);

I wish to know what is the Equivalent in MySQL.

TEXT Datatype
Is there any way to count the number of words in a TEXT datatype? I thought of counting the number of spaces between words then adding 1 but I want to see if anyone knows of a better method.

Altering Datatype
I'm having a little trouble and can't seem to find the correct command to change an existing field's datatype to a different one. Currently the field has datatype "int(10)" and I'd like to change it to "decimal(9, 1)".
If anyone can point me in the right direction to solving this problem.

Changing Datatype
I have a table - lets call it "TABLE". I have a column in TABLE called "COUNT", which has a datatype of "TEXT".
currently, TABLE is heavily populated with records. Column COUNT is populated with Integers. I want to change the datatype of COUNT to "BIGINT" w/o losing any of the data.

Correct Datatype Question
I'm trying to construct a database containing information that will eventually be brought into a flash movie. It's basically information to be used in a digital clothing catalogue slideshow. I need to include the following information for each clothing item:

Style Number
Description
Colours
Sizes

I was just going to create 4 tables (haven't decided yet how these will be structured) but just wanted to ask a question about the description column. I was going to make this a text datatype even though information included in it would be be repeated. That is possible information could be a number of sleeve lengths, cuff types, fabric compositions, collar types etc. All of these could very well be placed in separate tables to avoide redundancy but I just wondered if, in this particular instance, it is really necessary to do so.

I was going to create a simple CMS so that the client could enter new items, ie. style number, description, colours, sizes and then maybe have the ability to delete these items. So I guess my question is should I be using a text datatype for the description or be breaking the information up further?

What Is The Datatype For Money In Mysql
what is the datatype for money in mysql.

How To Decrypt Password Datatype
how to decrypt the password datatype in mysql. i made a page where in i list all username and password, but the password is encrypted because i used password data type. is there any way how to decrypt it?

Datatype For A List Of Items
I have a table called students and a table called classes.

In the web page you create a student by typing in the classes he has taken. If the class doesn't yet exist in the classes database, it will be created.

Somehow, I want to store what classes each student has taken. I to be able to find, for example, all the students who have taken courses 232 and 199 but haven't taken course 104.

Is there a datatype similiar to an array/list, or is there a better way to store this information? (I want to be able to search quickly.)

Mysql To Fget Datatype
lets say i have a table..

table.myfruit
-------
fruitname | varchar(25)

the variable fruitname is a varchar with a limit of 25 characters. i want to query to database to find out that fruitname is limited to 25 characters (in a php script)

SET Datatype In Stored Functions
i'm trying to create a stored function that can tokenize a file path and give me access to each directory in the path. i hoped to do this by replacing the slashes with commas and then treating the result as a set. below is the function. the 'FIND_IN_SET' command works properly, so that proves that the file path variable can be treated like a SET, at least in some respects. i need to to be able to supply an integer index and retreive the corresponding string in the set from within my function. is there any way to do this?

delimiter $
CREATE FUNCTION ADD_FILE (filepath VARCHAR(256) )
RETURNS VARCHAR(25)
BEGIN
DECLARE i INT;
SET filepath = REPLACE(filepath, '/', ',');
SET i = FIND_IN_SET('luma', filepath );
RETURN filepath;
END$
delimiter ;

Datatype For Textarea Form Field
What is the best datatypes relavent to mysql for form fileds such as textarea? I set my textarea form field that accepts comments to map to column in mysql with datatype as Text, Is this OK?

Working With The Text Datatype In Mysql
I am managing a small database where a few fields are defined as being of
type text. In my understanding, this would allow the user to enter up to
about 65,000 characters in the text field.

However, if the user types about 2,000 characters, then nothing is entered
in the database. I have checked the max_packet_allowed and the
net_buffer_length and the limits are quite high.

Could somebody advice me on how to go about solving this problem?

"I think there is a world market for maybe five computers."
Thomas Watson, Chairman of IBM, 1943.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw

Working With The Text Datatype In Mysql
I am managing a small database where a few fields are defined as being of
type text. In my understanding, this would allow the user to enter up to
about 65,000 characters in the text field.

However, if the user types about 2,000 characters, then nothing is entered
in the database. I have checked the max_packet_allowed and the
net_buffer_length and the limits are quite high.

Could somebody advice me on how to go about solving this problem?

I look forward to hearing from you soon.

"I think there is a world market for maybe five computers."
Thomas Watson, Chairman of IBM, 1943.

Newbie Help - Change Datatype Of A Table
--Apple-Mail-5--1016425622
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed

how can i change the data type of a table? I'm not sure if that is my
problem. If anyone is interested here is a link what I've been trying to
do:

Retrieve The 'month' From Datatype Date
is there any function that allows me to retrieve a particular information, say, the month, from the datatype 'date'?

Displaying TEXT Datatype With C Program
I'm currently trying to display the contents of a field which datatype
is set to TEXT, I'm trying to output this through a C program.
the line is as follows:

while((row = mysql_fetch_row(result)) != NULL)
{
printf("SID: %d Sensor: %s",(row[1] ? row[1] : "NULL"));
}

I have trued %c and %s in to print the Sensor name, however %s
crashes the program and %c produces a smiley face.

Displaying TEXT Datatype With C Program
I'm currently trying to display the contents of a field which datatype
is set to TEXT, I'm trying to output this through a C program.

the line is as follows:

while((row = mysql_fetch_row(result)) != NULL)
{
printf("SID: %d Sensor: %s",(row[1] ? row[1] : "NULL"));
}

I have trued %c and %s in to print the Sensor name, however %s
crashes the program and %c produces a smiley face.

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

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