Posts by smantscheff

there is already data in the table...

If you want help, show it. Or submit a test case which shows the undesired behaviour.

drop table if exists plants;
create table plants (date char(10), plant integer, value integer);
insert into plants values 
('date1','1','10'),
('date2','1','12'),
('date3','1','15'),
('date4','2','30'),
('date5','2','34'),
('date3','2','43');
select p1.date, p1.plant, value from plants p1 where p1.date = (select max(date) from plants where plant=p1.plant group by plant ) 
;

Show some test data (in form of INSERT statements) which should give a non-empty result.

Create a temporary table (country_id, city_name), load your 5.000 queried records into it and then form a join between this temporary table and the big cities table.

By means of replication.
http://dev.mysql.com/doc/refman/5.1/en/replication.html

To insert the comment you'll have to know to which blog entry it belongs.
To insert a comment for entry with id=2, use:

INSERT INTO cms.comments(blog_id, name,email,comments_body) VALUES (2, 'theName', 'theMail', 'theComment' );

You cannot use a WHERE clause in an INSERT statement.

Foreign keys are to guarantee database integrity. If you maintain only the local database and update the remote by use of federated tables, then integrity errors on the remote should not occur - except with incomplete transactions. Do federated tables support transactions?

Look for an mysql primer which explains the uses of the INSERT, UPDATE and DELETE stamements to you. For specific help, post the relevant table structures you're using.

If that doesn't help, maybe you should spread your database on several servers by means of partitioning. http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Oh, that looks promising. Thanks for sharing.

A cleaner table structure would have made this easier.
I'd propose to have a separate position table as a n:1 child table of members which records the positions:

create table positions (mem_id integer not null, position enum(1,2,3,4,5,6,7), foreign key (mem_id) references members (mem_id));

Then you could state your query as:

select mem_id from members where not (mem_id in( select (mem_id from positions));

or

select m.mem_id from members m left join positions p on m.mem_id=p.mem_id where p.mem_id is null;

If your idea is to replicate the development steps on the production server by paralleling database statements, then I too deem this as highly insecure. Your development path should instead include a routine which runs all necessary database commands against the production database after they have been thoroughly tested in the development environment. Otherwise you wouldn't need a development environment at all and could develop directly on the production server - bugs and downtime included.
Still, to answer your question, you could install the database on the production server as db2 with the same credentials as db1 and then duplicate your transactions.

I cant do this if they are on different servers?

AFAIK, no.

If not (which would slow things down alot....), any way I can emulate it in a Java servlet at least?

Slow down how many things? How many transactions per second do you expect? Why can't you use standard mysql replication? And as I said,

execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails

If you do it with Java or PHP or whatever, does not matter. You just have two connections to which you feed the same statements.

Yes you can, but only if db1 and db2 share the same connection parameters: are on the same server and have the same user credentials (username, password).

I don't think there is an API or function to connect to an external database from within a stored procedure.
You could alternatively set up a complete database replication or execute the DML statements from your scripting environment by use of transactions which you rollback if one command fails.

Oracle bought MySQL to get rid of the free competitor. That may suffice to prove that MySQL in standard situations (like running a website) can do anything which Oracle can, only for free. If you want the bleeding edge, try MariaDB instead.

No I can't. If you don't understand the 4 steps you should first learn about the linux OS, how daemons/services work, how you modify partitions etc.

If your partition runs out of space you have to increase it. This has nothing to do with mysql.
You can move the mysql data to a larger partition in 4 steps:
1) stop the mysql daemon
2) move the data directory (default /var/lib/mysql, look in my.cnf) to another directory on a larger partition
3) change the datadir entry in my.cnf
4) restart mysqld

1. Don't use PHP to learn MySQL. Install the mysql command line client instead. Otherwise you will have a hard time to tell apart mysql and php mistakes and errors.
2. Im mysql you assign a value using the INSERT and UPDATE commands.
To insert key/value pairs in table1, use

INSERT INTO table1 (staff_id, staff_role) VALUES (0,'corr'),(1,'princi'),(3,'staff');

3. Don't use abbreviations. What is "corr", what is "princi", and who will know it when he has to revise your code in years to come?
4. staff_id and staff_role seem to be redundant in one table. If one staff_id point to exactly one staff_role, then staff_role should be eliminated from table1.
5. When you use the ENUM data type there is no need for numerical codes into the values. With ENUM types you can use the literal string constants 'corr', 'princi' etc. directly in your code without bothering with numerical codes for them.

Your query tries to compare the equality of a single value with a whole set. Try instead:

SELECT * FROM usergroups u, links l where u.`user_id` = '1' and find_in_set(u.group_id,l.group_id);

As the error message asks:

Is there a MySQL server running on the machine/port you are trying to connect to?

Install the mysql command line client and test if it can connect to your mysql server on your local machine. Or, if you're running windows, have a look at the services: is mysql active? Or, in linux, look which processes are running. Is mysqld running?

If you have a concrete question, a piece of code which doesn't work or a query which goes amiss, post it and we will try to help.

Use an enum field with literal values. Define it as
myField enum('admin','manager','staff')
instead of numerical values.

I did not mean the OS's ENVIRONMENT but the ecosystem in which you dwell. No, it's not an ENVIRONMENT problem as you get error messages from apache which means that the server is running o.k. But on which machine does it run, which URL do you use, and what do the apache error log has to say about your attempts?

The apache log usually resides in /var/log/apache (on linux) or in apache/logs (on windows) on the machine on which the apache server is installed. What do you mean with "their site"? I don't understand your environment setup.

