Posts by smantscheff

Show the output of
SHOW CREATE TABLE
for all involved tables (preferably as text, not as a screenshot).

I wouldn't know of any version issues regarding your problem. Did you try a line break before the INSERT ?

I tried your code (with the table and some field names changed) and it does not generate an error in my test.

DELIMITER //
CREATE TRIGGER confirmation_triggers AFTER INSERT ON `bielefeld`
FOR EACH ROW
IF new.institution='True' THEN INSERT INTO `notifications` VALUES(NULL, new.institution, "Notification here", CURDATE());
END IF;
END;
DELIMITER //

What have you tried so far? Have a look at the to_days() and the round() function which may help to solve your problem.

You say that the level cannot expressed by a mathematical function, but it can be expressed by a function using the TRUNCATE() and CEILING() functions. So you can define level as a user defined function of score and use it in views and queries.

EXPLAIN doesn't tell you anything about execution time but only about query optimization.

Then there should be no difference.

Why not? A binary comparison does not need any conversion before the comparison, while a non-binary string comparison requires some operations for case matching. Therefore a non-binary comparison should need more time - though I never saw a relevant performance gap in a real-life application between binary and non-binary comparisons.

Set an index on productID. Test with the EXPLAIN <query> statement if it is used by the query optimiser.
Other indexes which are not used by query optimisation are irrelevant - it does not harm to keep them.

Looks like you don't have an index on productID. Isn't that your primary key? Show the output of CREATE TABLE tbltransaction

Set an index on transactionDate.
Study the output of EXPLAIN <thequery> for optimisation hints.
How often do you have to run the query, anyway? Maybe you are better of by caching the result instead of optimizing the run-time behaviour of the query.

Have a look at the CAST() function.

Along those lines:

SELECT productID,totalAmountAfter,transactionDate FROM tbltransaction
WHERE transactionDate BETWEEN '2010-01-01' AND '2010-12-31' 
and (productID, transactionDate) in
(SELECT productID, max(transactionDate) from tbltransaction group by productID)
ORDER BY productID,transactionDate DESC

There is a REPLACE statement in mysql with similar meaning as INSERT except that rows with duplicate keys will be deleted before the insertion. The alternative is the INSERT IGNORE statement which will skip any new rows with duplicate keys.

http://www.lmgtfy.com/?q=mysql+command+line+client

The mysql storage format is YYYYMMDD. If you want to enter the data in another format you have to convert it in your application to and from the target date format.

In which format do you have the "huge data"? In general I'd recommend that you use the mysql command line client and the LOAD DATA statement.

If one customer may have more than one bank details set, theory dictates that bank details is a separate table. So leave the overall table structure as is.
A separate table adds only to security if it has separate access rights than the other tables. If you are building a webserver based application, it would be quite awkward to manage different virtual users for different queries and views. If you are building a custom interface, it might make sense to enhance security that way, but only if you make sure that your application is the only one with access rights to the database at all.
In my opinion this is not the most efficient aspect for database protection.

Your design is flawed.
If one customer has exactly one set of bank details, they should go in the same table as the customer data. If you don't store any payments in the payment table, there is no need for it.
If one customer may have more than one set of bank details, you might want to have a `Bank details` table with a 1:n relationship to customers, and further down the line a Invoice and a Payments table. Maybe you should study a few examples in http://www.databaseanswers.org/data_models/
For creating innodb tables just add an "engine innodb" to the create table statement AFTER the last bracket.

Yes, you will need innodb so that the foreign key constrains show any effect. If innodb is your default storage engine you can omit the clause, though.
id_user allows for multiple payments by allowing more than one row in the payments table with the same value in the id_user field.
I prefer utf-8 over latin (and other 8-bit character sets) because it adapts better to multilingual input. If the whole system - database, backend and frontend - share the same utf-8 character set, you will have less character set translation issues.

This is exactly the wrong way round. You need a reference field for the user id in the payment table, too. Change your tables to

CREATE TABLE `Users` (
`Unique ID` int(11) NOT NULL auto_increment,
`Date` date NOT NULL,
`Completed By` varchar(20) NOT NULL,
`Business Name` varchar(60) NOT NULL,
`Address` varchar(100) NOT NULL,
`Contact Name` varchar(20) NOT NULL,
`Postcode` varchar(8) NOT NULL,
`Telephone` varchar(20) NOT NULL,
`Fax` varchar(16) NOT NULL,
`Mobile` varchar(16) NOT NULL,
`E-Mail` varchar(40) NOT NULL,
`Payment Name` varchar(20) NOT NULL,
`Phone No.` varchar(16) NOT NULL,
`Delivery Address` varchar(100) NOT NULL,
`Opening Times` varchar(15) NOT NULL,
`Business Type` varchar(15) NOT NULL,
`Home Address` varchar(100) NOT NULL,
`Nature of Business` varchar(20) NOT NULL,
`Years Trading` int(3) NOT NULL,
`Registration Number` int(10) NOT NULL,
`Registered Office` varchar(100) NOT NULL,
`VAT No.` int(11) NOT NULL,
`Trade Reference 1` varchar(100) NOT NULL,
`Trade Reference 2` varchar(100) NOT NULL,
PRIMARY KEY (`Unique ID`)
);


drop table if exists `Payment Info`;
CREATE TABLE `Payment Info` (
`Unique ID` int(11) NOT NULL auto_increment,
`Bank Name` varchar(20) NOT NULL,
`Address` varchar(100) NOT NULL,
`Account No.` int(8) NOT NULL,
`Sort Code` int(6) NOT NULL,
id_user integer not null,
PRIMARY KEY (`Unique ID`),
constraint users foreign key (id_user) references users (`Unique ID`)
);

