Date: 10/06/05     Keywords: no keywords

In dealing with some odd flat files I finally have this.

TABLE A has a column called posts

TABLE B has a column called date

They both are in order so if I SELECT * FROM each and put the data side by side the date matches the post.

How can I get these two tables merged with the dates in the date column lined up with the posts in the posts column?

Source: http://www.livejournal.com/community/mysql/69932.html

Date: 10/02/05     Keywords: no keywords

Hi all, the previous maintainer of this community abandoned it and it's been transferred to me now. Email me with any problems or ideas (info is in the userinfo for the community). Thanks.

Source: http://www.livejournal.com/community/mysql/69610.html

Date: 09/30/05     Keywords: sql, microsoft

Компания, специализирующаяся на разработке управленческих аналитических систем, приглашает на работу разработчика баз данных

Требования:
знание и опыт работы с Microsoft SQL Server 2000;
знание Access 2000, ADO, ADO. net.;
преимуществом является владение OLAP технологией

Обязанности:
участие в проектировании и разработке автоматизированных систем управления для крупных предприятий.

Условия:
Условия, гарантированные Трудовым Кодексом;
Комфортные условия работы;
Льготное питание;
Возможности карьерного и профессионального роста
Заработная плата от $700

Контактная информация:
Имя: Воробьева Мария
Телефон: (812) 335 42 80/ 8 901 306 54 15
E-mail: maria.vorobyeva@splc.ru

Source: http://www.livejournal.com/community/mysql/69244.html

Date: 09/17/05     Keywords: php, mysql, software, database, sql, web

Does anyone know how much one can expect to spend to hire someone to create a mysql/php script? The one in particular is for allowing a member to view online statements at a gym website and needless to say, quite secure. The software we use for gym management uses a mysql database. I've got to run this by my manager in two days so I'm looking for how much he can expect to spend and what is a good and reliable source to get this done. Thanks for any assistance. Trinity

Source: http://www.livejournal.com/community/mysql/68184.html

Date: 09/07/05     Keywords: cms, php, mysql, html, sql

Ok, I’m trying to make a nice little search script for my CMS. The whole site is in the db, and I would like to have a way for people to be able to search all the content. Each entry in the db has an id, name, title, description and body. They may all contain html. now I would like to run a query that looks through all the fields (except the id) and pulls out all those that match, then display them in a table (I assume a while loop is fie for this). So what I'm really looking for is an sql query that will search all the fields. Now I would also like it to recognise boolean searches if this is possible.

Ok, I’m sure you get the idea by now. Does anyone know how this might be achieved? Please note, my php knowledge is about a year old and my mysql is very poor. I have also been through mysql docs (mainly this page, but have found it s little beyond me).

Thanks for any help

x-posted to php

Source: http://www.livejournal.com/community/mysql/68021.html

Date: 09/07/05     Keywords: php, mysql, html, database, sql

i'm doing a project where i need to measure distances between zip codes; en route to a solution i stumbled upon a couple of handy resources that i figured i'd pass along:

http://www.zend.com/codex.php?id=1486&single=1
a class used to do various zipcode calculations such as distance and finding the zip codes within range of another zip code
take a look at the class to see how he wants your tables and fields named for the state and zip code database tables

http://www.cfdynamics.com/cfdynamics/zipbase/index.cfm
zipbase - datbase of zip codes with lat & long info, state info, etc. available in a text file and an access datbase
i used the textfile and in mysql said:
mysql> LOAD DATA LOCAL INFILE 'c:/ZIP_CODES.txt'
-> INTO TABLE zip_code
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';

http://27.org/isocountrylist/usps_states_list.sql
SQL file for creating a states table (i believe in the zipcode class, he wants the states table called "state")

i used that to make a silly little app to test some of the features of the class:

 $zip){
		$field = 'zip'.($key+1);
		if(isset($_REQUEST[$field])){
			if(is_numeric($_REQUEST[$field])){
				$zips[$key] = $_REQUEST[$field];	
			}
		}
	}
	
	

?>
" method="GET">
" /> " /> 



 $zip){
		if($zip){
			echo "
";
			echo "$zip:";
			$details = $zc->get_zip_details($zip);
			if(!empty($details)){
				foreach($details as $key => $value){
					echo "
$key: $value
";
				}
			}
			echo "
";
		}
	}
?>


