Search Results

Search found 20838 results on 834 pages for 'mysql num rows'.

Page 165/834 | < Previous Page | 161 162 163 164 165 166 167 168 169 170 171 172  | Next Page >

• Write a SQL to meet my requirement.

  - by rgksugan

  I have been trying to solve this problem for a lot of days. But wouldn't. Please help me. I need a SQL to list product_code, product_name, qty_sold, last_order_date for all the products that have been sold within a date range sorted by the number of quantity sold. My Table structure: tbl_product(product_id,product_code,product_name) tbl_order_detail(order_item_id,order_id,product_id,quantity) tbl_order(order_id,order_date)

  Read the article

• Need help with a SELECT statement

  - by Travis

  I express the relationship between records and searchtags that can be attached to records like so: TABLE RECORDS id name TABLE SEARCHTAGS id recordid name I want to be able to SELECT records based on the searchtags that they have. For example, I want to be able to SELECT all records that have searchtags: (1 OR 2 OR 5) AND (6 OR 7) AND (10) Using the above data structure, I am uncertain how to structure the SQL to accomplish this. Any suggestions? Thanks!

  Read the article

• mysql changing delimiter

  - by jimsmith

  I'm trying to add this function using php myadmin, first off I get on error line 5, which is apparently because you need to change the delimiter from ; to something else so i tried this DELIMITER | CREATE FUNCTION LEVENSHTEIN (s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS INT DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; DECLARE s1_char CHAR; DECLARE cv0, cv1 VARBINARY(256); SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0; IF s1 = s2 THEN RETURN 0; ELSEIF s1_len = 0 THEN RETURN s2_len; ELSEIF s2_len = 0 THEN RETURN s1_len; ELSE WHILE j <= s2_len DO SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; END WHILE; WHILE i <= s1_len DO SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1; WHILE j <= s2_len DO SET c = c + 1; IF s1_char = SUBSTRING(s2, j, 1) THEN SET cost = 0; ELSE SET cost = 1; END IF; SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost; IF c > c_temp THEN SET c = c_temp; END IF; SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1; IF c > c_temp THEN SET c = c_temp; END IF; SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1; END WHILE; SET cv1 = cv0, i = i + 1; END WHILE; END IF; RETURN c; END DELIMITER ; But I get this error: #1064 - 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 'delimiter | Please help !?

  Read the article

