Search Results

Search found 56516 results on 2261 pages for 'mysql create'.

Page 150/2261 | < Previous Page | 146 147 148 149 150 151 152 153 154 155 156 157  | Next Page >

• question about MySQL database migration

  - by WilliamLou

  Hi there: If I have a MySQL database with several tables on a live server, now I would like to migrate this database to another server. Of course, the migration I mean here involves some database tables, for example: add some new columns to several tables, add some new tables etc.. Now, the only method I can think of is to use some php/python(two scripts I know) script, connect two databases, dump the data from the old database, and then write into the new database. However, this method is not efficient at all. For example: in old database, table A has 28 columns; in new database, table A has 29 columns, but the extra column will have default value 0 for all the old rows. My script still needs to dump the data row by row and insert each row into the new database. Is there any tools or a better method than writing a script yourself? Here, I dont need to worry about multithread writing problems etc.., I mean the old database will be down (not open to public usage etc.., only for upgrade ) for a while. Thanks!!

  Read the article

• Rails Active Record Mysql find query HAVING clause

  - by meetraghu28

  Is there a way to use the HAVING clause in some other way without using group by. I am using rails and following is a sample sccenario of the problem that i am facing. In rails you can use the Model.find(:all,:select,conditions,:group) function to get data. In this query i can specify a having clause in the :group param. But what if i dont have a group by clause but want to have a having clause in the result set. Ex: Lets take a query select sum(x) as a,b,c from y where "some_conditions" group by b,c; This query has a sum() aggregation on one of the fields. No if there is nothing to aggregate then my result should be an empty set. But mysql return a NULL row. So this problem can be solved by using select sum(x) as a,b from y where "some_conditions" group by b having a NOT NULL; but what happens in case i dont have a group by clause?? a query like below select sum(x) as a,b from y where "some_conditions"; so how to specify that sum(x) should not be NULL? Any solution that would return an empty set in this case instead of a NULL row will help and also that solution should be doable in rails. We can use subqueries to get this condition working with sumthin like this select * from ((select sum(x) as b FROM y where "some_condition") as subq) where subq.b is not null; but is there a better way to do this thru sql/rails ??

  Read the article

• Best way to handle Many-to-Many relationships in PHP MySQL

  - by Jayrox

  I am looking for the best way to handle a database of many-to-many relationships in PHP and MySQL. Right now I have 2 tables: Users (id, user_name, first_name, last_name) Connections (id_1, id_2) In the User table id is auto incremented on add and user_name is unique, but can be changed. Unfortunately, I don't have control over the user_name and its ability to be changed, but I must account for it. The Connections table is obviously, user1 and user2's id. The connection table needs to account for these possible relations: user1 --> user2 (user 1 friends with user 2 but not user2 friends with user1) user2 --> user1 (user 2 friends with user 1 but not user1 friends with user2) user1 <--> user2 (user 1 and user 2 mutually friends) user1 <-!-> user2 (user 1 and user 2 not friends) That part is not the problem, The problem I am having with is keeping these relations unique when and if they change in batches. Possible solution 1: delete all of user 1's relations and readd them with the updated list. I think this might be too slow for my needs. Solution 2? Anyone else encounter this problem? How should I best handle this? update: distinguishing relationships: i handle relationships like this: user1, user2 user1, user3 user2, user1 in that example the following is true: user1 follows user2 and user3 user2 only follows user1 but doesn't follow user3 user3 doesn't follow either user1 or user2

  Read the article

• Having an issue with Nullable MySQL columns in SubSonic 3.0 templates

  - by omegawkd

  Looking at this line in the Settings.ttinclude string CheckNullable(Column col){ string result=""; if(col.IsNullable && col.SysType !="byte[]" && col.SysType !="string") result="?"; return result; } It describes how it determines if the column is nullable based on requirements and returns either "" or "?" to the generated code. Now I'm not too familiar with the ? nullable type operator but from what I can see a cast is required. For instance, if I have a nullable integer MySQL column and I generate the code using the default template files it returns a line similar to this: int? _User_ID; When trying to compile the project I get the error: Cannot implicitly convert type 'int?' to 'int'. An explicit conversion exists (are you missing a cast?) I checked teh Settings files for the other database types and they all seems to have the same routine. So my question is, is this behaviour expected or is this a bug? I need to solve it one way or the other before I can procede. Thanks for your help.

  Read the article

