Update One of Three tables in a single query
I have three tables, TableA, TableB, and TableC each has a unique ID field, idA, idB, idC respectively each table also has a field called 'Status'. The id field is always greater than zero. Now suppose I have three variables A, B, and C. Two of them are zero and the other is a valid ID for the corresponding table. Only I don't know which one. Is there a way to write a single update statement that will update only the one table that has the matching ID? I have tired a few different ideas but none seem worth mentioning here since they all either update too many records or don't update any records. Thanks for the help. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Get MySQL server IP address in SQL
On 3/2/2011 5:59 PM, Reindl Harald wrote: Am 03.03.2011 00:31, schrieb Claudio Nanni: Anyone knows how to get the server* IP address* thru SQL? no, because it is nonsense and has nothing to do with a db-server if you connect via tcp you know the ip Isn't that kind of like going to someones home, knocking on their door, and asking, Where do you live? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ORDER BY with field alias issue
I have the following query that is giving me problems. SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time` FROM `reservation` ORDER BY `Time` Problem is it sorts wrong because of the date format function output with am and pm. I guess I should have named things differently but I would rather not do that. Is there a standard way to get around this and have it sort by the non-formatted time value? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Not to show until a certain date
On 9/28/2010 10:04 AM, Patrice Olivier-Wilson wrote: Figured it out SELECT * FROM announcements WHERE announcements.announcements_expiredate CURDATE() AND announcements.announcements_postdateCURDATE() ORDER BY announcements_expiredate ASC I think you probably should do it like this. SELECT * FROM announcements WHERE announcements_expiredate CURDATE() AND announcements_postdate = CURDATE() ORDER BY announcements_expiredate ASC Otherwise they won't show till after the postdate. I assume you want to display them on the post date and not the next day? This of course assumes your field is of type 'date' and not 'datetime'. Prefixing the field name with the table name is not needed unless you have a join with a table with the same field names. Based on your field naming method it appears as though that won't happen. If it does, it is much less to type and easier to read if you alias the table name. like this.. SELECT * FROM announcements a WHERE a.announcements_expiredate CURDATE() AND a.announcements_postdate=CURDATE() ORDER BY a.announcements_expiredate ASC also it is a good habit to get into to have all filed and table names enclosed in back ticks just in case you have field names that are sql reserved words or otherwise would confuse MySQL. SELECT * FROM `announcements` a WHERE a.`announcements_expiredate` CURDATE() AND a.`announcements_postdate` = CURDATE() ORDER BY a.`announcements_expiredate` ASC Also to me it just makes it easier to read/ understand if you second condition is rewritten like this... AND CURDATE() = announcements_postdate Just my opinion on that. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: prime number table
Keep in mind that to store a number as a 32 bit unsigned integer takes 4 bytes of data no matter how small or large that number is as long as it is less than 4,294,267,296. If you store numbers as a string like it is in your file it takes 8 bits per digit so for 19,999,999 it would take 8 bytes or 64 bits. I assume the file has a line feed between each number, that is another 8 bits and may also have a carriage return adding another 8 bits. You could probably make your table even smaller if you were to move the descript filed to a new table. I highly recommend this option based on your statement that most of them are empty. A table with only integer values will have a fixed row size and probably be a little faster to access. If most of the rows will have descript text them leave it in this table, other wise it would be more efficient to keep in a different table. Also I'm not sure you really need an independent field for a primary key since the prime numbers could be used as the primary key. That would make your table even smaller. I'm not sure why you would what a table of prime numbers unless you had an index on that field and making it the primary key does that for you. Chris W On 9/17/2010 1:28 PM, Elim PDT wrote: I got a file of the list of the 1st 1270607 prime numbers (the 1270607th prime is 1999, beat the $227 book at http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8). the file is an output of a python script. the file size is about 12Mb. Then I created a simeple mysql table prime as mysql desc prime; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prime; --+ | Table | Create Table --+ | prime | CREATE TABLE `prime` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB; 12,754KB) Then I do mysql create table prm select * from prime order by prime.oid; mysql alter table prm modify oid int unsigned primary key auto_increment; mysql desc prm; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prm; +---+-- | Table | Create Table +---+-- | prm | CREATE TABLE `prm` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 | +---+-- The table file prm.frm is only 9KB My question is that how come it's SO SMALL? (currently the colum description in both tables prime and prm are empty except one identical row, with very short string value. Is is recommend to index the other twoo columns? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: AW: Dup Key Error Messages
On 9/7/2010 3:33 AM, Thorsten Heymann wrote: Yes sure, but you will consider, it is more than a nice to have to let the user know what field he filled incorrectly (e.g. in a webform,...). And it would be nice to this in an automated way. I have to agree. Due to database design issues that the end user has no knowledge of, it is very common for the standard mysql error messages to be cryptic at best. I always intercept duplicate key errors and display an error that is much more meaningful to the end users. For errors that I am not expecting, I never display them to the end users (I normally log them and email them to myself so I know there is a problem.) Many consider displaying raw error messages to the end user to be a security risk, especially in a web application. That said, I do try and write my code and design my database such that even if my full schema and source code were available to the public, I wouldn't have to worry about security. However I'm sure my code isn't perfect, and I don't have full control over all of the possible ways my web sites could be compromised so I'm certainly not going to give an attacker help by displaying raw error messages. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
To join or not to join?
I need a single row from 2 different tables. It is rather trivial to create a join that will join these two tables and give me all the information I want in one query. It is also fairly easy to just execute two different queries with out any join and get the data I need. Since the both single table queries or the join query will always only return a single row, I was wondering if there was a performance hit doing it one way or the other. On one table the where clause is on the primary key on the other table there where clause is on a single column that is indexed but not unique. However in this situation it will be unique. I can't put a unique key on this field in the second table because there are other applications of the second table where I do need it to non unique. Hope that makes sense. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performing subtraction between fields
If you want C to always be A-B then it would, in my opinion, be a very bad idea to store C in the table. Instead you can just put C in your query. SELECT `A`, `B`, `A` - `B` AS `C` FROM `table` If that seems like a hassle, you could always create a view using that select. Chris W b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Decimal points
Mark Goodge wrote: This is the sort of thing that is far better handled in the application layer, rather than the database layer. PHP, for example, even has a built-in function which will do this: setype($value,float); I agree about using the application layer, but I like to use type casting instead. $value = (float) $value; http://www.php.net/manual/en/language.types.type-juggling.php#language.types.typecasting I try to avoid asking why but in this case I have to. I can't imagine wanting to have a list of numbers displayed and not have them all aligned right with the sane number of digits after the decimal point. So why would you even want to do this is? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newb problem
'unsigned' is part of your data type which must be before the 'not null' Your closing ) needs to be at the very end. There is no reason t have the unique id since the primary key is unique. First you need the column name 'patid' then the data type 'INT UNSIGNED' then the other column options 'NOT NULL' You should really use the back tick quotes around your column and table names. The (9) after int is of no use in a php application since you will have to do all your output formating in your php code. I have changed the query to use upper case letters as that is the standard way queries are written. CREATE TABLE `patnotes` ( `patid` INT UNSIGNED NOT NULL, `patnote` INT UNSIGNED NOT NULL AUTO_INCREMENT, `parentid` INT UNSIGNED NOT NULL , `appuserid` VARCHAR(40) NOT NULL, `subject` VARCHAR(100) NOT NULL, `body` LONGTEXT NOT NULL, PRIMARY KEY(`patnote`)) Chris W dennis skinner wrote: Hello I am a new mysql user. Can anyone tell me why this does not create a table? ?php(the spaces before the question mark are not in the code) $dbuser=smeduser; $dbpassword=x; $dbname=smed; mysql_connect(localhost, $dbuser, $dbpassword); mysql_select_db($dbname) or die(unable to select database); $query=create table patnotes(patid int(9) not null unsigned, patnote int(6) not null unsigned auto_increment, parentid int not null unsigned, appuserid varchar(40) not null, subject varchar(100) not null, body longtext not null), primary key(patnote), unique id(patnote); mysql_query($query); mysql_close(); then the closing question mark and carat on this line this does not build a file and I am wondering what syntax I am missing here thanks dennis Hotmail. Get busy. _ The New Busy is not the old busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
With out the table definitions, I'm not sure how anyone could help. Can you send the output of show create table for each of the tables involved in this query? Chris W Keith Clark wrote: I'm trying to produce a report that will tell me how many products were available with a Quantity0 before a certain date, and have that ordered by date. Table: Date Quantity Result desired DateQuantity Available May 1 5000 May 2 5050 May 3 5075 Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Speeding up inserts in InnoDB
I have a very simple table.
CREATE TABLE `hams`.`phoneticcallsign` (
`CallSign` char(6) NOT NULL,
`PhoneticCallSign` char(6) NOT NULL,
PRIMARY KEY (`CallSign`),
KEY `PhoneticIdx` (`PhoneticCallSign`) USING BTREE
)
I inserted a little over 1 million records with CallSign = to a value
from another table and PhoneticCallSign blank. Then I used the
following simple php script to set the value of PhoneticCallSign.
$query = SELECT `CallSign` \n;
$query .= FROM `phoneticcallsign` \n;
$query .= WHERE `PhoneticCallSign` = '' \n;
$result = mysql_query($query) or die(DB error $query . mysql_error() );
while(($row = mysql_fetch_row($result))){
$CallSign = $row[0];
$PhoneticCallSign = SoundsLike($CallSign);
$query = UPDATE `phoneticcallsign` \n;
$query .= SET `PhoneticCallSign` = '$PhoneticCallSign' \n;
$query .= WHERE `CallSign` = '$CallSign' \n;
$Uresult = mysql_query($query) or die(DB error $query .
mysql_error() );
}
This was running very slow and I was getting only about 50 inserts per
second. I noticed that the table was InnoDB so I decided to change it
to MyISAM and try again. With MyISAM I was getting around 10,000
inserts per second. Surely there is some way to make InnoDB faster.
Any ideas?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
Johan De Meersman wrote: On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote: Wouldn't that strategy cause problems if one or more rows have been deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row 2 has been deleted - new sequence number would be 4). Yeps. I'm none too sharp today, apparently. Max() it is. That may not be an issue in this case. Since it sounds like he is keeping a revision history, I wouldn't be surprised if he plans on not allowing the deleting of records, unless of course all of the revision history for a given file Cluster/File are deleted. If that is the case the count would work fine. If that is not the case, max may not work either since if the last revision record has been deleted then using max will give faulty data as well. Seems the only way for something like this to work is if you keep the full revision history. Although I suppose that if you were to keep say the most recent X revisions then the last revision would always be in the table and max could work where count would not always. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speeding up inserts in InnoDB
Sorry I misspoke, I am doing updates not inserts. If I was doing
inserts I thought about the multiple record at a time idea but unless
there is something I don't know, I don't think you can do that with
updates. I will look into turning autocommit off and see what that does.
Chris W.
Andrew Carlson wrote:
If you are doing batch inserts, either turn autocommit off, and commit
after every so many inserts, or use the multiple values insert
statement to insert multiple records at one time. If the inserts are
coming from multiple sources/processes, it's a little bit of a harder
problem.
On Thu, Apr 22, 2010 at 10:13 AM, Chris W 4rfv...@cox.net wrote:
I have a very simple table.
CREATE TABLE `hams`.`phoneticcallsign` (
`CallSign` char(6) NOT NULL,
`PhoneticCallSign` char(6) NOT NULL,
PRIMARY KEY (`CallSign`),
KEY `PhoneticIdx` (`PhoneticCallSign`) USING BTREE
)
I inserted a little over 1 million records with CallSign = to a value from
another table and PhoneticCallSign blank. Then I used the following simple
php script to set the value of PhoneticCallSign.
$query = SELECT `CallSign` \n;
$query .= FROM `phoneticcallsign` \n;
$query .= WHERE `PhoneticCallSign` = '' \n;
$result = mysql_query($query) or die(DB error $query . mysql_error() );
while(($row = mysql_fetch_row($result))){
$CallSign = $row[0];
$PhoneticCallSign = SoundsLike($CallSign);
$query = UPDATE `phoneticcallsign` \n;
$query .= SET `PhoneticCallSign` = '$PhoneticCallSign' \n;
$query .= WHERE `CallSign` = '$CallSign' \n;
$Uresult = mysql_query($query) or die(DB error $query . mysql_error() );
}
This was running very slow and I was getting only about 50 inserts per
second. I noticed that the table was InnoDB so I decided to change it to
MyISAM and try again. With MyISAM I was getting around 10,000 inserts per
second. Surely there is some way to make InnoDB faster.
Any ideas?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=naclos...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: INSERT INTO multiple tables
I have no idea how you got here but there is no reason to do it that
way. This will work just fine and I do it every day in php.
However I don't use mysqli I still use ...
mysql_connect
mysql_select_db
mysql_real_escape_string
mysql_query
Don't forget to use the mysql_real_escape_string function to be sure sql
injection can't happen.
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error
connecting to
MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
Gary wrote:
Michael
Thank you for your response. It gave me the idea how to solve this, and it
seemed to have worked!
For those following hoping to see a solution, what I did was open the
connection, insert into one table, closed the connection, closed the php
script, and the data was inserted into 2 of the tables... The code looks
like this:
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query=INSERT INTO name(fname, lname).VALUES('$fname','$lname');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
?php
$dbc=mysqli_connect('localhost','root','','test')or die('Error connecting to
MySQL server');
$query=INSERT INTO address (street, town, state,
zip).VALUES('$street','$town','$state','$zip');
$result=mysqli_query($dbc, $query)
or die('Error querying database.');
mysqli_close($dbc);
?
It seems a bit redundant for php, but it seems to work.
If by the way anyone sees a problem with this solution, I would love to read
it.
Again, thank you for your response.
Gary
Michael Dykman mdyk...@gmail.com wrote in message
news:s2p814b9a821004121404ge4415a07tbb2ab1bbba1fd...@mail.gmail.com...
It is not a question of multiple tables, it is a question of multiple
statements. Most PHP configurations prohibit the application of more
than one statement per call to execute. This is generally thought to
be a security issue as the vast majority of simple PHP-based SQL
injection attacks only work on servers that allow multiple statements.
I haven't been deep in PHP land for a little while, but I think you
will find the default driver/config is expressly preventing you from
doing this.
- michael dykman
On Mon, Apr 12, 2010 at 9:44 AM, Gary gwp...@ptd.net wrote:
Seriously
You should read your answers before you post, the SA link did not provide
the answer. Had you read the page you sent, you would notice it does not
apply to mulitple tables...
Gary
Colin Streicher co...@obviouslymalicious.com wrote in message
news:201004112310.16594.co...@obviouslymalicious.com...
Seriously...
I found the answer in the first result.
http://lmgtfy.com/?q=mysqli+multiple+insert+statements
Assuming mysqli, if you are using a different driver, then google that
Colin
On April 11, 2010 10:36:41 pm viraj wrote:
is it mysqli query or 'multi_query'?
http://php.net/manual/en/mysqli.multi-query.php
~viraj
On Sun, Apr 11, 2010 at 10:27 PM, Gary gwp...@ptd.net wrote:
I am experimenting with multiple tables, it is only a test that is my
local machine only. This is the current code, which does not work , I
have tried to concatonate the insert statements. I have tried multiple
$query variables, but it is just overwriting itself (only the last one
gets inserted). I also tried writing the $query as an array, which got
me
an error message (saying it was expecting a string and I offered an
array).
Someone point me in the right direction?
Gary
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
head
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
titleUntitled Document/title
/head
body
form action=?php echo $_SERVER['PHP_SELF'];? method=post
labelFirst Name /label input name=fname type=text /br /br
/
labelLast Name /labelinput name=lname type=text /br /br
/
labelStreet Address /labelinput name=street type=text /br
/br /
labelTown /labelinput name=town type=text /br /br /
labelState /labelinput name=state type=text /br /br /
labelZip Code/labelinput name=zip type=text /br /br /
labelTelephone/labelinput name=phone type=text /br /br /
labelFax/labelinput name=fax type=text /br /br /
labelE-Mail/labelinput name=email type=text /br /br /
labelComments/labelbr /textarea name=comments cols=100
rows=15/textareabr /br /
input name=submit type=submit value=submit /
/form
?php
$fname=($_POST['fname']);
$lname=($_POST['lname']);
$street=($_POST['street']);
$town=($_POST['town']);
$state=($_POST['state']);
$zip=($_POST['zip']);
$phone=($_POST['phone']);
$fax=($_POST['fax']);
$email=($_POST['email']);
Combine Two Queries
I have a content management system. One of the methods I use to grant
access to various parts of the site is with Groups. I can link Pages,
Users, Modules, etc (objects) to any number of groups. So a Many to
Many relationship. I use the grouplink table to do this.
CREATE TABLE `grouplink` (
`LinkType`
set('user','page','template','templatefile','menu','module') NOT NULL
DEFAULT '',
`ID` int(10) unsigned NOT NULL DEFAULT '0',
`GroupID` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`LinkType`,`ID`,`GroupID`)
) ;
LinkType indicates what type of object I am linking to. If I am
linking to a page, ID is the PageID, if to a User, ID is UserID...
etc. And GroupID is just the group I am linking the object to.
The group table looks like this...
CREATE TABLE `group` (
`GroupID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`GroupName` varchar(45) NOT NULL DEFAULT '',
`Active` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`GroupID`)
);
My rule to give a user access to any object is the user has to be linked
to at least one of the same groups that object is linked to. There is
one exception to that rule, and that is, if an object isn't linked to
any groups then it doesn't matter what groups the User is in. Currently
I use two queries to implement these rules. If the Count on the first
query is 0, they access is granted, if not I execute the second query
and if the count on it is greater than 0, access is granted.
SELECT COUNT(`GroupID`)
FROM `grouplink`
WHERE `LinkType` = '$LinkType' AND `ID` = '$ID'
SELECT COUNT (g.`GroupID`)
FROM `grouplink` u //Groups the user, UserID is in
JOIN `grouplink` l USING (`GroupID`) //Groups the LinkType, ID is in
JOIN `group` g USING (`GroupID`)
WHERE u.`LinkType` = 'user' AND l.`LinkType` = '$LinkType'
AND u.`ID` = '$UserID' AND l.`ID` = '$ID'
AND g.`Active`
Is there any way merge these into one query?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to deal with 96 Dimensional Points ?
I'm not sure why, but it seems that some people, I don't mean to imply
that you are one of them, think there is some magic MySQL can preform to
find points with in a given radius using the GIS extension. There is no
magic. They simply use the well known math required to determine what
points are inside the circle. I could be wrong but I doubt there is any
way to create an index that can directly indicate points with a a
certain distance of other points unless that index included the distance
from every point to every other point. That is obviously not practical
since with a set of only 14 points the index would have over 6 billion
entries.
lets call each of your dimensions d1, d2, d3 d96.
If you create an index on d1, d2, d69, you can then create a simple
query that will quickly find all points that will find all points that
are with in a bounding box. Since this query is going to get a bit
large with 96 dimensions, I would use code to create the query. I will
use php. Let's start with the desired radius being r and the test point
dimensions being in an array TestPointD[1] = x, TestPointD[2] = . . .
$select = 'SELECT `PointID`, ';
$where = 'WHERE ';
foreach($TestPointD as $i = $d){
$di = 'd' . $i;
$select .= `$di`,
$MinD = $d - $r;
$MaxD = $d + $r;
$where .= `$di` = '$MinD' AND `$di` = '$MaxD' AND ;
}
$select = substr($select, 0, -2); //trim of the trailing comma and space
$where = substr($where, 0, -4); //trim off the trailing 'AND '
$query = $select FROM `points` $where;
Obviously this is going to give you points outside the sphere but still
inside the cube. However it will narrow down the set so the further
math will not take as long. If this were 3 dimensions with an uniform
distribution of points, about 52% of the points returned by that query
will be inside the sphere. I'm not sure how to calculate the ratio of
the volume sphere to a cube in 96 dimensions.Then it will be a
simple loop to find the points you really want. While this query will
likely return a lot of points that you don't want especially in 96D
space, it will reduce it enough that the following loop will be much
faster than looking all points in the table.
$result = mysql_query($query) or die(DB error $query . mysql_error() );
while(($row = mysql_fetch_row($result))){
$sum
foreach($row as $i = $d){
if($i == 0){
$PointID = $d;
continue; // skip point id at $row[0]
}
$SumSq += pow($TestPointD[$i] - $d, 2);
}
if(sqrt($SumSq) = $r){
print $PointID is with in $r of test point.\n;
}
}
In an application I had that was similar (but in 2D) I would insert the
id of the points that passed the condition into a temp table. Then I
could join that temp table to other tables do other queries I may need
on those points.
Chris W
Werner Van Belle wrote:
Hello,
I have been pondering this for a while, but never really looked deeply
into the problem.
I have 96 dimensional points and I would like to pose queries such as:
'give me all points that are within such a radius of this one'. The gis
extensions to mysql might support such type of query. The problem is of
course that points are 2 dimensional and I'm not sure whether I can
extend it to more than 3 dimensions ?
Does anybody have an idea about this ?
Wkr,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table Length Question...
Your first table layout is horrible, the second one is only marginally better. You should read up on database normalization. I have no idea what id, id2 and type are but since they seem like they are the same for every 'f' and every day, I am pretty sure they all relate directly to the user so those should all go in the first table. I will assume 'name' is the field you use to identify users.So your second table, let's call it dftable, should have the following fields. ID --- generic auto increment id for each record. name -- foreign key to link this table back to your user table. Fnumber -- this would be an int or maybe a set that would be set to 1,2,3, or 4 Date -- this would be the just like it says the date and you only have one table not one for every month. DFValue -- not sure what type of data this is. you probably want a unique key on name, FNumber and Date. They could also be your primary key instead of using ID, but I find it easier to code my applications and deal with changes in the long run if you use a single field auto increment value for your primary key. a simple join with a group concat will give you back your first output example. However for you example where you want d02f01 the query would simply be Note: since in my table I am using date, instead of just 02 we need the whole date so I will assume it is Mar, 02 of 2010 SELECT u.`name`, u.`id`, u.`id2`, u.`type`, d.`DFValue` FROM `user` u JOIN `dftable` d USING(`name`) WHERE `FNumber` = '1' AND `Date` = '2010-03-02' If id, id2 and or type change over time and you need to keep track of that you could add those fields in this table. Chris W Steven Staples wrote: Hi there, I currently store some information about a users daily habits in a table. The table has 4 fields per day, and another 4 fields as the keys. This table, depending on the month, can be from (4 keys + (28 days * 4 fields per day)) fields, to (4 keys + (31 days * 4 fields per day)) fields long... The table layout is like such: +-+---++-+--+--+--+--+--+--+ -- |name |id |id2 |type |d01f1 |d01f2 |d01f3 |d01f4 |d02f1 |d02f2 |.and so on +-+---++-+--+--+--+--+--+--+ -- Performance wise, would it be better to have it laid out in a manner such as +-+---++-++---+---+---+---+ |name |id |id2 |type |day |f1 |f2 |f3 |f4 | +-+---++-++---+---+---+---+ So that each row, contains a single days details, rather than have a single row, contain the entire months details? Also, when i would do a select, if i wanted say d02f1, would it load the entire row first, and then just give me that field? -Select `d02f01` from `mytable` where [where clause] Or would it jsut load that field... Does these questions make sense? (they do in my head) Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to deal with 96 Dimensional Points ?
Here is an idea, I'm not going to code this one:) It's still not an
ideal solution because it has to make assumptions about your data set.
Execute the algorithm I outlined previously with a very small r value,
if you didn't find the number of points you are looking for, increase r
and modify the query slightly so it doesn't return any of the points the
first query returned something like AND `PointID` NOT in ('34',
'56', '67', . . .).
At every step along the way insert the point id of the points inside of
r along with the distance they are from the test point, once you have
over 100 records in this table stop increasing r and query the temp
table sorted by distance with a limit of 100. Of course you have to
have some knowledge of your data set to get a reasonable start value for
r and a reasonable method for determining how much to increase it each time.
On the other hand a minor modification seems better. By inserting all
the points in the cube along with their distance in the temp table, a
query like SELECT count(*) FROM temp WHERE `Distance` = r Would be a
good way to see if you need to continue to the next round. Also doing
it that way, instead of using the NOT IN syntax, which I understand can
be slow, you can modify the where condition to find points that are
inside the current cube of size r but are outside the previous cube.
Chris W
Werner Van Belle wrote:
Hello Chris,
The use case I'
m talking about is actually a typical usecase for GIS applications: give
me the x closest points to this one. E.g: give me the 10 points closest
to (1,2,79) or in my case: give me the 100 points closest to
(x1,x96). A query like yours might be possible and might be a good
solution if we would know the radius in which we are looking for the
points, but this is not really the case: we merely want a list returned
ordered by distance. Solving this with your solution is possible but is
quite slow. There exists nice datastructures to deal with this type of
problem as said and these are used in the GIS implementation in MySql.
Chris W wrote:
I'm not sure why, but it seems that some people, I don't mean to imply
that you are one of them, think there is some magic MySQL can preform
to find points with in a given radius using the GIS extension. There
is no magic. They simply use the well known math required to
determine what points are inside the circle.
GIS extenstions are also not only about distances: the above query is
better solved with specialized datastructures.
I could be wrong but I doubt there is any way to create an index that
can directly indicate points with a a certain distance of other points
unless that index included the distance from every point to every
other point. That is obviously not practical since with a set of only
14 points the index would have over 6 billion entries.
Partitioning of the space such as done in 3D render engines do solve
this problem more efficiently than having a list of all pairtwise
distances. So the question is not whether such algorithms exist, it is
rather whether they are available in/through MySql.
lets call each of your dimensions d1, d2, d3 d96. If you create
an index on d1, d2, d69, you can then create a simple query that
will quickly find all points that will find all points that are with
in a bounding box. Since this query is going to get a bit large with
96 dimensions, I would use code to create the query. I will use php.
Let's start with the desired radius being r and the test point
dimensions being in an array TestPointD[1] = x, TestPointD[2] = . . .
$select = 'SELECT `PointID`, ';
$where = 'WHERE ';
foreach($TestPointD as $i = $d){
$di = 'd' . $i;
$select .= `$di`,
$MinD = $d - $r;
$MaxD = $d + $r;
$where .= `$di` = '$MinD' AND `$di` = '$MaxD' AND ;
}
$select = substr($select, 0, -2); //trim of the trailing comma and space
$where = substr($where, 0, -4); //trim off the trailing 'AND '
$query = $select FROM `points` $where;
Thanks for the nice illustration. In this case with the proper indices
this will indeed split the space in sections; nevertheless this approach
has great difficulties returning an ordered list of distances and
prefereably only the 100 closest ones at that.
Wkr,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 7-day average
I changed the names slightly when I tested it but here is the query that gives the result you want... SELECT a.`AccountID`, a.`company`, sum(h.hits), count(h.AccountID), sum(h.hits)/count(h.AccountID) AS AvgHits FROM account a JOIN hitsperday h using (AccountID) WHERE `date` DATE(now()) AND `date` = DATE(DATE_SUB(now(), INTERVAL 7 DAY)) GROUP BY a.`AccountID`, a.`company` ORDER BY sum(h.hits)/count(h.AccountID) DESC I think I would change the math. Since there are several days in there where there are no hits, that should in my opinion count against the average. So instead of dividing by count(h.AccountID), I would divide by 7 which would change the values to about... 11.4 8.6 5.7 still in the same order though. Brian Dunning wrote: My brain just squirted out my ear trying to figure this out, so maybe one of you can help me scoop it back up. I have a table of accounts, and a table of hits-per-day per account. I'm trying to sort my accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago through yesterday). Table: accounts ++-+ | account_id | company | ++-+ | 1 | ABC | | 2 | DEF | | 3 | GHI | ++-+ Table: hits_per_day +++--+ | account_id |date| hits | +++--+ | 1 | 2010-03-01 | 10 | | 1 | 2010-03-12 | 10 | | 2 | 2010-03-12 | 10 | | 3 | 2010-03-12 | 10 | | 1 | 2010-03-15 | 30 | | 2 | 2010-03-15 | 70 | | 3 | 2010-03-15 | 50 | | 3 | 2010-03-18 | 10 | +++--+ * Notice the first and last rows should be excluded because they are not between 8 days ago and yesterday. So the results should look like this: ++-+--+ | account_id | company | avg_hits | ++-+--+ | 2 | DEF | 40 | | 3 | GHI | 30 | | 1 | ABC | 20 | ++-+--+ I hope that makes sense! I've driven myself crazy with this to the point that I can no longer think about it clearly. Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL command for MySQL ?
I'm not 100% sure I understand what you are wanting but if I do, the INSERT ... ON DUPLICATE KEY UPDATE Syntax detailed here http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Will probably do what you want. For it to work you would have to have a unique key on the three fields (dom_id, Date_Start and Date_End). So your query would look like this INSERT INTO Table_Logs_Summary (mails_recus ,mail_rbl, dom_id, Date_Start, Date_End) VALUES (1, 1, 4, '2010-03-16 06:00:00', '2010-03-16 06:59:59') ON DUPLICATE KEY UPDATE mails_recus=(mails_recus+1),mail_rbl=(mail_rbl+1) Chris W Stephane MAGAND wrote: Hi i am debutant in SQL and i am search to know if it's possible: My SQL requets: UPDATE Table_Logs_Summary SET mails_recus=(mails_recus+1),mail_rbl=(mail_rbl+1) WHERE dom_id=4 AND Date_Start=2010-03-16 06:00:00 AND Date_End=2010-03-16 06:59:59; I wan't know if they have a optimised sql requets for said if the WHERE don't exist, he create it ! (if he don't have dom_id=4 AND Date_Start=2010-03-16 06:00:00 AND Date_End=2010-03-16 06:59:59 he create by in INSERT) (a IF NO EXIST ?) thanks Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: redirecting query output?
I'm not sure this is the best solution, but whenever I am inserting a lot of records with the possibility of some of those records already existing and I have no reason to update the existing records with new data, I use 'INSERT IGNORE'. I'm not sure if that will 'ignore' other errors that you may want to show. I would read up about it here http://dev.mysql.com/doc/refman/5.1/en/insert.html Richard Reina wrote: I have a perl script that periodically reads and enters (via perl-DBI parsed system output (about received faxes) into a table. Since the old output is repeated I have put, when creating the table, the UNIQUE key on the field of the faxname, which is always different (something like fax07879.tif) Every time the script is run many of the old faxes that have already been entered into the table are still on the server (as I would like them to be). As a result MySQL via execute() dutifully warns about all the duplicate errors. Is there anyway to redirect that output so the it does not appear as standard output? Or is it a bad idea to do so and should I being doing a different sort ow query altogether? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
fsb wrote: as a relatively unsophisticated dbms user (just dynamic web site back end), i thought it was very interesting to see the kinds of things oracle users do that i'd never have imagined. more than 61 joins in a query?! man, those guys are hardcore. Hardcore stupid if you ask me. I suppose it is possible to have a valid reason (can't imagine what it might be) for using more than 61 joins. But I would be willing to bet that 99.99% of the time if you get even close to that many joins you have a very poorly designed database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: tmp tables
to criticize at will. Chris W p.s. I don't mean to discourage the criticizing but a few notes about the way I do things. Keys are always auto inc. I never attempt to find some real data to use as a unique key (very bad idea to me) I always use the same name for my Keys in the parent and child table (why do you think the NATURAL JOIN syntax exists :) Ok let the criticizing begin :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is there a better way than this?
Unless I am missing something, this should work. SELECT DISTINCT X FROM `A` WHERE Y IN (25) AND Y NOT IN (24) Chris W Tim Molter wrote: I'm new to MySQL and I'm looking for some guidance. I have a table A, with two columns X and Y with the following data: | X|Y| 1 24 1 25 2 25 2 26 3 27 I want my SQL query to return 2 following this verbose logic: SELECT DISTINCT X FROM A WHERE Y equals 25 and Y also does NOT equal 24. I came up with the following SQL, which gives me my desired result, but is there a better way to do it? Can it be achieved using MINUS or UNION somehow? BTW, I'm using IN here because I intend to replace the single numbers (24 and 25) with arrays that have 0 to N members. SELECT DISTINCT X FROM `A` WHERE X IN ( SELECT X FROM `A` WHERE Y IN (25) ) AND X NOT IN ( SELECT X FROM `A` WHERE Y IN (24) ) Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Prepared statements and printing Queries
I know this is more a PHP question but no one on the list at news.php.net is responding. So I was hoping someone here might know. If I am using the mysqli extension and prepared statements, after I execute bind_param, is there a away to print the actual query that gets sent to the server? I have looked through the PHP docs and can't seem to find it. Of course I could be blind. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Update with value form another table
I have a table with a date value that I need to update with a value from another table. The table I want to update is profile. The profile table is linked to the study table with the foreign key StudyID. the profile table has the date I want to update to the value in the date value in the study table. So I can do a simple select like this SELECT ProfileID, p.`Date` as `BadDate`, s.`Date` as `GoodDate` FROM profile JOIN study USING (`StudyID`) Of course study to profile is a one to many relationship. How do I run an update to set p.`Date` equal to s.`Date`? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: dynamically splitting a table through one full table scan
Why not write a simple script that scans every record and inserts them
into the proper table one at a time?
In php for example..
$query = SELECT * \n;
$query .= FROM `X` \n;
$result = mysql_query($query);
while(($row = mysql_fetch_array($result, MYSQL_ASSOC))){
$Values = ;
foreach($row as $TmpVar = $TmpValue){
$$TmpVar = $TmpValue;
$TmpValue = mysql_real_escape_string($TmpValue);
$Values .= '$TmpValue',;
}
$Values = substr($Values,0,-1); // remove the last comma
$Table = sprintf(x%03d,$chunkId);
$query = INSERT INTO `$Table` \n;
$query .= VALUES($Values) \n;
$Iresult = mysql_query($query);
}
Knowing all of the columns could allow you to hard code the insert query
values clause and avoid the foreach loop which could make it slightly
faster. You would still need to use the mysql_real_escape_string
function on any columns that contained something other than numbers.
Jacek Becla wrote:
Hi,
Is there a way to dynamically split a big table
into n smaller tables by doing a single scan of
the table that is being split? Here is more
details:
* Suppose I have a million row MyISAM table X, with
relatively small number of columns. It has
a column chunkId with values between 1 and 100.
* I need to split this table into 100 in-memory
tables, essentially I need to do:
INSERT INTO X001 SELECT * FROM X WHERE chunkId=1;
INSERT INTO X002 SELECT * FROM X WHERE chunkId=2;
INSERT INTO X003 SELECT * FROM X WHERE chunkId=3;
and so on.
Based on the tests, each of these individual INSERTS
costs ~50% of a full table scan of X, even with
a clustered index on chunkId. The cost is totally
dominated by the SELECT * FROM X WHERE chunkId=n
Since we need to do such split many times, this
performance is not acceptable. It feels it should
be possible to do the split through a single scan.
Any suggestions?
thanks,
Jacek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: avoiding use of Nulls
Arthur Fuller wrote: Exactly the point. Michael, NULL *is* information. It means unknown and that is in itself useful information. A common example: A new employee is hired but which department she will work in is unknown. So the data entry person enters all the known information and leaves the rest until it has been clarified. In this case, you could have a table to link the employee to department. If you didn't know what department an employee was going to be in, there would be no record in that table. However I agree with Mike, why are you hiring someone if you don't know what they are going to be doing. Also having the separate table to link employees and departments allows for a many to many relationship, so an employee can work in more than one department. I'll leave the discussion for how bad an idea that is for another list :) Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join question
I have two tables, one is a list of users and the other is a list of events for each user. It is a one to many relationship. The event table is pretty simple just an event type and a the date and time of the event in a datetime field. I need a query that shows all events of a certain type for each user, very simple so far. In fact the query I use now is simply, SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime) FROM user u JOIN event e USING(UserID) ORDER BY u.LName, u.FName, e.EventType, e.DateTime The twist comes in that there can be several records for a given user and event type all on the same day, in a case like that, I only want the query to show one record. So I need one record per user per event type per day. The query will strip the time part off of the date time field and only display the date. We don't really care if that event happened 1 or 10 times in one day just that it happened at least once on that day for a user. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
I have no idea what I was thinking. For some reason I was thinking Distinct wouldn't work, must have been temporarily brain dead. Thanks for the wake up call. Gerald L. Clark wrote: Chris W wrote: I have two tables, one is a list of users and the other is a list of events for each user. It is a one to many relationship. The event table is pretty simple just an event type and a the date and time of the event in a datetime field. I need a query that shows all events of a certain type for each user, very simple so far. In fact the query I use now is simply, SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime) FROM user u JOIN event e USING(UserID) ORDER BY u.LName, u.FName, e.EventType, e.DateTime The twist comes in that there can be several records for a given user and event type all on the same day, in a case like that, I only want the query to show one record. So I need one record per user per event type per day. The query will strip the time part off of the date time field and only display the date. We don't really care if that event happened 1 or 10 times in one day just that it happened at least once on that day for a user. Chris W try DISTINCT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Table structure
Not sure how someone can intelligently comment on your table structure when you haven't given any details of the data you are storing. In my experience, the fact that you have 75 fields in your table is a strong indicator that your data is not normalized. If that is the case you tables are likely much larger than they need to be and queries may be slower. In general the larger your tables are the slower queries are going to be, if it is too slow for you depends on your hardware and how fast you need it to be. Also not knowing how large the 75 fields are, makes it hard to make any guess on this either. If they are all INTs or char(1)s, then that really isn't that much data and half a million records won't be all that much to handle. You also don't mention which database engine you are using. MyISAM will be much faster than some of the others, if you don't need to do transactions that would be what I would use. Velen wrote: Hi, I have a table containing 75 fields with a primary index and index set on 5 other fields. Everything is working fine so far as the table contains only about 80,000 records. I expect these records to reach 500,000 by end of september. I would like to know: - if the number of records will slow down my queries when I search on the indexed fields? - if manipulating the records within the table will be slow, (i.e. insert into table..., delete from table..., subqueries) Can anyone advise? Also, if you have a similar table please let me know your pros and cons for this kind of table structure. Thanks. Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Mark Can you elaborate on that point? Do you not use auto-increment values to link records in a one to many relationship? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
Jerry Schwartz wrote: [JS] No, you've made several good points. My reasoning is that inexperienced folks using this table through MS Access don't generally understand enough about database design to create the necessary JOINs to do it right. Perhaps the trade-off is a bad one. My thought is you should develop an application that will give your users the information they need with out direct access to the DB. My thought is, if a user doesn't have a solid understanding of at least 1st and 2nd normal form, and basic joins, they should not be given direct access to the DB. Doing so would be kind of like giving a 16 year old kid the keys to a 200mph race car and say have fun. Sooner or later something bad is going to happen. Just my opinion. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lookup tables
This is really a MySQL and php question since one of the two options require programming. Of course I don't think the result would be different in a different programming language. I use lookup tables a lot. I have had between 5 to 10 lookup tables for one table of data. Normally the lookup tables are small (much less than 100 records) A few get close to 100 and very few go over 100. Often times even with out using the lookup tables, the joins required to get the data set I want can be complex on certain projects. So rather than complicating it even more by doing even more joins to all the lookup tables, what I have done on some projects is to read all the lookup tables into a php array (which is really just a hash table in php) then as I am iterating through my rows in the query and displaying them for the user I just use my arrays to look up and display the data on the web page. It only amounts to a few hundred fairly short strings in a few arrays. Since the tables are small, mostly static, and the queries simple, I would think they would stay in a query cache. So my question is, is doing that way better than making the query more complex with all the joins? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
Mr. Shawn H. Corey wrote: Actually, it's scarier to give access to people who know what they're doing. They're the ones who would know how to sabotage it. Access should only be granted to those who need it to do their jobs. Everyone else should be restricted to using a user interface with predefined pathways. Can't really argue with that. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Match/No Match query
Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) This will return null for prod_num if there is no association in the ProductNumTable. Not having more details on your data I can't say for sure but I am guessing a group by *might* be needed. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Match/No Match query
Jerry Schwartz wrote: From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Match/No Match query Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as CodeTable, and do this all in a single (probably nested) query. In that case I must be missing something. What data do you have in the database that can be used to create the result. Some table structure would help and some more sample data that you want to use. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial data and mysql
I just did a quick look at the documentation on the mysql spatial extension and it seems like over kill for what you are looking for. An easy way to approximate the search for all points a given distance from another is to simply use a bounding box. An index on the X and Y coordinates of the point then will make the search fast. something like this... SELECT * FROM points WHERE x = minx AND x = maxx AND y = miny AND y = maxy If your data is evenly distributed in the space about 21% of the returned points will be outside the distance you want. You can then use a script to scan the result to find and reject the points you don't want. I do this for a mapping project I have on a web site. I don't have a lot of data so I can't say how well the performance is. Even if you have a huge data set, as long as your result sets weren't too big, this should be pretty fast. If the points you are dealing with are latitude longitude coordinates, I have the formula you need to calculate the distance written in PHP if you want it. Rob Wultsch wrote: I have been storing points in mysql without use of the spatial extension. I do not forsee the need to ever store more than points, and am wondering if the spatial extensions would offer any significant advantages. I have looked a bit for tutorials, etc... and have not found much. One feature that I would like is to be able to find all points withen X distance from of point Y, without doing a table scan. Would the spatial index (Rtree) be able to achieve this? Are there any good tutorials (or heaven forbid, books) that anyone can suggest? Should I go hang out with the cool kids that are using postGIS ;) -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data truncation warnings by special characters
I have some php code I use to import data that is a bit more flexible and robust than the load data statement in MySQL If you use php I can share the code with you. C.R.Vegelin wrote: Hi Jerry, Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt. The script giving errors now is used once a year, and last year without any warning. Since last year all I changed was adding in my.ini: # in [client] part default-character-set=utf8 # in [mysqld] part default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci When I undo these changes, and stop and start mysql, the errors remain. The Data.txt file (from an external source) looks okay with Wordpad. TIA, Cor - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'C.R.Vegelin' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, April 18, 2008 2:30 PM Subject: RE: data truncation warnings by special characters -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update select question
I have the following query... SELECT c.NLCID, n.publishdate FROM newsletter n JOIN newslettersection s using (NLID) JOIN newslettercontent c using(NLCID) WHERE contenttype = 1 AND n.publishdate AND c.`timestamp` = '-00-00 00:00:00' I want to run an update on newslettercontent and set its timestamp column to be the publishdate from the newsletter table using the join rules in that query. Is there a way to do that in a query? I should probably just write a quick script to do it since I could probably do that in less time than I have spent trying to figure out a query to do it. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing a query
I was wondering if someone could tell me what things I might need to do
to make this query as fast as possible.
I am developing a web site where users will have access to certain
things based on what groups they are in and what groups have access to
certain things. There are several different types things they have
access to based on the group but for this discussion lets limit it to
pages. For a user to view a page they have to be associated with one or
more of the groups that the page is linked to.
Since the relation ship between pages to groups and users to groups is
many to many I have a table just for that relationship. So here are my
tables
Group:
contains various info about the group with key field GroupID.
User:
contains various info about the user along with the key field UserID.
Page:
contains various info about a page on the site along with it's PageID.
GroupLink:
CREATE TABLE `grouplink` (
`LinkType` set('user','page','template','templatefile','menu') NOT
NULL default '',
`ID` int(10) unsigned NOT NULL default '0',
`GroupID` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`LinkType`,`ID`,`GroupID`)
) ;
Since there are several things that will be linked to groups I decided
to use one table to create all links and the The LinkType field to
designate which think we are linking to a group.
For example suppose I had page 18 linked to group 2, 5, 6, and 7 and I
had User 23 linked to group 1, 2, and 9. The rows in the table would be
like this
group, 18, 2
group, 18, 5
group, 18, 6
group, 18, 7
user, 23, 1
user, 23, 2
user, 23, 9
Now I want to know if user 23 can access page 18 so I execute this query
SELECT COUNT(`GroupID`)
FROM `grouplink` u
JOIN `grouplink` p USING(`GroupID`)
WHERE u.`LinkType` = 'user' AND p.`LinkType` = 'page'
AND u.`ID` = '23' AND p.`ID` = '18'
Since User 23 and Page 18 are both linked to group 2, COUNT(`GroupID`)
should return 1.
The way the rules I have set work, if the count is 1 or larger then that
user has access to the page.
Now the question is there anything I can do to make this query faster?
--
Chris W
KE5GIX
Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm;
Ham Radio Repeater Database.
http://hrrdb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]
Daevid Vincent wrote: WOW! You are right! That's silly. It's a table with a single column. All unique. With out the index MySQL doesn't know they are unique. Anyways, here's the magic incantation that worked for me: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is critical. DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); DROP TABLE IF EXISTS `dupes`; I think what happens if the index isn't there on the dupes table, MySQL looks at every row in the buglog table and then does a sequential search in the dupes table for that LogID. So if there there are say 100,000 in bug log and say 1000 in dupes that would be 100,000 x 1,000 = 100 million compares. If it were to do it the other way around, it would be faster. Take every record in the dupes table and then use the index in the buglog table to find the row that matches the LogID. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicate rows via temporary table either hung or taking way way too long
Daevid Vincent wrote: DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; LOCK TABLES buglog WRITE; SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM dupes) LIMIT 10; #DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); UNLOCK TABLES; The problem is the SELECT (DELETE) is either taking way too long to return or it's hung. I don't sit there long enough to figure it out. It seems like it shouldn't take as long as I wait. If I run the delete version, my buglog table count never decreases in the time I wait. I am pretty sure I have does this in the past and having an index on the temporary table made it amazingly faster. I assume the LogID field has an index in the other table already, if not you will want to add an index for that field in that table too. The easiest way is to add the index with your create temporary table statement and then do an ... INSERT INTO dupes (SELECT .) -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut Auto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
David T. Ashley wrote: Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT From '3.6.9. Using AUTO_INCREMENT' For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiplecolumn index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. I didn't know it only worked in MyISAM and BDB... I almost always use MyISAM anyway. However I don't use that feature anymore due to my change in thinking on primary keys. The only time I use a primary key that has more than one field is if the table is a many to many relationship table and in that case neither field is auto increment. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? Yes CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group by time range.
I have the following query... SELECT CreateDate, count( * ) FROM `userprofile` GROUP BY CreateDate It isn't exactly what I want. Records are added to this table in 2 main ways. First people use the web site interface to create records. In this case, records are only added by one or 2 people and with a significant time between record inserts. The second way is through an import that reads data from a text file. In the second case the date on the records will all be close together with about 60 records added per second. What I want to do is find all the groups where the inserts all happened with in say 10 seconds. So my group by would be more like.. GROUP BY CreateDate +- 10 SECOND Is there a way to do this? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join to find Duplicates
I need to find records in a table that may be duplicate records. The table stores basic information about the Users. I want to do a match on the FName and LName fields. The query I have looks like this SELECT u1.UserID, u1.FName, u1.LName, u1.Email, COUNT(u1.Email) AS `Count` FROM user u1 JOIN user u2 ON u1.FName = u2.FName AND u1.LName = u2.LName GROUP BY u1.UserID HAVING Count 1 ORDER BY u1.LName, u1.FName This works fine. However, I would like to help determine which of the duplicates should be removed buy getting data from another table. That is were I get lost. The second table has User Profile records for each user. It is a one to many relationship so each user can have 0 or more profile records. What I would like to add to this query is a count of how many profile records each UserID has in the User profile table. That way if there are two users with the same name and one has 5 profile records and the second has no profile records it is clear which to remove. What I have come up with is SELECT u1.UserID, u1.FName, u1.LName, u1.Email, COUNT(u1.Email) AS `Count`, COUNT(p.UserID) as ProfileCount FROM user u1 JOIN user u2 ON u1.FName = u2.FName AND u1.LName = u2.LName LEFT OUTER JOIN userprofile p ON u1.UserID = p.UserID GROUP BY u1.UserID HAVING `Count` 1 ORDER BY u1.LName, u1.FName However that returns rows that are not duplicate names and I'm not sure why. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not In join query.
I have 2 queries to give me a list of names. Q1: SELECT DISTINCT FName, LName FROM user u JOIN userprofile p USING ( UserID ) JOIN trainingstatus t USING ( UserID ) WHERE ProgramID =12 ORDER BY LName, FName Q2 SELECT DISTINCT FName, LName FROM namelist WHERE `Date` What I need is query that will give me a list of names that are in the Q2 result but not in the Q1 result. This is easy enough if I am just doing the match on one filed I can do this SELECT Name FROM namelist WHERE `Date` AND Name NOT IN( SELECT Name FROM . . . . . . ) What I can't figure out is how to do it if I want to match of FName and LName. I tried to use concat to build the full name and do the not in based on the new field MySQL didn't like that query at all. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Lookup Table Joins
I often find that I have more than one column in a tale that is an integer ID used to join to a lookup table. If there is only one Join to do it is to do something like this SELECT t.data, l.group FROM table t JOIN lookuptable l USING (groupID) WHERE whatever however if I need to join more than one that syntax wont work because the second join will be trying to join to the first lookup table no the main table. Is there a way around this or do I need to just do joins using this syntax SELECT x, y, z FROM table t, lookupA la, lookupB lb WHERE t.aID = a.aID AND t.bID = b.bID -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't fetch result twice in a PHP script
Mahmoud Badreddine wrote: it is more of a PHP question , I admit. I tried mysql_fetch_array, mysql_data_seek, mysql_result . Same result. And I am sure there's more than zero rows to be fetched, because it works in the first call, but not the second. That just means there is one row in the result and only one row. you should really print the query and then execute it using some other tool to see the result, I would be willing to bet there is only one row in the result. An error that has caught me more than once is having another query inside the look and using the same result variable. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicate key question
mos wrote: Chris, You probably want to know the specific table, so I wonder if it shows up in the MySQL error logs? Of course you can dump the database structure and look for key x by doing: mysqldump --no-data --skip-opt --skip-comments --compact and redirect it out to a text file. Hopefully it only shows up in one table. I always trap my SQL errors and write them out to my own log so I know exactly what operation is failing, including the SQL that caused it. I use a compiled language so it's easy enough to trap exceptions. I should have given a bit of context for my question. I am working on a tool for editing records. Some of the tables have a unique key for things like user name. When the tool is being used to create a new record and I get a duplicate entry error I want to capture it and present a better error to the user so he knows what to fix. In most cases it is key 2. However some tables have several keys so I need a way to find out what field(s) are part of that key so I can present a message like The User Name you entered is already being used and must be unique, please use a different value. Like I said this tool is designed to be somewhat general. I was hoping that something like SHOW COLUMNS FROM `TableName`, would give me the information I need. That query will tell me that UserName is a unique key, but how do I know it is Key 2 or 3 etc. I assume that the primary key is key 1 but other than that I'm not sure how I can now what the other ones are if there is more than one additional unique key. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Arbitrary Boolean Functions as Relational Database Structure?
This seems like a simple query to me. Correct me if I am wrong but as I understand it you want to get a list of SwReleases that a user has access to. Would something like this not work SELECT s.ID, s.Name FROM SwRelease as s WHERE s.ID IN ( SELECT SwID FROM GroupSwRel-- this table is the group to SwRelease relation ship WHERE GroupID IN ( SELECT GroupID FROM UserGroupRel -- this table is the user to group relationship. WHERE UserID = 'someuserid')) I've never done a query with a sub query in a sub query but it seems like it should work to me. In fact, if I'm not mistaken you may be able to rewrite this just using joins. Chris W David T. Ashley wrote: Hi, I'm implementing a software release database. Users may aribtrarily be members of groups (a many-to-many mapping), and each software release may contain multiple files. I'd like to allow users the maximum flexibility in deciding who may view what software releases. The most obvious approach is to allow specification in the form of Release X may be viewed by Users in Group Y or Group Z, per release. In this case, the database design would be something like: [Users] (many:many) [Groups] (many:many) [SwReleases] (1:many) [Files] The many:many relationship between groups and software releases specifies a Boolean function, of the form is in Group X or is in Group Y or Since one knows the user who is logged in (for a web database), one can do an outer join and quickly find all the software releases that the user may view. I believe one can do it in O(log N) time. However, the Boolean function is of a fairly special form (is in Group X or is in Group Y ...). This is the only form where it seems to translate to an SQL query naturally. Here is my question: Is there any interesting way to structure a database so that other forms of permissions can be specified and translate directly into SQL queries? For example, what if, for a software release, one says, to view this software release, a user must be in Group X or Group Y, but not in Group Z and not user Q? Is there a database structure and a corresponding O(log N) query that will quickly find for a given user what software releases may be viewed? Thanks. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fields seem to be mis-wired (for lack of a better term)
Hello, I'm having a problem when trying to update a record. I'm trying to set a field called 'balance' to 0 but instead of 'balance' becoming 0 the field 'name' becomes 0. None of the other columns are updated incorrectly. Here's the SQL statement I'm using: UPDATE `users` SET `name` = 'First Last' AND `email` = '[EMAIL PROTECTED]' AND `balance` = 0 AND `accrual` = 14400 AND `is_manager` = 1 AND `is_superadmin` = 1 AND `type` = 0 AND `manager_id` = 0 AND `modified` = NOW() WHERE `id` = 5 Here's the table definition: CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `email` varchar(255) NOT NULL default '', `password` varchar(32) NOT NULL default '', `type` tinyint(3) unsigned NOT NULL default '0', `manager_id` int(10) unsigned NOT NULL default '0', `is_manager` tinyint(1) NOT NULL default '0', `is_superadmin` tinyint(1) NOT NULL default '0', `fulltime_start` date NOT NULL default '-00-00', `accrual` smallint(6) NOT NULL default '0', `balance` mediumint(9) NOT NULL default '0', `created` datetime NOT NULL default '-00-00 00:00:00', `modified` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; I did an experiment just now through phpMyAdmin with the following query and it worked as expected: UPDATE `users` SET `balance` = 0 WHERE `id` = 5 Only thing I can guess is that there's an obvious error that is not obvious to me. :) Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fields seem to be mis-wired (for lack of a better term)
On Tuesday, April 24, 2007 10:12 AM Jim Winstead mailto:[EMAIL PROTECTED] said: AND `type` = 0 AND `manager_id` = 0 AND `modified` = NOW() WHERE `id` = 5 you can't use 'AND' to connect your updates, you need to use commas: UPDATE users SET name = 'First Last', email = '[EMAIL PROTECTED]', ... what you're doing now is setting your name column to a boolean expression. Oops. :) Thanks a lot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load sharing
I have a potential client that is anticipating rapid growth of a web site they want me to build. Some quick research tells me that there is the potential for as many as 50 million users that will access the site for an hour or two every day. All of those users will be located in the USA so most of the access will be during the day.. To use the web site you will have to have an account and log in. At this time I can't really say how much data will need to be stored about each user. If this site grows as much as this client thinks, will I need to have some kind of load sharing system to access the database? I was reading in the MySQL manual about the NDB Cluster storage engine. Is this something that would work well in a situation like this? One thing that was mentioned was the possibility of having servers in different locations which seems to make the Cluster storage engine not a good choice. Can someone here give some insight and suggest other options I could look into? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: On Duplicate Key Update question
Ed Reed wrote: I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks doing it one record at a time I would do something like this... Insert Into tablename (myID, Qty) Values ($myID,$Qyt) On Duplicate Key Update Qty = Qty + $Qty you may also be able to use... On Duplicate Key Update Qty = Qty + Values(Qty) But I have never used that before so I'm not sure -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: float numbers
Dan Nelson wrote: Since base-10 fractions can't be represented exactly by a base-2 floating-point number, any fractional value you store will be imprecisely stored and will cuase rounding errors. That isn't exactly true. If you were to add the word always between can't and be, it would have been true. There are some base 10 fractions that can't be expressed exactly in base 2, and some base 2 fractions that can't be expressed exactly in base 10. However, there are also fractions that can be stored exactly in both base 10 and base 2, such as .5, .25, .125, .75, .625. You get the idea. I don't remember any examples of the first two cases, and am too lazy to figure any out, but they do exist. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Workbench
Anyone have any info on MySQL Workbench? In it's latest state it isn't usable at all. It looks like it'll be really great. Thanks, Chris ParkerAardvark Tactical, Inc.IT Manager1002 W Tenth St. Azusa, CA 91702phone: 800.997.3773 x130 fax: 626.334.6860[EMAIL PROTECTED]
Finding duplicates
Suppose I have table t with fields k1, k2 and k3 that all make up the primary key. Then I have fields x, y and z. If fields x, y and z all have the same values in more than one row, there is a good chance they are duplicate records. What is the best way to list all of the rows where this condition exists? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding duplicates
Chris wrote: Chris W wrote: Suppose I have table t with fields k1, k2 and k3 that all make up the primary key. Then I have fields x, y and z. If fields x, y and z all have the same values in more than one row, there is a good chance they are duplicate records. What is the best way to list all of the rows where this condition exists? Something like: select k1, k2, k3 from table group by k1, k2, k3 having count(*) 1; I don't think I was clear, x, y, and z are not the only rows in the table and the group of k1, k2, k3 will always be unique as it is a unique primary key. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of different length/size datatypes
Hello, Originally I had this long explanation of what I'm doing and why I'm asking this question but I thought I'd just cut to the chase and ask... For a db that doesn't get a lot queries is there much of a performance difference between BLOB and VARCHAR(255)? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remove unwanted characters from a name field
Mark wrote:
I have a field called name which stores a person's first, middle and
last name.
Previously, these names (17800 of them) were entered in the database
by different people so there was no consistency in the format used.
There are now names entered such as:
Smith, John S.
Doe - Jane W.
Doe John
Try this page with several examples and see if it will do what you
want it to do.
http://hrrdb.com/FormatName.php
The main line of code that does the work is...
$FormatedName = preg_replace('/^([A-Za-z]+)([^a-zA-Z]+)(.*)$/', '$1,
$3', $FullName);
--
Chris W
KE5GIX
Protect your digital freedom and privacy, eliminate DRM,
learn more at http://www.defectivebydesign.org/what_is_drm;
Gift Giving Made Easy
Get the gifts you want
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Web Hosting and MySQL
I thought those here might like to know my experience with Hosting at godaddy.com. It is cheap at $3.50 or so a month. However there are huge limitations on what you can do. My first annoyance was when I found out that they used MySQL version 4.0 instead of 4.1 or 5. To their credit though they did have the most recent patch of 4.0 installed. The only way to upgrade was to go to there virtual hosting for $40 a month, which would let you install any software you wanted. Then I decided to have it connect to another database on a different server. But as I found out they don't allow any external connections. (I wonder if it would have worked if set up my mysql server on the FTP port) anyway the final straw was when I found out I could not create temporary tables. They said it was a security issue. How that could possibly be true I have no idea. Also as for php they had version 4.3.11. If you are considering using godaddy.com for hosting, you can use this information to make the decision as to if their service offers what you need. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
remote connection
I have set up a hosting account on godaddy and they have a ridiculously old version of mysql so I am trying to set it up to use the my server instead. But it refuses to connect. I can't figure out what could be the problem. I have tried connecting to my machine from a few other servers and they all connect fine. The only thing php gives me back is ... *Warning*: mysql_connect(): Lost connection to MySQL server during query in *Test.php* on line *18* Could not connect: Lost connection to MySQL server during query The user I am connecting with has host set to '%' with only one entry for that user. Can someone give me some ideas? Php info gives me this ... PHP Version 4.3.11 MySQL Client API version 3.23.49 My version of mysql is 4.1.15 and I have set it to use the old style password. Anyone have any ideas on what to look for? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's the PHP equivallent of mysql mydb somefile.sql
Read the section on the manual on the Client and Utility programs, specifically mysql. The syntax is essentially the same as you have in your code something like this from the command line mysql -u dbUser -pMyPassword dbName SomeFileWithLotsOfSQLCommands.sql Note there is no space between the '-p' and the password. Daevid Vincent wrote: Could you be more specific? What is SOURCE? Where do I use that? I tried to search, but I find a lot of hits related to source code. -Original Message- From: Ligaya A. Turmelle [mailto:[EMAIL PROTECTED] Sent: Monday, October 09, 2006 6:57 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: What's the PHP equivallent of mysql mydb somefile.sql I don't know if it will work - but have you tried using SOURCE in the mysql query? -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:01 AM To: mysql@lists.mysql.com Subject: What's the PHP equivallent of mysql mydb somefile.sql Currently I run an 'updater' script to run through a directory of .sql files using something like this in PHP: $COMMAND = mysql .$OPTION['db_prefix'].$db. .$mydir.$filename; system($COMMAND, $ret); What would be the equivallent way to to this in a PHP mysql_query(); way? I see LOAD, but that only works for data it seems. http://dev.mysql.com/doc/refman/5.0/en/load-data.html These scripts I use have ALTER statements and CREATE and all other types of SQL in them and almost no data actually. mySQL 5.0.15 ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making varchar field to act like numeric field
Rajesh Mehrotra wrote: Hi Steve, Correction: Use: select * from table where field1 like '4%' or like '5%' or like '6%' or field1 like '7%'; This would not have the desired result as it would return things like 500 or 50 or 5,000,000 etc. You must use the cast as another reply suggested. However I'm unclear as to why you would store numeric data in a var char field. If at all possible it would be better to store it in a numeric field. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Problem
Albert Padley wrote: I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly different sort order. They only want to sort on the spts column if the difference between 2 teams is greater than 9 in the spts column. All other sort criteria remain the same. So, the ORDER BY would be tpts DESC, spts DESC (but only if the difference is 9), w DESC, ga ASC, team_number ASC. if spts is an integer so that 9 is the same as saying = 10 then you could sort by a rounded version of spts like this... ORDER BY tpts DESC, ROUND(spts,-1) DESC, w DESC, ga ASC, team_number ASC by putting the -1 there it rounds to the nearest 10's before doing the sort. Of course the output is not rounded. The following are a few examples of the output of the round statement. ROUND(23.632, 2) = 23.63 ROUND(23.632, 1) = 23.6 ROUND(23.632, 0) = 24 ROUND(23.632, -1) = 20 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Computing a column based on other columns
Dan Jakubiec wrote: Hello, Is there a way to generate a column which computes a value from other columns in the same query? For example, I want to do something similar to: SELECT MIN(table.myvalue) as min, MAX(table.myvalue) as max, min/max as derived_column FROM table ORDER BY derived_column ; This works on a table I have. SELECT MIN(tone) as `min`, MAX(tone) as `max`, MIN(tone)/MAX(tone) as ratio FROM pltone p I put the back tick mark around min and max alias, I'm not sure you need it but it is always a good idea anyway. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to load non-native Date type with LOAD DATA
David Perron wrote: I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? in VI the following should work depending on the other data in the file :%s/ \(\d\d\)\/\(\d\d\)\/\(\d\d\d\d\)/ \3-\1-\2/ :%s/ \(\d\d\)\/\(\d\)\/\(\d\d\d\d\)/ \3-\1-0\2/ :%s/ \(\d\)\/\(\d\d\)\/\(\d\d\d\d\)/ \3-0\1-\2/ :%s/ \(\d\)\/\(\d\)\/\(\d\d\d\d\)/ \3-0\1-0\2/ this only works if there is a space in front of the dates. If the date is the first thing on the line, replace the first space on each line with a ^ and remove the second space. If the date is quoted, replace both spaces on each line with a quote. If the date is preceded by just a tab, replace both spaces on each line with a \t -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with insert +php
Marcelo Fabiani wrote: Here is the code: . . . $result = mysql_query($sq)or die (problema Leyendo Tabla);; . . . Why are there two ; at the end of that line? I'm really not sure what if anything that will do but I would remove it just in case. Also are there any triggers on this table that could be doing this? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By question
I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional Insert
Douglas Sims wrote: Hi Ravi You are correct. I was just sitting here thinking about this after I sent that and realized that what I said was incorrect; the REPLACE command will not do what you want, because it delete a record instead of updating it, it has no way to know what the previous value is. You probably should define a stored procedure and call that. Here is an example which seems to work: Why not use this INSERT INTO table VALUES(..) ON DUPLICATE KEY UPDATE X = $X, y=$y .. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
Jesse wrote: This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse You need to get a table that gives you the latitude and longitude of each zip code. The location is of course some where near the center of the zip code so these calculations won't be perfect. Once you have the latitude and longitude it just takes some math to figure out the distance. Not sure where you get the data but someone here probably knows. You can find details on the calculations here... http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seperating Application server and Database server
Ratheesh K J wrote: Hello all, Currently our application and MySQL server are on the same machine. When should these be seperated? What are the main reasons that we should be having a seperate DB server? There are at least three possible reasons. First, if your applications are using lots of CPU power, moving them to their own server should speed up the DB. If your applications use a lot of disk I/O, other than the DB, that's another reason. Memory is another. If the machine is maxed out on RAM and you are using all of it, having the applications on their own machine should also help. If you move it, you need to be sure to have enough network band width between the two machines. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Audit trail
I would like to create an audit trail for one table in my DB. Users will login to my web site and be able to enter and edit information, I want to keep a record of what changes are made by what user. These users will be web site users and not actual MySQL users. Is there an easy method in MySQL to do this, or do I just need to write code to track any changes as they are entered? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
Peter Van Dijck wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter SELECT Count(*) as Count, UserID FROM table GROUP BY UserID ORDER BY Count DESC LIMIT 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Mike Blezien wrote: Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) The zip code tables usually give a lat long location somewhere near the center of that zip code (which can be HUGE in rural areas) You can then use some math that with give you the distance between the coordinates of 2 zip codes. Here is a web site I found for help when doing similar calculations. Remember that this type of math usually wants angle measurements in radians, not degrees. http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Mike Blezien wrote: Chris, this is something very similar to what we are attempting to accomplish: http://www.papajohnsonline.com/restlocator/RestaurantLocator The math in the link I gave you will still work for the distance calculations, however if you want what that site does you will need a GIS database that can give you lat long of an address. I am about 90% sure something like that is going to cost you a lot of money and cost a lot to keep it up to date. I'm afraid I don't know where to look for a vendor though. I would search for GIS software or maybe contact some of the mapping people like Google maps or Street Atlas USA, if they don't have a product they can sell you to provide lat long of street addresses, maybe they can tell you who provides them with data and contact them. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing using Max(Field) ?
obed wrote: On 8/15/06, Visolve DB TEAM [EMAIL PROTECTED] wrote: Hello William Try the below Query to insert next maximum value of the field into same table INSERT INTO Sample(id) SELECT MAX(id)+1 FROM Sample Wooww it works :D ... i didn't know that... great ! Wouldn't this cause a problem if more than one person were trying to insert a record in that table at the same time? Could that not cause the ID to be the same for 2 records -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Help - Stupid Question, sorry to bother.
By default MySQL uses port 3306 so you need to be sure that port is open on the server, and not blocked by a firewall. You also need to be sure the user you are trying to login as can login remotely. In the MySQL user data base, there is a column for host which is the host that user can login from. If that host says localhost you can only login from the localhost. If it says % you can login from any host. Also note there can be more than one entry for each user all with a different host. It is best to only set it up so you can login from a specific host, that makes it more difficult for a hacker to break in. If the user you are logging in as is set up just for localhost I would add a user and use the host you plan to login from if you can, other wise just change the host to % then you can login from anywhere. Also if you do an update to the user table, using the sql update command, you also need to execute flush privileges for the changes to take effect. Sana Farshidi wrote: Hi, Im sure this is a stupid problem but im a bit confused, and some assistance would be greatly appreciated. Im trying to assist with the maintenance/updating of a php site for a school which uses mysql, and is allready up and running on a remote server. I have the ftp username and password, and i can access the php/html side of things no problems, but i want to connect to the mysql database on the server, and as stupid as it sounds, i have no idea how to go about connecting. Ive used mysql before, not very efficiently, but never have had to set it up, i was always told the program and connection details. I downloaded a mysql program (enginsite MySql client) and tried to connect but am not having any luck and have no idea what the port is supposed to be... the detail i have are: (of course ive undisclosed the username/passwords) DOMAIN ACCOUNT DETAILS Domain Name: www.mhmcindia.org FTP DETAILS Host Name / IP Address: 216.67.234.167 or ftp.mhmcindia.org username: undisclosed password: undisclosed and the PHP connects through the script (on the remote server of course) $host=localhost; $username=undisclosed; $password=undisclosed; $db=undisclosed; $link = mysql_connect($host, $username,$password) or die(Could not connect : . mysql_error()); mysql_select_db($db) or die(Could not select database); Is there anyway i can connect to the mysql database with these details? if so how? Im sorry for asking seemingly obvious questions, but some assistance would be greatly appreciated. Thanks, Sana -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete one record, automatically delete other records...
Hello, I'm not sure if this is possible (or what it's called, and how to search for it) at the db layer or if this has to be done at the application layer... I would like to be able to delete one record in one table and then automatically (without making an extra call to the db) delete other records. If I'm using this term correctly, I think I'm trying to avoid having orphan records. In my specific case I want to delete a product from my database and then automatically delete all associated records like the category and manufacturer relationships. BTW, I'm using MySQL 4.1.20. I'm not really looking for code snippets but rather the name for this idea and/or links so I can rtfm. :) Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Delete one record, automatically delete other records...
Chris mailto:[EMAIL PROTECTED] on Monday, August 07, 2006 6:19 PM said: Foreign keys with an on delete cascade should do it. http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html Thanks everyone! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and testing an Insert statement
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
And then get back a success or fail error code.
Using MySQL 4.1.
Thank you for your time,
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with Result Ordered by Minimum of Fields
David T. Ashley wrote: Can I just write something like: SELECT * FROM mytable WHERE fieldofinterestvalue ORDER BY MIN(field1, field2) ASC; I think this will work.. SELECT *, IF(a-b 0,a, b) as SortField FROM table WHERE whatever ORDER BY SortField a and b being the names of the fields you are interested in. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with Result Ordered by Minimum of Fields
David T. Ashley wrote: On 8/6/06, Chris W [EMAIL PROTECTED] wrote: David T. Ashley wrote: Can I just write something like: SELECT * FROM mytable WHERE fieldofinterestvalue ORDER BY MIN(field1, field2) ASC; I think this will work.. SELECT *, IF(a-b 0,a, b) as SortField FROM table WHERE whatever ORDER BY SortField Question: Is there any way to get SortField (or a similar per-selected-record field) included with the SELECT output? The reason for this inquiry is that my PHP script that uses the SELECT results will also have to calculate SortField as MySQL did as part of the query. If MySQL has done it already, no need to do it a second time in the PHP script. If you run that select in MySQL query browser you will see that in addition to all the other fields in the table it adds to the end a field called SortField. If you would like it at the beginning they change it to . SELECT IF(a-b 0,a, b) as SortField, * If you want it the middle you will need to list every field in the select with the SortField if between the 2 you want it between -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replicating -- sort of 2 way
Before I go reading too deep into the documentation I would like to know if this is even possible or if it is just a bad idea. I have Server A with DB X, and server B with DB Y. I would like to set up Server A and B as both a replication master and slave. Where Server A would be the Master for DB X and the Slave for DB Y AND Server B would be the Master for DB Y and Slave for DB X. The reason I am doing this is just for keeping a backup. Is this a bad idea? Any better ways? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating -- sort of 2 way
Enrique Sanchez Vela wrote: Chris, Replicating for the sake of backups is in general a bad idea, since once you delete/update a record, it will be automatically propagated to the slave server. Replication, should be used to provide better availabilty/load balancing but that would need to be setup as part of a bigger plan to provide higher availabilty to the service that you are providing. One thing that you could do, is to setup a replicating slave server, freeze the replication, perform backups (to tape/disks etc), then re-enable the replication activity, this way you provide a non-stop service without compromising your data availabilty. The reason I am wanting to use it as a backup is because it is easy automatic and I don't have to do anything once it is set up. The backup would be in case of disk failure not data corruption. I would probably run local backups too. The replication I was planing on doing was going to be between 2 servers at 2 different locations, which would allow for the best backup in case of disaster that completely destroyed one location. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is a set current TIMESTAMP operation atomic when updating/inserting multiple rows?
Dan Jakubiec wrote: Hi. I want to insert/update multiple rows into a table which has a timestamp field, and I want to set the timestamp field in each row to the current timestamp. However, it is important to me that all the rows I update actually end up with the same timestamp value. My concern is: what happens if the SQL query take a long time and the current timestamp crosses a second boundary? For example, suppose I issue the following request on a very large table: UPDATE ts SET my_timestamp=NULL; Say that this query takes 3 seconds to complete. My questions: 1) Will all the rows have the same timestamp value? Or will some rows have now, now+1, and now+2? 2) Will the behavior be different if I use my_timestamp=NOW()? 3) If the timestamps will be different, what's the best way to make them all the same? In the manual it says that all calls to now() in a query always return the same time regardless of how many there are and how long it takes the query to run so I am thinking that all records with an auto update time stamp column that get changed in a single query would all have the same time stamp. It should be easy enough to test if you have a large set of data where an update would take a long time. I'm pretty sure it will do what you need though and set them all the same. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd join questing
It may not be odd to many but I can't seem to think of how to do this I have 2 queries below that give me exactly what I want each to do but now I want to combing them is such a way that in the second query I have one additional column that gives me a 1 on a row where the PID would be in first query and a 0 if it would not me in the first query. so lets say for example query 1 returns the following PIDs 2,5,7,9 And the second query returns rows with the Following PIDs 1,2,3,4,5,6,7,8,9,10. ignoring, for now, the other columns I am selecting in the second query, what I want is PID followed by a column called say check such that I would get the following output. | PID | Checked | | 1 | 0 | | 2 | 1 | | 3 | 0 | | 4 | 0 | | 5 | 1 | | 6 | 0 | | 7 | 1 | | 8 | 0 | | 9 | 1 | | 10 | 0 | SELECT `PID` FROM serviceplanfeaturelink WHERE `SID` = '$SID' SELECT `PID`, `psoc`, `pName` FROM product WHERE pTypeID IN ($_FEATURES_TYPE_IDS) Just in case your aren't familiar with PHP the $SID is just a php variable. Any suggestions -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
Brian Dunning wrote: My understanding is that SCSI has a faster transfer rate, for transferring large files. SCSI is better for EVERYTHING except your budget. Faster for large transfers, small transfers, seek times, and most especially it handles requests from multiple threads much better. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date functions
It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date functions
Addison, Mark wrote: From: Chris W Sent: 07 July 2006 09:23 It's late and I just gave up reading the manual. Can someone please tell me the easiest way to do a query that will return all rows with a time stamp that is X number of seconds older than the current time? Something like this. SELECT * FROM t WHERE TimeCol (now() - 60*60*24*3) Yes I know that is just 3 days but other times I will want to find records that are a few hours old so I like using the formula. SELECT * FROM t WHERE TimeCol DATE_SUB(CURDATE(), INTERVAL 60*60*24*3 SECOND); Maybe it was just too late at night but I read about the DATE_SUB function in the manual and got the impression that it ignored the time part of a date time field so I could not use it for finding records only a few hours old. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Math problem
Karl Larsen wrote: I'm trying to multiply numbers one of which is money. The money looks like this: SELECT price FROM titles; | price | ++ | $20.00 | | $19.99 | | $7.99 | | $19.99 | | $11.95 | | $19.99 | | $14.99 | | $11.95 | | $22.95 | | $2.99 | | $10.95 | | $7.00 | | $2.99 | | $20.95 | | NULL | | $19.99 | | $21.59 | | NULL | ++ 18 rows in set (0.01 sec) When I use SELECT title_id, ytd_sales * price From titles; I get: | title_id | ytd_sales | price * ytd_sales | +--+---+---+ | PC | 4095 | 0 | | BU1032 | 4095 | 0 | | PS | 3336 | 0 | | PS | 4072 | 0 | | BU | 3876 | 0 | | MC | 2032 | 0 | | TC | 4095 | 0 | | TC4203 | 15096 | 0 | | PC1035 | 8780 | 0 | | BU2075 | 18722 | 0 | | PS2091 | 2045 | 0 | | PS2106 | 111 | 0 | | MC3021 | 22246 | 0 | | TC3218 | 375 | 0 | | MC3026 | NULL | NULL | | BU7832 | 4095 | 0 | | PS1372 | 375 | 0 | | PC | NULL | NULL | +--+---+---+ 18 rows in set (0.04 sec) It appears that mysys 4.1 does not know how to multiply a dollar amount to another number. Has anyone else seen this problem? What does a show create table give for the price column? I bet it is varchar. The only way to make it work then would be to trim off the dollar sign and cast it to a float or double. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database size
Eko Budiharto wrote: Hi, I would like to ask about the size that can be handled by mysql. How big if I use innoDB? How big with myISAM? Quoting from the manual 65536 terabytes http://dev.mysql.com/doc/refman/5.0/en/table-size.html Try 5 minutes of searching the manual next time. Although I'm not sure why this question isn't in the FAQ as often as it is posted here. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett wrote: (I know that TIMESTAMP has a far smaller date range than DATETIME. But all our data has to be time-zone independent. Therefore TIMESTAMP is the only field appropriate for our use.) try and see if this works SELECT * FROM a WHERE cast(t as datetime) '0001-01-01 00:00:00' I only have 4.1 so I don't know what would happen in 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select 9000000000000.10*19.80 wrong result 178200000000001.97
wang shuming wrote: Hi, select 9.10*19.80 wrong result 1782001.97 if = 9000.10*19.80 right result178201.98 This is very typical floating point error. Computers don't have infinite precision or it would take infinite time to compute the answer. I have to ask why you need such precise number. In the real world if you can measure or control something to 4 significant digits you are doing really good. If you can do 6 you are doing great. If you can do 8 you are one step from a miracle worker. Measuring or controlling something to a level of precision of 16 significant digits is completely impossible. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select 9000000000000.10*19.80 wrong result 178200000000001.97
George Law wrote: You don't need to take it to 16 digits : mysql 4 : select 1/666; +---+ | 1/666 | +---+ | 0.00 | +---+ mysql 5.0.18: select 1/666; ++ | 1/666 | ++ | 0.0015 | ++ That has nothing to do with the precision of the calculation. It is due to the assumptions MySQL makes about how many digits to display. Try select 1.00/666 and it will give the 0.0015. Add more zeros, and it displays more significant digits. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Limitations??
Harish TM wrote: hi... I need to store something like a couple of million rows is a MySql table. Is that ok or do I have to split them up. I intend to index each of the columns that I will need to access so as to speed up access. Insertion will be done only when there is very little or no load on the server and time for this is not really a factor. I also do not have any constraints on disk space. Please let me know if I can just use MySql as it is or if I need to make some changes I have a table with around 900,000 rows and I know others have tables with many millions of rows, I think I read some even have tables with billions or rows. The limitation you hit is generally because of your OS. Most OSs have an upper limit on file size. Since a table is stored in a file, that file size limitation of the OS is what generally limits the table size. In which case the number of rows depends on the size of the rows. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