get_distance($zips[0], $zips[1]);	
	}
?>

php

mysql

bobalien

Source: http://www.livejournal.com/community/mysql/67585.html

Date: 09/03/05     Keywords: no keywords

Hi guys,
Been lurking for a while, figured I'd step out and join a few communities.
*waves*

Source: http://www.livejournal.com/community/mysql/67504.html

Date: 08/23/05     Keywords: mysql, database, sql

Okay, I have a dilemma.

I'm not sure how to assemble a SELECT query for what I want to do.

I have a database of ratings for books and movies. Each rating/review is placed into a new row, with a reference number for the item they're related to (actual books and movies are in their own table). and I wish to run a SELECT that will find rating entries for books that have a minimum of 5 entries (ie; 5 rows).

I'm somewhat of a newcomer to mysql, but I'm not a complete moron. I've been able to set up long and complex query strings, but for some reason, this is befuddling me.

Any ideas?

** EDIT **

Problem solved, thanks to timeimp & bobalien

The resulting query ended up being:

$query = "SELECT book_reviews.bookid AS idofbook, avg(book_reviews.rating) "
$query .= "AS nrating, books.bookname AS nameofbook FROM book_reviews,books "
$query .= "WHERE books.id = book_reviews.bookid GROUP BY book_reviews.bookid "
$query .= "HAVING count(book_reviews.rating) >= 5 ORDER BY nrating DESC LIMIT 10";

Source: http://www.livejournal.com/community/mysql/67126.html

Date: 08/09/05     Keywords: browser, database

it keeps throwing errors



/*********

postings & comments database structure
written by : that intern

*********/

CREATE DATABASE if not exists commentsDB;
USE commentsDB;

