mysql

html getting garbled from php

Hello, I have a mysql database which as one of the fields contains a html description. This description is not in my control, and is obtained and inserted automatically. An example of one of these descriptions is here: http://www.nomorepasting.com/getpaste.php?pasteid=22492 The data is originally exported from an access database, and ...

Selecting rows where first n-chars are equal (MySQL)

I have a table with playerhandles, like this: 1 - [N] Laka 2 - [N] James 3 - nor | Brian 4 - nor | John 5 - Player 2 6 - Spectator 7 - [N] Joe From there I wanna select all players where the first n-chars match, but I don't know the pattern, only that it's the first n-chars. In the above example I wan't it to return rows 1,2,3,4 and 7...

self join query

Consider the following table: mysql> select * from phone_numbers; +-------------+------+-----------+ | number | type | person_id | +-------------+------+-----------+ | 17182225465 | home | 1 | | 19172225465 | cell | 1 | | 12129876543 | home | 2 | | 13049876543 | cell | 2 | | 15064223454 | home | ...

PHP MySQL Search And Order By Relevancy

I know how to do a regular php mysql search and display the results. However, because of the nature of what I'm trying to accomplish I need to be able to sort by relevancy. Let me explain this better: Normal Query "apple iphone applications" will search the database using %apple iphone application%, but if there aren't records which dis...

Rails transactions

Trying to use ActiveRecord::Base.transaction I figured that rollback doesn't work by default using Rails 1.2.6 and mysql 5.0. Playing with it a little bit more I found out that autocommit is not set to 0 in mysql connection. Questions: 1) how do I disable autocommit in rails for all connections 2) will it have some negative impact on...

mysql update math

say p.products_price equals 1 why does: UPDATE products p SET p.products_price = (1 + p.products_price) WHERE p.products_id = 8 make p.products_price equals 3? It is adding 1 to the price and then doing it all over again? I am trying to do something a little more complicated but when it didn't work I broke it down to the simplest ...

PHP: multiple SQL queries in one mysql_query statement

So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it. -> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly -> It cannot be assumed that the mysqli extension is installed /* cont...

Aptana Studio MySQL Connection String using SSH

Hello, I would like to connect to a MySQL DB using SSH from Aptana Studio, does anyone know what the connection string should be? ...

UTF8 MySQL problems on Rails - encoding issues with utf8_general_ci

I have a staging Rails site up that's running on MySQL 5.0.32-Debian. On this particular site, all of my tables are using utf8 / utf8_general_ci encoding. Inside that database, I have some data that looks like so: mysql> select * from currency_types limit 1,10; +------+-----------------+---------+ | code | name | symbol | ...

Building 32-bit Qt Mysql plugin fails with MinGw

I'm building a MySQL plugin for Qt 4.4.3 Open Source Edition (Qt documentation), and using command: cd %QTDIR%\src\plugins\sqldrivers\mysql qmake "INCLUDEPATH+=C:\MySQL\include" "LIBS+=C:\MYSQL\MySQL Server <version>\lib\opt\libmysql.lib" mysql.pro make I manage to build it to my 64-bit Qt just fine using 64-bit MySQL dev files (using...

MySQL best way to construct this query?

I've inherited a database that has a structure with a table of products, a table consisting of some product attributes and another table to build the relationship between these attributes and a given product. A user can filter the products by a combination of these attributes, meaning that if more than one attribute is selected only pro...

UTF8 problem with MySQL 5

I'm migrating my WordPress blog and phpBB Forum into a new hosting server. I am using phpMyAdmin to import the SQL script from the database in the previous site. When I open the .sql script with Kate, it says it uses UTF8 as encoding. When I import the sql in the new server, I have the option in phpMyAdmin to choose the encoding, where...

How to query for 10 most recent items or items from last month, whichever is more?

On my blog, I want to display the all the posts from the last month. But if that is less than 10 posts, I want to show the ten most recent posts (in other words, there should never be less than 10 posts on the front page). I am wondering if there is a way to do this in a single query? Currently, I first run this query: select count(*...

LINQ for Java tool

Would a LINQ for java be a useful tool? I have been working on a tool that will allow a Java object to map to a row in a database. Would this be useful for Java programmers? What features would be useful? ...

MySQL query takes >15 seconds to run, what can I do to cache/improve it? [PHP]

Well I have a videos website and a few of its tables are: tags id ~ int(11), auto-increment [PRIMARY KEY] tag_name ~ varchar(255) videotags tag_id ~ int(11) [PRIMARY KEY] video_id ~ int(11) [PRIMARY KEY] videos id ~ int(11), auto-increment [PRIMARY KEY] video_name ~ varchar(255) Now at this point the tags table has >1000 rows a...

Should I use PHP or Perl for massaging my data and storing/retrieving it with MySQL?

Hi All, I have a desktop app that needs to send data to a MySQL Server. The app will be for internal company use, but the MySQL is on a server at a hosting company. The data will need to be massaged a bit before being inserted and standard simple insert, delete and update. Which should I use PHP or Perl? I use PHP now for a variety o...

Paginating very large datasets

I have a dataset in MySQL where using limit is already an expensive query, and finding the number of results is expensive as well. Thus, I'd like to avoid doing another query to find the number of results. I cannot use MYSQL_CALC_FOUND_ROWS because the limit is inside a subquery: SELECT * FROM items, ( SELECT item_id FROM ...

Big MySQL Tables

I'm working on a problem that requires caching paginated "search" results: http://stackoverflow.com/questions/347277/paginating-very-large-datasets The search works as follows: given an item_id, I find the matching item_ids and their rank. I'm willing to concede not showing my users any results past, say, 500. After 500, I'm going to...

Is this MySQL query the best idea?

Hi guys. The project I'm working on has two type of accounts, "people" and "companies". I hold a single "users" table with all the accounts and just the basic info needed for login (email, pass, etc), and two other tables "user_profiles" (regular people) and "company_profiles" (companies) that hold more specific columns for each type, bo...

MySQL query for max() of all columns

Hello, What is the correct way of retrieving maximum values of all columns in a table with a single query? Thanks. Clarification: the same query should work on any table, i.e. the column names are not to be hard-coded into it. ...