Posts by smantscheff

Where do you "click import"? Which interface do you use? What are the settings, especially regarding character sets?
If you want to control all relevant parameters use the mysql command line utility and the LOAD FILE command.

How do you import data? Which error do you get?

How do you want to learn anything if you don't even try?
If this is really a MySQL question, use the LIMIT clause in your query.
If it is standard SQL, use something like

SELECT * from Students A WHERE (SELECT count(*) FROM Students B WHERE A.marks < B.marks) = 2

If you use the InnoDB engine with foreign keys, both primary and foreign key tables must have indexes on the common key field. Even with other engines indexes in all tables linked by foreign keys will boost performance.

You can
a) use an interface like Navicat which is capable of global search/replace operations;
b) dump the whole database to a file, replace the string on the file and reload it into the database.
To the best of my knowledge there is no mysql function which does what you wish.

Add a column to your table which contains the function value, and create a trigger which computes this function value on any update of the record. Then you can use an index on this column.

You have to bracket the clauses of the OR condition:

SELECT * 
FROM rwb_bk_schedule s 
INNER JOIN rwb_bk_rooms r 
ON s.room_id = r.room_id 
WHERE s.room_status=1 
AND r.room_name=$room
AND (start_date BETWEEN $sdate AND edate OR end_date BETWEEN sdate AND $edate)

Without a test case I'm not sure what you mean.
It looks like bad table design. Can you change it?

Can you do it with 4 summands?

(select count(*) from connections c, persons a where c.connect_to_id = a.id and c.connection_type = 1 and a.user_id=@super)
+ (select count(*) from connections c, artists a where c.connect_to_id = a.id and c.connection_type = 2 and a.user_id=@super)
+ (select count(*) from connections c, companies a where c.connect_to_id = a.id and c.connection_type = 3 and a.user_id=@super)
+ (select count(*) from connections c, events a where c.connect_to_id = a.id and c.connection_type = 4 and a.user_id=@super)

What do you mean by "does not work" ?
Show the relevant table structure.

How about this:

select 
count(*) from persons where user_id=@super
+ count(*) from companies where user_id=@super
+ count(*) from artists where user_id=@super
+ count(*) from events where user_id=@super

select * from project p, replies r
where p.project_id=r.project_id
and
r.mydate = (select max(mydate) from replies rr where rr.project_id=p.project_id group by project_id)

Looks more clean to me. That way you avoid doubling of attributes and keep their semantic limitations.

In my opinion you have to merge all members with shared attributes in one table. Otherwise you might get member Charles, 101 High Street, in the Customers as well as in the Admin table. This would contradict the paradigm of non-redundant data storage. If you still would like to separate other attributes in separate tables, those should contain foreign keys pointing to the member table.

Yes, that is no problem, in one table you can have several different foreign keys pointing to the same foreign table.

What makes a customer a customer or a driver a driver if not the fact that the customer books a driver. So what is the use of the customer and driver tables? They are just users and should be stored in a common table, sharing a lot of attributes - the all have names, addresses etc. The admin is just another user with some special rights - there should be a flag in the user table denoting if a user is an admin.
Otherwise you will get redundant data as soon as an admin decides to patronize the establishment as a customer or to get a 2nd job as a driver.

You can use the variable names of your parameters in stored procedures. But what do you mean with "append to query"? Give an example.

To me it seems that you just need a regular database backup. I would set up a daily cronjob which dumps the whole database into a file with the current date in the filename. Then you can afterwards reconstruct any state of the database for a given date. Another option would be to use binary logging - make a backup of the current database status, and by means of the binary log you can reconstruct any point in the lifetime of your database after the backup.

You're right about the superfluous 2nd table. It grew out of some testing.
I didn't do any performance testing. I assume that differences with such small datasets are due mainly to the execution time for parsing and optimizing the query.

I didn't know that you can use LIMIT in subqueries. Must be new.
Here my alternative:

select m1.*
from mytable m1, mytable m2 
where m1.id=m2.id
group by m2.sid
limit 2;

Test case:

use test;
drop table if exists mytable;
create table mytable (id integer, sid integer, num varchar(255));
insert into mytable values 
('1','15','one'),
('2','15','two'),
('3','17','three'),
('4','17','four'),
('5','18','five'),
('6','18','six');

select * from mytable where id=(
    select id from mytable where sid=(
        select Min(sid) from mytable
    ) limit 0,1
) or id=(
    select id from mytable where sid=(
        select Min(sid) from mytable where not sid=(
            Select Min(sid) from mytable
        )
    )limit 0,1
);

select m1.*
from mytable m1, mytable m2 
where m1.id=m2.id
group by m2.sid
limit 2;

This works only because of a (in my opinion) mis-interpretation of the GROUP BY clause in MySQL.

It is not clear how the output of your query should look like.

If you query more than one table you have to
- list all tables in the FROM clause: add ", userlist" to the FROM clause;
- use a where clause which semantically links the rows of one table to the other. This is missing in your where clause.
It is also syntactically wrong - replace the second "WHERE" by "AND".

Your query

SELECT id FROM vwRetrieveCorporateDetails WHERE companyName=companyName AND rocNo=rocNo AND country=country

has the same names on both sides of the equation signs. Which means that all records are retrieved. You have to use variable names which are not the same as the column names.

