Hi, I'm doing some work on a MySQL database... and I'm completely stumped as to why I'm receiving the error 1054 - Unknown column 'Puzzles.PuzzleID' in 'on clause' Both the table and the column names exist and are correct... I mean, I just created it Navicat's visual designer... SELECT `PuzzleCategories`.`PuzzleCategory`, `Clients`....
I have a column, which is datetime type, the value is like this: 2009-10-20 10:00:00 I want to do a select, my query: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Should I do this: SELECT * FROM data WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59' ORDER BY datetime DESC But i...
I am running the following MySQL query: select * from combinations where family_type='f597'; on a table that has about 90,000 lines. If I run the query via MyPHPAdmin, it takes 0.3 seconds to run, but in my php page on the same host it consistently takes about 8 seconds. To test the amount of time it takes in my page, I run it like ...
Hello all, I run queries using SQLCMD utility via PHP and I wanted to know if there was a way to stop queries or scripts that I have executed via another SQL command by passing the process ID or something. I also make use of the PHP Driver for SQL Server would it be easier to do this way? Or is none of this possible - once a query run...
Hi, I have a query with several aggregate functions and then a few grouped columns. I want to take one of the grouped columns out of the group and perform some sort of aggregate "concatenating" of all the VARCHAR values it has. (Ideally in a new carriage separated list). Here is my query and I note where I'd like to do this: SELECT r...
We have a sql query as follows select * from Table where date < '20091010' However when we look at the query plan, we see The type of query is SELECT. FROM TABLE Worktable1. Nested iteration. Table Scan. Forward scan. Positioning at start of table. Using I/O Size 32 Kbytes for data pages. With MR...
I have posts, votes, and comments tables. Each post can have N 'yes votes', N 'no votes' and N comments. I am trying to get a set of posts sorted by number of yes votes. I have a query that does exactly this, but is running far too slowly. On a data set of 1500 posts and 15K votes, it's take .48 seconds on my dev machine. How can I opti...
I have a index on a column and it is correctly used when the query is select * from Table where x = 'somestring' However it seems to be not used when the query is something like select * from Table where x != 'someotherstring' Is this normal or am I missing something else in the query? The actual query is of course much larger and...
I'm now running two queries,which is not so efficient I think: select count(*) from table where id>0; select * from table where id>0 limit 10; ...
hi i am new in NHibernate and i am a little confused. Suppose we have a product table. Let the product table have 2 columns price1 and price2. then i may query mapped product entities via HQL as follows: string queryString = @"from product p where p.price1 = p.price2 + 100 "; IList result = session.CreateQuery(queryString).List(); ...
hello all! I'm trying to validate a pair of data columns on mysql from my php page across md5 function. I've encrypted the string "helloworld" with php md5 function and attempted to compare it with MYSQL MD5 function but it won't work. I do this because in the database there is a pair of strings "hello" and "world" needs to be compared w...
When adding the line: LEFT JOIN core_records_sales as sales ON DATE(appointments.date) = DATE(sales.date_sold) To my query, it boosts the time for the script to run from about 8 seconds, to 2-3 minutes. Would there be some data causing this problem or am I not implementing the function correctly? I need to use DATE() because I need ...
i need to write a program in java that will connect to a mysql database and execute some sql queries and display the result. suggest me a link with similar implementation / discussion of such concepts for guidance. ...
I have a table that holds topic types another that holds materials. I then have another table which holds the keys of each table thus creating the many to many relation. However, when I try to search the topics to pick out two topics which have share the same material it doesn't work. Example tables: Material Table: MatID | Name ---...
Hey Everyone I'm currently working on a small feature for a project and wondered how best to achieve the result, i have a table full of reviews with the score being out of 5 and being stored as score in the database, on one page i want to show how many of each score reviews there are, ie number of 5 star reviews, 4 star etc But i don't...
I have query like this: SELECT `table_1`.* from `table_1` INNER JOIN `table_2` [...] INNER JOIN `table_3` [...] WHERE `table_1`.`id` IN( SELECT `id` FROM [...] ) AND [more conditions] When I use EXPLAIN, there is 'DEPENDENT SUBQUERY' at the end, but I want this subquery to be performed first, before other conditions. Is is poss...
I have a fairly complex query that will be referencing a single date as a start or stop date multiple times throughout. I'm going to have to run this query for 3 different fiscal years, and don't want to have to hunt down the date 17 times in order to change it throughout my query. Is there a way to set a variable at the beginning of my...
I have the below code that i am wanting to into certain files so that when someone visits this "certain" file they get banned if they are not allready. but for some reason it is not adding new visitors into the database, if i add a user manually it works fine and echo's Banned! but otherwise it just echo's the $sql query but does not ac...
I am using php and mysql. i have a question, lets say, I have a userid column in users table under database A, and userid column in purchases table under database B. Can I execute a single query, using innerjoin, to get value from 2 databases? Or I must use multiple queries? Oh ya, if let say, i have this variable: $conn // connect t...
Hi All, I am running following query in a stored proceudre and it is taking 30 milliseconds to execute. Can anyone help me out to optimize this query: Table Definition is: Create Table Customer ( CustID int not null auto_increment, CustProdID int, TimeStamp DateTime, primary key(CustID) ); Update Customer Set TimeStamp =...