Possible to combine these three queries?




I'm not using mysql v4.1!!!

Is it possible to combine these queries into one

Code: SELECT max( id ) FROM table WHERE id > '$my_id' SELECT * FROM table WHERE id = '$my_id' SELECT min( id ) FROM table WHERE id > '$my_id'

I am trying to use the query to develop a "previous" and "next" navigation system.

Posted On: Wednesday 24th of October 2012 09:14:46 PM Total Views:  152
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Is it possible to ORDER BY sum(amount) if there is $ sign in the column?

; Is it possible to ORDER BY sum(amount) if there is $ sign in the column The values are like $240, $544,...
VIEWS ON THIS POST

123

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to do this with 1 query?

I save prices data for different days. If a day doesn't have any changes, it is not in the database. table prices date | price ---------------------- 2010-10-01 | 200 2010-10-02 | 200 2010-10-03 | 220 2010-10-06 | 260 2010-10-09 | 190 ... Now I need to prices for statistic purposes for 1 month, but only for every 7 days of the month. If there is not data in each 7 day, it checks what was the price for the most recent date that exists. For this I make for each 7 day a query, so I need 4 queries 1. SELECT pageViews FROM stats WHERE date
VIEWS ON THIS POST

98

Posted on:

Friday 12th October 2012
View Replies!

Impossible with join?

I have two tables with the following fields: 1 - uid, account_number, company 2 - uid, fk_account_number, month, year, value. I can't see any way without using a sub-query to do a sum on all values in the second table where the month is 7 and the year is 2010, as well as being able to get all values where the year is 2010 regardless of month. Is there a way An example is PHP Code: SELECT u.uid, u.company, SUM(value)-Somethingisneededhere... FROMusersu INNERJOINsalessONs.fk_account_number=u.account_number GROUPBYu.uid EDIT - Would a UNION work here
VIEWS ON THIS POST

92

Posted on:

Friday 12th October 2012
View Replies!

SQL JOIN in PHP - is it possible to retrieve two different values from one column?

, my first time in this forum, i have tried looking for the solution to my problem on this forum as well as others, however i have not found an answer yet. would much appreciate your help. I am building a web based database using php and mysql. i think this problem is more associated with mysql. basically i have a two tables in the database: Location, Distance. Location consists of the fields: Location_ID, name. Distance consists of the fields: Distance_ID, origin, destination, distance. the two fields origin and destination in the distance field, reference the Location_id. what i am wondering is, is it possible to create a query that will out put a table with the following details: Distance_ID, Origin_name, Destination_name, distance. for example: Location_ID, name 1, london 2, paris 3, rome Distance_ID, origin, destination, distance 1, 1, 2, 50 2, 1, 3, 75 3, 3, 2, 12 what i want to display is: Distance_ID, Origin, Destination, Distance 1, london, paris, 50 2, london, rome, 75 3, rome, paris, 15 the code that i have so far is: Code: $query="SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; if ($num==0){ echo "Sorry, there are no distances."; } else while ($i < $num) { $DistanceID=mysql_result($result,$i,"DistanceID"); $origin=mysql_result($result,$i,"Location.name"); $destination=mysql_result($result,$i,"Location.name"); $distance=mysql_result($result,$i,"distance"); echo "$DistanceID$origin$destination$distance"; $i++; } what my code will output is : Distance_ID, Origin, Destination, Distance 1, london, london, 50 2, london, london, 75 3, rome, rome, 15 this is not exactly what i want. i can sort of see where the problem is (the issue with Location.name), but i have no idea how to solve this, or whether it is even possible. i am not familiar with INNER JOIN, but i understand that Code: INNER JOIN Location ON Distance.origin=Location.locationID gets stored in origin Code: $origin=mysql_result($result,$i,"Location.name") aswell as destination Code: $destination=mysql_result($result,$i,"Location.name"); . and that only Code: INNER JOIN Location ON Distance.origin=Location.locationID is declared for origin. but if i also do it for destination, the code breaks. i.e. Code: "SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID INNER JOIN Location ON Distance.destination=Location.locationID" any ideas on how to display the names of both the origin and destination would be much appreciated. edit: i guess i could always create 2 tables named location_origin and location_destination, but my idea was to save space by just having one table to serve the purpose of storing names of places. though i wonder if it would be a better/easier idea to have the two tables instead of one.
VIEWS ON THIS POST

