Posts by smantscheff

Don't use the sum() function:

SELECT planCode, handset, planName, RRP, simRebate, commission, subsidy, 
subsidy + simRebate + commission - RRP AS total
FROM plans
RIGHT JOIN (
vhandsetmatrix
LEFT JOIN vodahandsets
USING ( handset ) 
)
USING ( planNum )

Yes, the test clause is fine - as far as MySQL is concerned.
But if you are passing empty character strings '' or 0 to it, the test will fail. Maybe you should better test for both: IF ((lat IS NULL) [B]or (lat = 0) or (lat = ''))[/B] ... You don't have to install the mysql server, just the command line client (/usr/bim/mysql or mysql.exe) is fine if you have a server already running to which you can connect. The client is available for any flavor of OS.

Check for null with the IS NULL test (http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html). Also have a look at ifnull function.
Debug triggers from the command line interface, not phpMyAdmin or any other higher level client. That way you'll get the unfiltered error messages.

When I try this I get the error message Unknown column 'user_id' in 'NEW' Probably you got some field names wrong (user_id/userid).

My test case:

drop table if exists users;
create table if not exists users (
user_id integer not null primary key auto_increment,
lat decimal (12,7) not null,
lon decimal (12,7) not null
);
insert into users values (1,26.1962610 ,-80.1181660);


create table if not exists message( userid integer not null, occured date, message_text text, 
lat decimal (12,7) not null,
lon decimal (12,7) not null);

DELIMITER //

CREATE TRIGGER `latlong_default` BEFORE INSERT ON `message` 
FOR EACH ROW BEGIN
  declare user_lat decimal(12,7);
  declare user_lon decimal(12,7);
  IF NEW.lat = 0 AND NEW.lon = 0 THEN
     SELECT lat,lon INTO user_lat, user_lon FROM users WHERE user_id = NEW.user_id;
  END IF; 
  SET NEW.lat = user_lat;
  SET NEW.lon = user_lon;
END //

DELIMITER ;

INSERT INTO message (userid, occured, message_text, lat, lon) VALUES ('1', '2010-12-03', 'trigger test without lat/lon', '', '');

select * from message;

This code does what you want:

drop table if exists users;
create table if not exists users (
user_id integer not null primary key auto_increment,
lat decimal (12,7) not null,
lon decimal (12,7) not null
);
insert into users values (1,26.1962610 ,-80.1181660);


create table if not exists message( userid integer not null, occured date, message_text text, 
lat decimal (12,7) not null,
lon decimal (12,7) not null);

DELIMITER //

CREATE TRIGGER `latlong_default` BEFORE INSERT ON `message` 
FOR EACH ROW BEGIN
  declare user_lat decimal(12,7);
  declare user_lon decimal(12,7);
  IF NEW.lat = …

Install a local copy of MySQL and your database which you can download via phpMyAdmin and try the command line client with your trigger.
I strongly assume that phpMyAdmin interferes with the redefinition of the delimiter.

I don't know C#. But I cannot see how "keyvalue" could have a value other than NULL. Nowwhere it gets assigned.
And how do your test the success of your insert query? Maybe you have a problem with the table definition. Would it accept NULL for FileID or for Value?

Maybe you should start with a mysql beginner's course. Or read a manual. It's all in there. And saves a lot of time.

I wouldn't like to deal with the quirks of the MySQL fulltext engine. For example, when I last used it it had a minimum search word length of 3 or 4 characters, so you could not search for "db". I don't know if they changed that, but I was always better off doing my own search routines.
For triggers, consult the manual: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
There are lots of examples there.

sorry, could not load your data, please try again later

This is not a mysql error message. Where does it come from?
Can you connect to 192.168.0.200 from a mysql client at your workstation with the given username? And didn't you forget the password?

Have a look at the function last_insert_id(). It retrieves the last automatically generated id for this session so there has to be no worries about other updates sneaking in.
And bracket your two inserts with "begin transaction" and "commit".
Apart from that, it seems to me like bad design having the same content (file names) duplicated in one table with different ids.

How do you pass the variable value? What does your statement looks like in your code? There is probably an error in your coding so that your variable values do not get into the actual query.
If your mysql server runs a log, you can see in the log file how your query actually gets to the server.

RTFM.
And try

update upload set email=null where id=154;

What is a "dynamic string", what are "fixed data"? Show what you did and what didn't work, then maybe someone will be able to help you.

Looks like your delimiter statement is not accepted. Maybe it's filtered by some library layer in between? Did you enter the code at the MySQL command line client?

