Search Results

Search found 63875 results on 2555 pages for 'mysql error 1045'.

Page 202/2555 | < Previous Page | 198 199 200 201 202 203 204 205 206 207 208 209  | Next Page >

• MYSQL distinct query

  - by Patrick

  This is my example of my table: id | name | foreign_id | 1 a 100 2 b 100 3 c 100 4 a 101 5 b 101 6 c 101 I would like to get the distinct file with the latest foreign_id (bigger number). In this example, it would be row with id 4,5,6. Anyone has any idea? Thank you very much!

  Read the article

• MySQL Query: Winning Auction Bid

  - by mabwi

  I have a small Bidding system that I'm using for a fantasy auction draft. I'm trying to use the below query to pull up the max bids on each player. However, it's not actually giving me the max bid, it's just giving me the first one entered in to the database. SELECT Bid.id FROM bids AS Bid WHERE Bid.active =1 GROUP BY player_id HAVING MAX( Bid.amount ) Here's the Bid table layout, in case it helps: CREATE TABLE IF NOT EXISTS `bids` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `player_id` int(10) NOT NULL, `amount` int(6) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `winning_bid` int(1) NOT NULL DEFAULT '0', `active` int(1) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

  Read the article

• Mysql syntax using IN help!

  - by Axel

  Hi, i have a pictures table : pictures(articleid,pictureurl) And an articles table : articles(id,title,category) So, briefly, every article has a picture, and i link pictures with article using articleid column. now i want to select 5 pictures of articles in politic category. i think that can be done using IN but i can't figure out how to do it. Note: Please only one query, because i can do it by selecting articles firstly then getting the pictures. Thanks

  Read the article

• MYSQL and the LIMIT clause

  - by Lizard

  I was wondering if adding a LIMIT 1 to a query would speed up the processing? For example... I have a query that will most of the time return 1 result, but will occasionaly return 10's, 100's or even 1000's of records. But I will only ever want the first record. Would the limit 1 speed things up or make no difference? I know I could use GROUP BY to return 1 result but that would just add more computation. Any thoughts gladly accepted! Thanks

  Read the article

• Mysql regexp performance question

  - by Tim

  Rumour has it that this; SELECT * FROM lineage_string where lineage like '%179%' and lineage regexp '(^|/)179(/|$)' Would be faster than this; SELECT * FROM lineage_string where lineage regexp '(^|/)179(/|$)' Can anyone confirm ? Or know a decent way to test the speed of such queries. Thanks

  Read the article

• MySQL get row closest to NOW()

  - by Christopher McCann

  I have a table with User data such as name, address etc and another table which has a paragraph of text about the user. The reason that they are separate is because we need to record all the old about data. So if the user changes their paragraph - the old one should still be stored. Each bit of about data has a primary key aboutMeID. What I want to do is have a join that pulls their name, address etc and the latest bit of aboutMe data from the other table. I am not sure though how I can order the join to only get the latest about me data. Can someone help?

  Read the article