SELECT N_Etudiant, Professeur, count(Professeur) as CP
FROM Foyer
GROUP BY N_Etudiant, Professeur
HAVING CP > 2;

In PHP you do the same as in MySQL: You bracket all queries which belong to a transaction with BEGIN TRANSACTION and COMMIT or ROLLBACK , respectively. This works only on InnoDB tables, though.

Categories are a tree structure. You cannot write a query which returns a recursive structure. Therefore I recommend that you write a user defined function (UDF) which combines all parent categories for a given category in a string which your application then can display.

MySQL auto_increment fields are counters which move upward only. With each new record the value for the next record is increased by 1 by the system. Thus you can be sure that in each table you have unique values in auto_increment fields even if you deleted some records in between.
To restart the numbering at zero you have to delete and re-create the table.

The mysql_query function sets an internal pointer, the cursor, to record 0. Each subsequent call of mysql_fetch_array retrieves the current row and advances the cursor by 1 row. If you call mysql_fetch_array 3 times, the result of the last call will be the 3rd row.

You should not compare explicit values with float values. Due to rounding and precision issues the value of a float can only be of a limited subset of all real numbers. It is stored as the number which is closest to the input value. Therefore mysql does not store 0.6 in a float field but a value very close to it.
Try this:

create table f(f float);
insert into f values (0.6);
select * from f where f - 0.6 > 0.0000001;
-- Empty set (0.00 sec)
select * from f where f - 0.6 > 0.00000001;
-- f = 0.6
select * from f where f = 0.6;
-- Empty set (0.00 sec)

I have no clear idea of what you are trying, but this here is definitively wrong:

$z=mysql_query("UPDATE levels SET bal=bal-'$new[$i]' WHERE itemsize= '$item[$i]'");
$result=mysql_query($z);

The result of the mysql_query function is a ressource identifier which you cannot use as input for the same function.

Show the output of SHOW CREATE TABLE account . This table has a unique index on three fields which seems to be semantically wrong.

Looks like inconsistent data due to bad table design. Show a data sample.

The default path to search for database objects is the database currently in use. If there is no object of that name (myUDF) in the database, you have either to switch the database or to include the search path (the database name) in the function call.

Your design is definitively flawed as one information - the outcome of a game - is stored in two locations: with each team separately.
I'd propose a structure like:

teams (team_id, team_name)
games (game_id, team_1, team_2, game_result)

With this structure it is much easier to count games, to avoid duplicates or to calculate averages.

It is a matter of interpretation of your application if an exercise is cardio or strength, but not an explicit datum. Therefore I'd rather have only one table for all attributes of the cardio and strength tables and compute from the actual data if a row is a cardio or a strength exercise or something in between.

First of all, this looks like bad table design. A table with similar columns most probably should be split in at least two tables.
Second, you don't tell where your problem is. To count non-zero column values in a stored procedure or function this function will have to contain an expression like

if(game1 is not null, 1, 0) 
+ if(game2 is not null, 1, 0)
...
+ if(game9 is not null, 1, 0)

This sentence no verb.

Hire a programmer. Or come back if you have some code which doesn't work.

mysql_insert_id is an integer. You cannot have database, table or field names beginning with numerals. Prefix the table name with some literal constant.

You can prefix the table name with the (dot-terminated) database name in queries, thus allowing for joins across database limits. To the beste of my knowledge, you cannot create foreign keys, though, which are maintained by InnoDB.

Grant access to the UDF to all involved parties and call it with the database name as a dot-terminated prefix, like in

call testDB.myUDF();

Do not convert NOW() and terminationDate into strings for date arithmetics and comparisons.

select greatest(t1.chan1,t1.chan2,t1.chan3), captureTime
from myTable t1
where greatest(t1.chan1,t1.chan2,t1.chan3) = (
  select max(greatest(t2.chan1, t2.chan2, t2.chan3))
  from myTable t2
  group by substr(t2.captureTime, 1, 15)
)
and substr(t1.captureTime, 1, 15 ) = substr( t2.captureTime, 1, 15)

Lots of query optimization needed, I presume.

Use the commandline client. That way you know exactly what you're doing, and you can easily script and replicate your whole database setup. Pay attention to the various character encoding settings.

Show the query and the table structure. And learn some CSS and how to avoid redundant coding.

I do not see anything fishy in the table set up. Provide test data. Use the mysqldump program to extract them from the database or the database export feature from phpMyAdmin.
A dump is a text output of the database content in the form of sql statements so that it can be used as input to re-create the data.

As I said, show the output of SHOW CREATE TABLE ... here. And also a dump of your test data if you want us to debug this issue.

Is Customer_Infor a spelling error?

Sounds to me like a corrupt index. I never had this problem with mysql, but such where the symptoms 20 years ago with dBase etc. with corrupt indexes. Show the output of SHOW CREATE TABLE kw and try to apply REPAIR TABLE kw

I don't know of such a function.
I'd rather code a routine outside of mysql which generates the mysql query from an array of fieldnames and then poses it explicitly to mysql. mysql does not lend itself easily to meta operations on database object names.

My first try at the query is redundant. Better:

SELECT barcode, quantity, tstamp FROM inventory i1
WHERE tstamp = (
  SELECT max(tstamp) FROM inventory WHERE barcode=i1.barcode AND YEAR(tstamp) = 2010 AND MONTH(tstamp) = 10
)

Show the code. Probably you are omitting some quotation marks.