mysql-query

Getting percentage of "Count(*)" to the number of all items in "GROUP BY"

Let's say I need to have the ratio of "number of items available from certain category" to "the the number of all items". Please consider a MySQL table like this: /* mysql> select * from Item; +----+------------+----------+ | ID | Department | Category | +----+------------+----------+ | 1 | Popular | Rock | | 2 | Classical | ...

mysql order by on varchar

Hi in my table , there is columns called time , Field type varchar , value like HH:MM , 02-25 ,21-42,07-15 Can u tell me , how to do the order by desc , Regards Bharanikumar ...

How to find the difference between 2 times in MySQL-like db in a single query

Hi, I have a table with the following schema: +------------------+ |Field | Type | +------------------+ | id | char(30) | | time | datetime | +------------------+ The id column is NOT a primary key, so therefore, the table contains entries such as: 10, 2010-05-05 20:01:01 10, 2010-05-05 20:01:05 13, 2010-05-05 20:04:01 10, ...

SQL - Link to more table information

I have a datagrid with 3 of 5 fields from a SQL table. The first databound was changed to an asp:hyperlink so the field can be clicked. What I want to do is, when the user clicks an object in the field, it sends the ID to another page. From there, it uses the request in order to display the required information which is found in the same...

Trying to build a dynamic PHP mysql_query string to update a row and getting back the updated row

I have a form that jQuery tracks the onChage .change() event so when something is changed it runs a ajax request and i pass in the column, id, and the values in the url. Here i have the PHP code that should update the data. My question is now how do i build the mySQl string dynamically. and how do i echo back the changes/updates that w...

How to upload timestamp data from a file into mysql

shell> cat /data/mysql/tm.sql 1276609796 1276606325 mysql> CREATE TABLE `tm_table` ( f TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected mysql> LOAD DATA INFILE '/data/mysql/tm.sql' INTO TABLE tm_table; Query OK, 2 rows affected Records: 2 Deleted: 0 Skipped: 0 Warnings: 2 mysql> SHOW WARNINGS; +---------+------+...

How do I select only rows that don't have a corresponding foreign key in another table in MySQL?

I have 3 tables: listing photo calendar "photo" and "calendar" both have a "listing_id" column in them and every "listing" has a "photo". But I only want to select rows that have no entry in the "calendar" table with the matching "listing_id". I'm not sure if I'm saying it correctly, but any help would be greatly appreciated. And if...

My SQL query for getting sorted value in Date time

Hi All, I having values in such format "21/06/2010 10:50:27 am" in my DB table and i want to write the SQL query for getting Values in Sorted order I tried " $query = "SELECT status,identity, time_of_process, FROM config ORDER BY time_of_process"; But this doesn't help!!!!!!!! Regards New Dev ...

Do I need to create Index seperatly for MySQL??

I am facing some problem with MySQL database. actually It was fast when I did testing on My localhost..but when I uploaded to public server it is slow. data s are displaying but taking more time than usual. there are 7 table which opens to read for a single page. so what I did is for first table I added username and password. for last ta...

Structuring SQL querie based on multiple checkboxes state.

Hi, I'm making a restaurant search in which users can filter the search based on many criteria... I have three tables: Table1 - **Restaurant** ------+----------+---------- id + name + place ------+----------+---------- 1 Rest1 Ny 2 Rest2 La 3 Rest3 Ph Table2 - **r_type** ------+-----...

While loop into 2 different divs?

Hey all, I've got an about page, and two HTML columns and I want to while loop the contents of my mysql query into BOTH columns, even though they are defined as two different divs. What I was thinking of doing is somehow dividing the amount of rows in the mysql database by 2, and then showing half and half, but I'm not sure how to do thi...

which is the right Query for MySQL

I know which column should be called in a table ..for example table name is "table1"; column name is "name"; search is "$query"; Now I query MySQL like this mysql_query("SELECT * FROM table1 WHERE name='$query'"); Is this the right way to call?? I feel it took more time. any suggestions?? ...

Conditionally set column values in SQL result

I'm creating a result set based on a join of multiple tables. For the sake of simplicity, I have attached an image showing my theoretical layout: My result set normally looks like: table1.value, table2.value, table3.value However, if table3.table4_key is set, I would want the result set to instead be: table1.value, table2.value, t...

Using SELECT DISTINCT in MYSQL

Been doing a lot of searching and haven't really found an answer to my MYSQL issue. SELECT DISTINCT name, type, state, country FROM table Results in 1,795 records SELECT DISTINCT name FROM table Results in 1,504 records For each duplicate "name"... "type", "state", "country" aren't matching in each record. Trying to figure out ...

Specifying index in HQL queries

Is there a way to specify which index to use, in HQL, to retrieve values from a MySQL table that has an index defined? ...

Using the value of a field as column name in a query

I'm trying to figure out a way to only grab the specific field that pertains to an operation step a unit is in. I have one table with units and their operation step, then I have another table that has a column for each step. I want to pull only the column that relates to that unit's current step. Here is my attempt but I can't seem to...

Weird result at selecting rows

I've been worndering why this query returns this result: SELECT direccion_principal FROM tb_dysport_contacto_medico_terapeutica WHERE direccion_principal LIKE '%Ú%' Result: +---------------------+ | direccion_principal | +---------------------+ | COLSANITAS | +---------------------+ The table collation is utf8_general_ci....

Mysql optimization for select query with IN() clause inside where clause (explain output given)

I have this query:- SELECT SUM(DISTINCT( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398, 2436, 2940, 1973, 2791, 1389 ) )) AS key_1_total_matches, IF(( od.id_od > 0 ), COUNT(DISTINCT( od.id_od )), 0) AS tutor_popularity, td.*, u.* FROM tutor_deta...

How to select a column value that corresponds to a row returned by a MySQL aggregate function?

I have a table like date user_id page_id 2010-06-19 16:00:00 1 4 2010-06-19 16:00:00 3 4 2010-06-20 07:10:00 1 1 2010-06-20 12:00:10 1 2 2010-06-20 12:00:10 1 3 2010-06-20 13:05:00 2 ...

Mysql – Detecting changes in data with a hash function over a part of table

Hi I need generate a single hash over some data in a table CREATE TABLE Table1 ( F1 INT UNSIGNED NOT NULL AUTO_INCREMENT, F2 INT default NULL, F3 Varchar(50) default NULL, .. FN INT default NULL, PRIMAR...