mysql

Select one row with C# from MySQL?

I tried MySqlDataReader and any variation of mycmd.ExecuteScalar() but still there is no success. At last I am using MySqlDataAdapter and Fill method and use some if cases and select one row from there. but this makes the code uglier. Here is the example: DataSet tmpDs = new DataSet(); myda.Fill(tmpDs); if (tmpDs.Tables.Count > 0) if...

Extracting words from text field in SQL

Hi, I'm currently building a little CMS for a smaller site. Now I want to extract all words from the text_content field and store them in my word table for later analysis. page( id int, title varchar(45), # ... a bunch of meta fields ... html_content text, text_content text); word( page_id int, # Forei...

Help with MySQL Query with many Joins

Setup: Contact database using 4 tables Contacts Cities States Zips Structure: CREATE TABLE `contacts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, `first` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, `prefix` varchar(50) ...

PHP + MySQL profiler

You know how vBulletin has a sql profiler when in debug mode? How would I go about building one for my own web application? It's built in procedural PHP. Thanks. ...

Tools for Migrating from Oracle to MySQL

I want to migrate schema from Oracle to MySQl, so are the any free tools that would be useful for this task ? I have only Create tables in Oracle SQL Script but it contains unique constraints, foreign key and MySQL has MyISAM storage engine and so foreign key is not supported, how to go about this issue ? Sample Oracle create statments...

php time is not giving the correct date

I'm looking to get the current date and time in a UNIX timestamp so I do some calculations but I am not getting the correct time. The date is correct but the time is off. I've already set my timezone so I'm lost on this. Can someone lend a hand? Thanks. Here is some of the code I am using: date_default_timezone_set('America/Los_Ange...

Storing Twitter OAuth in MySQL

I'm about to add a feature on my website that will post stuff to the users' twitter accounts. I read that storing an "OAuth" token is better than storing their usernames and passwords (which makes sense). What should I store in my MySQL database? The token, secret and username? Or just the token? What data type(s) would you use? How bi...

Can I use a MySQL database with an App Engine application

I know that App Engine has its own datastore. This is great for most cases and fairly easy to used. However, we have a MySQL database that we use for several applications and not all of them are Web based. We want to use App Engine for many reasons, but would like to have the App Engine application access our MySQL database. The document...

MySql query is to slow. `Join Tbl AS t ON t.c = t0.c`

My supervisor told me this query will 'crash the server' (because it has millions of tables i believe). Can anyone tell me WHY? maybe show me the way i am suppose to do it? I am currently reading manuals to figure this out. What i was told is these joins are to slow. What is slow about it? "JOIN A AS o ON a.A =aa.A " . "JOIN B AS...

php/mysql: how to search case insentitively

hello, i have this table in UTF8 (collation is utf8_bin in case it matters). I need to search in it for strings using LIKE '%$searchstring%' . Right now you have to use the exact case in order for the results to show up. Is there a way to make it case insensitive ? Thank you UPDATE sorry, found the answer. ...

MySQL add fields to an Enum

I have to add some enum options to a database table. The problem being, I will have to do this to several tables & databases, and they may not all have the same enum data type. Is there a why to write an alter table query or something similar to append options to a enum data type? If there is no way to do this purely in MySQL, how woul...

MySQL remove duplicates from big database quick

Hello, I've got big (>Mil rows) MySQL database messed up by duplicates. I think it could be from 1/4 to 1/2 of the whole db filled with them. I need to get rid of them quick (i mean query execution time). Here's how it looks: id (index) | text1 | text2 | text3 text1 & text2 combination should be unique, if there are any duplicates, only ...

MySQL Default Keyword usage errors

When am trying to set default date and default sysdate am getting following errors: MySQL Query: create table product_offer_type(object_id INT(19), snapshot_id INT(19), PRIMARY KEY(object_id,snapshot_id), enum_value VARCHAR(64) NOT NULL, external_name VARCHAR(64) NOT NULL, description VARCHAR(255), business_validation INT(1), valid_for...

[Ruby/PostgreSQL]: How to host my first Ruby website?

I am a Ruby newbie. I am designing a web application and plan to use Ruby for coding. This project is for learning purpose but I want to host it live to see how it works. I plan to use PostgreSQL as back-end. I want to know how to host a Ruby website. Which hosting service providers provide support for it? And I also want to know whethe...

Mysql query. What is the difference between Join and a SubQuery?

I always though join gets the results of something and then joins only using those results. SELECT * FROM tbl AS t1 JOIN tbl2 AS t2 ON t1.id = t2.foreignId JOIN tbl3 AS t3 ON t2.id = t3.foreignId WHERE t1.date > SOMEDATE From my understanding it will run the where statement and get only the results that fall within the date range. Th...

can't start mysql on mac OS X getting mysql.sock connection error

I'm trying to get mysql running on my mac. I used macports for the initial install but can't get past this mysql socket error. Note: macports appends the version number to commands if they look odd below. Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' I get this error when: mysqladmin5 -u ro...

MySQL Count values based on multiple columns

I have two tables: a 'userlist' that has a 'grade' and 'userID' columns, and a 'login' table that records each time a user is logged in. The login table has a 'userID' column that is associated with the 'userID'. This login table has a row inserted into it each time a user is logged in, so if a user logs in three times, three rows will b...

Can I use wildcards in "IN" MySQL statement?

I would like to run something like: select * from table where field in ("%apple%", "%orange%") Is there a way? Or at least is there a better way than dynamically building query for every keyword: select * from table where field like "%apple%" or field like "%orange%" Thanks. ...

MySQL - Is this possible in one query?

I'm currently running the following query: SELECT * from people WHERE id = 4; on the following table: id name state age ----------------------------------- 1 tony jones CA 22 2 Sue Smith FL 50 3 Alex Guam AL 44 4 tony jones SC 32 5 tony jones OH ...

How to gem install mysql with ruby version 1.8.4?

I need to install mysql on the system. As usual I typed in gem install mysql. Instantly got an error message: "mysql requires Ruby version >= 1.8.6" Current version is at 1.8.4. Considering it's a company's server, it's not easy to upgrade Ruby in a short time (people are afraid their stuff will be broken and it needs a special approval ...