mysql-query

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

I am trying to insert html pages to MySQL with my Asp.NET project but i am getting error; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'de Osman patlaması', '', '<div style=\"text-align: center\">\r\n<img src=\"/i' at line 1 How can i fix that p...

Creating new items based on sort values

I have a situation where I'm building a new list based on sort values on another column. For example table: products id, name, sort_order, expiration_date table: lists id, product_id Let's say I want to always have 15 products in the lists table and in order. However, products expire, and when this happens they are being removed from...

Mysql select list of items (repeat until certain amount)

I have the following structure: Table: products id, name, sort_order Let's say I only have 5 products, but I want 20. How would I loop through to get 20 results? Additionally, I'll need to start at a specific sort_order. So let's say I have 1,2,3,4,5 and I want to get 20 results and start at 3. It should end up as: 3,4,5,1,2,...

Weighted average calculation in MySQL?

I am currently using the following query to get some numbers: SELECT gid, count(gid), (SELECT cou FROM size WHERE gid = infor.gid) FROM infor WHERE id==4325 GROUP BY gid; The output I am getting at my current stage is the following: +----------+-----------------+---------------------------------------------------------------...

mysql retrieve partial column

I am using TinyMCE to allow users to write in what will eventually be submitted to a database. TinyMCE includes a button which will insert a "pagebreak" (actually just an HTML comment <!--pagebreak-->). How can I later pull back everything up to the first occurrence of the pagebreak "tag"? There might be a significant bit of data after...

MySQL: Set user variable from result of query

Is it possible to set an user variable based on the result of a query in MySQL? What I want to acheive is something like this (we can assume that both USER and GROUP are unique): set @user = 123456; set @group = select GROUP from USER where User = @user; select * from USER where GROUP = @group; Please note that I know its possible ...

how to modify complex sql query w/ join into rails 3

I'm working on a car pooling application where users can add lifts and are able to select multiple stops for each lift ( A to B via c, d, e). Now when a user searches the database for a lift the results should also include lifts like 'A to d', 'c to B' or 'c to e' and so on. I got this working with Rails 2.3.5 using the below code but st...

speeding up outer join query for newsletter script

Hi, I've got a PHP script that sends newsletters to a bunch of email adresses, it sends 15000 newsletters an hour, until all email adresses have been processed. Because this process isn't run in one time, I have a table where I register all the adresses which have been mailed, so I can always easily get the unmailed adresses. Here is...

is it possible to automatically delete a row in a table if a certain condition is met?

is it possible to automatically delete a row in a table if a certain condition is met? for example a products table pid pname quantity 1 shoes 5 now the condition must be if quantity is equal to 0 then the whole row must be deleted ...

MySQL query, many-to-many relationship - Tagcloud and pagination concepts

Does someone have a good idea / way on how to work with the following situation? : Situation is: I have the tables 'releases' and 'ntags', related via 'releases_ntags' (containing 'release_id' and 'ntag_id') Starting with a number of ntag id's I have a query that returns only releases that have all corresponding tags assigned. Thanks t...

Is it possible to achieve this selection with a single query?

This one has been haunting me for quite a while now.. I have been developing my own CMS using a MySQL database; each uploaded image is assigned to a category, according to which part of the site it is related to (I need to do this since each category has its own way to handle images). I have several tables for the various entities, an '...

MySQL spatial index doesn't work when equating MBRContains() to TRUE?

I have a SQL query that seems to be producing correct results but according to EXPLAIN isn't using the spatial index and so is taking much longer than necessary to return all the rows. SELECT * FROM listings2 WHERE MBRContains( GeomFromText('POLYGON((32.653132834095 -117.40548330929, 32.653132834095 -117.06151669071, 32.942267165905 ...

hw to traverse tree in mysql??

node (id , name ,parentid); tree (id, parent id) this is my table structure i want 2 store in this procedure i want to insert nodes id with its all parents eg this is node table contents +----+------+----------+ | id | pid | nodename | +----+------+----------+ | 1 | NULL | 1 | | 2 | 1 | 2 | | 3 | 1 | ...

PHP/MySQL: Updating nonexistent column value

So, this query: mysql_query("UPDATE item SET name = 'foo' WHERE name = 'bar'"); is returning 1, but the value 'bar' doesn't exists in the table. As expected, nothing has changed in the database itself, but shouldn't mysql_query() return 0 in that case? ...

Need help on MySQL DISTINCT+COUNT query

I have a table called "r", inside that table I store id, ip, and country. I am running this query: SELECT r.country, count(rr.ip), count(DISTINCT rr.ip) FROM `r` LEFT JOIN r rr ON r.country = rr.country GROUP BY r.country I do get rows with columns: "country";"number";"number"; But both "counts()" doesn not show what I want it to...

Mysql intersect results

I'm trying to do something like the PHP array_intersect. I have the following table CREATE TABLE `recipes_ingredients_items` ( `id` INTEGER(11) DEFAULT NULL, `itemid` INTEGER(11) DEFAULT NULL, `orderby` TINYINT(4) NOT NULL, KEY `id` (`id`), KEY `itemid` (`itemid`) )ENGINE=MyISAM AVG_ROW_LENGTH=47 CHARACTER SET 'utf8' COLLATE '...

Query for results ordered by column of association table, most recent association only

given the following schema class User has_many :page_views #[id] end class Page has_many :page_views #[id] end class PageView belongs_to :user belongs_to :page #[id, created_at, updated_at] end How can i query for a given page, all users most recent page views in order by created_at date. One row per user, showing only...

How can I delete duplicate mysql column entries?

I have a mysql table named "dev" with a column of "email"s. I want to select out all the unique e-mail addresses and delete the duplicates. My structure is as follows: id || email 1 [email protected] 2 [email protected] 3 [email protected] What I want is... id || email 1 user1@exa...

How to insert values into an MYSQL-DB via subselect?

Hey folks, In oracle-db it is possible to insert values from table A to table B like Insert into table_a values Select * from table_b where ID = 10 ; If the structures are the same. How can I do this in MYSQL? My Editor gave me an sytanx-error. Thx 4 your answers! Greetz ...

query in sql database

Hi, How do we find the top 2 most populated cities i.e where no. of customers are higher than any other.I only have the SSN of the customers. They are unique and I am thinking of counting them for a particular city and check if that is higher than any other city. ...