mysql

unable to connect to database on AWS

hi all actually I have my website build with Joomla hosted on hostmonster but all Joomla website need a database support to run this database is on AWS configuration files need to be updated for that I have to change host address I put the IP (of AWS host) in host but it displayed "unable to connect to database MySQL" can u help me ...

Nested Set Model, count items in categories

I've got a Nested Set Model working for my site with items in subcategories and so on. It's working great except for one problem I can't come around. +---------+-----------------------------+ | item_id | item_name | +---------+-----------------------------+ | 1 | Laptop | | 2 | iPod Cla...

how to connect to database on another server

Hi, Could I have my php scripts on server A and connect to the MySQL database on server B? If yes, how it would be done? Thanks in advance ...

Regexp MySql- Only strings containing two words

Hi I have table with rows of strings. I'd like to search for those strings that consists of only two words. I tried few ways with [[:space:]] etc but mysql was returning three, four word strings also ...

error when creating PROCEDURE on mysql

Am trying to create a mysql stock procedure, but i have the following error Script line: 2 Failed to CREATE PROCEDURE proc_test_bideep The syntax proc is: DELIMITER $$ DROP PROCEDURE IF EXISTS `commun`.`insert_categorie` $$ CREATE PROCEDURE `commun`.`insert_categorie` (id_mere INT, ...

SELECT command to calculate percentage

I'm trying to get the percentage of each video I have in my database based on its view count against all other videos. I'm then trying to display all the videos from highest view count to lowest, displaying its percentage on its side inside a nice HTML page. Obviously the percentage would range from 0 - 100% (and not over) and the mos...

mysqldump entire structure but only data from selected tables in a single command

My database has 3 tables: table1, table2 and table3 I would like to do a mysqldump on this database with the following conditions: Dump structure for all tables Only dump data for table1 and table2, ignore data in table3 Currently, I do this with 2 mysqldump statements mysqldump -u user -p -d db > db_structure.sql mysqldump -u user...

Persistence strategy for low latency reads and writes

I am building an application that includes a feature to bulk tag millions of records, more or less interactively. The user interaction is very similar to Gmail where users can tag individual emails, or bulk tag large amounts of emails. I also need quick read access to these tag memberships as well, and where the read pattern is more or...

SQL design for survey with answers of different data types

I am working on an online survey. Most questions have a scale of 1-5 for an answer. If we need to add a question to the survey, I use a simple web form, which does an INSERT into the appropriate table, and voila! surveys are asking the new question -- no new code or change to the database structure. We are being asked to add survey ques...

MySQL Connector/J Problem

So I've been having issues with the MySQL Connector/J driver not correctly loading in a Java Web Start application that is running on Tomcat 6.0.20. I've copied the MySQL connector JAR file into the lib directory of Tomcat as well as the lib directory within webapps//WEB-INF. I also added a reference to the JAR file inside of the JNLP ...

Check a field in MySql from a php md5 string

hello all! I'm trying to validate a pair of data columns on mysql from my php page across md5 function. I've encrypted the string "helloworld" with php md5 function and attempted to compare it with MYSQL MD5 function but it won't work. I do this because in the database there is a pair of strings "hello" and "world" needs to be compared w...

Mysql compaire two dates from datetime?

I was try to measure what is faster and what should be the best way to compare two dates, from datetime record in MySql db. There are several approaches how to grab a date from date time, but I was concentrate on two one is to use DATE function and second is to use LEFT function, something like this DATE(created_on) , and for LEFT...

MySQL: Getting data for histogram plot?

Is there a way to specify bin sizes in MySQL? Right now, I am trying the following SQL query: select total, count(total) from faults GROUP BY total; The data that is being generated is good enough but there are just too many rows. What I need is a way to group the data into predefined bins. I can do this from a scripting language, but...

Is a one table 9 GB database good design?

I was asked to think about a database for our application. There are about 7 different data need to be stored. One is identification data which may contains an unique serial number, time, location. All other 6 data sets (4 binary raw data, 2 text data) must be identified by the identification data. 3 of them are about 2 MB a record, othe...

Cleaning a Production Database for use in Testing

I'm building a local vm for doing web dev rather than using our on site development. I need a database locally, but I don't want to just pull down a production db and use that as it has information that, while not protected by HIPAA or anything, should not be available in the case of laptop theft. Are there any apps or recommended practi...

MySQL skip a column name when inserting values

I have a table in which the first column is auto_increment. I want to insert data into the table, but skip the first column as it is updated automatically when a new row is begun. so: INSERT INTO table VALUES (NULL,"lady","gaga","rulz"); But NULL cannot be inserted into a column as I specified earlier. What do I need to replace NULL w...

Select from table1 WHERE table2 contains ALL search parameters

I have two tables (notes and tags). Tags has a foreign key to notes. There may be several tag records to a single note record. I'm trying to select only the notes that contain all of the desired tags. SELECT notes.*, tags.* FROM notes LEFT JOIN tags ON notes.id = tags.note_id WHERE {my note contains all three tags I would like to sear...

MYSQL DATE function running insanely slow in LEFT JOIN

When adding the line: LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold) To my query, it boosts the time for the script to run from about 8 seconds, to 2-3 minutes. Would there be some data causing this problem or am I not implementing the function correctly? I need to use DATE() because I need ...

Convert a mySQL date to Javascript date

What would be the best way to convert a mysql date format date into a javascript Date object? mySQL date format is 'YYYY-MM-DD' (ISO Format). ...

MySQL AVG query help.

I have this query which works perfectly: SELECT cp.* FROM CustPrimaryQ cp JOIN Customer c ON cp.CxID = c.CustomerID JOIN SacCode sc ON sc.SacCode = c.SacCode WHERE sc.ResellerCorporateID = 392 However I am trying to modify it to calculate an average. Each row of the CustPrimaryQ table has a field called QScore and it's this f...