Question revised Really wanted a group_concat of sums... Table: shops +---------+--------+--------+ | shop_id | name | state | +---------+--------+--------+ | 0 | shop 0 | 5 | | 1 | shop 1 | 5 | | 2 | shop 2 | 5 | | 3 | shop 3 | 2 | +---------+--------+--------+ Table: items +---------...
I have 2 tables. The first one is "product" with "id" and "title" columns. The second is "price" with "productId", "price" and "weight" columns. One product can have several weights with it's own price (Simplified situation.) I want to select all products sorted (DESC) by their minimal price. How to do this? ...
Is it possible to get the size of the result-set when doing a query? I need to set a proper MySql cache_limit (MB) and therefore I am trying out some queries, but I need to know the sizes of the result-sets to fine-tune my cache configurations. What exactly does query_cache_limit do when measuring the size of a query (or result)... An...
I am investigating using javax.sql.rowset.CachedRowSet in part of my application, however I can only find information on using the proprietary sun implementation com.sun.rowset.CachedRowSetImpl or Oracle specific implementations. The sun implementation is unsupported and subject to change. Using this could cause also us problems if we w...
Hi, I'm running WordPress and I'd like to write a custom query that would show a monthly archive of posts based on one specific category. I used the debug mode to find out how to get a monthly archive based on all posts, select a specific category. Here are the queries: Get montlhy archive: SELECT YEAR(post_date) AS `year`, MONTH (post...
I want my function to only run once. Meaning that if many threads call it at the same time, the function will block all threads apart from one and only allow it to run. ...
how can i get row count values in mysql as @@ROWCOUNT in mssql ...
Dear all, I want an auto username suggestion if a username is already used ,using mysql procedure ...
I want to return custom values as the values of the rows in case when no rows are fetched by executing the stored procedure How do i do that? ...
Hi, I recently upgraded my MySQL data adapter from v5 to v6. When I use the v5 MySQL Data Adapter this code below worked perfectly. However since I have begun using v6 of the data adapter, when I add a new record, it does not return the "new" ID of the record. Does anyone know an elegant way to achieve this? protected void SetRecord(ref...
I'm using Open Flash Chart to create statistics and one of the things i need to be able to do is generate a Stacked Bar Chart. Logically i need to be able to Group all the distinct Month/Year combinations, Dec 2009, Jan 2010, Feb 2010 etc. and then from that group all the various rows, i.e. the different types of enquiry a visitor made ...
I have a form field that includes a mixture of HTML and text. I want users to be able to use basic HTML and punctuation. Currently I am using mysql_real_escape_string and preg_replace to sanitise the data and insert it into the database. My understanding is that preg_replace is the best way to strip any characters that are not in a whit...
Hey Guys, i've got a big Problem and i was trying the whole day and did not find any Solution. Hope you can help me? I have two tables: The first one named "orders": orders_id | orders_date | ..... 1 xxxxxxxxxx 2 xxxxxxxxxx 3 xxxxxxxxxx The second is "orders_history": orders_id | order_status_id | dat...
I'm try to create a foreign key between tables by using MySQL Workbench. But I don't know why I can't tick the checkbox to select a field in order to map with another field in another table. Maybe it require both field has the same type (and other conditions??) So can you tell me the criteria to create relationship using foreign key and ...
Hi, I'm working on the Head First PHP and MySql book, and I have this problem with a date/timestamp. I'm going to make an URL with a date as a GET parameter. But when the date is returned from the database (type of timestamp), there is a white space betwen the date and the time, so the URL breaks. How can I format the date to get incl...
Hello! Here's my problem. I have a many-to-many table called 'user_has_personalities'. In my application, users can have many personalities, and a personality can belong to many users. The table has two integer columns, user_id and personality_id. What I need to do is get all users that have at least all of the personalities (a set of...
I'm trying to do this: $sth = $dbi->prepare('INSERT INTO table VALUES (?, ?, ?)'); $sth->execute( $var1, $var2 || 'NOW()', $var3 ); without any luck. Any ideas? ...
This query has baffled me... I've searched the web work over a day now and I have tried numerous things. I want to get the avg number of orders for every day of the week from my db. I can pull the total # with COUNT just fine. But I just can't figure out how to get the AVG of COUNT on a GROUP BY. I've tried subqueries... functions... ev...
Consider the following SQL: CREATE TABLE USER1 ( pkUSER1_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, DATE_UPDATED TIMESTAMP NULL DEFAULT NULL, NAME VARCHAR(25) NOT NULL, CONSTRAINT PRIMARY KEY (pkUSER1_ID), CONSTRAINT UNIQUE (NAME) ) ENGINE = INNODB; INSERT INTO USER1 SET NAME = 'asdf' ON DUPLICATE KEY UPDATE DATE_UPDATED...
Hey, I'm trying to add a button that when clicked will grab data from the mysql database and display it with a pagination. However, I can't seem to get it to work properly. The first main page looks like this: pagination.php: <?php include('config.php'); $per_page = 3; //Calculating no of pages $sql = "select * from explore"; $result ...