mezise
-
Posts
72 -
Joined
-
Last visited
Never
Posts posted by mezise
-
-
'your' word is in default MySQL FULLTEXT stopword list. Check information about FULLTEXT configuration variables: ft_stopword_file, ft_min_word_len, etc. There are more restrictions for using FULLTEXT, please check relevant manual entries.
-
I know one way but it's a little tricky:
SELECT c.description , t.category_id , t.post_id , t2.i FROM tie AS t INNER JOIN ( SELECT t.id_tie , IF(t.category_id != @i_cat, @i := 1, @i := @i + 1) AS i , @i_cat := t.category_id c FROM tie t , (SELECT @i := 0, @i_cat := 0) AS INIT_VARIABLES ORDER BY t.category_id, t.post_id DESC ) AS t2 ON t2.id_tie = t.id_tie INNER JOIN categorys c ON c.id_category = t.category_id HAVING t2.i <= 10 ;
Your LIMIT number is moved into the HAVING clause.
-
It might be a typo or whitespace character at the beginning or at the end of Pword value.
My advice is that you should not use unencrypted way of storing passwords.
-
If you want to have the output you first posted (with all clashing sessions not grouped by member):
firstName lastName title roomName startTime ------------------------------------------ -------------- Dave Mann Flute Foundation 09:00:00 Dave Mann Piano Hall 09:00:00 Bob Dillan Guitar 2 09:00:00 Bob Dillan Adv. Guitar 4 09:00:00
you need to INNER JOIN second time with session table but only with records you want to display and next GROUP by memberID and sessionID.
-
In my opinion the best way when it comes to checking allowed values of database fields is to use DESCRIBE information before saving data. That lets you do not worry about correctness of data ranges even if your interface validation fails. Additionally when you will need to change values ranges you just alter database structure and your application will follow these changes.
-
Please remember: choosing CVS columns separator character you must be sure that the character is not present within content of any column.
-
So in your movies table firmID is auto_increment field and title is foreign key of titles table.
Remove commas from the end of lines (which represents last column) and at the begining of every line
add one comma (which represent first column that will be treated as NULL and will be auto incremented by
database engine).
-
You should start with this example.
For this data to display products of every size with prices based on ensemble flag set or not set you may use query with LEFT JOINS:
SELECT p.product_id , p.size , p1.price price1 , p2.price price2 FROM products p LEFT JOIN products p1 ON p1.product_id = p.product_id AND p1.size = p.size AND p1.ensemble = 0 LEFT JOIN products p2 ON p2.product_id = p.product_id AND p2.size = p.size AND p2.ensemble = 1 GROUP BY p.size ORDER BY p.size ;
or query with SUBQUERIES:
SELECT product_id , size ,(SELECT p2.price FROM products p2 WHERE p2.product_id = products.product_id AND p2.size = products.size AND p2.ensemble = 0 LIMIT 1 ) AS price1 ,(SELECT p2.price FROM products p2 WHERE p2.product_id = products.product_id AND p2.size = products.size AND p2.ensemble = 1 LIMIT 1 ) AS price2 FROM products GROUP BY size ORDER BY size ;
-
General rule designing a database structure is: Anticipate probable changes in the data that may forces you to change the database structure.
If you will use username instead of user_id as a foreign key and if a user will want to change his username you will be forced to update every record where his username was used.
-
Please provide us with several first lines of imported CVS file.
-
I have some experience with big load applications where performance is a crucial matter and I assure you that doing ideal normalization at any price very often is shooting yourself in the foot. Practical solutions and professor's theories do not always are the same things. Don't you agree with this?
-
If I understand correctely your task, I would do this like that:
---- USERS ------------------------------------------------------ user_id | email | password | username | notify | ----------------------------------------------------------------- ---- GROUPS ----------------------------------------------------- group_id | groupname | ----------------------------------------------------------------- ---- GROUPS_USERS ----------------------------------------------- group_id | user_id | ----------------------------------------------------------------- GROUP_USERS table assigns many groups to many users. ---- BETS ------------ --------------------------------------------------------------------- bet_id | description | date | amount | between_user_id | winner_user_id | creator_user_id | --------------------------------------------------------------------------------------------
There may be many users.
There may be many groups.
Every user may be assigned to many groups.
There may be many bets.
Every bet may have one between_user, one winner_user and one creator_user.
-
SET is quite evil... not very flexible, not easy to query / edit, etc.
I do not agree. As TINYINT is for small numbers, SET is for small lists.
SET is very efficient during execution and if you code appropriate API, querying and editing works like a charm.
-
If we go into this subject SHA1() is even more secure replacement.
-
What a mess.... store integers as INT.
He does. The problem was with not cleaning the data sent from web interface (separation signs).
-
Could you format those queries, because it is very hard to read it?
-
should product_id be a foreign key with PRODUCTS.id?
Yes, correct, as the name suggests (I thought it was obvious).
and what would happen if there is more than one item in the order?
ORDERS_PRODUCTS will be filled with first record with appropriate order_id and product_id and every next record with the same order_id and succeeding product_id.
-
What is the wrong result of this query and what result it should be? Provide us some tabular data and post generated SQL query.
-
Add table PRODUCTS(id, name)
and table ORDERS_PRODUCTS(order_id, product_id).
-
AUTO_INCREMENT field cannot have default value defined, because it is handled by MySQL engine.
-
Please be precise what kind of data you have, e.g. this:
product_id size price ensemble ---------- ------ ------ -------- 1 Single 10 0 1 Double 0 20 2 Double 20 0 2 Queen 0 30
or e.g. this:
product_id size price ensemble ---------- ------ ------ -------- 1 Single 10 20 1 Double 15 25 2 Double 20 30 2 Queen 25 35
Or maybe something else?
-
So try to execute:
REPAIR TABLE YourDatabaseName.tblproductqty EXTENDED;
-
Server also has Windows Defender installed as well as AVG - was just wondering if an update to either has blocked MySQL?
Just disable these applications and check.
-
For a query for 100 or 1000 sites...
Here I agree with your point. But it was the best I could figure out without using PROCEDURES.
Also I agree that using PHP you can just use simple selection of consumption data and then handle the data to appropriate display them.
... and dates for a whole year or for example the last 12 months or for the data from two years ago or the past three years, are you going to rewrite the query each time?
Here you are wrong. The last ONE query lets you analyze given sites for every month that exists in the consumption table. Additionally repeating site select part:
, (SELECT t2.`kWh` FROM consumption AS t2 WHERE t2.`From` = consumption.`From` AND t2.`Site_id` = 1 LIMIT 1) AS 'kWh of site 1'
needs just replacing Site_id number.
That is not comprehensive.
For my current knowledge of MySQL it is. I do not want to be get overwise. If someone can provide more comprehensive SQL query on the subject I will be glad to learn something new and useful.
Greatings for all who like to play with SQL!
Search through tags
in MySQL Help
Posted
Use this: