mysql

Selecting a float in MySQL

I am trying to do a SELECT match on a table based upon an identifier and a price, such as: SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31'; The above returns zero rows, while if you remove the price='101.31' bit it returns the correct row. Doing a... SELECT * FROM `table`; Returns the same row as above and qui...

Whats a great dev setup for working with PHP + MYSQL?

I've been a PHP Dev for many years now, and it just dawned on me that maybe I could be using better development tools. For example, my typical setup for development is Notepad ++ Dev WAMP Server (local machine usually) CodeIgniter framework (lately I've fallen in love with it, as it speeds up deployment for me, big time.) PHPMYADMIN ...

How to call MySQL Stored Procedure from SQL Server 2000?

I have two databases, one MySQL 5, one SQL Server 2000. I've got the MySQL database mapped as a linked server in the MS SQL database. I'd like to call a stored procedure saved in the MySQL database from the MS SQL database. What's the correct syntax to do this? Is it even possible in SQL Server 2000? Edit: I've tried EXEC webpush......

PHP/MySQL Reservation System

I'm currently working on an Equipment Reservation System for my school. Here's basically what my tables look like: tblEquipment: id name description 1 Camera Takes pictures 2 Projector Projects images 3 Stereo Plays music tblEvents: id equipmentID start...

How do I alias a database in MySQL?

I'm looking for a way to alias a database in MySQL. The reason is to be able to rename a live, production database without bringing the system down. I figure I can alias the database to the new name, change and deploy the code connecting to it at my leisure, and eventually remove the old alias. If there's a better way to accomplish th...

Show data sorted by parent objects

Hey guys, i have a problem and don't really know, what to search for to find a solution. Description: I have a mysql table with the following colums "id", "name", "value", "parent" with "id" as primary key. "id" is a autincreament number, "name" is the name of the category, "value" is the value of the category and "parent" is the pare...

Semi-Complicated PHP/MySQL Select Statement

Hello all, I currently have 3 tables, which I'm using for people to make reservations for certain pieces of equipment. Here are my tables: tblEquipment: id name description 1 Camera Takes pictures 2 Projector Projects pictures 3 Laptop Portable Computer tblEven...

Unixtime & mysql

Hi guys, I have a field on my table which is called X, and I store unixtime(php -> time()). My question is: How can I list all the months from my DB, something like: 6.2009 8.2009 etc.. And my second question is: How can I make a query to list all the informations based on a month and year on the same field X(i store unixtime),...

migrate postgreSQL data to mysql

I'm looking to grab a few bits of data from musicbrainz db to use in a mysql based app. I don't need the entire database, and have been looking at 'migrating' postgreSQL to mysql, which it seems lots of people have difficulty with. Wouldn't it be simplest to just dump the postgreSQL data into a comma-delimited text file, and then imp...

Is a VARCHAR(20000) valid in MySQL?

I’m in need of some clarification of the maximum length of a varchar field in MySQL. I’ve always thought the max length was 255 (255 what? Characters I’ve assumed, but this might be a source of my confusion). Taking a look at the tables of a database set up by an external company we’re working with, I see a field set-up as varchar(20000...

Why remove django DATABASE_OPTIONS's "init_command set engine=INNODB" after table creation?

http://docs.djangoproject.com/en/1.0/ref/databases/#creating-your-tables says: Another option is to use the init_command option for MySQLdb prior to creating your tables: DATABASE_OPTIONS = { "init_command": "SET storage_engine=INNODB", } This sets the default storage engine upon connecting to the database. After your tables have ...

How to program a vote up system?

I am in the very beginnings of teaching myself php. I am giving myself micro projects to push myself. Thus far I have a MYSQL database, created through a php form. One Column is for karma. I have the values of the database table display in an html table, and at the end of each row, I would like a click on a hyperlink, lets say a plus si...

MySQL fulltext performance solution

How to deal with queries like : select ... from ... where match(field1) against('someA') and match(field2) against('someB') limit 50 If results returned by condition match(field1) against('someA') & match(field2) against('someB') are both huge,the entire query will be very,very slow. A solution for this? ...

Most efficient way to find the closest integer in MySQL?

I have a table in a MySQL database from which I want to select the row with the closest timestamp to another given timestamp. time is the timestamp column (an integer UNIX timestamp). I chose 1250710000 arbitrarily. This is the query that I've come up with, and I'm wondering if there's a more efficient way to do it: SELECT *, ABS(time...

Retrieving top 25 tables (defined by field values)

Hello, The code below returns the top 25 most recently-created tables in a MySQL database. $index = mysql_query("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='bookfeather' order by CREATE_TIME desc limit 25"); Each table has the following format: id INT(11) NOT NULL auto_increment, site VARCHAR(350) NOT NULL...

how to join or Union result of 2 sql queries in single result set

i have 2 tables(result of two separate SQL queries and this result will be contained by List<Object> where each object represents 1 row of the database ) Table_1 Dimension_1 Dimension_2 Fact_1 Table_2 Dimension_1 Dimension_2 Fact_2 I want to join these two result in the RESULTSET AS Table_Resultant Dimension_1 Dimension_2 Fac...

MYSQL: self written string-manipulation function returns unexpected result

I'm trying to implement a MYSQL function MY_LEFT_STR(STRING x,INT position) in such a way that MY_LEFT_STR('HELLO', 4) => returns 'HELL' (same as internal LEFT function) MY_LEFT_STR('HELLO',-1) => returns 'HELL' DROP FUNCTION IF EXISTS MY_LEFT_STR; CREATE FUNCTION MY_LEFT_STR( in_str VARCHAR(255), pos INT ) RETURNS VARCHAR(255)...

DESCRIBE in a FROM subquery

Hi Guys, Is it possible to have a DESCRIBE table clause inserted as a subquery in FROM of a SELECT clause in MySQL? Further, is there a way to enforce a WHERE like condition on a DESCRIBE output? EDIT: Basically, I have a table with a large number of columns and I want to pull out and act upon the particulars of only one column. ...

How do I select multiple items from each group in a mysql query?

I have some forum data of the form post(author, thread_id, text) For each author, I would like to select 10 distinct thread_ids associated with that author (there may be more than 10, and the number will vary by author). I'm thinking of using GROUP BY to group on 'author', but I cannot understand how to express the LIMIT on each grou...

Insert Subquery in Select Query

Hi, I have a "Groups" table and a "Participants" table. Now I need to insert one Participant for each Group. How would I automate this? INSERT INTO "Participants" ("Name", "FirstName", "GroupID") VALUES ("GENERIC", "GENERIC", GroupID) This Insert should be called for each Group in the Groups table, and the "GroupID" replaced with the...