• Efficient algorithm for Next button on a MySQL result set

  - by David Grayson

  I have a website that lets people view rows in a table (each row is a picture). There are more than 100,000 rows. You can view different subsets of the rows, and you can view them with different sort orders. While you are viewing one of the rows, you can click the "Next" or "Previous" buttons to go the next/previous row in the list. How would you implement the "Next" and "Previous" features of the website? More specifically, if you have an arbitrary query that returns a list of up to 100,000+ rows, and you know some information about the current row someone is viewing, how do you determine the NEXT row efficiently? Here is the pseudo-code of the solution I came up with when the website was young, and it worked well when there were only 1000 rows, but now that there are 100,000 rows I think it is eating up too much memory. int nextRowId(string query, int currentRowId) { array allRowIds = mysql_query(query); // Takes up a lot of memory! int currentIndex = (index of currentRowId in allRowIds); // Takes time! return allRowIds[currentIndex+1]; } While you are thinking about this problem, remember that the website can store more information about the current row than just its ID (for example, the position of the current row in the result set), and this information can be used as a hint to help determine the ID of the next row. Edit: Sorry for not mentioning this earlier, but this isn't just a static website: rows can often be added to the list, and rows can be re-ordered in the list. (Much rarer, rows can be removed from the list.) I think that I should worry about that kind of thing, but maybe you can convince me otherwise.

  Read the article

• PHP + MySQL - Match first letter of directory

  - by user1822825

  Let's say I have a class table. In the class table, there are many students with their pictures. In the first registration, I've registered the class and students with pictures. The pictures were put into a directory like classid_classname. Then, I change the class name. Now, I'm adding the student's picture. Now, the new picture can't be recognized because the class name has changed. The pic url will be set as classid_class(new)name. How can I match the first letter of the directory? This is my update code : $classID= $_POST["classID"]; $className= $_POST["className"]; $p1 = $_FILES['p1']['name']; $p2 = $_FILES['p2']['name']; $p3 = $_FILES['p3']['name']; $direct = $_POST["className"]; $direct = strtolower($direct); $direct = str_replace(' ', '_', $direct); $tfish = $classID."_".$direct; //the directory variable will have new name because it can't be fetched if the directory has been changed many times// $file = "slider_imagesClass/".$tfish."/"; $url = "/".$tfish."/"; How can I make the variable to match the first letter of the directory because the classID will not change? Thank you. Really appreciate your help :D

  Read the article

• List hits per hour from a MySQL table

  - by Axel

  I am trying to work out the hits per hour from a database. Data basically is stored as follows (with other columns) : Table Name: Hits ============================ VisitorIP TIMESTAMP ---------------------------- 15.215.65.65 123456789 I want to display total hits per hour (within the last 6 hours ) including the hours that has no hits. Example of the output: // Assuming now : 21:00 21:00 - 0 hits 20:00 - 1 hits 19:00 - 4 hits 18:00 - 0 hits 17:00 - 2 hits 16:00 - 3 hits i would love to get the data as array, Please note that the stored date is in UNIX time stamp format. and there may be some hours without any hits! Thanks

  Read the article

• Problem with auto increment primary key (MySQL).

  - by mathon12

  I have 2 tables each using other's primary key as a foreign key. The primary keys for both are set to auto_increment. The problem is, when I try to create and entry into one of the tables, I have no idea what the primary key of the entry is and can't figure out what to put in the other table as a foreign key. What should I do? Do I drop auto_increment altogether and cook up a unique identifier for each entry so I can use it to address the created entries? I'm using PHP, if that's relevant. Thanks.

  Read the article

• Syntax for combining joins in mysql

  - by UltraVi01

  I seem to remember reading somewhere that there is a way to combine LEFT JOIN statements into a more simple query. I have the following query and was hoping someone could kindly take a look at it. SET @userId = 8; SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches FROM user_goal ug, user u, profile p LEFT JOIN user_block ub ON @userId = ub.blocked_id LEFT JOIN user_block ub2 ON @userId = ub2.blocker_id LEFT JOIN user_user uu ON @userId = uu.user_id LEFT JOIN friend_request fr ON @userId = fr.user_id WHERE ug.user_id = u.id AND u.profile_id = p.id AND (ub.blocker_id IS NULL OR ub.blocker_id != ug.user_id) AND (ub2.blocked_id IS NULL OR ub2.blocked_id != ug.user_id) AND (uu.user_friends_id IS NULL OR uu.user_friends_id != ug.user_id) AND (fr.to_user_id IS NULL OR (fr.to_user_id != ug.user_id)) AND ug.user_id!=@userId AND p.suggestible AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId) GROUP BY user_id ORDER BY matches DESC LIMIT 4

  Read the article

• How to search a MySQL database for a specific string

  - by Nadia

  I am trying to set up a search feature on my site that will only return exact matches to keyword entered by the user. So if the user searches "dog" I don't want an article titled "Doggy Style" to appear in the search results (just an example I don't really have an article by that name). This of course does exactly that: SELECT * FROM articles WHERE article_title LIKE '%$searchQuery%' $searchQuery here is a PHP variable taken from the user's input form. So is there any way to return only exact matches?

  Read the article

