Search Results

Search found 22463 results on 899 pages for 'sub query'.

Page 179/899 | < Previous Page | 175 176 177 178 179 180 181 182 183 184 185 186  | Next Page >

  • How does mysql define DISTINCT() in reference documentation

    - by goran
    EDIT: This question is about finding definitive reference to MySQL syntax on SELECT modifying keywords and functions. /EDIT AFAIK SQL defines two uses of DISTINCT keywords - SELECT DISTINCT field... and SELECT COUNT(DISTINCT field) ... However in one of web applications that I administer I've noticed performance issues on queries like SELECT DISTINCT(field1), field2, field3 ... DISTINCT() on a single column makes no sense and I am almost sure it is interpreted as SELECT DISTINCT field1, field2, field3 ... but how can I prove this? I've searched mysql site for a reference on this particular syntax, but could not find any. Does anyone have a link to definition of DISTINCT() in mysql or knows about other authoritative source on this? Best EDIT After asking the same question on mysql forums I learned that while parsing the SQL mysql does not care about whitespace between functions and column names (but I am still missing a reference). As it seems you can have whitespace between functions and the parenthesis SELECT LEFT (field1,1), field2... and get mysql to understand it as SELECT LEFT(field,1) Similarly SELECT DISTINCT(field1), field2... seems to get decomposed to SELECT DISTINCT (field1), field2... and then DISTINCT is taken not as some undefined (or undocumented) function, but as SELECT modifying keyword and the parenthesis around field1 are evaluated as if they were part of field expression. It would be great if someone would have a pointer to documentation where it is stated that the whitespace between functions and parenthesis is not significant or to provide links to apropriate MySQL forums, mailing lists where I could raise a question to put this into reference. EDIT I have found a reference to server option IGNORE SPACE. It states that "The IGNORE SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive", later on it states that recent versions of mysql have reduced this number from 200 to 30. One of the remaining 30 is COUNT for example. With IGNORE SPACE enabled both SELECT COUNT(*) FROM mytable; SELECT COUNT (*) FROM mytable; are legal. So if this is an exception, I am left to conclude that normally functions ignore space by default. If functions ignore space by default then if the context is ambiguous, such as for the first function on a first item of the select expression, then they are not distinguishable from keywords and the error can not be thrown and MySQL must accept them as keywords. Still, my conclusions feel like they have lot of assumptions, I would still be grateful and accept any pointers to see where to follow up on this.

    Read the article

  • Most commonly occurring string in mysql column.

    - by MILESMIBALERR
    I am making a website where users can vote on which category a page is. They can vote that the page is category, for example: a, b, c, or d. Please don't ask what I am using this for, it is not important, I just want to know how to do it. I need to find the most commonly occurring category in the mysql row out of all the votes. Each time a user submits their vote, it submits the "category" that they voted for, and the "page_id". I have this so far: select page_id, category from categories group by page_id You cannot simply use a "COUNT(*) where category = 'a'" then repeat it for each category because there is many more categories in the actual project.

    Read the article

  • Row Number for group by ?

    - by Damien Joe
    I have table structure like Category EmpName 1 Harry 1 John 1 Ford 2 James 2 Mark 2 Shane 3 Oliver 3 Ted I want results like Category EmpName RowNumber 1 Harry 1 1 John 2 1 Ford 3 2 James 1 2 Mark 2 2 Shane 3 3 Oliver 1 3 Ted 2 I am using db2 and row_number() is not working for different groups of records.

    Read the article

  • iPhone SDK mySQL

    - by Kevin
    Hello everybody, I'm starting on a new iPhone project, and this application mostly relies on mySQL. I have a mysql database running on my computer, and I need this application to send queries to the server to gain information. One example is creating and logging into an account. I have successfully done this on my windows vb.net application, but I know that objective c will be harder. This is basically getting text from the sql database and displaying it on the iPhone application. If someone could simply help me on that, I'll easily get started. So I hope that you guys help me out here :). Thanks Kevin

    Read the article

  • Using dispatchertimer in combination with an asynchroneous call

    - by Civelle
    Hi. We have an issue in our Silverlight application which uses WCF and Entity Framework, where we need to trap the event whenever a user shuts down the application by closing the web page or the browser instead of closing the silverlight application. This is in order to verify if any changes have been made, in which case we would ask the user if he wants to save before leaving. We were able to accomplish the part which consists in trapping the closing of the web page: we wrote some code in the application object that have the web page call a method in the silverlight application object. The problem starts when in this method, we do an asynchroneous call to the Web Service to verify if changes have occured (IsDirty). We are using a DispatcherTimer to check for the return of the asynchroneous call. The problem is that the asynchroneous call never completes (in debug mode, it never ends up stepping into the _BfrServ_Customer_IsDirtyCompleted method), while it used to work fine before we added this new functionality. You will find belowthe code we are using. I am new to writing timers in combination with asynchroneous call so I may be doing something wrong but I cannot figure out what. I tried other things also but we without any success.. ====================== CODE ============================================== 'Code in the application object Public Sub New() InitializeComponent() RegisterOnBeforeUnload() _DispatcherTimer.Interval = New TimeSpan(0, 0, 0, 0, 500) End Sub Public Sub RegisterOnBeforeUnload() 'Register Silverlight object for availability in Javascript. Const scriptableObjectName As String = "Bridge" HtmlPage.RegisterScriptableObject(scriptableObjectName, Me) 'Start listening to Javascript event. Dim pluginName As String = HtmlPage.Plugin.Id HtmlPage.Window.Eval(String.Format("window.onbeforeunload = function () {{ var slApp = document.getElementById('{0}'); var result = slApp.Content.{1}.OnBeforeUnload(); if(result.length 0)return result;}}", pluginName, scriptableObjectName)) End Sub Public Function OnBeforeUnload() As String Dim userControls As List(Of UserControl) = New List(Of UserControl) Dim test As Boolean = True If CType(Me.RootVisual, StartPage).LayoutRoot.Children.Item(0).GetType().Name = "MainPage" Then If Not CType(CType(Me.RootVisual, StartPage).LayoutRoot.Children.Item(0), MainPage).FindName("Tab") Is Nothing Then If CType(CType(Me.RootVisual, StartPage).LayoutRoot.Children.Item(0), MainPage).FindName("Tab").Items.Count = 1 Then For Each item As TabItem In CType(CType(Me.RootVisual, StartPage).LayoutRoot.Children.Item(0), MainPage).Tab.Items If item.Content.GetType().Name = "CustomerDetailUI" _Item = item WaitHandle = New AutoResetEvent(False) DoAsyncCall() Exit End If Next End If End If End If If _IsDirty = True Then Return "Do you want to save before leaving." Else Return String.Empty End If End Function Private Sub DoAsyncCall() _Item.Content.CheckForIsDirty(WaitHandle) 'This code resides in the CustomerDetailUI UserControl - see below for the code End Sub Private Sub _DispatcherTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles _DispatcherTimer.Tick If Not _Item.Content._IsDirtyCompleted = True Then Exit Sub End If _DispatcherTimerRunning = False _DispatcherTimer.Stop() ProcessAsyncCallResult() End Sub Private Sub ProcessAsyncCallResult() _IsDirty = _Item.Content._IsDirty End Sub 'CustomerDetailUI code Public Sub CheckForIsDirty(ByVal myAutoResetEvent As AutoResetEvent) _AutoResetEvent = myAutoResetEvent _BfrServ.Customer_IsDirtyAsync(_Customer) 'This method initiates asynchroneous call to the web service - all the details are not shown here _AutoResetEvent.WaitOne() End Sub Private Sub _BfrServ_Customer_IsDirtyCompleted(ByVal sender As Object, ByVal e As BFRService.Customer_IsDirtyCompletedEventArgs) Handles _BfrServ.Customer_IsDirtyCompleted If _IsDirtyFromRefesh Then _IsDirtyFromRefesh = False If e.Result = True Then Me.Confirm("This customer has been modified. Are you sure you want to refresh your data ? " & vbNewLine & " Your changes will be lost.", "Yes", "No", Message.CheckIsDirtyRefresh) End If Busy.IsBusy = False Else If e.Result = True Then _IsDirty = True Me.Confirm("This customer has been modified. Would you like to save?", "Yes", "No", Message.CheckIsDirty) Else Me.Tab.Items.Remove(Me.Tab.SelectedItem) Busy.IsBusy = False End If End If _IsDirtyCompleted = True _AutoResetEvent.Set() End Sub

    Read the article

  • PHP - Plus sign with GET query

    - by Nate Shoffner
    I have a PHP script that does basic encryption of a string through the method below: <?php $key = 'secretkey'; $string = $_GET['str']; if ($_GET['method'] == "decrypt") { $output = rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, md5($key), base64_decode($string), MCRYPT_MODE_CBC, md5(md5($key))), "\0"); } if ($_GET['method'] == "encrypt") { $output= base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, md5($key), $string, MCRYPT_MODE_CBC, md5(md5($key)))); } echo $output; ?> An example of a URL to encrypt a string would look like this: Encrypt.php?method=encrypt&str=the quick fox Which would return this as the encrypted string: LCuT/ieVa6cl3/4VtzE+jd9QPT3kvHYYJFqG6tY3P0Q= Now to decrypt the string all you have to do is change the "method" query to "decrypt", like so: Encrypt.php?method=decrypt&str=LCuT/ieVa6cl3/4VtzE+jd9QPT3kvHYYJFqG6tY3P0Q= The only problem is that when that encrypted string is decrypted it returns this: ¬ƒ§rYV}̳5Äš·nßì(ñïX8Þ;b I have narrowed down the problem to the plus sign that is in the encrypted string. PHP's GET method seems to translate a plus sign into a blank space. I have searched this bug and found out that it has already been filed here. I have tried different methods listed on that page and others with no success. The closest I got is by using this: $fixedstring = str_replace(" ", "+", $string); and then using $fixedstring in the encryption methods, the problem is, upon decryption, all blank spaces are converted to plus signs. Any ideas?

    Read the article

  • Integer in MySQL Subqueries in store procedure

    - by confiq
    I made simple procedure just to demonstrate CREATE PROCEDURE `demo`(demo_int int) BEGIN DECLARE minid INT; SELECT min(id) FROM (SELECT id FROM events LIMIT demo_int,9999999999999999) as hoo INTO minid; END$$ The problem is with demo_int, if i change it to LIMIT 1,9999999999999999 it works but LIMIT demo_int,9999999999999999 Does not... It gives 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 'demo_int,9999999999999999) as hoo INTO minid; END' at line 4 (errno: 1064) Any clues?

    Read the article

  • Code that Worked with MultiView fails with Wizard ASP.NET

    - by davemackey
    I originally created a process that occurred by transitioning between views in a multiview and it worked fine. Now, I've moved this same code into a ASP.NET Wizard and it keeps throwing an error at the second step. The error is: Method 'System.Object AndObject(System.Object, System.Object)' has no supported translation to SQL. Any ideas why this would occur when moving the code into the wizard? I'm sure its something stupid, but I've checked over the code 3-4 times now and it appears identical operationally. Here is the code: ' Make sure we have the LDAP portion of the .NET Framework available. Imports System.DirectoryServices ' Allows us to interface with LDAP. Imports System.Data.Linq.SqlClient ' Allows us to use LINQ SQL Methods. Partial Public Class buildit Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ' ******* Grab the LDAP info. for current user. Dim ID As FormsIdentity = DirectCast(User.Identity, FormsIdentity) Dim ticket As FormsAuthenticationTicket = ID.Ticket Dim adDirectory As New DirectoryEntry("LDAP://OU=[info],DC=[info],DC=[info],DC=[info]") ' We need to strip off @email.address from the ticket name, so we'll use substring to grab the first ' five characters. Dim adTicketID As String = ticket.Name.Substring(0, 5) Dim adEmployeeID As String adEmployeeID = adDirectory.Children.Find("CN=" & adTicketID).Properties("employeeID").Value ' ******* Lets make sure they have signed the housing contract and the community covenant. Dim dbContractSigs As New pcRoomOccupantsDataContext Dim pcContractSigs = From p In dbContractSigs.webContractSigs _ Where p.people_id = adEmployeeID _ Select p.res_contract, p.comm_life If pcContractSigs.Count.Equals(0) Then Response.Redirect("signcontract.aspx") Else Dim cs As String = pcContractSigs.First.res_contract.ToString Dim cos As String = pcContractSigs.First.comm_life.ToString If cs = "Y" And cos = "Y" Then ' We don't need to do anything. ' We use the else statement b/c there are multiple conditions that could occur besides "N" ' that would cause us to redirect to the signature page, whereas there is only one valid response - "Y". Else ' Redirect the individual to our contracts page. Response.Redirect("signcontract.aspx") End If End If ' ******* Now lets find out what gender that individual is. Dim dbIndividual As New pcPeopleDataContext Dim pcIndividual = From p In dbIndividual.PEOPLEs _ Join d In dbIndividual.DEMOGRAPHICs On p.PEOPLE_CODE_ID Equals d.PEOPLE_CODE_ID _ Where p.PEOPLE_ID = adEmployeeID _ Select p, d ' Make a session variable that will carry with the user throughout the session delineating gender. Session("sgender") = pcIndividual.First.d.GENDER.ToString ' Debug Code. ' Put a stop at end sub to get these values. ' Response.Write(adEmployeeID) End Sub Sub LinqDataSource1_Selecting(ByVal sender As Object, ByVal e As LinqDataSourceSelectEventArgs) ' Lets get a list of the dorms that are available for user's gender. Dim pcDorms As New pcDormsDataContext Dim selectedDorms = (From sd In pcDorms.PBU_WEB_DORMs _ Where IIf(Session("sgender").ToString = "M", sd.description = "Male", sd.description = "Female") _ Select sd.dorm_building).Distinct() e.Result = selectedDorms End Sub Public Sub Button_ItemCommand(ByVal Sender As Object, ByVal e As RepeaterCommandEventArgs) ' ******** Lets pass on the results of our query in LinqDataSource1_Selecting. Session("sdorm") = RTrim(e.CommandName) ' ******** Debug code. ' Response.Write(sDorm) End Sub Sub LinqDataSource2_Selecting(ByVal sender As Object, ByVal e As LinqDataSourceSelectEventArgs) ' ******** Get a list of rooms available in the dorm for user's gender. Dim pcDorms As New pcDormsDataContext Dim selectedDorm = (From sd In pcDorms.PBU_WEB_DORMs _ Where IIf(Session("sgender").ToString = "M", sd.description = "Male", sd.description = "Female") _ And sd.dorm_building = CStr(Session("sdorm")) _ Select sd.dorm_room) e.Result = selectedDorm End Sub Public Sub Button2_ItemCommand(ByVal Sender As Object, ByVal e As RepeaterCommandEventArgs) ' ******** Lets pass on the results of our query in LinqDataSource2_Selecting. Session("sroom") = RTrim(e.CommandName) End Sub Sub LinqDataSource3_Selecting(ByVal sender As Object, ByVal e As LinqDataSourceSelectEventArgs) ' ******** Grabs the individuals currently listed as residing in this room and displays them. Note the use of SqlMethods.Like ' for dorm_building, this is due to legacy issues where dorms sometimes have leading or trailing blank spaces. We could have ' also used Trim. Dim pcOccupants As New pcRoomOccupantsDataContext Dim roomOccupants = (From ro In pcOccupants.webResidents _ Where SqlMethods.Like(ro.dorm_building, "%" & CStr(Session("sdorm")) & "%") _ And ro.dorm_room = CStr(Session("sroom")) _ Select ro.person_name) e.Result = roomOccupants ' ******** Debug code. 'Response.Write(CStr(Session("sdorm"))) 'Response.Write(CStr(Session("sroom"))) End Sub Protected Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button4.Click ' ******** Reserve the room for a student. End Sub End Class

    Read the article

  • Calling methods or functions with Jquery

    - by Ross
    So I can call a php page using jquery $.ajax({ type: "GET", url: "refresh_news_image.php", data: "name=" + name, success: function(html) { alert(html) $('div.imageHolder').html(html); } }); However this getting a bit messy, I have a few .php files that only really preform very simple tasks. If I want to call a method $images-refresh_image(); is this possible. Failing that I could just create a big file with lots of functions in it? Thanks, Ross

    Read the article

  • self join- how to use the aggregate functions

    - by Ranjana
    self join- how to use the aggregate functions select a.tablename, b.TableName,b.UserName from Employee a inner join Employee b on a.ColumnValue=b.ColumnValue and and a.TableName <> b.TableName and a.UserName=b.UserName and also to check whether the same user has count of records i.e Employee a = count of records of Employee b. how to add count function over here

    Read the article

  • VB.net (aspx) mysql connection

    - by StealthRT
    Hey all i am new to ASP.NET and VB.net code behind. I have a classic ASP page that connects to the mySQL server with the following code: Set oConnection = Server.CreateObject("ADODB.Connection") Set oRecordset = Server.CreateObject("ADODB.Recordset") oConnection.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxx.com; PORT=3306; DATABASE=xxx; USER=xxx; PASSWORD=xxx; OPTION=3;" sqltemp = "select * from userinfo WHERE emailAddress = '" & theUN & "'" oRecordset.Open sqltemp, oConnection,3,3 if oRecordset.EOF then ... However, i am unable to find anything to connect to mySQL in ASP.NET (VB.NET). I have only found this peice of code that does not seem to work once it gets to the "Dim conn As New OdbcConnection(MyConString)" code: Dim MyConString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=xxx.com;" & _ "DATABASE=xxx;" & _ "UID=xxx;" & _ "PASSWORD=xxx;" & _ "OPTION=3;" Dim conn As New OdbcConnection(MyConString) MyConnection.Open() Dim MyCommand As New OdbcCommand MyCommand.Connection = MyConnection MyCommand.CommandText = "select * from userinfo WHERE emailAddress = '" & theUN & "'"" MyCommand.ExecuteNonQuery() MyConnection.Close() I have these import statements also: <%@ Import Namespace=System %> <%@ Import Namespace=System.IO %> <%@ Import Namespace=System.Web %> <%@ Import Namespace=System.ServiceProcess %> <%@ Import Namespace=Microsoft.Data.Odbc %> <%@ Import Namespace=MySql.Data.MySqlClient %> <%@ Import Namespace=MySql.Data %> <%@ Import Namespace=System.Data %> So any help would be great! :o) David

    Read the article

  • MySql multiple selects batching in .net

    - by Amith George
    I have a situation in my application. For each x-axis point in my chart, I am plotting 5 y-axis values. To calculate each of these 5 values, I need to make 4 different queries. Ie, for each x-axis point I need to fire 20 sql queries. Now, I need to plot 40 such points in the my chart. Its resulting in a pathetic performance where it takes close to a minute to get all the data back from the database. Each of 4 different queries consists of a join between 2 tables. One has only 6 rows. The other close to 10,000. Each of the 4 queries has different WHERE clauses, so they are different queries. For each point in the x-axis, only the values for the where clauses change. I have tried combining each of the 4 queries into one big string. Basically batch the four selects. These are again batched for each y-axis value. So, for each x-axis point, I am now firing one big command that consists of 20 different select statements. Technically, I should be experiencing a big performance boost, right? Instead of hitting the db 40x5x4 = 800 times, I am now hitting it just 40 times. But instead of taking 60 seconds, it taking 50-55 seconds... not much of a help. I am using MySql 5.1, and the 6.1 version of its .Net connector. What can I do to improve the performance? Edit: One of the 4 queries is as follows: SELECT SUM(TIME_TO_SEC(TIMEDIFF(T1.col2, T1.col1))* T2.col1 / (3600 *1000)) AS TotalTime FROM Table T1 JOIN Table T2 ON T1.col3 = T2.col3 WHERE T1.col4 = 'i' AND T1.col1 >= '2009-12-25 00:00:00' AND T1.col2 <= '2009-12-26 00:00:00'; The other 3 queries are similar, only the where clause changes slightly. This set of 4 queries is fired 5 times. The first 3 times against the join of table T1 and T2, passing in different values for col4. And the next two times against the join of table T3 and T2 passing in different values for col4. These 5 values are the y-axis values for a particular x-axis point. The data returned by all these queries is the same format. so, we tried doing a UNION ALL on all these queries. No substantial difference. One strange thing, however, after indexing the foreign key on the table T1 [while it contained over a lakh records], the queries were using the index, but they had become slower. At times, the queries would take double the time to return the data.

    Read the article

  • Replace Temp with Query

    - by student
    The Replace Temp with Query refactoring method is recommended quite widely now but seems to be very inefficient for very little gain. The method from the Martin Fowler's site gives the following example: Extract the expression into a method. Replace all references to the temp with the expression. The new method can then be used in other methods. double basePrice = _quantity * _itemPrice; if (basePrice > 1000) return basePrice * 0.95; else return basePrice * 0.98; becomes if (basePrice() > 1000) return basePrice() * 0.95; else return basePrice() * 0.98; double basePrice() { return _quantity * _itemPrice; } Why is this a good idea? surely it means the calculation is needlessly repeated and you have the overhead of calling a function. I know CPU cycles are cheap but throwing them away like this seems careless? Am I missing something?

    Read the article

  • Aggregate survey results recursively by manager

    - by Ian Roke
    I have a StaffLookup table which looks like this. UserSrn | UserName | ManagerSrn =============================== ABC1 | Jerome | NULL ABC2 | Joe | ABC1 ABC3 | Paul | ABC2 ABC4 | Jack | ABC3 ABC5 | Daniel | ABC3 ABC6 | David | ABC2 ABC7 | Ian | ABC6 ABC8 | Helen | ABC6 The staff structure looks like this. |- Jerome | |- Joe || ||- Paul ||| |||- Jack ||| |||- Daniel || ||- David ||| |||- Ian ||| |||- Helen I have a list of SurveyResponses that looks like this. UserSrn | QuestionId | ResponseScore ==================================== ABC2 | 1 | 5 ABC2 | 3 | 4 ABC4 | 16 | 3 ... What I am trying to do sounds pretty simple but I am struggling to find a neat, quick way of doing it. I want to create a sproc that takes an Srn and returns back all the staff under that Srn in the structure. If there is a score for QuestionId of 16 then that indicates a completed survey. I would like to return a line for the Srn entered (The top manager) with a count of completed surveys for the direct reports under that manager. Under that I would like each manager under the original manager with a count of completed surveys for each of their direct reports and so on. I would like to see the data as such below when I set the top manager to be Joe (ABC2). UserName | Completed | Total ============================ Joe | 2 | 2 Paul | 1 | 2 David | 0 | 2 TOTAL | 3 | 6

    Read the article

  • Can't change data type on MS Access 2007

    - by khalidfazeli
    Hi All, I have a huge database (800MB) which consists of a field called 'Date Last Modified' at the moment this field is entered as a text data type but need to change it to a Date/Time field to carry out some queries. I have another exact same database but with only 35MB of data inside it and when I change the data type it works fine, but when I try to change data type on big database it gives me an error: Micorosoft Office Access can't change the data type. There isn't enough disk space or memory After doing some research some sites mentioned of changing the registry file (MaxLocksPerFile) tried that as well, but no luck :-( Can anyone help please?

    Read the article

  • MSAccess: select N records from each category

    - by Jaume
    I can't solve this on MS Access SQL. I have a foo table with this fields: id, title, category, date I need to show 6 records showing the 2 most recent items from each category For example, I have: ID TITLE CAT DATE ------------------------ 1 aaa cat1 12/03/12 2 sdfs cat2 12/03/12 3 asg cat2 13/03/12 4 flkgjfl cat3 11/03/12 5 dgdg cat1 18/03/12 6 dfgd cat1 15/03/12 7 dgdgd cat3 18/03/12 8 dfgd cat3 12/03/12 9 uiuou cat1 14/03/12 10 ghj cat4 11/03/12 So, I need something like: ID TITLE CAT DATE ---------------------------------- 5 dgdg cat1 18/03/2012 6 dfgd cat1 15/03/2012 3 asg cat2 13/03/2012 2 sdfs cat2 12/03/2012 7 dgdgd cat3 18/03/2012 8 dfgd cat3 12/03/2012 Thank you in advance.

    Read the article

  • ORA-00904: "FORMAT": invalid identifier

    - by gary A.K.A. G4
    I am trying to format a date: FORMAT(table.TCKT.TCKT_ISS_DATE, 'YYYY') AS TICKETYEAR but I am getting the following error: ORA-00904: "FORMAT": invalid identifier Right now the date show the complete timestamp. Any suggestions on how to fix this problem, or any other way to format the date to just show the four digit year?

    Read the article

  • How to store data which contents the quotes in mysql

    - by Nitz
    Hey Guys, i have one problem. In one of my form i have use rich text editor from the yahoo. now i want to store the data from that text area to mysql database. bcz user can enter anything in that textarea. as example user can enter many double quotes, or single quotes. so i need to store that data which may content many double quotes or many single quotes, so how to do that? normally we store by adding that data in one variable and then put that in sql then fire. but now variable contents many quotes and now i have problem to store. i can't remove that quotes bcz of my style which is generated by rich text editor. So how can store that data without affecting my styles of data.

    Read the article

  • Mysql : Request to select the last 10 send/received messages to/by different users

    - by Yako malin
    Hello, I want to select the 10 last messages you received OR you sent TO different users. For example the results must be shown like that : 1. John1 - last message received 04/17/10 3:12 2. Thomy - last message sent 04/16/10 1:26 3. Pamela - last message received 04/12/10 3:51 4. Freddy - last message received 03/28/10 9:00 5. Jack - last message sent 03/20/10 4:53 6. Tom - last message received 02/01/10 7:41 ..... Table looks like : CREATE TABLE `messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `sender` int(11) DEFAULT NULL, `receiver` int(11) DEFAULT NULL, `content` text ) Have someone any solution ? Thanks in advance for your help.

    Read the article

  • Using Oracle hint "FIRST_ROWS" to improve Oracle database performances

    - by bobetko
    I have a statement that runs on Oracle database server. The statement has about 5 joins and there is nothing unusual there. It looks pretty much like below: SELECT field1, field2, field3, ... FROM table1, table2, table3, table4, table5 WHERE table1.id = table2.id AND table2.id = table3.id AND ... table5.userid = 1 The problem (and what is interesting) is that statement for userid = 1 takes 1 second to return 590 records. Statement for userid = 2 takes around 30 seconds to return 70 records. I don't understand why is difference so big. It seems that different execution plan is chosen for statement with userid = 1 and different for userid = 2. After I implemented Oracle Hint FIRST_ROW, performance become significantly better. Both statements (for both ids 1 and 2) produce return in under 1 second. SELECT /*+ FIRST_ROWS */ field1, field2, field3, ... FROM table1, table2, table3, table4, table5 WHERE table1.id = table2.id AND table2.id = table3.id AND ... table5.userid = 1 Questions: 1) What are possible reasons for bad performance when userid = 2 (when hint is not used)? 2) Why would execution plan be different for one vs another statement (when hint is not used)? 3) Is there anything that I should be careful about when deciding to add this hint to my queries? Thanks

    Read the article

  • how to delete duplicates in mysql using case

    - by JPro
    Right now I am using something like this to delete duplicates in mysql table : delete t2 from my_table1 as t1, my_table1 as t2 where t1.TestCase = t2.TestCase and t2.id > t1.id; say I have a structure like this : ID TestCAse Result 1 T1 PASS 2 T2 FAIL 3 T3 FAIL 4 T3 PASS now, in the above case T3 is duplicate entry, and if I use the SQL that I mentioned above, it would delete 4th row where the result is PASS, but this is the row that I want to keep and I want row 3 to get deleted which is FAIL. Any help please? Thank you.

    Read the article

  • php edit database entery script not working.

    - by Jacksta
    I have two files which show all contats in a database, then edits them. The second script keeps directing the browser back due to this part. I cant work out how to make it work! :) <?php if (!$_POST[id]) { header( "Location: pick_modcontact.php"); exit; } else { session_start(); } if ($_SESSION[valid] != "yes") { header( "Location: pick_modcontact.php"); exit; } This script shows all contacts in a database which is an "address book" this part works fine. please see below. Name:pick_modcontact.php if ($_SESSION[valid] != "yes") { header( "Location: contact_menu.php"); exit; } $db_name = "testDB"; $table_name = "my_contacts"; $connection = @mysql_connect("localhost", "admin", "user") or die(mysql_error()); $db = @mysql_select_db($db_name, $connection) or die(mysql_error()); $sql = "SELECT id, f_name, l_name FROM $table_name ORDER BY f_name"; $result = @mysql_query($sql, $connection) or die(mysql_error()); $num = @mysql_num_rows($result); if ($num < 1) { $display_block = "<p><em>Sorry No Results!</em></p>"; } else { while ($row = mysql_fetch_array($result)) { $id = $row['id']; $f_name = $row['f_name']; $l_name = $row['l_name']; $option_block .= "<option value\"$id\">$f_name, $l_name</option>"; } $display_block = "<form method=\"POST\" action=\"show_modcontact.php\"> <p><strong>Contact:</strong> <select name=\"id\">$option_block</select> <input type=\"submit\" name=\"submit\" value=\"Select This Contact\"></p> </form>"; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Modify A Contact</title> </head> <body> <h1>My Contact Management System</h1> <h2><em>Modify a Contact</em></h2> <p>Select a contact from the list below, to modify the contact's record.</p> <? echo "$display_block"; ?> <br> <p><a href="contact_menu.php">Return to Main Menu</a></p> </body> </html> This script is for modifying the contact: named show_modcontact.php <?php if (!$_POST[id]) { header( "Location: pick_modcontact.php"); exit; } else { session_start(); } if ($_SESSION[valid] != "yes") { header( "Location: pick_modcontact.php"); exit; } $db_name = "testDB"; $table_name = "my_contacts"; $connection = @mysql_connect("localhost", "admin_cantsayno", "cantsayno") or die(mysql_error()); $db = @mysql_select_db($db_name, $connection) or die(mysql_error()); $sql = "SELECT f_name, l_name, address1, address2, address3, postcode, prim_tel, sec_tel, email, birthday FROM $table_name WHERE id = '$_POST[id]'"; $result = @mysql_query($sql, $connection) or die(mysql_error()); while ($row = mysql_fetch_array($result)) { $f_name = $row['f_name']; $l_name = $row['l_name']; $address1 = $row['address1']; $address2 = $row['address2']; $address3 = $row['address3']; $country = $row['country']; $prim_tel = $row['prim_tel']; $sec_tel = $row['sec_tel']; $email = $row['email']; $birthday = $row['birthday']; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Modify A Contact</title> </head> <body> <form action="do_modcontact.php" method="post"> <input type="id" value="<? echo "$_POST[id]"; ?>" /> <table cellpadding="5" cellspacing="3"> <tr> <th>Name & Address Information</th> <th> Other Contact / Personal Information</th> </tr> <tr> <td align="top"> <p><strong>First Name:</strong><br /> <input type="text" name="f_name" value="<? echo "$f_name" ?>" size="35" maxlength="75" /></p> <p><strong>Last Name:</strong><br /> <input type="text" name="l_name" value="<? echo "$l_name" ?>" size="35" maxlength="75" /></p> <p><strong>Address1:</strong><br /> <input type="text" name="f_name" value="<? echo "$address1" ?>" size="35" maxlength="75" /></p> <p><strong>Address2:</strong><br /> <input type="text" name="f_name" value="<? echo "$address2" ?>" size="35" maxlength="75" /></p> <p><strong>Address3:</strong><br /> <input type="text" name="f_name" value="<? echo "$address3" ?>" size="35" maxlength="75" /> </p> <p><strong>Postcode:</strong><br /> <input type="text" name="f_name" value="<? echo "$postcode" ?>" size="35" maxlength="75" /></p> <p><strong>Country:</strong><br /> <input type="text" name="f_name" value="<? echo "$country" ?>" size="35" maxlength="75" /> </p> <p><strong>First Name:</strong><br /> <input type="text" name="f_name" value="<? echo "$f_name" ?>" size="35" maxlength="75" /></p> </td> <td align="top"> <p><strong>Prim Tel:</strong><br /> <input type="text" name="f_name" value="<? echo "$prim_tel" ?>" size="35" maxlength="75" /></p> <p><strong>Sec Tel:</strong><br /> <input type="text" name="f_name" value="<? echo "$sec_tel" ?>" size="35" maxlength="75" /></p> <p><strong>Email:</strong><br /> <input type="text" name="f_name" value="<? echo "$email" ?>" size="35" maxlength="75" /> </p> <p><strong>Birthday:</strong><br /> <input type="text" name="f_name" value="<? echo "$birthday" ?>" size="35" maxlength="75" /> </p> </td> </tr> <tr> <td align="center"> <p><input type="submit" name="submit" value="Update Contact" /></p> <br /> <p><a href="contact_menu.php">Retuen To Menu</a></p> </td> </tr> </table> </form> </body> </html>

    Read the article

  • Optimize SQL databases by adding index columns

    - by Viktor Sehr
    This might be implementation specific so the question regards how SQL databases is generally implemented. Say I have a database looking like this; Product with columns [ProductName] [Price] [Misc] [Etc] Order with columns [OrderID] [ProductName] [Quantity] [Misc] [Etc] ProductName is primary key of Product, of some string type and unique. OrderID is primary key and of some integer type, and ProductName being a foreign key. Say I change the primary key of Product to a new column of integer type ie [ProductID] Would this reduce the database size and optimize lookups joining these two tables (and likewise operations), or are these optimizations performed automatically by (most/general/main) SQL database implementations?

    Read the article

  • Average of Average in one row

    - by asrijaal
    Hi there, I'm not sure if this is possible what I'm trying to achieve. I want to get the avg of averaged columns. SELECT avg(col1), avg(col2), avg(col3) FROM tbl My Result should be the avg of all three avg columns, is this possible? Something like this SELECT avg( col1, col2, col3) FROM tbl doesn't work at MySQL 5.1

    Read the article

< Previous Page | 175 176 177 178 179 180 181 182 183 184 185 186  | Next Page >