How to paginate in case of SELECT * AGAINST ?




When generating a LIST via SELECT AGAINST then LIMIT X, Y does not work.
That is if the SELECT statemenet is like:

SELECT ix_id, title, descpt FROM customers WHERE MATCH (title, descpt)
AGAINST('$find') LIMIT 90, 100

Rather than getting the results from 90 to 100 MySQL returns all 100 results!

So how can one pageinate in case of SELECT * AGAINST

To put it another way, since MySQL returns all 100 values rather than from
90 to 100 only, how can we then effectively execute:

while ($result_chk_word = mysql_fetch_array($query_chk_word)) {

$url_id = $result_chk_word['ix_id'];
$title = $result_chk_word['title'];
$descpt = $result_chk_word['descpt'];

}


to list results for only 90 to 100

Posted On: Monday 31st of December 2012 01:44:31 AM Total Views:  441
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Finding the location of a particular record in a paginated query

I have a situation with a paginated record set where 10 records are presented on a page with the ability to go backwards and forwards through the records. Each of the records has it's own url identifier so each is able to be bookmarked. When someone comes back in and say they have bookmarked a record on page 7 of 15 and I want present the paginated result set and show their record on page 7 or whatever page it is currently on, is there a way to find out what page it is on. Understand that the page may change from time to time depending on whether the total number of records changes. So saving the original page number doesn't help. On the positive side I suppose, the record counts of the result sets are in the hundreds so if necessary I can always retrieve a limited set of information and parse it to find the location. I am just curious if there was a way to accomplish this with a query.
VIEWS ON THIS POST

101

Posted on:

Thursday 25th October 2012
View Replies!

case and order by issue

