mysql-query

How to have more than one condition for data recall

I have a table in my MySQL database that looks like the following banner -------- id : bigint(20) name : varchar(75) type : set('news','event') link : bigint(20) when : int(10) What I want to do is I want to be able to recall all the rows with the type of "news" but I only want to recall all of the rows with the type of "event" wher...

mysql query help

My query is: SELECT business.name, AVG(rating.value) as total FROM business INNER JOIN rating ON business.id = rating.business_id INNER JOIN directory ON business.id = directory.item_id WHERE directory.directory_id = 13 AND directory.type = 'business' AND rating.rating_id = '1' GROUP BY business.id ...

Single query for all records having no key in a join table and those having a matching key

I have a trip that has many residencies. I need a single query that returns all trips where no residency information has been specified. And all trips that match a specified residency. I can get the first from this query: SELECT * FROM `trips` WHERE (((NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)) Bu...

PHP - Too many mysql_query("SELECT .. ") .. ?

Hey, I'm making an e-shop and to display the tree of categories and all the products with their multiple variations of prices I made like more than 150 mysql_query("SELECT ..."); queries on one page. (If I count the "while" loops). Is it too many, and if yes, can it have any negative effect? (ofc. it takes longer to load the data ..) A...

MySQL "NOT IN" query

I wanted to run a simple query to throw up all the rows of Table1 where a principal column value is not present in a column in another table (Table2). I tried using: SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal This is instead throwing a syntax error. Google search led me to forums where people were saying that...

Join Multiple tables in my-sql

Hello All, Parent Table ID EMP_ID EMP_NAME 1 emp01 Sam 2 emp02 Jam 3 emp03 Mam Child Table 1 ID EMP_ID EMP_ADDRESS 1 1 A Street 2 1 B Street 3 2 Z Street 4 3 L Street 5 3 M Street 6 3 N Street Child Table 2 ID EMP_ID EMP_PHONE 1 1 123456789 2 1 456789123 3 3 456987321 4 3 465987321 5 3 321651213 If...

Search engine crawlers and SQL

Are MySQL queries executed/indexed by search engine bots? ...

Ordering by fields in addition to grouping in MYSQL- Can I use conditional statements in sql?

I have a database with a single table right now, which contains the following fields: name, Suite This is my first hacked together implementation of a database to track tests, and I realize that the way I am storing them is not efficient, but as I said, this is my first round of doing this. Some of the names belong to suites, and so...

select inbetween elements in mysql?

I am trying to implement the pagination in php. I am using the Mysql as back end database. I am trying to implement the pagination logic. I would be having lots of record. But the user will see only 10 at a time. Now to show the first page, i do a SELECT * from USERS LIMIT 10. Now to get the next 10 and the subsequ...

How to save URL's to a MYSQL database from an array

What is the best way to save a URL or symbols into a MYSQL database. I'v seen this being used "{$htmlOutputArray[1]}" and then else where this "$htmlOutputArray[1]" and some other places do what i'v done below... but which is the best? So far I have: (example code) $html = "034251\nhttp://stackoverflow.com/questions/ask" $htmlOutputAr...

mysql insert nested select from other db truncates double values

I have a table in one database, call this db x. I have another database, call it y. I want to copy data from x.some_table to y.some_table. I don't want to do an exact copy of the table, because some columns don't make sense in database b. I use the following query: INSERT INTO y.some_table (a_field) SELECT a_field FROM x.some_table; a...

Getting multiple records on year wise.

Hi All, I have a Patient information table with ~50 million records. I need to check some samples for each year which may be in any order. Here are the sample date available in database "20090722", "20080817", ... "19980301". Also i have a primary-key column called "PID". My requirement is to get 2 or 3 samples for each year with a...

Zend Framework Cardinality violation: 1241 Operand should contain 1 column(s)

Hi guys, I have a sql problem and i don't know how to fix it, I have tried a few things but..you know.So here is my query: /** * Returns a list with all the months for the archive * * @return array */ public function Archive() { $q = "SELECT DISTINCT MONTH(`data`) AS `month`,YEAR(`data`) AS `year` FROM `posts` ORDER BY `data` D...

String Check operation in MySQL and Mapping it to a different Value

Hi Friends, I have a MySQL table in which i have a time field in INT(10) format. I am storing UnixTime in it. Some of the records have a Zero value and some have a date in it. I want to build a query which shows "No Date" if the value is 0 else show the stored date if there is any date stored in the record on query execution. The table...

Best way to store time interval values in MySQL?

Stupid easy problem, but I haven't been able to find an elegant solution. I want to store time intervals in a MySQL columns, for instance: 1:40 (for one hour, 40 minutes) 0:30 (30 minutes). Then be able to run queries, summing them. Right now I store them as INT values (1.40), but I have to manually do the addition (unless I'm missing ...

How can I optimize this query?

I've got a bit of a nasty query with several subselects that are really slowing it down. I'm already caching the query, but the results of it changes often and the query results are meant to be shown on a high traffic page. SELECT user_id, user_id AS uid, (SELECT correct_words FROM score ...

Zend Framework insert multiple rows, error with some empty data

I'm building a query and using "execute" for a multiple insert like : $sql='INSERT INTO tablename (col1, col2, col3) VALUES (?, ?, ?), (?, ?, ?)' $stmt = $contactsTable->getAdapter()->prepare($sql); $stmt->execute(); It all works fine if all the data is not empty, but if some values are empty, I get an error like this: Message: SQLST...

Problem in Creating Table

Hello All, create table mainTable as select curr_Table.empID as empID, (currTable.ToTalDays-oldTable.ToTalDays) as DiffDays from currTable left outer join oldTable on currTable.empID = oldTable.empID This is the query that i use to find the days worked by an employee. The Problem raises when there is "New Joinee". "oldTable.ToTalDa...

MYSQL IN or multiple conditionals

I have a simple query which is returning records based on the field status not having certain values. Lets say for arguments sake that the field can have values 1,2,3...10 and I want to return all records that don't have values 3, 7 and 9. Which of the following would be best to use? Option 1. SELECT `id` FROM `tbl` WHERE (`status` != ...

MySql skips first record found

Hello , in my database i have 10 records with almost exact same data , they differ only by one field ( the field is not in the query) and when i run the following query SELECT * FROM friends WHERE user_id= 'MyUserName' AND follow_back = 0 AND until_date= '2009-10-13' LIMIT 12 it shows only 9 records , any one stumbled upon similar p...