• Rolling back file moves, folder deletes and mysql queries

  - by Workoholic

  This has been bugging me all day and there is no end in sight. When the user of my php application adds a new update and something goes wrong, I need to be able to undo a complex batch of mixed commands. They can be mysql update and insert queries, file deletes and folder renaming and creations. I can track the status of all insert commands and undo them if an error is thrown. But how do I do this with the update statements? Is there a smart way (some design pattern?) to keep track of such changes both in the file structure and the database? My database tables are MyISAM. It would be easy to just convert everything to InnoDB, so that I can use transactions. That way I would only have to deal with the file and folder operations. Unfortunately, I cannot assume that all clients have InnoDB support. It would also require me to convert many tables in my database to InnoDB, which I am hesitant to do.

  Read the article

• MySQL query pulling from two tables, display in correct fields

  - by Matt Nathanson

  I'm trying to select all fields in two separate tables as long as they're sharing a common ID. //mysql query $result = mysql_query("SELECT * FROM project, links WHERE project.id = links.id and project.id = $clientID") //displaying the link if ($row['url'] != null){ echo "<div class='clientsection' id='links'>Links</div>"; echo "<a class='clientlink' id='link1' href='" . $row['url'] . "'>" . $row['name'] . "</a>"; } else { echo "<a class='clientlink' id='link1' href='" . $row['url'] . "' style='display:none;'>" . $row['name'] . "</a>"; }; As you can see, my tables are "projects", and "links" Each is sharing a common field "id" for reference. It looks as though where both links.id and project.id are equal, it outputs anything, but when there is no links.id associated with a given $clientID the container relative to the $clientID doesn't display at all. Essentially I'm using this to add links dynamically to a specific client in this CMS and if there are no links, I want the container to show up anyway. Hopefully I've expressed myself clearly, any pointers in the right direction are appreciated. Thanks!

  Read the article

• mysql/algorithm: Weighting an average to accentuate differences from the mean

  - by Sai Emrys

  This is for a new feature on http://cssfingerprint.com (see /about for general info). The feature looks up the sites you've visited in a database of site demographics, and tries to guess what your demographic stats are based on that. All my demgraphics are in 0..1 probability format, not ratios or absolute numbers or the like. Essentially, you have a large number of data points that each tend you towards their own demographics. However, just taking the average is poor, because it means that by adding in a lot of generic data, the number goes down. For example, suppose you've visited sites S0..S50. All except S0 are 48% female; S0 is 100% male. If I'm guessing your gender, I want to have a value close to 100%, not just the 49% that a straight average would give. Also, consider that most demographics (i.e. everything other than gender) does not have the average at 50%. For example, the average probability of having kids 0-17 is ~37%. The more a given site's demographics are different from this average (e.g. maybe it's a site for parents, or for child-free people), the more it should count in my guess of your status. What's the best way to calculate this? For extra credit: what's the best way to calculate this, that is also cheap & easy to do in mysql?

  Read the article

• Adding Mysql Columns in Rails Rake

  - by Gigg

  I have a rake file that does a series of calculations on a database. Basically it adds usage on equipment regularly. At the end of the day it needs to add that days total to a monthly total table and update that same table. i use the following simple concepts: To get data from the database is pretty simple: @usages = Usage.find(:all) time = Time.new for usage in @usages sql = ActiveRecord::Base.connection To insert it into the database: sql.execute "INSERT (or UPDATE) into usages ## add values and options as per MySQL But how do I: take a column from a database, add all of its values together that have a common value in another column, (i.e. if column x == value y) and then insert it into another column in another table, say dailyusages? I have tried these options: task (:monthly => :environment) do @dailyusages = Dailyusage.find(:all) for dailyusage in @dailyusages sql = ActiveRecord::Base.connection time = Time.new device = monthlyusages.device month = time.month if device == dailyusages.device ##&& month == dailyusages.month total = (dailyusage.total.sum.to_i) @monthlyusages = Monthlyusage.find(:all) for monthlyusage in @monthlyusages sql = ActiveRecord::Base.connection old_total = monthlyusage.total.to_i new_total = (old_total + total) sql.execute "UPDATE monthlyusages ( year, month, total, device ) values('#{time.year}', '#{time.month}', '#{total}', '#{dailyusage.device}' )" end I obviously have uncommented options and tried all sorts of things. Any help would really save me a load of trouble. Thanks in advance. (** BTW - I am new to rails, so go easy on me **)

  Read the article