• Next and Previous MySQL row based on name

  - by NightMICU

  Hi everyone, I have a table with details on personnel. I would like to create a Next/Previous link based on the individual's last name. Since personnel were not added in alphabetical order, selecting the next or previous row based on its ID does not work. It is a hefty table - the pertinent fields are id, name_l, and name_f. I would like to order by name_l, the individuals' last name. How would I go about accomplishing this task? Thanks!

  Read the article

• The conceptual process of populating related tables in a database (MySql) from a CSV file

  - by user322772

  I'm new to relational databases and all the material I've read covered primary and foreign keys, normal forms, and joins but left out to populate the database once it's created. How do you import a CSV file so the fields match their related table? Say you were tying to build a beer database and had a CSV file with each line as a record. Header: brewer, beer_name, country, city, state, beer_category, beer_type, alcohol_content Record 1: Anheuser-Busch, Budweiser, United States, St. Louis, Mo, Pale lager, Regular, 5.0% Record 2: Anheuser-Busch, Bud Light, United States, St. Louis, Mo, Pale lager Light, 4.2% Record 3: Miller Brewing Company, Miller Lite, United States, Milwaukee, WI, Pale lager, Light, 4.2% You can create a "Brewer" table and a "Beer" table. When importing how does you connect the primary keys between the tables?

  Read the article

• mysql subquery strangely slow

  - by aviv

  I have a query to select from another sub-query select. While the two queries look almost the same the second query (in this sample) runs much slower: SELECT user.id ,user.first_name -- user.* FROM user WHERE user.id IN (SELECT ref_id FROM education WHERE ref_type='user' AND education.institute_id='58' AND education.institute_type='1' ); This query takes 1.2s Explain on this query results: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY user index first_name 152 141192 Using where; Using index 2 DEPENDENT SUBQUERY education index_subquery ref_type,ref_id,institute_id,institute_type,ref_type_2 ref_id 4 func 1 Using where The second query: SELECT -- user.id -- user.first_name user.* FROM user WHERE user.id IN (SELECT ref_id FROM education WHERE ref_type='user' AND education.institute_id='58' AND education.institute_type='1' ); Takes 45sec to run, with explain: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY user ALL 141192 Using where 2 DEPENDENT SUBQUERY education index_subquery ref_type,ref_id,institute_id,institute_type,ref_type_2 ref_id 4 func 1 Using where Why is it slower if i query only by index fields? Why both queries scans the full length of the user table? Any ideas how to improve? Thanks.

  Read the article

• MySQL Select Statement - Two Tables, Sort One Table by Count of Other Table

  - by Robert Boka

  So I have built a voting system for a custom post system i wrote. I want to be able to sort by "most voted", "Most liked", etc. I have two tables. Entry: ID, Title, Post Vote: ID, EntryID, Result I want to be able to query the vote table for each entry and see how many vote's there are, and then sort the entry's by how many vote's each table had. I have messed around with joins, etc. and cannot seem to figure it out. Any suggestions?

  Read the article

• php code to mysql database export

  - by kumar

  i want php code to import *.sql file to database and export db to *.sql file without going to phpmyadmin

  Read the article

