How do I force MySQL Workbench, when using a foreign key, to perform an INSERT to my referenced table before the INSERT to the referencing table. Without the obvious solution of editing the generated SQL by hand? ...
Sorry , maybe it's trivial but I can't find a way to do this : table customers customer_id customer_email customer_newsletter (1=yes/0=no) table orders order_id customer_id order_status (1=ok/0=no_ok) Each customer can have zero, one or many orders. I'm trying to make a query to get all the emails of the persons who are : ...
Hello. I am working on a community website. I want to show the user's activity in 2 places in the website. The User "A" Profile. The Friends page of the user "A" friends. "What are your friends doing?" The tables for instance are: members members_gallery members_videos members_friends my problem is in the Sql structure. I've ...
Hello, I am programming a WebService in Java that create and call this class : public class Manager{ private Connection aConnection; public CacheManager(){ //We get a connection aConnection = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/mydb?user=root&password="; ...
In my table I have two fields: start_date and end_date. I'm just trying to retrieve rows where the current date is between the start and end dates. For some reason, I am not getting this at all. I've tried using between, but I keep getting empty sets even though I know that there are records that exist... I don't think I'm thinking a...
Im stumped by this simple query because its one I have not tried before. Ive got a User table, User_Widget table and a Widget table. A simple inner join shows me what widgets they have by joining user_widget.user_id = user.user_id. How would I show the widgets in the Widget table that they dont have? ...
I have an application server (jetty 6 on a linux box) hosting 15 individuals applications (individual war's). Every 3 or 4 days I get an alert from nagios regarding the number of open TCP connections. Upon inspection, I see that the vast majority of these connections are to the MySQL server. netstat -ntu | grep TIME_WAIT Shows 10,000+...
I have a classifieds website which uses SOLR to search for whatever ads the user wants to search for... SOLR then returns the ID:s of all the matches found. I then use the ID:s to fetch and display the ads from a MySQL table. currently I have one huge table containing everything in MySQL. Sometimes some of the fields are empty because f...
I'm using php to fetch some records from my db and it keeps returning "No results found". But, when I print the query to the screen and copy and paste it at the mysql prompt, I get 1 row. Granted, it's a monster query, but shouldn't I get the same results? What causes that to happen? Any suggestions on what to check? Don't know if...
We're using Doctrine, a PHP ORM. I am creating a query like this: $q = Doctrine_Query::create()->select('id')->from('MyTable'); and then in the function I'm adding in various where clauses and things as appropriate, like this $q->where('normalisedname = ? OR name = ?', array($string, $originalString)); Later on, before execute()-in...
I'm trying to go over each row of a table in MySQL using Spring and a JdbcTemplate. If I'm not mistaken this should be as simple as: JdbcTemplate template = new JdbcTemplate(datasource); template.setFetchSize(1); // template.setFetchSize(Integer.MIN_VALUE) does not work either template.query("SELECT * FROM cdr", new RowCall...
Hi, I have a rather complex logical query I'm trying to execute. Essentially there are a series of related tables. I am having difficulty figuring out a good way to approach it. Tables: Transaction - T_id Discount - D_id item_id Type (percentage or basic value) value Transaction_Discount - T_id D_id Item - item_id price ED...
Website: Classifieds website (users may put ads, search ads etc) I plan to use SOLR for searching and then return results as ID nr:s only, and then use those ID nr:s and query mysql, and then lastly display the results with those ID:s. Currently I have around 30 tables in MySQL, one for each category. 1- Do you think I should do it di...
My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together. Currently I'm tuning the MySQL query cache to get it in line with the characteristics of queries being run on the server. query_cache_size is the maximum amount of data...
Here's the desired flow of my PHP app (yes, it's vague, but it's easier that way): User submits a set of, let's say, about 5 objects by integer IDs. (It'll really be more like 15, but let's say 5 for ease.) App checks if this collection has been submitted before, and saves it in a MySQL database if not App saves these objects in the da...
Ok, so on my site we have a filter section where a user can select to filter through games by either the game's genre or the model that the game will play on. I'm trying to write a query that will query the database and filter it by genre if genre is the only set item, filter it by model if model is the only set item, or filter both if ...
Hi All, We've got a table we use as a queue. Entries are constantly being added, and constantly being updated, and then deleted. Though we might be adding 3 entries/sec the table never grows to be more than a few hundred rows. To get entries out of table we are doing a simple select. SELECT * FROM queue_table WHERE some_id = ? ...
Hi everybody, i tried now nearly everything i could to solve he following problem. So far with no success. But there must be a solution cause i dont think the case is s.th too special. I think i am just a bloody beginner ;-) I need to join union merge or whatever ;-) in MySQL to solve the following problem... CASE: Three tables "posts"...
I need to implement a Geo proximity search in my application but I'm very confused regarding the correct formula to use. After some searches in the Web and in StackOverflow I found that the solutions are: Use the Haversine Formula Use the Great-Circle Distance Formula Use a Spatial Search Engine in the Database Option #3 is really no...
I am pulling a set of numbers from a MySQL database and am trying to do some simple math on them to calculate a total price to put into an invoice, but PHP isn't cooperating. I thought it might be a type-coercion issue, so I tried adding some intval and floatval calls, but still the total always comes out to 0. Here is the relevant cod...