Did you restart the server after the changes? That would be necessary.
Also, have a look at the apache error log which in standard configuration often contains reasons for access restrictions.

The error message is from Apache, it has nothing to do with MySQL. Look in your apache configuration file (httpd.conf) and in the local directory's .htaccess files for access restrictions.

$result = mysql( "select * from users where url='$url' and name='$name'" );
if (mysql_num_rows($result) > 0)
  echo "Error: User already in database";

Export all rows in a text file. Fold your various in- an exclusion conditions into a regular expression and filter the textfile using grep. Might be much faster than mysql.

Have a look at the LOCAL option in the upload statement and make sure that upload.txt is readable by the mysql daemon (not by the mysql user).
See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You have to drop the foreign key relation before you can drop the index. Have a look at the output of

SHOW CREATE TABLE likes

It will show you the internal name of the foreign key constraint which you set up with your first statement. You have to drop this foreign key with

ALTER TABLE likes DROP FOREIGN KEY key_name

before you can drop the index on the foreign key field. InnoDB relies on indexes on all key columns in a foreign key constraint.

Try to find the shift key on your keyboard and unlock it.
What response do you expect from the server?

A comma is missing at the end of the previous line.
address TEXT NOT NULL,

Dump the database with the command line tool mysqldump and import the dump file using the command line tool mysql on the second server.

Replace

CONCAT('£', act_price) AS Amount,

by

sum(act_price) AS Amount,

If you have a function distance(zip1,zip2), you can use it in a query:

select a.username, b.username, distance(a.zip_code,b.zip_code) 
from user a, user b
where a.username='coolUser' and b.username='blueUser'

If you want all users sorted by distance to a given user, query

select a.username, b.username, distance(a.zip_code,b.zip_code) 
from user a, user b
where  b.sex = 'M' and b.birthdate > 4-12-1986
and a.username='coolUser' and b.username!=a.username
order by distance(a.zip_code,b.zip_code)

I don't mind that you don't know how to tackle your problem - that's how we all got here in the first place. What upsets me is that you got an error message from mysql which tells you where the trouble with my solution is:

#1052 - Column 'mem_id' in field list is ambiguous

Your first explanation was fine already. I believe that my two attempts at a solution - if used as intended - should give you the desired result. So, did you include the clause "AND position_1.mem_id IS NULL" in your query or not?

It might help if you knew what you are doing.
I omitted the clause AND position_1.mem_id IS NULL in the SQL syntax formatting in my query above. Add it, and you will get more sensible results.
And yes, it can be done without a left join: see my example #1.
A left join contains all rows from the left table and all matching rows from the right table. It contains even rows where there is no match in the right table. For those rows any right table column expression evaluates to NULL.

Prepend the ambiguous field names with the table names:

SELECT members.mem_id, user
FROM members
LEFT JOIN position_1 ON members.mem_id = position_1.mem_id
WHERE (
bypass_purchase = 'P'
OR bypass_purchase = 'Y'

)
AND position_1.mem_id IS NULL

select mem_id, user
from members
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and not (mem_id in select mem_id from position_1)

or

select mem_id, user
from members left join position_1
on members.mem_id=position_1.mem_id
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and position_1.mem_id is null

That is a mistake I won't make again because I'll write a script to do

Yes you will. And your database will crash someday. And then you'd better had some backup mechanism - and if you had, you might retrieve the lost information from there. So set the backup high on your priority list.

You should at least alter the column names. How do you anyone expect to debug this database with names like "s1" or "regno"?

For normalization purposes the subjects should be in their own table and there should be a relation students_and_subjects.

If regno is the student's id and s1 to s4 the grades in up to 4 subjects then you can query the subject count above a certain GRADE with this query:

select regno, if(s1 >= @grade, 1, 0) + if(s2 >= @grade, 1, 0) + if(s3 >= @grade, 1, 0) + if(s4 >= @grade, 1, 0) as number_of_passed_subjects
from sem1
where regno=@student_id

Most probably your child table contains values which are not in the master table. Check that all asset_type values of the child occur in the master.

Try:

UPDATE productbalance 
SET Quantity_OnHand = (
  select sum(Product_OrderQuantity) from addproduct 
    where Product_ID= productbalance.Product_ID
- select sum(Sale_Quantity) from sellproduct 
    where Product_ID= productbalance.Product_ID
+ select sum(BoL_Product_Quantity) from reportfoundproduct
    where Product_ID= productbalance.Product_ID
)
where Product_ID= productbalance.Product_ID
;

The mysql command line client is in the mysql server package which you can download from mysql.com. Install mysql on your local machine - even if you don't need the server, because the client makes use of some of the libraries -, and then you'll find it in the mysql/bin directory.

If your web host is any good chances are that you won't be able to access your database from the outside.
In shared servers mysql is usually ocnfigured to respond only to requests from the local machine, for example from the webserver which runs phpMyAdmin.
Try to access your database with the mysql command line client:

mysql -u<user> -p<password> -h<host> <database-name>

If this works, enter those paremeters in your java connect string.

You have to escape the quotation marks.

So you weren't looking for a global search/replace but only for the standard replace function applied to a specific column in a specific table - which is quite another task.

I just installed phpMyAdmin to see what your problem might be, and it turns out that you are misunderstanding the format options.
Format SQL is for sql scripts which can be parsed and executed by the database.
What you want is format CSV which shows also the field and line delimiter options for your CSV format.

Obviously phpMyAdmin does not recognize the comma as a field delimiter. Switch it to semicolons.