Date: 12/11/06     Keywords: database, web

[edit to add:] Problem solved - in the end I have merged the tables together so all the data is now in one table. Thank you everyone for your suggestions - it was just getting too complicated and dragging the search time down with the data split out.

Hi

I'm working on a project that has 600,000+ records and they have been divided up by state in the database (therefore there are 51 different tables - including DC) and I'm having a problem getting all of the data out of the tables when more than one table is selected.

My original select statement looks something like this:

-----

SELECT AZ_ID,AR_ID,AZ.BusinessName,AR.BusinessName,AZ.Address,AR.Address,AZ.City,AR.City,AZ.State,AR.State,
AZ.Zip,AR.Zip,AZ.Phone,AR.Phone,AZ.SalesVolume,AR.SalesVolume,AZ.ContactFirstLastTitle,AR.ContactFirstLastTitle
FROM AZ, AR WHERE AZ.SalesVolume = 'Less than $500,000' AND AZ.WebAddress IS NOT NULL
AND AR.SalesVolume = 'Less than $500,000' AND AR.WebAddress IS NOT NULL LIMIT 500

-----

I have tried doing joins but there are no common fields so it is returning the results of the last state selected.

Any assistance would be greatly appreciated as this has been stumping me for the past little while.

Source: http://community.livejournal.com/mysql/107000.html

Date: 11/14/06     Keywords: php

Hi, I am using phpmyadmin's search to find bad data. The data is WAY too long to search manually.

What I am basically looking for is when the start_time has seconds in it. Such as 01:00:05. (01:00:00 is good data). I am looking for all instances where the seconds are not equal to zero.

Lil' help with the query?


SOLVED: See comments.

Source: http://community.livejournal.com/mysql/106501.html

Date: 11/13/06     Keywords: no keywords

I have a question

So, I have the following table:

+----------+-----------+------+-----+---------+-------+
| Field    | Type      | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| nid      | int(32)   | NO   | PRI |         |       |
| cid      | int(11)   | YES  |     |         |       |
| ntitle   | char(255) | YES  |     |         |       |
| ndate    | char(40)  | YES  |     |         |       |
| month    | char(5)   | YES  |     |         |       |
| day      | char(5)   | YES  |     |         |       |
| year     | char(5)   | YES  |     |         |       |
| nvisible | int(1)    | YES  |     |         |       |
+----------+-----------+------+-----+---------+-------+

khamon

Source: http://community.livejournal.com/mysql/106469.html

Date: 11/09/06     Keywords: mysql, sql

Right now were doing backup's by hand because the last developer lied and said he had a working backup system. So instead of trying to figure out what he tried to do, my senior and I decided to start from scratch. Connection between the machines is handled with ssh pub/priv key and keychain so the local script doesn't have a problem connecting securely.

Untested but the plan is to have a cron script connect to the remote and run mysqldump. The backup DB account only has select privileges so one of the idea's was to put the password in my.cnf. IS there a better way then this?

Source: http://community.livejournal.com/mysql/106193.html

Date: 11/06/06     Keywords: mysql, sql

I can’t find this in the mysql manual, I’m working on some filters and one of the things that we need to do is to have relative string comparisons.

When comparing strings is there any difference between > and >=? I tried both comparisons and they seemed to return the same result sets.

Thanks

Source: http://community.livejournal.com/mysql/105911.html

Date: 11/01/06     Keywords: sql

Trying to optimize a DB server but it's got some difficulties.  So far the only benchmark suite I've run is the sql-bench but I don't have anything to compare it to except its self.  So far, with minimal optimization, it can do 30K of key based updates in 31 seconds or just a little bit over 1 second a update.  I'd like to bring that time down and with the hardware provided it should be possible.  Any suggestions or links to other benchmark results?

Source: http://community.livejournal.com/mysql/105557.html

Date: 10/28/06     Keywords: no keywords