• Converting MySQL Resultset from Rows to Columns

  - by gms8994

  I have output from a select like this: 04:47:37> select * from attributes left outer join trailer_attributes on attributes.id = trailer_attributes.attribute_id; +----+--------------+----------+-----------+------------+--------------+-----------------+ | id | name | datatype | list_page | trailer_id | attribute_id | attribute_value | +----+--------------+----------+-----------+------------+--------------+-----------------+ | 1 | Make | text | 1 | 1 | 1 | Apple | | 1 | Make | text | 1 | 2 | 1 | sdfg | | 2 | Year | number | 1 | 1 | 2 | 2009 | | 2 | Year | number | 1 | 2 | 2 | sdfg | | 3 | Type | text | 0 | 1 | 3 | iPhone | | 3 | Type | text | 0 | 2 | 3 | sdfg | | 4 | Axles | text | 0 | 1 | 4 | asdf | | 4 | Axles | text | 0 | 2 | 4 | sdfg | | 7 | Size | text | 0 | 1 | 7 | asd1 | | 7 | Size | text | 0 | 2 | 7 | sdfg | | 8 | Frame | text | 0 | 1 | 8 | | | 8 | Frame | text | 0 | 2 | 8 | sdfg | | 9 | Height | text | 0 | 1 | 9 | | | 9 | Height | text | 0 | 2 | 9 | sdfg | | 10 | Dollies | text | 0 | 1 | 10 | | | 10 | Dollies | text | 0 | 2 | 10 | sdfg | | 11 | Tires/Wheels | text | 0 | 1 | 11 | | | 11 | Tires/Wheels | text | 0 | 2 | 11 | sdfg | | 12 | Condition | text | 1 | 1 | 12 | New | | 12 | Condition | text | 1 | 2 | 12 | sdfg | | 13 | Title | text | 0 | 1 | 13 | | | 13 | Title | text | 0 | 2 | 13 | sdfg | +----+--------------+----------+-----------+------------+--------------+-----------------+ I want to convert it to something more along the lines of: id, Make, Year, Type, Axles, Size, Frame (etc) 1, Apple, 2009, iPhone, ..... 2, sdfg, sdfg, sdfg, ..... Any suggestions?

  Read the article

• MySQL VARCHAR strange column behavior

  - by Mat

  I have the following SQL statement which returns a single record as expected: select * from geodatasource_cities C, geodatasource_countries D where C.CC_FIPS = D.CC_FIPS and D.CC_ISO='AU' and UCASE(TRIM(C.FULL_NAME_ND)) LIKE '%JAN JUE%'; However, If I use the following SQL statement, no records are returned. I have only changed the LIKE clause to an equal to clause: select * from geodatasource_cities C, geodatasource_countries D where C.CC_FIPS = D.CC_FIPS and D.CC_ISO='AU' and UCASE(TRIM(C.FULL_NAME_ND)) = 'JAN JUE'; Can anybody please help me understand why this may be happening?

  Read the article

• Get column of a mysql entry

  - by Xelluloid

  Is there a possibility to get the name of the column a database entry belongs to? Perhaps I have three columns with column names col1, col2 and col3. Now I want to select for every column the column with the maximum entry, something like this. Select name_of_column(max(col1,col2,col3)). I know that I can ask for the name of the columns by its ordinal position in the information_schema.COLUMNS table but how do I get the ordinal position of a database entry within a table?

  Read the article

• MySQL Query exceptions

  - by Wayne

  In one page, it should show records that has the following selected month from the drop down menu and it is set in the ?month=March So the query will do this $sql = "SELECT * FROM schedule WHERE month = '" . Clean($_GET['month']) . "' AND finished='0' ORDER BY date ASC"; But it shows records that has a value of 2 in the finished column and I don't want the query to include this. I've tried $sql = "SELECT * FROM schedule WHERE month = '" . Clean($_GET['month']) . "' AND finished='0' OR finished = '1' OR finished = '3' ORDER BY date ASC"; But it shows records on different months when it shouldn't be. So basically I want the record to exclude the records that has the value of 2 in the record that will not be shown in the page.

  Read the article

• Mysql Performance Question - Essentially about normalizing efficiency

  - by freqmode

  Hi there. Just a quick question about database performance. I'll outline my site purpose below as background. I'm creating a dictionary site that saves the words users define to a database. What I'm wondering is whether or not to create a words table for each user or to keep one massive words table. This site will be used for entire schools so the single words table would be massive! The database structure is as follows: A user table with: User_ID PRIMARY KEY Username First Last Password Email Country Research Standings SendInfo Donated JoinedOn LastLogin Logins Correct Attempts Admin Active And one word table with: User_ID PRIMARY KEY Word Vocab Spell Defined DefinedAttempted Spelled SpelledAttempted Sentenced SentencedAttempted So what I'm asking is , performance-wise, should I create a new table for each user when they join the site - each user could have hundreds or thousands of words over time? Or is it better to have one massive table with thousands and thousands of records and filter by User_ID. I don't think I'll perform many table joins. My gut feeling is to create a new table for each user, but I thought I'd ask for expert advice! Thanks in advance.

  Read the article

