How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red.
As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Complete Forum Thread with Replies
Related Forum Messages:
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
Select Within Select Needed To Get This Query Going?
I'm not sure how to go about this, but I am thinking I need some sort of a select query within a select query to get this properly displayed... My table structure is as follows (simplified, of course): payment_id | member_id | completed | amount | trans_added and I am trying to get the following data out of the table - a daily sum of all the transactions that were 1) completed, 2) by a member who already has an existing completed transaction (completed = 1) on a different date (or members with a count of all completed transactions > 1 will be included only). so far I've got this query, but I don't know where/how to plug in the condition for the members/completed/etc: .....
View Replies !
Select Previous And Next Id After Id=# ?
For a gallery I'm trying to query for a picture by its id and also get some images before and after that id (according to current sort) are there references like DOM previousNode or stuff like that to use in the WHERE statement? Or how could I do this? Currently I have: ($picId is a php variable) '(SELECT id, picUri, picUriSmall FROM pictures WHERE id < '.$picId.' ORDER BY id DESC LIMIT 2) UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id = '.$picId.') UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id > '.$picId.' LIMIT 2)' This works but doesn't seem to be the best way. I want to be able to have it working for other sorts than by id. And the two first images gotten by this query will be in reversed order (can be fixed in php). Is there a way to ask for my picIDs position in a query and then use another query to make the LIMIT 10,14 if my image was in position 12?
View Replies !
Best Way To Select Next And Previous Items With Order By
to select the previous and next items. I am using this: DO @a:="title_of_current_item"; SELECT * from tabled where itemtitle < @a order by itemtitle desc limit 0,5; --previous items SELECT * from tabled where itemtitle > @a order by itemtitle asc limit 0,5; --next items is there better (one query) and/or faster way?
View Replies !
Previous, Current, And Next In One Query?
This situation has come up a couple times and I've wanted to know if there's a better way to do it. Given something unique (an ID number, a specific date/time, etc) I want to find the thing that comes before it and the one after it when sorted by something else. Oh, and the "something unique" isn't sequential - random intervals between each. Code: ....
View Replies !
SELECT Query.
Table 1 3 #1,#2 2 5 #2,#3 3 3 #1,#2,#3 4 4 #4 5 5 #1,#2,#3 6 5 #1,#2,#3 7 4 All 8 3 #1,#2,#3 Need to compare the value of field2 with the occurrences of "#" in field3. Tried this: $query_plcResultsSet = "SELECT * FROM Table WHERE RteType IN ('Mainline', 'Connector') AND StartDateD = '$strchvarval' AND (field2 = 'All' OR " . substr_count(field3, '#') . " = field2)";
View Replies !
SELECT Query With IN(...)
I have a query SELECT id, name, text FROM text WHERE id IN (11,22,1,34,56,13,21,19,3,6,9) I need result sorted as in 'IN ()'. But it is always sorted by id. How can I achieve it
View Replies !
Can't Use Select...IN Inner Query
I'm trying to make the following SQL query and keep getting errors: select * from mytbl where reportid in (select reportid from mytbl where level>60) I get this error: 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 'select reportid from mytbl )' at line 1 When I'm running the inner query (select reportid from mytbl where level>60) it is running OK. Also, when I'm running without inner query (select * from mytbl where reportid in (1,2,3)) itis running Ok. But Somehow I can't run it together. I'm using MySQL 4.0.14.
View Replies !
Help With SELECT Query
I am having trouble coming up with the proper SQL that will get me a certain result. i have a table that looks something like this: request_id | member_id | Club_id 1 | 10 | 12 2 | 10 | 12 3 | 10 | 142 4 | 10 | 142 5 | 10 | 72 I am trying to come up with a query that will give me the number of times that member #10 chose each different club. ex: club #12 - 2 times club #142 - 2 times club #72 - 1 time i have tried a few things but everything i have tried with DISTINCT, or COUNT gives me 5 (total requests). And it is late in the day and my brain is a bit fried.
View Replies !
Using LIKE In A Select Query -
I am trying to query 2 tables. One has a field, County_name which includes a list of counties and the state they are associated with ie. MT-Missoula The other table is a list of sites and has a State field. I would like to bring up a list from the County_names field that begin with a State that matches the value in the Site State field. I can't figure this out, can anyone help a new mysql user? $countyresults=mysql_query("SELECT ct.*, s.State, s.Site_ID FROM county_tbl AS ct, site_tbl AS s WHERE s.Site_ID='$ID' AND ct.County_name LIKE 's.State%' ORDER BY County_name");
View Replies !
Need Help With A SELECT Query
I have two tables. Table 1 = ContentWords Table 2 = TitleWords The tables have both each two fields named wordId and articleId. The wordId field references a wordId in a table named Words which contains the fields wordId and word. As you might understand I use these tables (ContentWords and TitleWords) to index words that appear in articles to use with a search function. It's easy to construct a search query to have it select a articleId from either ContentWords or TitleWord (SELECT articleId FROM ContentWords/TitleWords WHERE wordId = $var). But if I want to search if a word exists in both the Content and Title? I've constructed a SELECT query that kind off works: SELECT ContentWords.articleId AS ContentArticleId, TitleWords.articleId AS TitleArticleId FROM `ContentWords` , `TitleWords` WHERE ContentWords.wordId = TitleWords.wordId AND ContentWords.wordId = $var AND TitleWords.wordId = $var This will output something like: ContentArticleId TitleArticleId 53 76 77 76 That means the word exists in the content in article 53 and 77. The word also exists in the Title in article 76. What I actually want is only ONE field named ArticleId with the result of 3 rows (53,76 and 77). Is this possible? Thanks in advance!
View Replies !
Select Query Help Please
The below query works well for returning 1 result however I now need to expand it to return multiple results. At the moment I feed it 1 car type and 1 company id and it spits out one result, I then loop the query to get the number of results I need - but this is very inefficiant so I want to change it so I pass all my variables at once. So where I have cars.type =Ƈ' I want to have car.type =Ƈ' or cars.type=ƈ' which in itself works but how to I extend the company section as when I add the extra 'Or's' in there the query goes wacky and spits out hundreds of results which are all the same - I think its something to do with the inner join but im not sure. MySQL SELECT prices.single_f, prices.return_f, prices.vehicle, resort.company_id, resort.resort, cars.type, cars.description, cars.long_description, cars.image, surcharges.type, surcharges.adult, surcharges.kids, prices.timefrom, prices.timeto, prices.resort_id, cars.min, cars.max, company.is_enabled FROM prices INNER JOIN resort ON resort.id = prices.resort_id AND resort.company_id = Ƈ' AND resort.resort = 'Acoteias' INNER JOIN company ON company.id = Ƈ' INNER JOIN cars ON cars.type = prices.vehicle AND cars.type = ƈ' LEFT JOIN surcharges ON surcharges.resort_id = prices.resort_id WHERE prices.break <= ďb' AND company.is_enabled = 'Yes' AND ( ( prices.timefrom <= prices.timeto AND prices.timefrom <= ཁ:00:00' AND prices.timeto >= ཁ:00:00' ) OR ( prices.timefrom >= prices.timeto AND prices.timefrom <= ཁ:00:00' AND ན:59:59' >= ཁ:00:00' ) OR ( prices.timefrom >= prices.timeto AND ༼:00:00' <= ཁ:00:00' AND prices.timeto >= ཁ:00:00' ) ) LIMIT 0 , 30
View Replies !
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
View Replies !
Mysqldump Select Query
I am trying to do a dump for a 2 gig table... I would like to dump the tables in portions so that it's a little easier to work with... Can somebody suggest a way to do a mysqldump table query ie.. mysqldump -u user -p database="MYDATABASE" Query="SELECT * FROM TABLE WHERE State='NV';" I'm pretty sure this is possible... I have no idea how to do it.
View Replies !
IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS.. I'm trying to do this: insert into table2 (field1) select field1 from table1 where field1 not in (select field1 from table2) delete from table1 where field1 in (select field1 from table2) 1. Insert field1 from table1 into table2 if it doesn't already exist there... 2. Once moved, delete from table1.
View Replies !
Select Last_insert_id Query...
I have a site with various parts which allow users to enter info via forms which gets entered into various tables in a MySQL db (I'm using ASP, rather than PHP). I have an ecards bit, and when users send a card, their data is entered into the db via something like: INSERT vars into ecard_tbl.... I just read that I can use this to get the most recent id from the autoincrement field in the table: SELECT LAST_INSERT_ID However, it doesn't seem to need a table name for that. What if exactly at the same time another users enters something into my guestbook. That will submit data to the same MySQL db, so won't the 'SELECT LAST_INSERT_ID' give me that ID, and not the ID from the card submission? Am I missing something very obvious here? I checked the MySQL docs but can't see anything obvious.
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 !
Double Select Query
i've got 2 tables tb_orders --------------- id_orders description 1 client1 2 client2 tb_reminders --------------- id_reminders id_orders reminders_time 1 1 2005-02-18 18:30 2 2 2005-02-18 15:30 the user inserts the reminders time for the specified order. i have to select the orders that have the last reminder < today ordered by reminders_time desc. thanks
View Replies !
INSERT SELECT In One Query
I keep getting an error with this query: Insert into new_voter (salutation, firstname, lastname, electoral_district, poll_number, birthdate, gender, occupation, party_affiliation, ph_home, email) values select (txtSalutation, firstname, lastname, txtElectDistrict, txtVotingArea, dtBirth, txtGender, txtOccupation, nPartyId, txtPhoneDay, email) from person Can someone spot the error (presumably with the syntax).
View Replies !
SELECT Query Optimisation
I have the a query - rather large - as show in the attached JPG. As you can see, the optimisation is not ideal as it is "using where, using temporary, using filesort" for the first row of the EXPLAIN. The query takes around 8 seconds to execute.
View Replies !
Select Query Using Limit
i have the basic sql query as follows: $sql = "select * from photo where CategoryId='$idc' order by Id limit $start, 9"; this will select all rows from db photo based on CategoryId, then stop after it gets the 9 beginning at $start right? start is the value of the ID i want to start the select from. in this example, i have valid db values ranging from id: 11-20. the 1st page that runs this query has start=11, and displays for 11-19. when i goto the next page setting start=20, i get nothing. Limit returns 9 rows starting at $start right? or am i misunderstanding how all this works?
View Replies !
IF Statement Within A Select Query?
If there a way to do an IF statement within a MySQL select query? I have this query that gets a vendor's code and name from the database and CONCAT it to put the code into "()" and add the vendor name to the end. Is there a way to write it so the "()" will not be included if the vendor does not have a code set.
View Replies !
MYSQL Select Query
I am new to MYSQL.I am stuck with building a MYSQL Query. I have two tables X and Y Table x column1 Id Table Y Column1 column2 ID Monday-Sunday i need the ids which has saturday and sunday in the table Y.
View Replies !
Select Distinct Query
I have a table in my db with two fields, one called link and the other shop. I have another table called link_partners. This latter table has an id column that matches the link column in the former table. I am trying to run a sql statement on the two tables and am able to do so until I try to just show the records in the link_partners table with distinct category column value. Here is my sql Code: mySQL="select DISTINCT category from link_partners inner join link_partners on link_partners.id = links.link where links.shop = 77"
View Replies !
Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5 this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent). I want to select all the rows that has :7: in the sections field.
View Replies !
Select Query Between Two Dates
select * from table where Fri, 23 May between sdate and enddate Fri, 23 May is user given date table likes sno | sdate | enddate 1 | Mon, 19 May | Sat, 24 May 2 | Sat, 17 May | Mon, 19 May 3 | Fri, 23 May | Mon, 26 May so the above queries i need the 1 and 3 results only what is the problem in query and how to change it give solutions.
View Replies !
How To Speed Up Select Query
I have a very big table >2GB. My query is as follows: Select JDay, cda_lake.Cell_Num,NH4, NOx, TP, DO, pH from cda_lake, site_cells where Site_Num =1 and site_cells.Cell_Num = cda_lake.Cell_Num and truncate(JDay,0)=152 order by JDay,Cell_Num; It took more 1 min to get 20504 records. Can you tell me what's wrong?
View Replies !
Lock On Select Query
I am doing some etl work, trying extract some data from source mysql database. When I am running select * from query, I want to know if there are locks on the table, what is the best way for me to look at that info,
View Replies !
SELECT / UNION Query Help!
I have a list of queries in a union, ex: (SELECT * FROM tbl WHERE a='b') UNION (SELECT * FROM tbl WHERE c='d') UNION (SELECT * FROM tbl WHERE e='f') etc. I would like to calculate in the query the percentage of criteria each row (primary key ID) fits. For example, if a row with id='20' fits 2 of 3 SELECT criteria, then an additional variable (created in the query) such as: percAccurate would equal to (instances/total_select_queries), so in this case: for id=20, percAccurate = (2/3) = 66% A result row could be returned like this:
View Replies !
Mysql Select Query Help
i am working with a shop script that i have inherited and i want to add the ability to have products in multiple categories. To do this i have created an extra field in the product table called cat_id_2. Now on the list products page I have the follwing call at the moment, which pulls all of the products for the current category... $sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name FROM tbl_product pd, tbl_category c WHERE pd.cat_id = c.cat_id AND pd.cat_id IN $children AND pd_status=1 ORDER BY pd_name"; I tried to change this myself and added the extra paret to the WHERE clause and came up with this... $sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name FROM tbl_product pd, tbl_category c WHERE (pd.cat_id = c.cat_id OR pd.cat_id_2 = c.cat_id ) AND pd.cat_id IN $children AND pd_status=1 ORDER BY pd_name"; But for some reason, instead of putting a product in two categories it puts the same product in the category twice, - its wierd. Can anyone see anything wrong with what i have tried?
View Replies !
Select Query (date)
How would I set the WHERE condition to select the fileds below to select only those who were placed 2 years ago? mysql> SELECT placement_date, name, age -> FROM table1 -> WHERE
View Replies !
Select And Update In One Query
My remoteurl table has 3 columns: id, hits and url. PHP UPDATE remoteurl SET hits=hits+1 WHERE id=2 SELECT url FROM remoteurl WHERE id=2 Instead of using two queries I want to use one query which could select url and update hits column. Is it possible?
View Replies !
Select Query Prblems
The below query is prelexing me - basicaly when you leave the search filed empty is to display all columns below where status = 'ON', but it does not - it display all no matter the status. SELECT id, name, jobtitle, date, location, salary_from, salary_to, job_desc FROM vacancy WHERE status = 'ON' AND id LIKE '".$searchterm."' OR jobtitle LIKE '%".$searchterm."' OR location LIKE '%".$searchterm."' OR salary_from LIKE '%".$searchterm."' OR salary_to LIKE '%".$searchterm."'";
View Replies !
More Complex Select Query
i tried to do this also looking at documentation from mysql site, but keep getting error messages: basically i got data on a table, there is a field 'views', and a field 'published_on', related to articles. what i need is a query that selects the articles and orders them according to views/day. so, i'd need some operation that first figures how many days the articles have been up: DATEDIFF(NOW(), published_on) then, i gotta divide that by published_on, and make the query return first the articles that have had the most views a day. any idea how i can accomplish this?
View Replies !
Difficult Select Query
Here's the scoop, I need to list the newest 5 works or series(group of works) that are available Don't list a work if it's in a series. Do list if it's not. The result set will just be w_id , finished, and s_id, but s_id will be null for works that don't belong in a series The series date == date of the newest finished work in that series Here's the table structure work --------- id primiary finished date work_series ------------- w_id int s_id int series ----------- id int
View Replies !
Optimizing A SELECT Query
SELECT id, gameid, name, type FROM media ORDER BY date DESC LIMIT 5 SELECT * FROM games ORDER by wishlist DESC LIMIT 5' SELECT * FROM games ORDER by mygames DESC LIMIT 5 SELECT mediaid, count(*) AS number FROM download WHERE date > ��-08-14 10:44:43' GROUP BY mediaid ORDER by number DESC LIMIT 5 SELECT * FROM news ORDER BY id DESC LIMIT 14' SELECT gameid, count(*) AS number FROM views GROUP BY gameid ORDER by number DESC LIMIT 10 All of these queries I have determined are inefficient (using some tools) and they all have the same thing in common. They're all trying to find the "Top 5" or "Newest 10" or whatever from a table, but in doing so they are going through every single value and comparing them. What other way is there to do this same idea of getting the "top 5" of a certain column values, without the SQL having to check every entry?
View Replies !
Select With Join Query Question
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status !=3D 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = =3D s.job_id) where s.status_id !=3D 1 group by j.job_id Any help is greatly appreciated.
View Replies !
Multi-table Query In One Select - Possible?
Runnint Mysql 3.23.47 I have the following two tables: backorder_notification ->product_code varchar ->email varchar ->date_added date Products ->code varchar ->name varchar ->other stuff that is not important Essencially - I want to get a count of the product codes that are in the backorder_notification table ordered by count then by product name and output the display:
View Replies !
Unix Timestamp Select Query
I am having trouble with the following sql query.I am trying to select all records in the future where the date is greater than today but to include today, the date field in the db is an unix timestamp. Code: "SELECT * FROM mysqltable WHERE mysqldatefield > now()-1 ORDER BY mysqldatefield DESC"
View Replies !
How To Include A Space In A LIKE Select Query?
On the bottom 3 lines you'll notice a space on either or both sides of the $term variable. What I'm trying to do is match $term with a space on the left, a space on both sides, or a space on the right. IF( $col = '$term' ,$score,0) or IF( $col LIKE '$term %' ,$score,0) or IF( $col LIKE '% $term %' ,$score,0) or IF( $col LIKE '% $term' ,$score,0) But for some reason the space is just getting ignored - can anyone tell me how to get it to recognise that I want the space included as part of the match?
View Replies !
Select Query Lasts 40sec
Let's say a MYSQL select query lasts 40sec. Is there a way to stop this query after let's say 6 seconds and let PHP know that?(and then tell the user that: "Sorry. This query was too time consuming...")
View Replies !
Select Query Within Specified Date Range..?
Im doing a select in which im specifying the date range.. "select key_id from result where started_at_date >= '2008-10-17 13:30:00' and ended_at_date <= '2008-10-17 13:30:20'" but in the above query im getting the key_id for which the date range is out of the date range specified here. That is im getting key_id = 5, for whcih started_at = '2008-10-18 13:30:00' and ended_at = '2008-10-18 13:30:20'
View Replies !
|
TRACKER
