Search Results

Search found 68715 results on 2749 pages for 'mysql data'.

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

  • writting an sql query

    - by Praveen Prasad
    iam having 2 tables table Items Table (this table holds all items iam having) itemId --------- Item1 Item2 Item3 Item4 Item5 table 2 users_item relation UserId || ItemId 1 || Item1 1 || Item2 userId one has stored 2 items Item1,Item2. Now i want to write a query on table1 (Items table) so that it displays all items which user1 has NOT chosen.

    Read the article

  • Need MYSQL query for finding lowest score per game player

    - by Chris Barnhill
    I have a game on Facebook called Rails Across Europe. I have a Best Scores page where I show the players with the best 20 scores, which in game terms refers to the lowest winning turn. The problem is that there are a small number of players who play frequently, and their scores dominate the page. I'd like to make the scores page open to more players. So I thought that I could display the single lowest winning turn for each player instead of displaying all of the lowest winning turns for all players. The problem is that the query for this eludes me. So I hope that one of you brilliant StackOverflow folks can help me with this. I have included the relevant MYSQL table schemas below. Here are the the table relationships: player_stats contains statistics for either a game in progress or a completed game. If a game is in progress, winning_turn is zero (which means that games with a winning_turn of zero should not be included in the query). player_stats has a game_player table id reference. game_player contains data describing games currently in progress. game_player has a player table id reference. player contains data describing a person who plays the game. Here's the query I'm currently using: 'SELECT p.fb_user_id, ps.winning_turn, gp.difficulty_level, c.name as city_name, g.name as goods_name, d.cost FROM game_player as gp, player as p, player_stats as ps, demand as d, city as c, goods as g WHERE p.status = "ACTIVE" AND gp.player_id = p.id AND ps.game_player_id = gp.id AND d.id = ps.highest_demand_id AND c.id = d.city_id AND g.id = d.goods_id AND ps.winning_turn > 0 ORDER BY ps.winning_turn ASC, d.cost DESC LIMIT '.$limit.';'; Here are the relevant table schemas: -- -- Table structure for table `player_stats` -- CREATE TABLE IF NOT EXISTS `player_stats` ( `id` int(11) NOT NULL auto_increment, `game_player_id` int(11) NOT NULL, `winning_turn` int(11) NOT NULL, `highest_demand_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `game_player_id` (`game_player_id`,`highest_demand_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3814 ; -- -- Table structure for table `game_player` -- CREATE TABLE IF NOT EXISTS `game_player` ( `id` int(10) unsigned NOT NULL auto_increment, `game_id` int(10) unsigned NOT NULL, `player_id` int(10) unsigned NOT NULL, `player_number` int(11) NOT NULL, `funds` int(10) unsigned NOT NULL, `turn` int(10) unsigned NOT NULL, `difficulty_level` enum('STANDARD','ADVANCED','MASTER','ULTIMATE') NOT NULL, `date_last_used` datetime NOT NULL, PRIMARY KEY (`id`), KEY `game_id` (`game_id`,`player_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3814 ; -- -- Table structure for table `player` -- CREATE TABLE IF NOT EXISTS `player` ( `id` int(11) NOT NULL auto_increment, `fb_user_id` char(255) NOT NULL, `fb_proxied_email` text NOT NULL, `first_name` char(255) NOT NULL, `last_name` char(255) NOT NULL, `birthdate` date NOT NULL, `date_registered` datetime NOT NULL, `date_last_logged_in` datetime NOT NULL, `status` enum('ACTIVE','SUSPENDED','CLOSED') NOT NULL, PRIMARY KEY (`id`), KEY `fb_user_id` (`fb_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1646 ;

    Read the article

  • Get latest record from second table left joined to first table

    - by codef0rmer
    I have a candidate table say candidates having only id field and i left joined profiles table to it. Table profiles has 2 fields namely, candidate_id & name. e.g. Table candidates: id 1 2 and Table `profiles`: candidate_id name 1 Foobar 1 Foobar2 2 Foobar3 i want the latest name of a candidate in a single query which is given below: SELECT C.id, P.name FROM candidates C LEFT JOIN profiles P ON P.candidate_id = C.id GROUP BY C.id ORDER BY P.name; But this query returns: 1 Foobar 2 Foobar3 Instead of: 1 Foobar2 2 Foobar3

    Read the article

  • What do you call the concept of dynamic data definition?

    - by DJTripleThreat
    Maybe this is simpler and more straightforward then what I'm thinking but I can't seem to find this concept on google anywhere. The concept is this: You have a table in a database and the table has a specified number of columns. However, it has been asked of me by previous clients that there also be a set of dynamic user defined columns that can be added on the fly. What is this concept called and is it considered a design pattern?

    Read the article

  • Calculating percent of votes inside mysql statement.

    - by Beck
    UPDATE polls_options SET `votes`=`votes`+1, `percent`=ROUND((`votes`+1) / (SELECT voters FROM polls WHERE poll_id=? LIMIT 1) * 100,1) WHERE option_id=? AND poll_id=? Don't have table data yet, to test it properly. :) And by the way, in what type % integers should be stored in database? Thanks for the help!

    Read the article

  • Copying a database into a new database including structure and data

    - by Jason
    In phpMyAdmin under operations I can "Copy database to:" and select Structure and data CREATE DATABASE before copying Add AUTO_INCREMENT value I need to be able to do that without using phpMyAdmin. I know how to create the database and user. I have a source database that's a shell that I can work from so all I really need is the how to copy all the table structure and data part. (I know, the harder part) system() & exec() are not options for me which rules out mysqldump. (I think) How can I loop through each table and recreate it's structure and data? Is it just looping through the results of SHOW TABLES then for each table looping through DESCRIBE tablename Then, is there an easy way for getting the data copied?

    Read the article

  • How to concatinate text on existing database entry?

    - by Starx
    I am a table, whose structure is somewhat like this id, name, link the link holds the name of the page like "link" = "index.php". Now I want to update this field and add "page=" in front of "index.php". Using this method I would like to update every entry in my table. My desired SQL syntax need to be something like this UPDATE mytable set link= 'page=' + <existing value of link> WHERE 1; I am using Where 1; to denote every other rows Anyone know what to accomplish this?

    Read the article

  • Please verify the trigger created below for delete is correct or not?

    - by Parth
    Please verify the trigger created below for delete is correct or not? Its for the insertion of every field of deleted row in audit table.. Please reply whether this trigger will work for me? delimiter // CREATE TRIGGER audit_menu BEFORE DELETE ON menu FOR EACH ROW BEGIN INSERT INTO audit (menuid, field, oldvalue, changedone) VALUES (OLD.menuid, 'name', OLD.name, UNIX_TIMESTAMP() ), (OLD.menuid, 'age', OLD.age, UNIX_TIMESTAMP() ), (OLD.menuid, 'address', OLD.address, UNIX_TIMESTAMP() ), (OLD.menuid, 'sex', OLD.sex, UNIX_TIMESTAMP() ), (OLD.menuid, 'town', OLD.town, UNIX_TIMESTAMP() ) END;// delimiter ;

    Read the article

  • Mysql query to fetch data

    - by hnihar
    i have a table "request" with 4 columns namely: 1.recId :long primary key 2.interactionId:long 3.requestedBy:boolean 4.requestedType:boolean and data is as follows: VALUES (185,455699,0,5), (186,455746,0,1), (187,455746,1,1), (188,455752,0,1), (189,455753,0,1), (190,455753,1,1), (191,455754,1,1) i want a query to fetch all the rows where interactionId is same and having requestedBy both 1 and 0 values and requestType=1; regards, Nihar

    Read the article

  • What data actually gets cached in InnoDB/MySQL?

    - by ming yeow
    Hi folks, i am trying to optimize performance for my database. My question is - what get cached in the db memory? For example: (table with 2 columns: key (indexed), data (not indexed) updated (not indexed) Select * where updated=20100202 (the db will do a scan - will the scanned rows be kept in memory?) Select * where key = 20 (the db will refer to the index - will the identified rows be kept in memory?)

    Read the article

  • How to concatenate text on existing database entry?

    - by Starx
    I have a table, whose fields are id, name, link the link holds the name of the page like "link" = "index.php". Now I want to update this field and add "page=" in front of "index.php". Using this method I would like to update every entry in my table. My desired SQL syntax need to be something like this UPDATE mytable set link= 'page=' + <existing value of link> WHERE 1; I am using 'WHERE 1;' to denote every row. Anyone know how to accomplish this?

    Read the article

  • NHibernate and MySql is inserting and Selecting, not updating

    - by Chris Brandsma
    Something strange is going on with NHibernate for me. I can select, and I can insert. But I can't do and update against MySql. Here is my domain class public class UserAccount { public virtual int Id { get; set; } public virtual string UserName { get; set; } public virtual string Password { get; set; } public virtual bool Enabled { get; set; } public virtual string FirstName { get; set; } public virtual string LastName { get; set; } public virtual string Phone { get; set; } public virtual DateTime? DeletedDate { get; set; } public virtual UserAccount DeletedBy { get; set; } } Fluent Mapping public class UserAccountMap : ClassMap<UserAccount> { public UserAccountMap() { Table("UserAccount"); Id(x => x.Id); Map(x => x.UserName); Map(x => x.Password); Map(x => x.FirstName); Map(x => x.LastName); Map(x => x.Phone); Map(x => x.DeletedDate); Map(x => x.Enabled); } } Here is how I'm creating my Session Factory var dbconfig = MySQLConfiguration .Standard .ShowSql() .ConnectionString(a => a.FromAppSetting("MySqlConnStr")); FluentConfiguration config = Fluently.Configure() .Database(dbconfig) .Mappings(m => { var mapping = m.FluentMappings.AddFromAssemblyOf<TransactionDetail>(); mapping.ExportTo(mappingdir); }); and this is my NHibernate code: using (var trans = Session.BeginTransaction()) { var user = GetById(userId); user.Enabled = false; user.DeletedDate = DateTime.Now; user.UserName = "deleted_" + user.UserName; user.Password = "--removed--"; Session.Update(user); trans.Commit(); } No exceptions are being thrown. No queries are being logged. Nothing.

    Read the article

  • mysql table creation problem..

    - by pradeep
    Hi, I have a small problem. I am creating an appointment table where in the foreign key is patient id which is referenced from patient table.This table is for all registered patients. there will be unregistered patients also, who will be seeking appointments.SO i just need to store the name,phone and few details. I don't want to make these 2 as different tables. So is there a way to skip the integrity check of foreign key when i ma inserting unregistered patient data

    Read the article

  • Recomposing data structure in Excel

    - by Velletti
    I've got a sheet of 35k rows of the following kind of data that I want to reshape into table below. So, I want to reshape this data in a way to get all the people within a specific GroupID in separate columns. I suppose that I should add a counter for each row within specific group id? Also, I suppose these kind of issues are a lot more comfortable to be done in databases? Since I often have this kind of data, I need be much quicker about solving it, then I am now.

    Read the article

  • MySQL select date, join statistic data - how?

    - by Webnet
    I have some statistics I need to report on a monthly basis. I need my query to return 0's for statistics which aren't there. The only way I know to do this is to use SQL to generate a list of days within the specified time period and LEFT JOIN the statistic data that I need to report on. Problem is.... I've never not selected from a table like that... how do I do that?

    Read the article

  • How to return null value if the query has no corresponding value?

    - by Holicreature
    Hi i've a query select c.name as companyname, u.name,u.email,u.role,a.date from useraccount u, company c, audittrial a where u.status='active' and u.companyid=c.id and (u.companyid=a.companyID and a.activity like 'User activated%' and a.email=u.email) order by u.companyid desc limit 10 So if the following part doesnt't satisfy, (u.companyid=a.companyID and a.activity like 'User activated%' and a.email=u.email) no rows will be returned.. but i want to return the result of the following query select c.name as companyname, u.name,u.email,u.role,a.date from useraccount u, company c, audittrial a where u.status='active' and u.companyid=c.id order by u.companyid desc limit 10 but to add that, i should return the date if available and return null value if date is not available.. how can i do this?

    Read the article

  • Create table with PHP and populate from MySQL

    - by typoknig
    Hi all, I am creating a table to display on a web page and that table is populated from data in a MySQL database. I am trying to do a couple of things that are making it difficult for me. First I am trying to have call the PHP code that exists in a separate file in HTML via JavaScript. I think I have that working right but I am not 100% sure (because the table will not display). I think it is working right because some of the code for the table (which is in the PHP file) displays in FireBug. Second I am trying to make it so the rows alternate colors for easy viewing too. My PHP code so far is below. The table does not display at all in any browser. $query = "SELECT * FROM employees"; $result = mysql_query($query); $num = mysql_num_rows($result); echo '<table>'; for ($i = 0; $i < $num; $i++){ $row = mysql_fetch_array($result); $id = $row['id']; $l_name = $row['l_name']; $f_name = $row['f_name']; $ssn = $row['ssn']; $class = (($i % 2) == 0) ? "table_odd_row" : "table_even_row"; echo "<tr>"; echo "<td class=" . $class . ">$wrap_id</td>"; echo "<td class=" . $class . ">$wrap_l_name</td>"; echo "<td class=" . $class . ">$wrap_f_name</td>"; echo "<td class=" . $class . ">$wrap_ssn</td>"; echo "</tr>"; } echo '</table>'; mysql_close($link); }

    Read the article

  • MySQL INSERT IGNORE not working

    - by gAMBOOKa
    Here's my table with some sample data a_id | b_id ------------ 1 225 2 494 3 589 When I run this query INSERT IGNORE INTO table_name (a_id, b_id) VALUES ('4', '230') ('2', '494') It inserts both those rows when it's supposed to ignore the second value pair (2, 494) No indexes defined, neither of those columns are primary. What don't I know?

    Read the article

  • How can this SQL be wrong? What am I not seeing?

    - by ropstah
    Can anybody please spot my error, this should be a legal query in SQL shouldn't it?? Unknown column u.usr_auto_key in the ON clause This is the database schema: User: (usr_auto_key, name, etc...) Setting: (set_auto_key, name etc..) User_Setting: (usr_auto_key, set_auto_key, value) And this is the query... SELECT `u`.`usr_auto_key` AS `u__usr_auto_key`, `s`.`set_auto_key` AS `s__set_auto_key`, `u2`.`usr_auto_key` AS `u2__usr_auto_key`, `u2`.`set_auto_key` AS `u2__set_auto_key`, `u2`.`value` AS `u2__value` FROM `User` `u`, `Setting` `s` LEFT JOIN `User_Setting` `u2` ON `u`.`usr_auto_key` = `u2`.`usr_auto_key` WHERE (`s`.`sct_auto_key` = 1 AND `u`.`usr_auto_key` = 1 AND admin_property is null)

    Read the article

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