I am trying to use the results of a case statment as the ordering mechanism in my query. it errors saying no such column as 'stuff'. what is I doing wrong, please Please note that this sequencing requirement is a different one from that being done by sequence_number column. Code MySQL: select fd.file_name_other_language , fd.file_heading , fd.file_text , case when fd.text_or_a_list = 'text' then 1 else 0 end as stuff , fs.sequence_number , fi.image from file_data AS fd inner join file_sequencing AS fs on fs.file_id = fd.file_id left outer join file_images AS fi on fi.id = fd.id where fs.business_id = 504 and fs.file_name_in_home_language = 'Home' and fs.live_from = ( select max(b.live_from) from file_sequencing as b where b.business_id = fs.business_id and b.live_from
VIEWS ON THIS POST

134

Posted on:

Thursday 25th October 2012
View Replies!

How in MySQL to check to see if two expression are equal irrespective of their case?

, How does one in MySQL check to see if two values are equal or not irrespective of their case I mean I have an expression like: X1 = 'Hotels in Boston' And a field in MySQL DB named 'keywords'. which can contain: 'hotels in boston' I want these two to match, irrespective of their case. How do I do this via a query command like: Code MySQL: SELECT COUNT(id) AS kw_ads FROM keywords WHERE keywords = '$X1'
VIEWS ON THIS POST

165

Posted on:

Thursday 25th October 2012
View Replies!

sql case question, simple one

. I could really use some help. does anyone know how to do the following: I have table1 which has a field called "customized". but, the value of "customized" for a given record can be either a string or the value of an id of a catalog. i need to make a query so that, if i want to select some rows with some conditions, if the value of customized is an id that matches a catalog row, i select the description of the catalog that matches the id, and if there is no match, i want to select the text of the "customized" field. I dont have a mysql environment at hand, but i thought it could go like this: select field1, field2, field3, case WHEN cat.description IS NULL THEN tn.customized ELSE cat.description END, from myTable tn left join catalog cat on tn.customized = cat.id where tn.field1 = 5 would it work if not, can anyone post code to show me how to do it
VIEWS ON THIS POST

164

Posted on:

Thursday 25th October 2012
View Replies!

Trying to set up MySQL for the first time

Not sure if this helps My MySQL settings Quote: mysql> \s -------------- mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using readline 5.0 Connection id: 2 Current database: Current user: Brad@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.27 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 49 min 22 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.001 --------------
VIEWS ON THIS POST

78

Posted on:

Monday 29th October 2012
View Replies!

Where is mysql.server

Hi folks, CentOS 4.4 amd64 I'm following; Virtual Users With Postfix, PostfixAdmin, Courier, Mailscanner, ClamAV On CentOS http://www.howtoforge.com/virtual_u...lamav_centos_p4 to build the server. Coming to P-4 # /etc/init.d/mysql.server start Code: bash: /etc/init.d/mysql.server: No such file or directory # /etc/init.d/mysqld status Code: mysqld (pid 3164) is running... MySQL is running. At the bottom of P-4 CentOS manner: /etc/init.d/mysql.server start /etc/init.d/saslauthd start /etc/init.d/courier-authlib start /etc/init.d/courier-imap start /etc/init.d/postfix start The other 4 commands worked without problem Where is mysql.server TIA B.R. satimis
VIEWS ON THIS POST

141

Posted on:

Monday 29th October 2012
View Replies!

Update a Field

Everyone,, What I am trying to do is: I have a table called main and a table called salmatadelaide. On salmatadelaide, there is a field called Result, which the user has 3 choices. Successful, Unsuccessful and No Show. When they select a choice, I'd like that choice to update the Result field on the main table. Here is the code I have so far, obviously it fails :-( UPDATE main INNER JOIN main ON main.Result = salmatadelaide.Result SET main.Result = salmatadelaide.Result WHERE main.Result = ''"
VIEWS ON THIS POST

65

Posted on:

Monday 29th October 2012
View Replies!

MySQL Administrator wont let me in

Usually i use localhost 3306 root letmein and i get in no problem so today i am running my own home based server and cant get in to MySQL administrator i even change localhost to my Machine IP (error 1005 access denied for root@localhost using password YES... then i get access denied for my machine ip I only have this problem now that i am runnming a web server and nothing is localhost any more i am new at this so be nice lol DB
VIEWS ON THIS POST

69

Posted on:

Monday 29th October 2012
View Replies!

A union with a limit clause

suppose I have a table of a single column of uid. values are as follows: 1,2,3,4,5,6,7,8,9 suppose I have a second table of friends two columns uid and fid the values I have are (1,2),(1,3),(1,8). I want to do a union on the two tables such that all friends of uid =1 are returned and additional rows from , but I want a total of only 7. Does the query below written in this manner assure that the friends will always be returned and a random sampling of else will be returned Code: select * from ( (select fid from friends where uid=1) union (select uid from where uid != 1 order by rand() ) ) as DT limit 7 I want to ensure that all friends of user1 are returned in the results. If they have 7 friends I don't want any other users to show up. If they only have 3 friends then I want four random values to bring the total up to 7.
VIEWS ON THIS POST

128

Posted on:

Monday 29th October 2012
View Replies!

Duplicating tables help.

I am looking to duplicate a bunch of tables from one DB to another (on the same server) and in my research I found this method of doing it: CREATE TABLE destination_db_name.destination_table_name SELECT * FROM source_db_name.source_table_name HOWEVER after I thought that this would work perfectly, I now realise it doesn't duplicate indexes and autonumbers etc... Is there anyway of doing that so that will duplicate that too I want the duplicate table in the other DB to be a FULLY duplicate table. My MySQL is MySQL 4.0.26-standard
VIEWS ON THIS POST

185

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Selecting next record

Quote: Originally Posted by r937 those arrays don't mean a thing to me, i don't do php the reason they are "blank" is probably because there is no id that satisfies the WHERE clause in the subquery for example, in your original sample data, what is the next_id for flah.jpg answer: NULL p.s. why are you looking at a DB2 site if you're using mysql well.. that db2 site.. helped me understand it abit anways.. the problem is that... lets say.. one image belongs to lets say... categorie_one wich has an id of 1 right now.. lets say there is another image that belong to another categorie.. now this image wont get the id.. but yet your query is correct.. but anyways.. i will keep playing around with it and see what i can do.
VIEWS ON THIS POST

60

Posted on:

Monday 29th October 2012
View Replies!

How to send to subcribers when listing is inserted by other users using mysql & email

Hi I have a table for users and one for listings. I am wanting the users to fill out an email alert subscription where they may be able to select preferences of listings worked out by location and category, to be emailed to them. Does anybody have any sugestions on what mysql query to write to let this happen. I know how to send the email using php. I think I need to write a query after users have inserted their listing advert and somehow mysql to determine which subscribers suit this listing for an email to get sent to them. Would it be SELECT* FROM users,subscription WHERE users.userid LIKE subscription.stateCode AND regid.regid LIKE subscription.class_category I wonder if this is joining to the listing that is being inserted at the time Hope it makes sense.
VIEWS ON THIS POST

142

Posted on:

Monday 29th October 2012
View Replies!

Export Stored Procedures

How can I export my stored procedures I couldn't find anything in the documentation or on Google.
VIEWS ON THIS POST

52

Posted on:

Monday 29th October 2012
View Replies!

Need help on WHERE clause

I recently purchased a forum from http://www.webwizguide.com/webwizforums/ I need to modify the SQL slightly as when i click a link to one of my forums within my main forum, I only want that Category forum to show. eg; default.aspc=1 to only show Category 1 forum Code:
VIEWS ON THIS POST

167

Posted on:

Wednesday 7th November 2012
View Replies!

acess another sql server

How can I access another sqlserver database table with a SQL statement In the same database, one can access by ..
VIEWS ON THIS POST

86

Posted on:

Wednesday 7th November 2012
View Replies!

Dictionary in the Application object

Originally posted by : Paul Distant (pdistant@linneydesign.com)In my application I'm trying to prevent a user logging into the application twice. I have the idea of using the dictionary object and storing it in the application object. Does anyone know if this is possible If so howCheers,Paul
VIEWS ON THIS POST

75

Posted on:

Wednesday 7th November 2012
View Replies!

**Large database with 30,000 hit at a time ** HELP !!!!!

Originally posted by : Salil Kumar Ghosh (mcc@cal.vsnl.net.in)If I'm going to have a 30,000 hit at a time in my website with a database of 0.4 million records what kind of platform will be ideal. a. NT with SQL server b. Unix with Oracle Database c. Linux with Oracle DatabaseAfter the site is ready, how do I test to ensure that it can handle the expected traffic of 30,000 hits Many
VIEWS ON THIS POST

87

Posted on:

Wednesday 7th November 2012
View Replies!

New to the Sql server

Hi , Iam new to the SQL server.Whats the best way to learn and practice t-sql,sql queries(now iam learning syntax).Can anyone please guide me.
VIEWS ON THIS POST

85

Posted on:

Saturday 10th November 2012
View Replies!

SQL INSERT and Numbers

Originally posted by : Mike Horton (mhorton@planet.eon.net)I'm using a form to get information to insert into a database. I know from using Response.Write that the information passed is correct but I keep getting the following error: Syntax error in INSERT INTO statement.Here's my INSERT code:SQLstmt = "INSERT INTO Drivers (First Name,Last Name,Country,Helmet)"SQLstmt = SQLstmt & " VALUES (" SQLstmt = SQLstmt & "'" & First_Name & "',"SQLstmt = SQLstmt & "'" & Last_Name & "',"SQLstmt = SQLstmt & "'" & Country & "',"SQLstmt = SQLstmt & "'" & Helmet & "'"SQLstmt = SQLstmt & ")"Now Country and Helmet are numbers (I've got them tied into relationships). I've used this exact same code when all the fields were text and it worked great. I've also seen where you use a # around a date so I've tried that with now success. Any help would be greatly appreciatted. BTW there's also a Yes/No field in the table. Does that have to be filled on the INSERT statement
VIEWS ON THIS POST

60

Posted on:

Saturday 10th November 2012
View Replies!

data display from a drop down

Originally posted by : Julie (julie@hangtownwebdesign.com)I could sure use a little help here. I have a drop down menu that offers 6 choices that all works fine but when I tell it to display my info, I have a set criteria that I want displayed. For instance.......... I offer Vehicles, Cars, Trucks, Vans, Sport Utility and Sport Cars in the drop down. You click on one and only those requested SHOULD show. Instead it gives all my cars in the data base. I have three tables made in the database. In the first table I fields that have the same fields Cars, trucks ect.... in each one I labeled all vehicles 1, cars 2, trucks 3 ect... what am I doing wrong
VIEWS ON THIS POST

89

Posted on:

Saturday 10th November 2012
View Replies!

Please help! Stored procedures

Hi , I'm using Stored procedure in SQL Server 2005 to store record in a temp table before generating a report. From this stored procedure, after import some data into the temp table, I will to delete some records if those records in the temp table has type no. "90" and the top record in Source table also has type no. "90", those records should be deleted. If VB code, this is how it goes: cTemp = "select serialnumber, partnumber from OP_TempTable where type = '90'" RSTemp.open ctemp,globalsqlconn,adopenstatic, adlockreadonly, adcmdtext if not rstemp.eof then do until rstemp.eof RSSource.open "select top(1) type from OP_Partsmovement where partnumber = '" & rstemp.fields(1) & "' and serialnumber = '" & rstemp.fields(0) & "' and (type = "90" or type = "41") order by docdate desc, lastupdatedate desc", globalsqlconn, adopenstatic, adlockreadonly, adcmdtext if not rssource.eof then if rssource.fields(0) = "90" then rstemp.delete end if end if rssource.close rstemp.movenext loop end if rstemp.close But I need to create a SQL statement in stored procedure for the above action. I have tried some coding in stored procedure but it does not work. Here's the wrong sql statement: delete from OP_temptable where type = '90' and (select top(1) type from sourcetable where (sourcetable.type = '90' or sourcetable.type = '41') and sourcetable.partnumber = OP_temptable.partnumber and sourcetable.serialnumber = OP_temptable.serialnumber order by docdate desc, lastupdatedate desc) = '90' Can anyone help me with in I have searched for days but no solution.
VIEWS ON THIS POST

96

Posted on:

Sunday 11th November 2012
View Replies!

help optimizing query

I have a query that is taking about 12 minutes to run. I'm sure there has to be a way to speed it up...i'm just not sure how. any suggestions on how to rewrite this would be much appreciated. i think it has something to do with the "not in" join. Select Distinct T.TSNUMB from TSR T where T.TSSTAT='C' and T.TSCDAT >= '01-Oct-03' and T.TSCDAT
VIEWS ON THIS POST

150

Posted on:

Sunday 11th November 2012
View Replies!

paging with stored procedures with parameters

Originally posted by : Oliver (oliver@idea-uk.com)How do you use recordset paging if you are using a stored procedure that has parameters.ADO recordsets don't use paramteter so I am using the command object to pass in the paramters.The paging code works if there are no input paramsbut if they are it will not pass the params through the spCan anyone help
VIEWS ON THIS POST

128

Posted on:

Sunday 11th November 2012
View Replies!

Convert nvarchar to data type money

its peebman2000, semi-beginner programmer. I'm currently building a asp.net ticketing system for work, that capture $ figures. I'm using sql 2005 and in my data type for figures is money and i'm displying the figures in a form view. Im not doing any additional data binding on the form view and I'm casting the column (dollars) as cast(dollars as decimal(18,2)) as dollar to show as 2.00. I'm using the formview to allow the user to UPDATE that column and when I hit the update link button I get and error: DISALLOWES IMPLICIT CONVERSION FROM DATA TYPE NVARCHAR TO DATA TYPE MONEY, TABLE GOVEFFICIENCY.DBO.MONEYSAVE, COLUMN DOLLAR. uSE THE CONVERT FUNCTION TO RUN QUERY oKAY SO I guess I have to convert the varchar to money. I've tried convert(varchar, convert(money, @dollar,1) and it didn't. Does anyone know how to convert a varchar to money in a update statement Please share the knowledge and
VIEWS ON THIS POST

92

Posted on:

Monday 12th November 2012
View Replies!

Newbie mysql question

I have successfully connected to a mysql database from an asp page, both of them running on a web hotel. But I can't get it to work on my own web server, all I get is this error message: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Driver Manager] No data source and no Standard driver (translated from Swedish...) /mysql/inc_first_page.asp, line 3 Line 3 looks like this: Code: myConn.Open("DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=bah;UID=userid;PWD=password;") And yes, the database exists and the userid and password are correct. Do I need to install the drivers or what
VIEWS ON THIS POST

99

Posted on:

Monday 12th November 2012
View Replies!

Converting from hours/minutes to seconds

I have a varchar field that contains values like 00:17:17, 01:04:47 which symbolize the time. How can I convert this to seconds 00:17:17 --> 1037 seconds 01:04:47 --> 3887 seconds
VIEWS ON THIS POST

113

Posted on:

Monday 12th November 2012
View Replies!

Access fields called into asp page--how do I make hyperlinks?

Originally posted by : Austin (harrisaj@muohio.edu)I am designing a webpage for a project in one of my classes. We are setting up a database using MSAccess for a movie theater and we are going to create a website that will show movie titles, listings, and capacity in the different theaters. I figured out how to call the movie titles into the page but I can't figure out how to get those movies to link to another asp page that just shows the listings for that movie. Ex. If Cast Away is a movie, I want it to be linked to Cast Away.asp which has its listings and so on. Can anyone help me out
VIEWS ON THIS POST

122

Posted on:

Thursday 15th November 2012
View Replies!

applet to read from lotus notes

Originally posted by : jeff (jeffpaul@hotmail.com)hey,i need to create an applet that can read from a lotus notes database. i've tried to find information on how to do this, but have failed. does anyone know of a website or know themselves anything about this topic
VIEWS ON THIS POST

48

Posted on:

Thursday 15th November 2012
View Replies!

sql 70-228 cert

I am going to be taking the sql certification test in the 3 months I was wondering if anyone had any suggestions on what books I should use as a study guide. I already purchased a meassure up practice test
VIEWS ON THIS POST

78

Posted on:

Monday 19th November 2012
View Replies!

Copying a Recordset ...

Originally posted by : Yves (ypetinot@rand.com)I want to copy a Recordset in my Session object, therefore I'm using Set ... still it only provides a reference to this object and not a copy of it ..Has anyone an idea on how to do this
VIEWS ON THIS POST

58

Posted on:

Tuesday 20th November 2012
View Replies!

SQL - 3 table join

Sorry folks, you can ignore this post (I can't delete it), I found another problem so I'll have to go a different way. Cheers though.
VIEWS ON THIS POST

130

Posted on:

Tuesday 20th November 2012
View Replies!

Discrepancy between '9/30/2006' and '09/30/2006'

What is the difference, in SQL My queries are coming out weird. Code: SELECT * FROM table WHERE '9/1/2006' > myDate Update: I've been alerted that perhaps this is turning into a string comparison. In that case, what is the best way to test past/future CAST
VIEWS ON THIS POST

58

Posted on:

Tuesday 20th November 2012
View Replies!

Bit data type displaying as '-1' ??

, I'm confused about the bit datatype. I have two tables that I'm accessing in different ways. The first is an XSL transform. When creating an XML file from a recordset and then transforming it, bit values show as '-1' for True. However, when accessed from a script which turns the recordset into an XML file (without transform, just a feed) its value is 'True'. What gives!
VIEWS ON THIS POST

125

Posted on:

Tuesday 20th November 2012
View Replies!

Help with some queries

hi every one I am making a project for school its called petstore I have created all the tables and joind them (normalized) then I inserted sample data the tables are: sale saleItem animals customers animalorder breed saleanimal supplier orderitem merchandise category city I made initial quiries with SQLplus but I am trying to modify these queries to answer the following questions: 1: List the cats born in May 2:What is the total value of animals sold in December 3:How many cats were sold in October 4:What was the most expensive item sold in July 5:Which cats sold for at least 50 percent more than their cost 6:For each merchandise supplier, what is the average shipping cost 7:List the products with a list price greater than the average list price of all products. 8:Which customer has given us the most total money for animals and merchandise 9:Which customers who bought more than $100 in merchandise in May also spent more than $50 on merchandise in October thats all I know they are pretty much and your help will be appreciated. thanx.
VIEWS ON THIS POST

82

Posted on:

Tuesday 20th November 2012
View Replies!

Normalization Question

I'm just trying to get some opinions on something. I have a base table [ACCOUNT]. To simplify the question I'll say the table only has three columns [ACCOUNTID],[FIRMID] and [SPONSORID]. [ACCOUNTID] is the PK [FIRMID] is a FK pointing to a table called [FIRM] [SPONSORID] is a FK pointing to a table called [SPONSOR] In the [SPONSOR] table there are names like ABC. Sponsor has a relationship with firm in that Sponsors can have many firms but the firm could also be the sponsor. Ex. Sponsor ABC can have a firm of ABC, DEF, GHI My thinking is that since they are different entities they should be in seperate tables, even though there may be duplicate entries such as ABC across the two tables. One of my co-workers argues that if the name changes or something to the sponsor you then need to update the firm as well. So if ABC changes to 123 the firm will need to be changed to 123 as well. Therefore FIRMs and SPONSORs should be combined. Your opinions would be appreciated. Thank You
VIEWS ON THIS POST

78

Posted on:

Tuesday 20th November 2012
View Replies!

Using Variable in a Where clause

I have an SQL statement which works when I hard code the name and comes up with a syntax error when I use a variable Can anyone help me out Select companyid from table1 where medname= &rsmedi The error shown is as follows" Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'medname= &rsmedi'. Thankyou all, M
VIEWS ON THIS POST

210

Posted on:

Tuesday 20th November 2012
View Replies!

Oracle tool for PL/SQL Developers

I found this software DreamCoder for Oracle and it is fantastic for PL/SQL Developers. URL : http://www.mentattech.com
VIEWS ON THIS POST

102

Posted on:

Tuesday 20th November 2012
View Replies!

WHERE Member_Num = & XMreadCookie("memberCookie") ???

.
VIEWS ON THIS POST

72

Posted on:

Tuesday 20th November 2012
View Replies!

automatic updation of one table if data inserted in another table

Originally posted by : vegobha (vegobha@hotmail.com)i am using sql7Is it possible to insert the values of some columns of one table into another table automatically. If so guide me. For ex. I have a table table1 with 5 columns (a,b,c,d,e)i inserted data into them. simultaneously i need to insert 3 columns data (say a,b,e) into table2
VIEWS ON THIS POST

77

Posted on:

Tuesday 20th November 2012
View Replies!

how to use connect string while connecting to informix database useing odbc

Originally posted by : Ram (rbsaroj@rediffmail.com)hello i am faceing problem in connectstringi am able to connect informix rdbms databse by useing odbc from visual basic . But i am not ableto connect to databse by useing same odbc connect string through my ASP page . Can anybody help me with sending exact syntax my email id is rbsaroj@rediffmail.co
VIEWS ON THIS POST

134

Posted on:

Tuesday 20th November 2012
View Replies!

How to refresh the right side frame in the window?

Originally posted by : carlos (carlos_lai@hongkong.com)i have design a web page that is divided into two frames. Left side had a text box and a submit button. I want to refresh right frames when the text box is empty and the user click the submit button. Could anyone suggest some method for me I'm pleading for a help. Thank You.
VIEWS ON THIS POST

111

Posted on:

Tuesday 20th November 2012
View Replies!

Query help

Is there anything wrong about this query strSQL = "SELECT source, message, cdate, ctime From alarms WHERE (SELECT p1.source, p1.message, p1.cdate, p1.ctime FROM alarms as p1 WHERE p1.ctime = (SELECT max(ctime) FROM alarms WHERE source=p1.source AND cdate=p1.cdate) ORDER BY cdate DESC, ctime DESC) AND message LIKE '%Node Down%'" I think it may be overboard...and I'm getting an error "Operand should contain 1 column(s)" But I'm experimenting with this query to try and get what I want and wanted to see if this error was just something minor or not.
VIEWS ON THIS POST

98

Posted on:

Tuesday 20th November 2012
View Replies!

Wiritng to diff tables

hi im completely new at this, so pls bear with me my database front and backend are Access 2002. what i want to know is whether SQL will let me write to multiple tables from one form the prob im having with access is that the subforms that are being produced are not what i need - i need a single page form, with controls that write to 4 diff tables. i was thinking that if i simply created the form with the fields, and then use sql statements as the controls to write to the diff tables is this possible. / feasible
VIEWS ON THIS POST

65

Posted on:

Tuesday 20th November 2012
View Replies!

Update limit in a stored procedure

I have a table with more then 20,000 records. this table has a record_id column. I want to change the record_id values based on a different sorting. I wrote a stored procedure using a cursor running over all the records in that table (in the new order) and then for each record assign a new record_id. when I run the stored procedure without the update, I get all 22,798 records. when I run it with the update I get only 4,838 records. Is there a limit to the amount of record a stored procedure can update can it be changed STORED PROCEDURE: CREATE PROCEDURE sft_sort_table as BEGIN DECLARE @record_ID INT DECLARE @MAX_record_ID INT DECLARE @CUR_RECORD_ID INT --Get the max rate_record_id SELECT @MAX_record_ID INT = max(record_id) FROM table DECLARE curRecord CURSOR FOR select record_id from table order by new_sort FOR UPDATE OF record_id OPEN curRecord FETCH NEXT FROM curRecord INTO @CUR_RECORD_ID WHILE @@FETCH_STATUS = 0 BEGIN SET @MAX_RECORD_ID = @MAX_RECORD_ID + 1 PRINT 'Record ' PRINT @CUR_RECORD_ID Print 'will be ' PRINT @MAX_RECORD_ID PRINT '-----------------------------' --Update the record_id UPDATE table SET record_id = @MAX_RECORD_ID WHERE current of curRecord FETCH NEXT FROM curRecord INTO @CUR_RECORD_ID END CLOSE curRecord; DEALLOCATE curRecord; END;
VIEWS ON THIS POST

72

Posted on:

Monday 26th November 2012
View Replies!

order by an alias?

I have problems with the following sql query: SELECT c.CarNr, c.Make, c.OutTo, c.OutOn, (t.Date - c.OutOn)+1 AS Days, c.Cost, Days * c.Cost AS Total FROM Cars c, Today t WHERE c.OutOn null ORDER BY Total Asc I get the error: Too few parameters. Expected 1. If I try to order by c.Cost it works. Is there a problem with ordering on an alias It seems like it doesn't find it.. I hope someone can help me
VIEWS ON THIS POST

104

Posted on:

Monday 26th November 2012
View Replies!

how to conver access into sql?

to I'm new and i'm happy that i have found this. I had a question which might have been asked many times: how do I convert access codes into sql I have this data base in access which needs to be in sql . I wanted to know if i have to write codes or something else. I have office xp and sql server 2000 personal edition. hope to get an answer very soon.
VIEWS ON THIS POST

436

Posted on:

Monday 26th November 2012
View Replies!

MySQL Workbench and Primary Keys

I am using MySQL workbench for the first time. I have created an InnoDB relational database. When i connect two tables with a many to one connection it automatically adds a new row in the many table that references the row_id in the other table. When it adds this new row it inserts it as a Primary row. So now i have a table with 2 primary rows. Is this how things should be setup I though every table should only have one primary row Should i set the newly added relational row so that it's not a primary row On a side note. I am using phpMyAdmin through MAMP. I have read some tutorials about connecting MySQL workbench to MAMP's phpMyAdmin. In the tutorials it states that i should configure MySQL workbench to use the file located at /Applications/MAMP/tmp/mysql/mysql.sock but when i go to this location their is no file with this name (http://phpprotip.com/2011/10/using-m...nch-with-mamp/). Anyone know where i can find this file
VIEWS ON THIS POST

72

Posted on:

Friday 28th December 2012
View Replies!

DATE range calucation optimization

I would like to find out if someone can help me to optimize the date range calculation in the following query: Code: SELECT USERID, birthday FROM members WHERE birthday BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 0 YEAR) OR birthday='0000-00-00' ; I want a speedy birthday comparison over huge users database. Thank you. , See here http://stackoverflow.com/questions/4...rformance-over. I don't see anything that will help other than POSSIBLY allowing nulls in birthday and switching the default value to NULL so that you are searching for birthday is null rather than birthday = '0000-00-00'
VIEWS ON THIS POST

171

Posted on:

Saturday 29th December 2012
View Replies!

[MySQL 5.0.45-community-nt] JOIN Syntax

Hi all, I hope your help. I have this two tables in MySQL: Code: DROP TABLE IF EXISTS `tbl_1`; CREATE TABLE `tbl_1` ( `id` int(10) NOT NULL AUTO_INCREMENT, `MAC` varchar(3) DEFAULT NULL, `REG` varchar(3) DEFAULT NULL, `CODE` varchar(10) DEFAULT NULL, `NAME` varchar(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; INSERT INTO `tbl_1` VALUES ('1', 'AAA', 'TOS', '380264', 'VF'); INSERT INTO `tbl_1` VALUES ('2', 'BBB', 'TOS', '380264', 'VF'); INSERT INTO `tbl_1` VALUES ('3', 'CCC', 'TOS', '380264', 'VF'); INSERT INTO `tbl_1` VALUES ('4', 'DDD', 'TOS', '380264', 'VF'); DROP TABLE IF EXISTS `tbl_2`; CREATE TABLE `tbl_2` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `CODE` varchar(10) DEFAULT NULL, `matr` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; INSERT INTO `tbl_2` VALUES ('1', '380264', '112036'); If tried this query: Code: SELECT * FROM tbl_2 WHERE Matr = '112036' AND Code = '380264'; I have this output: Code: ID CODE MATR 1 380264 112036 If tried this join query: Code: SELECT a.Matr, c.Name, COUNT(*) `number` FROM tbl_2 a JOIN tbl_1 c ON a.Code = c.Code GROUP BY c.Code, Matr, a.Code; I have this other wrong output: Code: Matr Name number 112036 VF 4 I need this right output: Code: Matr Name number 112036 VF 1 Group for c.Code and a.Code is not sufficient Can you help me
VIEWS ON THIS POST

96

Posted on:

Saturday 29th December 2012
View Replies!