Posts by smantscheff

So maybe your original code would have worked, too, with the "SELECT" in it? But my version is definitely more efficient. And better readable.

If this is an elementary exercise, what is the solution? And why do you mind helping them with their homework if you don't mind helping them with their work?

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)

To retrieve the values, use a SELECT query.
Study the JOIN syntax (http://dev.mysql.com/doc/refman/5.1/en/join.html) and give it at least a try before you ask for help. Come back when you cannot work it out.
To create a table with those values, use the CREATE TABLE AS SELECT ... syntax (http://dev.mysql.com/doc/refman/5.1/en/create-table.html)

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

This seems to be not so trivial as it looks. You have to find not only some distance between two actors, but the minimum distance. This means that you have to scan all possible paths from actor1 to actor2, including the possibility that they are not connected at all.
I don't know yet the answer, but I found this here thinking about a solution:
http://techportal.ibuildings.com/2009/09/07/graphs-in-the-database-sql-meets-social-networks/

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.

Ok, let me answer: the server load will be 20.000 pages per second. And with a 5 kb page it will be 100.000 kb per second.
Is this much or not? It all depends how the pages are constructed and delivered. Can you use a cache? Can you use the MySQL cache for repeated queries? And so on.
Apart from that, it seems to me like a silly idea to have a database pose as a countdown timer. Maybe you'd better tell us more about your problem, because this thread here gets somewhat misleading.

This would be a task for a cron job.
MySQL has date/time functions, so you could write a trigger which checks and updates a time value on every operation (update,insert,delete), but it would not do anything without such an operation.
You could also code something like

select unix_timestamp('2012-01-01') - unix_timestamp(now()) as countdown;

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.

To be more precise, the trigger should not check how many but the maximum id value for cars of this brand and then add one.

Import the dump file into a fresh database. What is the error message? What is the code immediately before the place which the error message indicates?

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

Do you have access to a backup file in plain text format?

Install a local copy of Mysql server on your machine and try to solve the problem with the command line client (mysqladmin and mysql). Chances are that you will have a better understanding of privileges afterwards which you can then utilize using phpMyAdmin.

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?

You have to grant privileges separately to any localhost users. By default there is a rule in the privileges table which denies all privileges to any user at localhost. To grant privileges to localhost users you have to grant them explicitly with "GRANT ... TO `someone`@`localhost`". And don't forget the "FLUSH PRIVILEGES" afterwards.

Are you sure this is a database problem? How high is the load on your server with intensive DB operations (use "top" to find out)? Try to identify the slow queries (using the slow query log) and optimize them. Do you have all the indexes you might use?

Customers, Items and Employees don't need foreign keys, representing independent stand-alone real-world objects.
Sales belong to Customers and Employees (1 Employee sells to 1 Customer N times), and SalesItems belong to Sales (1 Sale consists of N SalesItems).
So Sales and SalesItems need foreign keys into their master tables.

You cannot use the '+' operator for character strings in MySQL. Use the concat function instead.

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname LIKE concat('$lname','%') or fname LIKE concat('$fname','%')";

Or change your PHP code to

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname LIKE '$lname" . "' or fname LIKE '$fname" . "%'";

Normalization means in effect that you store any piece of information exactly once.
A standard report format may (and should) be based on such a normalization, but contains lots of repeated information and structures. It does not make sense to map a report format 1:1 to a database design scheme.
Start with designing one table for customers, one for items, one for employees and then one for sales and for sales items, which link them all together.
A table named "info" is a guarantee for a troublesome database application. After all, which table does not contain "info" -?

SELECT [U]DISTINCT[/U] tag_name FROM photoTagged WHERE file_id=" . $row1['id'] should give you unique tag_names. If they are not unique, chances are that they contain invisible characters like trailing spaces in which they differ from one another.

And for summing up the field contents use:

SELECT sum(field1) as s1, sum(field2 as s2 from t2

Since MySQL does not allow LIMIT clauses in subqueries, this is no trivial task. If you can tackle the problem on the client side in a procedural language, it's not a problem. But if you want to have exactly one query, it gets tricky.
I remember dimly to have solved such a problem using a comparison with the count of records which have an ID bigger than the current one and selecting only those where this count was 0 or below my LIMIT target.

RTFM.
And change your syntax to

SELECT * FROM `details` WHERE BRAND = 'bmw' OR [U]BRAND = [/U]'audi'

No, I can't. The last C compiler I used was Turbo C by Borland back in the 80s.
On http://dev.mysql.com/doc/refman/5.1/en/source-installation.html it says that you need

A working ANSI C++ compiler. GCC 3.2 or late

to compile MySQL, so that would be the requirement for client programs as well.

I do not see how you might need Cygwin to run MySQL. The server and client programs for Linux and Windows have the same functionality. If your C program uses some shell commands, that's another story, but just for MySQL you don't need it.

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     |
+------+--------+--------+

See to it that you have indexes on all relevant fields.
Show your "CREATE TABLE" statements so that we can see which are missing.
Is there an index on ptntPatientDetailsLang.ptntPatientID ? If not, add it.

Google for "mysql primer" or "mysql tutorial" and you'll find for example:
http://www.webdevelopersnotes.com/tutorials/sql/mysql_primer_creating_a_database.php3
which seems to be a decent primer.

No, that way you have an alias for the column names, not for the tables.
It must go along the line

SELECT Agents.UserID, IFA.UserID FROM Users As Agents, Users As IFA where ...

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 |
+------+------+---------------+------+------+-------------+

In your FROM clause you ought to have a different alias name for each instance of users .

I think you could code a stand-alone procedure and call this procedure from your different triggers. But you cannot define one trigger for multiple tables in MySQL.
Maybe it's possible with an ugly hack. In MySQL 5.1 triggers are stored as external files. You might link several of those files to the same source.

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 |
+------+------+

It should not scare you. You need properly designed indexes so that your query can make use of them. Test your main queries against the database design with the "EXPLAIN <query>" statement which will tell you if your indexes are really made use of in the query.

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.

I don't know about your C libraries, but the MySQL server works the same on Windows and Linux.

Well, do some testing.
Apart from that it all depends of the data structure and your indexes. And your programming skills.
With a bunch of properly designed tables and indexes I doubt that with a standard web application with at most a few 10.000 entries per table you will see any performance loss with the single table approach. The multiple table approach means also that you would be duplicating data structures which is always bad.

So show us your query, show us the results and what is the problem with it.
Besides there is a flaw in your table design.
If "rushyards" (whatever that means) is a function of athleteid and week, and teamid is a function of athleteid and week, too, these two (rushyards and teamid) should go into the same table stats. This also simplifies your query. With that design change there would be no more need for any athlete's data for calculating the team score - you could read it directly from stats grouped by teamid.

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?

To the best of my knowledge this is not possible in MyQL.
A trigger is a procedure which is automatically executed on standard table operations like insert, update and delete. Such a trigger could copy the desired character string to the indexed field.

Your columns are already in UTF8, but in different collations, which means the internal order for sorting characters. The default collation for MySQL is latin1_swedish_ci. Have a look at the results of "show create table" to see where this default collation is set, and modify the table or column where it occurs to the collation utf8_general_ci. Changing the database collation does not affect tables and columns which are already in the database; it's only the default value for new tables.
And yes, I would strongly propose setting all text columns to utf-8 to avoid this kind of hassle. Are the columns which you would rather leave in lation1?

Set all character set variables (database, tables, connections) to UTF-8.

You can create an indexed 4-character field and update it using a trigger.

Please post the schema.

How can an album have exactly one SongID? There are more than one songs on an album. Seems to be a database design issue.
Do a mysqldump of your database and post it here.

Mainly unique key issues.