Posts by smantscheff

Yes, you have to apply ifnull to any column which you want to concat() and which might be NULL.
Try this:

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, ''))) AS PropertyRef, PropertyID, ProductTypeID FROM properties

You have not yet understood the ifnull function. Look into the manual.
Replace your blanks by empty strings (' ' => '').
Insert a blank (' ') after each ifnull() function call.
Experiment like this:

select ifnull('abc','xyz');
select ifnull('abc',NULL);
select ifnull(NULL,'abc');
select concat(NULL, 'abc' );
select concat(ifnull('abc','xyz'), ' ', 'xyz');
select concat(ifnull(NULL,'xyz'), ' ', 'xyz');

Your last ifnull clause has only one parameter [ifnull(properties.PriceofProperty)].
The ifnull() function returns the first parameter except if it is NULL. In this case the second parameter gets returned.
If the concat() function gets a NULL as one of its parameters, it will return NULL.
Therefore each column has to be guarded against NULL values if you want to use it in a concat function.

Any concatenation with NULL results in NULL.
Therefore you have to use the ifnull function:

SELECT CONCAT(ifnull(properties.PropertyRef,''), ' ', ifnull(properties.SaleType,'') ...

If Mysql isn't the best, as you say, how would you solve this in another DBMS?

Some punctuation marks would make your gibber more readable.
Set the timeouts wait_timeout and global interactive_timeout in your server configuration and restart the mysql server.

The httpd.conf timeout is a timeout for the web server, not for mysql. Which language or framework do you use for connecting your website to your mysql server? And how does your code explicitly close the connection? Please show.

If this is an mysql problem, you can check it by logging into your mysql server with the mysql command line client with the same parameters as your website scripts. Then enter "show status" at the mysql command line. If there are a lot of processes running, your script does not come back from its queries and you will have to check it.

Do it in two steps. First without the plot, then update the plot field with the plot field content from the old table.
This assumes, that all non-null plot fields have identical content. If they have not, select the longest field content.

insert into movies_merged (id, title, genre)
select a.id, a.title, group_concat(a.genre)
from movie_genre a
group by a.title
;
update movies_merged a set plot = 
(select plot from movie_plot b where a.title=b.title and plot is not null)
;

This should also be much more performant than the single query.

Try on a smaller sample first.
Set an index on all relevant fields.
Which interface are you using? I would recommend to start the process from a mysql console on the database server. Be prepared to wait quite some time until it finishes (if ever). You can look at what the server is doing with "show status" in a second mysql console.

First of all make sure that your set definition is complete. As of now, it lacks "Biography" and "Music".

Afterwards go along those lines:

drop table if exists movie_genre;
create table movie_genre
(id integer
,title text
,genre varchar(255)
);
drop table if exists movie_plot;
create table movie_plot
(id integer
,title text
,plot text
);
insert into movie_genre values
(1, 'Copying Beethoven (2006)', 'Biography' ),
(2, 'Copying Beethoven (2006)', 'Drama' ),
(3, 'Copying Beethoven (2006)', 'Music' )
;
insert into movie_plot values 
(1, 'Copying Beethoven (2006)', 'A fictionalized account of the last year...')
;
drop table if exists movies_merged;
create table movies_merged 
(id integer
,title text
,plot text
,genre set ('Action','Adventure','Adult','Animation','Biography','Comedy','Crime','Documentary','Drama','Fantasy','Family','Film-Noir','Horror','Music','Musical','Mystery','Romance','Sci-Fi','Short','Thriller','War','Western')
,rating float
);
insert into movies_merged (id, title, plot,genre)
select a.id, a.title, b.plot, group_concat(a.genre)
from movie_genre a, movie_plot b
where a.title=b.title
group by a.title
;

Beware: MySQL will pick any (presumably the first) ID from the group, as this field is not processed by an aggregate function.

If you are not able to prepare some test data - which do not have to be your actual customer's data - you should go back to square 1 and try again.
Apart from that your WHERE clause is quite unreadable. I doubt that even you fully understand what you are doing there.
Bracket your AND and OR clause to avoid logical errors.
Your WHERE clause

(DateSignup BETWEEN '2010-11-01' AND '2010-12-01' OR PaymentCycle = "1" AND DateSignup <= '2010-12-01') 
OR 
(PaymentCycle = "12" AND 
	(YEAR(DateSignup) < YEAR('2010-11-01') 
	AND YEAR(DateSignup) < YEAR('2010-12-01') 
	AND MONTH(DateSignup) >= MONTH('2010-11-01') 
	AND MONTH(DateSignup) <= MONTH('2010-12-1') 
	AND DAY(DateSignup) BETWEEN DAY('2010-11-01') 
	AND DAY('2010-12-01')
))

can be recoded as

(	(DateSignup BETWEEN '2010-11-01' AND '2010-12-01') 
OR 
	(PaymentCycle = "1" AND DateSignup <= '2010-12-01')
)
 
OR 
(PaymentCycle = "12" AND 
	(YEAR(DateSignup) < 2010
	AND YEAR(DateSignup) < 2010
	AND MONTH(DateSignup) >= 11
	AND MONTH(DateSignup) <= 12
	AND DAY(DateSignup) BETWEEN 1 AND 1
))

which is obviously nonsense.

So prepare some test data and we'll see how we can help with that.

This query is not syntactically correct in MySQL. How do you validate it?

Test data would really help.
If I understand you correct, the WHERE condition would change to:

((DateSignup BETWEEN '2010-11-1' AND '2010-12-1') AND (PaymentCycle = "1" OR PaymentCycle = "12")) 
OR 
((DateSignup < '2010-01-01') AND (PaymentCycle = "12"))

It is not clear what your problem is. Submit some test data and the results you would like to have.
I only noticed that the in the where clause (DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "1") OR ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "12") you are duplicating the DateSignup conditon which might lead to erroneous coding. I'd rather code: ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1') AND (PaymentCycle = "1" OR PaymentCycle = "12"))