139

Posted on:

Friday 12th October 2012
View Replies!

Problem with fulltext searching, possible alternatives?

Hi , I have two tables, they both contain domain names. For arguments sake, lets say: known_domains - The table of domains I care about mapped to customer ids. About 200,000 domains are listed in this table. domain_data - A table holding data about random domains, around 300,000 rows but varies significantly on a day to day basis. The domain_data table is populated by a job that has to run in the shortest time possible, it has no time to be checking if the domain it is recording data for exists in the known_domains table. I have another job that compares the domains and removes them from the domain_data table if they do not appear in the known_domains table. At first, I thought this would be easy but I ran into a big headache with subdomains Basically, imagine that exampledomain.com listed in known_domains, sub.example.com may or may not (in fact its a whole lot more complicated than this, just trying to keep it simple for now). I still need to keep the data for the subdomain even if it's not explicitly defined. Ideally, what I need to do is: Code: SELECT id from known_domains WHERE domain = 'exampledomain.com' OR ('exampledomain.com' REGEXP CONCAT('^(.+\.)', known_domains.name, '$') Obvously, thats just not viable when checking 300,000 domains as, understandably, each query takes about 5 seconds. I've setup a fulltext index on known_domains.name and call Code: SELECT id,domain, MATCH(name) AGAINST('exampledomain.com') AS score FROM known_domains MATCH(name) AGAINST('exampledomain.com') ORDER BY score DESC LIMIT 10 Now thats probablly not going to be totally accurate but the testing I have done seems to indicate that the matches I am interested in will be in the first 10 rows. The 10 rows returned are passed through some script voodoo to determine the best possible match. Thats all fine and dandy but when examining the logs, I noticed that I was deleting quite a few rows for domains that have entries in the known_domains table. Mostly these domains appeared to be 6 characters in length but thats not always the case. For example: abc.com in known_domains but nothing returned when matching against either foo.abc.com or abc.com abc.def.de in known_domains but a match against abc.def.de returns nothing. I've checked ft_min_word_length and that is set to 4 so Im somewhat confused by this and am tired of The only other thing I can think of is that the problem being caused by stopwords but after looking at the default stopwords I can't see it. I should add that I'm using Mysql v4.1.20 (not in a position to upgrade either at this time). Wondering if anyone here is able to shed any light on the issue or perhaps suggest an alternative way of getting to the end goal. TIA.
VIEWS ON THIS POST

118

Posted on:

Friday 12th October 2012
View Replies!

Set a as database variable possible ?

set a = db1 select * from a.table1 where col1='123'; is this possible in mysql 50 server or any refferences link Best
VIEWS ON THIS POST

89

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to create index for a temporary table

ALL, I have to do SQL query over two temporary tables, I want to create indexes on fields of the temporary tables. Is it possible to create index on a temporary table. I am using MySQl 5.0.26 Furthermore, Is it possible to merge the statements for creating temporary table and the statement for creating index on the temporary table into one singel statement Code: Create Temporary Table Tmp AS Select Tb1.filed1 AS field1 FROM Tb1 LEFT Join Tb2 On Tb1.field1 = Tb2.field1 Code: CREATE INDEX idx_field1 ON Tmp (field1)
VIEWS ON THIS POST

101

Posted on:

Friday 12th October 2012
View Replies!

How possible is this mysql query?

all l am trying to mass update my table using this query. l use this query to select username and customerid to select from customer(table) users that have sponsors as 2 from the table. The sponsor is done when a user want to register he or she needs a sponsor to before he or she can register and the sponsor id is the username of the person who referred him. To register you must insert a sponsor id which is the same with the username of the person who referred you. On insert, the sponsor id which is the username of your referrer would be inserted in the sponsor column of the new user's row as his or her sponsor. so below is the query that select from the customer table users who have 2 sponsors. $sql = "SELECT sponsor,mstatus, count( username ) from customer Group by sponsor DESC Having count( username ) = 2 so l want to mass update users that have 2 sponsors or atleast a sponsor.
VIEWS ON THIS POST

