mysql

How to find out which script is running a MySQL query?

So I need to fix a query in a large application that I'm not familiar with. The application is live, and the issue is urgent. The query is killing our server. I found the query that is causing the issue, and I know how to fix it. I found it in phpMyAdmin's status or SHOW processlist. But I don't know which script is actually running it. ...

PHP & MySQL delete all comments related to an article question.

Lets say I have 20 comments that are related to article_id 76 will I have to count all the article ids that have an article_id 76 and then run a foreach loop for all the comments related to article 76 or can I delete all the comments related to that article using a query? SELECT article_id FROM articles_comments WHERE article_id = 76 ...

How do I remove rows where some key values have occurred 'before'?

I have views which look something like this: mysql> select * from p2; +---+---------+---------+ | k | measure | time_id | +---+---------+---------+ | D | 200 | 2 | | E | 201 | 2 | | F | 203 | 2 | | A | 20 | 1 | | B | 22 | 1 | | C | 23 | 1 | | D | 100 | 1 | | E | ...

How to select part of a text on mysql?

I have a column saved as LONGTEXT on mysql. This text saves rich text. I'm currently reading all the text then fixing it using javascript to get the first 100 characters in a way not to split the word in its middle. Yet this way don't seem the best way to do it. I want to select a summary directly using the query, yet I also want to be ...

Which query will execute faster in MySql

Hi folks, I created indexing on my tables and then I fire same queries using two different ways:I run those queries on MySql but always got different execution time sometimes first one is faster and sometimes second..Thats why I want experts opinion on this.Queries are First one is select t1.field from table1 as t1 w...

SELECT * FROM table WHERE field IN (SELECT id FROM table ORDER BY field2)

Hi there. I have 4 tables: categories - id, position subcategories - id, categories_id, position sub_subcategories - id, subcategories_id, position product - id, sub_subcategories_id, prod_pos Now I'm doing tests to find out what's wrong with my query. So i want to select sub_subcategories, and to get someting like that: [[1,2,3,4,5...

Meaning of SQL-code in reviewed code

I need review some code of test-application written in PHP/MySQL. Author of this code wrote three SQL-queries. I can't understand, if he meant here some performace optimization? DB::fetch("SELECT COUNT( * ) AS count, `ip`,`datetime` FROM `logs` WHERE `datetime` > ('2006-02-03' - INTERVAL 10 DAY) GROUP BY `ip` ORDER BY `datetime` DESC...

Strange results of full-text MySQL search, need an explanation

Hello, my SQL query SELECT keyword FROM table WHERE MATCH (keyword) AGAINST ('eco*' IN BOOLEAN MODE); matches cells with these words:economy,ecology,echoscopy(why?),echo(why?) etc. other SQL query SELECT keyword FROM table WHERE MATCH (keyword) AGAINST ('eci*' IN BOOLEAN MODE); matches cell with the word:echidna. Yet both queries don'...

Mysql: Order by like?

assume that we are performing search using keywords: keyword1, keyword2, keyword3 there are records in database with column "name": 1: John Doe 2: Samuel Doe 3: John Smith 4: Anna Smith now Query: SELECT * FROM users WHERE (name LIKE "%John%" OR name LIKE "%Doe%") it will select records: 1,2,3 (in this order) but i want to order ...

PHP/MySql - DateTime detecting if values was assigned?

I want to make an if statement that only runs if the datetime value is NOT null (0000-00-00 00:00:00) I have passed the value via a query into a variable but how do i determine if it equals 0000-00-00 00:00:00? $query = "SELECT * FROM stats WHERE member_id='" . $_SESSION['SESS_MEMBER_ID'] . "' "; $result = mysql_query($query); while(...

Remote Server, php, MySQL connection within an iPhone app

I have found many places online that say in order connect to a remote MYSql database, use php. I do not know how to get from my iphone app, to php, to mysql and back. I cannot seem to find anything on the relationship of these things. I would really really appreciate any help in this matter. Where do I write the php code? Where do I...

dump xml string verbatim to mysql db

Hi everyone, I need to dump an xml string (coming into a web service as a POST param), directly into a Mysql DB. The column into which I am writing is currently of type 'text', though I have tried blob as well. Right now, when I save the string and retrieve it later on using sql, it comes back in a serialized like format, like this: a:...

MySQL: ERROR 1064 (42000) - Why is ORDER BY failing?

as a limit 0,50 ORDER BY SortOrder, paid desc, ae desc, name asc, title asc ERROR 1064 (42000): 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 'ORDER BY SortOrder, paid desc, ae desc, name asc, titile asc' at line 48 ...

Trimming in SQL Management Studio 2008

Hi, I'm trying to trim some info from a table. The column has a number and then a word (e.g. 5 Apples). I just need the number so that I can sum the total of apples. I can't use count because I need to go by the value (e.g. 5 Apples for one, 3 Apples for the other) and count will just return that there are 2 entries and not pull the 5...

convert CSV record in column to multiple rows

select * from responses brings: 2 | user_name | green,red Can I change the query so that it brings it in the following format?: 2 | user_name |green 2 | user_name |red number of CSV values in third column varies from record to record. ...

Mix of Database design, multilang and User tracking Q for user content website

Few questions, though i'll ask it all in 1 post. 1) Any difference between a Lookup & reference table? And what is best practice on how many to use? Example List of Cities, Countries, Account types, Category names, User tags, Movie names - are these all lookup, reference or some other type of tables? Key is all this data will be search ...

Import big (My)SQL file

I got a crappy connection so I can't use apps like BigDump, that would stop if I can't stay in the page. What can I do? I'm looking for a way to import without the necessity of keep connected all the time... ...

PHP/MySQL join 2 tables

Hi, I've got one mysql table with usernames, passwords and property names, that allows a user to log-in to a secure site section. Now what I'd like to be able to do is pull-in pdfs from a second table where it checks the property name from table one against the documentCategory of the pdfs in table two, and then only displays the releva...

how do I get this :through?

so I have these relationships: a location: has_many :services has_many :products, :through => :services a product: has_many :services has_many :locations, :through => :services has_many :add_ons and a service: belongs_to :product belongs_to :location has_many :service_add_ons has_many :add_ons, :through => :service_add_on...

foreign key problem with my database

I am creating a social networking site and am working on a friends table and dont know what Im doing wrong. I have a users(userId, name - userId is primary key) and another table called friends(friend1, friend2, status). friend1 and friend2 are foreign keys to userId on update and delete are set to cascade table users has an entry wit...