• MySql: Query multiple identical dynamic tables.

  - by JYelton

  I have a database with 500+ tables, each with identical structure, that contain historical data from sensors. I am trying to come up with a query that will locate, for example, all instances where sensor n exceeds x. The problem is that the tables are dynamic, the query must be able to dynamically obtain the list of tables. I can query information_schema.tables to get a list of the tables, like so: SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name'; I can use this to create a loop in the program and then query the database repeatedly, however it seems like there should be a way to have MySql do the multiple table search. I have not been able to make a stored procedure that works, but the examples I can find are generally for searching for a string in any column. I want to specifically find data in a specific column that exists in all tables. I admit I do not understand how to properly use stored procedures nor if they are the appropriate solution to this problem. An example query inside the loop would be: SELECT device_name, sensor_value FROM device_table WHERE sensor_value > 10; Trying the following does not work: SELECT device_name, sensor_value FROM ( SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name' ) WHERE sensor_value > 10; It results in an error: "Every derived table must have its own alias." The goal is to have a list of all devices that have had a given sensor value occur anywhere in their log (table). Ultimately, should I just loop in my program once I've obtained a list of tables, or is there a query structure that would be more efficient?

  Read the article

• PHP/MySQL - Working with two databases, one shared and one local to an instance of application

  - by Extrakun

  The situation: Using a off-the-shelf PHP application, I have to add in a new module for extra functionality. Today, it is made known that eventually four different instances of the application are to be deployed, but the data from the new functionality is to be shared among those 4 instances. Each instance should still have their own database for users, content and etc. So the data for the new functionality goes into a 'shared' database. The data for the application (user login, content, uploads) go into a 'local' database To make things more complex, the new module I am writing will fetch data from the local DB and the shared DB at the same time. A re-write of the base application will take too long. I only have control over the new module which I am writing. The ideal solution: Is there a way to encapsulate 2 databases into one name using MySQL? I do not wish to switch DB connections or specifically name the DB to query from inside my SQL statements. The application uses a DB wrapper, so I am able to change it somehow so I can invisibly attempt to read/write to two different DB. What is the best way to handle this problem?

  Read the article

• Good PHP / MYSQL hashing solution for large number of text values

  - by Dave

  Short descriptio: Need hashing algorithm solution in php for large number of text values. Long description. PRODUCT_OWNER_TABLE serial_number (auto_inc), product_name, owner_id OWNER_TABLE owner_id (auto_inc), owener_name I need to maintain a database of 200000 unique products and their owners (AND all subsequent changes to ownership). Each product has one owner, but an owner may have MANY different products. Owner names are "Adam Smith", "John Reeves", etc, just text values (quite likely to be unicode as well). I want to optimize the database design, so what i was thinking was, every week when i run this script, it fetchs the owner of a proudct, then checks against a table i suppose similar to PRODUCT_OWNER_TABLE, fetching the owner_id. It then looks up owner_id in OWNER_TABLE. If it matches, then its the same, so it moves on. The problem is when its different... To optimize the database, i think i should be checking against the other "owner_name" entries in OWNER_TABLE to see if that value exists there. If it does, then i should use that owner_id. If it doesnt, then i should add another entry. Note that there is nothing special about the "name". as long as i maintain the correct linkagaes AND make the OWNER_TABLE "read-only, append-new" type table - I should be able create a historical archive of ownership. I need to do this check for 200000 entries, with i dont know how many unique owner names (~50000?). I think i need a hashing solution - the OWNER_TABLE wont be sorted, so search algos wont be optimal. programming language is PHP. database is MYSQL.

  Read the article

