Search Results

Search found 49789 results on 1992 pages for 'mysql insert id'.

Page 257/1992 | < Previous Page | 253 254 255 256 257 258 259 260 261 262 263 264  | Next Page >

• Need to exclude results in a MySQL query where two table fields are not of certain values (brain far

  - by DondeEstaMiCulo

  I don't know if I'm just burnt out and can't think, or what... But I can't seem to make this work right... (We're using MySQL 5.1...) I have two tables which have some transactional stuff stored in them. There will be many records per user_id in each table. Table1 and Table2 have a one-to-one relationship with each other. I want to pull records from both tables, but I want to exclude records which have certain values in both tables. I don't care if they both don't have these values, or if just one does, but both tables should not have both values. (Does this make any sense? lol) For example: SELECT t1.id, t1.type, t2.name FROM table1 t1 INNER JOIN table2 t2 ON table.xid = table2.id WHERE t1.user_id = 100 AND (t1.type != 'FOO' AND t2.name != 'BAR') So t1.type is type ENUM with about 10 different options, and t2.name is also type ENUM with 2 options. My expected results would look a little like: 1, FOO, YUM 2, BOO, BAR 3, BOO, YUM But instead, all I'm getting is: 3, BOO, YUM Because it's filtering out all records which has 'FOO' as the type, and 'BAR' as the name. I keep waiting for that D'oh! moment where it hits me and I feel like an idiot for not realizing what I'm doing wrong. But it hasn't come. And I still feel like an idiot, lol. I appreciate any light any of you can shed on this! Many thanks in advance for the help!

  Read the article

• How can I use rows in a lookup table as columns in a MySQL query?

  - by TomH

  I'm trying to build a MySQL query that uses the rows in a lookup table as the columns in my result set. LookupTable id | AnalysisString 1 | color 2 | size 3 | weight 4 | speed ScoreTable id | lookupID | score | customerID 1 | 1 | A | 1 2 | 2 | C | 1 3 | 4 | B | 1 4 | 2 | A | 2 5 | 3 | A | 2 6 | 1 | A | 3 7 | 2 | F | 3 I'd like a query that would use the relevant lookupTable rows as columns in a query so that I can get a result like this: customerID | color | size | weight | speed 1 A C D 2 A A 3 A F The kicker of the problem is that there may be additional rows added to the LookupTable and the query should be dynamic and not have the Lookup IDs hardcoded. That is, this will work: SELECT st.customerID, (SELECT st1.score FROM ScoreTable st1 WHERE lookupID=1 AND st.customerID = st1.customerID) AS color, (SELECT st1.score FROM ScoreTable st1 WHERE lookupID=2 AND st.customerID = st1.customerID) AS size, (SELECT st1.score FROM ScoreTable st1 WHERE lookupID=3 AND st.customerID = st1.customerID) AS weight, (SELECT st1.score FROM ScoreTable st1 WHERE lookupID=4 AND st.customerID = st1.customerID) AS speed FROM ScoreTable st GROUP BY st.customerID Until there is a fifth row added to the LookupTable . . . Perhaps I'm breaking the whole relational model and will have to resolve this in the backend PHP code? Thanks for pointers/guidance. tom

  Read the article

• ASP.NET MVC - hiding id in URL?

  - by mcfroob

  I'm building a basic blog application just now, for viewing data I'm just using the default route, i.e. - routes.MapRoute ( "Default", // Route name "{controller}/{action}/{id}", new { controller = "Blog", action = "Index", id = UrlParameter.Optional } ); So that when you go to mysite.com/View/12 it displays the blog with id 12. I want to modify the URLs so that they look as follows: mysite.com/View/2010/06/01/this-is-the-title. I could have a URL mapping like - routes.MapRoute( "View", "View/{year}/{month}/{day}/{title}", new { controller = "Blog", action = "View" } ); But then I'm not passing the ID into the Controller action so I would have to search on the date and title which doesn't feel right. On the other hand, if I pass the ID in it will show up in the URL which isn't what I'm aiming for either. Is there any way to redirect to the URL I want in the controller action after passing only the ID in as a paramter, or to pass the ID into the a Map Route but hide it in the URL? Thanks for your help!

  Read the article

• how can i set up a uniqueness constraint in mysql for columns that can be null?

  - by user299689

  I know that in MySQL, UNIQUE constraits don't treat NULL values as equal. So if I have a unique constraint on ColumnX, then two separate rows can have values of NULL for ColumnX and this wouldn't violate the constraint. How can I work around this? I can't just set the value to an arbitrary constant that I can flag, because ColumnX in my case is actually a foreign key to another table. What are my options here? Please note that this table also has an "id" column that is its primary key. Since I'm using Ruby on Rails, it's important to keep this id column as the primary key. Note 2: In reality, my unique key encompasses many columns, and some of them have to be null, because they are foreign keys, and only one of them should be non-null. What I'm actually trying to do is to "simulate" a polymorphic relationship in a way that keep referential integrity in the db, but using the technique outlined in the first part of the question asked here: http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association

  Read the article

• What is a good solution to log the deletion of a row in MySQL?

  - by hobodave

  Background I am currently logging deletion of rows from my tickets table at the application level. When a user deletes a ticket the following SQL is executed: INSERT INTO alert_log (user_id, priority, priorityName, timestamp, message) VALUES (9, 4, 'WARN', NOW(), "TICKET: David A. deleted ticket #6 from Foo"); Please do not offer schema suggestions for the alert_log table. Fields: user_id - User id of the logged in user performing the deletion priority - Always 4 priorityName - Always 'WARN' timestamp - Always NOW() message - Format: "[NAMESPACE]: [FullName] deleted ticket #[TicketId] from [CompanyName]" NAMESPACE - Always TICKET FullName - Full name of user identified by user_id above TicketId - Primary key ID of the ticket being deleted CompanyName - Ticket has a Company via tickets.company_id Situation/Questions Obviously this solution does not work if a ticket is deleted manually from the mysql command line client. However, now I need to. The issues I'm having are as follows: Should I use a PROCEDURE, FUNCTION, or TRIGGER? -- Analysis: TRIGGER - I don't think this will work because I can't pass parameters to it, and it would trigger when my application deleted the row too. PROCEDURE or FUNCTION - Not sure. Should I return the number of deleted rows? If so, that would require a FUNCTION right? How should I account for the absence of a logged in user? -- Possibilities: Using either a PROC or FUNC, require the invoker to pass in a valid user_id Require the user to pass in a string with the name Use the CURRENT_USER - meh Hard code the FullName to just be "Database Administrator" Could the name be an optional parameter? I'm rather green when it comes to sprocs. Assuming I went with the PROC/FUNC approach, is it possible to outright restrict regular DELETE calls to this table, yet still allow users to call this PROC/FUNC to do the deletion for them? Ideally the solution is usable by my application as well, so that my code is DRY.

  Read the article

• MySql: Is it reasonable to use 'view' or I would better denormalize my DB?

  - by Budda

  There is 'team_sector' table with following fields: Id, team_id, sect_id, size, level It contains few records for each 'team' entity (referenced with 'team_id' field). Each record represent sector of team's stadium (totally 8 sectors). Now it is necessary to implement few searches: by overall stadium size (SUM(size)); the best quality (SUM(level)/COUNT(*)). I could create query something like this: SELECT TS.team_id, SUM(TS.size) as OverallSize, SUM(TS.Level)/COUNT(TS.Id) AS QualityLevel FROM team_sector GROUP BY team_id ORDER BY OverallSize DESC / ORDER BY QualityLevel DESC But my concern here is that calculation for each team will be done each time on query performed. It is not too big overhead (at least now), but I would like to avoid performance issues later. I see 2 options here. The 1st one is to create 2 additional fields in 'team' table (for example) and store there OverallSize and QualityLevel fields. If information if 'sector' table is changed - update those table too (probably would be good to do that with triggers, as sector table doesn't change too often). The 2nd option is to create a view that will provide required data. The 2nd option seems much easier for me, but I don't have a lot of experience/knowledge of work with views. Q1: What is the best option from your perspective here and why? Probably you could suggest other options? Q2: Can I create view in such way that it will do calculations rarely (at least once per day)? If yes - how? Q3: Is it reasonable to use triggers for such purpose (1st option). P.S. MySql 5.1 is used, overall number of teams is around 1-2 thousand, overall number of records in sector table - overall 6-8 thousand. I understand, those numbers are pretty small, but I would like to implement the best practice here.

  Read the article

• Android programming. Application stopped unexpectedly.

  - by user277704

  I've just created a prototype of interface for my android app and tried to run it. Unfortunately I get an error that my app has stopped unexpectedly (my reputation doesn't allow me to post images so follow my links): screenshot of error message This is layout mode of editing. Everything looks as I want so there shouldn't be errors: mobileka.freehostia.com/3.png (I can post only one hyperlink...) This is my main.xml code: <code> <?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" android:gravity="top" android:background="@drawable/back" > <TextView android:id="@+id/score" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text = "@string/scoreT" android:textColor="@string/scoreColor" android:gravity = "left" android:typeface="serif" android:textStyle="bold|italic" android:textSize="16sp" android:paddingLeft = "10px" android:paddingTop="4px"/ <TextView android:id = "@+id/scoreTxt" android:layout_toRightOf="@+id/score" android:textSize="16sp" android:paddingTop = "5px" android:paddingLeft="4px" android:typeface="serif" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text=" 0" android:textColor="@string/scoreTextColor" android:gravity = "left"/> <TextView android:id = "@+id/scoreSeparator" android:layout_toRightOf="@+id/scoreTxt" android:textSize="16sp" android:paddingTop = "3px" android:paddingLeft="4px" android:typeface="serif" android:textStyle="bold" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="|" android:textColor="@string/scoreColor" android:gravity = "left"/> <TextView android:id = "@+id/timerTxt" android:layout_alignParentRight="true" android:textSize="16sp" android:paddingRight="10px" android:paddingTop="4px" android:typeface="serif" android:layout_width="wrap_content" android:layout_height="wrap_content" android:textColor="@string/scoreTextColor" android:gravity = "right" android:text="00:00" /> <TextView android:id = "@+id/timer" android:layout_toLeftOf="@+id/timerTxt" android:textColor="@string/scoreColor" android:layout_width="wrap_content" android:layout_height="wrap_content" android:textSize="16sp" android:padding = "3px" android:typeface="serif" android:textStyle="bold|italic" android:text="@string/timerT" android:gravity = "left"/> <TextView android:id = "@+id/timerSeparator" android:layout_toLeftOf="@+id/timer" android:textSize="16sp" android:paddingTop = "3px" android:paddingLeft="4px" android:typeface="serif" android:textStyle="bold" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="|" android:textColor="@string/scoreColor" android:gravity = "left"/> <ImageButton android:id="@+id/buttonOne" android:layout_below="@+id/score" android:layout_marginTop="40px" android:layout_marginLeft="14px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:clickable="true" android:src="@drawable/inactive"/> <ImageButton android:id="@+id/buttonTwo" android:layout_toRightOf="@+id/buttonOne" android:layout_marginTop="63px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonThree" android:layout_toRightOf="@+id/buttonTwo" android:layout_marginTop="63px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonFour" android:layout_toRightOf="@+id/buttonThree" android:layout_marginTop="63px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonFive" android:layout_below="@+id/buttonOne" android:layout_marginTop="40px" android:layout_marginLeft="14px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonSix" android:layout_toRightOf="@+id/buttonFive" android:layout_marginTop="164px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonSeven" android:layout_toRightOf="@+id/buttonSix" android:layout_marginTop="164px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonEight" android:layout_toRightOf="@+id/buttonSeven" android:layout_marginTop="164px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonNine" android:layout_below="@+id/buttonEight" android:layout_marginTop="40px" android:layout_marginLeft="14px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonTen" android:layout_toRightOf="@+id/buttonNine" android:layout_marginTop="264px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonEleven" android:layout_toRightOf="@+id/buttonTen" android:layout_marginTop="264px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> <ImageButton android:id="@+id/buttonTwelve" android:layout_toRightOf="@+id/buttonEleven" android:layout_marginTop="264px" android:layout_marginLeft="10px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:src="@drawable/inactive" /> And this is my logcat errors (p.s. line #12 is the first TextView in main.xml): 03-26 22:59:31.670: WARN/dalvikvm(185): threadid=3: thread exiting with uncaught exception (group=0x4001b188) 03-26 22:59:31.727: ERROR/AndroidRuntime(185): Uncaught handler: thread main exiting due to uncaught exception 03-26 22:59:31.784: ERROR/AndroidRuntime(185): java.lang.RuntimeException: Unable to start activity ComponentInfo{kz.androidmarket.www.randomtest1/kz.androidmarket.www.randomtest1.randomTest1}: android.view.InflateException: Binary XML file line #12: Error inflating class 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2496) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2512) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.ActivityThread.access$2200(ActivityThread.java:119) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1863) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.os.Handler.dispatchMessage(Handler.java:99) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.os.Looper.loop(Looper.java:123) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.ActivityThread.main(ActivityThread.java:4363) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at java.lang.reflect.Method.invokeNative(Native Method) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at java.lang.reflect.Method.invoke(Method.java:521) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at dalvik.system.NativeStart.main(Native Method) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): Caused by: android.view.InflateException: Binary XML file line #12: Error inflating class 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.createView(LayoutInflater.java:513) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at com.android.internal.policy.impl.PhoneLayoutInflater.onCreateView(PhoneLayoutInflater.java:56) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.createViewFromTag(LayoutInflater.java:563) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.rInflate(LayoutInflater.java:618) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.inflate(LayoutInflater.java:407) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.inflate(LayoutInflater.java:320) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.inflate(LayoutInflater.java:276) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at com.android.internal.policy.impl.PhoneWindow.setContentView(PhoneWindow.java:198) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.Activity.setContentView(Activity.java:1622) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at kz.androidmarket.www.randomtest1.randomTest1.onCreate(randomTest1.java:11) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2459) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): ... 11 more 03-26 22:59:31.784: ERROR/AndroidRuntime(185): Caused by: java.lang.reflect.InvocationTargetException 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.widget.TextView.(TextView.java:320) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at java.lang.reflect.Constructor.constructNative(Native Method) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at java.lang.reflect.Constructor.newInstance(Constructor.java:446) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.view.LayoutInflater.createView(LayoutInflater.java:500) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): ... 22 more 03-26 22:59:31.784: ERROR/AndroidRuntime(185): Caused by: android.content.res.Resources$NotFoundException: File #ffff9900 from drawable resource ID #0x7f040002: .xml extension required 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.content.res.Resources.loadColorStateList(Resources.java:1820) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.content.res.TypedArray.getColorStateList(TypedArray.java:289) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): at android.widget.TextView.(TextView.java:627) 03-26 22:59:31.784: ERROR/AndroidRuntime(185): ... 26 more Could anybody help me?

  Read the article

• What is the equivalent of Oracle’s REF CURSOR in MySQL when using JDBC?

  - by dacracot

  In Oracle I can declare a reference cursor... TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; ...and use it to pass a cursor as the return value... FUNCTION end_spool RETURN t_spool AS v_spool t_spool; BEGIN COMMIT; OPEN v_spool FOR SELECT * FROM spool WHERE key = g_spool_key ORDER BY seq; RETURN v_spool; END end_spool; ...and then capture it as a result set using JDBC... private Connection conn; private CallableStatement stmt; private OracleResultSet rset; [...clip...] stmt = conn.prepareCall("{ ? = call " + call + "}"); stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.execute(); rset = (OracleResultSet)stmt.getObject(1); What is the equivalent in MySQL?

  Read the article

• Replacement relevance sorting for MySQL fulltext in InnoDB tables?

  - by Giles Smith

  I am using MySQL InnoDB and want to do fulltext style searches on certain columns. What is the best way of sorting the results in order of relevance? I am trying to do something like: SELECT columns, (LENGTH(column_name) - LENGTH(REPLACE(column_name, '%searchterm%', ''))) AS score FROM table WHERE column_name LIKE '%searchterm%' ORDER BY score However this will become quite complicated once I start searching more than 1 column or using more than one keyword. I already have several joins happening so have simplified the query above. Can anyone suggest a better way? I don't want to use any 3rd party software like Sphinx etc

  Read the article

• MySQL Binary Storage using BLOB VS OS File System: large files, large quantities, large problems.

  - by Quantico773

  Hi Guys, Versions I am running (basically latest of everything): PHP: 5.3.1 MySQL: 5.1.41 Apache: 2.2.14 OS: CentOS (latest) Here is the situation. I have thousands of very important documents, ranging from customer contracts to voice signatures (recordings of customer authorisation for contracts), with file types including, but not limited to jpg, gif, png, tiff, doc, docx, xls, wav, mp3, pdf, etc. All of these documents are currently stored on several servers including Windows 32 bit, CentOS and Mac, among others. Some files are also stored on employees desktop computers and laptops, and some are still hard copies stored in hundreds of boxes and filing cabinets. Now because customers or lawyers could demand evidence of contracts at any time, my company has to be able to search and locate the correct document(s) effectively, for this reason ALL of these files have to be digitised (if not already) and correlated into some sort of order for searching and accessing. As the programmer, I have created a full Customer Relations Management tool that the whole company uses. This includes Customer Profiles management, Order and job Tracking tools, Job/sale creation and management modules, etc, and at the moment any file that is needed at a customer profile level (drivers licence, credit authority, etc) or at a job/sale level (contracts, voice signatures, etc) can be uploaded to the server and sits in a parent/child hierarchy structure, just like Windows Explorer or any other typical file managment model. The structure appears as such: drivers_license |- DL_123.jpg voice_signatures |- VS_123.wav |- VS_4567.wav contracts So the files are uplaoded using PHP and Apache, and are stored in the file system of the OS. At the time of uploading, certain information about the file(s) is stored in a MySQL database. Some of the information stored is: TABLE: FileUploads FileID CustomerID (the customer id that the file belongs to, they all have this.) JobID/SaleID (the id of the job/sale associated, if any.) FileSize FileType UploadedDateTime UploadedBy FilePath (the directory path the file is stored in.) FileName (current file name of uploaded file, combination of CustomerID and JobID/SaleID if applicable.) FileDescription OriginalFileName (original name of the source file when uploaded, including extension.) So as you can see, the file is linked to the database by the File Name. When I want to provide a customers' files for download to a user all I have to do is "SELECT * FROM FileUploads WHERE CustomerID = 123 OR JobID = 2345;" and this will output all the file details I require, and with the FilePath and FileName I can provide the link for download. http... server / FilePath / FileName There are a number of problems with this method: Storing files in this "database unconcious" environment means data integrity is not kept. If a record is deleted, the file may not be deleted also, or vice versa. Files are strewn all over the place, different servers, computers, etc. The file name is the ONLY thing matching the binary to the database and customer profile and customer records. etc, etc. There are so many reasons, some of which are described here: http://www.dreamwerx.net/site/article01 . Also there is an interesting article here too: sietch.net/ViewNewsItem.aspx?NewsItemID=124 . SO, after much research I have pretty much decided I am going to store ALL of these files in the database, as a BLOB or LONGBLOB, but there are still many considerations before I do this. I know that storing them in the database is a viable option, however there are a number of methods of storing them. I also know storing them is one thing; correlating and accessing them in a manageable way is another thing entirely. The article provided at this link: dreamwerx.net/site/article01 describes a way of splitting the uploaded binary files into 64kb chunks and storing each chunk with the FileID, and then streaming the actual binary file to the client using headers. This is a really cool idea since it alleviates preassure on the servers memory; instead of loading an entire 100mb file into the RAM and then sending it to the client, it is doing it 64kb at a time. I have tried this (and updated his scripts) and this is totally successful, in a very small frame of testing. So if you are in agreeance that this method is a viable, stable and robust long-term option to store moderately large files (1kb to couple hundred megs), and large quantities of these files, let me know what other considerations or ideas you have. Also, I am considering getting a current "File Management" PHP script that gives an interface for managing files stored in the File System and converting it to manage files stored in the database. If there is already any software out there that does this, please let me know. I guess there are many questions I could ask, and all the information is up there ^^ so please, discuss all aspects of this and we can pass ideas back and forth and teach each other. Cheers, Quantico773

  Read the article

• MySql timeouts - Should I set autoReconnect=true in Spring application?

  - by George

  After periods of inactivity on my website (Using Spring 2.5 and MySql), I get the following error: org.springframework.dao.RecoverableDataAccessException: The last packet sent successfully to the server was 52,847,830 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. According to this question, and the linked bug, I shouldn't just set autoReconnect=true. Does this mean I have to catch this exception on any queries I do and then retry the transaction? Should that logic be in the data access layer, or the model layer? Is there an easy way to handle this instead of wrapping every single query to catch this?

  Read the article

• "SELECT ... FOR UPDATE" not working for Hibernate and MySQL

  - by Andres Rodriguez

  Hi, We have a system in which we must use pessimistic locking in one entity. We are using hibernate, so we use LockMode.UPGRADE. However, it does not lock. The tables are InnoDB We have checked that locking works correctly in the database (5.0.32), so this bug http://bugs.mysql.com/bug.php?id=18184 seems to be no problem. We have checked that datasource includes the autoCommit = false parameter. We have checked that the SQL hibernate (version 3.2) generates includes the " FOR UPDATE". Thanks,

  Read the article

• How to replace all instances of a particular value in a mysql database with another?

  - by Joseph

  I'm looking for a MySQL equivalent of what str_replace is for PHP. I want to replace all instances of one word with another, I want to run a query that will replace all "apples" with "oranges". The reason why: UPDATE fruits SET name='oranges' WHERE name='apples'; isn't going to work for my situation, is because I often times have multiple words in a table row separated by commas like: "apples, pears, pineapples". In this case I want just apples to be replaced by oranges and pear and pineapples to stay in tact. Is there any way to do this?

  Read the article

• Need to find current connections in mysql media server log table.

  - by Roger

  Hi, I have a media server logging to a mysql database and it records a seperate row for each connect, play, stop, disconnect event. What I would like to do is find connect events that do not have a related disconnect event or play events that do not have a related stop event. date time category event clientId stream streamId =============================================================================== 2010-04-21 10:30:00 session connect 1 2010-04-21 10:30:05 stream start 1 stream1 1 2010-04-21 10:35:00 stream stop 1 stream1 1 2010-04-21 10:35:00 session disconnect 1 2010-04-21 10:35:00 session connect 2 2010-04-21 10:35:05 stream start 2 stream2 1 2010-04-21 10:35:08 session connect 3 2010-04-21 10:35:13 stream start 3 stream1 1 2010-04-21 10:37:05 stream stop 2 stream2 1 2010-04-21 10:37:10 stream start 2 stream1 2 I would like to be able to get a total of current sessions and in a seperate query, a total of current streams being played. Thanks, Roger.

  Read the article

• How do I flip a column in mysql that has data structured: 'last name, first name'?

  - by ggg

  I want to create a new column in a MYSQL table that has Fn Ln instead of Ln, Fn. Most of my data is printed Fn Ln. Another idea is to incorporate a string function each time there is an output (php based) but that seems to waste resources. Finally, I couldn't find the syntax (loop or foreach) for my php function anyway. Here is the working php function that I got from a previous post: $name = "Lastname, Firstname"; $names = explode(", ", $name); $name = $names[1] . " " . $names[0];

  Read the article

• how to calculate a bill from several tables on mysql?

  - by Audel

  I'm using mysql to create an hotel booking system, but i am struggling a little bit to calculate the final bill. I need a SELECT command to get data from several tables and make some calculations. Basically I just need to get the 'night cost' from a table called 'room_types'. Then, use DATEDIFF function to get the difference of days between the 'checkin' and 'checkout' columns in the table 'room_booking' and multiply the difference with the night cost and display the total. These are the tables I would be using: are room_booking, room_types, booking, and room. One booking may have several room bookings, so Im looking for a table that looks something like this: +------------+------------+---------------+------------------+ | bookingid | Room price | nights stayed | total room price | +------------+------------+---------------+------------------+ | B001 | 30.00 | 4 | 120.00 | +------------+------------+---------------+------------------+ | B001 | 40.00 | 3 | 120.00 | +------------+------------+---------------+------------------+ booking id comes from table 'booking' room price from 'room_types', 'nights stayed' is calculated from the table room_booking, using the datediff command between checkin and checkout . I hope i was clear

  Read the article

• How to do an IF statement based on the value of a MySQL table cell?

  - by Susan

  How can I do an if statement based on the value of a mysql table cell. For example, I have a table of people with a column called marital_status. This column has one of two values: yes or no. But this doesn't work: $query = mysql_query("SELECT marital_status FROM people WHERE first_name = 'John'"); while ($row = mysql_fetch_assoc($query)) { $maritalStatus = $row['marital_status']; } if ($maritalStatus == "yes") { echo "This person is married."; } else { echo "This person is NOT married."; } $maritalStatus == "yes" doesn't return as true even though that is exactly the value in that cell.

  Read the article

• How do you write "Select (all) From (table) Where posting=$posting except this posting? (Mysql)

  - by ggfan

  I want to write a Mysql statement that selects all from a table(posting) where title is like $title except for the title of $title. Basically I want to display all related posting of a certain posting. I want the query to select all the postings in the table that has the title name in the title and detail. But I don't want the posting to display in the related postings. $query="Select * From posting Where title,detail, like %$title% except $title"; how do I write the except part?

  Read the article

• Help need to with MySQL query to join data spanning multiple tables and use data as column names

  - by gurun8

  I need a little help putting together a SQL query that will give me the following resultsets: and The data model looks like this: The tricky part for me is that the columns to the right of the "Product" in the resultset aren't really columns in the database but rather key/value pairs spanned across the data model. Table data is as follows: My apologies in advance for the image heavy question and the image quality. This just seemed like the easiest way to convey the information. It'll probably take someone less time to write the query statement to achieve the results than it did for me to assemble this question. By the way, the "product_option" table image is truncated but it illustrated the general idea of the data structure. The MySQL server version is 5.1.45.

  Read the article

• MySQL - Calculating a value from two columns in each row, using the result in a WHERE or HAVING clause

  - by taber

  I have a MySQL db schema like so: id flags views done ------------------------------- 1 2 20 0 2 66 100 0 3 25 40 0 4 30 60 0 ... thousands of rows ... I want to update all of the rows whose flags / views are = 0.2. First as a test I want to try to SELECT to see how many rows would actually get updated. I tried: SELECT flags/views AS poncho FROM mytable HAVING poncho 0.2 But this only returns like 2 rows and I know there should be a lot more than that. It seems like it's calculating the poncho value on all rows or something odd. What am I doing wrong? Thanks!

  Read the article

• Is 'second' a keyword in mysql full-text searches?

  - by Larry

  I have a simple MySQL table that is setup for full text search. | id | title | ---------------------- | 1 | test event | | 2 | Second test | | 3 | Larry's event | | 4 | this second | When I use the query: SELECT * FROM EVENTS WHERE MATCH (title) AGAINST ('test event' IN BOOLEAN MODE); I get back 3 rows; the ones containing 'test event', 'Second test', and 'Larry's Event'. Now If I run the following query: SELECT * FROM EVENTS WHERE MATCH (title) AGAINST ('second' IN BOOLEAN MODE); Nothing is returned... strange? Lastly, if I run the query: SELECT * FROM EVENTS WHERE MATCH (title) AGAINST ('second test' IN BOOLEAN MODE); I get back 2 rows; the ones containing 'test event' and 'Second test'. I appears that the word 'second' cannot be searched or needs to be escaped somehow. Am I missing something?

  Read the article

• MySQL is returning an "column ambiguous" error when it used to work before I upgraded...

  - by Ronedog

  I recently upgraded MySQL to 5.1.41. Before the upgrade the following SQL worked (or at least I thought I remembered it working...it has been a few weeks since designing this...). Now the SQL gives me an error stating that the "archived" column is ambiguous. How can I write this differently, or is there a different problem I'm not aware of? I simply want to return the "unit_id", "lease_count" (stored in another table with a unit_id that should correspond with the "a.unit_id"), and "lease_archived_count (stored in another table with a unit_id that should correspond with the "a.unit_id"). SELECT a.unit_id, (SELECT count(*) FROM o_leases WHERE unit_id = a.unit_id AND archived = 0) as lease_count, (SELECT count(*) FROM o_leases WHERE unit_id = a.unit_id AND archived = 1) as lease_archive_count FROM p_unit a, properties b, portfolio c WHERE a.property_id = b.properties_id AND b.portfolio_id = c.portfolio_id AND a.archived = 0 Thanks for your help.

  Read the article

• How to select the first row for each group in MySQL?

  - by Jader Dias

  In C# it would be like this: table .GroupBy(row => row.SomeColumn) .Select(group => group .OrderBy(row => row.AnotherColumn) .First() ) Linq-To-Sql translates it to the following T-SQL code: SELECT [t3].[AnotherColumn], [t3].[SomeColumn] FROM ( SELECT [t0].[SomeColumn] FROM [Table] AS [t0] GROUP BY [t0].[SomeColumn] ) AS [t1] OUTER APPLY ( SELECT TOP (1) [t2].[AnotherColumn], [t2].[SomeColumn] FROM [Table] AS [t2] WHERE (([t1].[SomeColumn] IS NULL) AND ([t2].[SomeColumn] IS NULL)) OR (([t1].[SomeColumn] IS NOT NULL) AND ([t2].[SomeColumn] IS NOT NULL) AND ([t1].[SomeColumn] = [t2].[SomeColumn])) ORDER BY [t2].[AnotherColumn] ) AS [t3] ORDER BY [t3].[AnotherColumn] But it is uncompatible with MySQL.

  Read the article

• How to configure MySQL connection properties with Spring, Hibernate 3.3 and c3p0?

  - by sfussenegger

  I am currently in the process of upgrading an application from Hibernate 3.2 to Hibernate 3.3. I though I'd stick with the default connection pool (Hibernate changed its default from Commons DBCP to c3p0) as I don't have any good reason to choose a non-default pool. At least non but having used DBCP before. The upgrade went pretty much without any problems so far. The only thing I can't get to work is passing properties to the underlying MySQL JDBC4Connection. Up to now, I used DBCP's BasicDataSource.addConnectionProperty(String,String) to pass properties (useUnicode=true, characterEncodin=UTF-8, characterSetResults=UTF-8, zeroDateTimeBehavior=convertToNull). However, I can't find any way to do the same with c3p0 other than including them in the JDBC URL. (That's something I'd like to avoid as I wanna keep the URL configurable without forcing users to include those parameters.) So far, I've tried to use a ConnectionCustomizer without success. Any other suggestions?

  Read the article

• Help needed with MySQL query to join data spanning multiple tables with data used as column names

  - by gurun8

  I need a little help putting together a SQL query that will give me the following resultsets: and The data model looks like this: The tricky part for me is that the columns to the right of the "Product" in the resultset aren't really columns in the database but rather key/value pairs spanned across the data model. Table data is as follows: My apologies in advance for the image heavy question and the image quality. This just seemed like the easiest way to convey the information. It'll probably take someone less time to write the query statement to achieve the results than it did for me to assemble this question. By the way, the "product_option" table image is truncated but it illustrated the general idea of the data structure. The MySQL server version is 5.1.45.

  Read the article

< Previous Page | 253 254 255 256 257 258 259 260 261 262 263 264  | Next Page >