• How can get unique values from data table using dql?

  - by piemesons

  I am having a table in which there is a column in which various values are stored.i want to retrieve unique values from that table using dql. Doctrine_Query::create() ->select('rec.school') ->from('Records rec') ->where("rec.city='$city' ") ->execute(); Now i want only unique values. Can anybody tell me how to do that... Edit Table Structure: CREATE TABLE IF NOT EXISTS `records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `school` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16334 ; This is the Query I am using: Doctrine_Query::create() ->select('DISTINCT rec.city') ->from('Records rec') ->where("rec.state = '$state'") // ->getSql(); ->execute(); Generting Sql for this gives me: SELECT DISTINCT r.id AS r__id, r.city AS r__city FROM records r WHERE r.state = 'AR' Now check the sql generated:::: DISTINCT is on 'id' column where as i want Distinct on city column. Anybody know how to fix this. EDIT2 Id is unique cause its an auto incremental value.Ya i have some real duplicates in city column like: Delhi and Delhi. Right.. Now when i am trying to fetch data from it, I am getting Delhi two times. How can i make query like this: select DISTINCT rec.city where state="xyz"; Cause this will give me the proper output. EDIT3: Anybody who can tell me how to figure out this query..???

  Read the article

• jQuery nextUntill id < num or alternative

  - by Volmar

  Hi, i'm using jQuery to show/hide different LI-elements based on their classes. Look at this example. <li id="1" class="fp de1"></li> <li id="2" class="fp de1"><button onclick="hide(2,2);"></li> <li id="3" class="fp de2"><button onclick="hide(3,3);"></li> <li id="4" class="fp de3"><button onclick="hide(4,4);"></li> <li id="5" class="fp de4"></li> <li id="6" class="fp de3"></li> <li id="7" class="fp de3"></li> <li id="8" class="fp de1"><button onclick="hide(8,2);"></li> <li id="9" class="fp de2"><button onclick="hide(9,3);"></li> <li id="10" class="fp de3"><button onclick="hide(10,4);"></li> <li id="11" class="fp de4"></li> You se that some of these have a button with a hide funcion. what i want is that when you press the hide button The following elements the have a highernumber in the .de# class should be hidden untill it reaches a LI with the same .de#-class. so if you press the hide(), i want LIs with ids 3,4,5,6,7 to be hiden. if i press the next on i want 4,5,6,7, and the thirs i want id 5 to be hidden. so this is the Javascript i made for it: function hide(id,de){ var de2 = de-1; $('#'+id).nextUntil('li.de'+de2).hide(); } The problem is that this function is not working exactly as i want. it would work correctly in the first hide()-function and the thirs but not in hide()function number two. here it will hide IDs: 4-8. so i want to do something. so i want the nextuntill() to hide elements untill it reaches a LI-element with the same .de# or a lower .de#. i hope i didn't complicate it to much in my description of the problem. if you have better idea than using nextUntill i'm all ears.

  Read the article

• Table alias -- Unkown column in field list

  - by Jason

  Hi all, I have a sql query which is executing a LEFT JOIN on 2 tables in which some of the columns are ambiguous. I can prefix the joined tables but when I try to prefix one of the columns from the table in the FROM clause, it tells me Unknown column. I even tried giving that table an alias like so ...From points AS p and using "p" to prefix the tables but that didn't work either. Can someone tell me what I'm doing wrong. Here is my query: SELECT point_title, point_url, address, city, state, zip_code, phone, `points`.`lat`, `points`.`longi`, featured, kmlno, image_url, category.title, category_id, point_id, lat, longi, reviews.star_points, reviews.review_id, count(reviews.point_id) as totals FROM (SELECT *, ( 3959 * acos( cos( radians('37.7717185') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('-122.4438929') ) + sin( radians('37.7717185') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '25') as distResults LEFT JOIN category USING ( category_id ) LEFT JOIN reviews USING ( point_id ) WHERE (point_title LIKE '%Playgrounds%' OR category.title LIKE '%Playgrounds%') GROUP BY point_id ORDER BY totals DESC, distance LIMIT 0 , 10

  Read the article

• ASP.Net / MySQL : Translating content into several languages

  - by philwilks

  I have an ASP.Net website which uses a MySQL database for the back end. The website is an English e-commerce system, and we are looking at the possibility of translating it into about five other languages (French, Spanish etc). We will be getting human translators to perform the translation - we've looked at automated services but these aren't good enough. The static text on the site (e.g. headings, buttons etc) can easily be served up in multiple languages via .Net's built in localization features (resx files etc). The thing that I'm not so sure about it how best to store and retrieve the multi-language content in the database. For example, there is a products table that includes these fields... productId (int) categoryId (int) title (varchar) summary (varchar) description (text) features (text) The title, summary, description and features text would need to be available in all the different languages. Here are the two options that I've come up with... Create additional field for each language For example we could have titleEn, titleFr, titleEs etc for all the languages, and repeat this for all text columns. We would then adapt our code to use the appropriate field depending on the language selected. This feels a bit hacky, and also would lead to some very large tables. Also, if we wanted to add additional languages in the future it would be time consuming to add even more columns. Use a lookup table We could create a new table with the following format... textId | languageId | content ------------------------------- 10 | EN | Car 10 | FR | Voiture 10 | ES | Coche 11 | EN | Bike 11 | FR | Vélo We'd then adapt our products table to reference the appropriate textId for the title, summary, description and features instead of having the text stored in the product table. This seems much more elegant, but I can't think of a simple way of getting this data out of the database and onto the page without using complex SQL statements. Of course adding new languages in the future would be very simple compared to the previous option. I'd be very grateful for any suggestions about the best way to achieve this! Is there any "best practice" guidance out there? Has anyone done this before?

  Read the article

• Join unrelated tables through a second level connected table

  - by Andy M

  Hello! I have two tables of activities on a page: Views & Comments. Views id timestamp project_id user_id page_id Comments id timestamp project_id user_id page_id comment Pages id project_id title Now pages are related to projects: Projects id account_id title I am trying to create a summary page that combines views and comments ordered by time (so that the most recent views/comments are at the beginning, grouped by projects. Also, only projects for a specific account. So the result could potentially be: Project 1 View 5 (June 20th) View 4 (June 18th) Comment 5 (June 15th) Comment 4 (June 14th) Comment 3 (June 12th) Project 3 View 3 (June 10th) View 2 (June 8th) Comment 2 (June 7th) Project 2 View 1 (June 5th) Comment 1 (June 4th) If you could help with how to do this using SQL (or even doctrine) that would be awesome. Thank you.

  Read the article

• php connecting to mysql server(localhost) very slow

  - by Ahmad

  actually its little complicated: summary: the connection to DB is very slow. the page rendering takes around 10 seconds but the last statement on the page is an echo and i can see its output while the page is loading in firefox (IE is same). in google chrome the output becomes visible only when the loading finishes. loading time is approximately the same across browsers. on debugging i found out that its the DB connectivity that is creating problem. the DB was on another machine. to debug further. i deployed the DB on my local machine .. so now the DB connection is at 127.0.0.1 but the connectivity still takes long time. this means that the issue is with APACHE/PHP and not with mysql. but then i deployed my code on another machine which connects to DB remotely.and everything seems fine. basically the application uses couple of mod_rewrite.. but i removed all the .htaccess files and the slow connectivity issue remains.. i installed another APACHE on my machine and used default settings. the connection was still very slow. i added following statements to measure the execution time $stime = microtime(); $stime = explode(" ",$stime); $stime = $stime[1] + $stime[0]; // my code -- it involves connection to DB $mtime = microtime(); $mtime = explode(" ",$mtime); $mtime = $mtime[1] + $mtime[0]; $totaltime = ($mtime - $stime); echo $totaltime; the output is 0.0631899833679 but firebug Net panel shows total loading time of 10-11 seconds. same is the case with google chrome i tried to turn off windows firewall.. connectivity is still slow and i just can't quite find the reason.. i've tried multiple DB servers.. multiple apaches.. nothing seems to be working.. any idea of what might be the problem?

  Read the article

• $query returns results but not the ones i want: $query looks good to me :S

  - by Toni Michel Caubet

  I'll start again, Lets say My data is: Table element (id,name,....) 1, name element 1, .... 2, name element 2, .... 3, name element 3, .... Table tags (id,name,id_element, ....) 1, happy , 1 2, result, 1 3, very , 1 4, element, 2 5, another, 3 6, element, 1 7, happy, 2 So if search is 'very, happy,element,result': Results i would like 1) element with id = 2 because it has all tags 2) element with id = 1 because it has the tag 'element' and the tag 'happy' (only 2 less taggs) 3) .... (only 3 less taggs) So if search is 'happy,element': Results i would like 1) element with id = 1 because it has all tags (and no more) 2) element with id = 2 because it has the tag 'element' and the tag 'happy' (and two more tags) 3) .... and 3 more tags This is an echo to my query: (it doesn't fit al requirements i wrote, but its first test to find with matched tags) SELECT element.id as id_deseada,tagg.* FROM element,tagg WHERE tagg.id_element = element.id AND tagg.nombre IN ('happy','tagg','result') GROUP BY tagg.id_element ORDER BY element.votos This returns 10 duplicated elements... :S and doen't even have all taggs (and on database there are taggs with 'happy' results) if it helps, thats how i get the elements of a tag (by name and with only one tagg) $query = "SELECT element.id FROM element,tagg WHERE tagg.nombre = '$nombre_tagg' AND tagg.id_element = element.id AND lan = '$lan' GROUP BY tagg.id_element"; I hope it's a bit easier to understand now, excuse my english.. :) Thanks a lot for you possible aportation!

  Read the article

• How do I re-create a MySQL InnoDB table from an .ibd file?

  - by knorv

  Assume that the following MySQL files have been restored from a backup tape: tablename.frm tablename.ibd Furthermore, assume that the MySQL installation was running with innodb_file_per_table and that the database was cleanly shutdown with mysqladmin shutdown. Given a fresh install of the same MySQL version that the restored MySQL files were taken from, how do I import the data from tablename.ibd/tablename.frm into this new install?

  Read the article

• Strange data swapping error occurs when I attempt to update rows in my table from another table in m

  - by Wesley

  So I have a table of data that is 10,000 lines long. Several of the columns in the table simply describe information about one of the columns, meaning, that only one column has the content, and the rest of the columns describe the location of the content (its for a book). Right now, only 6,000 of the 10,000 rows' content column is filled with its content. Rows 6-10,000's content column simply says null. I have another table in the db that has the content for rows 6,000-10,000, with the correct corresponding primary key which would (seemingly) make it easy to update the 10,000 row table. I have been trying an update query such as the following: UPDATE table(10,000) SET content_column = (SELECT content FROM table(6,000-10,000) WHERE table(10,000).id = table(6-10,000.id) Which kind of works, the only problem is that it pulls in the data from the second table just fine, but it replaces the existing content column with null. So rows 1-6,000's content column become null, and rows 6-10,000's content column have the correct values...Pretty strange I thought anyway. Does anybody have any thoughts about where I am going wrong? If you could show me a better sql query, I would appreciate it! Thanks

  Read the article

• Java: Making concurrent MySQL queries from multiple clients synchronised

  - by Misha Gale

  I work at a gaming cybercafe, and we've got a system here (smartlaunch) which keeps track of game licenses. I've written a program which interfaces with this system (actually, with it's backend MySQL database). The program is meant to be run on a client PC and (1) query the database to select an unused license from the pool available, then (2) mark this license as in use by the client PC. The problem is, I've got a concurrency bug. The program is meant to be launched simultaneously on multiple machines, and when this happens, some machines often try and acquire the same license. I think that this is because steps (1) and (2) are not synchronised, i.e. one program determines that license #5 is available and selects it, but before it can mark #5 as in use another copy of the program on another PC tries to grab that same license. I've tried to solve this problem by using transactions and table locking, but it doesn't seem to make any difference - Am I doing this right? Here follows the code in question: public LicenseKey Acquire() throws SmartLaunchException, SQLException { Connection conn = SmartLaunchDB.getConnection(); int PCID = SmartLaunchDB.getCurrentPCID(); conn.createStatement().execute("LOCK TABLE `licensekeys` WRITE"); String sql = "SELECT * FROM `licensekeys` WHERE `InUseByPC` = 0 AND LicenseSetupID = ? ORDER BY `ID` DESC LIMIT 1"; PreparedStatement statement = conn.prepareStatement(sql); statement.setInt(1, this.id); ResultSet results = statement.executeQuery(); if (results.next()) { int licenseID = results.getInt("ID"); sql = "UPDATE `licensekeys` SET `InUseByPC` = ? WHERE `ID` = ?"; statement = conn.prepareStatement(sql); statement.setInt(1, PCID); statement.setInt(2, licenseID); statement.executeUpdate(); statement.close(); conn.commit(); conn.createStatement().execute("UNLOCK TABLES"); return new LicenseKey(results.getInt("ID"), this, results.getString("LicenseKey"), results.getInt("LicenseKeyType")); } else { throw new SmartLaunchException("All licenses of type " + this.name + "are in use"); } }

  Read the article

• Updating fields in one table that are not empty

  - by WmasterJ

  I got this SQL: UPDATE users2 SET picture = 'sites/site2/files/pictures/' + picture; WHERE picture NOT IS NULL And the only thing I get are that all picture fields get the value '0'.

  Read the article

• how to diffrentiate between same field names of two tables in a select query??

  - by developer

  i have more than two tables in my database and all of them contains same field names like table A table B table C field1 field1 field1 field2 field2 field2 field3 field3 field3 . . . . . . . . . . . . I have to write a SELECT query which gets almost all same fields from these 3 tables.Iam using something like this :- select a.field1,a.field2,a.field3,b.field1,b.field2,b.field3,c.field1,c.field2,c.field3 from table A as a, table B as b,table C as c where so and so. but when i print field1's value it gives me the last table values. How can i get all the values of three tables with the same field names??? do i have to write individual query for every table OR there is any ways of fetching them all in a single query????

  Read the article

• Faster way to know the tolal number of rows in Mysql Database?

  - by Starx

  If I need to know the total number of rows in a table of database I do something like $query = "SELECT * FROM tablename WHERE link='1';"; $result = mysql_query($query); $rows = mysql_fetch_array($result); $count = count($rows); So you see the total number of data is recovered scanning through entire database Is there a better way

  Read the article

• How to create wordpress-like option table and get values for each row?

  - by Nacho

  Hi guys. I'm looking to create an options table in my db that makes every record a system option, so I can work with a little number of fields. My db has the following structure: 3 columns named id, name, and value The following data is inserted as an example: +--+-----------+--------------------------+ |id|name |value | +--+-----------+--------------------------+ | 1|uri |www.example.com | | 2|sitename |Working it out | | 3|base_folder|/folder1/folder2/ | | 4|slogan |Just a slogan for the site| +--+-----------+--------------------------+ That way I can include a large number of customizable system options very easily. The problem is that I don't know how to retrieve them. How do I get the value of uri and store it as a var? And better yet, how do I get, for exmaple, values of id 1 and 4 only without making a query each time? (I assume multiple queries are useless and a pretty ugly method.) I know the question is pretty basic but I'm lost here. I'd really appreciate your answer!

  Read the article

• Delphi / MySql : Problems escpaing strings

  - by mawg

  N00b here, having problems escaping strings. I used the QuotedStr() function - shouldn't that be enough. Unfortunately, the string that I am trying to quote is rather messy, but I will post it here in case anyone wants to paste it into WinMerge or KDiff3, etc. I am trying to store an entire Delphi form into the database, rather than into a .DFM file. It has only one field, a TEdit edit box. The debugger shows the form as text as 'object Form1: TScriptForm'#$D#$A' Left = 0'#$D#$A' Top = 0'#$D#$A' Align = alClient'#$D#$A' BorderStyle = bsNone'#$D#$A' ClientHeight = 517'#$D#$A' ClientWidth = 993'#$D#$A' Color = clBtnFace'#$D#$A' Font.Charset = DEFAULT_CHARSET'#$D#$A' Font.Color = clWindowText'#$D#$A' Font.Height = -11'#$D#$A' Font.Name = 'MS Sans Serif''#$D#$A' Font.Style = []'#$D#$A' OldCreateOrder = False'#$D#$A' SaveProps.Strings = ('#$D#$A' 'Visible=False')'#$D#$A' PixelsPerInch = 96'#$D#$A' TextHeight = 13'#$D#$A' object Edit1: TEdit'#$D#$A' Left = 192'#$D#$A' Top = 64'#$D#$A' Width = 121'#$D#$A' Height = 21'#$D#$A' TabOrder = 8'#$D#$A' end'#$D#$A'end'#$D#$A before calling QuotedStr() and ''object Form1: TScriptForm'#$D#$A' Left = 0'#$D#$A' Top = 0'#$D#$A' Align = alClient'#$D#$A' BorderStyle = bsNone'#$D#$A' ClientHeight = 517'#$D#$A' ClientWidth = 993'#$D#$A' Color = clBtnFace'#$D#$A' Font.Charset = DEFAULT_CHARSET'#$D#$A' Font.Color = clWindowText'#$D#$A' Font.Height = -11'#$D#$A' Font.Name = ''MS Sans Serif'''#$D#$A' Font.Style = []'#$D#$A' OldCreateOrder = False'#$D#$A' SaveProps.Strings = ('#$D#$A' ''Visible=False'')'#$D#$A' PixelsPerInch = 96'#$D#$A' TextHeight = 13'#$D#$A' object Edit1: TEdit'#$D#$A' Left = 192'#$D#$A' Top = 64'#$D#$A' Width = 121'#$D#$A' Height = 21'#$D#$A' TabOrder = 8'#$D#$A' end'#$D#$A'end'#$D#$A''' afterwards. The strange thing is that my complete command 'INSERT INTO designerFormDfm(designerFormDfmText) VALUES ("'object Form1: TScriptForm'#$D#$A' Left = 0'#$D#$A' Top = 0'#$D#$A' Align = alClient'#$D#$A' BorderStyle = bsNone'#$D#$A' ClientHeight = 517'#$D#$A' ClientWidth = 993'#$D#$A' Color = clBtnFace'#$D#$A' Font.Charset = DEFAULT_CHARSET'#$D#$A' Font.Color = clWindowText'#$D#$A' Font.Height = -11'#$D#$A' Font.Name = ''MS Sans Serif'''#$D#$A' Font.Style = []'#$D#$A' OldCreateOrder = False'#$D#$A' SaveProps.Strings = ('#$D#$A' ''Visible=False'')'#$D#$A' PixelsPerInch = 96'#$D#$A' TextHeight = 13'#$D#$A' object Edit1: TEdit'#$D#$A' Left = 192'#$D#$A' Top = 64'#$D#$A' Width = 121'#$D#$A' Height = 21'#$D#$A' TabOrder = 8'#$D#$A' end'#$D#$A'end'#$D#$A''");' executes in a MySql console, but not from Delphi, where I pass that command as parameter command to a function which ADOCommand.CommandText := command; ADOCommand.CommandType := cmdText; ADOCommand.Execute(); I can only assume that I am having problems escpaing sequences which contain single quotes (and QuotedStr() doesn't seem to escape backslahes(?!)) What am I doing that is obviously, glaringly wrong?

  Read the article

• Is MySQL Replication Appropriate in this case?

  - by MJB

  I have a series of databases, each of which is basically standalone. It initially seemed like I needed a replication solution, but the more I researched it, the more it felt like replication was overkill and not useful anyway. I have not done MySQL replication before, so I have been reading up on the online docs, googling, and searching SO for relevant questions, but I can't find a scenario quite like mine. Here is a brief description of my issue: The various databases almost never have a live connection to each other. They need to be able to "sync" by copying files to a thumb drive and then moving them to the proper destination. It is OK for the data to not match exactly, but they should have the same parent-child relationships. That is, if a generated key differs between databases, no big deal. But the visible data must match. Timing is not critical. Updates can be done a week later, or even a month later, as long as they are done eventually. Updates cannot be guaranteed to be in the proper order, or in any order for that matter. They will be in order from each database; just not between databases. Rather than a set of master-slave relationships, it is more like a central database (R/W) and multiple remote databases (also R/W). I won't know how many remote databases I have until they are created. And the central DB won't know that a database exists until data arrives from it. (To me, this implies I cannot use the method of giving each its own unique identity range to guarantee uniqueness in the central database.) It appears to me that the bottom line is that I don't want "replication" so much as I want "awareness". I want the central database to know what happened in the remote databases, but there is no time requirement. I want the remote databases to be aware of the central database, but they don't need to know about each other. WTH is my question? It is this: Does this scenario sound like any of the typical replication scenarios, or does it sound like I have to roll my own? Perhaps #7 above is the only one that matters, and given that requirement, out-of-the-box replication is impossible. EDIT: I realize that this question might be more suited to ServerFault. I also searched there and found no answers to my questions. And based on the replication questions I did find both on SO and SF, it seemed that the decision was 50-50 over where to put my question. Sorry if I guessed wrong.

  Read the article

• Multiple column subselect in mysql 5 (5.1.42)

  - by rubber boots

  This one seems to be a simple problem, but I can't make it work in a single select or nested select. Retrieve the authors and (if any) advisers of a paper (article) into one row. I order to explain the problem, here are the two data tables (pseudo) papers (id, title, c_year) persons (id, firstname, lastname) plus a link table w/one extra attribute (pseudo): paper_person_roles( paper_id person_id act_role ENUM ('AUTHOR', 'ADVISER') ) This is basically a list of written papers (table: papers) and a list of staff and/or students (table: persons) An article my have (1,N) authors. An article may have (0,N) advisers. A person can be in 'AUTHOR' or 'ADVISER' role (but not at the same time). The application eventually puts out table rows containing the following entries: TH: || Paper_ID | Author(s) | Title | Adviser(s) | TD: || 21334 |John Doe, Jeff Tucker|Why the moon looks yellow|Brown, Rayleigh| ... My first approach was like: select/extract a full list of articles into the application, eg.SELECT q.id, q.title FROM papers AS q ORDER BY q.c_year and save the results of the query into an array (in the application). After this step, loop over the array of the returned information and retrieve authors and advisers (if any), via prepared statement (? is the paper's id) from the link table like:APPLICATION_LOOP(paper_ids in array) SELECT p.lastname, p.firstname, r.act_role FROM persons AS p, paper_person_roles AS r WHERE p.id=r.person_id AND r.paper_id = ? # The application does further processing from here (pseudo): foreach record from resulting records if record.act_role eq 'AUTHOR' then join to author_column if record.act_role eq 'ADVISER' then join to avdiser_column end print id, author_column, title, adviser_column APPLICATION_LOOP This works so far and gives the desired output. Would it make sense to put the computation back into the DB? I'm not very proficient in nontrivial SQL and can't find a solution with a single (combined or nested) select call. I tried sth. like SELECT q.title (CONCAT_WS(' ', (SELECT p.firstname, p.lastname AS aunames FROM persons AS p, paper_person_roles AS r WHERE q.id=r.paper_id AND r.act_role='AUTHOR') ) ) AS aulist FROM papers AS q, persons AS p, paper_person_roles AS r in several variations, but no luck ... Maybe there is some chance? Thanks in advance r.b.

  Read the article

• How to convert rows returned by a query into columns in oracle?

  - by Piyush Lohana

  I have to display the results of the below query as columns. select to_char(sysdate + 1 - rownum,'MON-YYYY') as d from all_objects where trunc(sysdate + 1 - rownum,'MM') = trunc(to_date(:from_date,'MON-YYYY'),'MM') minus select to_char(sysdate + 1 - rownum,'MON-YYYY') as d from all_objects where trunc(sysdate + 1 - rownum,'MM') trunc(to_date(':to_date','MON-YYYY'),'MM') Please help me in figuring that out.

  Read the article

• Insert Registration Data in MySQL using PHP

  - by J M 4

  I may not be asking this in the best way possible but i will try my hardest. Thank you ahead of time for your help: I am creating an enrollment website which allows an individual OR manager to enroll for medical testing services for professional athletes. I will NOT be using the site as a query DB which anybody can view information stored within the database. The information is instead simply stored, and passed along in a CSV format to our network provider so they can use as needed after the fact. There are two possible scenarios: Scenario 1 - Individual Enrollment If an individual athlete chooses to enroll him/herself, they enter their personal information, submit their payment information (credit/bank account) for processing, and their information is stored in an online database as Athlete1. Scenario 2 - Manager Enrollment If a manager chooses to enroll several athletes he manages/ promotes for, he enters his personal information, then enters the personal information for each athlete he wishes to pay for (name, address, ssn, dob, etc), then submits payment information for ALL athletes he is enrolling. This number can range from 1 single athlete, up to 20 athletes per single enrollment (he can return and complete a follow up enrollment for additional athletes). Initially, I was building the database to house ALL information regardless of enrollment type in a single table which housed over 400 columns (think 20 athletes with over 10 fields per athlete such as name, dob, ssn, etc). Now that I think about it more, I believe create multiple tables (manager(s), athlete(s)) may be a better idea here but still not quite sure how to go about it for the following very important reasons: Issue 1 If I list the manager as the parent table, I am afraid the individual enrolling athlete will not show up in the primary table and will not be included in the overall registration file which needs to be sent on to the network providers. Issue 2 All athletes being enrolled by a manager are being stored in SESSION as F1FirstName, F2FirstName where F1 and F2 relate to the id of the fighter. I am not sure technically speaking how to store multiple pieces of information within the same table under separate rows using PHP. For example, all athleteswill have a first name. The very basic theory of what i am trying to do is: If number_of_athletes 1, store F1FirstName in row 1, column 1 of Table "Athletes"; store F1LastName in row 1, column 2 of Table "Athletes"; store F2FirstName in row 2, column 1 of Table "Athletes"; store F2LastName in row 2, column 2 of table "Athletes"; Does this make sense? I know this question is very long and probably difficult so i appreciate the guidance.

  Read the article

• MySQLdb rowcount Returns Nothing

  - by Alec K.

  I am trying to log into my table called acounts using MySQLdb in Python, but it does not work for me. I keep getting my message "Not Logged In". Here is my code: database = MySQLdb.connect("127.0.0.1", "root", "pswd", "Kazzah") cursor = database.cursor() cursor.execute("SELECT * FROM Accounts WHERE Email=%s AND Password=%s", (_Email, _Password)) database.commit() numrows = cursor.rowcount if numrows == 1: msg = "Logged In" else: msg = "Not Logged In" cursor.close() database.close() What am I doing wrong? Thanks.

  Read the article

• howto have condition in a nested SQL query?

  - by fenec

  here is my SQL statement , i would like to find all the games that have the status 0 and names of teams that are like key_word or the sport's name that are like the key word. The problem is that all the games that are displayed don't have status 0 . What am i doing wrong? sql="select * from games where games.status=0 and games.team_2_id IN (select id from teams where name like '"+key_word+"') or games.team_1_id IN (select id from teams where name like '"+key_word+"') or games.sport like '"+key_word+"' "

  Read the article

• MySQL command-line tool: How to find out number of rows affected by a DELETE?

  - by ambivalence

  I'm trying to run a script that deletes a bunch of rows in a MySQL (innodb) table in batches, by executing the following in a loop: mysql --user=MyUser --password=MyPassword MyDatabase < SQL_FILE where SQL_FILE contains a DELETE FROM ... LIMIT X command. I need to keep running this loop until there's no more matching rows. But unlike running in the mysql shell, the above command does not return the number of rows affected. I've tried -v and -t but neither works. How can I find out how many rows the batch script affected? Thanks!

  Read the article

< Previous Page | 161 162 163 164 165 166 167 168 169 170 171 172  | Next Page >