• MySQL: Get average of time differences?

  - by Nebs

  I have a table called Sessions with two datetime columns: start and end. For each day (YYYY-MM-DD) there can be many different start and end times (HH:ii:ss). I need to find a daily average of all the differences between these start and end times. An example of a few rows would be: start: 2010-04-10 12:30:00 end: 2010-04-10 12:30:50 start: 2010-04-10 13:20:00 end: 2010-04-10 13:21:00 start: 2010-04-10 14:10:00 end: 2010-04-10 14:15:00 start: 2010-04-10 15:45:00 end: 2010-04-10 15:45:05 start: 2010-05-10 09:12:00 end: 2010-05-10 09:13:12 ... The time differences (in seconds) for 2010-04-10 would be: 50 60 300 5 The average for 2010-04-10 would be 103.75 seconds. I would like my query to return something like: day: 2010-04-10 ave: 103.75 day: 2010-05-10 ave: 72 ... I can get the time difference grouped by start date but I'm not sure how to get the average. I tried using the AVG function but I think it only works directly on column values (rather than the result of another aggregate function). This is what I have: SELECT TIME_TO_SEC(TIMEDIFF(end,start)) AS timediff FROM Sessions GROUP BY DATE(start) Is there a way to get the average of timediff for each start date group? I'm new to aggregate functions so maybe I'm misunderstanding something. If you know of an alternate solution please share. I could always do it ad hoc and compute the average manually in PHP but I'm wondering if there's a way to do it in MySQL so I can avoid running a bunch of loops. Thanks.

  Read the article

• smallest mysql type that accomodates single decimal

  - by donpal

  Database newbie here. I'm setting up a mysql table. One of the fields will accept a value in increment of a 0.5. e.g. 0.5, 1.0, 1.5, 2.0, .... 200.5, etc. I've tried int but it doesn't capture the decimals. `value` int(10), What would be the smallest type that can accommodate this value, considering it's only a single decimal. I also was considering that because the decimal will always be 0.5 if at all, I could store it in a separate boolean field? So I would have 2 fields instead. Is this a stupid or somewhat over complicated idea? I don't know if it really saves me any memory, and it might get slower now that I'm accessing 2 fields instead of 1 `value` int(10), `half` bool, //or something similar to boolean What are your suggestions guys? Is the first option better, and what's the smallest data type in that case that would get me the 0.5?

  Read the article