In my table I have 4 columns: item_date, start_time, item_date2, end_date
ie item_date, item_date = yyyy-mm-dd
and start_time, end_date = hh:mm:ss

I am trying to calculate the value of line items

My old query used to work fine but now we have added an end date.

Here's where I am at, but it doesnt work. I think I need to merge (item_date2 end_time) and (item_date start_time)


SELECT invoice_number,
sum( (( (TIME_TO_SEC(item_date2)+ TIME_TO_SEC(end_time)) - (TIME_TO_SEC(item_date) + TIME_TO_SEC(start_time)) )/3600) * rate) AS subtotal
FROM invoice_items
WHERE office_id='300' AND service_type!='225'
GROUP BY invoice_number
ORDER BY invoice_number


Source: http://community.livejournal.com/mysql/105248.html

Date: 10/24/06     Keywords: mysql, database, sql

Hello. I am so glad I found this community. I am very new to MySQL & completely lost. I'm using it for a Database Fundamentals course that I'm taking. The instructor isn't teaching us the material...she's expecting us to learn from the textbooks. I'm finding this to be a subject that's hard to learn from books alone. An example of what I'm having a problem with:

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`BOOK_CODE` char(4) NOT NULL default '',
`TITLE` char(40) default NULL,
`PUBLISHER_CODE` char(3) default NULL,
`TYPE` char(3) default NULL,
`PRICE` decimal(4,2) default NULL,
`PAPERBACK` char(1) default NULL,
PRIMARY KEY (`BOOK_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `henry`.`book`
--

/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` (`BOOK_CODE`,`TITLE`,`PUBLISHER_CODE`,`TYPE`,`PRICE`,`PAPERBACK`) VALUES
('0180','A Deepness in the Sky','TB','SFI','7.19','Y'),
('0189','Magic Terror','FA','HOR','7.99','Y'),
('0200','The Stranger','VB','FIC','8.00','Y'),
('0280','Rumpole for the Defence','PE','MYS','7.19','Y'),
('0378','Venice','SS','ART','24.50','N'),
('0389','Concepts of Database Management','CT','CMP','43.99','Y'),
('079X','Second Wind','PU','MYS','24.95','N'),
('0808','The Edge','JP','MYS','6.99','Y'),
('1351','Dreamcatcher: A Novel','SC','HOR','19.60','N'),
('1382','Treasure Chests','TA','ART','24.46','N'),
('138X','Beloved','PL','FIC','12.95','Y'),
('1456','Truman','SS','HIS','29.90','Y'),
('2226','Harry Potter and the Prisoner of Azkaban','ST','SFI','13.96','N'),
('2281','Van Gogh and Gauguin','WP','ART','21.00','N'),
('2766','Of Mice and Men','PE','FIC','6.95','Y'),
('2908','Electric Light','FS','POE','14.00','N'),
('3350','Group: Six People in Search of a Life','BP','PSY','10.40','Y'),
('3743','Nine Stories','LB','FIC','5.99','Y');
INSERT INTO `book` (`BOOK_CODE`,`TITLE`,`PUBLISHER_CODE`,`TYPE`,`PRICE`,`PAPERBACK`) VALUES
('3906','The Soul of a New Machine','BY','SCI','11.16','Y'),
('5163','Travels with Charley','PE','TRA','7.95','Y'),
('5790','Catch-22','SC','FIC','12.00','Y'),
('6128','Jazz','PL','FIC','12.95','Y'),
('6328','Band of Brothers','TO','HIS','9.60','Y'),
('669X','A Guide to SQL','CT','CMP','37.95','Y'),
('6908','Franny and Zooey','LB','FIC','5.99','Y'),
('7405','East of Eden','PE','FIC','12.95','Y'),
('7443','Harry Potter and the Goblet of Fire','ST','SFI','18.16','N'),
('7559','The Fall','VB','FIC','8.00','Y'),
('8092','Godel, Escher, Bach','BA','PHI','14.00','Y'),
('8720','When Rabbit Howls','JP','PSY','6.29','Y'),
('9611','Black House','RH','HOR','18.81','N'),
('9627','Song of Solomon','PL','FIC','14.00','Y'),
('9701','The Grapes of Wrath','PE','FIC','13.00','Y'),
('9882','Slay Ride','JP','MYS','6.99','Y'),
('9883','The Catcher in the Rye','LB','FIC','5.99','Y'),
('9931','To Kill a Mockingbird','HC','FIC','18.00','N');
/*!40000 ALTER TABLE `book` ENABLE KEYS */;

How do I put that in proper format in a database, so I can run & install it in a MySQL database? Or can I run it as is?
Or are there any really simple resources that you could suggest, that might also offer some assistance?

Thank you so much for the help!

Source: http://community.livejournal.com/mysql/105012.html

Date: 10/23/06     Keywords: mysql, sql

I have a site where postings expire after 30 days. Whats the best way to automatically delete them from MySQL?

Source: http://community.livejournal.com/mysql/104915.html

Date: 10/05/06     Keywords: no keywords

Is it possible to insert data into a row above/before the previous point?

Example:

            If  “Y” is already in the DB
            Can I insert “Z” above the “Y” row so that it appears (when viewed):

Z
Y

I don’t even know how I’d look this up.

I’ve been storing Dates and Times (and what the user did) in plain text (Oct-05-2006) in separate columns.

User logs in  ---  Information stored
User downloads a file ---- Information stored

Without having to rewrite all the scripts that look at this data, I was simply hoping to write it in a different order---one more preferable for viewing.

After all the great help  (Really—Thanks!) I can see that I just need to store time/date properly and change the ORDER by which I bring things up on the screen.

Thanks again!

Source: http://community.livejournal.com/mysql/104531.html

Date: 10/05/06     Keywords: mysql, sql, web, hosting

My current contractor has a number of websites that were developed by a whole lot of different people. Because of the NDA, I can't cut & paste the code involved, but has anyone heard of a Error # 30 when using MyIsam tables on a 4.1.x MySql server? I've ransacked the documentation trying to figure out what caused this because the current solution offered by the hosting company is unacceptable.

Some more specifics... the error was with a UPDATE that took a field value and set it to myField = myField + 1; It's been bothering me because I have the mysql source code and I've tried walking through the code to figure out where this phantom error code came from.

Source: http://community.livejournal.com/mysql/104322.html

Date: 09/25/06     Keywords: mysql, database, sql

Hello,

I'm making a wallpaper database for my site, and I have two tables: wallpapers and wpartist. The wallpapers table holds all of the wallpaper info (obviously), while the wpartist table has the info on the artists who made the wallpapers. The two tables are JOINed by the artistid field.

Right now I'm able to show all wallpaper results, but I would like to also be able to show only a certain category, like a specific character. Is it possible to do that now that WHERE is being used by the joined tables? Here is the code I'm using now:

$result = mysql_query
("SELECT * FROM wallpapers, wpartist
WHERE wallpapers.artistid = wpartist.artistid
order by date desc");


I hope you guys can help! =)

Source: http://community.livejournal.com/mysql/104162.html

Date: 09/23/06     Keywords: no keywords

Hello, not directly on topic to the community but I've been trying to find a US census dataset of US population broke down by State -> county. I know it exists cause I have an old copy, but I can't find a recent one from the 2000 census and not sure where to look.

Source: http://community.livejournal.com/mysql/103768.html

Date: 09/21/06     Keywords: php, mysql, database, sql

My laptop dual boots Windows XP and Ubuntu with a shared FAT32 drive they can both access. I'm working on a PHP project and ideally I would like both XP and Ubuntu to be able to access the same databases. Does anybody know if this is possible? ie, install MySQL on both OSes but somehow have them look on the shared drived for the actual database files? Any pointers / guides you guys could give me would be greatly appreciated!

Source: http://community.livejournal.com/mysql/103467.html

Date: 09/20/06     Keywords: no keywords

How could I SELECT DISTINCT the first letter of a string stored as a varchar? 

Source: http://community.livejournal.com/mysql/103279.html

Date: 09/07/06     Keywords: no keywords

Is there a quick way of find the latest record if the field set includes a datetime field.  I am not going by the assumption that the latest will be the last auto. num. ID in the table.  My other thought was since I am taking a hit by accessing the DB, I might as well just add the latest record ID to the applications equivalent of a registry.

Source: http://community.livejournal.com/mysql/102685.html

Date: 09/02/06     Keywords: mysql, database, sql, hosting

Anyone know of a freeware tool to Synchronize a MySQL testing server on my machine, a MySQL remote server on my firm's server, and a MySQL client's server. Trick is that the database name's for each server is different according to each server's hosting scheme. Local has no prefix but the firm and client server's have prefix's. I have to do it in stages test->firm, test->client... that's fine because its better then nothing.

otherwise I am just going to write a script to try and transfer data between the servers with the testing server being the master of the others.

Source: http://community.livejournal.com/mysql/102449.html

Date: 08/31/06     Keywords: mysql, sql, web

      Hello, I just found the MySQL community and figured this could be an educational group to watch.  I am predominately a web developer 1st, and a DB scheme designer by necessity.  Otherwise I learned SQL on Access in 1999 then it took me 3-4 years to unlearn the MS way and now prefer MySQL.  So yeah, that's me.

Now my fun filled question:   Is there some sort of way to join 'SHOW TABLES' and 'SHOW FIELDS FROM' in a SQL query?  Maybe a multi-line query or such that is dynamic.  I've been rummaging through the MySQL reference manual and found nothing so far... though I did try the crazy idea of DESCRIBE (show tables); which was a long shot that did nothing.

Update:
Got this from beskov's comment.

SELECT table_name, column_name
FROM Information_Schema.columns;

And added WHERE to focus just on the DB I am interested in.

SELECT table_name, column_name
FROM Information_Schema.columns
WHERE table_schema = 'myDbName';

Source: http://community.livejournal.com/mysql/102165.html

Date: 08/30/06     Keywords: mysql, sql

When I try to remotely connect to mysql, I'm getting the collowing error:

ERROR 2003: Can't connect to MySQL server on 'subdomain.domain.edu' (111)

As far as I can tell, "skip-networking" has been commented out in my.cnf -- a pound sign in the front means commented out, as far as I know. There is a /tmp/mysql.sock file, which should mean there's an available socket. I've compared my server against a test server I use, and the my.cnf files look pretty much identical.

The only different that I know of between my server at the test server is that the real server is behind a firewall, with packets being routed via snat. That might be my problem, because I can't seem to telnet to port 3306 from anywhere either.

Any help would be greatly appreciated. Thanks!

Source: http://community.livejournal.com/mysql/102128.html

Date: 08/27/06     Keywords: no keywords

I've got categories, and products in categories. (generalizing a bit). The products have a certain order within each category. Each product has an image. I need to list all of the categories, showing the image for the product with the lowest sort order in each category.

Imagine:
create table categories (id int auto_increment not null primary key, name varchar(32));
create table products (id int auto_increment not null primary key, category_id int references categories(id), name varchar(32), image varchar(32), sort int);

categories: (1,'Watches'),(2,'Surfboards');
products: (1,1,'Alpha','alpha.jpg',2), (2,1,'Beta','beta.jpg',0), (3,2,'Surboard','surf.jpg',3), (4,2,'Surfboard2','fwibble.jpg',4);

I want a query to return:

1, Watches, Beta, beta.jpg
2, Surfboards, Surfboard, surf.jpg

I feel like I'm missing something obvious, but I'm blanking. I thought I could GROUP BY category_id HAVING min(products.sort) = products.sort, but no luck. ;)

Source: http://community.livejournal.com/mysql/101787.html