mysql

insert value in Mysql containg single quotes

How do I insert a Value in Mysql that consist of single of Double Quotes. i.e, Value: This is Ashok's Pen. This Single Quote will create problems. There might be other escape characters... How to insert the data properly.. Thanks in advance for ur precious time.. ...

How would you optimize the following query.

I am using the following query to find out to top 6 viewed pages in my Drupal site: SELECT n.title, n.nid, c.daycount FROM node n JOIN node_counter c ON n.nid=c.nid WHERE n.type='page' AND n.status = 1 ORDER BY c.daycount DESC LIMIT 0,6; This is very natural and works well on most sites. However, on a site with many nodes (1.7m), ...

MySQL query indexing with timestamp structure

Hi all, I was wondering what the best way of storing user queries correlated with timestamps in MySQL was. Let's say I have just two inputs, a user's "query" and "timestamp"... I could create a MySQL table with fields (id, query, count, timestamp_list), where: id is unique identifier of the query, query is the literal query string...

convert the following code in XML

I am getting the result in browser, but i need this output to be in xml format too. print "<h2>Search results for '".$_POST['keyword']."':</h2>\n"; for( $i = 1; $row = mysql_fetch_array($result); $i++ ) { print "$i. <a href='".$row['url']."'>".$row['url']."</a>\n"; print "(occurrences: ".$row['occurrences'].")<br><br>...

Does dropping a table in MySQL also drop the indexes?

It's not explicitly mentioned in the documentation (http://dev.mysql.com/doc/refman/6.0/en/drop-table.html). I ask because I just saw a curious database migration in a Rails project where the developer was removing all the indexes before dropping the table, and that seemed unnecessary. ...

MySQL UPDATE behavior

mysql> update tablename set fieldname = 'C200900674' where fieldname - 'C200900673'; ERROR 1062 (23000): Duplicate entry 'C200900674-2008-0-1' for key 1 Any thoughts or suggestions on this? We had someone accidentally do that update with a minus sign instead of an equal sign. It apparently tried to change all records less than that val...

any formal benchmarking of Open source Database software ?

Hi, Is there any formal performance and stress test reports of open source database, specially sqlite,MySQL an PgSQL? I want to use sqlite in server for its simple structure and easy embeddable capability. But I can not find any pros and cons (by Googling and Yahoo!ing) regarding performance of these database software. Please suggest....

How to order the results of a query by the same order as the IN statement ?

Hello I have the following query (I'm using mysql) SELECT * FROM campaigns where campaign_id IN ( 'idStrOne', 'idStrTwo', 'idStrThree' ); The results of which are being ordered by the primary key of the table campaigns which is 'id'. This is not the order I want. I want the results to come back in the same order as the arguments t...

mysql slave database problem

Currently we have 3 slave databases, but almost always there is one of them extremly slow than others(can be an hour after master database) Has any one met similar problem?What may be the cause? ...

mysql query help (like statement)

I am using mysql which i have a stored procedure which has an input variable. I want to use this variable in a select statement (with like clause). Eg: DELIMITER $$ DROP PROCEDURE IF EXISTS `DeleteDataByTransactionID` $$ CREATE DEFINER=`root`@`%` PROCEDURE `DeleteDataByTransactionID`(in **$TransactionID** varchar(50)) BEGIN delete ...

How to get Phusion Passenger, MySQL and Rails to play nicely in production

I've written a rails app that's been running fine for a while without a database. Now, I'm dropping the registration database into the mix. It's not working in production. Everything works fine, of course, on my Mac. Here's the production environment: - Ubuntu Hardy - Phusion Passenger - Rails 2.3.2 MySQL on the machine is running fin...

How to use a string/column value as a mysql date interval constant (DAY, MONTH...) ?

I have three columns: a date column, a integer column, and a varchar column like this: +------------+------+---------+ | date |value | unit | +------------+------+---------+ | 2009-01-01 | 2 | DAY | | 2009-02-01 | 3 | MONTH | +------------+------+---------+ I want to use the values of the integer and the varchar...

Fetch only first match in a query where there is an order precedents?

I have a table with fields id (primary key), name1, name2, and nicknames. Given a name, I want it to return the entry that contains that name in any of the three fields; however, I want it to return only one entry and sometimes the query that I have returns more than 1 match. Also, if there is more than one match, I want it to return th...

Mysql Sort order very Weird

SELECT events.title FROM events ORDER BY events.title DESC I'm getting the proper ordering for all but a couple events at the end of my table. The encoding on the title table is utf8_general_ci. I've tried retyping the the title, hoping it was using some weird russian characters I couldn't see, but it still appears in the wrong order....

Change value in Mysql database via PHP

Hey all, I want to change a value in a table (in a mysql database) via PHP code. I have a row in the table called 'approved' and there are two options it can be set to, "0" (not approved) and "1" (approved). I am creating a script that will change an individual approved from "0" to "1". For example, there is a different value called...

Format STR_TO_DATE to a date.

Hi I have been able to extract a VARCHAR to a date using string_to_date, however whatever I try the date always echoes as e.g. 2009-05-25 Here is my code that works: $query = "SELECT u.url_id, url, title, description, STR_TO_DATE( pub_date, '%d-%b-%Y') AS pub_date FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AN...

MySQL: Move node in nested set

I'd need a MySQL query that moves a node and all it's children within a nested set. I found this site, but that function just seems so illogical - there's no universeid or treeid in a nested set model, and the code itself it just longer than what feels required. The only extra column I've got in the table is "parent". I couldn't just re...

MySQL: Calculated Column Based on Two Calculated Columns

I'm trying to do a rather complicated SELECT computation that I will generalize: Main query is a wildcard select for a table One subquery does a COUNT() of all items based on a condition (this works fine) Another subquery does a SUM() of numbers in a column based on another condition. This also works correctly, except when no records m...

MySQL Type Conversion: Why is float the lowest common denominator type?

I recently ran into an issue where a query was causing a full table scan, and it came down to a column had a different definition that I thought, it was a VARCHAR not an INT. When queried with "string_column = 17" the query ran, it just couldn't use the index. That really threw me for a loop. So I went searching and found what happened,...

Is there anyway to upgrade PHP4 mysql client?

I get the following error: Client does not support authentication protocol requested by server; consider upgrading MySQL client I know I can solve it with: SET PASSWORD FOR user@localhost = OLD_PASSWORD('password'); But I want to know if there is another way to do it by using a different PHP mysql extension or something? ...