To make your life easier you should also encode all data in utf-8 instead of latin1 and not use field names with blanks in them.

select * from users where to_days(exp_date) - to_days(now()) = 7

You seem to have set up the foreign key for the wrong table. Since user:payments is a 1:n relation, payments needs the user id as a foreign key into the user table.
Show the output of

show create table users;
show create table `Payment info`

Use the console mysql client. It will tell you where it fails.

You are trying to enter a payment with a user ID for which there exists no user.
Show us the table contents and your insert statement for further help.

Yes, that would be the first step.
The second step is to ensure relational integrity, which means that your system has to make sure that only valid student IDs and advisor names can be entered. Therefore you need also a student table and an advisor table and two foreign key relations. Alternatively you could make both studentID and advisorName enum fields which can contain only a fixed set of values. But this would be a quite unflexible design.

You cannot have variable names with blanks in HTML forms. Rename your form variables which you retrieve via the $_POST array.

Set all character set parameters to utf8. Query for "show variables like '%char%'" for a list. Set those variables in your my.cnf and set the correct character set for the connection in your application. Set also the database, table and field default character set to utf8.

Your where clause is redundant. Since Status has to be one of "In Progress" or "NTC" it cannot be neither "Activated" nor "Done".

Either mysql is not running or your webserver is not configured properly to connect to it.

First of all, you have a bad table design. Column X should not exists except in a view.
If you still stick to the design, the question is if the values in column X refer to another table. For the sake of the argument let's assume they refer to column C in table T and for each single value in column X there is a referred value in table T.
Then you could query:

select * from myTable, T
where locate(concat( ',', T.C, ';'), X)
or X rlike concat( '^', T.C, ';')
or X rlike concat( ';', T.C, '$');

Your solution lists only two of the the three possible status values. If you really don't care about the status you can just eliminate it from your query:

SELECT * FROM installs 
WHERE r1 = 'Standard' 
ORDER BY building, appointment_date ASC

select * from test 
where name = 'Meals' 
or parent = (select id from test where name = 'Meals');

The table defintion and the semicolon is missing after table1 and table2.

I just stumbled over this:

mysql> select "abc" = 0;
+-----------+
| "abc" = 0 |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.06 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+-----------------------------------------+
1 row in set (0.06 sec)

What's happening here? Where are the implicit type casts documented?

Your mysql server is not running. I don't know about fedora, but in Ubuntu you start it as root with
/etc/init.d/mysql start

MySQL does not have a URL field type which might handle the HTML formatting. You could write a MySQL function which replaces all URL-like text with HTML code. But it's easier done in PHP.

The idea looks correct.
Each class should have an ID and a name. The ID is the unique key. The class name should not be stored anywhere else than in the classes table, and refereoces to the class should contain the ID, not the name.

http://lmgtfy.com/?q=command+line+client+mysql

Do not use phpMyAdmin for that. It will lead you astray. Use the command line client to debug character set issues.

Check the database and connection setup with the command line mysql client and enter "show variables".
Check the table and field setup with "show create table <tablename>"
Check the browser content encoding by looking for the Content-type meta-tag in the source code or, with firefox, with the menu items "View | Encoding"

Probably you are using the wrong character set for the connection. Try it with command line mysql and have a look at the connection variables with

show variables like "%conn%"

Set the connection character set to utf8.

I deem it bad practice to do calculations in PHP which can be done on the database level. It is inefficient (because of the database in- and output) and it's error prone if the data are accessed through an other interface than PHP.

Have a look at the time_diff function:

drop table if exists t;
create table t (t1 time, t2 time);
insert into t values ('00:01:00', '00:02:00');
select t1, t2, t2 - t1, timediff(t2,t1), timediff(t1,t2) from t;
+----------+----------+---------+-----------------+-----------------+
| t1       | t2       | t2 - t1 | timediff(t2,t1) | timediff(t1,t2) |
+----------+----------+---------+-----------------+-----------------+
| 00:01:00 | 00:02:00 |     100 | 00:01:00        | -00:01:00       |
+----------+----------+---------+-----------------+-----------------+

I never found a way to do that. You can use a case statement which lists all the allowed table names and chooses the right one depending on a procedure input parameter, but I don't think that you can have truly dynamic table names in stored procedures or queries.

Obviously there is a character set error. The data seem to be in UTF-8, while the browser announces them as another character set (maybe latin-1?) Check all relevant character set parameters:
- database character set
- table character set
- field character set
- connection character set
- character set as used in the Content-type header.

You can search for text fields containing non-printing characters with a regular expression:

select mytextfield from mytable where mytextfield rlike "[[:cntrl:]]"

To search for non-ascii use the regexp "[^[:ascii:]]"
For other character classes see here: http://www.petefreitag.com/cheatsheets/regex/character-classes/

Show your table structure, show some test data, show what should work and what doesn't.

Do you have a PHP or a mysql error? What is the exact error message?

No, this code is not correct. While 0or seems to be a typo, the insert syntax is wrong. The id parameter is lacking a value.

You can do it in a query which uses 3 left joins to connect from the highest to the lower levels of the hierarchy. But this is not a general solution as the hierarchy can - in principle - be arbitrarily deeply nested. Therefore you will need a prodedure to build a general tree view model - either in an external procedural language like PHP or, preferably, in MySQL itself.

Your brackets are wrong.
Your sub-select returns the number 32, and the outer select cannot find any row with user_id = 1 and unique_id = 32.
Try:

select * from blocks order by unique_id desc where user_id=1 limit 1;

or

select * from blocks where user_id=1 and unique_id=(SELECT max(unique_id) from blocks where user_id=1);

http://www.databaseanswers.org/data_models/