Posts by smantscheff

Table structure and data are a good start.
It would be a better start if it would work. Your insert statements contain a syntax error which proves that you did not test them. Plese do not leave all your homework to others.
When you have corrected the error (probably only a typo), prepare the SQL query which you are unhappy with and present it in an repeatable form. Your query contains PHP variables which are not known to us. Of course it would not be hard to reconstruct them, but, as I said before, don't let us do your homework.
Wnen you have the query, please show us the query and the results and tell us why you are unhappy with them.

First try to solve your MySQL problem, then the PHP problem.
Submit a test case which contains the relevant CREATE TABLE statements, some INSERT statements to fill the tables with test data and your query which goes amiss. Then anyone will gladly try to look into your problem.
As of now, your PHP code is quite unreadable. Separate styles from HTML with CSS and PHP logic from HTML with variables. Do all your PHP logic before the output is generated.

This is a bit tricky, but with a subselect it can be done:

select ExamId,StudentId,Mark from marks where (ExamId,Mark) in (select ExamId as e, min(mark) as mm from marks group by ExamId);

The proposal of genevish is no solution since it refers to tables which are not accessible.

Always include a complete test case which makes it a lot easier to view into your problem. Here follows one in which I replaced the IDs by text for readability reasons.

create table marks 
(ExamID varchar(255)
,StudentId varchar(255)
,mark integer
,unique(ExamId,StudentId)
);
insert into marks values
('physics','dinky',1),
('physics','bofur',1),
('physics','gollum',2),
('art','bofur',5),
('art','dinky',8),
('art','gollum',1),
('math','dinky',1),
('math','bofur',2);


select ExamId,StudentId,Mark from marks where (ExamId,Mark) in (select ExamId as e, min(mark) as mm from marks group by ExamId);

+---------+-----------+------+
| ExamId  | StudentId | Mark |
+---------+-----------+------+
| physics | dinky     |    1 |
| physics | bofur     |    1 |
| art     | gollum    |    1 |
| math    | dinky     |    1 |
+---------+-----------+------+

