mysql

CakePHP sub query SQL in HABTM relation

I want to suggest related products by tags and sort order by the most matched. the HABTM model association between Product and Tag class Product extends AppModel { //.. var $hasAndBelongsToMany = array("Tag"); //.. } and vice versa in Tag model. also join-table name is "products_tags". for Ex.sample.. //just sample of Product conta...

Import CSV file in mysql with email validation

I have a 2 column in my subscriber table. Name | email I want to import a CSV file data in respective column with validation of email column. I have done uploading CSV part, but i don't know how to insert data with email validation. How I can do this, I have search allover net but I can't find any working answer on it. Please help ...

Two conditions for one field in MySQL WHERE

Hi, Is there a way for me to have two conditions on one field in MySQL Where?... I have a field called type and I want to do this: WHERE `type` != 'name' AND `type` != 'photo' I'm hoping for a better way to do that, so I could compare type to two possible options? Thanks! ...

PHP search engine problem

Im using Sphider as a search engine for my website, its really easy to work with but im having some major issues with localized characters. All of my html/php pages have the charset defined as UTF-8 and the search and result page from Sphider had charset=ISO-8859-1, when I first used the Sphider "spider" to crawl my website it made all ...

mysql rollback on myisam engine

by mistake i run the update query , This update made my table all rows values as o , Is it possible to rollback the table values , Plz advise ...

Are multiple indices needed for multiple where clauses?

Say I have a query like the following: SELECT * FROM users WHERE username = 'test' AND somethingelse = 'test' I'm wondering if it's necessary to index both columns for optimization. Does MySQL first find all username columns with the value 'test', and then search those results for somethingelse columns with 'test'? Or does it happen s...

xampp - connecting to a local database from another local machine inside a LAN

Hi, I am using XAMPP to create php projects. My system is: name=con7 and local ip=192.168.1.20 Another system is: name=con12 and local ip=19.168.1.10 I want to connect to a database in con12 from con7. I read that we need to provide access permission for the host(ip) which is trying to connect from a different machine like the Access H...

On using letters as unique identifiers

Creating a table of codes/abbreviations, each with a unique combination of case Sensitive letters. Is it ok to skip using an integer id and use them for foreign key checkups? Or would there be issues I'm unaware of? ...

Scaling Write Intensive App (LAMP, MySQL) (Catalog Style Application)

Hi, I was wondering if there's anyone out here who has experience with write-intensive data due to file import. The main requirement is for the business users to be able to import a data that represents relationships between master tables. They should be able to export the same in real-time ( as much as possible). Setup: front-end (...

How to verify list of items in a test

I am writing a test case where I do send a list of Strings to be saved in the database. Then I will retrieve those from database and has to verify that everything is fine. I have written a assertNotNull(list) assertEquals(listSize, response.listSize()) However I want to verify the actual contents are also same. But my assertEquals ...

Question: how to use the current time in a where clause

Hello all, there was a question coming up to my mind. Is there any possibility to use the current timestamp instead of a selected date in the Where Clause? SELECT this, that FROM here WHERE start>='2010-07-01' I thought it would be sth. like: start='now()' or curdate() or curtime(). Anything I found was that they're used in the Selec...

What to use to save important generated data as-is? PDF/XML/DB or other?

My question is about generating invoices and receipts. The said bills use rates, names and values from a database. If the sources for generating the receipt stay unchanged, one can generate the same receipt dynamically each time. However, since names, rates and values may be changed or removed, the receipt also changes with time, i.e dya...

MySQL mystery: Null value is not different from non-null string

Why is this query returning 0 rows? select t.f1, t.f2 from (select null f1, 'a' f2 from dual) t where t.f1<>t.f2; This is a distilled version of a complex query I have. I want to compare two tables containing one-to-one related data and I want to select those rows that contain different values for certain fields. But also there can be...

self join for unique rows?

select * from hari; +------+------------+-------+ | id | mydate | email | +------+------------+-------+ | 1 | 2009-10-10 | 1111 | | 1 | 2009-10-10 | 1111 | | 1 | 2009-10-10 | 2222 | | 2 | 2010-11-11 | 3333 | | 2 | 2010-11-11 | 3333 | +------+------------+-------+ 5 rows in set (0.01 sec) After grouping by...

How can I search the string X times in the DB? (MYSQL + PHP)

Hey, first I use SQL + PHP the type of DB is MYSQL. I have a column with the many strings, but I want to search the string 08/08/10 if it exists 5 times for example in the column, how do I do it? ** If I will do: SELECT * FROM x WHERE y LIKE '%08/08/10%' Maybe it exists, but I don't know if 5 times.. Thank you very much! ...

SQL add tablename as field

I have seen this done before but i can't remember where i saw it. What i need to do is have the table name as a field in a query Say i have a table called users, how would i add the table name 'users' as a column like SELECT username, password, **table** as mytable FROM users ...

Mysql LOAD command

Mysql LOAD command lets import data from csv files LOAD DATA INFILE 'data.csv' INTO TABLE table_main FIELDS TERMINATED BY ','; What if there are multiple relation tables table_main id firstname lastname table_type id table_main_id table_type_id table_type id typename Is it possible to loa...

Why can't "SELECT DISTINCT 1 AS score ORDER BY score DESC;" work when used with creation of temporary table.

Hello... In MySQL, this portion of code works and produces a single row of '1' as value. SELECT DISTINCT 1 AS score ORDER BY score DESC; However, when I use it with creating temporary tables as in the one below: DROP TEMPORARY TABLE IF EXISTS `testTable`; CREATE TEMPORARY TABLE `testTable` SELECT DISTINCT 1 AS score ORDER BY score DE...

mysql get current date and row date

Hi, Is it possible to do sql query on what day is today and get the row of today for date column? So let say today is july 25th, i have database table sales, column name date, date = sales transaction date in timestamp. i need all row that the sales date is same with current date and also is it possible set value as gmt+5? ...

MySQL: OUTER JOIN and COUNT()

I have table 'posters', table 'reviews' and table 'trailers'. Every table is connected with an movieID column, but each table can be empty for certain movieIDs: ++ posters_table +++ ++ reviews_table ++ ++ trailers_table ++ --itemID--+--filename- --itemID--+--review-- --itemID--+--trailer ---------------------- -----...