help with ORDER BY
Hi all!
I need some help with ORDER BY in the following example. I want to order by
selected category, then by subcategories of the selected category, then by
categories with the same parent_id of the selected category, then by random
if possible, or random within the categories if possible, but the first
order by part is not working because is not returning products of the
selected category first, instead returns products from a top category
(parent_id = 0).
table categories
id | parent_id | category
- where parent_id is 0 for top categories.
table products
id | id_category | product
SELECT
products.id,
(SELECT
CASE WHEN CHAR_LENGTH(products.product) > 40 THEN
CONCAT(SUBSTRING(products.product,1,37),'...')
ELSE products.product END) AS product,
(SELECT
CASE WHEN CHAR_LENGTH(products.description) > 70 THEN
CONCAT(SUBSTRING(products.description,1,67),'...')
ELSE products.description END) AS description
FROM
products
WHERE
products.id_stock = 1
ORDER BY
products.id_category IN (".$id_selected_category."
, (SELECT categories.id
FROM categories
WHERE categories.parent_id = ".$id_selected_category.")
, (SELECT categories.id
FROM categories
WHERE categories.parent_id = ".$parent_id."))
, RAND() LIMIT 11
(the php vars have correct values)
Please apologise my bad English.
Thanks in advance.
Pedro.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from unknown format (.bdd .ind .mor .ped)
Greetings. I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. Does anyone knows the db format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from unknown format (.bdd .ind .mor .ped)
Greetings. I'm building an application and I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. I was wondering if anyone knows the format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro.
Query results for text with á é ö ã etc
Greetings. I have Text fields in some tables which contain text in Portuguese with accent characters like á, à, é, í, ú, ü, ó, ö, ç, etc. When I query like: SELECT text_column FROM table WHERE text_column LIKE %maçã% [ or ] WHERE text_column LIKE %ç%ã% it also returns 'maca', 'maça', etc, which are different words. How can I restrict results to accent characters/words? Must I use the '=' operator instead of 'LIKE'? In that case I suppose I do not get results for similar words. Adding a FULLTEXT to those text fields would solve it? The charset on all the tables is latin1, using Mysql 5. Thanks in advance. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp error
Greetings! I am building a website using MySQL 5.0.18 and PHP 5.1.2. When I try to insert in a table a timestamp value from php's mktime() I get the following error: 1292: Incorrect datetime value: '1139776424' for column 'access_date' at row 1 The sql for the table is: CREATE TABLE `members_acs` ( `id` int(17) unsigned NOT NULL auto_increment, `member_id ` int(13) unsigned default NULL, `access_date` timestamp NOT NULL default '-00-00 00:00:00', `ip` varchar(15) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Are timestamps different from php to mysql? When I first create a timestamp field it defaults to CURRENT_TIMESTAMP then the following will default to -00-00 00:00:00 . Is this the normal behaviour? What am I doing wrong? Thanks in advance. Pedr. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help on Unknown field error
> -Mensagem original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > "pedro mpa" <[EMAIL PROTECTED]> wrote on 01/31/2006 12:49:48 AM: > > > Greetings. > > > > I need help on the following query. > > I get an error like "Unknown/Invalid column total_price [...]" when I > try > > filter by total_price. > > How can I do this correctly? > > > > SELECT receipts.*, > >(SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE > >receipt_itens.id_receipt=receipts.id) AS total_price > >FROM receipts > >WHERE > > total_price >= ".$minprice." " > > AND total_price <= ".$maxprice." " > >ORDER BY receipts.date DESC > > > > > > Thanks in advance. > > > > Pedro. > > > > You don't need a subquery to get these results. You can also use a regular > low-tech JOIN: > > CREATE TEMPORARY TABLE tmpDateTotals SELECT > receipts.date > , sum(receipt_itens.price) total_price > FROM receipts > LEFT JOIN receipt_itens > ON receipts.id = receipt_itens.id_receipt > HAVING total_price >= ".$minprice." > AND total_price <= ".$maxprice." > GROUP BY receipts.date; > > SELECT receipts.*, dt.total_price > FROM receipts > INNER JOIN tmpDateTotals dt > ON dt.date = receipts.date > ORDER BY receipts.date desc; > > DROP TEMPORARY TABLE tmpDateTotals; > > The problem with your original query was that you were trying to compare > the results of a calculation in your WHERE clause. Results do not exist > when WHERE clauses are evaluated but they do by the time the HAVING > clauses are checked. The column`total_price` only exists _after_ the SUM() > function is computed which happens _after_ the restrictions of any ON and > WHERE clauses are applied to your source data. The HAVING clause is > specifically designed to handle this type of comparison. I will bet > dollars to doughnuts that your subquery version of this query will execute > 5 to 10 times slower than my version without the subquery. Can you please > try both and report your results? Thank you for your good explanation. The query I wrote is an example of a more complex query I am building in which I use a lot of subquerys in detriment of JOIN clause. When I finish rebuilding the query I will compare performance and post it. I didn't know that subquerys are much slower than JOINS. Thank you. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help on Unknown field error
Greetings. I need help on the following query. I get an error like "Unknown/Invalid column total_price [...]" when I try filter by total_price. How can I do this correctly? SELECT receipts.*, (SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE receipt_itens.id_receipt=receipts.id) AS total_price FROM receipts WHERE total_price >= ".$minprice." " AND total_price <= ".$maxprice." " ORDER BY receipts.date DESC Thanks in advance. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help on query joining a 3rd table
Solved. Thanks. -Mensagem original- De: pedro mpa [mailto:[EMAIL PROTECTED] Enviada: domingo, 29 de Janeiro de 2006 18:25 Para: mysql@lists.mysql.com Assunto: Help on query joining a 3rd table Hello! I am building a query to get a monthly total for receipts and receipts plus TAX. My problem is the TAX, it can be different for each receipt. I need help on including and relating each TAX value/id with each receipt, like receipt_items.price * 1.21 etc. The tables are like the following example: Table receipts: id | id_tax | date 112005-12-31 212006-01-01 322006-01-25 ... Table receipt_items: id | id_receipt | price (exc tax) 111000 221000 ... Table tax: id | tax (%) 119 221 So far I have: SELECT SUM(receipt_items.price) AS total , (SUM(receipt_items.price) * [-help here-]) AS total_plus_tax FROM receipt_items, receipts WHERE receipt_items.id_receipt = receipts.id AND MONTH(receipts.date)=".$month." AND YEAR(receipts.date)=".$year." Thanks in advance. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help on query joining a 3rd table
Hello! I am building a query to get a monthly total for receipts and receipts plus TAX. My problem is the TAX, it can be different for each receipt. I need help on including and relating each TAX value/id with each receipt, like receipt_items.price * 1.21 etc. The tables are like the following example: Table receipts: id | id_tax | date 112005-12-31 212006-01-01 322006-01-25 ... Table receipt_items: id | id_receipt | price (exc tax) 111000 221000 ... Table tax: id | tax (%) 119 221 So far I have: SELECT SUM(receipt_items.price) AS total , (SUM(receipt_items.price) * [-help here-]) AS total_plus_tax FROM receipt_items, receipts WHERE receipt_items.id_receipt = receipts.id AND MONTH(receipts.date)=".$month." AND YEAR(receipts.date)=".$year." Thanks in advance. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help on nested categories query
Hi! I need help on implementing a query that performs a search for items under nested categories. An example is on ebay search where you can restrict your search by selecting a subcategory and you only get items under that category/subcategories. Do I have to first get a list of all subcategories under the selected category and then loop OR's or is there a better way? Some example tables: | id | pid | category | 1 0cat1 2 0cat2 3 1cat3 4 1cat4 5 2cat5 | id | id_category | item | 13 item1 24 item2 35 item3 (Running Mysql 4.1.9 and PHP 5.0.4). Thanks in advance. Apologies for my bad English. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
