Increment The Specific Field By 1 In A Query
I still do the process of incrementing a specific field in a stand alone apps by
query 1: get its value
query 2: update value +1
But, i realize that this is really a bad idea when this would be used in web applications... specially the database is in another webhost....(very slow response)
i tried (using php function)
Expand|Select|Wrap|Line Numbers
View Complete Forum Thread with Replies
Related Forum Messages:
Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id. How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously? My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense). Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.
View Replies !
Sorting On Language-specific Translated Field
I have a table with a numeric field that will be translated to text, depending on language. That text is NOT in a table, just an array. Is it possible to construct a query, so that the table output can be sorted on that language specific field, using the texts that I give in the query? What I mean is that the query will take the field, translate the number to text using an array that I give, and then sort on that text, and give me the resulting records? Or is the only way to put those texts into a table?
View Replies !
Add Auto Increment Field
I'm trying to add an auto-increment field to an existing table but the system does not accept "#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key ". Bu when looking at the table I dont see any other auto-increment field. please find the exported table :
View Replies !
Auto Increment For INT Field
In my database I've 'CLIENTS' table contain my client's information's, the table have auto increment field, it is the ID field. The table contains now 6540 entry. Table type MyISAM ID field type is INT(30) it was 5 and I modify it to 30 I think I can solve problem by doing this but no way.. no solve… Problem: Now when we add new client the ID field value become automatically 2147483647, and if we try to add again new clients the ID value become same number so we can Delicate Error I try to set the AUTO_INCREMENT value to 6541 but didn't changed and keep old value.
View Replies !
Auto Increment Field And Primary Key
I have a table with a 2 column index and one of the columns is a autoincrement and primary key field. When I delete a record from this table then do another insert, the id count is now off. For instance if I delete the record with id=3, then do another insert, the id is set to 4.
View Replies !
Auto Increment Field Value From A MySQL Database
My perl code reads as follows (simplified here for brevity): # build up the query my $q = "INSERT INTO `$db::tablename` VALUES "; #note that the first field is an auto increment int $q .= "(null, ?, ?, ?, ?, ?)"; # Prepare the query. $db::sth = $db::dbh->prepare($q); # execute the query. $db::sth->execute( $mydata1, $mydata2, $mydata3, $mydata4, $mydata5); The first field in the table is an autoincrement (int) field How do I pull back the autoincrement field value after the insert I'm trying to avoid doing a post insert query to fetch the max value if possible as this is an expensive way to do this, but may be my only option.
View Replies !
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
Question About A Specific Query
imagine I've got user that can have visual preference (enabled or disabled) saved on a table. Before a user make change of a preference, there is no row associate to that user and by default, the preference is enabled. Now, is it possible to do a query that will output user that have preference set to enabled (those who have update their preference) while also outputing those who have no row in the table preference? I know it may be not super clear, but I need to know before working on that script. So here a sample to make sure it is all clear: TABLE USER (id - name - email) 1 - NameA - a@a.com 2 - NameB - b@b.com 3 - NameC - c@c.com TABLE PREFERENCE (id - label) 1 - Show warning 2 - Show help TABLE USER_PREFERENCE (user_id - preference_id - status) 1 - 1 - Disable 2 - 1 - Enable So if I want to output user email that show warning, it should give me: b@b.com, c@c.com My problem so far is, how to retreive user that have a user_preference row enabled, and user that have no user_preference row in the same query!
View Replies !
MYSQL Could Not Fill Emptry String In Auto Increment Field
I made a database and it running well with PHP. Then I made a backup my database. I reinstall my computer for some reasons. After it, I begin faced a problem. My MySQL could not fill data from PHP site page. So far known problem by me that I must not fill anything, even empty string (“”) in Auto Increment field. Code:
View Replies !
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
View Replies !
Sql Query To Exclude Specific Data From Resultset
I have got two tables,one customer with fields cust_id,email and another with field cust_id,domname,expirydate. i have this query to combine these two tables to give me email and the expirydate as $sql = "SELECT l.email,s.expirydate from customer l, domain s where l.cust_id = s.cust_id "; I need to exclude those expirydates whose domname contains .np in the text.
View Replies !
MySQL Admin - Lost Thread On Specific Like Query?
mySQL win32 (4.0.22) I am using mySQL Control Admin and seeing a lockup on a thread specific to a like query. select * from table1 where field1 like 'http://h%' limit 100 This request will sit (thread) forever and never return any records. (status : sending...) However : select * from table1 where field1 like 'http://h%' limit 100 returns records immediately, no problems... I have already run : /mysql/bin/myisamchk --sort-recover dbtable1, no errors, no problems... How can I tell what this thread is really doing or if any additional problems exist with this table using myisamchk?
View Replies !
Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following: ----------------------------------- SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC while(FetchRowAssoc()) { SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id echo cat_it, cat_name, itemcount, blah... etc. } ------------------------------------ I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table. Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category? "SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.
View Replies !
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
View Replies !
Query That Duplicate Row And Increment Date
I have a table EVENT id date duration 1 2006-01-01 3 2 2006-04-23 5 3 2006-10-01 0 is possible made a query that do it? id date 1 2006-01-01 1 2006-01-02 1 2006-01-03 1 2006-01-04 2 2006-04-23 2 2006-04-24 2 2006-04-25 2 2006-04-26 2 2006-04-27 2 2006-04-28 3 2006-10-01
View Replies !
Increment Of Auto Increment Is Not Consistent
I have a table with one the the column set as auto increment. MY storage engine is innodb. The problem is the my increment of the auto increment is not consistent at all it was growing from 1 till 6 then suddenly it went to 802 ? Can some one guide how to make sure its increment is one by one ? Thanks.
View Replies !
Increment Column Without Auto Increment
What's the fastest way I can use an INSERT statement to insert a new record including an 'ID' which is one greater than the current highest ID in the table. Is this possible without first doing a SELECT query? I'll be using php too btw, so if there's a way to do this in php, then I'm open to such suggestions also.
View Replies !
Query For Getting Top 10 In A Particular Field
I am trying to write a query which can display top 10 entries in a particular field with the count. for eg: If i have a field "Month" which has entries Jan thru Dec. There are about 1000 rows. Now, i want to find the top 10 months with the count say, Month | Count Jan | 20 Feb | 13 Mar | 12 so on... Has anyone worked on this?
View Replies !
Using A Field From One Db In A Query Of A 2nd Db
I have a page that has an address something like: [someserver.com?SID=23] SID is the 'SID' field of the 'subject' table in 'database1'. I use the SID in a query to pull out some data and display from database1. Within the subject.php page, I want to do a MYSQL query of 'database2' and display some of the data from it. One of the fields is 'CID' in the 'category' table. I don't want to include any data from 'database1' in this query, but since database1.subject.SID is equal to database2.category.CID, I figure I can use it to identify the 'category' in 'database2'. See Query 2 below. I realize that the problem is the 'WHERE c.CID=" . $_GET['SID'] . I don't know how to pull in the SID value from the URL to use in Query 2. In the first query, I did this: Query 1 (database1: tables- subject) mysql_select_db("database1")or die("Could not connect : " . mysql_error()); $qry1 = "SELECT SID from subject"; $results = mysql_query($qry1) or die("Query failed : " . mysql_error()); $SID = $_GET['SID']; I then echoed some information to display from database1, using SID as the identifier. Query 2: database2 (tables: item, bridge, category) mysql_select_db("database2")or die("Could not connect : " . mysql_error()); $qry = "SELECT title,url from item i left join bridge b on i.ID=b.ID left join category c on b.CID=c.CID WHERE c.CID=" . $_GET['SID'] . "order by title"; I hope that is clear enough and that someone can give me some tips on how to do this. It is unclear to how to data use from previous queries and/or the original GET Request in the URL to do another query.
View Replies !
Field In Multiple DBs Using Sql Query
I have 60 databases with identical structures and I'd like to change a field value for the same field in each DB. At the moment, I've crafted this code: UPDATE `jos_users` SET `username` = 'newName' WHERE `jos_users`.`name` = "Administrator" ; How can I modify this code so that each database will inherit this new username value? I've been trying to use SELECT but can't quite get it working.
View Replies !
Query For A Memo Field
I want to select records that have a defined string in a memo field. Could you please tell me how to do that. For example, I want to search for the records that have a string "playing game" in a field of memo (text field).
View Replies !
Field Format Query
I have had a program written for my company (we fundraise money for charity). Essentially it records donor information (names, addresses, phones, and donations). Part of the program's design was to improve my telemarketers collection of information whilst on the phone (ie. standardising the format of the fields for output/printing etc). So... my dilema is that the SURNAME field has the following rule applying Make first letter Uppercase then all following letters lowercase.....
View Replies !
Query Field For Underscore String?
I have a field that contains an underscore. How can I do a query for the underscore since I just found that underscore really doesn't find underscore it is a wildcard in mysql. I need to be able to query for the actual underscore. For example. Select Title where Title LIKE '10_%' doesn't give me what I want.....
View Replies !
Query 3 Fields In Same Table As If They Were One Field
I have a very simple test table which I am using to try and get the query right: id | col1 | col2 | col3 | 1 | 3 | 2 | 3 | 2 | 4 | 3 | 1 | 3 | 2 | 1 | 1 | 4 | 3 | 2 | 4 | 5 | 3 | 1 | 3 | 6 | 3 | 1 | 3 | So the table contains and id field and 3 integer fields - each of the integer fields contains an integer between 1 and 4. What I want to achieve is the value of the most frequently occuring entry across the 3 integer fields, as though all 3 fields were actually one - in my example, this would be 3 as there ar 8 occurenecs. I will also require the lowest occuring value also, in this case it is 4. I cannot work out to query all 3 columns simultaneously to obtain my results - I have been looking at joins but cannot work out how to join within the same table
View Replies !
Update Query For Part Of A Field
I need to update part of a field in around 3000 rows of a table. In each case the field in question has text already in it but I need to add an extra bit of text to the start of each one. I've had a look on the web and have found examples that show you how to replace text in part of a field but haven't found anything about adding new text.
View Replies !
IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.
View Replies !
How To Form Query When Field Name Has Spaces?
I'm doing a database for a dog-rescue group, and their info is being written by Front Page to a CSV file. Finally got an import CSV to MySQL to work. But all 160 fields have field names with numbers, spaces, apostrophes, question marks, etc. I really don't want to hack into the source program to make the fields "nice" for MySQL if I can avoid it. Here's a sample ... SELECT * FROM appforms WHERE appforms.[18_Sex_of_ Dog_you_want?] LIKE 'female'; The WHERE clause needs something ... and I've tried all kinds of variations of quotes, etc. Don't know if the brackets are right either. Easy enough to test with the Query Browser, but I've run out of ideas!
View Replies !
Query: It's Possible To Retrieve Only Numbers From A Field?
How to retrieve only numbers from a field? select firstname, address from `acme`.`anytable` where idanytable=100; +-----------+--------------------+ | firstname | address | +-----------+--------------------+ | ERICKA | RUA B NR 50 AP 202 | +-----------+--------------------+ select firstname, onlynumbers(address) from `acme`.`anytable` where idanytable=100; +-----------+---------+ | firstname | address | +-----------+---------+ | ERICKA | 50202 | +-----------+---------+
View Replies !
Date Field In Mysql Query
Just after a bit of advice or guidance on the best way to go about an issue i have.. At present i have a simple user form with drop down lists for the user to enter a date. (eg. 1,2,3, - Jan, feb, mar, - 2006,2007 etc..) as -$day -$month- $year... All other input areas on this form are text and will go in a Varchar fields so will not be an issue... My query is what is the best way to handle this date, and how..? I undestand Mysql needs the date as yyyy mm dd to be in a date field.. Im just really unsure of how to put my format date in to a date field on the table and query it out so it displays in my format again.. Ive done quite of bit of searching for this and seen many suggestions including using INT and Varchar fields so i am really confused as the best and correct way to go about this..
View Replies !
Query Returns Integer As Character Field
Not sure where I've gone wrong here but I have a query selects records via a left join and I'm atmysqlting to set a column in the query as integer and default it value to zero. I'm also executing this query through the 3.51 odbc connector.... I thought I'd mention this but don't think it has anything to do with the problem. select concat(lastname,' ',firstname) as custname,'' as worktype,'' as appointment,hphone as phone,custid,0 as booked_id,0 as booked_by,'' as booked_stamp from customers left join bookings on bookings.booked_by=custid where booked_by is null order by lastname,firstname asc When the query executes the records are returned properly but the booked_id and booked_by fields are being set as character type when they should both be integer.
View Replies !
Can You Use A Boolean Field Within A Table To Evaluate A Query?
One quick question. I want to build a query like this: SELECT * FROM <table> WHERE <condition> Where <condition> is a boolean field in the table being accessed. I assumed in the construction of the query that simply placing a boolean-type field in the query after WHERE would cause the MySQL engine to only return the rows where the field is TRUE. Please let me know if this is correct, and will work.
View Replies !
|
TRACKER
