Hi all, I asked a similar question yesterday, but now I have a slight twist. Since I already gave credit to an answer, I've decided to create a new question. Here's what I'm trying to do: Select an arbitrary number of rows, but placing a limit on the number of selected rows of one field. $query = "SELECT test_id, field1, field2 FROM...
What are some Oracle gotchas for someone new to the platform, but not new to relational databases (MySQL, MS SQL Server, Postgres, etc.) in general. Two examples of the kind of things I'm looking for Many relational database products handle creating an auto_increment key for you. Oracle does not, you must manually create the sequence...
function updateDemo($demoTitle, $desc, $keyword, $linkTitle, $clientname, $imageTitle, $adminName, $demoID) { $query = "UPDATE demos SET demoTitle ='$demoTitle' , dmDesc = '$desc' , dmKey = '$keyword' , dmLink= '$linkTitle' , client='$clientname' , imageTitle = '$imageTitle' , userName = '$adminName' W...
Although I can group and order by on an aliased sub query, I can't use the alias in a where clause. Do I need to use a join instead? Works: SELECT entries.*, (SELECT avg(value) FROM `ratings` WHERE ratings.entry_id = entries.id) as avg_rating FROM `entries` ORDER BY avg_rating DESC Fails ("unknown column 'avg_rating' i...
Hello all, I currently have the following tables for a private messaging forum: What I'm trying to do is output an "inbox" that displays the most recent thread at the top and group by thread (meaning, you don't see the same thread twice in your inbox), no matter who the sender is. What I have right now works fine for simple messa...
What's the right way to control timeouts, from the client, when running against a MySQL database, using SQLAlchemy? The connect_timeout URL parameter seems to be insufficient. I'm more interested in what happens when the machine that the database is running on, e.g., disappears from the network unexpectedly. I'm not worried about the ...
Is the standard MySQL JDBC driver thread-safe? Specifically I want to use a single connection across all threads, but each statement will only be used in a single thread. Are there certain scenarios that are safe and others that aren't? What's your experience here? ...
Hi,everyone, Recently, I put my project which is php+smarty+mysql in my httpd server. But I encountered an error that says: 500 Internal Server Error My OS is archlinux, and the httpd server and php were installed like this: sudo pacman -S apache php If I use a test native php file which contains the following: <html> <head...
I have a table in which I have two fields i.e name of the product and release date (type:date)... ->>I want to select all the products which have not been launched yet!!! Thanks... ...
Dear All I am an SQL newbie, I am having an excel sheet in which the first row contains title for all the columns. I want to all the names in the first row. Question What is the SQL command for - reading all the entries in the first row? If possible, I want to define the max-limit. Addition: What I want is, "Enumerate all the column...
Is there a limit on the SQL query string you can pass to MySQL for it to execute? ...
Hi all, I currently have a MySQL SELECT statement that pulls information from two tables to display a "Sent Messages" field for private messaging. I'd like to know how I can do add a COUNT to my query to count the number of "receivers" in one thread. Here's the basic gist of my table structure, (NOTE: the relational tie between the ...
Hi, I have a DB-Application and now we have to start with replication (master-master-replication). We build an stored-function witch returns an BIGINT. This value is unique on all involved servers. The situation: I have a table definition: create table test ( id BIGINT not null primary key auto_increment, col1 TEXT); the table has...
I need to store a very large number (tens of millions) of 512-bit SHA-2 hashes in a MySQL table. To save space, I'd like to store them in binary form, rather than a string a hex digits. I'm using an ORM (DBix::Class) so the specific details of the storage will be abstracted from the code, which can inflate them to any object or structure...
I would like to have a trigger to perform following operation for inserted records: # pseudocode if new.group_id is null set new.group_id = new.id else # don't touch it end More clearly: say I have one table with three columns: id primary key, group_id int, value varchar. When I insert with group_id like that: INSERT INT...
Currently have an 'item' table, and a 'pair' table. The pair table simply contains two columns, which contain the primary key from the item table. A common query is to find a number of items that are featured in the least number of pairs. SELECT id,COUNT(*) AS count FROM item i LEFT JOIN pair p ON (i.id = p.id1 OR i.id = p.id2) GROU...
I am trying to develop an time card application. So for Every month there will be 30 or 31 days. (with the fields AM-IN, AM-OUT, PM-IN, PM-OUT, Etc) which is a VARCHAR(4500) in mysql What is my idea is to store this one month data (30 days) in to one row in the database. I am storing 30 days data in XML format. so while fetching only on...
I'm working on selecting locations (city, state) out of a database. The problem is that the query is running a tad slow and I'm not sure how to speed it up. For example: SELECT CONCAT_WS(', ', city, state) as location, AVG(latitude), AVG(longitude) FROM places WHERE city='New York' AND state='NY' GROUP BY location There's going to ...
I am an undergraduate looking to go to grad school for a masters or PhD. I have worked on several different projects which relied heavily upon a SQL database (MySQL and SQL Server). I have enjoyed this work and feel that I have developed a pretty strong working knowledge of SQL. I see that MySQL offers developer certification and I am...
Hey everyone, I have the following 'users' table in MySQL: CREATE TABLE `users` ( `uid` int(11) NOT NULL auto_increment, `fname` varchar(50) NOT NULL, `lname` varchar(50) NOT NULL, `role` varchar(75) NOT NULL, `region` tinyint(4) unsigned default NULL, `username` varchar(25) NOT NULL, `password` varchar(75) NOT NULL, `n...