DROP TABLE IF EXISTS `userDB`; /* works fine! */
CREATE TABLE `userDB` (
`userID` INT NOT NULL AUTO_INCREMENT,
`username` varchar(8) NOT NULL,
`userpass` VARCHAR(8) NOT NULL,
`first_name` VARCHAR(10) NULL,
`last_name` VARCHAR(15) NULL,
`address_1` VARCHAR(25) NULL,
`address_2` VARCHAR(15) NULL,
`city` VARCHAR(18) NULL,
`state` VARCHAR(2) NULL,
`zip_code` TEXT NOT NULL,
`email` VARCHAR(25) NOT NULL,
`gender` VARCHAR(1) NULL,
PRIMARY KEY (`userID`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `userInfo`;
CREATE TABLE userInfo (
`browser` VARCHAR(255) NULL,
`userIP` TEXT NULL,
`userOS` varchar(255) default NULL,
`pages_visited` varchar(255) default NOT NULL,
`last_login` varchar(255) default NOT NULL, /* works until here */
PRIMARY KEY (`userID`),
FOREIGN KEY (`userID`) FROM userDB(`userID`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `post`; /* works fine! */
CREATE TABLE `post` (
`post_num` INT(15) NOT NULL,
`post_text` TEXT NOT NULL,
`post_cat` VARCHAR(10) NOT NULL,
`post_time` TIMESTAMP NOT NULL,
`post_date` DATE NOT NULL,
`post_user` VARCHAR(15) NOT NULL,
PRIMARY KEY (`post_num`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
`comment_text` TEXT NULL,
`comment_time` TIMESTAMP NOT NULL,
`comment_date` DATE NOT NULL,
`userIP` TEXT NOT NULL /* works until here */
post_num INT,
userID INT,
FOREIGN KEY (post_num) REFERENCES post(post_num),
FOREIGN KEY (userID) REFERENCES userDB(userID)
) TYPE=InnoDB;

Source: http://www.livejournal.com/community/mysql/66303.html

Date: 08/07/05     Keywords: no keywords

I’m a little stumped. We are building an application in which we need to join a table to itself in a many to many fashion.
The problem is that the relationships are communitive so I can’t do the standard where clause. I have thought to pull out all the joining records with a
2 selects in a union, but quite frankly that is ugly. Is there an elegant way to do this? I can’t think of one.

Source: http://www.livejournal.com/community/mysql/65823.html

Date: 08/05/05     Keywords: php, mysql, sql, web, apache

hey guys, me again -

i've got mysql 4.1.13a-nt running on win xp pro (apache and php4 as well, shouldn't be relevant tho) and I'm trying to set up and use InnoDB (for the foreign keys) - whenever I make a new table in EMS MySQL Manager or through the command-line utility and try to set it as InnoDB, when the table appears, it's always MyISAM

I'm sure my MySQL Manager version supports InnoDB since it works when I create an InnoDB table on my webhost, it's only not working locally

I did the InnoDB config on install of MySQL - is there anything else I have to set or enable to use InnoDB?

Source: http://www.livejournal.com/community/mysql/65658.html

Date: 08/02/05     Keywords: database, shopping

i'm still working on the same catalog/shopping cart system i've posted about before (details irrelevant) and I'm trying to work out how I'm going to store records of the sales that go through the site - is it a better practice to store actual values in sales tracking table as opposed to id's -

for instance - say I have a table called tblSales - in tblSales, when a transaction goes through, I'm obviously going to want to store actual dollar values for the price of the item, the amount charged, the commissions paid out (instead of doing the math later when the past sales data is pulled up - in case prices are changed or items are removed, this way I've got a record of all actual dollars that changed hands)

but what about things like the name of the item, the name of the seller, the category of the item sold - is it worth it to store these actual strings in the sales tracking database as opposed to just the item 'id', and referencing the other data later? what i'm worried about is if id's for items or categories change in the future, or items are deleted, I don't want the previous sales records to get screwy.

thanks, and let me know if i didn't make any sense

Source: http://www.livejournal.com/community/mysql/65026.html

Date: 07/29/05     Keywords: mysql, sql, web, google

I am trying to execute a delete using a subquery but I keep getting the same error and google and mysql.com are both no use as to what the error actually means

My query is this

delete from exon where exon_id in(select exon.exon_id from exon left join exon_transcript on exon.exon_id = exon_transcript.exon_id where exon_transcript.exon_id is NULL);


and this is the error

ERROR 1093 (HY000): You can't specify target table 'exon' for update in FROM clause

do you have any ideas what I am doing wrong?

exon_transcript is a table which links the exon table with another table called transcript and there were a series of rows missing from exon transcript so the links weren't present

I have been able to use other methods to delete the links from transcript but I can't see any other way of deleting the links from exon

if you want to see the schema description have a look here

http://cvsweb.sanger.ac.uk/cgi-bin/cvsweb.cgi/ensembl/sql/table.sql?rev=1.229&view=log

I am using this version of mysql

mysql Ver 14.7 Distrib 4.1.12, for dec-osf5.1b (alphaev68) using readline 4.3

thanks

Source: http://www.livejournal.com/community/mysql/64885.html

Date: 07/29/05     Keywords: no keywords

Is there a reason why no one is responding to my posts???

Source: http://www.livejournal.com/community/mysql/64707.html

Date: 07/26/05     Keywords: database

Grettings... first time poster to this community, and I'm in dire need of figuring something out.

Hindsight being 20/20 and all, I've written a lot of code around the "timestamp" table field for entries that people post. I've just started working on the "edit post" function, and I'm finding that if you update *any* column in the table, the timestamp also updates. Is there any way to make it *not* update automatically?

The only thing I can think of is running a query, pulling the timestamp and slapping it into a variable, and writing back to the database. I'd rather just have it not auto-update at all, but I'm open to suggestions!

Thanks!

Source: http://www.livejournal.com/community/mysql/64381.html

Date: 07/26/05     Keywords: php, mysql, html, sql, web, microsoft, google

Windows XP SP2
MySQL Version -- 4.1.13
PHP Version -- 5.0.4
Web Server -- IIS 6.0
Dreamweaver MX 6.1
Frustration Level -- Through the ROOF!!!


Last I posted, I was having troubles with permissions. Well, to resolve that little issue I re-install MySQl. Admin permissions are now accessible.

PROBLEM: I am unable to connect to my test server through Dreamweaver MX. I was getting the oh so helpful error message: "An unidentified error has occurred." I changed some permissions (I don't know which ones at this moment) and then I was getting the error message "HTML error code 403 forbidden". Now, I'm back to "An unidentified error has occurred." It's got to be a permissions issue but for the life of me I can't figure out which ones. I am also having an issue viewing "http://localhost/" without having to enter a password. I can view other pages on localhost just not the root. I'm sure that has something to do with my test server issue. I have gone through the MySQL manual online and offline. I scoured microsoft.com and msdn for help with the Windows permissions. I have googled all there is to google and I want to google NO MORE. I did find an article reguarding my helpful "An unidentified error has occurred" message (link - technote).

It said to install 6.1 updater for Dreamweaver MX, so I did and no change. I re-ployed the connection scripts and no change. I stopped and restarted the services and nothing. I meticulously inspected all folders that need to be accessible for the server and whatever else needs them, and no dice. I found this technote (Technote), useless. I have pretty much done everything that flipping article said and any other articles that I have found and of course no change.

So there inlays my dilemma. Where do I go from here????

*off to scream*

Source: http://www.livejournal.com/community/mysql/64173.html

Date: 07/25/05     Keywords: mysql, database, sql

Hey everyone, I'm new to the community.. i'm curious if anyone has seen anything like this. i've got a RHES3 (Fedora Core 3/4 tested as well) server running running 4.1.10a.

Long story short.. mysql runs fine minus seemingly random spiked load averages caused by a pileup of locked connections due to an UPDATE query. While query runs, all additional updates are locked. Sometimes it lasts 2 minutes, sometimes it lasts 30 minutes. You'll notice my lingo is more admin oriented, that's because I'm the sysadmin and not the programmer. Our guys who create and program the site don't have much insight to resource usage.

The query looks like this: UPDATE shockwave_traffic SET minutes_vis = minutes_vis + 1 WHERE game_id = '1' AND start_hour LIKE '2005-07-24 20%';

root 19379 0.0 0.0 5860 996 ? S 20:44 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql --open-files=4096 --datadir=/usr/local/mysql/data -O max_heap_table_size=1000m --log-slow-queries --skip-bdb --skip-innodb
mysql 19423 99.9 1.9 472984 75136 ? S 20:44 108:28 \_ /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/moe.pid --skip-locking --open-files-limit=4096 --port=3306 --socket=/tmp/mysql.sock -O max_heap_table_size=1000m --log-slow-queries --skip-bdb --skip-innodb

random tidbits i've noticed while troubleshooting:

- load avg stays between 2.00 -- 3.00 which is fine, i would think if a process were truly taxing a CPU for 99% of it's resources the load would be MUCH higher. see my next tidbit.
- during peak times and not during peak times at seemingly un-andom intervals, the load will jump to 30. When this happens I check the process list and there's one UPDATE query running and theres a full connection limit of locked queries, I believe said locked queries are waiting for the update query to finish.
- table size is tiny, 400k -- there are other tables within the db that are 2.2gb that aren't accessed, purely around for archival reasons. I've ran myisamchk and there isn't any errors being reports, check table via the CLI reports no errors as well.
- nothing in the slow query log.
- binary log disabled.
- I've had this problem on databases housed on other machines while doing SELECT (using AS), but raising the max_heap_table_size was able to calm them down.

Hardware info:

Dell poweredge 2850
Raid 5 via PERC4 dual channel raid adapter
3 15K SCSI drives.
Dual 3.2ghz Intel Xeon processors (HT enabled, although this has been disabled with the same effect).


Any thoughts or insight would be greatly appreciated. :D

Source: http://www.livejournal.com/community/mysql/63772.html

Date: 07/25/05     Keywords: no keywords

I have 500 entries in a table, and I want one field to be updated with word "dog". How do I do this?

Thanks!

Source: http://www.livejournal.com/community/mysql/63614.html

Date: 07/24/05     Keywords: mysql, sql, web

**Before asking this, I did go to the normal help sites (mysql.com...) and also scoured the web for other info. Way to complicated.


I need some help specifically for the process of setting user permissions running on XP. I'm trying to create DB's and tables and in some places I'm getting "Access Denied" errors. If anyone could point me in the right direction (i.e. your personal knowledge, a website like MySQL for Dummies..) would be much appreciated.

Source: http://www.livejournal.com/community/mysql/63395.html

Date: 07/21/05     Keywords: no keywords

$Query = "SELECT invoice_number, sum(((TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))/3600) * rate) FROM invoice_items GROUP BY invoice_number HAVING office_id='$office_id' AND service_type!='225' ORDER BY invoice_number";

Error: Unknown column 'office_id' in 'having clause'

However, office_id is a column. And the query works if I remove sum, GROUP BY, and change HAVING to WHERE.

More on the time conversion

Source: http://www.livejournal.com/community/mysql/63088.html