A query can validate and still have a result with no rows.
Please show your code sample.

If the auditlog table has a primary auto_increment key, you can insert the records into a new table by selecting all fields except the auto_increment field for which you insert a NULL.
Example:

insert into newtable (id, field2, field3, field4) select NULL, field2, field3, field4 from oldtable;

This leads into problems, though, if there are some relations which refer to the id field. So if this does not solve your problem post the table structure and the foreign key constraints or relations.

There is no database application which you could "open". MySQL does not come with a GUI. MySQL server usually runs as a service. You can connect to the server with various command line client programs like mysql, mysqladmin, mysqldump etc. which you find in the mysql/bin folder.

Not in standard SQL. But have a look at the MySQL function GROUP_CONCAT() which will help you.
And submit a complete test case if you want more help.

Join the tables on the field userid and use there WHERE search for the name only on the user table.

$sql = "mos_users.id as userid, block, username, email, lastvisitDate, firstname, lastname, avatar, cb_age, userip FROM mos_users, mos_comprofiler, mos_comprofiler_plug_iplog WHERE mos_users.username = '". $user."' AND mos_comprofiler.user_id = mos_users.userid AND mos_comprofiler_plug_iplog.userid  = mos_users.userid";

(not tested)

Permanent settings are in Linux stored in /etc/mysql/my.cnf or /etc/my.cnf, depending on the distribution.
On a Mac you probably won't find the file and you'll have to create it. See http://forums.mysql.com/read.php?11,366143,376017#msg-376017

select surname, subj_name, mark
from student st
left join exam_marks em on st.stud_id=em.stud_id
join subject su on em.subj_id=em.subj_id

If this does not fit your needs, please post a complete test case with table structure, test data, your query, the expected results and the actual results. Then it will become clearer to you and to us what you are aiming at.

What means "optimum code" ? In number of code lines? In performance? In usability?
Maybe I would try to solve this not in MySQL but in plain text format on a dump file with regular expressions (grep or, in PHP, preg). This could be the optimum in coding efficiency.

Study this: http://dev.mysql.com/doc/refman/5.1/en/stored-procedure-syntax.html

If you are grouping rows, the non-grouped columns have to be aggregate functions. Otherwise MySQL will pick one row of each group at random (I believe the first one in historical table order).
Consider those examples;