117

Posted on:

Friday 12th October 2012
View Replies!

Single query possible?

I am building a dynamic cms. First I creat a list, then I asign inputfields to this list. So e.g. A list called 'cars' (table content_List) The inputfields are: 'brand', 'description', 'country' (table content_Field) Some different cars with different specs (table content_Text) I am trying to create a single query, but so far I could only acieve this with a loop within a loop using a second query. This is my dump: CREATE TABLE `content_Field` ( `fieldID` int(11) NOT NULL auto_increment, `fieldListID` int(11) NOT NULL default '0', `fieldName` varchar(255) NOT NULL default '', `fieldType` varchar(25) NOT NULL default '', `fieldSort` varchar(255) NOT NULL default '', `fieldOrder` int(11) NOT NULL default '0', PRIMARY KEY (`fieldID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Gegevens worden uitgevoerd voor tabel `content_Field` -- INSERT INTO `content_Field` (`fieldID`, `fieldListID`, `fieldName`, `fieldType`, `fieldSort`, `fieldOrder`) VALUES (1, 1, 'brand', 'text', '', 0), (3, 1, 'country', 'text', 'alfabet ASC', 0), (4, 1, 'description', 'html', '', 0); -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `content_List` -- CREATE TABLE `content_List` ( `listID` int(11) NOT NULL auto_increment, `listName` varchar(255) NOT NULL default '', PRIMARY KEY (`listID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Gegevens worden uitgevoerd voor tabel `content_List` -- INSERT INTO `content_List` (`listID`, `listName`) VALUES (1, 'cars'); -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `content_Text` -- CREATE TABLE `content_Text` ( `textID` int(11) NOT NULL auto_increment, `textItemID` int(11) NOT NULL default '0', `textListID` int(11) NOT NULL default '0', `textFieldID` int(11) NOT NULL default '0', `textContent` text NOT NULL, `textOrder` int(11) NOT NULL default '0', PRIMARY KEY (`textID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; -- -- Gegevens worden uitgevoerd voor tabel `content_Text` -- INSERT INTO `content_Text` (`textID`, `textItemID`, `textListID`, `textFieldID`, `textContent`, `textOrder`) VALUES (1, 1, 1, 1, 'volvo', 10), (5, 2, 1, 1, 'peugot', 20), (3, 1, 1, 3, 'sweden', 10), (4, 1, 1, 4, 'This car is very safe', 10), (6, 2, 1, 3, 'france', 20), (7, 2, 1, 4, 'This is a car with good seats', 20), (8, 3, 1, 1, 'saab', 30), (9, 3, 1, 3, 'sweden', 30), (10, 3, 1, 4, 'Car which is very expensive', 30), (11, 4, 1, 1, 'BMW', 40), (12, 4, 1, 3, 'germany', 40), (13, 4, 1, 4, 'I cannot affort this one...', 40); Now I want to create a query so that the following would be printed: peugot france This is a car with good seats BMW germany I cannot affort this one... saab sweden Car which is very expensive volvo sweden This car is very safe As you can see, the list is sorted by the countrie (alfabet). This is because I have assigned the field 'countrie' (in content_Field) to be alfabet ASC. What I have now is: query1: SELECT * FROM content_Field, content_Text, content_List WHERE listID = '1' content_List.listID = content_Field.fieldListID AND content_Text.textListID = content_Field.fieldListID AND content_Text.textFieldID = content_Field.fieldID AND fieldSort != '' do_query1 { query2: SELECT * FROM content_Text, content_List LEFT JOIN content_Field ON (textFieldID = fieldID AND fieldListID = '1') WHERE textListID = listID AND content_Text.textItemID = '$textItemID_query1' ORDER BY fieldOrder ASC do_query2 { echo the list. } while query2 } while_query1 So in fact query 1 is to get the order from the textItemID, query2 is to echo the complete list which goes with the textItemID that came out of query1 I wonder if it is possible to get these 2 into 1 query. I do get the seperate sublists sorted by textItemID, but the sublists should be ordered alfabetacally. Is there a way to do this\t
VIEWS ON THIS POST

95

Posted on:

Friday 12th October 2012
View Replies!

Multiple tables insert not possible?

hi! i can't find information on multiple tables INSERT. is it possible this query fails: mysql Code: Original - mysql Code INSERT INTO srv_stk_utils.pog_citycodes2groups ssp, srv_stk.groups ssg ( ssg.name, ssp.citycode, ssg.req ) VALUES ( 'asxasx', '234342', '2343' )
VIEWS ON THIS POST

113

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to pass one field from one database to other

Hi..... I have two database.... what ever i add new row,i should get in two database....
VIEWS ON THIS POST

94

Posted on:

Wednesday 17th October 2012
View Replies!

INSERT SELECT possible from one DB to another?

I can successfully do an insert select from one table to another in the same database. Is it possible to do this from one database to another
VIEWS ON THIS POST

89

Posted on:

Sunday 21st October 2012
View Replies!

should column names be as descriptive as possible?

I'm trying to design a database and ran into a stumbling block. I have a row in the table for hair color, so should I name the column hair_color or something shorter like color to make it as efficient as possible
VIEWS ON THIS POST

235

Posted on:

Sunday 21st October 2012
View Replies!

MySQL impossible WHERE in JOIN

, first, here the description of what I want to do: There are users and roles. Users can have many roles. This relation is stored in a separate table. I want to create a search form where you can search for users with specified roles as an AND connection. Eg, you select 3 roles, the result should just be useres that have all three roles at the same time. My tables with excerpt sampledata: Code: user_users --------------------------------------- id username isDeleted ....................................................... 1 admin 0 11 test4 0 2 Guest 0 10 test2 0 12 Test5 0 13 Test6 0 14 Test7 0 15 Test8 0 16 Test9 0 17 Test 10 0 18 Dummy 0 roles --------------------------------------- id roleName ....................................................... 1 Administrator 2 Guest 3 Member 4 Moderator 7 Another 1 8 Another 2 9 Another 3 acl_usersroles --------------------------------------- user_users_id acl_roles_id isDeleted ....................................................... 12 2 0 1 8 0 11 2 0 11 3 0 15 2 0 14 4 0 14 8 0 14 9 0 14 2 0 16 3 0 16 4 0 16 8 0 16 9 0 16 2 0 17 4 0 17 7 0 17 2 0 1 2 0 My SQL query till now is Code SQL: SELECT `user_users`.`id`, `user_users`.`username` FROM (`user_users`) JOIN `acl_usersroles` ON `acl_usersroles`.`user_users_id`=`user_users_id` WHERE `acl_usersroles`.`acl_roles_id` = 8 AND `acl_usersroles`.`acl_roles_id` = 7 AND `user_users`.`id` = `acl_usersroles`.`user_users_id` AND `user_users`.`isDeleted` = 0 GROUP BY `user_users`.`username` ORDER BY `user_users`.`username` ASC The result is empty. If you remove the WHERE condition `acl_usersroles`.`acl_roles_id` = 7, the result is again all users with the role 8. But trying to force more than one role at the same time gives an empty result. Does anyone have an idea how to solve this
VIEWS ON THIS POST

89

Posted on:

Sunday 21st October 2012
View Replies!

Trigger with algorithm - possible?

Dear Sitepoint Members, I am looking to create a trigger on a database table that does following: If a new entry (INSERT) comes into the table: TABLE_Y I want to take the value of the INSERT statement and values of: column.x from TABLE_X and column.z from TABLE_Z and have it run through a mathematical algorithm which then updates column.x from TABLE_X I am running MySQL 4.0.27 and am new to triggers, can anyone help me out with this or point me in the right direction on MySQL 4 trigger resources (Google searches have left me empty handed) Best regards, Eiso
VIEWS ON THIS POST

88

Posted on:

Sunday 21st October 2012
View Replies!

return only 3 photos from 3 specified galleries in one query (was "Is it possible?")

Let me start from the beginning.... I have a table that lists photo details, with a foreign key to link them to a gallery. Lets say I have 20 photos altogether and 4 galleries, (for simplicity sake, 5 photos per gallery). My question is: Is it possible to query the table to get it to return only 3 photos from 3 specified galleries in one query (So returning 9 photos altogether, 3 from each gallery) Rather than querying the table 3 times with a limit of 3 and merging the results.
VIEWS ON THIS POST

84

Posted on:

Sunday 21st October 2012
View Replies!

Get only 1 result of 2 possibles in query

I have this query wich gives me property name, season, price and start and end dates and I have 2 years in the calendar so I get 2 years ie repeated seasons with there start and end dates and what I am really interested in is to get just the start and end date and the price for each property excluding the year, or only display 1 year. When I enter the prices and seasons in the calculator I mark the start day and end day in column start_end_dates. This is the query: SELECT property, minimo, description, start_end_dates, cal_date, price_client_week, price_owner_week FROM calendar_table WHERE start_end_dates = 'start' or start_end_dates = 'end' order by property, cal_date I get result like this: Casa_Blanca_4 Christmas start 2012-01-01 2900.00 2030.00 Casa_Blanca_4 Christmas end 2012-01-06 2900.00 2030.00 Casa_Blanca_4 Low start 2012-01-07 2200.00 1540.00 Casa_Blanca_4 Low end 2012-05-31 2200.00 1540.00 etc, and the same for year 2013 1- Any ideas of how to get only one year (start and end dates). 2. also I been trying to concat the result from table start_end_date, if posible I would get only 1 row.
VIEWS ON THIS POST

99

Posted on:

Wednesday 24th October 2012
View Replies!

Is it possible to GROUP BY first two characters of a column ?

I am looking to group a column that contains uk post codes. I can group the post codes using the following php/mysql format: SELECT * FROM addy GROUP BY pcode I would like to group them using only the first two characters from the 'pcode' column eg: NE11 2PP NE22 2PP NE33 2PP SE34 1XB SE44 1XB SE55 1XB would give me two groups - NE and SE 1: Can someone give me the mysql code to achieve this 2: Later in the code I will need to loop through all of the 'pcode' groups to find out all of the codes that start with NE ...and then SE etc. Could you help me with this query too Many
VIEWS ON THIS POST

106

Posted on:

Wednesday 24th October 2012
View Replies!

Impossible duplicate...

, i have a very strange case; when i submit the following query Code: select id from text_tmp limit 25; my result is : Code: +----+ | id | +----+ | 1 | | 2 | | 4 | | 5 | | 6 | | 7 | | 8 | | 10 | | 11 | | 13 | | 15 | | 16 | | 17 | | 18 | | 20 | | 21 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 30 | | 31 | | 32 | +----+ therefore, id 22 (and a few more) is missing... and when i ask for : Code: UPDATE text_tmp SET id='21', place=((text_tmp.original_id % 5) +1), round=round-1, occupied='no' WHERE occupied='yes' ORDER BY id DESC LIMIT 1; it returns me Code: DBD::mysql::db do failed: Duplicate entry '22' for key 1 .... how come p.s.: meanwhile i'm googling for help on the issue but the last 2 hours haven't brought be any info yet in my needs.
VIEWS ON THIS POST

161

Posted on:

Wednesday 24th October 2012
View Replies!

Is it possible to re-arrange primary key sequence?

hi i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g 1 - entry 2 - entry (3 - deleted no mere here) 4 - entry 5 - entry When key is not there my AJAX application fails to run properly due to not finding next id. Is there any method to re-arrange primary key values to a proper sequence when one or more numbers are missing so that i will write a query and whenever user deletes an item i will re-arrange the primary key sequence automatically....
VIEWS ON THIS POST

386

Posted on:

Wednesday 24th October 2012
View Replies!