How To Query For Multiple Records At The Same Time
Basically let us assume i have a table (table1) with column (column1). I want to select only the records that have column1='x' or 'y' or 'z'. Is it possible to create a standard query that will do that for any number of records?
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
Calculate Time Between Records
I have been trying to do this for days and would appreciate some help! This database was designed by someone else and i have been lumped with writing some reports! I have a table that stores GPS information from vehicles. I am trying to write a report that will show a vehicles "idle time" Below is the table Structure.....
View Replies !
Time Difference Between Records From Two Tables
I have two tables with data from two separate data loggers. The timestamps of the records are not perfectly synchronised (eg. 2005-07-11 22:50:00 and 2005-07-11 22:49:58). All data must be joined to one table and all records that are out of sync by more then 30 seconds must be rejected. I tried somethig like this: Code:
View Replies !
Sum Of Time In Multiple Rows
I have a table Code: TASKS{ started => time column, ended => time column } Now, for every task I do I make a new record (e.g.) saying I started at 11:00 and ended in 11:15 Now, I can find how long I worked PER row (e.g. in past example the following would return 15 min) Code: select subtime(ended, started) as time from tasks How do I sum all the rows, not just one?
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
UPDATING Multiple MySQL Tables In The Same Time
I have to perform a query and I don't know how to do it. It is a Joomla 1.5 guery and here is it: $_CB_database->setQuery(" UPDATE #__comprofiler SET avatar = " . $_CB_database->Quote( 'gallery/' . $newAvatar ) . ", avatarapproved=1, lastupdatedate = " . $_CB_database->Quote( date('Y-m-d H:i:s') ) . " WHERE id = " . (int) $row->id . "; UPDATE #__mycom SET avatar = " . $_CB_database->Quote( 'gallery/' . $newAvatar ) . " WHERE user_id = " . (int) $row->id .";"); if( ! $_CB_database->query() ) { $msg = The query hasn't been performed; }
View Replies !
1 Record To Multiple Records
I'm looking for an efficient way of doing the following... table1 - groupName, firstGuy, secondGuy, thirdGuy,... sample - 'Red', 2, 6, 3,..... table2 - guyId, firstName, lastName sample - 2, 'Jim', 'Smith' ; 3, 'Bill', 'Adams' ; 6, 'Tim', 'Jones' If I queried groupName = 'Red', how do I get the result to be the names from table2. Subqueries?result to contain: red, Jim Smith, Bill Adams, Tim Jones I hope this is clear.
View Replies !
Multiple Records Single Value
I have been developing a realtor intranet system. I am currently writing a php script that deals with 2 tables "property" and "photos" property table: id | address | etc.. ---------------------------- 1 56 My Road 2 389 Your Street photo table: id | file_id | default | property_id --------------------------------------- 1 3434... 0 1 2 343c... 1 1 3 3udu... 0 1 I have added a field "default" in the "photos" table. The purpose of this is to set the default photo for the property listing. My problem is: I want to perform a single update query as I currently only know how to perform this operation with two. eg: $sql1 = "UPDATE `photos` SET `default` = 0 WHERE `property_id` LIKE {$var}"; $sql2 = "UPDATE `photos` SET `default` = 1 WHERE `file_id` LIKE {$var}"; Any way of doing the above 2 queries in a single query?
View Replies !
Obtaining Multiple Records
I am having a lot of difficulty in trying to access multiple rows in a table. The scenario goes like this: 'config' Table conf_id (primary key) storage_id cont_title conf_descript sort_order visible Each of the 'conf_id' is different. The table above as the information I want in it under the mentioned fields. I have set up 2 tables called 'multiconfig' containing: multiconfig_id (primary key) storage_id (index) And 'storage' containing: storage_id (primary key) field1 field2 field3 field4 field5 The idea is to link the 'storage' table to the 'config' table via 'multiconfig'.
View Replies !
Multiple Records As Columns
I have a table of Sessions and a table of Speakers. Each Session could have several Speakers and each Speaker could speak on several Sessions. I have a link table containing just SessionID's and SpeakerID's. All this is working fine on a successful website. I am now trying to write a query that will return records for each Session with each of the Speakers' details for that Session. Each record could have a different number of fields according to how many Speakers were assigned to that Session - this is the bit that makes it seem difficult.
View Replies !
Instert Multiple Records
What would be the best way to insert multiple(20-50) records at once with php. Should i just run multiple queries? or is there a way to do it in one query?
View Replies !
Deleting Records From Multiple Tables
i am using dreamweaver, php mysql. i have 4 tables and want to delete a record from more than one table , all of them are related. For example client table, product , delivery etc. if client A is removed i want to remove all records of client A from product and delivery.
View Replies !
Multiple Insert, Unique Records Only
I want to insert lets say 100 records into a db that looks like the following: ID auto increment IPAddress varchar The query I am running inserts all 100 with one trip to the DB using php. What happens If I only want Now lets say I do not want more than 1 IP in this database. Surely I don't have to search the whole database looking to see if there is a match for IPAddress.
View Replies !
Gathering/Updating Multiple Records
I'm trying to make my PHP/MYSQL database display several search results before updating them to the inventory database. I can now type the stock_code and the part is displayed from the inventory, but I now want to immediately enter another stock_code and have the page show this result and the previous one, and so on for several searches. I can then enter how many of each part I have used then press 'submit' to update the inventory.
View Replies !
Selecting Records Containing Multiple Values
I want to query a database for records with which the field 'network_letter' contains G,R,M, and Q and display those results back. Is there an easier way than doing the following: $query1 = mysql_query("SELECT * FROM global_pops WHERE network_letter='G'"); $query2 = mysql_query("SELECT * FROM global_pops WHERE network_letter='R'"); $query3 = mysql_query("SELECT * FROM global_pops WHERE network_letter='M'"); $query4 = mysql_query("SELECT * FROM global_pops WHERE network_letter='Q'"); and then merging those results into one variable and printing them?
View Replies !
Inserting Multiple Records Using Checkboxes
I'm working through my fist PHP/mySQL project, and am getting on OK with it. The database structure is as below, with three main tables (Candidates, Vacancies and Profiles) and two lookup tables (CandidateProfiles and VacancyProfiles) : Candidates --------------- Candidate_ID (autonumber) Name (text) ... 1, Iain 2, Fi 3, Rob Vacancies -------------- Vacancy_ID (autonumber) Vacancy (text) ... 1, Cartographer 2, Gardener 3, Occupational therapist 4, Web designer 5, Recruitment manager Profiles ----------- Profile_ID (autonumber) Profile (text) ... 1, Map making 2, Web design 3, Gardening 4, Hand therapy 5, Recruitment 6, Aviation 7, Sport 8, Travel VacancyProfiles ---------------------- Vacancy_ID (number) Profile_ID (number) ... 1,1 1,2 4,2 2,3 3,4 5,5 5,6 CandidateProfiles -------------------- Candidate_ID (number) Vacancy_ID (number) ... 1,1 1,2 1,7 1,8 2,3 2,4 2,8 3,5 3,6 3,7 and from there created two queries CandidatesQuery -------------------- SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID, Profiles.Profile FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON Profiles.Profile_ID = CandidateProfiles.ProfileID; 1, Iain, 1, Map making 1, Iain, 2, Web design 1, Iain, 7, Sport 1, Iain, 8, Travel 2, Fi, 3, Gardening 2, Fi, 4, Hand therapy 2, Fi, 8, Travel 3, Rob, 5, Recruitment 3, Rob, 6, Aviation 3, Rob, 7, Sport and Vacancies_Query ------------------- SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID, Profiles.Profile FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID = VacancyProfiles.ProfileID; ... 1, Cartographer, 1, Map making 1, Cartographer, Web design 2, Gardener, 3, Gardening 3, Occupational therapist, 4, Hand therapy 4, Web designer, 2, Web design 5, Recruitment manager, 5, Recruitment 5, Recruitment manager, 6, Aviation What i'm a bit unsure about is how my insert / delete pages should be created. (I'm using Dreamweaver). The absolute ideal would be to have a page whereby new Candidates could be entered, along with any job Profiles, ideally by clicking checkboxes, that would then insert the data into Candidates table, and also populate the CandidateProfiles table. I guess something like : if checkbox1 is checked insert current(new) CandidateID into CandidateProfiles.CandidateID and 1 into CandidateProfiles.ProfileID AND if checkbox2 is checked insert current(new) CandidateID into CandidateProfiles.CandidateID and 2 into CandidateProfiles.ProfileID etc If that makes sense, and anyone can point me in the right direction that would be great. Or, if thats cobblers, any direction - but hopefully you get the drift - I'm basically after the most efficient way of entering / attaching any appropriate Profiles to Candidates on the insert / edit web pages.
View Replies !
Counting Records From Multiple Tables
Got a question for you that is really stumping me. I have 3 tables job_jobs ------------ job_id job_name . . . job_cats ------------- cat_id cat_name . . . job_jobsToCats ------------- jtc_id job_id cat_id *************** ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get accounting (12) admin(2) etc etc
View Replies !
Most Effecient Way To Insert Multiple Records
i have some areas on my site where i'm inserting hundreds of records. currently i'm looping through each record and performing the insert. something like... PHP Code: foreach ($records as $record) {Â Â Â Â Â // insert into mysql} is there a more efficient way of doing this? can i not send all of the records at once to mysql? like a transaction-type thing?
View Replies !
Select Where Multiple Joined Records Match
I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. Here's a simplified version of my problem. I have two tables, resources and goals. resources table: ID TITLE 1 civil war women 2 bunnies on the plain 3 North Carolina and WWII 4 geodesic domes goals table: ID RESOURCE_ID GRADE SUBJECT 1 1 1 English 2 1 1 Soc 3 1 2 English 4 2 1 English 5 2 3 Soc 6 3 2 English 7 4 1 English Now, how do I select all the resources which have 1st and 2nd grade English goals? If I just do: Select * from resources, goals where ((resources.ID = goals.RESOURCE_ID) and (SUBJECT="English") and ((GRADE="1") and (GRADE="2"))); I'll get no results, since no record of the joined set will have more than one grade. I can't just put 'or' between the Grade conditions; that would give resources 1, 2, 3, and 4, when only 1 really should match. My real problem is slightly more complex, as the 'goals' table also contains an additional field which might be searched on. I'm thinking it's time for me to go into the deep end of SQL (MySQL, actually), and my old O'Reilly MySQL & mSQL book isn't doing the trick.
View Replies !
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.....
View Replies !
Retrieve Multiple Linked Records Efficiently
I have some records in database related to supply chain. e.g. N1 supplies item I1 to Node N2 is represented as N1 I1 N2. Now I want to find all parent nodes or say chain of nodes supplying to particular node in an efficient way. Can you please tell me how I can do it with efficient query. Currently it needs multiple queries & performance drops considerably.
View Replies !
PhpMyAdmin Editing Multiple Records Not Working
I'm trying to modify more than one record at a time. I'm checkmarking all the records I want to edit, but when I click on the edit button next to where it asks what to do "With Selected:" it only gives me one record to edit at a time. Any way to fix this pain in the @$$?
View Replies !
Get Query Time Query Time Using PHP
I just want to ask, how to get query time like this one: SELECT f_name, l_name from employee_data where f_name = 'John'; +--------+------------+ | f_name | l_name | +--------+------------+ | John | Hagan | | John | MacFarland | +--------+------------+ 2 rows in set (0.00 sec) <- This Time <-- Maybe some instruction using PHP?
View Replies !
Time Query - Please Help?
I’m completely baffled by a query that I thought I had working yesterday, but I can’t get it working today. Here’s the screnario: I’m publishing a story to the web and I don’t won’t it to be published live until: -The current date (type: date) is less or equal to now -The current time (type: time) is less or equal to now So here’s my query: select * from cms_stories WHERE section = 'news' AND published_web_date <= NOW() OR published_web_date IS NULL AND (published_web_time <= NOW() OR published_web_time IS NULL) ORDER BY story_id DESC LIMIT 1 Now the query works for the published_web_date, but not for the published_web_time. It seems to ignore that as you can see from the screenshot returned below from that query.
View Replies !
Limiting Query Time
Is it possible to limit the query time in MySQL (3.x or 4.0)? For example, I'd like to have any query that takes more than a specified number of seconds just quit automatically. Seems dumb, but on a web site, nobody is going to wait minutes for a query to return so they refresh anyway. So on a busy server, MySQL ends up with several queries running that all take a long time to finish which compound to make it even slower. A simple time limit would solve the problem. Yes, I know that the queries should take less time, but again, on a busy server, sometimes the longer queries do take a long time (10 minutes or more) to complete.
View Replies !
Add Time Using A Select Query
Hello, how can I add, say, 2 hrs from a time in a database using a select query. I have searched the forums but cannot find an answer. Is it possible PHP Code: select date, `time` as timetime , time_format(`time','%l:%i %p') as Printtime, client, address from..........
View Replies !
Exceeded Run Time Query
I made a application in PHP, I have a table "control1" and this table have 85000 registries and Size is 200 MB, so when I try do a query in PHP, I have a error "exceeded run time", I changed the time but is the same, and after I connected by console, and I could see that the query take 14 minutes. Here my sql: $sql_filtro="SELECT COUNT(*) nFilas FROM control1 WHERE MATCH (contenido) AGAINST ('$q')";
View Replies !
How To Get Time Execution Of A Query?
Do you know how to get the time that a query executes? Is there a formula on how to get it? Example: Note: I have 5000 data in the table cars $result = mysql_query("Select * from cars"); Question: How to get time of executing the query above?
View Replies !
How To Get Query Execution Time
How to get query execution time in "mysqlquery.log" files I am using: Linux 7.3 mysql 4.1 How to display the query execution time for each and every query in log files. i have configured "slow-log-query" and "mysqlquery.log" in "my.cnf" is pasted below. log = /var/lib/mysql/mysqlquery.log log-slow-queries = /var/lib/mysql/slowquery.log long-query-time = 0 slow-launch-time = 1 then i can get a output in "mysqlquery.log" like, 070830 9:24:29 2 Connect root@localhost on 2 Init DB jbdatabase 2 Query select count(*) from vacancy1_table 2 Query select category,listcategory from category_t able order by category 2 Quit but i need to display the "timetaken of each end every executed query" along with the query in the above logfile like, E.g:select category,listcategory from category_table order by category(0.03 secs).
View Replies !
Execute Time Of A Query
I'm running a SELECT * query on a table with over 3,000 rows. I need to know the execute time on the query. PHPMyAdmin shows it to me, but because they automatically add a LIMIT 0, 30 I suspect the time is only for those 30 records. It also has a feature to prevent web server timeouts that prevents me from setting the LIMIT to 0, 3100. BTW, I'm programming in PHP. Does the time to run the query get automatically passed when PHP sends the query to MySQL? <? PHP $Query = "select * from my_table where 1"; $Result = MYSQL_QUERY($Query); ?>
View Replies !
Last Query Execution Time
Is there a way to retreive the elapsed time for the prevously executed query? Alternatively, Is there a way to query the current time in fractions of a second? I am attempting to use a stored procedure to execute and track the time it takes to run some queries and other commands. The logic of the stored proc would go something like this.....
View Replies !
Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this. Here is the query im using:
View Replies !
Take Long Time To Run A Query!
I have a webserver that I just use for a webapplication. PHP, Apache and MySQL is installed on the server. The size of MySQL database is 10 GB. End inn it is millions of rows. I wonder where I search what is the normail time the data to return? In my case it takes from 20 sec to 2 min to run a query!
View Replies !
Time Query Question
I'm trying to pull dates from a db that are newer than 6 months old. I've been testing with 1 day, since there's nothing in the db that's older than 6 months. query PHP $DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time "; $DATEsql .="FROM login "; $DATEaql .="WHERE time_in > CURDATE() - INTERVAL 1 DAY "; $DATEsql .="ORDER BY time DESC "; that is giving me the same results as PHP $DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time "; $DATEsql .="FROM login "; $DATEaql .="WHERE time_in < CURDATE() - INTERVAL 1 DAY "; $DATEsql .="ORDER BY time DESC "; This is what I'm interpreting as what I need to do according to the MySql manual.
View Replies !
Query Log With Execution Time?
I have developed a big property portal web site and have probably written about 1000 different SQL queries in the process (OK, maybe only 500 or so) but a lot. The site is now gaining popularity and although it is on a dedicated server I want to make sure that everything is running smoothly. I have optimised several tables with indexes etc. and have sped up certain tasks, but would like to know if there are any other queries that take too long and should be optimised. Is there a way to log all queries together with the time it took to execute the query? Is this already logged and if so - where is it?
View Replies !
Query Execution Time In PHP Script
I am interested in displaying the query execution time as mysql does from the console, but using php. I've looked everywhere for this and can't find anything. Does anyone know how to display this information to the browser via php? The information must be there since the console provides it. I checked the php function mysql_info(), but that only shows records, duplicates, deleted, matches, changed, warnings, etc. Didnt see any specific php function for getting execution time.
View Replies !
|
TRACKER
