mysql-query

Combining two conditional logic results into one case statement

I have written a query to Select sales values from a database and apply discounts if there were any used. The discounts are either a percentage or just a flat-value. My Query applies the discounts (first case statement) and then pulls the amount that was applied. It got me thinking if it was possible to get this done in one case state...

Disappear the arrays generated with mysql_fetch_array() after use?

I have a typical database query: $query = mysql_query('SELECT titulo,referencia FROM cursos WHERE tipo=1 AND estado=1'); and I can convert it in an array and print the data: while ($results=mysql_fetch_array($query)): ?> echo $results['referencia']; // and so... endwhile; but in some cases I need to print the same data in another p...

MySql Error 1064

Sql: create table autori( id_autor integer primary key auto_increment, nume varchar(50) not null, prenume varchar(50) not null )Engine=InnoDB; create table domenii( id_domeniu integer primary key auto_increment, nume_domeniu varchar(50) not null, descriere varchar(1000) not null )Engine=InnoDB; Php: $sqlpath = "fil...

How can get unique values from data table using dql?

I am having a table in which there is a column in which various values are stored.i want to retrieve unique values from that table using dql. Doctrine_Query::create() ->select('rec.school') ->from('Records rec') ->where("rec.city='$city' ") ...

Which SQL is Better? on MySQL 5.1

In MySQL INSERT REPLACE INTO INSERT INTO .. ON DUPLICATE KEY UPDATE Which SQL is Better? Could you give me a advice? ...

MYSQL - select first 4 records for each category in a table

I have a table INVENTORY that has among other columns CATEGORY and UPDATED. Sorting the table by these columns is easy: SELECT * FROM INVENTORY ORDER BY CATEGORY ASC, UPDATED ASC What I want is to get a result set that contains only the first 4 rows from each category. Any idea how to get it done? ...

How do write a MySQL query that will limit the results of a joined table or tables and also count the number of items in the joined table or tables?

How would you write a MySQL query that will limit the results of a joined table (or sub select if that works better) and also counts the number of items in the joined table or tables? For instance, let's say you had three tables: projects, tasks and comments, where a project has 0 or more tasks and a task has 0 or more comments. How wo...

How to optimize a nested query?

Can I somehow join tables and avoid the usage of distinct in the following MySQL query. invited_by_id shows the user id of who invited this user. SELECT user1.id, count(distinct user2.id) AS theCount, count(distinct user3.id) AS theCount2 FROM users AS user1 LEFT OUTER JOIN users AS user2 ON user2.invited_by_id=user1.id LEFT...

does 'KEY' in SHOW CREATE TABLE mean a column is indexed?

I am dealing with a db I did not create. I will be issuing a number of long-running queries involving three integer columns of a large table: id (a primary key), x and y. (I won't be writing/updating records; just issuing queries.) SHOW CREATE TABLE shows that: `primary_key` int(11) NOT NULL auto_increment, `x` int(11) default NULL, `y...

what mysql query should i use to select a category that matches ALL my criteria?

Hi, i have the following data in my table called cat_product cat_id product_id 1 2 1 3 2 2 2 4 If given a set of values for product_id (2,3) i want to know the unique cat_id. In this case, that will be cat_id 1. how should i construct mysql query? i tried to use select distinct cat_id from cat...

How can I write this MYSQL query without using UNION?

I'd like to do a single query on one table without using UNION Here are the two queries that I need to combine. SELECT field1, field2, field3 FROM table1 WHERE field4 != 'condition1' AND feild3 >= 'condition2' ORDER BY field3 ASC LIMIT 20; SELECT field1, field2, field3 FROM table1 WHERE field4 != 'condition1' AND feild3 < 'condition2' ...

mysql timediff to hours

Hi, I'm Trying to get the timediff from my table and convert it to hours (it's for an hourly billed service) SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600 FROM tasks > where endDate and startDate are in datetime format is there another way (more efficient) to do this task? Thanks ! ...

How to list all table in mysql database

Hi I want to list all tables in my mysql database. I want that each table list with column name and datatype. So how could i do this, any query or something like that. Running on php Thanks Avinash ...

Need advice on how to do this in a single MySQL query

Is this possible to do this in one MySQL query? Basically I need to sort users by how many responses they have. I have 2 tables table "users": id username ------------------ 1 hunter 2 loserville and another table "responses": id user_id response ------------------------------- 1 1 yes 1 ...

MySQL - Using If Then Else in MySQL UPDATE or SELECT Queries

How do I update a table and set different values upon the condition evaluating to True. For instance : UPDATE Table SET A = '1' IF A > 0 AND A < 1 SET A = '2' IF A > 1 AND A < 2 WHERE A IS NOT NULL; I have seen CASE expression and IF expression in Procedures and Functions but I want to use it in a simple update/select statement. Is i...

mysql query help - complicated join/order by scenario

Hi, I need to write a query and I'm not even sure where to begin on this one. I have a set of tables that I did not create and cannot change. member table +----+-------+ | id | class | +----+-------+ | 1 | 1 | | 2 | 2 | +----+-------+ member_data table +----+------------+-----------+ | id | first_name | last_name | +----+----...

album tree deletion

i have following table album_id | sub_album_id | name sdf2342 | 0 | family a2243d | sdf2342 | wife 23ods | sdf2342 | jack 23jskd | 0 | places i want to delete selected album with its all the sub_album and if there is more sub_album then de...

MySQL query involving cross join, uniqueness, etc

Thankfully, I haven't had to work with particularly complex SQL queries before. Here's my goal. I have the table hams, which I would like to cross-join with the table eggs - that is, get all ham-egg combinations... to an extent. The eggs table also has an attribute how_cooked, which is defined as ENUM('over-easy','scrambled','poached')...

WHERE clause causing table to omit LEFT JOIN rule

Hi, I am essentially attempting to modify this stored procedure. Modified stored procedure: CREATE PROCEDURE sp1(d1 date, d2 date, client INT(10)) declare d datetime; create TEMPORARY TABLE foo (d date NOT NULL, Amount INT(10) DEFAULT 0); set d = d1; while d <= d2 do insert into foo (d) values (d); ...

How do I structure a SELECT query for the following...

Hoping that someone here will be able to provide some mysql advice... I am working on a categorical searchtag system. I have tables like the following: EXERCISES exerciseID exerciseTitle SEARCHTAGS searchtagID parentID ( -> searchtagID) searchtag EXERCISESEARCHTAGS exerciseID (Foreign key -> EXERCISES) se...