How Do I Query A Table For Specific Row?
How i get text from DB , if columm name is sloupec and line have ID #5 ?
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 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 !
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 !
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 !
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 !
How To Disable Binlog For Specific Table
For security reasons, we need a single table (containing sensible information) to be excluded from going to binlog. I have found out as much, that I can use SET SQL_LOG_BIN = {0|1} to switch logging on/off per connection, but I would prefer if I could simply exclude the table all together. The table is a ENGINE=MEMORY type table btw.
View Replies !
Can't Create/Edit/Drop Specific Table
had a table named "lines". All of a sudden this morning, I could not access this table from the cli. Everything I tried, even a simple "DESC lines;" returned: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lines' at line 1 I was however able to access and edit the table via phpmyadmin. I dropped the table via phpmyadmin, and tried re-creating it via the CLI, but get the same error messages. I also tried creating a brand new database, and creating a "lines" table in it, and I also get the same error. So I can't create table called "lines" in any db, new or old, at the moment. I may have a view involving this table, if that may cause problems, however a SHOW VIEWS also returns the above error message. Mysql Ver: 5.0.32-Debian_7etch4-log
View Replies !
Importing A CSV File To Update Specific Table
I have serveral CSV files that I collect each morning, that I would like to automate the updating of hte tables. I have been doing a litlte research, and I am not sure if mysqlimport is the correct tool I should be using. Is it possible to use mysqlimport to update a specific table in my database?
View Replies !
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
View Replies !
Multi Table Query, Grab More Than Single Row From Secondary Table
I have a query like so: PHP Code: $selectt = mysql_query("SELECT trailers.trailer_id, trailers.trailer_title, trailers.description, trailers.file_url, trailers.runtime, trailers.views, trailers.rating, trailers.tt, trailers.keyw, trailers.date, tags.snub, films.film_title, films.studio, films.keyw, films.image FROM films,tags,trailers WHERE trailers.trailer_id=tags.trailer_id AND trailers.film_id=films.film_id ORDER BY trailers.trailer_id DESC LIMIT 8",$dbh); I know this is pretty sloppy, I have multiple 'tags' per trailer ID, and I was hoping to grab all of them in a single query; maybe into an array? So that it shows the row, and then all the tags that belong to it. Possible?
View Replies !
1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly. With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B. I will do this using the Cron. But what would be the best insert query to use so that it does the process really quick. The query that I have so far is: Quote: mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1"); With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A. How can I add to the query so that it does this.
View Replies !
Query A Single Table, But Check A 2nd Table First
I have two tables. tblUsers and tblOrders. tblUsers contains the user information, userID, userName, userPassword, etc. tblOrders contains a list of all the orders placed by the users, it includes a userID column to link an order to a user. What I want to do is run a query and return a list of users who have NOT ordered anything. Because tblOrders has a userID column, I can pull up a list of users who have ordered. But I want to do the opposite.
View Replies !
Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning. current query looks like this: SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id; So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?
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 !
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 !
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 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 !
|
TRACKER
