How can I do this in MySQL? When a user creates an account on a website, I want a profile URL to be created for him/her. For example: http://www.domain.com/profile.php?id=111101, the next user's URL may be: http://www.domain.com/profile.php?id=111102 Will I have to set Auto Increment? Some of the fields I do save during the user's regi...
I'm developing a PHP web app that handles information about certain companies. So far, the DB design looks like this: CREATE TABLE `group` ( `group_id` int(10) unsigned NOT NULL auto_increment, `group_name` varchar(50) NOT NULL, PRIMARY KEY (`group_id`) ) ENGINE=InnoDB; CREATE TABLE `company` ( `company_id` int(10) unsigned NO...
Table1 F1 F2 F3 F4 ab1 bc1 de1 5 ab2 bc2 de2 6 ab3 bc3 de3 0 Table2 F21 F22 F23 5 five d 6 six e Table3 f31 ab1 ab2 ab3 select a.f1 a.f2 b.f22 from Table1 as a Table2 as b Table3 as c where a.f1 = c.f31 and a.f4 = b.f21 I am looking for result ab1 bc1 five ab2 bc2 sex ab3 bc3 "" I dont...
Using RAND() in MySQL to get a single random row out of a huge table is very slow: SELECT quote FROM quotes ORDER BY RAND() LIMIT 1 Here is an article about this issue and why this is the case. Their solution is to use two queries: SELECT COUNT(*) AS cnt FROM quotes - Use result to generate a number between 0 and COUNT(*) SELECT q...
I'd like to insert a row into table A, but only if another row in table B exists. For example something like this... IF EXISTS (SELECT * FROM B WHERE id=1) INSERT INTO A (id, value1, value2) VALUES (1, 'foo', 'bar') However that doesn't work. What will? ...
Hi there, I've got an issue with inserting/reading utf8 content from a db. All verifications I'm doing seem to point to the fact that the content in my DB should be utf8 encoded, however it seems to be latin encoded. The data are initially imported from a PHP script from the CLI. Configuration: Zend Framework Version: 1.10.5 mysql-ser...
This query is easy, but when the text contains some quotes it doesn't work. cursor.execute ("INSERT INTO text (text_key, language_id, text) VALUES ('%s', '%s', '%s')" % (key, language_id, text)) What is the best way to protect my text variable ? ...
I am not sure why I am getting this error #1062 - Duplicate entry '1' for key 1 cany any one help explain what it means. Thanks ...
Scenario: 4 users launch separate instances of the same client program (Winforms) that is connected to a database based To-Do list. The first user selects the 3rd To-Do list item. How do I update/refresh the other 3 users screens to reflect that item #3 is no longer available? My thought was a table that contains the last update d...
I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP): SELECT title, site_id, location, id, count( * ) FROM jobs GROUP BY site_id, company, title, location ...
I have three tables: stories, story_types, and comments The following query retrieves all of the records in the stories table, gets their story_types, and the number of comments associated with each story: SELECT s.id AS id, s.story_date AS datetime, s.story_content AS content, t...
how do i make an exact duplicate copy of a database in mysql? create database test1 from test ??????? if this is not possible, how do i copy a table from one database into another"? ...
insert into test.qvalues select * from qcvalues.qvalues; i would like to knwo if the above line locks the database QCVALUES ...
For example, if all entries in a particular column have the form [a-z]+[0-9]+, how can one extract just the leading letters, so that asdf123 and as3456 return 'asdf' and 'as', respectively? ...
Hi, i have a table like entryid, roomid 1 1 2 55 3 1 4 12 5 1 6 44 7 1 8 3 9 1 now I would like to delete ALL entries where roomid = 1 and keep the latest 3 from roomid = 1 left (best with just one command) so finaly entryid: 1 & 3 ...
hi all, I'm getting the following exception after my program runs for 30 minutes or so with 3cp0 as my connection pool. here's the error: [java] INFO [Timer-0] (BasicResourcePool.java:1392) - A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@eaecb09 [java] The last packet su...
I have a table of items that need to be displayed in a certain order, but that order can be changed. Items can be added at the beginning, end, or in the middle, and items can be rearranged. How can I set up the table to keep track of that order in such a way that it's easy to modify but the list can also be fetched in order with a sing...
I'm running into a really, really, really weird problem with mysql. I have a primary key, "id". It's set to auto increment. Problem is, first entry started at "3". And every new entry increases by 5, so the next entry's id is 8, the next is 13, then 18, so on. This is stupid. Why isn't it just incrementing by 1, like it should be? And w...
I have a database in which all of the tables have been generated by Java JPA/Hibernate code. I need to update several tables in my database in a fashion similar to this, UPDATE Department SET id=100000 WHERE id=0; Unfortunately this results in the error ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constr...
I'm trying to count the number of clients associated with each health clinic, and I want to show all clinics even if the number of clients is 0. My query works as expected until I throw in a WHERE clause. Here's a stripped down description of the database tables and query to get to the essence of the problem. clients table client_id |...