Customers At A Specific Time
I have a table called "HISTORY" that contains
historyid = integer that holds the autoinc
custid = integer that holds the customer id
startdate = a datetime that customer came to the shop
stopdate = a datetime that customer leave the shop
What I want to know is how many customers where at the shop at a specific time, example
2005-10-10 12:00:00
I want the number of people that were in the shop at this specific time.
PS: Take for granted that day may change, for example someone can come (Startdate) at 2005-10-10 23:58:55 and leave at 2005-10-11 02:11:49
View Complete Forum Thread with Replies
Related Forum Messages:
Select Inactive Customers
I have a customers table and an orders table. I want to select customers that have not placed an order in the last 6 months. So far, I have the following query:Code: SELECT cf_customers.* FROM cf_customers, cf_orders WHERE cf_orders.cust_id = cf_customers.cust_id AND cf_orders.date<$six_months_ago But that query selects any customer that has placed an order more than six months ago, regardless of whether they've placed an order in the last six months.
View Replies !
Restricted Access For Customers
I have an online flat file database with Laboratory data. I want to allow a clients application to access the flat file data (I.e SQL statement) but restrict his options as to what he can search. E.g. when his application does 'select * from db' he only gets results out where 'clientname='abb' Or allow him only to search all data where the sample date is greater than a certain date.
View Replies !
Huge Database With 800 Customers / 3.500 Products
I need for a webshopproject all customers with all products in one file. I have already both informations in two different ones. Pls. find below the relevant info. product ProductID|pricegroup| customer knr|pricegroup|%calc|price It is possible that one product is related to 800 customers with 800 different prices. In my thinking the new DB should look like pricefile ProductID|knr|pricegroup|%calc|price.
View Replies !
Graph Of Development Number Of Customers
Ok i have a table with the customers and their registration time. I cant figure out the sql to create a graph which: displays the number of registered customers per day in the past 30 days. I would know how to get it for one day, but have no idea how to do this effectively for the past 30 days without running 30 queries. How do i solve this?
View Replies !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Minutes + TIME = TIME Function
I need some way to convert minutes (e.g. 80 mins) to SQL TIME (e.g. 01:20:00) in order to add it to another TIME var. Is there any way to do this? I really dont want to convert my whole db. Basically, I want to do: e.g. 80 + 10:00:00 = 11:20:00.
View Replies !
How To Retrieve Mean Time Between Time Stamps
I have a table that stores failure information of external application. The time stamp of every failure event is stored. Now I'd like to make some system performance & reliability statistics calculations based on time stamps in certain recordset; mean time between failure MTBF, down time DT, etc. Example: 2007-09-27 12:23:52 2007-09-27 08:50:23 2007-09-26 23:31:34 2007-09-26 21:45:33 Here the times between are (hours:mins): 3:33, 9:18, 1:46 and MTBF=4:52 Any effective possibility to make this example within a single query - or a procedure is needed?
View Replies !
Time Format Military Time
I know the time format it HH:MM:SS in mysql but can I input military time? reason I can is that I want to later extract that information for calculations and military time is easier to work with.
View Replies !
Date-time Overlaps Another Time
had a time question in mysql... i have a start and end time for a meeting table and i am comparing whether a new meeting conflicts with another meeting in same room on the same date. i was wondering if i could check whether the 'date-time duration' in anyway overlaps another meetings date-time duration. visualized below... ............|____compareToThisMeeting__| .....|_________meetingNewOverlaps__________| i was doing something dumb before i realized i needed soemthing more complex (checking whether the start time of new meeting was 'BETWEEN' the start and end times of a meeting and whether the end time time is as well but i forgot the duration/middle value hehe). (using asp) and just returning booleans with a mySQL SELECT statement is what i am looking for like... //within a loop in asp for meetings on same date/room "SELECT '"+ year+"-"+month +"-"+day +" " + startHourString +":" + startMinuteString +":00''"+ " BETWEEN '"+ thisMeetingStart +"' AND '"+ thisMeetingEnd +"'"; (this doesnt cover the 'middle' overlap areas though)
View Replies !
How To Log Specific Queries
I've got this DB. Every few minutes new data comes in and is inserted. Now and then for some reason duplicate data comes through - not a big problem - unique key. However I'd like to log the insert failures, due to dupes, but I don't want to log all of the good inserts also.
View Replies !
Specific Table
Hello MySQL gurus, This is the command : mysqldump -u$user -p$pass $database >$backup Can you exclude a specific table, say table UnNecessaryAndHugeTable, from the dumping process? that is mysqldump all tables except table UnNecessaryAndHugeTable
View Replies !
Changing The Value In A Specific Row
i have a forum that i am trying to add a username kind of thing to. what i have done so far is to set up a table that has three coloumns in it, USERNAME, PASSWORD, and STAT, of which stat is a boolean value that i use to determine if they are logged in or not (what i intend for it to be), and the other two are pretty self explanatory. my question is is there a way for me to get into this specific row and change the value stored inside STAT, or is my only choice to completely delete this row then re-enter it into the table everytime stat has to change? thanks guys. btw good forum you have here
View Replies !
Copy Specific Row
I have a database that host a "user login data," and i have built a new script to expanse the service in other computer's database. I want to know are there any methods to copy the user data row from the "user login data" to the new computer's database that i have just built? I am not talking doing this manually by hand, but doing this like replication a specific of row.
View Replies !
Getting Specific Results
my db looks like this: username - skill1 - skill2 - skill3 John - warrior - thief - warrior How can i only output the fields that are warrior? Ive tried this but it dident work: SELECT skillsM1, skillsM2, skillsM3 FROM ugd HAVING skillsM1 AND skillsM2 AND skillsM3 = 'warrior' Or is it necesary for me to change my db structure?
View Replies !
Specific Data
Ive been struggling with this for some time, wonder if someone can help, I have a database table column that has some information within it that I do not want, however there are over 100,000 records and it'll take some time to update them all - I'm trying to find the sql command to update the info currently I have: PHP Code: REPLACE * from table WHERE `column` LIKE '%data_not_needed%' I want to replace it with nothing so it simply takes the not require word / data out from that column - what else can I add into that sql command to show this?
View Replies !
Specific Rows
When I was creating a database I misassigned some rows and now I just want to change those 4 rows but can't figure out how to do it. I tried using this command Code: update variables set bl_id=16 where bl_id in (SELECT bl_id from variables where var_id between 379 and 382)
View Replies !
Specific Search
I'm creating a PHP application in which I need to do a specific search that I'm not being able to formulate it. On my database, I have a table that register different organizations. Each organization has a primary key called org_id, which is an integer auto_increment. Well let's say I want to create a search in which I want only specific organizations to show up. Let's say I have 100 organizations registered, and I want to search for organizations with org_id 1, 4, 7, 15, 33, 45 and 85. Well, how do I write a MySQL code that will return these organizations, only these organizations, nothing else?
View Replies !
Specific Join
I have a table with roughly 50 million records that contains user data for my company's sites. Two of the columns in the table are varchar columns that show the url a user is coming from (referurl) and the url they're going to (url). So if you look at user's session, you can see the progression they made while navigating through our site. Each user's session is identified by a number, which is indexed. I recently created a companion table that assigns a number to both the url (url_id) and the referring url (ref_id) for each one of the records in the main table (a table matching those two values is elsewhere in the database. These two tables have the same auto-incrementing unique id number (uid), and the same number of records. The two non-unique numerical fields in the companion table have a multiple index on them. Previously, I have been using the mysqldb module for python to do a very particular kind of search. Basically what I want to do is search for every record matching a url that I input, then find the url of the first site that user came in on. What I would do is SELECT the records that matched my search (I was matching them on the url field I mentioned above), then have python submit a query to get the sessions from each one of those results. Python would then find the url from those results that had the highest id number and matched one of our 15 or so landing pages in the url field (which would make it the most recent landing that that user made to our site). It always seemed to me that this is something that could be accomplished more quickly by a more complicated MySQL query that uses the newly created companion table instead of matching the string of the url. I've come up with this query to just get the results I want using the companion table (table names changed to protect the innocent): ......
View Replies !
Specific Date
My registration_date column has to be a DATETIME type and I have to zero in on a specific date. SELECT registration_date FROM reserved WHERE ( registration_date >= '2005-18-06 00:00:00' ) AND ( registration_date <= '2005-18-06 23:59:59' ) Is there a more streamlined way I can extract just the date from a DATETIME type, or is this the best or only approach
View Replies !
Updating A Specific Row
I have a question about updating a row in a table. This table keeps a record of when a user logs in/out with username, time of login/logout and ip-adress. When a user logs in, the logouttime is of course left blank. When that user logs out I need to update that row with the logout time. I know I need to use "UPDATE log SET timeLoggedOut = '$timeLoggedOut'" I have a session variable with the identity of the user currently logged in. But how do I update only the last row in the table containing the username of the user logging out? If I only use WHERE username='$userloggedin' it will update every row containing that username..... EDIT: I guess I could use $sql = "SELECT MAX(loginID) from log WHERE username = '$userloggedin'" to identify the row I need to change, right? But how do I return that information from the db so that I can put it in a php variable?
View Replies !
Retrieving Specific Records
I am trying to establish (using a straight MySQL query in version 4.0.18) the level of qualifications someone has, i.e. degree, postgraduate degree or other. To simplify things, I need to use only two tables, namely: master table, containing: staffno (Primary Key); forename; surname; psnquals table, containing: psnqualid (Primary Key); staffno (Foreign Key); levelid (where 1 = degree, 2 = postgraduate degree, 3 = other) In the followning query, staffno 1 has three qualification levels, 1, 2, 3. The query finds the first matching record (where cdelevelid = 1) and then stops, making it appear as if the employee had no postgraduate qualification. One person can have one or all of the above qualifications. I need to write a query that shows if a person has one, two or all of the above. I tried using the following query, but it seems only to look at the first psnqualid record it finds and then stops: SELECT m1.staffno, m1.forename, m1.surname, CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE "O" FROM `master` m1 INNER JOIN psnquals p1 ON m1.staffno = p1.staffno I also tried using an IF statement, but this also seemed to stop after finding the first matching record in the psnquals table.
View Replies !
Privileges On Specific Fields In A DB
How do I make a table with fields again, say, name, age, address, wife etc. that I want people to be able to look at (with any client like MysqlCC etc). but only be able to modify certain fields, eg. wife (this includes deleting the field from the database, maybe) - the name, age etc. must be left intact, ie. not changeable by anyone.
View Replies !
Order By Specific Values
Is it possible to order by specific values in a column. For example a column may contain values like N, N1, N2, S, S1, S2, R, T. I want to order by in such a way that all the records with N, N1, or N2 in that specific column will show up on the top. All the records with S1, S2 values will show up next and finally all records with R and T values in that specific column will show up. Please give me the select syntax.
View Replies !
Get Specific Table Names
I have a couple of tables and I want to make a select statement that will show them to me. I think with an example is easier to explain. I have some tables users members clients cdhonda cdhonda_values hrtoyota hrtoyota_values emails prchevy prchevy_values I would like to make a query to look at all the tables, and the ones that have the "_values" to give me the part before the "_". For this database for example, the query would give me the resutl "cdhonda hrtoyota prchevy"
View Replies !
Rated By A Specific User
I'll ask in this thread since the problem is somewhat similar. What I have is one table named products with fields product_id and product_name one table named ratings with product_id, user_id, rating and one table named users with user_id and username. Ratings will contain entries added by users and what I need to do is select all products from the first table which have not been rated yet by a certain users identified by user_id.
View Replies !
Syncronising Specific Tables
I would like to ask you a question regarding a problem that I experience. I have to syncronize two databases, one MySQL and one MS-SQL. Unfortunately, I am not aware of MS-SQL databse but a friend of mine is. So, he told me that he can export data to a mySQL database, which will contain three diverse tables: one with new products added, one with the deleted products, and one with an update of the current products. Is there a way the initial (web-based) MySQL Database to read the exported database and make the necessary adjustments related to the each table (table 1: adding, table 2: deleting, table 3: updating). How can this procedure be done?
View Replies !
Updating On A Specific Count
I tried searching for this, but to no avail. I need to update a "valid" bit in my table (set it to 0), when the total number of rows that have been grouped by column bin_id is less than 5. Ie: bin_id valid 0 1 0 1 1 1 1 1 1 1 2 1 2 1 2 1 2 1 2 1 3 1 here, all rows where the number of rows contained in the respective bin_id is less than 5 are set to invalid. (in this case, there are 5 rows which have bin_id = 2, so valid remains 1. for the rest, there are too few rows of the respective bin_id, so i need to set valid to 0.
View Replies !
WHERE :: Specific Letter Of The Alphabet
How would I formulate a SQL statement where I want the first letter of a field to be equal to a specific letter of the alphabet? IE: I have the field lastName and I want to select all the records that begin with the letter 'a'.
View Replies !
Retrieving The Last Value Of A Specific Column
I need help in retrieving the last value that is entered in my table for a specific column An example to clarify what I want to do: I am collecting data automatically and the values of which are being inserted into a database. I want to retrieve for example the last row entered for a column named e.g. current_number. I am not sure how this can be done in SQL. One idea I had is to use the last_insert_id(), but I am not so sure how to use it as an sql query. Is there another way to get the the last value of a specific column in a table?
View Replies !
Selecting And Displaying Specific Row
I wonder if there is a way to select a specific row in a table and than displaying that row. For example i would like to dispaly results starting from the second oldest entry in the table ... thus omiting the latest entry. Can anyone point me in the right direction?
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 !
Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?
View Replies !
Only Selecting Specific Rows
Fields: studentID, reviewID, date I want to get all rows in a certain date range where there are at least 3 rows for a given studentID. In other words... Case 1: student has written 2 or fewer reviews during the date range. Don't retrieve these rows. Case 2: student has written 3 or more reviews during the date range. Retrieve ALL of their reviews during that time. So if they wrote 5 reviews, the query will have 5 rows for their studentID. Any ideas for this query? So far I got the date range to work using this. I'm struggling with the 3+ reviews part. SELECT `studentID`, `reviewID`, `date` FROM `review` WHERE `date` BETWEEN '2007-10-21' AND '2007-11-09' ORDER by studentID, `date` Any ideas?
View Replies !
Creating Indexes For Specific SQL
I'm trying to optimize a SQL statement that shows up in the slow query log several thousand times. The statement is the same but the values of 'XXX' differ. SELECT group_member.id, <many more fields here>... FROM group_member left outer join group_node on group_member.group_node_id=group_node.id left outer join domain on group_member.domain_id=domain.id WHERE group_member.domain_id = XXX and group_member.object_id = XXX and group_member.class_type = 'XXX'; To optimize this, should I create one compound index like this? ALTER TABLE group_member add index group_member_idx (`domain_id`,`object_id`,`class_type`); If I'm reading various articles correctly, adding indexes to domain_id, object_id, or class_type separately won't benefit this sql statement.
View Replies !
Can You Run MySQL On A Specific Cpu Core?
I'm running Windows 2003 sp2 web edition MySQL 5.0.51b I have a dual core machine but would like to restrict the MySQL server to run on only one of the cores. I've search the help, the mqsql.exe --verbose --help details Checked the entries in the my.ini googled (found one page but the site is down) I have the FireDaemon service tool installed so I could try removing all history of the existing service and create a fire daemon version (where I can specify the core). But I was hoping thre would be an easier way?
View Replies !
Generate ID Of A Specific Format
I am writing a Java web app, where I need to auto generate & increment the primary key. It should be of the format ABC<auto-increment-serial><month><year>, so it should look like ABC-001-072008. I know how use auto-increment id in MySql, but I am stumped on where to look for a similiar occurance. A search on this forum did not return a solution.
View Replies !
Do Not Group By A Specific Column
I got a question how to set up a SQL-Statement... I got 2 fields. One has ids saved (not unique!) and one got answers. |uid|answer| |1 |yes | |2 |yes | |1 |no | |1 |no | How do I count how often EACH uid got which answer? SELECT *, count(*) as c FROM table GROUP BY answer That is what I got till now - but the output here is: |uid|answer|c| |1 |yes |2| |1 |no |2| but I want: |uid|answer|c| |1 |yes |1| |2 |yes |1| |1 |no |2|
View Replies !
Populating A Specific Column
Is there a method of populating a specific column only? I've created the table and since then I've had to add another column. The information for this column is ready, but I don't want to enter 170 lines individually. I can't seem to find the proper code for doing it. I'm familiar with loading text files, and I've queried the one column I need, but I can't populate it.
View Replies !
Creating A Foreign Key With Specific Name
In my java program I want to programmatically convert some data from xml to a MySQL database. A couple of my tables have circular foreign key dependencies, so I'm unable to simply empty existing tables sequentially before the insertion of new data. I would like to drop certain foreign keys, then after data conversion, reinstate them. I'm able to drop the foreign keys. But later, when I try: alter table mytable add foreign key foreignkeyHello (mykey) references myothertable (myotherkey) it creates the foreign key properly, but the new foreign key does not have the name "foreignkeyHello". The foreign key's name is something like mytable_fk_1. I'm sure the constraint would work fine but since I need to reference that foreign key in code later, I need for the name to be "foreignkeyHello". How can I do that?
View Replies !
Deleting A Specific Record
Im working on a site witha Mysql backend for news and stuff and am trying to make it so that I can delete specific news entrys if need be. I have it set up so that in the admin section it generates a link next to each record like the following for its own idhttp://localhost/includes/deletenews.php?id=1 which points to the deletenews.php that looks like this Code:
View Replies !
|
TRACKER
