Search Results

Search found 14016 results on 561 pages for 'mysql'.

Page 181/561 | < Previous Page | 177 178 179 180 181 182 183 184 185 186 187 188  | Next Page >

• Mysql- "FLUSH TABLES WITH READ LOCK" started automatically

  - by ming yeow

  I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being locked up by "FLUSH TABLES WITH READ LOCK" 03:21:31 select type_id, count(*) from guid_target_infos group by type_id 02:38:11 select type_id, count(*) from guid_infos group by type_id 02:24:29 FLUSH TABLES WITH READ LOCK But i did not start this command. can someone tell me why it was started automatically?

  Read the article

• Optimise & improve performance of this MYSQL query

  - by David

  SELECT u.id, u.honour, COUNT(*) + 1 AS rank FROM user_info u INNER JOIN user_info u2 ON u.honour < u2.honour WHERE u.id = '$id' AND u2.status = 'Alive' AND u2.rank != '14' This query is currently utterly raping my server. It works out based on your honour what rank you are within the 'user_info' table which stores it out of all our users. Screenshot for explain. http://cl.ly/370z0v2Y3v2X1t1r1k2A SELECT u.id, u.honour, COUNT(*)+1 as rank FROM user_info u USE INDEX (prestigeOptimiser) INNER JOIN user_info u2 ON u.honour < u2.honour WHERE u.id='3' AND u2.status='Alive' AND u2.rank!='14'

  Read the article

• select command in mysql doesnot return any row

  - by jeyshree

  i created a database using the command CREATE TABLE login_table2(user_name VARCHAR(32), first_name VARCHAR(32), last_name VARCHAR(32), password VARCHAR(64)); then i inserted a data using command INSERT INTO login_table2(user_name ,first_name , last_name , password ) VALUES('ramya', 'ramya', 'muthu', 'India'); the data got inserted into the table. then i inserted another set of data using command INSERT INTO login_table2(user_name ,first_name , last_name , password ) VALUES('jeyshree', 'jey', 'shree', 'India'); the data got inserted into the table too. then i gave the command SELECT first_name FROM login_table2; the command displayed all the first_ name in the table. however when i gave the command SELECT password FROM login_table2 WHERE user_name = 'ramya'; it does not fetch anything though the entry exist in the table.mention where i am going wrong.awaiting your reply.

  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

• Question about Benchmark funcion in Mysql ( Incredible results ).

  - by xRobot

  I have 2 tables: author with 3 millions of rows. book with 20 miles rows. . So I have benchmarked this query with a join: SELECT BENCHMARK(100000000, 'SELECT book.title, author.name FROM `book` , `author` WHERE book.id = author.book_id ') And this is the result: Query took 0.7438 sec ONLY 0.7438 seconds for 100 millions of query with a join ??? Do I make some mistakes or this is the right result ?

  Read the article

• MySQL- complex data query in a single statement

  - by yoda

  Hi, Consider the following structure : Ignore the table user_token. Now, imagine that you need to get all the roles related to an user, wich may be through it's related groups or directly related to him. In case the same role appears related to a group and the user directly, the role related to the user will prevail over the role given by the group. Is there any chance this could be done in a single query? Cheers!

  Read the article

• Decimal VS Int in MySQL?

  - by TheOnly92

  Are there any performance difference between decimal(10,0) unsigned type and int(10) unsigned type?

  Read the article

• Dividing numbers between rows in MySQL + PHP

  - by André Figueira

  Hi I am working on a kind of raffle system which divides 1 million random numbers into an x amount of tickets, e.g. 1 million random numbers to 10,000 tickets. Each ticket is a row in a database, we then have another table ticket numbers in which i need to give 100 numbers to each ticket they are related by the ticket id. So at the moment this is my code: //Amount of the 1 million tickets divided to the tickets $numbersPerTickets = $_POST['numbersPerTicket']; //The total cost of the property $propertyPrice = $_POST['propertyPrice']; //The total amount of tickets $totalTickets = NUMBER_CIELING / $numbersPerTickets; //The ticket price $ticketPrice = $propertyPrice / $totalTickets; //Generate array with random numbers up to 999,999 $randomTicketNumbers = createTicketNumbers(); //Creation loop counter $ticketCreationCount = 1; //Loop and create each ticket while($ticketCreationCount <= $totalTickets) { //Create a padded ticket number $ticketNumber = str_pad($ticketCreationCount, 6, 0, STR_PAD_LEFT); $query = ' INSERT INTO tickets( propertyID, ticketNumber, price ) VALUES( "'.$propertyID.'", "'.$ticketNumber.'", "'.$ticketPrice.'" ) '; $db->query($query); //Get the ID of the inserted ticket to use to insert the ticket numbers $ticketID = $db->insert_id; $loopBreak = $numbersPerTickets; $addedNumberCount = 1; foreach($randomTicketNumbers as $key => $value) { $query = ' INSERT INTO ticketNumbers( ticketID, number ) VALUES( "'.$ticketID.'", "'.$value.'" ) '; $db->query($query); unset($randomTicketNumbers[$key]); if($addedNumberCount == $loopBreak){ break; }else{ $addedNumberCount++; } } $ticketCreationCount++; } But this isn't working it adds the right amount of tickets, which in the case for testing is 10,000 but then adds far too many ticket numbers, it ends up exceeding the million numbers in the random tickets array, The random tickets array is just a simple 1 tier array with 1 million numbers sorted randomly.

  Read the article