drop table if exists newproducts;
create table newproducts (id integer, groupcode integer, updated integer);
insert into newproducts values (1,1,10),(2,1,9),(3,2,7),(4,2,8);
SELECT * FROM newproducts GROUP BY groupCode ORDER BY updated DESC LIMIT 2;
+----+-----------+---------+
| id | groupcode | updated |
+----+-----------+---------+
|  1 |         1 |      10 |
|  3 |         2 |       7 |
+----+-----------+---------+
SELECT groupcode,updated FROM newproducts GROUP BY groupCode ORDER BY updated DESC LIMIT 2;
+-----------+---------+
| groupcode | updated |
+-----------+---------+
|         1 |      10 |
|         2 |       7 |
+-----------+---------+
SELECT groupcode,max(updated) FROM newproducts GROUP BY groupCode ORDER BY updated DESC LIMIT 2;
+-----------+--------------+
| groupcode | max(updated) |
+-----------+--------------+
|         1 |           10 |
|         2 |            8 |
+-----------+--------------+
SELECT * FROM newproducts where (groupCode,updated) in 
  (select groupcode,max(updated) from newproducts group by groupCode)
;
+----+-----------+---------+
| id | groupcode | updated |
+----+-----------+---------+
|  1 |         1 |      10 |
|  4 |         2 |       8 |
+----+-----------+---------+

Convert your first procedure to a function which returns the desired value and call it from your 2nd procedure.
Apart from that this looks like nonsense to me.
What for do you need a stored procedure which does nothing than insert a row into a table?

Maybe you mean something like this:

drop table leagues;
create table leagues (id integer, f1 enum('no', 'yes'), f2 enum('no','yes'));
insert into leagues values 
(1,'no','no'),
(1,'yes','no'),
(2,'no','no');
select id, 
if(sum(if(f1 = 'no', 0, 1)) = 0, 'no', 'yes') as field1, 
if(sum(if(f2 = 'no', 0, 1)) = 0, 'no', 'yes') as field2
from leagues
group by id;

+------+--------+--------+
| id   | field1 | field2 |
+------+--------+--------+
|    1 | yes    | no     |
|    2 | no     | no     |
+------+--------+--------+

Have a look at this example.

drop table c;
drop table t;
drop table p;

create table p( id integer, id_t integer, content text );
create table t( id integer, id_c integer, content text );
create table c( id integer, name text );

insert into p values (1,1,'c1'),(2,1,'c2'),(3,2,'c3'),(4,2,'c4'),(5,2,'c5');
insert into t values (1,1,'t1'),(2,2,'t2'),(3,2,'t3');
insert into c values (1,'cat1'),(2,'cat2');

select * from
(select c.*, count(*) as count_threads
from c join t on t.id_c=c.id
group by c.id) join1
join
(select c.*, count(*) as count_posts
from c join t on t.id_c=c.id join p on p.id_t=t.id
group by t.id) join2
on join1.id = join2.id

IMHO it does what you require.

+------+------+---------------+------+------+-------------+
| id   | name | count_threads | id   | name | count_posts |
+------+------+---------------+------+------+-------------+
|    1 | cat1 |             1 |    1 | cat1 |           2 |
|    2 | cat2 |             2 |    2 | cat2 |           3 |
+------+------+---------------+------+------+-------------+

You can limit the paged entries in a SELECT DISTINCT clause like that:

drop table if exists a;
drop table if exists b;
drop table if exists ab;
create table a (id integer);
create table b (id integer);
create table ab (a_id integer, b_id integer);
insert into a values (1),(2),(3),(4),(5);
insert into b values (1),(2),(3),(4),(5);
insert into ab values (1,1),(1,2),(1,3),(2,1),(2,2);

/* page 1 */
select a.*, b.*
from a 
inner join (
  select distinct a_id from ab order by a_id limit 0,1
) x
on a.id=x.a_id
inner join ab
on a.id=ab.a_id
inner join b
on ab.b_id=b.id
;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+

/* page 2 */
select a.*, b.*
from a 
inner join (
  select distinct a_id from ab order by a_id limit 1,1
) x
on a.id=x.a_id
inner join ab
on a.id=ab.a_id
inner join b
on ab.b_id=b.id
;
+------+------+
| id   | id   |
+------+------+
|    2 |    1 |
|    2 |    2 |
+------+------+

What syntax errors do you get? Do you get them also from the command line MySQL client? And do they persist if you delimit your statements with semicolons?
Also I think you have to add an alias clause to every sub-select which uses the same tables as the main select, so you need one table alias name for each FROM clause except for the outermost.

Where does last_post_datetime come from? Is it a field in categories ?
There is at least one error in your SQL: the join clause ON NP.thread_id = NT.category_id cannot be correct. Does the error persist when you fix that? And what are your unexpected results?

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.

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.

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?

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);

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.

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 …

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.

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

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

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

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.