• MySQL Need some help with a query

  - by Jules

  I'm trying to fix some data by adding a new field. I have a backup from a few months ago and I have restored this database to my server. I'm looking at table called pads, its primary key is PadID and the field of importance is called RemoveMeDate. In my restored (older) database there is less records with an actual date set in RemoveMeDate. My control date is 2001-01-01 00:00:00 meaning that the record is not hidden aka visible. What I need to do is select all the records from the older database / table with the control date and join with those from the newer db /table where the control date is not set. I hope I've explained that correctly. I'll try again, with numbers. I have 80,000 visible records in the older table (with control date set) and 30,000 in the newer db/table. I need to select the 50,000 from the old database, to perform an update query. Heres my query, which I'd can't get to work as I'd like. jules-fix-reasons is the old database, jules is the newer one. select p.padid from `jules-fix-reasons`.`pads` p JOIN `jules`.`pads` ON p.padid = `jules`.`pads`.`PadID` where p.RemoveMeDate <> '2001-01-01 00:00:00' AND `jules`.`pads`.RemoveMeDate = '2001-01-01 00:00:00'

  Read the article

• Mysql many to many problem (leaderborad/scoreboard)

  - by zoko2902

  Hi all! I'm working on a small project in regards of the upcoming World Cup. I'm building a roster/leaderboard/scoredboard based on groups with national teams. The idea is to have information on all upcoming matches within the group or in the knockout phase (scores, time of the match, match stats etc.). Currently I'm stuck with the DB in that I can't come up with a query that would return paired teams in a row. I have these 3 tables: CREATE TABLE IF NOT EXISTS `wc_team` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL , `description` VARCHAR(250) NULL , `flag` VARCHAR(45) NULL , `image` VARCHAR(45) NULL , `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`) , CREATE TABLE IF NOT EXISTS `wc_match` ( `id` INT NOT NULL AUTO_INCREMENT , `score` VARCHAR(6) NULL , `date` DATE NULL , `time` VARCHAR(45) NULL , `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`) , CREATE TABLE IF NOT EXISTS `wc_team_has_match` ( `wc_team_id` INT NOT NULL , `wc_match_id` INT NOT NULL , PRIMARY KEY (`wc_team_id`, `wc_match_id`) , I've simplified the tables so we don't go in the wrong direction. Now I've tried al kinds of joins and groupings I could think of, but I never seem to get. Example guery: SELECT t.wc_team_id,t.wc_match_id,c.id.c.name,d.id,d.name FROM wc_team_has_match AS t LEFT JOIN wc_match AS s ON t.wc_match_id = s.id LEFT JOIN wc_team AS c ON t.wc_team_id = c.id LEFT JOIN wc_team AS d ON t.wc_team_id = d.id Which returns: wc_team_id wc_match_id id name id name 16 5 16 Brazil 16 Brazil 18 5 18 Argentina 18 Argentina But what I really want is: wc_team_id wc_match_id id name id name 16 5 16 Brazil 18 Argentina Keep in mind that a group has more matches I want to see all those matches not only one. Any pointer or suggestion would be extremly appreciated since I'm stuck like a duck on this one :).

  Read the article

• mysql : Recieve data only per months

  - by Tristan

  Hello, few times ago, i asked how to do to display datas per month, i must told a bad explanation because i just figured out that it's not what i want : Here's what I got : $req1 = ... AND v.date > (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) AND v.date < (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) $req2= ... AND v.date > (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND v.date < (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) But the problem, imagine that today you are the 10th june, it's going to calculate ALL the data between the 10 june to the 10 may then the 10 may until the 10 april... But what i want is data : from 1st may to 1 st june, from 1st june to 1st july... do you see what i mean ? thank you ;)

  Read the article

< Previous Page | 198 199 200 201 202 203 204 205 206 207 208 209  | Next Page >