mysql-query

MySQL::Eliminating redundant elements from a table?

I have a table like this: +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | v1 | int(11) | YES | MUL | NULL | | | v2 | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+-------+ Th...

Consecutive absences in MySQL

Hi, I have 2 tables in my db: students and absences. In students: id_student, s_name; and in absences: id_student, date, day(the day of the week as number-it's not so important); I would like to get the students that were absent for more than 2 days consecutive and the dates. At the moment I have the next query: /*To control when the...

Enforcing a query in MySql to use a specific index

Hi, I have large table. consisting of only 3 columns (id(INT),bookmarkID(INT),tagID(INT)).I have two BTREE indexes one for each bookmarkID and tagID columns.This table has about 21 Million records. I am trying to run this query: SELECT bookmarkID,COUNT(bookmarkID) AS count FROM bookmark_tag_map GROUP BY tagID,bookmarkID HAVING tagID IN ...

Effects of order of columns, while defining an index on them, on the performance of the queries

Hi, Does order of the columns in an Index definition for a table in a database has any effect on the performance? for e.g. are these two queries different ? CREATE INDEX xxx ON tablex(col1,col2) CREATE INDEX xxx ON tablex(col2,col1) what about the in case that I use a BTREE index? I am using Mysql. thanks ...

A SELECT statement for Mysql

I have this table: id,bookmarkID,tagID I want to fetch the top N bookmarkIDs for a given list of tags. Does anyone know a very fast solution for this? the table is quite large(12 million records) I am using MySql ...

help with this query..

$test2=mysql_query("SELECT min(substr(status,1) FROM railways"); while($test_array1=mysql_fetch_array($test2)){ echo "<pre>"; print_r($test_array1); echo "</pre>"; } what is the correct form of this query...need to get the min value from the table itself.. ...

Simple PHP query question: LIKE

When I replace $ordering = "apples, bananas, cranberries, grapes"; with $ordering = "apples, bananas, grapes"; I no longer want cranberries to be returned by my query, which I've written out like this: $query = "SELECT * from dbname where FruitName LIKE '$ordering'"; Of Course this doesn't work, because I used LIKE wrong. I've ...

Shell script that iterates through a sql "delete" statement

I need a shell script that deletes 1000 rows from a massive database until there is no more rows left It is very simple, but i am very weak in shell scripting, and the many tutorials online offer extremely similar, but different nuances in syntax UPDATE: Would it be possible to get some sample code? I need to delete 1000 rows at a t...

How can I write full search index query which will not consider any stopwords?

I have written a query which will perform Full Text search using full search Index in mysql Table. But my problem is that when user searches with "to go" then it will not search anything because of stopwords in mysql. So my question is, how can I write a Full Search query which will ignore the stopwords? ...

MySQL query : all records of one table plus count of another table

Hello Guys! I have 2 tables: User and Picture. The Picture table has the key of the user. So basically each user can have multiple pictures, and each picture belongs to one user. Now, I am trying to make the following query: I want to select all the user info plus the total number of pictures that he has (even if it's 0). How can I do th...

Strange data swapping error occurs when I attempt to update rows in my table from another table in my sql database...please help!

So I have a table of data that is 10,000 lines long. Several of the columns in the table simply describe information about one of the columns, meaning, that only one column has the content, and the rest of the columns describe the location of the content (its for a book). Right now, only 6,000 of the 10,000 rows' content column is filled...

Search number entries on a mysql database with COUNT

I have a mysql database which allocate: iid, name, description, url, namecategory, idcategory, nametopic, idtopic How can i know the number of entries that has categoryid=1 and topicid=1? I've tried $result = mysql_query("SELECT COUNT(id) FROM videos WHERE idcategory = 1 ...

3-clique counting in a graph

I am operating on a (not so) large graph having about 380K edges. I wrote a program to count the number of 3-cliques in the graph. A quick example: List of edges: A - B B - C C - A C - D List of cliques: A - B - C MySQL Table structure: +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default...

SQL for total count and count within that where condition is true

Hello, I have a single user table and I'm trying to come up with a query that returns the total count of all users grouped by date along with the total count of users grouped by date who are of a specific client. Here is what I have thus far, where there's the total count of users grouped by date, but can't seem to figure out how to get...

SQL string replace with ID statement

Is it possible to create a sql statement to do the following: Table 1 lists all of the drug names, Table 2 contains all of the side effects. Table 3 contains all the drug names by ID and all of the side effects separated by |. Is there some kind of SQL query I can run to re-create Table 3 where the side effects separated by | are the ...

How to do this query?

Hello everybody! I have a mysql table with these columns: ID (auto-increment) ID_BOOK (int) PRICE (double) DATA (date) I know two ID_BOOK values, example, 1 and 2. QUERY: I have to extract all the PRICE (of the ID_BOOK=1 and ID_BOOK=2) where DATA is the same! Table example: 1 1 10.00 2010-05-16 2 1 11.00 2010-05-15 3 1 ...

MySQL count elements in a database based on how many unique values there are in a field.

(Sorry for the title, I don't really know how to phrase that :-) ) I have a table that has a date and a uid fields. I need to get the number of uid for each date, currently I'm doing it in php running multiple queries like this one: SELECT COUNT(uid) FROM users where Date = 'xxx'; Is there a simple way to achieve this with only an s...

Database EAV model, record listing as per search

I am building a dynamic application. I have three tables : ( EAV model style) Items ( ItemId, ItemName) Fields (FieldId, FieldName) Field Values ( ItemID, FieldId, Value) Can you tell me how to write SINGLE query to get starting 20 records from ALL items where FieldId=4 is equal to TRUE. Expected Result : Columns => Ite...

Complex data ordering...

Hi, I have one tables ids in an array and they are ordered in the way I want and I have to select data from another table using those ids and in a order they are listen in the array. Pretty confusing but I was thinking of two solutions giving ORDER BY parameter the array but I do not know if that possible and another is to get all the ne...

Best way to check for NULL value in MySql query

Can any one please let me know the best way to use IF statement in mysql query to show if the "email" field is NULL then it should show as "no email"... Postcode Telephone Email ---------------------------------------------------------- BS20 0QN 1275373088 no email BS20 0QN 1275373088 no email PO9 4H...