mysql

MySQL won't maintain character information while importing

I'm trying to import a large SQL file; created by mysqldump on one server, then converted with iconv; using these commands: $ mysqldump -uxxx -p xxx > data.sql $ iconv -f UTF-8 -t ISO-8859-1//TRANSLIT data.sql data.iconv.sql I noticed that the "\x91" in the database had turned into "\xc3\x82\xc2\x91" in the dump, because mysqldump tri...

How should I temporarily store rows in a stored procedure?

In essence I'd like to store some rows in a temporary variable for the life of a procedure in MySQL. My procedure will grab a column of foreign keys at the beginning of the procedure. Once I'm done working with them I want to update the table to indicate that they have been processed. There may be inserts into this table while my proced...

Development and Production Database?

Hi there. I'm working with PHP & mySQL. I've finally got my head around source control and am quite happy with the whole development (testing) v production v repository thing for the PHP part. My new quandary is what to do with the database. Do I create one for the test environment and one for the production environment? I currently...

Combining queries in MySQL

Hey all, I know you can combine multiple table-selects using a Join statement but is there a way to combine these two queries into one? SELECT Statistics.StatisticID FROM Statistics ORDER BY `SubmittedTime` DESC LIMIT 0, 10 And this one? SELECT COUNT(Votes.StatisticID) FROM Votes WHERE Votes.StatisticID = ? (fluff removed) At th...

Cross table join using MYSQL rather than MSSQL

Is it possible to do a cross table join in mysql spaning different tables? in different databases. This seem to be easily possible in MSSQL, and greatly speeds up data transfer? How about mysql, do you need to use a powerful IDE to achieve this? or do you have to write a program to do something like this? UPDATE tblUser SET tblUser.R...

Using variables as OFFSET in SELECT statments inside mysql's stored functions

Hi, I'm quite new to subject of writting stored function for mySQL database, hence i'm not sure if what i'm trying to do here is possible at all. I need a function that will return a column from random row from a table. I don't want to use ORDER BY RAND() method to do that, rather i would do this like this: DECLARE MAX_COUNT INT DEF...

php url parameters

I am working on my website, and I am trying to get the url parameter "page" which is an integer that tells which entry to read in the MySQL database that hols the HTML for all the pages. Here is my code, with the MySQL username and password removed for security reasons: if ($_GET["page"]) { $con = mysql_connect("localhost","username...

MySQL - Using COUNT(*) in the WHERE clause

Hey Everyone, I am trying to accomplish the following in MySQL (see pseudo code) SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) > 10 ORDER BY lastupdated DESC Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources. All help is appreciated. Thank you! ...

MySQL: adding current year as a default value for a field 'year'

I have the following table in MySQL (version 5): id int(10) UNSIGNED No auto_increment year varchar(4) latin1_swedish_ci No title varchar(250) latin1_swedish_ci Yes NULL body text latin1_swedish_ci Yes NU...

Which is the Best database for Rails application?

My doubt is the following: I will start to developing a Rails application that will accesss a lot of RSS feeds or crawl sites for data (most news). It will be something like Google News but with a different approach, so I'll store a lot of news (or news summaries), classify them in different categories and use ranking and recommending t...

Is there a way to do an "INSERT...ON DUPLICATE KEY UDPATE" in Zend Framework?

I would like to use "ON DUPLICATE KEY UPDATE" in Zend Framework, is this possible? Example INSERT INTO sometable (...) VALUES (...) ON DUPLICATE KEY UPDATE ... ...

Efficient Filtering / Searching

We have a hosted application that manages pages of content. Each page can have a number of customized fields, and some standard fields (timestamp, user name, user email, etc). With potentially hundreds of different sites using the system -- what is an efficient way to handle filtering/searching? Picture a grid view that you want to narr...

MySQL - Tricky LIMIT for each WHERE_IN problem.

Hey Everyone, I am looking to do the following (see pseudo code); I want to select 4 rows for each gd.id (7, 11 or 9). I've incorrectly use limit because that only brings up 4 rows in total. Anyone have an idea on how to change this query to accomplish my goal? SELECT gd.gid, gd.aid, li.ads, li.til FROM gd JOIN li ON li.a_id = gd.a...

Using mysql concat() in WHERE clause?

I would like to search my table having a column of first names and a column of last names. I currently accept a search term from a field and compare it against both columns, one at a time with select * from table where first_name like '%$search_term%' or last_name like '%$search_term%'; This works fine with single word sear...

Accessing MySQL relations with Ruby on Rails

How do you access a MySQL relation using RoR? ...

Log all queries in mysql

Hi, Is it possible for me to turn on audit logging on my mysql database? I basically want to monitor all queries for an hour, and dump the log to a file. ...

does mysql index foreign key columns automatically?

The subject says it all... ...

generate an integer sequence in MySQL

I need to do a join with a table/result-set/whatever that has the integers n to m inclusive. Is there a trivial way to get that without just building the table? (BTW what would that type of construct be called, a "Meta query"?) m-n is bounded to something reasonable ( < 1000's) ...

Is there a way to see a prepared query as it will be executed on the database?

I'm using the mysqli extension in PHP and I'm wondering, is there possibly any way to see a prepared query as it will be executed on the server, e.g. The query is something like this select * from table1 where id = ? and name = ? but I want to see the query after the values are filled in, like this: select * from table1 where id = 20...

Single database tool to access different database types?

I’d like a visual database tool that will allow me to access multiple database types from the same program. I’m looking for anything that supports Mysql, PostgreSQL, and Derby. Anything else it supports is a bonus. ...