Your desired result differs from your statements about it.
Why does (1,query 2,[url]http://yyy.co.uk/,1288051200[/url]) appear twice in your result set? In which respect is it different from the first line? Maybe you are aiming at something like select min(place),query,fullurl,date from places group by query,fullurl,date; But it is really hard to tell without clarification.

insert into ZTable1 select 1, ZTable.* from ZTable

Or maybe you want

insert into ZTable1 select TId,TId,TName,TAge from ZTable

It's hard to tell from your post.

Post a full test case with table creation and data insertion code together with your presumably erroneous query.

You are confusing levels. On one level (presumably PHP) you are building your query, on the MySQL level you are executing it.
To get rid of the the trailing " and" in your query use something like
$query = substr($query, 0,length($query)-4)
or whatever is appropriate in the used language.
Alternatively just add a "1" to your query like in

SELECT something FROM mytable WHERE condition1 and condition2 and 1

Create the table structure by
mysqldump myDatabase -w0 | mysql myNewDatabase
Then dump your contents with myssqldump -T myDatabase
Then you have a bunch of files (one for each table) which you can load with "LOAD DATA".

And are you sure that your mysql daemon runs as root? This would be a very unusual setup. If I remember right the "SELECT ... INTO OUTFILE" runs with the privileges of the mysqld process, not with those of the mysql user or the unix user who called mysql.

You could map the character values to a reference table which contains characters and their numerical equivalent, query a join of both grade and reference table and select the average number value from the reference table.
Or you could code a function which returns a numerical value for each character and average the function results.
Or you could use an inline function like in

select student, avg(-1 * (ord(grade)-69)) group by student;

(assuming A=4, B=3, C=2 etc.)

I don't find an "EXEC" statement in the MySQL manual. Do you?
Depending on your aims, maybe you should have a look at prepared statements in MySQL.
See also: http://forums.mysql.com/read.php?98,19285,19285#msg-19285

And I forgot to ask:
What value does name have after the insert? Is it NULL or an empty string? Could it be that somewhere in your configuration the emtpty string is set as a default?

In my version of MySQL (5.1.34-community) your code produces the desired error:

Field 'name' doesn't have a default value

So after your INSERT there is really a new row in your table? Or do you just suppress the error messsages?

The line

ON (topics.managerId = quiz.".$managerId.")

should read

ON (topics.managerId = quiz.managerId)

By the way: you don't have to exclude PHP variables from double quotes. WHERE topics.$egroup = 1 is easier to read than WHERE topics.".$egroup." = 1

You forgot the GROUP BY:

drop table if exists recipes;
create table recipes 
(id integer
,name varchar(255)
,cat varchar(255)
,instructions text
);
insert into recipes values
(1,'Tacos','Mex.','Mix and Serve'),
(2,'Kabobs','Beef','Combine/Grill');

drop table if exists ingredients;
create table ingredients
(id integer,
recipe integer,
qty integer,
measurement varchar(255),
ingredient varchar(255)
);
insert into ingredients values 
( 1,'1','1','lb','Beef' ),
( 2,'1','1','md','Onion' ),
( 3,'2','1','lb','Beef' );

select r.id, r.name, i.recipe, r.cat, r.instructions, group_concat(distinct ingredient) 
from recipes r, ingredients i 
where r.id=i.recipe group by recipe;
+----+--------+--------+------+---------------+-----------------------------------+
| id | name   | recipe | cat  | instructions  | group_concat(distinct ingredient) |
+----+--------+--------+------+---------------+-----------------------------------+
|  1 | Tacos  |      1 | Mex. | Mix and Serve | Beef,Onion                        |
|  2 | Kabobs |      2 | Beef | Combine/Grill | Beef                              |
+----+--------+--------+------+---------------+-----------------------------------+

You seem to have reset the admin password with mysqladmin -u root password "newPassword" Afterwards you try to connect but you do not provide a password, as proven by error: 'Access denied for user 'root'@'localhost' (using password: NO)' Could it be that you just forgot the password option -p as in mysql -uroot -pnewPassword

But this is what my proposal returns: any records from A with an x/y combination which is not in B.

mysql> select * from a;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set

mysql> select * from b;
+---+---+
| x | y |
+---+---+
| 2 | 2 |
| 1 | 3 |
+---+---+
2 rows in set

mysql> SELECT A.* FROM A LEFT JOIN B on A.x=B.x AND A.y=B.y WHERE B.x IS NULL;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 3 | 3 |
+---+---+
2 rows in set

So what would be the desired result from your example in scenario 2? Do you want to retrieve any records which have A.x=B.x OR A.y=B.y?

Do you mean that you want to select all records from table A with an x/y combination (x,y being field names) which does not occur in table B?
That would be

select A.* from A left join B on A.x=B.x and A.y=B.y where B.x is null

I'm not sure what you mean.
If you want to know if a point in time has passed for a certain time span use something like

SELECT * from mytable where TIMEDIFF(startdate,NOW()) > '48:00:00'

That's what I meant by doing the math in the database, not in PHP.
And yes, of course, your time fields should be of type TIME or DATETIME, respectively.

You should solve the math on mysql level, not in php.
Have a look into the date/time functions: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Who should know but you? It all depends what the data are used for.
But always include an auto_increment id field in your tables (except for m:n tables) which will make your life much easier.

Put a debug output line before the connection statement in your library to check if it uses the same parameters which you entered in your test script.
Maybe you redefined some constant -?
Also insert a debug_backtrace() in your code where it returns the mysql_error().

You are still using code which you do not control (or did you out-comment 235 of those 236 lines in utils.inc.php?).
Solve one problem at a time.
Write a one-line PHP script with your connection statement and test it.

Maybe you should find a Mssql forum.
And try to ask well defined questions there. Otherwise consult Google.

Is the database configured for connections from outside?
If so, try to connect from a mysql command line client using the connection parameters from your php scripts.
If not, use a PHP test script like this: <?php if (!mysql_connect(SERVER,USER,PASSWORD)) echo mysql_error(); ?> Try to debug only the connection issue, nothing else.

MySQL only allows for sorting on values, may they be field values or function results or constants or whatever, but not on the order of entry into the db.
In a client program like phpMyAdmin you could list the data in reverse order, though, by having a routine like

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {
  mysql_data_seek($i);
  $row = mysql_fetch_row($result);
  display($row);
}

But the more natural and easier way would be to enhance the table by an auto_increment field on which you could sort DESC and ASC using the phpMyAdmin interface.

$GLOBALS is obviously not an object.
It should have been created by $GLOBALS = new BxDolDb();
This seems to have failed.
Try to connect using the standard MySQL client from the command line with the same parameters as coded somewhere in your included scripts.
This might be a compatibility issue between old and new style passwords. Cf. http://dev.mysql.com/doc/refman/5.1/en/old-client.html

If you want any efficiency drop the PHP interface. Use mysql from the command line to perform your tests. If you don't know it, learn how to use it.
For timing your test use the linux "time" command as in time (echo 'update myTable set myField=myValue' | mysql myDatabase -umyUsername -pmyPassword)

If you are using Navicat don't expect performance. Navicat is an easy interface, but not performant. Also it seems that you are transferring databases from one server to another one using your local PC as the middle man. This means that all the traffic is being downloaded to your PC and uploaded to the 2nd server.
Look into mysqldump and the "LOAD DATA" statement of mysql. These are the most performant tools for the task.
Dump the source database, zip it, transfer it to the target, unzip it and feed it into the target using "LOAD DATA". You need of course shell access to both source and target server.

Since Mysql keeps track of the number of changed rows (which it displays after every update) I assume that the system retrieves the current field values before updating them in both cases, so the unconditional update (for all rows) should be slower than the filtered one. This would also depend on index usage, I assume. Did you time your updates? With which results?

Your query is syntactically wrong (WHERE clause after ORDER clause) and semantically wrong because
1. you want to select "WHERE id IN" instead of "WHERE id =" and
2. you do not need to sort the subquery because the sorting is done in the master query.

I doubt that your code returns the correct results - there is a typo "Sliver" which means that your code is not tested.
If the three queries work correctly you can combine them using UNION and select the desired fields from the resulting UNION.

I believe that John Linux did some thinking already before posting his problem.
Apart from that, your solution is technically more efficient while mine IMHO is better readable.
And what I do not like about your solution is that you have to change it in more than one place (WHERE and HAVING) when you add more conditions.

select distinct studentid from results 
where studentid in (select studentid from results where course='mam1')
and   studentid in (select studentid from results where course='phy2');

Test:

drop table if exists results;
create table results (
  studentid integer,
  course varchar(10),
  percent integer
);
insert into results values 
(1001,'mam1', 63),
(1002,'phy2', 55),
(1002,'mam1', 99);
select distinct studentid from results 
where studentid in (select studentid from results where course='mam1')
and   studentid in (select studentid from results where course='phy2');

In which fields do the seemingly duplicated result rows differ? This might lead you to a semantically wrong join (which I cannot tell without test data).
Maybe you could submit some test data to reproduce the problem (i.e. a complete mysql script for creating the tables, filling them and running the query).

If your code is supposed to be PHP, it should read

...where Emp_Id like ('%$empid%') and...

assuming it is quoted with double quotes.
If it is supposed to by JavaScript, drop the '$' before the variable names.
If it is supposed to be plain MySQL, it might rather read:

...where Emp_Id like concat('%', @empid, '%') and...

In my experience the fastest method would be:

1) Copy only the structure of the source to the target.
2) Dump the table contents to single CVS (TXT) files.
3) Load the text files with "Load data..."

