Given a SCHEMA for implementing tags ITEM ItemId, ItemContent TAG TagId, TagName ITEM_TAG ItemId, TagId What is the best way to limit the number of ITEMS to return when selecting with tags? SELECT i.ItemContent, t.TagName FROM item i INNER JOIN ItemTag it ON i.id = it.ItemId INNER JOIN tag t ON t.id = it.TagId is of course the e...
Is there a better way to watch for new entries in a table besides selecting from it every n ticks of time or something like that? I have a table that an external program updates very often, and clients can watch for this new data as it arrive, how can I make that without having to set a fixed period of repeatable select statements? ...
I'm trying to create a table with two columns comprising the primary key in MySQL, but I can't figure out the syntax. I understand single-column PKs, but the syntax isn't the same to create a primary key with two columns. ...
Null or empty string -- is one better than the other to represent no data in a table column? (I specifically use MySQL, but I'm thinking this is system-independent.) Are there major advantages/disadvantages to using one over the other, or is it simply programmer preference? Thanks! ...
Usually pagination queries look like this. Is there a better way instead of making two almost equal methods, one of which executing "select *..." and the other one "count *..."? public List<Cat> findCats(String name, int offset, int limit) { Query q = session.createQuery("from Cat where name=:name"); q.setString("name", name);...
Can anyone recommend a framework or basic technology to use to host a web service on a basic hosted web server? I have data in a mySQL database that will be accessed by the web service. ...
How do you set a default value for a MySQL Datetime column? In SQL Server it's getdate(), what is the equivalant for MySQL? I'm using 5.x if that is a factor. ...
My company currently has a transactional db running on Sql Server 2005. We are going to add a MySql (running on linux) reporting db. We'll need to get replication running from the MS-Sql db to the MySql db. It doesn't have to be real time but should be within a few minutes. I've got pretty good MSSql Dev skills and so-so dba skills but ...
http://thedailywtf.com/Articles/The-Hot-Room.aspx You see how at the bottom there're links to the next and previous articles ("Unprepared For Divide_By_Zero" and "A Completely Different Game")? How do I do that, but selecting the next and previous non-private articles? This works for selecting the next article: SELECT * FROM articles W...
I have an ISAm table in mySql that was created similar to this: create table mytable ( id int not null auto_increment primary key, name varchar(64) not null ); create unique index nameIndex on mytable (name); I have multiple processes inserting rows into this table. If two processes try to insert the same "name", I want to make sur...
When I run my php page, I get this error and do not know what's wrong, can anyone help? If anyone needs more infomation, I'll post the whole code. Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in H:\Program Files\EasyPHP 2.0b1\www\test\info.php on line 16 <?PHP $user_name = "root"; $passw...
I'm thinking the answer is no, but I'd love it it anybody had any insight into how to crawl a tree structure to any depth in SQL (MySQL), but with a single query More specifically, given a tree structured table (id, data, data, parent_id), and one row in the table, is it possible to get all descendants (child/grandchild/etc), or for tha...
What's the best way to create a non-NULL constraint in MySQL such that fieldA and fieldB can't both be NULL. I don't care if either one is NULL by itself, just as long as the other field has a non-NULL value. And if they both have non-NULL values, then it's even better. ...
I have a web site using apache httpd as the server and mysql as the backend. It publishes a "thought for the day" that has gotten so popular that the server is crashing due to the number of requests. Since the same page is been requested (the thought only changes once a day), is it possible to put a caching server in front of my main ser...
In my free time I started writing a small multiplayer game with a database backend. I was looking to separate player login information from other in game information (inventory, stats, and status) and a friend brought up this might not be the best idea. Would it be better to lump everything together in one table? ...
I'd like to know if anyone has any first-hand experience with this dichotomy. A few blogs say the mysql extension is faster than mysqli. Is this true? And I'm only asking about speed. I know mysqli has features that are not present in the older extension. ...
Hi, I want to display the TIME field from my mysql table on my website, but rather than showing 21:00:00 etc I want to show 8:00 PM. I need a function/code to do this or even any pointers in the right direction. Will mark the first reply with some code as the correct reply. ...
I just moved to a new hosting company and now whenever a string gets escaped using: mysql_real_escape_string($str); the slashes remain in the database. This is the first time I've ever seen this happen so none of my scripts use stripslashes() anymore. This is on a CentOS 4.5 64bit running php 5.2.6 as fastcgi on a lighttpd 1.4 se...
I am trying to take a rather large CSV file and insert it into a MySQL database for referencing in a project. I would like to use the first line of the file to create the table using proper data types and not varchar for each column. The ultimate goal is to automate this process as I have several similar files but the each has different ...
We run a medium-size site that gets a few hundred thousand pageviews a day. Up until last weekend we ran with a load usually below 0.2 on a virtual machine. The OS is Ubuntu. When deploying the latest version of our application, we also did an apt-get dist-upgrade before deploying. After we had deployed we noticed that the load on the C...