Let's say I have a large database that consists of products in groups. Let's say that there are 5 groups, each of them has 100,000 products. the product ids are random integers (so are the group ids) I need to find a product in a specific group. My question is which primary key is more efficient: (sid, pid) (pid, sid) sid, pid is in...
Hi! I wonder if it is possible to use FK's in Mysql (InnoDB) for inverse lookup. The reason - I want to read an XML-like structure from the db (using one table per "layer"), but be able to do this dynamically. I want to be able to update the xml structure by adding a new table and setting a FK constraint. To clarify, say we have a tab...
This SQL statement SELECT `ip`, `when` FROM `metrics` WHERE `vidID` = '1' GROUP BY DATE('when') returns one result, even though multiple are present in the table with different dates. I've tried using DATE_FORMAT as well. Am I doing something stupid? When is a timestamp column with full timestamp, including hours, minutes and seconds...
I have property management application consisting of tables: tenants landlords units properties vendors-contacts Basically I want one search field to search them all rather than having to select which category I am searching. Would this be an acceptable solution (technology wise?) Will searching across 5 tables be OK in the long run...
I have run into a situation where I want to ensure that a compound element of a table is unique. For example: Table ( id char(36) primary key, fieldA varChar(12) not null, fieldB varChar(36) not null ) I don't want fieldA and fieldB to be a compound primary key, since they change frequently, and 'id' is used as a reference thro...
Hi, I expect the following Stored Routine to return a serie of rows, while it only returns 1 CREATE PROCEDURE example() BEGIN DECLARE current_id INT; DECLARE done INT DEFAULT 0; DECLARE cur_main CURSOR FOR SELECT id from tasks; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN ...
hi , i need to make a website such that it can be accessed through the internet if internet is available otherwise when there is no internet then it should work properly on the local intranet also can this be achieved , i have built my site on php/mysql if this can be achieved please tell me and direct me to some examples of how to ach...
Let's say I have two tables, users and notes. Let's say the schemas look like this: users id, name, field2, field3 notes id, user_id, subject, heading, body, timestamp What I want to do is select every user, and the LATEST (just 1) note posted by each user based on the timestamp to show in an overview report. How would I go about d...
I hope it's the heat, this is the second question today and it's one problem after the other. Relatively simple stuff... I have this query ... while ($resultV = mysql_fetch_assoc($metQueryViews)) { $allViews[] = $resultV; } The date it's getting is:- date Count NULL 6 14-5-2009 12 15-5-2009 21 26-6-2009 18 29-6-...
Ok I have this problem that I've never had before, it's really bugging me. Basically I'm trying to submit text into a text field using a form. For some reason when I submit it with raw text and full-stops only it works fine, however it seems that when there is punctuation like a ' or a ! in the textarea then it just won't submit to the ...
I installed PHP and MySQL on our windows 2003 server about 3 months ago. Everything has been working wonderfully, but for some reason when I came into work this morning, the service had stopped working. I proceeded to start the service up again, shortly there after the service stopped again. In the Event Log for the server I have the...
I have a table that has three different date columns, so I set each column as type 'date'. However whenever im importing dates it seems to change them, and I have found it is because mysql does not allow null days and months. My dates range from 1909-00-00 1963-09-00 1907-11-30 so sometimes we dont know the month, sometimes the day, ...
In my heart, I feel that there must be a super simple recursive solution to this, but I cannot immediately grok it. I have a tree stored in SQL as a closure table. The tree looks like: (1 (2 (3), 4)), and the languages are MySQL's SQL and PHP 5.3. The closure table is thus: +----------+------------+ | ancestor | descendant | +-------...
Hey! I am going to start working on a website that has already been built by someone else. The main script was bought and then adjusted by the lead programmer. The lead has left and I am the only programmer. Never met the lead and there are no papers, documentation or comments in the code to help me out, also there are many functions ...
Hi, I'm having a problem with a query prepared in PHP with PDO. The code: $link = new PDO("mysql:dbname=$dbname;host=127.0.0.1",$username,$password); $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $query = $link->prepare("SELECT locality_name FROM :passedday GROUP BY locality_name ORDER BY locality_name DESC"); $query...
"I am getting a weird error when using NHibernate. And I don't know what is causing this error. I am new to the whole Visual Studio and NHibernate, but not to Hibernate. I used Hibernate in the past in Java projects. Any help would be appreciated in pointing me where my error is. I am using Visual Studio 2008 SP1 with Mysql 5.1. Below...
I need to solve a problem within job booking software and am hoping to find a good way of doing that. The current system uses a set of check boxes to represent the problem description when a customer rings up. These check boxes are useful to admin, but not really from a customer perspective. My thoughts were to have 1 or more english r...
Hi, I have two different tables, lead and click. I would like to query MySQL to get the sum of the commissions generated by clicks and leads ordered by date. Lead id|date|commission Click id|date|commission (Both have other fields, but they aren't important for this question.) So if I had: Lead: 1|2009-06-01|400 2|2009-06-01|3...
UNIX_TIMESTAMP() isn't an ANSI standard keyword but an addition to the MySQL syntax. However, since I'm supporting multiple DB's, is there an ANSI standard way to write UNIX_TIMESTAMP(); Thanks ...
Hi, I'm pulling a list of client names out of one MySQL table to serve as a drop down selection. The data is to be stored on a separate table. I would like the option to come up as selected if there already is a client assigned to the field in the second table. Also, my radio buttons are inputting their data into the wrong fields, an...