• [MySQL/PHP] Avoid using RAND()

  - by Andrew Ellis

  So... I have never had a need to do a random SELECT on a MySQL DB until this project I'm working on. After researching it seems the general populous says that using RAND() is a bad idea. I found an article that explains how to do another type of random select. Basically, if I want to select 5 random elements, I should do the following (I'm using the Kohana framework here)? If not, what is a better solution? Thanks, Andrew <?php final class Offers extends Model { /** * Loads a random set of offers. * * @param integer $limit * @return array */ public function random_offers($limit = 5) { // Find the highest offer_id $sql = ' SELECT MAX(offer_id) AS max_offer_id FROM offers '; $max_offer_id = DB::query(Database::SELECT, $sql) ->execute($this->_db) ->get('max_offer_id'); // Check to make sure we're not trying to load more offers // than there really is... if ($max_offer_id < $limit) { $limit = $max_offer_id; } $used = array(); $ids = ''; for ($i = 0; $i < $limit; ) { $rand = mt_rand(1, $max_offer_id); if (!isset($used[$rand])) { // Flag the ID as used $used[$rand] = TRUE; // Set the ID if ($i > 0) $ids .= ','; $ids .= $rand; ++$i; } } $sql = ' SELECT offer_id, offer_name FROM offers WHERE offer_id IN(:ids) '; $offers = DB::query(Database::SELECT, $sql) ->param(':ids', $ids) ->as_object(); ->execute($this->_db); return $offers; } }

  Read the article

• PHP & MySQL - Array to string conversion error problem.

  - by comma

  I keep getting an the following error of Array to string conversion error on this line listed below. How can I fix this problem? $skill = explode('', $_POST['skill']); Here is the PHP & MySQL code. $skill = explode('', $_POST['skill']); $experience = explode('', $_POST['experience']); $years = explode('', $_POST['years']); for ($s = 0; $s < count($skill); $s++){ for ($x = 0; $x < count($experience); $x++){ for ($g = 0; $g < count($years); $g++){ if (mysqli_num_rows($dbc) == 0) { $mysqli = mysqli_connect("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"INSERT INTO learned_skills (user_id, skill, experience, years, date_created) VALUES ('" . $user_id . "', '" . $skill[$s] . "', '" . $experience[$x] . "', '" . $years[$g] . "', NOW())"); } if ($dbc == TRUE) { $dbc = mysqli_query($mysqli,"UPDATE learned_skills SET skill = '$skill', experience = '$experience', years = '$years', date_created = NOW() WHERE user_id = '$user_id'"); echo '<p class="changes-saved">Your changes have been saved!</p>'; } if (!$dbc) { print mysqli_error($mysqli); return; } } } }

  Read the article

• mysql to xml (DOM question)

  - by Jerry

  Hello guys I am new to php dom and trying to get the mysql data transfer into xml. My current xml output is like this <markers> <city> <name>Seattle</name> <size>medium</size> <name>New York</name> <size>big</size> <city> <markers> but I want to change it to <markers> <city> <name>Seattle</name> <size>medium</size> <city> <city> <name>New York</name> <size>big</size> </city> <city> <markers> my php $dom=new DOMDocument("1.0"); $node=$dom->createElement("markers"); $parnode=$dom->appendChild($node); $firstElement=$dom->createElement("city"); $parnode->appendChild($firstElement); $getLocationQuery=mysql_query("SELECT * FROM location",$connection); header("Content-type:text/xml"); while($row=mysql_fetch_assoc($getLocationQuery)){ foreach ($row as $fieldName=>$value){ $child=$dom->createElement($fieldName); $value=$dom->createTextNode($value); $child->appendChild($value); $firstElement->appendChild($child); } } I can't figure out how to change my php code. Please help me about it. Thanks a lot.

  Read the article

• Disadvantages of MySQL Row Locking

  - by Nyxynyx

  I am using row locking (transactions) in MySQL for creating a job queue. Engine used is InnoDB. SQL Query START TRANSACTION; SELECT * FROM mytable WHERE status IS NULL ORDER BY timestamp DESC LIMIT 1 FOR UPDATE; UPDATE mytable SET status = 1; COMMIT; According to this webpage, The problem with SELECT FOR UPDATE is that it usually creates a single synchronization point for all of the worker processes, and you see a lot of processes waiting for the locks to be released with COMMIT. Question: Does this mean that when the first query is executed, which takes some time to finish the transaction before, when the second similar query occurs before the first transaction is committed, it will have to wait for it to finish before the query is executed? If this is true, then I do not understand why the row locking of a single row (which I assume) will affect the next transaction query that would not require reading that locked row? Additionally, can this problem be solved (and still achieve the effect row locking does for a job queue) by doing a UPDATE instead of the transaction? UPDATE mytable SET status = 1 WHERE status IS NULL ORDER BY timestamp DESC LIMIT 1

  Read the article

• using jquery in mysql php

  - by JPro

  I am new to using Jquery using mysql and PHP I am using the following code to pull the data. But there is not data or error displayed. JQUERY: <html> <head> <script> function doAjaxPost() { // get the form values var field_a = $("#field_a").val(); $("#loadthisimage").show(); $.ajax({ type: "POST", url: "serverscript.php", data: "ID="+field_a, success: function(resp){ $("#resposnse").html(resp); $("#loadthisimage").hide(); }, error: function(e){ alert('Error: ' + e); } }); } </script> </head> <body> <select id="field_a"> <option value="data_1">data_1</option> <option value="data_2">data_2</option> </select> <input type="button" value="Ajax Request" onClick="doAjaxPost()"> <a href="#" onClick="doAjaxPost()">Here</a> </form> <div id="resposnse"> <img src="ajax-loader.gif" style="display:none" id="loadthisimage"> </div> </body> and now serverscript.php <?php if(isset($_POST['ID'])) { $nm = $_POST['ID']; echo $nm; //insert your code here for the display. mysql_connect("localhost", "root", "pop") or die(mysql_error()); mysql_select_db("JPro") or die(mysql_error()); $result1 = mysql_query("select Name from results where ID = \"$nm\" ") or die(mysql_error()); // store the record of the "example" table into $row while($row1 = mysql_fetch_array( $result1 )) { $tc = $row1['Name']; echo $tc; } } ?>

  Read the article

• MySQL Ratings From Two Tables

  - by DirtyBirdNJ

  I am using MySQL and PHP to build a data layer for a flash game. Retrieving lists of levels is pretty easy, but I've hit a roadblock in trying to fetch the level's average rating along with it's pointer information. Here is an example data set: levels Table: level_id | level_name 1 | Some Level 2 | Second Level 3 | Third Level ratings Table: rating_id | level_id | rating_value 1 | 1 | 3 2 | 1 | 4 3 | 1 | 1 4 | 2 | 3 5 | 2 | 4 6 | 2 | 1 7 | 3 | 3 8 | 3 | 4 9 | 3 | 1 I know this requires a join, but I cannot figure out how to get the average rating value based on the level_id when I request a list of levels. This is what I'm trying to do: SELECT levels.level_id, AVG(ratings.level_rating WHERE levels.level_id = ratings.level_id) FROM levels I know my SQL is flawed there, but I can't figure out how to get this concept across. The only thing I can get to work is returning a single average from the entire ratings table, which is not very useful. Ideal Output from the above conceptually valid but syntactically awry query would be: level_id | level_rating 1| 3.34 2| 1.00 3| 4.54 My main issue is I can't figure out how to use the level_id of each response row before the query has been returned. It's like I want to use a placeholder... or an alias... I really don't know and it's very frustrating. The solution I have in place now is an EPIC band-aid and will only cause me problems long term... please help!

  Read the article

• MYSQL - SELECT ALL FROM TABLE if...

  - by hornetbzz

  Hello I have a (nice) mysql table built like this : Fields Datas id (pk) 1 2 3 4 5 6 master_id 1000 1000 1000 2000 2000 2000 ... master_name home home home shop shop shop ... type_data value common client value common client ... param_a foo_a 1 0 bar_a 0 1 ... param_b foo_b 1 0 bar_b 1 0 ... param_c foo_c 0 1 bar_c 0 1 ... ... ... ... ... ... ... ... ... All these datas are embed in a single table. Each datas are dispatched on 3 "columns" set (1 for the values, 1 for identifying if these are common values and one for identifying client values). It's not the best I got but many other scripts depends on this structure. I'd need sthg like this: SELECT parameters name (eg param_a, param_b..) and their values (eg foo_a, foo_b..) WHEN master_id=? AND type_data=(common or client) (eg for values=1 on the 2nd column) . in order to get the parameters hash like param_a => foo_a param_b => foo_b param_c => foo_c ... I could not succeed in self joining on the same table till now but I guess it should be feasible. (I'd like to avoid to do several queries) Thx in advance

  Read the article

• Display Yearly Report When Data Not Available (CI, PHP, MySQL)

  - by tegaralaga

  First of all, i do apologize for my bad english, cos english isn't my native language. I want to display yearly report based on month, let say i got order on January, August, December, but the rest there's no order. So in MySQL database only have 3 order (Jan,Aug,Dec). When i query use CI ( select month(order_date) as month_name , count(order_id) as amount from order where year(order_date)=2011 group by month(order_date) ) there's only 3 data let say the 3 data is (use $query-result_array()) Array ( [0] => Array ( [month_num] => 1 [amount] => 4 ) [1] => Array ( [month_num] => 8 [amount] => 1 ) [2] => Array ( [month_num] => 12 [amount] => 19 ) ) how to make it to 12 data (12 Month) the array become like this (when data not available the amount is 0) Array ( [0] => Array ( [month_num] => 1 [amount] => 4 ) [1] => Array ( [month_num] => 2 [amount] => 0 ) [2] => Array ( [month_num] => 3 [amount] => 0 ) etc ) Thanks in advance :)

  Read the article

• C# web service, MySql encoding problem

  - by Boban

  I use C# web service to insert, delete and get data from MySql database. The problem is some of the data is in Macedonian (Cyrilic). When I insert directly in the database, it inserts ok. For example: "???" is "???". When I insert throgh the service, it's not. For example: "???" is "???". When I try to get data throug the service, it gets it ok. What's the problem with the inserting? Here is part of my code for inserting: MySqlConnection connection = new MySqlConnection(MyConString); MySqlCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO user (id_user, name VALUES (NULL, ?name);"; command.Parameters.Add("?name", MySqlDbType.VarChar).Value = name; connection.Open(); command.ExecuteReader(); connection.Close(); return thisrow; Tnq U in advance!!!

  Read the article

• MySQL SELECT combining 3 SELECTs INTO 1

  - by Martin Tóth

  Consider following tables in MySQL database: entries: creator_id INT entry TEXT is_expired BOOL other: creator_id INT entry TEXT userdata: creator_id INT name VARCHAR etc... In entries and other, there can be multiple entries by 1 creator. userdata table is read only for me (placed in other database). I'd like to achieve a following SELECT result: +------------+---------+---------+-------+ | creator_id | entries | expired | other | +------------+---------+---------+-------+ | 10951 | 59 | 55 | 39 | | 70887 | 41 | 34 | 108 | | 88309 | 38 | 20 | 102 | | 94732 | 0 | 0 | 86 | ... where entries is equal to SELECT COUNT(entry) FROM entries GROUP BY creator_id, expired is equal to SELECT COUNT(entry) FROM entries WHERE is_expired = 0 GROUP BY creator_id and other is equal to SELECT COUNT(entry) FROM other GROUP BY creator_id. I need this structure because after doing this SELECT, I need to look for user data in the "userdata" table, which I planned to do with INNER JOIN and select desired columns. I solved this problem with selecting "NULL" into column which does not apply for given SELECT: SELECT creator_id, COUNT(any_entry) as entries, COUNT(expired_entry) as expired, COUNT(other_entry) as other FROM ( SELECT creator_id, entry AS any_entry, NULL AS expired_entry, NULL AS other_enry FROM entries UNION SELECT creator_id, NULL AS any_entry, entry AS expired_entry, NULL AS other_enry FROM entries WHERE is_expired = 1 UNION SELECT creator_id, NULL AS any_entry, NULL AS expired_entry, entry AS other_enry FROM other ) AS tTemp GROUP BY creator_id ORDER BY entries DESC, expired DESC, other DESC ; I've left out the INNER JOIN and selecting other columns from userdata table on purpose (my question being about combining 3 SELECTs into 1). Is my idea valid? = Am I trying to use the right "construction" for this? Are these kind of SELECTs possible without creating an "empty" column? (some kind of JOIN) Should I do it "outside the DB": make 3 SELECTs, make some order in it (let's say python lists/dicts) and then do the additional SELECTs for userdata? Solution for a similar question does not return rows where entries and expired are 0. Thank you for your time.

  Read the article

• Weird MySQL behavior, seems like a SQL bug

  - by Daniel Magliola

  I'm getting a very strange behavior in MySQL, which looks like some kind of weird bug. I know it's common to blame the tried and tested tool for one's mistakes, but I've been going around this for a while. I have 2 tables, I, with 2797 records, and C, with 1429. C references I. I want to delete all records in I that are not used by C, so i'm doing: select * from i where id not in (select id_i from c); That returns 0 records, which, given the record counts in each table, is physically impossible. I'm also pretty sure that the query is right, since it's the same type of query i've been using for the last 2 hours to clean up other tables with orphaned records. To make things even weirder... select * from i where id in (select id_i from c); DOES work, and brings me the 1297 records that I do NOT want to delete. So, IN works, but NOT IN doesn't. Even worse: select * from i where id not in ( select i.id from i inner join c ON i.id = c.id_i ); That DOES work, although it should be equivalent to the first query (i'm just trying mad stuff at this point). Alas, I can't use this query to delete, because I'm using the same table i'm deleting from in the subquery. I'm assuming something in my database is corrupt at this point. In case it matters, these are all MyISAM tables without any foreign keys, whatsoever, and I've run the same queries in my dev machine and in the production server with the same result, so whatever corruption there might be survived a mysqldump / source cycle, which sounds awfully strange. Any ideas on what could be going wrong, or, even more importantly, how I can fix/work around this? Thanks! Daniel

  Read the article

• How to link a table to a field a in MySQL server

  - by Nek

  I have this data from a xml file: <?xml version="1.0" encoding="utf-8" ?> <words> <id>...</id> <word>...</word> <meaning>...</meaning> <translation> <ES>...</ES> <PT>...</PT> </translation> </words> This forms the table named "words", which has four fields ("id","word","meaning" and "translation"). On the other hand, the "translation" field can hold several languages like ES,PT,EN,JA,KO,etc... So I create a table ("words.translation", one field is "id" and the others ones are languages ids like "ES","PT",...). I'm sorry for this newby question, but I'd like to know a couple of things about this one-to-many relationship. How to join (or link?) this two tables in MySQL? What information does the "translation" field in the "words" table has to store? How is the sql query to get all the word information (JOIN syntax used?) Thanks for your patience.

  Read the article

< Previous Page | 146 147 148 149 150 151 152 153 154 155 156 157  | Next Page >