mysql

Full text search from the database

Hi: Suppose I have a MySQL database named DB, and a table named ContactInfo. The following is its structure and some example data(Just one item): Table----ContactInfo: ----------------------------------------------------------- name fullname phone -----------------------------------------------...

What MySQL field should I use for a Yes/No Checkbox value?

Hello, I am using the MySQL table below. I would like to add a field called 'subcheck' that will be a yes/no value determined by the HTML form input TYPE = CHECKBOX. What "type" should I give this new field? Thanks in advance, John `submission` ( `submissionid` int(11) unsigned NOT NULL auto_increment, `loginid` int(11) NOT NUL...

Indexing this mysql query

I am querying database with follwing query. This query takes 21 seconds to execute. I have check it by explain query. I have index on fields groupId , batchId separately. EXPLAIN SELECT message, sentOn, maskId, isDndCheck, contentType FROM sms_histories WHERE groupId = 1750 GROUP BY batchId ORDER BY batchId DESC LIMIT 0 , 1 I am gett...

Entering a variable amount of data into a database with the best normalization possible

Hi guys, ok, so I have a database comprising of two tables, products and suppliers. All suppliers fill in a form and their data is then stored in the suppliers table, and the products table contains a list of all of the products, so when the supplier fills in the form, he can choose as many products as he wishes as I use jQuery JSON a...

Using an HTML checkbox to put 1 or 0 into a MySQL table

Hello, I am using a simple HTML checkbox in a form to put a 1 for checked and a 0 for unchecked in a field called "subcheck" in a MySQL table. Does the checkbox default to 1 for "checked" and 0 for unchecked? If not, how can I give it those values? Thanks in advance, John Form: <div class="subcheck"><INPUT TYPE=CHECKBOX NAME="subc...

MySQL Insert performance degrades on a large table

I'm working with a huge table which has 250+ million rows. The schema is simple. CREATE TABLE MyTable ( id BIGINT PRIMARY KEY AUTO_INCREMENT, oid INT NOT NULL, long1 BIGINT NOT NULL, str1 VARCHAR(30) DEFAULT NULL, str2 VARCHAR(30) DEFAULT NULL, str2 VARCHAR(200) DEFAULT NULL, str4...

Manually construct SQL with binary data in Java.

I have a small program that manually creates queries. In pseudo code, it's basically done like this string[] a = new string[x]; a[0] = "data 1"; a[1] = "data 2"; a[2] = "data 3"; string query = "insert into x (y) values("; for i { query += a[i] + ","; } query += ");"; I'm aware that this usage is sub-optimal and I should do a com...

Creating a Secure iPhone Web Data Source

I've searched the web for this bit to no avail - I Hope some one can point me in the right direction. I'm happy to look things up, but its knowing where to start. I am creating an iPhone app which takes content updates from a webserver and will also push feedback there. Whilst the content is obviously available via the app, I don't wa...

push Rails3 on Heroku. can not find mysql gem

hi, i'm getting an error when pushing to heroku using rails 3. I'm on windows with ruby 1.8.7 Administrator@WIN-DQC3IH63U7C ~/nasha (master) $ heroku rake db:migrate rake aborted! no such file to load -- mysql /disk1/home/slugs/280561_9c64ba2_1741/mnt/Rakefile:4 (See full trace by running task with --trace) (in /disk1/home/slugs/280561_...

Recreate mysql database structure and stored procedures

I have a set of MYSQL tables on the development server that I need to place on the production server. How can I "recreate" all of them? There are triggers present as well (I think). I need to recreate everything. ...

Uncaught Exception 'PDOException' with message 'SQLSTATE[HY000] [2003] Can't connect to MySQL server

Hi, I am using Zend Framework and my application works on localhost but produces the can't connect to MySQL server error when I'm trying to connect to the database I've uploaded on a live server. I've tried handling exceptions and Zend_Exception catches it("perhaps factory() failed to load the specified Adapter class"). I've emailed the ...

mysql, using if

I have this sql query: ... LEFT JOIN users ON users.id = mod.id and mod.level = 1 ... But if don't found any result with mod.level = 1, i wish search with mod.data > 1 (users.id = mod.id and mod.data > 1) ...

What's the best approach to designing a database that keeps track of orders and wish lists?

The best way to describe this scenario is to use an example. Consider Netflix: do they store their orders (DVD's they mail out) in a separate table from their member lists (NOT members table, but a joiner table of members and movies--a list of movies each member has created), or are orders distinguished by using additional information i...

MYSQL retrieve results where date = next weekend

Currently I have two tables and the following sql statement which correctly retrieves the items in the events table ordered by their dates in the event_dates table: SELECT * FROM events, event_dates WHERE events.id=event_dates.event_id AND events.preview=0 AND event_dates.start_date>=now() ORDER BY event_dates.start_date ASC,eve...

query works on MySQL, but no in PHP, why ??

Hi, I need to display the results of this query : SELECT * FROM projects WHERE PrestaCmd LIKE '% A - CREP - DPE - %' but in PHP, this query doesn't work :s This is my code : $req = "SELECT * FROM ".$table." WHERE PrestaCmd LIKE '%".$ch."%'"; echo $req; //returns : SELECT * FROM jos_projectlog_projects WHERE PrestaCmd LIKE '% A ...

Update Local Server MySql Database from the Server MySql Database

How it is possible to update localhost MySql database with server mysql database at particular interval. Web application is made in PHP and database should be in MySql. Suppose I have a Database at Hosting site Now i want to update my database at regular interval with Local Host Database at my Local Computer.Please suggest me ?? ...

Is bulk insert atomic?

I have table with auto increment primary key. I want insert a bunch of data into it and get keys for each of them without additional queries. START TRANSACTION; INSERT INTO table (value) VALUES (x),(y),(z); SELECT LAST_INSERT_ID() AS last_id; COMMIT; Could MySQL guarantee, that all data would be inserted in one continuous ordered flow...

How do I set a full date & time sql using java, and not just the date?

I am trying to set a timestamp in my database using java, however in my table all I get is the date, and no time (i.e., looks like "2010-09-09 00:00:00"). I am using a datetime field on my mysql database (because it appears that datetime is more common than timestamp). My code to set the date looks like this: PreparedStatement ps = c...

sql search query still not producing expected results.

I need a count and search query to work properly. The count query appears to be working properly, however the search query is not. Count query: SELECT COUNT(DISTINCT tg_id) FROM tg_keywords WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' ORDER BY tg_keyword LIKE 'keyword_1' AND tg_keyword LIKE 'keyword_2 DESC Re...

Find where and which word matched sql LIKE statement

I am using a very long like query to detect offensive words within my users usernames. I have made a moderator page where the moderator can click a button next to the detected username and it will replace the offending word with *'s. The problem is how can I easily detect which offensive word and where to replace? I guess I would need...