• Combining 2 Mysql update statments(same col, different values, different conditions)

  - by Paul Atkins

  Hi guys, I have been doing some searching but have not been able to find an answer for this so thought I would ask here as the people here know everything :) I am trying to combine these 2 update queries into one query. UPDATE addresses SET is_default='0' WHERE id!='1' UPDATE addresses SET is_default='1' WHERE id='1' I assume this should be too hard to accomplish but i cant seem to work it out :( Thanks Paul

  Read the article

• Storing apostrophes, exclamation marks, etc. in mysql database

  - by rein

  I changed from latin1 to utf8. Although all sorts of text was displaying fine I noticed non-english characters were stored in the database as weird symbols. I spent a day trying to fix that and finally now non-english characters display as non-english characters in the database and display the same on the browser. However I noticed that I see apostrophes stored as &#39; and exclamation marks stored as &#33;. Is this normal, or should they be appearing as ' and ! in the database instead? If so, what would I need to do in order to fix that?

  Read the article

• MySQL Multiple Subquery on same table

  - by user1444980

  I have a table of the following structure ID | Amount | Bank (1 or 2) ---+--------+------ 1 | 100000 | 1 2 | 256415 | 2 3 | 142535 | 1 1 | 214561 | 2 2 | 123456 | 1 1 | 987654 | 2 I want a result like this (from the same table): ID | sum(Bank 1) | sum(Bank 2) ---+-------------+------------ 1 | 100000 | 1202215 2 | 123456 | 256415 3 | 142535 | 0 What will be the easiest query to achieve this?

  Read the article

• C#:checking existing record in database Mysql

  - by Meko

  HI.I searched this question inform and I found solution to change column property Unique Index.Now If I try to insert same record cmd.ExecuteNonQuery() gives error that record exist ,but how can use this exception to give user a message that record exist and must enter new one ? I am trying to make some thing like if(cmd.ExecuteNonQuery() !=true ) { MessageBox.Show("User Exists"); } But I dont know what returns cmd.ExecuteNonQuery() ? Or I must get records using reader in table and compare them with text in Textfiel?

  Read the article

• PHP & MySQL checked checkbox problem

  - by BeepU

  I'm trying to check if the checkbox has been checked and display the check mark for the user to see when they check there account settings. I want to know how can I fix this problem using PHP so that the check mark is displayed every time the user views their account settings? Here is the HTML. <input type="checkbox" name="privacy_policy" id="privacy_policy" value="yes" />

  Read the article

• mysql date format with changing string value

  - by hacket

  I have a field called Timestamp, that stores its values as text as opposed to an actual Timestamp. The logging application is unchangeable, unfortunately. So table.Timestamp -> text field with format -> "Wed Mar 02 13:28:59 CDT 2011" I have been developing a query to purge all but the most recent row using this as my Timestamp selector, which is also converting the string into a date - MAX( STR_To_DATE( table.Timestamp , '%a %b %d %H:%i:%s CDT %Y' ) My query works perfectly... However, what I've found is that the string value - 'CDT' - changes between 'CDT' and 'CST' depending on whether the current time is daylight savings time or not. During daylight savings time, it logs as 'CDT', and vice versa. So all the rows that contain 'CST' get ignored when I run this - MAX( STR_To_DATE( table.Timestamp , '%a %b %d %H:%i:%s CDT %Y' ) and all the rows that contain 'CDT' get ignored when I run this - MAX( STR_To_DATE( table.Timestamp , '%a %b %d %H:%i:%s CST %Y' ) Is there a way to make it run against both string formats?

  Read the article

• mysql inserts & updates optimized

  - by user271619

  This is an optimization question, mostly. I have many forms on my sites that do simple Inserts and Updates. (Nothing complicated) But, several of the form's input fields are not necessary and may be left empty. (again, nothing complicated) However, my SQL query will have all columns in the Statement. My question, is it best to optimize the Inserts/Update queries appropriately? And only apply the columns that are changed into the query? We all hear that we shouldn't use the "SELECT *" query, unless it's absolutely needed for displaying all columns. But what about Inserts & Updates? Hope this makes sense. I'm sure any amount of optimization is acceptable. But I never really hear about this, specifically, from anyone.

  Read the article

• MySQL customized join query using multiple tables

  - by itgeek

  I am searching one student from each class from one group. There are different class groups and every group has different classes and every class has multiple students. See below: Group1 --> Class1, Class2 etc Class1 --> GreenStudent1, GreenStudent2 etc Class2 --> RedStudent1, RedStudent2 etc ------------------------------------------------------ SELECT table1.id, table1.myname, table1.marks table2.studentid, table2.studentname FROM table1 INNER JOIN table3 ON table1.oldid = table3.id INNER JOIN table2 ON table2.studentid = table3.newid WHERE table1.classgroup = 'SCI79' GROUP BY table1.oldid ORDER BY table1.marks DESC There are different joins applied in the query. Above mentioned query giving me correct results but I need little modification in it. Current query returning me one student from each class. What I need? I need one student from each class but only that student who has MAXIMUM table1.marks So I should have one student from each class who has maximum number in their relevant classes. Can anyone suggest some solution or rewrite this query? Thanks :)

  Read the article

• 0.00 decimal fields in MySQL database giving empty value when in a PHP array

  - by SamD

  The fields read as 0.00 in phpMyAdmin, but when put into an array, the value is blank, is there a way to get it to show just '0'? Thanks Sam

  Read the article

• formatting mysql data for ouptut into a table

  - by bsandrabr

  Following on from a question earlier today this answer was given to read the data into an array and separate it to print vehicle type and then some data for each vehicle. <?php $sql = "SELECT * FROM apparatus ORDER BY vehicleType"; $getSQL = mysql_query($sql); // transform the result set: $data = array(); while ($row = mysql_fetch_assoc($getSQL)) { $data[$row['vehicleType']][] = $row; } ?> <?php foreach ($data as $type => $rows): ?> <h2><?php echo $type?></h2> <ul> <?php foreach ($rows as $vehicleData):?> <li><?php echo $vehicleData['name'];?></li> <?php endforeach ?> </ul> <?php endforeach ?> This is almost perfect for what I want to do but I need to print out two columns from the database ie ford and mondeo before going into the second foreach loop. I've tried print $rows['model'] and all the other combinations I can think of but that doesn't work. Any help much appreciated

  Read the article

• Fighing system in Php & MYSQL

  - by Gully

  I am working on a game like Mafia Wars and i am trying to get the fighting system working but i keep getting lose trying to work out who is going to win the fight and it still needs to know if the stats are close then there is a random chace of them winning. $strength = $my_strength; $otherplayerinfo = mysql_query("SELECT * FROM accounts WHERE id='$player_id'"); $playerinfo = mysql_fetch_array($otherplayerinfo); $players_strength = $playerinfo['stre']; $players_speed = $playerinfo['speed']; $players_def = $playerinfo['def']; if($players_strength > $strength){ $strength_point_player = 1; $strength_point_your = 0; }else{ $strength_point_your = 1; $strength_point_player = 0; } I was trying a point system but i still could not do it.

  Read the article

• mysql select update

  - by Tillebeck

  Hi I have read quite a few selcet+update questions in here but cannot understand how to do it. So will have to ask from the beginning. I would like to update a table based on data in another table. Setup is like this: - TABLE a ( int ; string ) ID WORD 1 banana 2 orange 3 apple - TABLE b ( "comma separated" string ; string ) WORDS TEXTAREA 0 banana -> 0,1 0 orange apple apple -> BEST:0,2,3 ELSE 0,2,3,3 0 banana orange apple -> 0,1,2,3 Now I would like to for each word in TABLE a append ",a.ID" to b.WORDS like: SELECT id, word FROM a (for each) -> UPDATE b SET words = CONCAT(words, ',', a.id) WHERE b.textarea like %a.word% Or even better: replace the word found in b.textarea with ",a.id" so it is the b.textarea that ends up beeing a comma separeted string of id's... But I do not know if that is possible.

  Read the article

• MySQL Casting in C#

  - by user798080

  Okay, so I'm attempting to print out the contents of a table in a comma-separated file. using (OdbcCommand com = new OdbcCommand("SELECT * FROM pie_data WHERE Pie_ID = ?", con)) { com.Parameters.AddWithValue("", Request.Form["pie_id"]); com.ExecuteNonQuery(); using (OdbcDataReader reader = com.ExecuteReader()) { string finalstring = ""; while (reader.Read()) { finalstring = reader.GetString(9) + ","; for (int i = 0; i <= 8; i = i + 1) { finalstring = finalstring + reader.GetString(i) + ","; } } } Response.Write(finalstring); noredirect = 1; } My table layout is: CREATE TABLE `rent_data` ( `Pies` INT(10) UNSIGNED NOT NULL, `Name` VARCHAR(85) NOT NULL, `Email` VARCHAR(85) NOT NULL, `Pie_Rent` DATE NOT NULL, `Rent_To` DATE NOT NULL, `Returned_Date` DATE NULL DEFAULT NULL, `Place` VARCHAR(100) NOT NULL, `Purpose` MEDIUMTEXT NOT NULL, `Comments` MEDIUMTEXT NULL, `Pie_ID` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL, INDEX `Pie_ID` (`Equipment_ID`) ) The error I'm getting is this: Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.String'. On the line: finalstring = finalstring + reader.GetString(i) + ",";

  Read the article

• Help needed with simple mysql group by query

  - by Jack

  This query fails when I add the line shown... :BEGIN FAIL: I have this so far Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <------------------------- FAIL group by CompanyID :END FAIL: Any way to do this? Here's a long winded background if it'll help.... I have a single table query. here's a sample of the table: CompanyID, State 1,OH 1,IL 1,NY 2,IL 3,NY 3,OH 4,NY 5,CA 5,WA I want a query that'll return something like this: 2,IL 4,NY I have this so far Select Companyid, count(*) as cnt from mytable where State is not null group by CompanyID This gives me a count of the number of records for each company. IE: 1,3 2,1 3,2 4,1 5,2 Now I want to filter the above list to just the two records with one result. I tried adding another where clause, but it failed: BEGIN FAIL: I have this so far Select Companyid, count(*) as cnt from mytable where State is not null and cnt = 1 <-------------------- FAIL group by CompanyID END FAIL:

  Read the article

• MySQL: Select pages that are not tagged?

  - by lauthiamkok

  Hi, I have a db with two tables like these below, page table pg_id title 1 a 2 b 3 c 4 d tagged table tagged_id pg_id 1 1 2 4 I want to select the pages which are tagged, I tried with this query below but doesn't work, SELECT * FROM root_pages LEFT JOIN root_tagged ON ( root_tagged.pg_id = root_pages.pg_id ) WHERE root_pages.pg_id != root_tagged.pg_id It returns zero - Showing rows 0 - 1 (2 total, Query took 0.0021 sec) But I want it to return pg_id title 2 b 3 c My query must have been wrong?? How can I return the pages which are not tagged correctly? Thanks.

  Read the article

• In PHP + MySQL, How do I join many tables with conditions

  - by Moe

  Hi, I'm trying to get the users full activity throughout the website. I need to Join many tables throughout the database, with that condition that it is one user. What I currently have written is: SELECT * FROM comments AS c JOIN rphotos AS r ON c.userID = r.userID AND c.userID = '$defineUserID'; But What it is returning is everything about the user, but it repeats rows. For instance, for one user he has 6 photos and 5 comments So I expect the join to return 11 rows. Instead it returns 30 results like so: PhotoID = 1; CommentID = 1; PhotoID = 1; CommentID = 2; PhotoID = 1; CommentID = 3; and so on... What am i doing wrong?

  Read the article

• MySQL whats wrong with my foreign keys?

  - by Skiy

  Hello, what is wrong with the two foreign keys which I have marked with comments? create database db; use db; create table Flug( Flugbez varchar(20), FDatum Date, Ziel varchar(20), Flugzeit int, Entfernung int, Primary Key (Flugbez, FDatum)); create table Flugzeugtyp( Typ varchar(20), Hersteller varchar(20), SitzAnzahl int, Reisegeschw int, primary key (Typ) ); create table flugzeug( Typ varchar(20), SerienNr int, AnschDatum Date, FlugStd int, primary key(Typ,SerienNr), foreign key(Typ) references Flugzeugtyp(Typ)); create table Abflug( Flugbez varchar(20), FDatum Date, Typ varchar(20), Seriennr int, Kaptaen varchar(20), Primary key(Flugbez,FDatum,Typ,SerienNr), Foreign key(Flugbez) references Flug(Flugbez), -- Foreign key(FDatum) references Flug(FDatum), Foreign key(Typ) references Flugzeugtyp(Typ) -- ,Foreign key(SerienNr) references Flugzeug(SerienNr) ); When I uncomment these, I get: ERROR 1005 (HY000): Can't create table 'db.abflug' (errno: 150)

  Read the article

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