Since you are mixing prices, pageViews and dates in mysql and european format it is not quite clear what you are aiming at.
Maybe you could try something along the lines:

select * from prices where date in
select max(date) as date1 from prices group by year(date),week(date)

Test:

drop table if exists prices;
create table prices (date date, price integer);
insert into prices values ('2000-01-01','1');
insert into prices values ('2000-01-02','2');
insert into prices values ('2000-01-03','3');
insert into prices values ('2000-01-04','4');
insert into prices values ('2000-01-05','5');
insert into prices values ('2000-01-06','6');
insert into prices values ('2000-01-07','7');
insert into prices values ('2000-01-08','8');
insert into prices values ('2000-01-09','9');
insert into prices values ('2000-01-11','11');
insert into prices values ('2000-01-12','22');
insert into prices values ('2000-01-13','33');
insert into prices values ('2000-01-14','44');
insert into prices values ('2000-01-15','55');
insert into prices values ('2000-01-16','66');
insert into prices values ('2000-01-17','77');
insert into prices values ('2000-01-18','88');
insert into prices values ('2000-01-19','99');

select * from prices where date in 
(select max(date) as date1 from prices group by year(date),week(date))
;


+------------+-------+
| date       | price |
+------------+-------+
| 2000-01-01 |     1 |
| 2000-01-08 |     8 |
| 2000-01-15 |    55 |
| 2000-01-19 |    99 |
+------------+-------+

Try "SELECT DISTINCT ... "