Of course it's possible. Google for "c# mysql library" or the llke.
But first you should code as much of your database setup and logic as possible outside of C#. Do it in plain MySQL with some scripts or from the command line and learn to use the database hands-on. Only then start coding in C#.

First make sure that the MySQL full text search really fits your needs. More often than not it doesn't.
If it does, work with triggers. Have your referential keys in InnoDB tables and the text data in MyISAM and have the latter updated by operations on the former (or vice versa).

Well, if you have defined already a primary key, you cannot have another one. What does show create table pqrimasterdetail say?
You can instead add a second unique key: ALTER TABLE pqrimasterdetail ADD [B]Unique[/B] (PQRIMasterID,Type,YEAR,PhysiciansID);

Probably you do not have access rights for the user "root" from an outside address. Login to 192.168.0.200 and check the permissions for the mysql user root.

I'm sorry, I know MySQL but not the sqlite3 library. Maybe you better find a forum which focuses on sqlite, not MySQL, since your problem is obviously not pertinent to SQL per se but to the implementation by sqlite.
Does sqlite have triggers? Maybe you could install a trigger which notifies some agent of the change instead of polling the database incessantly.

I don't know the sqlite3 library. Is there some implicit locking in their functions? Also I would try to include a sleep() into the polling loop - maybe the database is just too busy with the polling to respond. What is the output of "show status" in your terminal window?

This problem does not occur when using other interfaces, e.g. the MySQL command line or Navicat or PHP connections. Therefore I assume that you have some locking logic in your C program which is responsible, not MySQL. Do you use 3rd party libraries which might contain the locking? What is the MySQL error message? Can you reproduce it from the MySQL command line?

Your syntax is wrong at the comma. Condition clauses can be combined with "AND" and "OR" in SQL. Try

SELECT uName, pwd FROM users WHERE uName = '$uName' [U][B]AND[/B][/U] pwd = '$pwd'

Also look exactly where the MySQL error message tells that the error occurs. That's exactly the place: the wrong comma.

You're welcome. :)
Before you use this query in a production environment test it for performance and optimize it. I fear it can generate a heavy server load with big tables.

The crucial point is that you need first a joined table which you can afterwards filter some tuples from. This is what the database engine does when you SELECT something. But for this you have to design the underlying query so that it joins all tuples from which you select the desired ones.
No you are telling me that the desired tuples are

eid X Y
 3  1 1
 4  2 1

so that tid is irrelevant at this stage.
Putting al together, I get to:

select A.eid,X,Y,X-Y 
from 
(
SELECT tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) AS X
FROM tbl1, tbl2
WHERE tbl1.eid = tbl2.eid
AND TYPE =1
GROUP BY tbl1.eid
) as A
JOIN
(
SELECT COUNT( tbl3.id ) AS Y, tbl2.eid
FROM tbl2, tbl3
WHERE tbl2.tid = tbl3.tid
AND tbl2.type =0
GROUP BY tbl2.tid
) as B
ON A.eid = B.eid

which results in

+-----+---+---+-----+
| eid | X | Y | X-Y |
+-----+---+---+-----+
|   3 | 1 | 1 | 0   |
|   4 | 2 | 1 | 1   |
+-----+---+---+-----+

Now on this query you can build your update query:

update tbl2 set status = 0 where eid in 
(select distinct eid from
(
select A.eid, X, Y
from 
(
  SELECT tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) AS X
  FROM tbl1, tbl2
  WHERE tbl1.eid = tbl2.eid
  AND TYPE =1
  GROUP BY tbl1.eid
) as A
JOIN
(
  SELECT COUNT( tbl3.id ) AS Y, tbl2.eid
  FROM tbl2, tbl3
  WHERE tbl2.tid = tbl3.tid …

This is IMHO only a fight about words. JOINs are intended to work for identical column content and can be shorthand for WHERE statements. In MySQL I cannot see any issues arising from using the one or the other.

What you cannot do is use the limit clause in subqueries. This would be an obvious way to go, by MySQL does not yet support it.
Here is another way to do it:

drop table if exists t1;
drop table if exists t2;
create table t1 (id1 integer, date1 datetime);
create table t2 (id2 integer, date2 datetime);
insert into t1 values (10,'2010/10/22 13:55:20'), (11,'2010/10/22 13:56:18');
insert into t2 values (16,'2010/10/22 13:55:19'), (17,'2010/10/22 13:57:30');

select t1.*, t2.* 
from t1, t2
where t2.date2 = (select max(date2) from t2 where date2 <= date1)
;
+-----+---------------------+-----+---------------------+
| id1 | date1               | id2 | date2               |
+-----+---------------------+-----+---------------------+
|  10 | 2010-10-22 13:55:20 |  16 | 2010-10-22 13:55:19 |
|  11 | 2010-10-22 13:56:18 |  16 | 2010-10-22 13:55:19 |
+-----+---------------------+-----+---------------------+

In your query for X there has to be a field on which you can join the X value to the corresponding Y value. Without knowing the semantics of your field names it's hard to guess what you mean. In your results you show that you form the tuples

eid tid X Y X-Y
 3   2  1 1  1
 4   4  2 1  0

My question: How are we to know that eid=3 and tid=2 belong to the same tuple?

How are we supposed to join the results for Y and X on tid if there is no tid field in the query for X? If they should join on x.eid=y.tid , I do not see how you get to your results, since the X result table does not contain a row with eid=2 . Also your column header for your last result table presumable should read " X-Y " instead of " Y-X ".

My contribution is a test case which should have been prepared by you. It shows that your expectations differ from the actual results:

drop table if exists tbl1;
create table tbl1 (id integer, eid integer, quantity integer);
insert into tbl1 values (1,3,2),(2,4,3);

drop table if exists tbl2;
create table tbl2 (tid integer, eid integer, qnty integer, status integer, type integer);
insert into tbl2 values 
(1,3,1,1,1),(2,3,0,1,0),(3,4,1,1,1),(4,4,0,1,0);

drop table if exists tbl3;
create table tbl3 (id integer, tid integer, type integer);
insert into tbl3 values (1,1,1),(2,2,0),(3,3,1),(4,4,0);

/* find x */
select tbl1.eid, (tbl1.quantity - sum(tbl2.qnty)) as X
from tbl1, tbl2 
where tbl1.eid = tbl2.eid AND type = 1;

+-----+---+
| eid | X |
+-----+---+
|   3 | 0 |
+-----+---+
1 row in set

/* find y */
SELECT tbl2.tid, COUNT(tbl3.id) as Y 
FROM tbl2, tbl3 
WHERE tbl2.tid = tbl3.tid 
AND tbl2.type = 0 
GROUP BY tbl2.tid;

+-----+---+
| tid | Y |
+-----+---+
|   2 | 1 |
|   4 | 1 |
+-----+---+
2 rows in set

Pick it up from there and clarify what you want.

Show us your query, show us your tables structure, including the indexes. Use the "EXPLAIN" command to see which indexes are actually used and which just sit there and wait. Which engine do you use?

Maybe you are using another character encoding in php than in phpMyAdmin. Try to submit SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8' (substitute the actual character set value for 'utf8') before you send your query.

Looks to me to a Java question rather than MySQL. In MySQL you cannot pass arrays as query parameters. You would have to explode your array to a concatenation of column names 'a,b,c' and build a query like "select a, b, c from x..."

The days condition in the 2nd parameter to date_sub should read DAY (without the plural "s").

Are you sure you want 30 days? An alternative would be

SELECT * FROM forms WHERE today >= DATE_SUB( CURDATE(), INTERVAL 1 month)

Create unique IDs with auto_increment fields and add whatever prefix you want in your interface.

I don't find this regex character class [:<:] in the docs - what does it mean?

The solution might use a little bit refining. How about this one:

create table test (textfield varchar(255));
insert into test values ('The Opera'), ('Opera, The'), ('My Opera' );
select * from test where textfield rlike "^(The )?opera";

+------------+
| textfield  |
+------------+
| The Opera  |
| Opera, The |
+------------+

You have to select into a variable to use the results in a procedure.
http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html

You could use the replace function:

SELECT name FROM series WHERE replace(name,'The ','') LIKE 'word%' ORDER BY replace(name,'The ','')

This would also replace "The" if it is somewhere in the string. To avoid that (and with no pattern matching replace function in MySQL) you could either write a function which does the replacement only at the start, or you can insert a marker for the start of the string:

SELECT name FROM series WHERE replace(concat('~',name),'~The ','') LIKE 'word%' ORDER BY replace(name,'The ','')

(Not tested)

Please submit the table structure and some test data.

It's hard to tell without the table structure. How about:

select [B]P.prod_id, [/B]PA.proposal_id, PA.evaluator_ID, PA.Primary_Evaluation_Status_ID, PA.Secondary_Evaluation_Status_ID, U.user_Given_Name, U.user_surname, 
from users U, proposal_appraisal PA, [B]proposals P[/B]
where PA.evaluator_ID = U.user_id 
[B]and P.proposal_id=PA.proposal_id[/B]
group by PA.proposal_id, PA.evaluator_ID 
order by PA.proposal_ID, U.user_ID desc

SELECT Proposal_ID, Mark, AVG(Mark) AS Average FROM proposal WHERE (Proposal_ID >= 931) GROUP BY Proposal_ID HAVING Average >= 20;

drop table sample;
create table sample
(id integer,
timestamp datetime,
bags_packed integer);

insert into sample values
('33  ',' 2010-10-02 15:19:23 ','  1'),
('34  ',' 2010-10-02 15:19:24 ','  21320'),
('72  ',' 2010-10-02 11:19:11 ','  2'),
('73  ',' 2010-10-02 11:19:12 ','  18432'),
('92  ',' 2010-10-03 07:19:41 ','  3'),
('93  ',' 2010-10-03 07:19:42 ','  15035'),
('999 ',' 2010-10-03 15:18:55 ','  4'),
('163 ',' 2010-10-03 15:18:56 ','  23937'),
('194 ',' 2010-10-03 11:19:53 ','  19304');

select * from sample where timestamp in (
select max(timestamp) from sample 
group by floor((unix_timestamp(timestamp)+40*60)/(8*3600))
);

There is still an error in it, but this is the direction.

Mysql has a nifty function for it: group_concat
Like in

select org_name, group_concat(activity_name)
from organisation o, activities a, org_activity oa
where o.org_id=oa.org_id
and oa.activity_id=a.activity_id
group by o.org_id
;

(Code not tested)
Alternatively you may look at the various JOIN syntax varieties of MySQL.

How does a single entry look like? How do you compute bags_packed? Is it a sum or a count or a field value?

Off topic: Re "Smart Aunt's Chef": We have a german author, Arno Schmidt, who dealt with this kind of spelling association quite excessively, developing what he called the "Etym theory", an Etym being the core of a word which subconsciously can shift and swap its meaning with similar sounding words. It amounted to that in the end you can suppone any meaning to any text.
The famous comic writer Walt Kelly also practised this a lot, so that when I first read the name "Seminole Sam" of one of his characters I assumed it would have to be the "Same an' Ol' Sam" (not knowing that there is a tribe of that name).
And of course James Joyce with Finnegans Wake. But that is another story...
So your screen name would mean - Ed Wood, tea? A hommage to the infamous movie director?
Good luck with your project...

As I said before, it's not a good idea to store easily computable values in a database. What for do you need a computed subscription date if the subscription period is stable? It only makes sense if you allow manual changing of the subscription end.

Apart from that, for a subscription reminder you have to set up a periodical process which does the checking. Such a mechanism is neither built into the database nor in PHP. Under linux you can set up a cronjob which regularly executes a script which does the checking. (For Windows, google "cronjob windows"). The script would run along the lines:

$q = myqsl('select * from customers where date_add(now(),interval 1 week) > subscription_end_date');
while ($record = mysql_fetch_array($q))
  mail($record['email'],'Subscription about to end', 'bla bla');

[By the way, are you working for my brother or what do you mean?]

I cannot confirm your observation. Here my test code with results:

drop table table1;
drop table table2;
create table table1 
(acct_no VARCHAR(15)
,column1 varchar(10)
);
create table table2
(acct_no VARCHAR(12)
,column2 varchar(10)
);
insert into table1 values ('1',1), ('2',2);
insert into table2 values ('1',1), ('2',2);

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE trim(a.acct_no) = trim(b.acct_no);

+---------+---------+---------+
| acct_no | column1 | column2 |
+---------+---------+---------+
| 1       | 1       | 1       |
| 2       | 2       | 2       |
+---------+---------+---------+

2 rows in set

SELECT a.acct_no,a.column1,b.column2
FROM table1 a, table2 b
WHERE a.acct_no*1 = b.acct_no*1;

+---------+---------+---------+
| acct_no | column1 | column2 |
+---------+---------+---------+
| 1       | 1       | 1       |
| 2       | 2       | 2       |
+---------+---------+---------+

Storing computable values is never a good idea, except for caching and performance reasons.
You could define a query or a view instead like as

select *, date_add(registration_date, interval 6 month) as subscription_end from mytable;

First I would try the performance of your attempt with the expected number of participants. MySQL can be quite performant, so maybe there is no need to change your code.
But probably you would be better off with another type of tree organization, the Nested Set Model. Have a look at http://dev.mysql.com/tech-resources/articles/hierarchical-data.html