mysql

SQLException : Before start of result set

ResultSet rs; rs = this.orderedProduct.select(" sum(unitstoproduce) "," soNo = "+ soNo); int sum = Integer.parseInt(rs.getString(1)); When i try to execute the above query inside the java class i'm getting an exception as below. Here the orderedProduct(orderedZnAlProduct) is the table SELECT sum(unitstoproduce) FROM or...

displaying mysql enum values in php

Hi friends what is the best way to show the mysql enum values while inserting and updating php page ? name ENUM('small', 'medium', 'large') edited: actually I was asking for this . I have a database field `color` enum('red','blue','green','white') DEFAULT NULL, and php form <label for="color">Colors</label><br /> <input type="te...

PHP eAccelerator and real-time data

Hi I have this application written in PHP, I'm considering on using eAccelerator for optimizing the application. My dilemma is that, this application works with real-time data (daily there are around 6 to 8 million records inserted). Until now I haven't used any optimizing tool to speed up my applications. So my question is, will eAcce...

localhost vs. 127.0.0.1

Hey, Does using localhost in mysql_connect() make the connection faster than using 127.0.0.1? What is the connection type between the PHP script and mySQL (when using the mysql_connect() function) ? Is it TCP/IP? Thanks Joel ...

Scaling MYSQL database for a multi site Drupal installation with 20,000 visitors per day

I have a multisite Drupal installation with about 20 urls pointing to the same code base and a common database. I have around 20,000 visitors visiting all sites daily which I hope would increase. I am using 2 servers currently- one being the webserver Apache 2 on Linux Platform and the other is the database server- MYSQL. Offlate my MY...

Drupal Multisite installation, old stories showing up

I have a multisite Drupal installation with about 20 urls pointing to the same code base and a common database. I have around 20,000 visitors visiting all sites daily which I hope would increase. I am using 2 servers currently- one being the webserver Apache 2 on Linux Platform and the other is the database server- MYSQL. Sometimes, my...

How do I write the following mySQL query?

I have a database in the following format: idA | idB | stringValue ----+-----+------------ xyz | 1 | ANDFGFRDFG xyz | 2 | DTGDFHFGH xyz | 3 | DFDFDS abc | 5 | DGDFHHGH abc | 6 | GG ... idA and idB together are unique. I have a file (list.txt) with a list of id pairs like this: xyz 2 abc 5 abc 6 ... and I would like to pr...

real escape string and PDO

I'm using PDO after migrating away from the mysql library. What do I use in place of the old real_escape_string function? I need to escape single quotes so they will go into my database and I think there may be a better way to handle this without add(ing)slashes to all my strings. Can someone tell me what I should be using? ...

how to delete duplicate entry by one SQL ?

i has a table deal: CREATE TABLE `deal` ( `id` int(11) NOT NULL auto_increment, `site` int(11) NOT NULL default '0', `area` int(11) NOT NULL default '0', `name` varchar(255) default NULL, PRIMARY KEY (`id`) ); i want to create : UNIQUE KEY `site` (`site`,`area`,`name`); but now, name filed is not UNIQUE,when i create thi...

Can two users work on the same row in mysql?

Hi, In my application I search for products available in my inventory table, select them and then insert the inventoryID into my items table. The SELECT & INSERT are two different operations. Is it possible that another user at the same time could snatch the same rows retrieved and use them to allocate the same inventory products to di...

What is the best way to document a mysql database?

Mainly, what is the best tool for the schema and tables relations visualization? Thanks. ...

PHP Sessions problem, help please.

Hi guys, I'm making an item shop for a game of my friends, when accessing the shop, I have it check the session to see if you are logged in, if you are it will take you to the shop, if you aren't it will give you a login page, the way I do that is like this. <?php if($_SESSION['LoggedIn'] == 1) { //Shop stuff here } else ...

mysql many-to-many tag implementation + full tag listing

based on http://stackoverflow.com/questions/1529073/how-to-implement-tagging-system-similar-to-so-in-php-mysql ive made small changes, and have the following image tag implementation: SELECT I.imageId, GROUP_CONCAT(DISTINCT T.tagName SEPARATOR ' '), COUNT(*) FROM Images I INNER JOIN ImageTagMap M ON I.imageId = M.imageId INNER JOIN Ima...

How to define following using relationships

I need to have one table in which i can set user preferences I want to have one table in which all preferences values are set and another relation table in which i will have user_id and the preference_id but I need to have preference for three different pages item favorite recent. I need to show the list of results as set by user...

Unable to connect to MySQL from PHP : "mysql_connect(): No such file or directory"

Hi, I'm not able connect to mysql via php. It produces [error] [client 127.0.0.1] PHP Warning: mysql_connect(): No such file or directory error message in apache error log when executing mysql_connect() function in PHP. Please find the warning messages when running php -m command: $ php -m | grep mysql PHP Warning: PHP Startup: Una...

How to reduce MySQL query execution time from 90 seconds to less than 5 seconds or even one second

I am using MySQL database. When I execute a query which the main table(20 thousands of rows) is joined with the other 5 tables, it takes 90 seconds to return me the result. I then added indices into each of the criteria(where clause). Then the execution time drops from 90 seconds to 45 seconds and then I tried to select only necessary fi...

PHP: Order by DESC won't do it?

I have this: $getWallCommentQuery2 = mysql_query("SELECT * FROM users_wallcomments WHERE wallID = '$displayWall[id]' ORDER BY date DESC LIMIT 2"); while($show = mysql_fetch_array($getWallCommentQuery2)){ echo $show["comment"] . " <br> "; } Now, my database: INSERT INTO `users_wallcomments` VALUES (1, 401, 1, 2, '12840567...

improve sql query performance

Hello, I have the following tables: Products Categories Subcategories Brands with the following fields: Products: id name description1 description2 description3 id_category id_subcategory id_brand Categories: id name Subcategories id name Brands id name What I want is to make a query, where the user inputs 1 or more...

Should I Use Single Connection or Multiple Connections with QSqlDatabase

I have dependent and independent classes, I need to create database connections in these classes. As I understand from Qt documentation, if I create connections in default way, all of them use same connection. Should I create different database connections for different classes, or should I use same database connection. What are pros an...

Optimize JOIN SQL query with additional SELECT

Hello, I need a query which will select just one (GROUP BY phi.id_product) image for each product and this image have to be the one with the highest priority (inner SELECT with ORDER BY statement). The priority is stored in N:M relation table called product_has_image I've created a query, but it tooks about 3 seconds to execute and I ...