mysql-query

DB Error on create - Errno 150

When running the follow query i receive the error below, Im just trying to create this simple table with no FK or anything and still receive this error. What could be the cause? SQL query: CREATE TABLE `xauction`.`orders` ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT , `type` VARCHAR( 1 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = InnoDB; ...

Fetching random rows using a random index and RAND( )

I'm using the following code to return a random row from a table. Using the field 'rand'. SELECT * FROM imgs WHERE rand > RAND( ) ORDER BY rand ASC LIMIT 1 The field 'rand' is generated by mysql at creation using something similar to: INSERT INTO imgs SET rand = RAND () For some reason the results although changing each run are...

help removing mysql file sort

Here is the log output: # Time: 100915 13:06:49 # User@Host: ss[ss] @ localhost [] # Query_time: 13.978355 Lock_time: 0.000029 Rows_sent: 10 Rows_examined: 562760 use ss; SET timestamp=1284574009; SELECT DISTINCT SQL_CALC_FOUND_ROWS B.*, U.username FROM sc_users AS U, sc_bookmarks AS B ...

PHP MYSQL error - "You have an error in your SQL syntax; check ... for the right syntax to use near ...

The exact error message is: 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 'where rfflag='0'' at line 1 Hi, I'm trying to get some php scripts working and it dies with the above error message. There are two locations where rfflag is used in the ...

Why does C# keep giving me -1 from a query when the console gives me the correct number from a count(*)?

MySqlCommand checkUsername = conn.CreateCommand(); checkUsername.CommandText = "SELECT COUNT(*) FROM users WHERE username='admin'"; MessageBox.Show("The count is " + checkUsername.ExecuteNonQuery()); There is more code where this "count" is actually being used, but it was not working correctly so I made this little message box pop up t...

MySql Temp Tables VS Views VS php arrays

Hey Guys I have currently created a facebook like page that pulls notifications from different tables, lets say about 8 tables. Each table has a different structure with different columns, so the first thing that comes to mind is that I'll have a global table, like a table of contents, and refresh it with every new hit. I know inserts a...

Sort SQL records based on matched conditions

I have this query: SELECT * FROM table WHERE key LIKE '1,2,3,%' OR key LIKE '1,2,%' OR key LIKE '1,%' Is it posible to sort records returned from this query based on which conditions was matched first. I'd like to get all records that match key LIKE '1,2,3,%' first, then key LIKE '1,2,%' and the others after. For example, if I have t...

Help with MySQL query synatx

I want to insert a row of data into a table with five columns (this table joins members and games); four of the five columns are known, while the fourth, rank, has to be dynamically calculated: wishlists(id (int, pk), memberid (int, FK), gameid(int, FK), rank (int), createdat(timestamp) ) INSERT INTO wishlists (memberid, gameid, rank) ...

MySQL returning value from a SELECT statement

I keep getting this error: Result consisted of more than one row I have this function: DROP FUNCTION IF EXISTS db.GetUserIDByCourseID; CREATE FUNCTION db.`GetUserIDByCourseID`(CourseID int) RETURNS int(11) BEGIN SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0)) INTO @user_id FROM courses c ...

Mysql Query: Group log results by minute || hour || day, and fill empty gaps with helper table

I'm using a helper table with all possible dates & hours for 2,5 years forward (26k rows) to fill out gaps for a given period set in my query. Now I want to extend the precision of the analytics, by returning a resultset with rows by the MINUTE. I'm a bit unsure how to accomplish this, since the helper table only have all possible da...

Order rows in join table mysql

I have a table with date ranges. I want to look for gaps between these ranges. I've figured out that I may left join table to itself and calculate the difference. Current table: date_begin date_end 2010-08-01 2010-08-15 2010-08-16 2010-08-30 2010-08-31 2010-09-12 2010-10-01 2010-10-15 I want to get: date_begin date...

php parse error

hi guys, this was working for me for the last couple of days until this morning when I must have changed something by accident. I thought I changed back everything but it's still not working. This is the error I'm getting Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in C:\wamp\www\xxxxx\reg.php on...

Delete all rows except recent two entries for each member

I have table where member_id gets inserted for different user. I want to delete the enteries of member_id except recent two entries for each member in the table. Tell me the optimized way because its a very large table. i.e table should has 2 recent entries for each member. I need query in mysql 4.X.X.X Please help me. http://www.w...

How do I get the name of the database to which my table belongs?

My query is "select column_name,table_name from information_schema.columns". This query returns a number of columns and tables. Now, I want to determine the name of the database that corresponds to the returned table names. How do I accomplish this? ...

SQL - Correct query for sorting items by values from other table

Hi there! I've got a little problem with correct query and hope you can help me. My testing tables structures are as follow: CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), ) ; CREATE TABLE IF NOT EXISTS `user_throws` ( `id` int(11) NOT NULL AUTO_I...

join multiple fields to one table

I have 2 tables (there are more but un related to question) optionValue and productStock I want to get the option names from the optionValue table for each option1, option2, option3 (the query below will should help to make more sense) below is my attempt, the current query it only works if all options are set but is returns null if a...

How to turn rows into columns?

I have a database where I store keywords grouped into projects and data related to each keyword, then I display datagrids foreach project with one row per keyword and several columns all retrieved from the same table "data". I have 4 tables, keywords, projects, group_keywords and data. "keywords" only stores the keyword, "projects" the p...

Display similar results from mySQL

How can I create a query that will return similar words if an exact match is not found? For example if the query is trying to match "mstke" and if the word is not found in the database then results should contain similar words like "mistake", "misstep", "take" and "mist". I tried %LIKE%, but it displays the exact match of words. ...

MySQL: Update all rows in a table matching results of another query

I've written a query returning rows associating Customers and Salespeoeple. Note that the query joins several database tables. And note that not all customers have a salesperson. c_id c_name s_id s_name 24 microsoft 1 mike 27 sun 1 mike 42 apple 2 bill 44 oracle 1 mi...

Super Slow MySQL - Need Help!

I have a SUPER slow query, which I posted here: http://pastebin.com/E5sdRi7e. When I did an EXPLAIN, I got the following: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL...