Database Connection Problem with MAMP (Mac)
I installed a preconfigured package called MAMP on my MacBook Pro and have just about everything working except my database connections. I can use phpMyAdmin to manipulate databases and tables, but I can't connect to those databases from Dreamweaver. I read somewhere that you have to use a special username (or host?) if your components are running on different servers. I have two Apache installations running, with the default installation running the show. In other words, phpMyAdmin is running with the original Apache, not MAMP's Apache (at least, I think so). Anyway, I created a user named 127.0.0.1, which I believe is my webserver's IP address. I assigned no password. This is what my connection looks like: $link = mysql_connect ("127.0.0.1" , "" , "") or die(mysql_error()); mysql_select_db ("WebServer", $link) or die(mysql_error()); I also tried it with host also designated 127.0.0.1... $link = mysql_connect ("127.0.0.1" , "127.0.0.1" , "") or die(mysql_error()); mysql_select_db ("WebServer", $link) or die(mysql_error()); Access denied for user ''@'localhost' to database 'webserver' * * * * * This database connection... $link = mysql_connect ("localhost" , "root" , "") or die(mysql_error()); mysql_select_db ("Test2", $link) or die(mysql_error()); ...yields this error: Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' (using password: NO) in /Users/davidblomstrom/Sites/Geobop/a1/dbc.php on line 21 Access denied for user 'root'@'localhost' (using password: NO) * * * * * The only database I could connect to without an error message was the default database "test," which I accidentally dropped. The weird thing is that I couldn't connect to it if I designated "root" as the username; rather, there was no error only if I used anything BUT root, even though the usernames I assigned didn't even exist. So I don't think I was really connected. I've seen some sources that talk about "symlinking" MySQL (or phpMyAdmin?) if Apache is on a different server. Could that be the problem? If not, does anyone know how I can troubleshoot this? Thanks. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/
Re: Need Database Connection Sans Password
You wrote, "You need to create a root password and other users." insert into mysql.user set Host='localhost', User='', * * * * * * * * * * Thanks, but that gave me an error message. I clicked on a particular database, then clicked Privileges and changed the password from "root" to "chestnut." But instead of assigning chestnut to that database, it apparently assigned it to EVERY database. Every database I click Privileges on now displays the following: User Host Type Privileges Grant Action chestnut localhost global ALL PRIVILEGES Yes root localhost global ALL PRIVILEGES Yes root webeditions-computer.local global ALL PRIVILEGES Yes Yet I can't write a database connection using "chestnut" as the usernameexcept for the table "test." This is what its privileges look like: User Host Type Privileges Grant Action Any % database-specific ALL PRIVILEGES No chestnut localhost global ALL PRIVILEGES Yes root localhost global ALL PRIVILEGES Yes root webeditions-computer.local global ALL PRIVILEGES Is there some way I can go into phpMyAdmin > NewDatabase > Privileges and change User to "Any" and Host to "%"? I don't want to do it if it's going to show up on every single database. Thanks. The fish are biting. Get more visitors on your site using Yahoo! Search Marketing. http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php
Re: Need Database Connection Sans Password
You wrote, "Is you MySQL properly installed ???" Apparently. I just finished reinstalling MAMP (Apache, PHP & MySQL for Mac), and I was able to copy a database I had created earlier into the MySQL folder. "Can you login to mysql client as root from your localhost ???" I'm not sure what you mean, but I can manipulate my databases using phpMyAdmin. "You need to create a root password and other users." OK, can I go into phpMyAdmin, click (Databases)...which displays no particular database at all...then click the little SQL tab on the left and paste the code you gave me in the SQL window? And that will create a username and password that will work for every local database I create, right? Thanks. insert into mysql.user set Host='localhost', User='', Password=PASSWORD(''), Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y', Create_priv='Y', Drop_priv='Y', Reload_priv='Y', Shutdown_priv='Y', Process_priv='Y', File_priv='Y', Grant_priv='Y', References_priv='Y', Index_priv='Y', Alter_priv='Y', Show_db_priv='Y', Super_priv='Y', Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y', Repl_slave_priv='Y', Repl_client_priv='Y', Create_view_priv='Y', Show_view_priv='Y', Create_routine_priv='Y', Alter_routine_priv='Y', Create_user_priv='Y', ssl_type='', ssl_cipher='', x509_issuer='', x509_subject=''; flush privileges; Adjust all privileges accordingly. - Original Message - From: "David Blomstrom" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Friday, March 23, 2007 11:42:28 AM (GMT-0500) Auto-Detected Subject: Need Database Connection Sans Password How would you write a database connection for a database on which you haven't yet established a username or password? None of the following work: $link = mysql_connect ("localhost" , "root" , "") or die(mysql_error()); mysql_select_db ("test", $link) or die(mysql_error()); $link = mysql_connect ("localhost" , "root") or die(mysql_error()); mysql_select_db ("test", $link) or die(mysql_error()); $link = mysql_connect ("localhost") or die(mysql_error()); mysql_select_db ("test", $link) or die(mysql_error()); Thanks. No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121
Need Database Connection Sans Password
How would you write a database connection for a database on which you haven't yet established a username or password? None of the following work: $link = mysql_connect ("localhost" , "root" , "") or die(mysql_error()); mysql_select_db ("test", $link) or die(mysql_error()); $link = mysql_connect ("localhost" , "root") or die(mysql_error()); mysql_select_db ("test", $link) or die(mysql_error()); $link = mysql_connect ("localhost") or die(mysql_error()); mysql_select_db ("test", $link) or die(mysql_error()); Thanks. No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail
Re: Creating Password & Username with phpMyAdmin
You wrote, "Actually the way to do this would be as follows: When you have created a new database, click on the SQL tab and the use the following to create the user and password: GRANT ALL PRIVILEGES ON databasename.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' Hit the submit button and you are set." * * * * * * * * * * Actually, that didn't work. I typed the following into my SQL window: GRANT ALL PRIVILEGES ON World.* TO [EMAIL PROTECTED] IDENTIFIED BY 'Citizen' It seemed to process correctly, with no error messages, but no database was created. So I created a database manually, then clicked on Privileges. Next, I created a password and username. Not only can I not access the database, I can't even access phpMyAdmin now; I get this error message: * * * * * * * * * * Welcome to phpMyAdmin 2.7.0-pl2 phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server. Error MySQL said: Documentation #1045 - Access denied for user 'root'@'localhost' (using password: YES) * * * * * * * * * * I opened config.inc.php but couldn't find any reference to the database table, password or username I created. Any tips? Thanks. - Original Message ---- From: Schalk Neethling <[EMAIL PROTECTED]> To: David Blomstrom <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, March 21, 2007 1:37:12 AM Subject: Re: Creating Password & Username with phpMyAdmin No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail
Re: Creating Password & Username with phpMyAdmin
Ah, that does look like a better way. Thanks! Schalk Neethling <[EMAIL PROTECTED]> wrote: Hi David, Actually the way to do this would be as follows: When you have created a new database, click on the SQL tab and the use the following to create the user and password: GRANT ALL PRIVILEGES ON databasename.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' Hit the submit button and you are set. - We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list.
Creating Password & Username with phpMyAdmin
Sorry for the beginner's question, but I'm confused. I can easily create passwords and usernames for my online databases by going into my C-Panel and clicking a MySQL icon. It fetches a page that allows me to easily create and delete databases, add and change passwords, etc. But I can't remember how I created my password and username for the database on my PC. I'm now trying to duplicate that database on a MacBook Pro running MySQL 5 and the current version of phpMyAdmin. But I'm not sure about creating a password and username with phpMyAdmin. Suppose I have a database named World, and I want to give it the password Global and the username Citizen. I created the database, then clicked the Privileges tab, which took me to the page User 'root'@'localhost' : Edit PrivilegesAm I correct in ignoring everything (Global Privileges, Database-Specific Privileges, etc.) except the section?... Change Login Information / Copy User In this section, under Username, I assume I would change "root" to "Citizen." Would I leave Password set to "Do not change password" before typing in "Global"? Then I would simply click "Generate" and I'm finished, right? Thanks. - Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
Importing Multiple SQL Files/Database Tables
I'm in the process of moving a MySQL database with about 170 tables from my PC to a new MacBook Pro. On my PC, I exported each database table as a SQL file, then copied a folder containing all these files to my Mac. I'd like to know if there's a way to import all these SQL files into a database on my Mac, or do I have to import them one at a time. I generally update my online database by deleting a table, then clicking a tab in phpMyAdmin that fetches a pop-up SQL window. However, it only allows me to import one SQL file at a time. Regardless of how I import these SQL files, I next need to know if I can copy or move more than one table at a time to another database. I want to dump everything into a temporary database, then move only the tables I decide to keep into a permanent database. I installed a program called MAMP, which includes MySQL 5 and the current phpMyAdmin. When I opened phpMyAdmin, I discovered that I can check multiple tables. But when I click the Operations tab to copy/move them to another database, it looks like I can only do one at a time. Thanks. - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started.
Re: Moving Database from PC to Apple
OK, now I get it. I'll give that a try. Thanks for the tip about the OmniWeb browser, too. - Original Message From: Chris Sansom <[EMAIL PROTECTED]> To: David Blomstrom <[EMAIL PROTECTED]>; mysql@lists.mysql.com Sent: Sunday, October 8, 2006 1:51:33 AM Subject: Re: Moving Database from PC to Apple At 15:01 -0700 7/10/06, David Blomstrom wrote: >Thanks. Is this something I can do through phpMyAdmin? Yes! Easy: First, create the database - just the database, no tables or anything - on the Mac. Next, go to the database on the PC in phpMyAdmin and without selecting a table in the sidebar, click the Export tab. In the 'export as' part of the page (which varies wildly between phpMyAdmin versions), SQL is probably selected as the default. If so, leave it; if not, select it. Also make sure both Structure and Data are selected in the 'what to export' part. Then click the Go button. This will display all your database as SQL commands in text format. Select All, copy it into a text file and save it. Warning: in some older versions of phpMyAdmin, you'll get a line saying something like 'Database xxx running on yyy' at the top, as an html , above the comment lines starting with #. This line will be included in the 'select all', so you'll have to delete it before you import... which comes next: Having transferred the text file to your Mac, go into phpMyAdmin, to the new database you have created, and click SQL among the tabs along the top. If there's anything in the that appears, delete it, then copy and paste the entire contents of the text file into there (tip: if you get hold of the OmniWeb browser for Mac OS X, you can open a into a nice big editing window, which will make it easier to see what you're doing). Click Go and, to quote the immortal Mr Jobs, 'Boom! You're done.' -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Outside of the killings, Washington has one of the lowest crime rates in the country. -- Mayor Marion Barry, Washington, DC
Importing a Database (.mpb file)
Hopeffully this will be the last question in this series. :) I want to copy a database from my PC to my Apple laptop. I installed MySQL's GUI Tools on both computers, created a file named Backup.mpb on my PC, then put a copy of it on my Mac. Now I'm trying to figure out how to get Backup.mbp into my new MySQL program. I thought perhaps I could import it with phpMyAdmin, just as I import SQL files, but that didn't work. If the database is named "Sky," do I have to create that database in my new MySQL program before I can import the Sky backup? What's the easiest way to import it? Thanks.
Re: Setting Up MySQL Administrator
OK, I'm halfway there. But I don't understand what you mean by "saved settings." Is there some sort of default value I can try? Also, if I can't recover my password, is there a file I can open and retrieve it from? I tried it with localhost, Port 3306, Username: root and the password of one of my databases (but nothing under "Stored Connection") and got MySQL error #1045 - "Access denied for user 'root'@'localhost' [using password: YES] Thanks. - Original Message From: John Meyer <[EMAIL PROTECTED]> Assuming that you installed mysql (and keep in mind, we're talking about your MYSQL, not apache), then stored connection refers to the saved settings. "Server Host" is either the ip or the domain name of your server. If you're talking to your local MySQL server, then it's "localhost". As far as the username and password, when you installed the mysql server itself, it should have asked you to create a password for the "root" user, just type that in. Username is the username that you set up, probably root.
Setting Up MySQL Administrator
I just downloaded MySQL Administrator and am now trying to set it up. Can anyone tell me what "stored connection" and "Server Host" mean? I'm using Apache on Windows XP, but I'm not sure what they mean by Server Host. 3306 is listed under "Port" by default. Also, what are the default username and password for MySQL? I have a username and password for my database, but I don't recall creating them for the overall MySQL program. As I recall, "root" serves as the username or password. Thanks.
Re: Moving Database from PC to Apple
Ah, that's better. I learned how to use Terminal just the other day. I'll check out the other programs you recommended, too. Thanks. - Original Message From: Douglas Sims <[EMAIL PROTECTED]> To: David Blomstrom <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 3:33:59 PM Subject: Re: Moving Database from PC to Apple Hi David mysqldump is a command-line program which you can run through the terminal window. The "Terminal" application is in the "Utilities" directory under the "Applications" directory. The unix command-line interface is amazingly useful; even though it may seem a bit intimidating at first, it is well worth getting used to. You will probably want to drag the Terminal application down to the Dock so you won't have to dig around for it every time. (Or you can just open the Spotlight window with Command-Space and then type "Terminal") You can also do the same thing with a gui tool, the MySQL administrator. There are three programs in this suite and they are all very nice (the newest one, the MySQL workbench, still crashes a bit but I'm sure that will improve.) You can download the gui tools here: http://dev.mysql.com/downloads/gui-tools/5.0.html and then from the MySQL Administrator program, choose "Backup". Good luck. I use a MacBook Pro for MySQL work also (mostly developing things that will run on a linux server) and I have been very pleased with it. Douglas Sims [EMAIL PROTECTED] On Oct 7, 2006, at 5:01 PM, David Blomstrom wrote: > Thanks. Is this something I can do through phpMyAdmin? I'm not used > to working with MySQL directly and don't understand exactly what > this command means: > shell> mysqldump [options] --all-databasesDoes "shell" mean I have > to be working in some sort of command line program? > > Also, if I can't figure this out and have to resort to creating new > databases, should I just ignore the Collation feature, presumably > letting it set a default setting, or should I enter a particular > value? > > Thanks. > > - Original Message > From: mos <[EMAIL PROTECTED]> > To: mysql@lists.mysql.com > Sent: Saturday, October 7, 2006 2:26:19 PM > Subject: Re: Moving Database from PC to Apple > > At 04:00 PM 10/7/2006, you wrote: >> I recently purchased a MacBook Pro laptop and hired someone to >> help me set >> up Apache, PHP and MySQL on it. Now I want to import my database >> tables >> from my PC. So my main question is this: Is there a quick, simple of >> importing an entire database? If not, I figured I'd simply export >> each >> database table as an SQL file on my PC, then copy all the SQL >> files to my >> laptop's desktop and import them through phpMyAdmin one by one. >> Also, when >> I create a new database on my Mac, what should I choose for >> Collation - or >> should I just leave it alone (presumably the default setting)? The >> following default settings are already registered: Language: English >> (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. > > David, > Try MySQLDump which is set up to do just this. > http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html > > Mike > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql? > [EMAIL PROTECTED] > > > > > > >
Re: Moving Database from PC to Apple
Thanks. Is this something I can do through phpMyAdmin? I'm not used to working with MySQL directly and don't understand exactly what this command means: shell> mysqldump [options] --all-databasesDoes "shell" mean I have to be working in some sort of command line program? Also, if I can't figure this out and have to resort to creating new databases, should I just ignore the Collation feature, presumably letting it set a default setting, or should I enter a particular value? Thanks. - Original Message From: mos <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Saturday, October 7, 2006 2:26:19 PM Subject: Re: Moving Database from PC to Apple At 04:00 PM 10/7/2006, you wrote: >I recently purchased a MacBook Pro laptop and hired someone to help me set >up Apache, PHP and MySQL on it. Now I want to import my database tables >from my PC. So my main question is this: Is there a quick, simple of >importing an entire database? If not, I figured I'd simply export each >database table as an SQL file on my PC, then copy all the SQL files to my >laptop's desktop and import them through phpMyAdmin one by one. Also, when >I create a new database on my Mac, what should I choose for Collation - or >should I just leave it alone (presumably the default setting)? The >following default settings are already registered: Language: English >(en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks. David, Try MySQLDump which is set up to do just this. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving Database from PC to Apple
I recently purchased a MacBook Pro laptop and hired someone to help me set up Apache, PHP and MySQL on it. Now I want to import my database tables from my PC. So my main question is this: Is there a quick, simple of importing an entire database? If not, I figured I'd simply export each database table as an SQL file on my PC, then copy all the SQL files to my laptop's desktop and import them through phpMyAdmin one by one. Also, when I create a new database on my Mac, what should I choose for Collation - or should I just leave it alone (presumably the default setting)? The following default settings are already registered: Language: English (en_utf_8) MySQL Connection Collation: utf8_general_ci Thanks.
Re: MySQL Debuggers
David, Who was the ISP? Maybe they'd like some free advertising. :)Ha! I'd better wait until April 1, when I hope to have a new home. I've actually been promoting this webhost since I've been with them. They just flaked out very badly all of a sudden. Coincidentally, they had a major server crash about the very time they dumped my database. In fact, that's one reason it took me so long to troubleshoot it - I assumed I had merely been affected by the server crash, and their were so many angry customers on the forum, I was leery of adding to the congestion. Now if you want to roast a webhost, try the infamous Dathorn. I was blasting the owner of that operation years ago. When I posted about my current situation on WebHostingTalk.com, I discovered another Andrew flame-fest. Sheez. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: MySQL Debuggers
[EMAIL PROTECTED] wrote: Is this a free hosting package David B? That's a bit naughty I think - just removing your database without warning you about it first. At least you would have had time to make a backup copy. * * * * * I'm paying $50 a month, and I was blown away when they shot me down like that. I complained about it on their forum, and they said they had no choice - it was an emergency. But what was stopping them from contacting me AFTER they yanked my database? At any rate, with my peak season about six weeks away, I'm now shopping for a new webhost. It's been so long since I researched webhosts, I've discovered terms I wasn't even aware of, like VPS. It looks like I can get a much better account for what I've been paying these guys. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
MySQL Debuggers
I recently discovered that all my websites were wrecked. I spent several hours trying to troubleshoot it, until I finally discovered that my database was missing. I asked my webhost about it, and they told me they deactivated it because it was "overwhelming the server." Someone told me about a software program called Mytop that can be used to debug MySQL. However, it looks way too complex for me. I just wondered if anyone on this list is aware of other, more user-friendly MySQL debuggers. Thanks. - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: GROUP BY Destroys 2nd Function
--- [EMAIL PROTECTED] wrote: > <<<>>> > > David, is it at all intuitive to organize your > geography into a tree-type > structure? Here is an example: > > Western Hemisphere (hemisphere) > C. America (continent) > Guatemala (country) > N. America (continent) > Canada (country) > Manitoba (state/province) > Moose > Elk I see what you're saying, but I'm not sure if it's workable. I'd need a separate row for every state, province and nation the moose is native to. Ditto for each of hundreds of species of rodents and bats. Instead, I'm linking species to ecoregions, then linking the ecoregions to geographic regions - which may just be another version of what you're suggesting. In fact, I have all the animal species organized into a tree structure, and I already have a separate tree structure for nations, states, etc. Maybe I can combine them somehow. Thanks. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY Destroys 2nd Function
--- Jigal van Hemert <[EMAIL PROTECTED]> wrote: > > ANIMALS TABLE > > Canis_lupus | wolf > > Panthera_tigris | tiger > > > > JOIN TABLE > > SPECIES | ECOREGION > > Canis_lupus | NA1008 > > Canis_lupus | NA1010 > > > > ECOREGIONS TABLE > > ID | NAME | Geog | Geog2 > > NA1008 | Alaska tundra | na | na > > IM1003 | Philippine rainforest | eur | phl > > (Note that mainland ecoregions feature the > continental > > ID in each of the last two columns, while island > > ecoregions feature the island's ID in the last > > column.) > > > > GEOGRAPHY TABLE > > ID | NAME > > na | North America > > phl | Philippines > > > Maybe you can start by rewriting the problem is > pseudo queries: > "I want a list of the NAMEs from the GEOGRAPHY table > for a certain > species from the JOIN table for which the ecoregions > and the geog are > listed in the ecoregions table. Each NAME should > only appear once." > Or something like that. Well, I've already made one major change. I can see that this is going to be way too complex for me no matter what, so I split it into TWO queries. This query displays the native continents: '; } ?> For example, if the species discussed is the yak, it would display this: Eurasia The giraffe page would display this: Africa The puma: North America South America For species that are native only to continental mainlands, that's all there is to it. I don't even have to worry about them in the second query. The second query so far looks like this: '; } ?> Its purpose is to identify species that are native to islands, then display a text message depending on whether or not that animal is also native to a continent. For example, the following data tells us that we can forget about the yak, which is a purely mainland species. yak | eur | eur Philippine eagle | eur | phl tiger | eur | eur tiger | eur | bal The Philippine eagle page might display a message like this: Eurasia* *Philippines only The tiger page might display this: Eurasia* *Including Bali I haven't even plugged in my animal species table yet. I'm just using the WHERE clause to select groups of ecoregions from my table gweocoregions and experiment with them. It's easy to eliminate mainland species, because their values in the fields Geog and Geog2 are identical; eur | eur for the Eurasia yak, for example. For my second query, I would join Geog2 to the field ID101 in my geography table. So if Geog2 contains "phl", it would display "Philippines" from the geography table. If that's still too complex, I might just hand code arrays grouping each continent's islands together and somehow draw the data from them. For example... ' $Eurasia = array("Philippines", "Borneo", "Sumatra") Then my PHP script could say something like, "Display this message if any name in this array appears and there is also a mainland region - like eur | eur - but display the other message if a name in this array shows up but there's no mainland ecoregion." It's hard to even explain it, but that's a start. Thanks. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY Destroys 2nd Function
I have a PHP script that displays data like this: Eurasia Eurasiaisland Africa Where Eurasia and Africa are mainland parents of ecological regions and Eurasiaisland is a parent of an ecological system that is associated with a continent. For example, Borneo would be Eurasiaisland. The finished script will display an animal species' distribution. Obviously, I don't want to say it lives in Eurasia Eurasia. Instead, I want to group them together, so an Old World species like the leopard might look like this: Eurasia Africa ...no matter how many ecological regions it inhabits on either continent, mainland or island. The problem is that when I add GROUP BY to my command, I lose the superscripts. It appears to favor a particular row, and if that particular row represents a mainland ecoregion, then EVERYTHING is defined as mainland. My script also displays footnotes that will eventually name the islands it's native to. These, too, disappear when I use the GROUP BY command. Is there a simple solution you can think of? If not, can you think of some sort of workaround, like a separate table listing islands that I can somehow plug into the system? Normalization isn't a priority; what I'm doing is already over my head, and my primary goal is user friendly - simply coming up with something that works. Below are some simple diagrams of my tables. Thanks. ANIMALS TABLE Canis_lupus | wolf Panthera_tigris | tiger JOIN TABLE SPECIES | ECOREGION Canis_lupus | NA1008 Canis_lupus | NA1010 ECOREGIONS TABLE ID | NAME | Geog | Geog2 NA1008 | Alaska tundra | na | na IM1003 | Philippine rainforest | eur | phl (Note that mainland ecoregions feature the continental ID in each of the last two columns, while island ecoregions feature the island's ID in the last column.) GEOGRAPHY TABLE ID | NAME na | North America phl | Philippines __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scheme for Displaying Either/And/Or
OK. I thought about that earlier, but I wasn't sure if I'd be able to adapt it to this particular problem. Do you think the "automated tree traversal" method described at the link you posted... http://www.sitepoint.com/article/hierarchical-data-database/2 ...is the only one that would work? I've never been able to get that to work. But I do have a working copy of the "adjacency" model, described at http://www.sitepoint.com/article/hierarchical-data-database It sounds like they do the same thing, but one is a little more efficient than the other. Thanks. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scheme for Displaying Either/And/Or
I hope this isn't too long-winded. It's kind of hard to explain. I'm trying to figure out a scheme that will display animal distribution information. First, I want to divide animal species by continents. Next, I want to further identify species that live on islands that are associated with continents, like Japan (Continent = Eurasia, Island = Japan). Now, imagine four species with the following distribution: Eurasian bird - Mainland Eurasia, plus the British Isles and Japan Eurasian bird2 - Mainland Eurasia only Island Bird - British Isles and Japan only Japanese bird - Japan only I'm working on a content management system, so each bird will have its own page. Tentatively, I'd like the distribution for each bird to display like this: EURASIAN BIRD Eurasia* *Including British Isles and Japan EURASIAN BIRD2 Eurasia ISLAND BIRD Eurasia* British Isles & Japan only JAPANESE BIRD Eurasia* Japan In other words, I always display a species' native continent by default, followed by a more detailed description if it also inhabits an island(s) or if it lives on an island only. What's the best way to do this? I thought one way to do that might be to make two columns, one for the broadest distribution (e.g. Eurasia), the other for the specific distribution (e.g. Japan). Then I could make a separate row for each place a species inhabits, as follows: SPECIES | DIST | DIST2 Eurasian Bird | Eurasia | Eurasia Eurasian Bird | Eurasia | British Isles Eurasian Bird | Eurasia | Japan Eurasian Bird2 | Eurasia | Eurasia Island Bird | Eurasia |British Isles Island Bird | Eurasia | Japan Japanese Bird | Eurasia | Japan In the table above, every species listed would display Eurasia, from colum DIST. Then I'd like to display this additional information based on the data in the last field, DIST2: Eurasian bird: "*Including the British Isles & Japan" Island Bird: "*British Isles and Japan only" Japanese Bird: "*Japan only" I should add that I already have geographic tables that display parent-child relationships. However, they're tricky to work with because of varying number of steps between children and parents. For example, Europe's parent is Eurasia, but the Philippines have TWO parents - Asia, then Eurasia. Anyway, before I try to figure out how to display this with PHP I want to make sure I'm using a workable scheme. Any tips? Thanks. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing underscores ( _ ) in database tables
--- [EMAIL PROTECTED] wrote: > Please, please! read up on normalization. The way I > would probably handle > this design issue is by creating a separate table of > taxonomic names (I > know this is very different than my previous advice > but I have had longer > to think about it). OK, I'll have another go at it. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing underscores ( _ ) in database tables
(Sorry if this is a repeat; I think my first post didn't go through.) I'm still stuck on the problem I asked about a day or two ago. I'm working on a page at http://www.geozoo.org/stacks/ that draws data from a table that lists animal taxons (orders, families, species, etc.) in a child-parent relationship. It works exactly the way it should. Try http://www.geozoo.org/stacks/Animalia, watching the navigation links and the column on the right, for example. The problem is that the children of genera - species - are properly displayed as TWO WORDS - the child (species) and parent (genus). http://www.geozoo.org/stacks/Canis illustrates the problem I run into when I tweak my PHP so that Canis lupus is displayed instead of just lupus, for example. If you click Canis lupus or type in http://www.geozoo.org/stacks/Canis_lupus, you get a 404 Page Not Found Error. I want it to work like this page: http://animaldiversity.ummz.umich.e...anis_lupus.html Notice that the parent displays with just one variable: http://animaldiversity.ummz.umich.e...tion/Canis.html But I don't think I'm ever going to figure this out until I encounter someone who already has a similar script up and running. In the meantime, I had another idea. Suppose I create a new table field that lists the full species name, including an underscore. For example, genera and species look something like this in my current table: NAME | PARENT Canis | Canidae lupus | Canis Panthera | Felidae leo | Panthera Home | Pongidae sapiens | Homo My new table might look like this: NEWNAME | NAME | PARENT Canis | Canis | Canidae Canis_lupus | lupus | Canis Panthera | Panthera | Felidae Panthera_leo | leo | Panthera Homo | Homo | Pongidae Homo_sapiens | sapiens | Homo So instead of displaying Parent + Name (Homo sapiens) and adding an underscore, I just display NewName (Homo_sapiens). I'm just wondering if there's anything I need to know about using underscores in database tables. I assume I can manipulate the underscore with PHP and/or Apache mod_rewrite, if necessary. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
--- [EMAIL PROTECTED] wrote: - In my opinion: The easiest thing for you to do right now would be a table like ID (primary key) NAME PARENTID You don't need PARENT, because you have the PARENTID, you can always retrieve parent. Although... i still think it would have been simpler to do separate tables for each taxonomic level, and this single table design may cause problems down the road if you start adding more fields... but anyway. Keep it simple. One ID is all you need to identify any row. As far as the URL, you could use the scheme i gave earlier for the URLs (if you can absolutely guarantee 100% every NAME-PARENT combination will be unique), but it would be more complicated PHP code and more complicated queries, and links would break when spellings changed. Just use the ID in the URL, it's simple. If you add or delete rows, or change spelling, the keys stay the same, primary keys don't change. Just make sure you don't delete anything's parent and everything will be just fine. * * * * * OK, thanks. I'll give that a try. I'll probably try to combine it with the recursive array described at http://www.sitepoint.com/article/hierarchical-data-database/3 (after I learn how to do it). Let me throw one more curve ball at you, though. I now understand that I can't combine my primary key with the field Name, like 8leo. But do you know if I could combine numerals from this "tree traversal" script with Name? Here's why numerals appeal to me. If I combine fields like Name/Parent ID - e.g. leo/Pan, then it will be more difficult when I want to separate them. If I combine Name with a numeral - e.g. leo/8 - then all I need is a script that weeds out all numerals. I think. Does this make sense? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
--- [EMAIL PROTECTED] wrote: > You have confused front end representation with > back-end data design. How > you store your data and create your data > relationships is only marginally > related to what your code makes it look like when it > presents your data > for the user. Why just create longer unique path > names that represent the > actual taxonomic path you have to take to get to an > animal? What's wrong > with: > > http://geozoo/stacks/mamalia/carnivora/canidae/canis/leo/ > http://geozoo/stacks/mamalia/carnivora/felidae/panthera/leo/ > > Those are unique and they accurately model your > heirarchy. It's a good > learning tool too as it documents the actual lineage > of an animal in its > URL. Each level of the path could resolve to > something distinctive about > each taxonomic group > > http://geozoo/stacks/mamalia/carnivora/canidae/ > would take you to a page > about the dog family > http://geozoo/stacks/mamalia/carnivora/ would take > you to a page about the > order of carnivores > ... and so on > > You don't have to worry about reorganziation of your > taxonomic tree > breaking your links (like you were when you wanted > to link based on ID). > You can add and delete nodes in your taxonomy at > will (a deleted node > could show some default "sorry, no profile exists > yet. This site is still > under construction" - type message). > > What's wrong with returning two responses if someone > searched on the > species "leo". What if they didn't know about Canis > leo? That could be a > cool surprise. Let the user figure out which one > they really wanted to > see. That means that with your existing tree, you > have to search the tree > for leo and if there is only one match you show the > matching page, > otherwise you have to present a page that shows them > the options > available. Thanks for the tips. Actually, I have several database-driven websites that are organized just as you describe. However, I'm trying to turn this site into a content management system, with all the articles displayed on the same page. In fact, it will be similar to the Animal Diversity Website, which displays wolf and Canidae at the following URL's: http://animaldiversity.ummz.umich.edu/site/accounts/information/Canis_lupus.html http://animaldiversity.ummz.umich.edu/site/accounts/information/Canidae.html As you can see, they don't just use lupus for wolf; they pair it with its genus name, Canis_lupus. That's what I want to do eventually, but there are so many competing things to figure out - PHP, mod_rewrite, MySQL, spaces between words, terms from two columns, etc. I appreciate what you said about the educational value of a bread crumbs-type URL. However, I have that sequence represented in the links at the top of my page. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
--- Peter Brawley <[EMAIL PROTECTED]> wrote: "As you note, the names [of animal taxons] aren't guaranteed to be unique, or to stay the same . . . > One way out is to give every table an > auto-incrementing integer PK, and > use those keys, which will never change, to mark > parent-child relationships. I wanted to follow up on this. I can easily substitute integers from my primary key for names, but how do I substitute them for parents? For example: ID | NAME | PARENT 10 | Canidae | Carnivora 11 | Canis | Canidae 12 | Vulpes |Canidae I can easily replace Canis with 11, Vulpes with 12. But they both have the same family - Canidae, which translates as 10. I could create a new field and manually, like this: ID | NAME | PARENT | PARENTID 10 | Canidae | Carnivora | 9 11 | Canis | Canidae | 10 12 | Vulpes |Canidae | 10 But if I add or delete a row, the numerals in my primary key will change, messing up the values in PARENTID. Along similar lines, I have another question... Consider the database table code below, which displays animal names (representing all taxonomic heirarchies) in a child-parent relationship: ID | NAME | PARENT 1 | Mammalia | (NULL) 2 | Carnivora | Mammalia 3 | Canidae | Carnivora 4 | Canis | Canidae 5 | leo | Canis 6 | Felidae | Carnivora 7 | Panthera | Felidae 8 | leo | Panthera Rows 5 and 8 represent identical species names, leo. If I type http://geozoo/stacks/leo/ into my browser, it defaults to Mammalia > Carnivora > Canidae > Canis > leo, rather than the lion, Mammalia > Carnivora > Felidae > Panthera > leo So I need a way to distinguish one leo from the other. Would it be possible to somehow combine my auto-incrementing primary key with the field Name, converting leo / leo to 5leo / 8leo? There are two things I'd have to deal with... 1. I'd need to weed the numerals out of the display, which should look like this... http://geozoo/stacks/leo/";>leo not this... http://geozoo/stacks/8leo/";>8leo 2. The numerals would have to be fluid, as I will be adding and deleting rows. Thus, the lion could be 8leo one day and 9leo the next. I can take this to a PHP forum to learn how to implement it. But I thought someone on this forum might tell me if it can be done in the first place. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
--- Peter Brawley <[EMAIL PROTECTED]> wrote: > David, > > >1. Some species names are shared by more than one > >mammal. For example, there's a marsupial named > >Antechinus leo. > > >2. Species are more properly cited with the name > of > >their parent (genus), so I eventually want my > species > >URL's to look like this: > > > > As you note, the names aren't guaranteed to be > unique, or to stay the > same either, therefore they won't do as primary > keys, therefore they > won't do as foreign keys. Also our understanding of > these taxonomic > relationships can change even when the names don't, > and it's considered > a design error to make it necessary to edit primary > keys in order to > update database tables. > > One way out is to give every table an > auto-incrementing integer PK, and > use those keys, which will never change, to mark > parent-child relationships. I already have an auto-incrementing integer PK, but I haven't used it in my queries, as I thought I'd need actual names to work with my scripts. But now that you mention it, I'm probably mistaken, so I'll give it a try. Thanks. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
I'll give that a try. Thanks. --- [EMAIL PROTECTED] wrote: > > > David Blomstrom wrote: > > >-- [EMAIL PROTECTED] wrote: > > > > > > > >>Can > >>you just concatenate > >>the two strings together after you get them from > the > >>database? e.g. > >>$parent.$name? And, just split() or explode() the > >>string when you get it from the URL? > >> > >> > > > >Consider the following URL's: > > > >1. stacks/leo > > > >2. stacks/Panthera_leo > > > >The first is "recognized" by my database and > fetches > >information. The second doesn't work. If I > concatenate > >then split the link as you suggest, will it produce > a > >link that looks like #2 yet is recognized by my > >database? > > > >If so, I'll learn how to do those functions. > > > > > > > Yes. > > The problem here is a PHP one, not an SQL one.. > MySql doesn't know > anything about your links, that's not it's job, but > it does know about > fields. It's PHP's job to know about links and make > MySql "recognize" > the links by formatting them in an appropriate way > in the SQL. > > in the taxonomic names, replace spaces with '-' > (look that up... it's > one of the PHP string functions) By the way, I hope > the names don't > have any ' or , in them, if so, you'll need to do > more replacing. > Search for replace on the php site. > > Then, concatenate like this: > $parent.'_'.$name > > Add that to the end of your URL. > > then when you get the URL parameter 'taxon', > explode() it on '_', then > replace all - with spaces in each of the two values > you get. > > > > > >Yes, there will definitely be spaces between words. > I > >haven't yet decided whether I'll replace them with > -, > >_ or . (period). However, I'm leaning towards > >underscores ( _ ) for this particular page and > hyphens > >( - ) on a topics page. > > > >T > > > > don't use periods. those have other meanings in PHP, > and it can get to > be confusing. > > -- > http://www.douglassdavis.com > > __ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Treating Two Fields Like One
-- [EMAIL PROTECTED] wrote: > Can > you just concatenate > the two strings together after you get them from the > database? e.g. > $parent.$name? And, just split() or explode() the > string when you get it from the URL? Consider the following URL's: 1. stacks/leo 2. stacks/Panthera_leo The first is "recognized" by my database and fetches information. The second doesn't work. If I concatenate then split the link as you suggest, will it produce a link that looks like #2 yet is recognized by my database? If so, I'll learn how to do those functions. > Can there be spaces in these names? If so, maybe > use a dash for space, > that way, there's no chance of ambiguity. Get very > familiar with the > PHP string functions too. Yes, there will definitely be spaces between words. I haven't yet decided whether I'll replace them with -, _ or . (period). However, I'm leaning towards underscores ( _ ) for this particular page and hyphens ( - ) on a topics page. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Treating Two Fields Like One
I'm trying to make my first content management system and am wrestling with a problem that seems to be about equal parts PHP, Apache mod_rewrite and MySQL. I wondered if anyone on this list can suggest a MySQL solution - or partial solution. I'm dealing with a single database table named gzanimals that lists animal taxons (orders, families, species, etc.) in a child-parent relationship (with fields named "Name" and "Parent". For example: NAME | PARENT Animalia | (NULL) Mammalia | Animalia Carnivora | Mammalia Felidae | Carnivora Panthera | Felidae leo | Panthera The code above illustrates the taxonomic hierarchy linking the lion (Panthera leo) to the animal kingdom (Animalia). With Apache mod_rewrite, my URL's look like this: www.geozoo.org/stacks/Animalia www.geozoo.org/stacks/Mammalia www.geozoo.org/stacks/Carnivora www.geozoo.org/stacks/Felidae www.geozoo.org/stacks/Panthera www.geozoo.org/stacks/leo There are two problems with this strategy: 1. Some species names are shared by more than one mammal. For example, there's a marsupial named Antechinus leo. 2. Species are more properly cited with the name of their parent (genus), so I eventually want my species URL's to look like this: www.geozoo.org/stacks/Panthera_leo * * * * * I can manipulate my PHP script and mod_rewrite to draw from two fields and display Panthera_leo instead of leo. The problem is that I more or less lose contact with my database; it recognizes leo as a row identifier, while Panthera_leo is an unknown. * * * * * You can see a good example of what I'm trying to do at http://www.geozoo.org/stacks/Carnivora Two separate scripts drive the bread crumbs-style links at the top of the page and the list of "children" (carnivore families, in this case) in the column on the right. If you click Felidae, the children change to a list of genera that belong to the family Felidae. Click Panthera - http://www.geozoo.org/stacks/Panthera - and the children change to species that belong to the genus Panthera. As you can see, I've modified these so they display the genus (parent) + species (child) name, both physically and in the link. However, they are nonfunctioning, as I haven't yet figured out how to to deal with the space between the two values. * * * * * At this point, I'm thoroughly confused, especially since fixes often require tweakingn two or more things simultaneously - PHP, mod_rewrite and MySQL. But one thing I haven't experimented with yet is combined fields. I created a key on two fields - Name and Parent - and it NameDual. Is there a way to connect with NameDual in a PHP script, or is it something that only exists in my database? For example, I thought I might change... $taxon = mysql_query ("SELECT Name, NameCommon, Parent FROM gzanimals AS GZA WHERE Parent = '$_GET[taxon]'"); to... $taxon = mysql_query ("SELECT NameDual, Name, NameCommon, Parent FROM gzanimals AS GZA WHERE Parent = '$_GET[taxon]'"); ...except it didn't work. Is there a way to do this, or can you see any other MySQL solutions to my problem? Thanks. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Child-Parent Relationships with 2 Parents
I just thought of a possible solution. Instead of listing all the phyla, orders, classes, families, genera and species in one big table, create separate tables for each taxonomic level. Each taxon would then have two parents. the genus Sus' (pigs) parents would be both the subfamily Suinae and family Suidae. But the genus Panthera doesn't belong to a subfamily, so its family (Felidae) would be listed as both Parent and Parent2. NAME | PARENT | PARENT2 Sus | Suinae | Suidae Panthera | Felidae | Felidae Then I could write PHP scripts focusing on either Parent or Parent2, depending on what I want to do. Does this sound sensible? Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Child-Parent Relationships with 2 Parents
Suppose I want to create an animal kingdom database that gives me the option of displaying the following taxonomic hierarchies: 1) A bread crumbs navigation string, including ALL taxons; e.g. Animalia (kingdom) > Chordata (phylum) > Vertebrata (a SUBphylum) > Mammalia (class) > Eutheria (a SUBclass) 2) A bread crumbs nav string that excludes taxons beginning with SUB; e.g. Animalia > Chordata > Mammalia 3) Lists of various taxons' children, including ALL taxons; e.g. a. CHORDATA (phylum) b. Vertebrata (subphylum) b. Urochordata (subphylum) 4) Lists of taxons' children, excluding SUBorders, SUBfamilies, etc.; e.g. a. CHORDATA (phylum) b. Mammalia (class) b. Aves (class) b. Reptilia (class), etc. 5. A combination that looks like this: a. CHORDATA (phylum) b. VERTEBRATA c. Mammalia c. Aves c. Reptilia c. Amphibia b. UROCHORDATA And if you clicked on Vertebrata, you'd see only its children, like this: Mammalia Aves Reptilia Amphibia What's the best way to organize a database to achieve this flexibility? My current child-parent relationship generally works, but I think I erred in treating SUBtaxons a little differently, assigning them parents but not children. For example, the data below illustrates how I assigned the phylum Chordata as the parent of both the subphylum Vertebrata and the class Mammalia. Chordata | Animalia Vertebrata | Chordata Mammalia | Chordata So clicking Chordata displays... Vertebrata Mammalia ...and clicking Mammalia displays mammal orders, but clicking Vertebrata displays nothing. Do you think I'll be able to write PHP scripts that do what I want, or does it make more sense to reorganize my database, assigning parents and children to every taxon, then writing PHP scripts that somehow weed out SUBtaxons when I don't want to display them? A third option is to use a recursive array, something I haven't learned how to do yet. If I try a recursive array, I assume I'd have to modify my database so that every taxon does indeed have a parent and a child. If it helps, I posted the code for the two PHP scripts I'm using to make my bread crumbs navigation and to display the children of various taxons. Eventually, I'll have to write a third script that will join tables with additional information on distribution, diet, etc. But I think what I really need now is confirmation that I should assign EVERY taxon a parent and child (or advice to the contrary) and whether I'd be better off sticking with a child-parent relationship or a recursive array. Thanks. [PHP] // http://www.sitepoint.com/article/hierarchical-data-database/2 function display_children($parent, $level) { $result = mysql_query('SELECT Name FROM gzanimals as A WHERE Parent="' . $parent . '";'); while ($row = mysql_fetch_array($result)) { echo str_repeat(' ',$level).$row['Name']."\n"; display_children($row['Name'], $level+1); } } function get_path($node) { $result = mysql_query('SELECT Parent FROM gzanimals '. 'WHERE Name="'.$node.'";'); $row = mysql_fetch_array($result); $path = array(); if ($row['Parent']!='') { $path[] = $row['Parent']; $path = array_merge(get_path($row['Parent']), $path); } return $path; } $mypath = get_path($mycode); for($i=0;$i ".$mypath[$i]." > "; } [/PHP] * * * * * * * * * * This is the script I'm using to display children of various taxons: [PHP] 0)) { } else { die('Invalid query: ' . mysql_error()); } { $taxon = mysql_query ("SELECT Name, Parent FROM gzanimals AS A WHERE Parent = '$_GET[taxon]'"); echo ''; echo ''; // // while ($row = mysql_fetch_array ($taxon)) { while ($row = mysql_fetch_array($taxon, MYSQL_ASSOC)) { // mysql_fetch_array($taxons, MYSQL_ASSOC) // {$row["Name"]} echo << {$row["Name"]} EOD; } } echo ''; ?> [/PHP] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating new username & password
OK, I think that worked. I didn't get any error messages at least. :) Thanks. --- Eugene Kosov <[EMAIL PROTECTED]> wrote: > David Blomstrom wrote: > > What's the easiest way to create a username and > > password for a new database? It's been so long > since I > > created my original database, I forgot how. > > You can do it with a query like this: > > GRANT USAGE ON database_name.* TO [EMAIL PROTECTED] > IDENTIFIED BY 'password'; > > Replace USAGE in query above with priveleges set you > want for your new account. > > See http://dev.mysql.com/doc/mysql/en/grant.html for > more info. > > > Regards, > Eugene Kosov > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL vs Plone/Zope/Python
I've been experimenting with databases, both Drupal and Plone. Plone is a can of worms, but it's intriguing, partly because it's so different. It sounds like they replace LAMP with something completely different, though it's a complete mystery to me at present. I wondered if anyone on this list has had experience with Plone and could explain how their system compares to PHP/MySQL. I'll be working with animal kingdom data - child-parent relationships and recursive arrays. I don't want to abandon PHP and MySQL for something totally new after I've put so much time and effort into them. But it might be worthwhile for this particular site if Plone offers some major advantage. It's also my understanding that PHP and MySQL can still be used with Plone, though not be default. So, do you know if Plone offers any significant advantages over MySQL, or is it just comparing apples and oranges? Thanks. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating new username & password
What's the easiest way to create a username and password for a new database? It's been so long since I created my original database, I forgot how. When I created my new database with phpMyAdmin, I scanned the page looking for anything mentioning username or password but I couldn't find anything. I remember encountering this problem before. It seems only logical to prompt users for a username and password after they've created a database. Or am I missing something? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs XML
Wow, this is turning into quite a research project. Thanks for the tip about ontologies; it doesn't make much sense to me yet, but I'll take a closer look at the article. In the meantime, I'm thinking of using a content management system called Plone. Unfortunately, I've so far been unable to install it, apparently because my computer has some kind of memory problem. When I get everything sorted out, I'll have to learn about recursive arrays, Plone, Zope, Python, XML and ontologies. Whew!!! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs XML
Thanks for all the tips. That makes it much clearer. I think I'll stick with PHP and MySQL and gradually introduce a little XML if it fits in. I just downloaded a content management system called Plone, which is supposed to be a good choice for hierarchical databases. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL vs XML
I've been gathering data for an animal kingdom database for quite some time and am now trying to figure out how to organize and display it. So far, I have a table that lists every order, suborder, family, subfamily, genus and species of mammal in a child-parent relationship, like this: NAME | PARENT Carnivora | Mammalia Canidae | Carnivora Canis | Canidae lupus (the wolf) | Canis I also broke that table into separate tables listing only orders, families, genera, species, etc., which I can then display via joins. I haven't yet figured out which methid is going to work best. I think I'd like to make a content management system, possibly modeled after Wikipedia, though I'm also looking at the Tree of Life website at http://tolweb.org/tree/phylogeny.html They use a recursive array technique called Edge Representation, which is discussed about halfway down this page: http://www.phyloinformatics.org/pdf/7.pdf Another possible guide is the Animal Diversity Web - http://animaldiversity.ummz.umich.edu/site/about/technology/index.html - which uses something called Mousetrap and TaxonDB. This is all new and very confusing to me. Making things even more confusing, I read that XML can be used in lieu of databases, and at least one reference seems to suggest that it's the superior choice. So, before I get in any deeper, I'd like to ask about the differences between XML and MySQL. What are the pros and cons, and which would be better for an animal kingdom database? Or could I use both at the same time? I'm new to XML, too, but it looks like it might not be too complex. But it's hard to envision how this all fits together. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative Numeric Values
--- Kim Briggs <[EMAIL PROTECTED]> wrote: > David, > > In reading through miscellaneous database design > text on the web, I > read just the other day that you should not try to > include meaningful > data in your key values. I assume there will be > some kind of "lookup" > tables for species, phylum, whatever. Trying to > make your key field > "smart" seems like way too much overhead and > complexity. I'm > wondering why, if the database is enormous, are you > being so short and > cryptic with the "user-friendly" values? Primarily because I want to make it easier to work with. If I create a new page that focuses on the king salmon, I'd rather type in $MyID = 'onc'; than $MyID = 'Oncorhynchus'. Or if I create an array, I'd rather list rhi, hip, equ than Rhinocerotidae, Hippopotamidae, Equidae. In fact, I'll have to discard big chunks of the animals database I received on a CD, as it's way to big (several MB) and includes living things I won't cover (bacteria, viruses, etc.). I'm also trying to decide on my URL structure. I could follow tradition and map out the lion like this: mammals.geobop.org/carnivora/felidae/panthera/leo/ ...but I'm thinking of shortening the URL's: mammals.geobop.org/car/fel/pan/leo/ I'm just trying to come up with something that's more user friendly. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relative Numeric Values
I think my question is more oriented towards PHP, but I'd like to ask it on this list, as I suspect the solution may involve MySQL. I'm about to start developing an enormous database focusing on the animal kingdom and want to find a key system more user friendly than the traditional scientific name. So imagine instead a page with the following in the head section: $AnimalID = 'canlup'; This page displays information on the wolf, based on the first three letters of its genus and species name, Canis lupus. Now imagine a page with this value: $AnimalID = 'bal'; This page displays information on the whale family Balaenidae. But what about the whale family Balaenopteridae, which begins with the same three letters? I could solve this problem by adding a numerical key to my database and displaying the following: $AnimalID = 'bal23'; $AnimalID = 'bal24'; The problem with this is that it makes it much harder to work with my data. When tweaking a page or writing a script, I can easily remember that bal = Balaenidae, but I can't possibly remember which numeral is associated with each mammal family. Also, what happens if I add or subtract rows from my database table, and the above values suddenly change to bal27 and bal28? So here's what I think I'd like to do: $AnimalID = 'canlup1'; $AnimalID = 'bal1'; $AnimalID = 'bal2'; The page with canlup1 will display the FIRST (and only) instance of canlup in the database - the wolf. The page with bal1 will display the first instance of bal, which will always be Balaenidae, whether the absolute value is bal27 or bal2884. A page with bal2 will always display the next mammal family that begins with bal, Balaenopteridae. So I THINK all I need to do is create a variable that reflects a particular value's ordinal position in a database... abc1 abc2 abc3, etc. Plus, I'll have to join two or three fields together to form a key; e.g. animals.species + animals.numerals Does anyone know how I can do this? Thanks. __ Do you Yahoo!? Plan great trips with Yahoo! Travel: Now over 17,000 guides! http://travel.yahoo.com/p-travelguide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help with 813-MDB File
Thanks for all the tips. I was able to import it with Navicat. In fact, I was amazed at how smoothly it went. I think it imported about 30 separate tables - nearly 4 million rows - before Navicat froze. But I was working on some other programs and probably ran out of memory. It recorded over 8,000 errors - duplicate keys, etc. - but most of those seemed to be associated with viruses (REAL viruses, not computer viruses), which I'm not concerned about. Thanks again. --- "Berman, Mikhail" <[EMAIL PROTECTED]> wrote: > Is there a description of tables anywhere on CD-ROM > for Access database. > > Mikhail > > -Original Message- > From: Rhino [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 30, 2005 9:10 AM > To: David Blomstrom; mysql@lists.mysql.com > Subject: Re: Need Help with 813-MDB File > > > - Original Message - > From: "David Blomstrom" <[EMAIL PROTECTED]> > To: > Sent: Tuesday, March 29, 2005 11:29 PM > Subject: Need Help with 813-MDB File > > > > I acquired a CD-ROM that lists many thousands of > > animal species. The main file is a 813-MB MDB > file. > > I'm not sure if it's a spreadsheet or database, > but > > it's apparently designed to work with Microsoft > > Access, which I THINK is a spreadsheet. (I don't > have > > it.) > > > Access is *not* a spreadsheet, it's a database. I > don't have it either > but > I've used it. > > > I have just enough memory to open the file in > WordPad, > > but it doesn't do any good because much of the > data > > consists of unintelligible characters. > > > > Anyway, I need to figure out a way to import this > > monster into MySQL. Are you aware of any freeware > > programs that can open up files designed for > Access? > > Is there a way to convert a MDB file directly into > a > > csv file, which could then be imported into MySQL? > > > Apparently, this is simple to do if you have Access; > I believe you can > just > do an Export to CSV (or other formats) within > Access. > > If you don't have Access, it could get somewhat more > complex depending > on > what tools you have. I was curious about your > question so I did a Google > newsgroup search using these three terms: > export MDB CSV > and got lots of hits. Some of them talked about ways > to read MDB files > without having Access, with Perl for example. So I > would suggest that > you do > a similar search, perhaps adding in some additional > keywords that > reflect > the tools/languages you do have, to find out which > ways are available to > you. > > > I'll probably eventually break it into sections. > At > > the very least, I'll probably divide it between > > vertebrates (which I'll use the most) and > > invertebrates. > > > I have no idea if that can be done without Access, > let alone how to do > it. > > Rhino > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.8.3 - Release > Date: 25/03/2005 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help with 813-MDB File
Thanks for both your tips. I discovered by chance that Navicat (which I have) will do the conversion - very easily. Whether or not it will be a success is hard to say; it's loaded nearly 3 million rows so far, with over 8,000 errors recorded. But I'm going to download DB Tools, as I have frequent need for data conversion tools. Thanks. --- "J.R. Bullington" <[EMAIL PROTECTED]> wrote: > DB Tools software will convert the file for you. You > can download it at > http://dbtools.com.br/EN/index.php. All you have to > do is download and > install the FreeWare version and then use the TOOLS > > DAO Import Wizard. > > J.R. > > -Original Message- > From: David Blomstrom > [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 29, 2005 11:29 PM > To: mysql@lists.mysql.com > Subject: Need Help with 813-MDB File > > I acquired a CD-ROM that lists many thousands of > animal species. The main > file is a 813-MB MDB file. > I'm not sure if it's a spreadsheet or database, but > it's apparently designed > to work with Microsoft Access, which I THINK is a > spreadsheet. (I don't have > it.) > > I have just enough memory to open the file in > WordPad, but it doesn't do any > good because much of the data consists of > unintelligible characters. > > Anyway, I need to figure out a way to import this > monster into MySQL. Are > you aware of any freeware programs that can open up > files designed for > Access? > Is there a way to convert a MDB file directly into a > csv file, which could > then be imported into MySQL? > > I'll probably eventually break it into sections. At > the very least, I'll > probably divide it between vertebrates (which I'll > use the most) and > invertebrates. > > Thanks. > > > > __ > Do you Yahoo!? > Yahoo! Small Business - Try our new resources site! > http://smallbusiness.yahoo.com/resources/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help with 813-MDB File
I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Create Table Error
Please ignore this thread. I think my main problem is I need glasses! --- David Blomstrom <[EMAIL PROTECTED]> wrote: > Can someone tell me what I'm doing wrong? When I > first > tried to create this table, I got an error message > pointing out two fields that looked perfectly fine > to > me. I couldn't figure it out, so I just deleted the > fields - but it then fingered two different fields. > I > deleted them, and now it says there's a problem with > two other fields. > > I don't get it. Thanks. > > CREATE TABLE `geog` ( > `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , > `IDArea` CHAR( 3 ) NOT NULL , > `Name` VARCHAR( 50 ) NOT NULL , > `coordNS` VARCHAR( 7 ) DEFAULT NULL , > `coordNS2` VARCHAR( 3 ) DEFAULT NULL , > `coordEW` VARCHAR( 8 ) DEFAULT NULL , > `coordEW2` VARCHAR( 3 ) DEFAULT NULL , > `coordnotes` TEXT DEFAULT NULL , > `location` VARCHAR( 255 ) DEFAULT NULL , > `mapref` VARCHAR( 33 ) DEFAULT NULL , > `areak` DECIMAL( 9, 2 ) DEFAULT NULL , > `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , > `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , > `areanotes` VARCHAR( 255 ) DEFAULT NULL , > `aracomp` VARCHAR( 100 ) DEFAULT NULL , > `borderk` INT( 6, 1 ) DEFAULT NULL , > `borderstates` VARCHAR( 255 ) DEFAULT NULL , > `bordernotes` TEXT DEFAULT NULL , > `coastk` DECIMAL( 6.1 ) DEFAULT NULL , > `coastnotes` TEXT DEFAULT NULL , > `martersea` INT( 3 ) DEFAULT NULL , > `marconzone` INT( 2 ) DEFAULT NULL , > `marconshelf` VARCHAR( 255 ) DEFAULT NULL , > `marexecon` INT( 3 ) DEFAULT NULL , > `marexfish` INT( 3 ) DEFAULT NULL , > `marnotes` VARCHAR( 255 ) DEFAULT NULL , > `climate` VARCHAR( 255 ) DEFAULT NULL , > `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , > `elevhighm` INT( 4 ) DEFAULT NULL , > `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , > `elevlowm` INT( 4 ) DEFAULT NULL , > `elevnotes` VARCHAR( 255 ) DEFAULT NULL , > `terrain` TEXT DEFAULT NULL , > `natres` TEXT DEFAULT NULL , > `nathaz` TEXT DEFAULT NULL , > `envagree` TEXT DEFAULT NULL , > `envagree2` VARCHAR( 255 ) DEFAULT NULL , > `envissues` TEXT DEFAULT NULL , > `landuse` INT( 2, 2 ) DEFAULT NULL , > `landusecrops` INT( 2, 2 ) DEFAULT NULL , > `landuseother` INT( 2, 2 ) DEFAULT NULL , > `landusenotes` VARCHAR( 100 ) DEFAULT NULL , > `irrigate` INT( 6, 2 ) DEFAULT NULL , > `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , > `geognotes` TEXT DEFAULT NULL , > PRIMARY KEY ( `NID` ) , > INDEX ( `IDArea` ) > ) > > MySQL said: Documentation > #1064 - You have an error in your SQL syntax; check > the manual that corresponds to your MySQL server > version for the right syntax to use near ' 1 ) > DEFAULT > NULL , > `borderstates` VARCHAR( 255 ) DEFAULT NULL , > `bordernotes`' at line 17 > > > > __ > Do you Yahoo!? > Yahoo! Small Business - Try our new resources site! > http://smallbusiness.yahoo.com/resources/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Create Table Error
Can someone tell me what I'm doing wrong? When I first tried to create this table, I got an error message pointing out two fields that looked perfectly fine to me. I couldn't figure it out, so I just deleted the fields - but it then fingered two different fields. I deleted them, and now it says there's a problem with two other fields. I don't get it. Thanks. CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes`' at line 17 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recovering Lost Database
--- Dan Nelson <[EMAIL PROTECTED]> wrote: > In the last episode (Mar 25), David Blomstrom said: > > If you accidentally drop a database instead of a > database TABLE in > > phpMyAdmin, is there any way to recover the lost > database, or is it > > gone forever? > > Only if you have a backup... Thanks. > > -- > Dan Nelson > [EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recovering Lost Database
If you accidentally drop a database instead of a database TABLE in phpMyAdmin, is there any way to recover the lost database, or is it gone forever? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 MySQL Errors
I can't figure out why I get an error message relating to fiels "borderstates" and "bordernotes" when I try to create a new table. One is VARCHAR, the other TEXT, and both are NULL, like almost every field on the table. Can someone tell me what I'm doing wrong? Thanks. SQL-query: CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1) DEFAULT NULL, `borderstates` VARCHAR(255) DEFAULT NULL, `bordernotes` TEXT DE' at line 1 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I get rid of this field???
I was trying to imnport a csv file into an online databse table, but I kept getting error messages alluding to a "child row," foreign key, etc. So I decided to delete both keys (primary and index), then import the csv file. I finally managed to delete the primary key, but I can't zap the index. When I try to delete the field itself, I get this error message: #1025 - Error on rename of './geoblue_gypsy/#sql-bb1_1e763' to './geoblue_gypsy/counties' (errno: 150) When I try to delete the primary key, I get this message: #1025 - Error on rename of './geoblue_gypsy/#sql-bb1_1e782' to './geoblue_gypsy/counties' (errno: 150) So how do I get rid of it? Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
--- Brent Baisley <[EMAIL PROTECTED]> wrote: There are advantages to one large > table over many > smaller tables. A large table will take advantage of > query caching > better (if you have query cache enabled). Lots of > smaller table would > probably cause the query cache to get flushed more > often. OK, thanks. I don't foresee my database growing to over 200 tables in the near future. How do I determine if I have query cache enabled? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sharing a Database Between Websites
I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ghost Table
Please ignore this thread; I suddenly stumbled over the solution, even if I can't explain it. :) __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ghost Table
I've spent the last few days modifying my website, and when I published my files online, one of my tables was knocked out of commission. It was really bizarre - everything between the tags disappeared online. In other words, I didn't just lose everything related to PHP/MySQL, I couldn't even see or in the source code. I thought it was a HTML validation error or a problem with one of my PHP scripts, but I haven't found any smoking gun. Then I pasted a database table from another page onto the problem page, and it works just fine. So copied the good table, then replaced the table and field names with names from the problem table - and it doesn't work. So I suspect there's either something really weird going on with this particular table - "counties" - or there's some sort of conflict between MySQL verions. My host hasn't upgraded to the latest MySQL. However, that hasn't been a problem for me before, except that I have to create tables independently due to that collation stuff. I put both tables online at http://www.geoworld.org/na/usa/az/counties2/ ...and I appended the source code below. (Notice that I included error_reporting(E_ALL);) Again, both tables work fine locally, but I'm now getting undefined index errors online and wonder if the latest MySQL version requires a different syntax in my script. Any tips? Thanks. '; error_reporting(E_ALL); ?> 0)) { // continue here with the code that starts //$res = mysql_query ("SELECT * FROM type. } else { die('Invalid query: ' . mysql_error()); } { $res = mysql_query ("SELECT * FROM weatherna WHERE weatherna.IDArea = '$mycode'") or die (mysql_error()); echo ' JanFebMarApr'; // $rowcounter=0; while ($row = mysql_fetch_array ($res)) { $c=$colors[$rowcounter++%$size]; echo "<". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] ."> ". $row['JanHot'] ."°". $row['JanCold'] ."°". $row['FebHot'] ."°". $row['FebCold'] ."°". $row['MarHot'] ."°". $row['MarCold'] ."°". $row['AprHot'] ."°". $row['AprCold'] ."°\n"; } } ?> Cool Table 0)) { // continue here with the code that starts //$res = mysql_query ("SELECT * FROM type. } else { die('Invalid query: ' . mysql_error()); } { $res = mysql_query ("SELECT * FROM counties WHERE counties.seat = 'Lafayette'") or die (mysql_error()); echo ' SeatArea'; // $rowcounter=0; while ($row = mysql_fetch_array ($res)) { $c=$colors[$rowcounter++%$size]; echo 'XXX'; echo mysql_num_rows($res); echo " ". $row['seat'] ."". $row['area'] ."\n"; } } ?> __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Decimal Puzzle
I apologize if this is a double post, but it appears that my first message didn't transmit for some reason. Anyway, I'm having a weird problem with decimals. Imagine a field with just two numerals - 450 and 24.8. You want to display them as 450 and 24.8, not 450.05 and 24.8 or 450 and 3. I designated a field Decimal 3,1. When I put a PHP query on my page, it displayed 450 as 450, but it rounded the fractions off, turning 24.8 into 25. So I went back to my spreadsheet and changed every cell to TEXT before exporting it into a new CSV file. I imported it into MySQL but got the same results. Next, I changed the field to Char 3, emptied the table and re-imported the CSV file. To my amazement, I get the same results. Yet when I browsed my table in phpMyAdmin, I saw the data displayed as 450 and 24.8, just like I wanted. So I can't figure out why it doesn't display like that. Actually, I'd like to change it just a bit. This table focuses on geological time. Rather than translate everything into a common unit (millions, billions, etc.), I decided to use both millions and billions and distinguish them by font color. For example, a yellow 2.5 would represent 2.5 BILLION, while a white 340 would represent 340 MILLION. The only remaining problem is the end of the Ice Age, 10,000 years ago. That would equal .01 million years. Still, I ought to be able to handle this with a Char or Varchar, shouldn't I? This is the script I'm using to display it, where YearBegan and YearEnded equal the beginning and ending dates of various geological periods: echo ' '; // while ($row = mysql_fetch_array ($res)) { // and add this in your while loop: $YearBegan_arr[] = $row['YearBegan']; echo " ". $row['NameTime'] ." ". $row['TimeType'] ." " . number_format($row['YearBegan']) . "-" . number_format($row['YearEnded']) . " \n"; $nameTypes[]=$row['NameTime']; } } ?> Thanks for any insights. __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple Decimal Question
Imagine a field with just two numerals, 250 and 2.8. How would I designate that field if I want to display them? First, I tried decimal 3,1. It displayed numerals like 250 perfectly, but it rounded all the decimals off, turning 2.8 into 3. So I converted the field to char 3 and made sure the cells in my spreadsheet were TEXT before saving it as a csv file and importing it. But the results are the same. .5 displays as 1, 2.8 as 3, etc. What am I doing wrong? However, I went into phpMyAdmin, and the numerals look correct - .5, 2.8, etc. So why are they being rounded when displayed? Thanks. __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Geologic Time
Peter Brawley wrote, "The earliest possible MySQL date is around 1000CE, so you could not store geologic dates in MySQL date cols. "Million years before present" is the geologic time unit that would most likely cohere with other geo databases, isn't it? Then the Cambrian would show up around 580 mya, the beginning of recorded human history around .005 mya, &c. A float, double or decimal col would handle such values." Yes, I was thinking of using decimals, especially if I can out a way to transform them into other numbers. For example, I might want to display "100,000 years" rather than .1 mya in some instances. --- Gary Richardson <[EMAIL PROTECTED]> wrote: > If you want to represent 290 million years as an > integer (290,000,000): > > - An UNSIGNED INT can store 4,294,967,295 > - A UNSIGNED BIGINT can store > 18,446,744,073,709,551,615 > > In your schema, I'd use a start_period and > end_period instead of a > varchar. It's easier to sort and do math on. > > You could factor out 1,000,000 from your dates and > use a float to > represent the numbers. 290.00 could represent > 290 mya, while 0.01 > represents 10,000 years ago. Just make sure there is > enough precision > on your float. I haven't worked with floats yet, so I don't understand what you mean, but I'll look into it. I could also create two columns - one with decimals based on a billion (e.g. 1 billion = 1, while 100,000 = .1) and the other pegged to million (e.g. 1 million = 1, and 1 billion = 1,000). Fortunately, there aren't many geologic periods, epochs, etc. to work with, so this shouldn't be too hard. Thanks for all the tips! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Geologic Time
I'm working on a geologic time database and want to ask a question about geologic time. Can/should you apply MySQL's date function to geologic time? In other words, if I create a field for the number of years ago a certain geologic period began or ended - say 260 million years ago - could I designate that field "Date"? I'll probably just designate a varchar field and enter data like this: 345-250 Then I can add "million years ago" or "mya" in my PHP script. Some scientists also use the term BP ("Before Present," I think). Another thing I have to deal with is units of thousands. For example, the Pleistocene Epoch (Ice Age) ended about 10,000 years ago. I just wondered if anyone had any suggestions for dealing with geologic time. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Are my databases dragging down my page?
Oops, I guess I added wrong. Nevertheless, unrestricted linking to database tables with no keys would increase loading time, right? Is there some way to gauge the effect, other than tweaking all my scripts and tables and checking the load time again? Thanks. --- Jay Blanchard <[EMAIL PROTECTED]> wrote: > [snip] > Total Size: 133537 bytes > > HTML: 26538 > Images: 69020 > Javascript: 12863 > CSS: 25116 > Multimedia: 0 > Other: 0 > [/snip] > > It's all right there and has nothing to do with your > database. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Are my databases dragging down my page?
Someone sent me the following comments on one of my webpages: Global Statistics Total HTTP Requests: 51 Total Size: 133537 bytes Object Size Totals Object type Size (bytes) HTML: 26538 Images: 69020 Javascript: 12863 CSS: 25116 Multimedia: 0 Other: 0 External Objects External Object QTY Total Images: 40 Total Scripts: 5 Total CSS imports: 5 * * * * * * * * * * If I interpret it correctly, my total page size is a whopping 133 KB (133537 bytes). Yet I get a little less than that when I add up the numbers. I just wondered if my databases could be contributing to the figure 133 KB. If so, would that number decrease if I made more effient use of keys and/or called up only the fields I need, rather than querying every field in a table (*)? Also, how do I obtain these sorts of statistics, so I can check my page again after I've made some improvements? Thanks. __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Help Importing CSV Data
I used to use Navicat to import CSV files into MySQL. Now I find that I can use either Navicat or the latest phpMyAdmin upgrade, and one or the other usually works, even when I'm battling collation errors. But I'm stumped on a relatively large but simple table. I wondered if I might make it work if I set my my MySQL table a certain way. I put a picture of my basic table structure online at http://www.geobop.org/dat1.gif The first six fields were originally int(3). However, I made them int(3) so I could insert 937937 in all the NULL cells. When that didn't work, I tried another experiment, changing them to VARCHAR and adding an "x" to the last cells in the 1st and 4th-6th fields. http://www.geobop.org/dat3.gif is a picture of my csv file, focusing on the bottom row. Adding an X to the numerals in the bottom row yielded quotes around those numerals, which I thought might help. http://www.geobop.org/dat2.gif shows what I see after I import the data nd browse my table. The 6th column, RemoteStatesReg, is almost always messed up, displaying NULL and apparently pushing the data on the right into the wrong field. But if I import the data another way, data from several fields are jumbled together in the last field. When I import csv with phpMyAdmin, I first truncate the table, then choose the following settings: Fields Terminated - , Fields Enclosed - " (Optionally) I leave "Fields Escaped By" and "Lines Terminated By" blank. Would it help if I created an extra column, inserted some unique symbol, like !!!, then chose "Lines Terminated By !!!"? Actually, I'll just give it a try. I'm just fishing for any tips anyone can offer for getting this data onboard. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Translating Data Into NULL
--- Andy Bakun <[EMAIL PROTECTED]> wrote: > On Sat, 2005-02-19 at 23:55, David Blomstrom wrote: > > > > Why not set columns to NOT NULL and default to > space > > > for character and 0 > > > for numeric? > > > > OK, you're suggesting I... > > > > 1. Set all the columns to NOT NULL > > 2. Set 0 as the default for numeric fields > > 3. Set "space" for the default in the other > fields? > > Using a single space as the default for a column > won't work because > mysql trims (removes trailing whitespace) from > inserted values. > > Doing something like this sounds like a really bad > work around. The > data you want should be stored and appear in the > right columns. OK, thanks. __ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Translating Data Into NULL
--- Alex S Moore <[EMAIL PROTECTED]> wrote: > David Blomstrom wrote: > > > So I THINK I may have a good idea for a > workaround. I > > simply change all my fields to NOT NULL, then fill > > every cell that would ordinarily be NULL with a > unique > > constant, like 606060. > > > > Then I find some sort of PHP code that translates > > 606060 into NULL, ignoring it when both displaying > > data and sorting table cells. > > Why not set columns to NOT NULL and default to space > for character and 0 > for numeric? OK, you're suggesting I... 1. Set all the columns to NOT NULL 2. Set 0 as the default for numeric fields 3. Set "space" for the default in the other fields? I don't understand what you mean by space. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Translating Data Into NULL
I'm having a terrible time with MySQL's new collation feature. It frequently fills cells that should be NULL with 0's and pushes data from one column to another. I can usually import csv files into my tables using either Navicat or phpMyAdmin, but I have to experiment with a lot of settings, and sometimes it doesn't work at all. Publishing tables online is yet another game, as my webhost hasn't upgraded to the latest MySQL. So I THINK I may have a good idea for a workaround. I simply change all my fields to NOT NULL, then fill every cell that would ordinarily be NULL with a unique constant, like 606060. Then I find some sort of PHP code that translates 606060 into NULL, ignoring it when both displaying data and sorting table cells. Does this sound like a workable solution, and does anyone know how to actually implement it? Thanks. __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"collate latin1_general_ci" Error
I just installed a new software package that included a MySQL upgrade. Everything was working fine until I tried to import one of my tables to my online database and got this error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'collate latin1_general_ci NOT NULL default '', `IDRealm` varc What does this mean, and what's the solution? Also, I noticed that when I create tables, I no longer have a choice for InnoDB, so I've been choosing MyISAM. If I leave it at default, what kind of table would I get? Actually, I'm leaning towards MyISAM anyway. I thought InnoDB were generally considered superior, but when my computer crashed, I lost most of my InnoDB tables but none of my MyISAM tables. Thanks. __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where's my ODBC icon?
--- Osvaldo Sommer <[EMAIL PROTECTED]> wrote: > David: > > If you have windows XP go: > Start/Control Panel in ther choose "Administrative > Tools" and in there > is "Data Sources (ODBC)" where you can define your > dsn. I can't see Administrative Tools anywhere in my Control Panel, but I now have it on my desktop. It does contain a Data Sources (ODBC) icon, so I'll read up on the DSN function you mentioned. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where's my ODBC icon?
--- Andrew Pattison <[EMAIL PROTECTED]> wrote: > < rewarded with > something similar to what I got before.>> > > Not sure what you are looking for then. The myODBC > driver should not need configuring, beyond setting > up data sources, which is exactly what the control > panel applet does for you. There is no program to > launch - you configure a data source to allow you to > access data, then use your ODBC-capable program to > connect to that data source. > > Start up the ODBC applet and change to the "System > DSN" tab. Next, add a new data source which uses the > myODBC driver to connect to the database you want to > access via ODBC. Once you have done this, you can > then connect to the data source from your > ODBC-capable program using the name of the data > source. OK, now I understand it a little better. My ultimate goal is to extract some data from some GIS files and import them into MySQL. So it looks like I'm going to be using ODBC to connect a software program called GeoClient (which I haven't begun to figure out yet) to another program called ArcExplorer (which isn't working for me). This should be interesting. :) Thanks. __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where's my ODBC icon?
--- Andrew Pattison <[EMAIL PROTECTED]> wrote: > The ODBC control applet in Windows can be found in > two places: > > 1. Control Panel. > 2. Administrative Tools (if you are using Windows > 2000 or XP). > > If you have Windows 2000 or XP, the easiest way to > find it is to find > your "Administrative Tools" folder (usually you need > to tweak the Start > menu to make it visible) and double click on the > "Data Sources (ODBC)" > icon. If you have Windows XP you can also open > control panel and use > the Administrative Tools icon in there. > > If you don't have Windows 2000 or XP, you need to > find a file called > odbccp32.cpl (or similar). If this is placed in the > windows\system > directory it should be visible in Control Panel. If > it is not visible, > you should use TweakUI to enable it. TweakUI is a > Microsoft utility and > can be found by searching on download.com . I have Windows XP, but I couldn't find an Administrative Tools folder, so I downloaded and installed TweakUI. I now have an Admin Tools folder on my desktop. I also located the file odbccp32.cpl in the Windows/system32 folder, so I'll try to figure out how to enable it. However, when I double-clicked odbccp32.cpl, I was rewarded with something similar to what I got before. I hope there's a user-friendly program waiting at the end of this journey! :) Thanks for all the tips. __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where's my ODBC icon?
I've checked Start > Programs very thoroughly, and I can't find any reference to ODBC. That's what's so weird; I can see it in Add/Remove programs. I made a desktop shortcut icon to the system32/ODBCad file, but all it does is open up ODBC DataSource Administrator, which doesn't appear to be a start program. I uinstalled and reinstalled ODBC, hoping the start icon would register, but nothing changed. Thanks. --- Peter Brawley <[EMAIL PROTECTED]> wrote: > David, > > >I installed ODBC before but never got a chance to > do > >much with it before my computer crashed. But I > could > >have sworn there was a simple icon that I clicked > to > >start it, just like a normal software program. > > My recollection is that ODBC installation "normally" > adds ODBCAdmin to > the Start Menu, so it's probably there somewhere, > but if you want a > desktop icon for ODBC Administrator, right click on > it in Windows > Explorer and select 'Create a Shortcut'. > > PB > > > David Blomstrom wrote: > > >--- Neculai Macarie <[EMAIL PROTECTED]> wrote: > > > > > > > >>>I just installed MySQL's ODBC program, but I > can't > >>>figure out how to launch it. I see no reference > to > >>>ODBC when I go to Start > Programs. The only > thing > >>>resembling an executable icon I can find is in my > >>>Add/Remove programs directory. I did a Windows > >>> > >>> > >>search > >> > >> > >>>and found many files named ODBC, most of them in > >>>Windows/Prefetch and Windows/system32, but none > of > >>>them appear to be executable programs. > >>> > >>>I posted a message on MySQL's ODBC forum but > >>> > >>> > >>haven't > >> > >> > >>>received any replies. Does anyone know of a way > to > >>>locate ODBC's executable file and create a > desktop > >>>icon? > >>> > >>> > >>I think the path is to configure ODBC datasources: > >>%SystemRoot%\system32\odbcad32.exe > >> > >>There isn't a MySQL ODBC executable as far as I > >>know. > >> > >>-- > >> > >> > >> > > > >OK, I found it there...but how do you start it? > Double > >clicking it just brings up all sorts of information > >and choices. > > > >I installed ODBC before but never got a chance to > do > >much with it before my computer crashed. But I > could > >have sworn there was a simple icon that I clicked > to > >start it, just like a normal software program. > > > >Thanks. > > > > > > > > > >__ > >Do you Yahoo!? > >The all-new My Yahoo! - What will yours do? > >http://my.yahoo.com > > > > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.7 - Release > Date: 2/10/2005 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where's my ODBC icon?
--- Neculai Macarie <[EMAIL PROTECTED]> wrote: > > I just installed MySQL's ODBC program, but I can't > > figure out how to launch it. I see no reference to > > ODBC when I go to Start > Programs. The only thing > > resembling an executable icon I can find is in my > > Add/Remove programs directory. I did a Windows > search > > and found many files named ODBC, most of them in > > Windows/Prefetch and Windows/system32, but none of > > them appear to be executable programs. > > > > I posted a message on MySQL's ODBC forum but > haven't > > received any replies. Does anyone know of a way to > > locate ODBC's executable file and create a desktop > > icon? > > I think the path is to configure ODBC datasources: > %SystemRoot%\system32\odbcad32.exe > > There isn't a MySQL ODBC executable as far as I > know. > > -- > OK, I found it there...but how do you start it? Double clicking it just brings up all sorts of information and choices. I installed ODBC before but never got a chance to do much with it before my computer crashed. But I could have sworn there was a simple icon that I clicked to start it, just like a normal software program. Thanks. __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where's my ODBC icon?
I just installed MySQL's ODBC program, but I can't figure out how to launch it. I see no reference to ODBC when I go to Start > Programs. The only thing resembling an executable icon I can find is in my Add/Remove programs directory. I did a Windows search and found many files named ODBC, most of them in Windows/Prefetch and Windows/system32, but none of them appear to be executable programs. I posted a message on MySQL's ODBC forum but haven't received any replies. Does anyone know of a way to locate ODBC's executable file and create a desktop icon? Thanks. __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing ArcView Shapefile into a Database
Aha - that's why I like to ask questions on newsgroups rather than get secondhand news from Google! :) This sounds really cool. One question, though...when you talk about downloading the "source code," are you saying your program is for Linux only? As much as I hate Microsoft, I'm still stuck with Windows XP. Actually, someone gave me a computer with a dual boot hard drive and SUSE installed, but I haven't had time to learn how to use it yet. Thanks for the tip! --- Jeremy Cole <[EMAIL PROTECTED]> wrote: > Hi David, > > > I want to import some data into my database, but > it's > > in an ArcView Shapefile, which I've never worked > with. > > Does anyone know what kind of software I can use > to > > access this data? Actually, I'd probably import it > > into a spreadsheeet first, then save it as a csv > file > > and import it into my database. > > I've been working on a library called libmygis which > is able to read > varoius types of GIS data files (mainly Shapefiles > for now). > > You're in luck. I've recently gotten a working > version of > mysqlgisimport which is able to take a SHP/SHX/DBF > set and turn it into > SQL directly to be imported. > > You can grab the source code of libmygis at: > > http://jcole.us/software/libmygis/ > > If you have any questions or need help getting it to > work (hey, it's > new!) please feel free to drop me a line. Same goes > for feature > requests. :) > > Regards, > > Jeremy > > -- > Jeremy Cole > Technical Yahoo - MySQL (Database) Geek > Desk: 408 349 5104 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing ArcView Shapefile into a Database
I want to import some data into my database, but it's in an ArcView Shapefile, which I've never worked with. Does anyone know what kind of software I can use to access this data? Actually, I'd probably import it into a spreadsheeet first, then save it as a csv file and import it into my database. Thanks. __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this a good scheme for EcoRegions?
I already have a series of tables defining the world's continents, oceans, nations, states, etc. Actually, it's just two tables. Type defines the kinds of divisions, like this: TABLE: TYPE (ID) (Type) pla | planet kon | continent oce | ocean nat | nation sta | state pro | province Table famarea lists geographic names, identifying each by ID and type and matching them to their parents... TABLE: FAMAREA (ID)(Name) (Type) (Parent) us-al | Alabama | sta | usa afr | Africa | kon | ear (Earth) alb | Albania | nat | eur arc | Arctic Ocean | oce | oce (Oceania) Now I want to incorporate ecological regions. They include the eight REALMS the world is divided into, fourteen major BIOMES and over 800 ECOLOGICAL REGIONS. I can easily define these three types in my Types table: TABLE: TYPE (ID) (Type) kon | continent rea | realm bio | biome eco | ecoregion But then I quickly get confused because ecological regions don't follow national or even continental borders. Adding a table similar to famarea, simply listing each realm, biome and ecological region and including parents, would probably be too complex. My instinct is to first create three separate tables, listing the realms, biomes and ecoregions, respectively. I could also add parents to the EcoRealms table, since there are only 8 realms, and none have more than two parents: TABLE: ECOREALMS (ID) (Name) (Parent 1) (Parent 2) R-PA | Palearctic | Eurasia | Africa R-NA | Nearctic | North America | (NULL) I think I would then list two KINDS of parents for ecoregions, the biome and realm: TABLE: ECOREGIONS (ID)(Name) (Biome) (Realm) NA010 | Great Plains | B-5 | R-NA NA011 | Tallgrass Prairies | B-5 | R-NA AT023 | Steppe | B-5 | R-PA NA10 | Boreal Forest | B-2 | R-NA For example, the table above identifies the Great Plains, Tallgrass Prairies and Steppe as temperate grasslands (biome B-5). However, it matches the first two with NORTH AMERICAN grasslands, with steppes matched to Eurasian grasslands. If this is OK so far, then I need to figure out what to do with biomes, each of which can have several continents (or realms) as parents. Should I just add extra lines for extra parents, like this?: TABLE: ECOBIOMES (ID) (Name) (Parent) B-5 | Temperate Grasslands | R-NA (North America) B-5 | Temperate Grasslands | R-PA (Eurasia) ...or should I just make a table that simply lists the biomes and ID's, then make a fourth table that matches the biomes to their parents, like this?: B-5 | R-NA (North America) B-5 | R-PA (Eurasia) Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing Tables on Top of Tables
If I revise a MySQL table and try to publish it online, I often get the error message, "Table my_table already exists." So I have to delete the online version before I can import the revised version. Is there a way to just publish one table over another, as long as both have the same name? Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a New Password & Username
--- Tom Crimmins <[EMAIL PROTECTED]> wrote: > It's all there in the manual. I like your explanation a lot better; it works, too! :) Thanks. __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a New Password & Username
Wow, I don't remember dealing with all that code the first time around. Can someone show me EXACTLY what I would write if my database is named my_database, and I want to add the username private_host and the password superstar? And if I don't have mysqladmin, can I type it into the SQL Query window on phpMyAdmin? Thanks. __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need a New Password & Username
I just recreated a database after my computer crashed. All my MyISAM tables survived, but my InnoDB tables vanished. The other problem is that I need to reassign a username and password, and I can't remember how I did that originally. I now have the latest version of phpMyAdmin, but I can't find any reference to username or password. There are instructions for assigning passwords in MYSQLADMIN at http://dev.mysql.com/doc/mysql/en/passwords.html Would these same instructions work in phpMyAdmin...I'd just click "Query" and type the commands in the window under "Run SQL query/queries on database XXX_ZZZ:"? As I recall, "localhost" is the standard ROOT name and is presumably already assigned. I've assigned a database name, so I just need to reassign my username and password. Thanks. __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying Database from One Hard Drive to Another
Ah, I see. So the tables that are represented by all three files are presumably MyISAM files, right? --- Victor Pendleton <[EMAIL PROTECTED]> wrote: > InnoDB files can not be copied from one machine to > another like the > MyISAM files. If you did not dump the data or you do > not have an > archived backup you will more than likely have to > recreate the table > structures. > > > David Blomstrom wrote: > > >Yikes...I've been replying to myself! I forgot that > >when I reply to messages from the MySQL newsgroup > on > >this e-mail account, it doesn't include > >mysql@lists.mysql.com, for some odd reason. > > > >At any rate, I wanted to say that I have just ONE > file > >type left - FRM. My MYD and MYI files have both > >vanished. > > > >But I took another look and discovered that SOME > >tables are represented by all three files, and when > I > >paste them into my new MySQL > Data folder, they > work > >just fine. > > > >I also discovered the following files in my backup > >folder: > > > >ibdata1...215,040 KB > >ib_logfile1...5,120 KB > >ib_logfile0...5,120 KB > >ib_arch_log_0...25 KB > > > >Can these somehow be used to reconstitute lost > >database tables? The situation isn't critical, as I > >can recreate most of these tables from my original > >data, which is on spreadsheets. But recreating all > the > >fields and keys would obviously be a pain in the > butt; > >I had over 100 tables. > > > >Thanks. > > > > > > > > > >__ > >Do you Yahoo!? > >Yahoo! Mail - Helps protect you from nasty viruses. > > >http://promotions.yahoo.com/new_mail > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying Database from One Hard Drive to Another
Yikes...I've been replying to myself! I forgot that when I reply to messages from the MySQL newsgroup on this e-mail account, it doesn't include mysql@lists.mysql.com, for some odd reason. At any rate, I wanted to say that I have just ONE file type left - FRM. My MYD and MYI files have both vanished. But I took another look and discovered that SOME tables are represented by all three files, and when I paste them into my new MySQL > Data folder, they work just fine. I also discovered the following files in my backup folder: ibdata1...215,040 KB ib_logfile1...5,120 KB ib_logfile0...5,120 KB ib_arch_log_0...25 KB Can these somehow be used to reconstitute lost database tables? The situation isn't critical, as I can recreate most of these tables from my original data, which is on spreadsheets. But recreating all the fields and keys would obviously be a pain in the butt; I had over 100 tables. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying Database from One Hard Drive to Another
I've been using a preconfigured package (Apache, PHP, MySQL) from Apache Friends/XAMPP for several months. About a week ago, my computer crashed, and I'm now getting back on my feet. I downloaded the latest XAMPP, which features upgrades for PHP, phpMyAdmin and I think MySQL, too. Everything seems to be working fine, and I'm ready to retrieve my database, which contains over 100 tables. I copied the original XAMPP, with all my databases into an external hard drive. I can see my databases at the following location: MySQL > data > my_database But before I do anything stupid, I'd like to ask what's the correct way to proceed? I assume I can simply copy the folder my_database and paste it into MySQL > data > on my new XAMPP setup, right? Will the usernames and passwords be installed with it, or will I have to recreate them? Or is it better to go into my current XAMPP/MySQL, create a database with the same name and assign it the proper passwords, then paste my tables inside it? I assume either method will work, but I just want to make sure. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound "IF" Statements?
I did a lot of homework on IF statements recently. Below are copies of some of the scripts I'm using now. This first example draws on a database filled with the names of the world's continents, oceans, nations and states, each given an ID in a field named IDArea. Each page on my site has an echo statement - $mycode - which is matched to a value in IDArea. I want most of the higher ranking pages to display an index of the nations or states they include. So the script below says that the pages Earth ($mycode = ear), North America ($mycode = na), etc. will include a file named include/index/$mycode.php. Thus, the Earth page will include include/index/ear.php. You can see this index in the right column of the page at www.geozoo.org/world/ But I want all the pages that don't feature indexes to display affiliate ads, like those on my France page at http://www.geozoo.org/world/eur/fra/. * * * * * * * * * * The followingn script says that IF there's any data for a particular page ($mycode, like usa) in a field named GZArticle, then everything between the first line (the IF statement) and the last line (ENDIF) will be displayed: Introduction * * * * * * * * * * I can't even remember exactly how the following script works, at least not at this late hour. But it displays various trivia only if there are trivia items in the relevant database table cells: Biggest Native Land Mammal* Animal Trivia () ~ lb. * * * * * * * * * * The following script is from my head section. It uses a combination of $mycode (e.g. usa) and $mykind (e.g. nation) values to determine what style sheets are displayed on various pages. '); break; case 'about': echo (''); break; case 'ref': echo (''); break; case 'topic': echo (''); break; } ?> '); break; case 'na': case 'sa': case 'cam': case 'eur': case 'mes': case 'afr': case 'aus': echo (' '); break; case 'anx': echo (' '); break; case 'oce': echo (' '); break; case 'pac': case 'atl': case 'ino': case 'arc': case 'sou': echo (' '); break; } ?> '); break; case 'pac': case 'atl': case 'ino': echo (' '); break; case 'usa': case 'can': case 'mex': echo (' '); break; /* default: print '' . $data_details['NameParent'] . ' >'; break; */ } ?> * * * * * * * * * * Towards the end of the script below, you'll see an IF satement that says IF there's a nickname in a cell named NNPx, then echo (display) that nickname. Otherwise, include a page named suggest/nickname.php. (Most of the pages on all my sites are designed to display a title, subtitle (or nickname) and quote. If no nickname or quote is available, they're replaced with a message inviting people to submit ideas.) 0)) { } else { die('Invalid query: ' . mysql_error()); } { $data_details[IDParent] = substr("$data_details[IDParent]", 0, 2); // returns "1st 2 characters, turning usa into us" $res = mysql_query ("SELECT * FROM gzstates where IDArea = '$data_details[IDParent]-$mycode_2Char'") or die (mysql_error()); // while ($row = mysql_fetch_array ($res)) { if ($row['NNPx']) echo ($row['NNPx']); else include($_SERVER['DOCUMENT_ROOT']."/includes/suggest/nickname.php"); // echo ''. $row['NNPx'] .''; } } ?> * * * * * * * * * * In the middle of the script I pasted below, you'll find this code: { if(empty($row['YearEnded'])) { $class='hilite'; } else { $class= ''; } It says if there's no data in a database table cell named YearEnded (the year a governor's term ended), give that a row a class named hilite. Thus, becomes Otherwise, leave it , or something like that. Thus, rows representing CURRENT governors (whose terms haven't ended are hilited yellow. If I remember correctly, the following script inserts a link closing tag - - IF a link is included in a cell named LinkPA. if($row['LinkPA'] != '') { print(""); } Here's the entire script: 0)) { } else { die('Invalid query: ' . mysql_error()); } { $res = mysql_query ("SELECT * FROM pxgovernors where IDArea = 'us-$mycode'") or die (mysql_error()); echo ' Governors NamePartyTerm(s)'; // while ($row = mysql_fetch_array ($res)) { if(empty($row['YearEnded'])) { $class='hilite'; } else { $class= ''; } echo ''. $row['LinkSenWiki'] , $row['NameFirst'] . ' ' . $row['NameLast'] .''; if($row['LinkPA'] != '') { print(""); } echo ' '. $row['Party'] .' '. $row['YearBegan'] . '-' . $row['YearEnded'] .''; echo ' '."\n"; } } echo ''; ?> * * * * * * * * * * That isn't a clear answer to your question, but maybe it will give you some ideas. I'm still getting the hang of it myself. --- Sue Cram <[EMAIL PROTECTED]> wrote: > Thanks to the people who helped me with my IF > statement question last night. Now I need
RE: Displaying Data for Each Day of the Year/Week
Thanks - that works great! --- Jay Blanchard <[EMAIL PROTECTED]> wrote: > [snip] > > Cal_ID | Cal_Date | Cal_Event > 1 | 1972-01-01 | A new species of whale was > discovered. > 7 | 1898-01-07 | The dodo was declared extinct. > 8 | 1972-01-08 | The first Earth Day was observed. > > Then I used this script to try to display a sample: > > $date = date("Y-m-d"); > $sql = "SELECT Cal_Event FROM gzcalendar WHERE > Cal_Date = '$date'"; > $res = mysql_query($sql); > while($row = mysql_fetch_assoc($res)) > { > print($row['Cal_Event'] . ""); > } > ?> > > $monthDay = date("m-d"); > $sql = "SELECT Cal_Event FROM gzcalendar WHERE > SUBSTRING(Cal_Date, 6, 5) > = '$monthDay'"; > > CAUTION! MySQL SUBSTRINGs start counting strings at > '1', PHP and othe > langauges usually count from '0' > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Displaying Data for Each Day of the Year/Week
I think mine is a fairly simple question. In fact, I think my script may just need a simple tweak. It's also possible that my problem relates to displaying the data with PHP, but I want to make sure I've got my MySQL table set up correctly. Anyway, I've collected historic events for every day of the year - including multiple events for some days. Now I want to create a web page that will display events that occurred on January 1 (various years) when it's January 1. When the clock hits midnight, it will display events that occurred on January 2. So I created a database table named "gzcalendar" with three fields. The first field will simply be numbered 1-366, for every day of the year (including leap years). As you can see, Cal_Date lists dates, while events are listed under Cal_Event. Cal_ID | Cal_Date | Cal_Event 1 | 1972-01-01 | A new species of whale was discovered. 7 | 1898-01-07 | The dodo was declared extinct. 8 | 1972-01-08 | The first Earth Day was observed. Then I used this script to try to display a sample: "); } ?> It doesn't work, and I'm guessing it's because of the first line - $date = date("Y-m-d"); If someone visits my website on January 7, 2005 (2005-01-07), then they're not going to connect with an event that occurred on January 7, 1888, right? It seems to me I want something like this: $date = date("m-d"); So if someone visits my website on January 7, they'll see an event that occurred on January 7, regardless of the year. But I haven't yet figured out exactly how to do that? If I can at least get my table set up correctly, then I can get some advice for displaying it from a PHP forum. I also want to learn how to display data for each day of the week - Sunday, Monday, etc. Thanks! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: [PHP] How to argue with ASP people...
> > -Ursprüngliche Nachricht- > > Von: mail.pmpa [mailto:[EMAIL PROTECTED] > > Gesendet: Freitag, 31. Dezember 2004 03:27 > > An: mysql@lists.mysql.com > > Betreff: RE: [PHP] How to argue with ASP people... > > > > Don't mean to start a discussion whatsoever, I > love php, but > > one thing i can't do in php is > Response.Redirect("page.asp") . > > Apart from that no complains so far :) > > > > * Happy New Year * > > > > Pedro Almeida. I'm just a beginner-intermediate at PHP and have no experience with ASP, so take what I have to say with a grain of salt. However, isn't it usually more expensive AND more risky to work in a Microsoft environment? Sure, Microsoft fans will tell that one technology or another is free...but I think there are usually hidden price tags, somewhere along the line. After all, Microsoft is in business to make (or steal) money, right? I just think people are better off if they stay as far away from Microsoft as possible, using "clean" technology ahead. Apache/Linux servers are the standard, and the Mozilla Firefox/Thunderbird software are showing up Internet Explorer and Outlook Express. I believe PHP and MySQL are the standards in their field, are they not? Why get mixed up with Microsoft when you have to constantly look over your shoulder for a hidden price tag, a computer virus, security risk or some new technology devised by Bill Gates to steal your ideas or hard work? __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions About Creating Tables
Thanks so much for the tips! It's all falling into place now. I'm still using the animal database tables you suggested for me some time ago, but I also wanted to tap into this ITIS database. Combining or linking the two should be interesting. :) --- [EMAIL PROTECTED] wrote: > I see you found some DB code for a different > platform. Most of your > questions are based on the fact that these are NOT > MySQL commands (or > scripts) but SQL statements phrased for a different > system. If you know > what system it is designed for (Oracle, DB2. ??? ) > you should be able to > read their docs, understand their commands and > translate this script into > something MySQL-compatible. > > Here are my best guesses (not being 100% certain > precisely which DB system > it's written for) > > David Blomstrom <[EMAIL PROTECTED]> wrote on > 12/25/2004 06:32:12 > PM: > > > I have some questions about some instructions for > > recreating tables I'm trying to follow: > > > > 1. This first table is the simplest. The only > thing I > > don't understand is "serial." I created char and > date > > columns easily enough, but I don't see "serial" > listed > > in phpMyAdmin. > > "Serial" is *not* a MySQL data type, that's why it > isn't listed. I would > suspect that you could use "int auto_increment" > instead. > > > > 2. In the table below, why do they designate two > > numerals for the second field, when it's varchar? > How > > do you enter 80,5 without getting an error? > > Again, I refer you to the documentation for the DB > system this command was > written for. This is not a MySQL convention. I > suspect that you could use > "VARCHAR(80)" as a MySQL equivalent. > > > > 3. I think I've figured out that the unique key > above > > refers to three combined fields - tsn, > vernacular_name > > and language. So how do I create a unique > triple-field > > key? > > UNIQUE (field1, field2, field3) > > read: > > http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html > > http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html > > > > > 4. Constraint refers to a foreign key, right? If > so, > > then I ought to be able to leave this blank for > now > > and add it later, right? > > No, "constraint" does not refer to only foreign > keys. A constraint is > anything that limits what data is allowed into your > table. the phrase "NOT > NULL" is one kind of constraint, so are primary, > unique, and foreign keys, > among others. > > read: > > http://dev.mysql.com/doc/mysql/en/Constraints.html > > http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html > > > > 5. In the example below, what do "row size" and > "index > > size" mean? How do I designate them? > > { TABLE "itis".change_operations row size = 12 > number > > of columns = 3 index size = 42 > > The most "simple" answer is: You don't need to > designate those values for > MySQL. Each MySQL storage engine will compute those > values for you. > > > Finally, am I correct that the text below is > designed > > to automatically create all my tables? I haven't > been > > able to make it work yet, but that's my > > interpretation. > > The "text below" (snipped) may work on another DB > system but not on MySQL. > You will need to translate it first in order to make > it MySQL-compatible. > > > > > Thanks. > > NP > > > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions About Creating Tables
I have some questions about some instructions for recreating tables I'm trying to follow: 1. This first table is the simplest. The only thing I don't understand is "serial." I created char and date columns easily enough, but I don't see "serial" listed in phpMyAdmin. create table "itis".kingdoms ( kingdom_id serial not null , kingdom_name char(10) not null , update_date date not null ); 2. In the table below, why do they designate two numerals for the second field, when it's varchar? How do you enter 80,5 without getting an error? create table "itis".vernaculars ( tsn integer not null , vernacular_name varchar(80,5) not null , language varchar(15) not null , approved_ind char(1), update_date date not null , vern_id serial not null , unique (tsn,vernacular_name,language) constraint "itis".u_vern, primary key (vern_id) constraint "itis".vern_key ); revoke all on "itis".vernaculars from "public"; 3. I think I've figured out that the unique key above refers to three combined fields - tsn, vernacular_name and language. So how do I create a unique triple-field key? 4. Constraint refers to a foreign key, right? If so, then I ought to be able to leave this blank for now and add it later, right? 5. In the example below, what do "row size" and "index size" mean? How do I designate them? { TABLE "itis".change_operations row size = 12 number of columns = 3 index size = 42 Finally, am I correct that the text below is designed to automatically create all my tables? I haven't been able to make it work yet, but that's my interpretation. Thanks. * * * * * * * * * * { DATABASE itis delimiter | } grant dba to "itis"; grant dba to "informix"; grant dba to "public"; grant dba to "root"; grant connect to "http"; { TABLE "itis".change_comments row size = 263 number of columns = 4 index size = 30 } { unload file name = chang00100.unl number of rows = 0 } create table "itis".change_comments ( change_track_id integer not null , chg_cmt_id serial not null , change_detail varchar(250,1) not null , update_date date not null ); revoke all on "itis".change_comments from "public"; { TABLE "itis".change_operations row size = 12 number of columns = 3 index size = 42 } { unload file name = chang00101.unl number of rows = 0 } create table "itis".change_operations ( change_track_id integer not null , chg_op_id integer not null , update_date date not null ); revoke all on "itis".change_operations from "public"; { TABLE "itis".chg_operation_lkp row size = 34 number of columns = 3 index size = 12 } { unload file name = chg_o00102.unl number of rows = 0 } create table "itis".chg_operation_lkp ( chg_op_id serial not null , change_operation varchar(25,5) not null , update_date date not null ); revoke all on "itis".chg_operation_lkp from "public"; { TABLE "itis".change_tracks row size = 367 number of columns = 9 index size = 12 } { unload file name = chang00103.unl number of rows = 0 } create table "itis".change_tracks ( change_track_id serial not null , old_tsn integer, change_reason varchar(40,18) not null , change_initiator varchar(100,1) not null , change_reviewer varchar(100,1) not null , change_certifier varchar(100,1) not null , change_time_stamp datetime year to minute not null , tsn integer not null , update_date date not null ); revoke all on "itis".change_tracks from "public"; { TABLE "itis".kingdoms row size = 18 number of columns = 3 index size = 12 } { unload file name = kingd00104.unl number of rows = 6 } create table "itis".kingdoms ( kingdom_id serial not null , kingdom_name char(10) not null , update_date date not null ); revoke all on "itis".kingdoms from "public"; { TABLE "itis".taxon_unit_types row size = 29 number of columns = 6 index size = 15 } { unload file name = taxon00105.unl number of rows = 136 } create table "itis".taxon_unit_types ( kingdom_id integer not null , rank_id smallint not null , rank_name char(15) not null , dir_parent_rank_id smallint not null , req_parent_rank_id smallint not null , update_date date not null ); revoke all on "itis".taxon_unit_types from "public"; { TABLE "itis".geographic_div row size = 54 number of columns = 3 index size = 93 } { unload file name = geogr00106.unl number of rows = 93244 } create table "itis".geographic_div ( tsn integer not null , geographic_value varchar(45,6) not null , update_date date not null ); revoke all on "itis".geographic_div from "public"; { TABLE "itis".jurisdiction row size = 59 number of columns = 4 index size = 70 } { unload file name = juris00107.unl number of rows = 44398 } create table "itis".jurisdiction ( tsn integer not null , jurisdiction_value varchar(30,6) not null , origin varchar(19,6) not null
Need Help with .UNL Files
I downloaded a TAR file from a link I found at http://www.itis.usda.gov/ftp_download.html The page also includes the following instructions: * The schema file (itis.sql) is an ASCII file that includes all of the create table statements plus additional information. This will allow comparison to the schema files that are available in the future to determine any changes. The table files are in an ASCII pipe delimited format. Within the schema file the information about each table may be found: { TABLE "itis".kingdoms row size=18 number of columns=3 index size=12 } { unload file name=kingd00104.unl number of rows=5 } create table "itis".kingdoms ( kingdom_id serial not null , kingdom_name char(10) not null , update_date date not null ); revoke all on "itis".kingdoms from "public"; create unique index "itis".kingdoms_key on "itis".kingdoms (kingdom_id); alter table "itis".kingdoms add constraint primary key (kingdom_id) constraint "itis".u119_9 ; In addition to the create table statements, the itis.sql file provides the "unload file name" information and constraint and index statements. The unload file name can be used to map the unload file to the table. For example, to load the kingdom, the kingd00104.unl file is used. * * * * * * * * * * Can someone tell me what all this means??? After unzipping the file, I have an SQL file named itis, along with a bunch of files with an .unl extension. If I understand correctly, I'm supposed to activate the .unl files with the SQL file, right? But I'm not even sure how to open the SQL file. I tried importing it into my database with phpMyAdmin but got this error message: SQL-query : { DATABASE itis delimiter | } GRANT dba TO "itis" MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '{ DATABASE itis delimiter | } grant dba to "itis"' at line 1 So what's the easiest way to get all these tables imported into my database? Thanks. __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INT or DECIMAL?
I was having trouble importing a particular csv file into a MYSQL table, so I designated most of the fields VARCHAR. Now that all my data is finally in the table, I'm weeding out various mistakes and redesignating the fields. But I'm hung up on a numerical field which features 1) some numerals that require commas and 2) variable decimal places. It appears to have a maximum of about 12 characters, with anywhere from 0-2 decimal places. It could include any of the following numerals: 1002 124.5 64.12 14025.1 This is how I designated it in my table: decimal(10,2) But the decimals are no longer displaying. I'm using the following code on my webpage to display commas: " . number_format($row['MilAvail']) . " Does anyone know what I have to do to display 1,024.05, rather than 1,024? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Preventing Publishing to Web?
Please disregard my last post. I found another workaround - I saved the database table on my computer in an unzipped format, and I was able to export it to my website. It looks good! http://www.geoworld.org/north_america/usa/az/counties/index.php Thanks. __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Preventing Publishing to Web?
--- [EMAIL PROTECTED] wrote: > hi, > > in the script you have > " CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) > REFERENCES > `statesarticles` ( `SCode` ) ON UPDATE CASCADE " > and this code it's a definition of the foreign key. > run the script without this part of the code. * * * * * * * * * * Thank you. So I can just create a table online by pasting the code into a query window, right? I've never done this before, and I'm not sure what the first step is. Should I choose "Query," or the small pop-up "Query Window"? I've been playing with both. Sometimes my experiment goes nowhere, and other times I get an error message related to the last line. Below is a copy of what I pasted in. Thanks. CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , ) TYPE = InnoDB PACK_KEYS =0 AUTO_INCREMENT =3143 __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Preventing Publishing to Web?
When I was first learning to work with MySQL, I discovered that I couldn't publish my databases to the Internet if they had foreign keys. So I stripped out all the foreign keys and got them online. I'm not trying to publish a table I haven't worked with in a while, but I just get error messages. The error message suggests it's a foreign key problem; at the very least, it suggests the table has a foreign key. But I can't remember how to even tell if a foreign key is present. When I look at my table in PPHPmyadmin, I just see one key - a primary key - under Keyname. When I click on SCode - the field that supposedly has the foreign key, I see no reference to a foreign key. I pasted the error message below. Can someone tell me what I'm missing? Thanks. SQL-query: # phpMyAdmin SQL Dump # version 2.5.3 # http://www.phpmyadmin.net # # Host: localhost # Generation Time: Aug 17, 2004 at 08:52 AM # Server version: 4.0.16 # PHP Version: 4.3.4 # # Database : `world` # # # # Table structure for table `counties` # CREATE TABLE `counties` ( `SCode` varchar( 6 ) NOT NULL default '', `NameC` varchar( 255 ) default NULL , `TypeC` varchar( 255 ) default NULL , `Seat` varchar( 255 ) default NULL , `Area_MI` decimal( 10, 1 ) default NULL , `Area_KM` decimal( 10, 1 ) default NULL , `Pop_2000` int( 10 ) default NULL , `Pop_1990` int( 10 ) default NULL , `Pop_MI` decimal( 10, 1 ) default NULL , `Pop_KM` decimal( 10, 1 ) default NULL , `Race1` int( 10 ) default NULL , `Race2` int( 10 ) default NULL , `Amerindian` int( 10 ) default NULL , `White` int( 10 ) default NULL , `Black` int( 10 ) default NULL , `Asian` int( 10 ) default NULL , `Pacific_Island` int( 6 ) default NULL , `Some_Other_Race` int( 10 ) default NULL , `Hispanic` int( 10 ) default NULL , `id` int( 6 ) NOT NULL AUTO_INCREMENT , PRIMARY KEY ( `id` ) , KEY `SCode` ( `SCode` ) , CONSTRAINT `0_132` FOREIGN KEY ( `SCode` ) REFERENCES `statesarticles` ( `SCode` ) ON UPDATE CASCADE ) TYPE = InnoDB PACK_KEYS =0 AUTO_INCREMENT =3143 MySQL said: Documentation #1005 - Can't create table './world_gypsy/counties.frm' (errno: 150) __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Problem
--- Ligaya Turmelle <[EMAIL PROTECTED]> wrote: > Think I found it. I made the changes with > explanations of what I did. > If you have any further questions feel free to ask. > Oh and this should > be on the list for others to see and maybe learn > from Wow, thanks so much for going to all that trouble. Several other people sent me tips, too. I feel bad to tell you that it still doesn't work. I got an immediate parse error. Also, I don't know if I should continue this on the list since it may be turning into more of a PHP problem. But it is a cool script that others might like to learn about. You can see a working example on my website at http://www.geoworld.org/reference/people/ (A good column to sort is Population; you'll see China at the top of the column if you choose DESCENDING.) But this page only sorts data from ONE database table. I'm now trying to make one that will sort fields from multiple tables. The problem is that there are too many elements, none of which I really understand. So if I fix a parse error, the data doesn't display, and if I fix it so the data displays, the PHP sorting switch doesn't work. I have learned a few things: 1. For some reason, I can't limit the display with a regular WHERE query. It displays ALL the rows (all the world's nations), even if I ask it to display rows only WHERE F.IDParent = 'eur' (Eurasia). To make it work, I have to use an "official" join, like this: FROM cia_people C LEFT JOIN famarea2 F ON C.IDArea = F.IDArea WHERE F.IDParent = 'eur' * * * * * * * * * * 2. I had the wrong field for the 'eur' values; it should be F.IDParent, not IDArea. * * * * * * * * * * 3. This is the most critical code: ORDER BY '" . $_POST['order'] . "','" . $_POST['direction']."'"; It's usually the first to flake out, either causing a parse error or simply not functioning. Every time I modify another key function, I have to modify this line, and it's too complex for me to re-engineer. * * * * * * * * * * 4. I've received a variety of opinions on the quotes, on functions throughout the source code. I'm not sure sure if I should be using single quotes, double quotes or no quotes at all in certain instances. * * * * * * * * * * 5. There may also be a conflict with globals and $_Post. Again, I don't understand this stuff. If I understand correctly, I should either turn globals on or off (or not have them in the first place), and use $_Post in one instance but not the other? * * * * * * * * * * I'm amazed there isn't more information about this script readily avaiable. It seems like such a useful function, I thought it would be rather common. Below is my current source code. It displays the data correctly, without errors, but the sort function doesn't work. Once again, it draws from two tables, named cia_people and famarea2, joined by the field they share in common, IDArea. Every field cited as an "option value" is from table cia_people except IDParentReg, which is the field from table famarea2 I want to sort by. Actually, both tables share a field named "Name," but I think I identified cia_people.Name in the query. Don't feel obligated to pursue this; I've already spent two days on it! :) Thanks. * * * * * * * * * * [DATABASE CONNECTION] Country, etc. Population Nationality Nationality: Plural Nationality: Adjective Geographic Region + - 0)) { // continue here with the code that starts //$res = mysql_query ("SELECT * FROM type. } else { die('Invalid query: ' . mysql_error()); } switch($order) { case 1: $order = 'Name'; break; case 2: $order = 'Pop'; break; case 3: $order = 'Nationality'; break; case 4: $order = 'NationalityPlural'; break; case 5: $order = 'NationalityAdjective'; break; case 6: $order = 'IDParentReg'; break; default: $order = 'Name'; break; } switch($direction) { case 0: $direction = 'ASC'; break; case 1: $direction = 'DESC'; break; default: $direction = 'ASC'; break; } $sql = "SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C LEFT JOIN famarea2 F ON C.IDArea = F.IDArea WHERE F.IDParent = 'eur' ORDER BY '" . $_POST['order'] . "','" . $_POST['direction']."'"; $res = mysql_query($sql) or die('Failed to run ' . $sql . ' - ' . mysql_error()); echo ' CountryX '; // $rowcounter=0; while ($row = mysql_fetch_array ($res)) { $c=$colors[$rowcounter++%$size]; echo "<". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] ."> ". $row['Name'] ." \n"; } ?> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL Gen
SQL Syntax Problem
This may be a purely PHP problem, but the error message says "SQL syntax. Check the manual that corresponds to your MySQL server version..." More important, I haven't been able to find a solution on any PHP forums. :) This is the complete error message: Failed to run SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = 'eur') ORDER BY $_POST['order'], $_POST['direction'] - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '['order'], $_POST['direction']' at line 11 But the line it references isn't really line 11. This is it: $_POST[\'order\'], $_POST[\'direction\']'; $res = mysql_query($sql) or die('Failed to run ' . $sql . ' - ' . mysql_error()); Someone suggested the problem is the word "order." So I replaced every instance of "order" with "reorder" and got the same results. Another individual suggested I remove the backward slashes in the first line, but I had to add those to get rid of a series of parse errors. Does anyone have a clue what the problem/solution is? Or can you tell me exactly what I'm supposed to look up in the manual? Thanks. [DATABASE CONNECTION] Country, etc. Population Nationality Nationality: Plural Nationality: Adjective Geographic Region + - 0)) { // continue here with the code that starts //$res = mysql_query ("SELECT * FROM type. } else { die('Invalid query: ' . mysql_error()); } switch($order) { case 1: $order = 'cia_people.Name'; break; case 2: $order = 'cia_people.Pop'; break; case 3: $order = 'cia_people.Nationality'; break; case 4: $order = 'cia_people.NationalityPlural'; break; case 5: $order = 'cia_people.NationalityAdjective'; break; case 6: $order = 'famarea2.IDParentReg'; break; default: $order = 'cia_people.Name'; break; } switch($direction) { case 0: $direction = 'ASC'; break; case 1: $direction = 'DESC'; break; default: $direction = 'ASC'; break; } //-- [...] $sql = 'SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = \'eur\') ORDER BY $_POST[\'order\'], $_POST[\'direction\']'; $res = mysql_query($sql) or die('Failed to run ' . $sql . ' - ' . mysql_error()); echo ' CountryX '; // $rowcounter=0; while ($row = mysql_fetch_array ($res)) { $c=$colors[$rowcounter++%$size]; echo "<". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] ."> ". $row['Name'] ." \n"; } ?> ?> __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subject Headings in Tables
Oops, I forgot my follow up question... I now have two tables: TABLE 1 Alabama Alaska Arizona Arkansas California Colorado Connecticut TABLE 2 Far North New England Pacific Coast South Southwest Rocky Mountains If I had put all of the above in one table, then it would be easy to display them like this: FAR NORTH Alaska NEW ENGLAND Connecticut But if I normalize my data, then I have to figure out how to make "FAR NORTH" from Table 2 display in a row just ahead of Alaska. How do you do that? Should I ask this on a PHP forum, or is this something people do with MySQL? Or can you use either one? Thanks. --- [EMAIL PROTECTED] wrote: > Normalize, David. Normalize. Your Regions get their > own table. If a > country/state can belong to more than one region you > will need another > table to hold that association. > > I can't remember if this was ever recommended to > you but I had a chance > to re-read it recently and think this could help > you to better understand > the basic concepts of normalization. > > http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html > > Don't get caught up in the terminology he uses but > pay close attention to > how he organizes his sample data. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > David Blomstrom <[EMAIL PROTECTED]> wrote on > 11/08/2004 09:30:41 > PM: > > > I have a series of database tables focusing on the > > world's nations. There are basic tables that give > each > > nation's abbreviation and parent (e.g. Eurasia) > and > > other tables that focus on information about the > > people, government, etc. > > > > Now I want to introduce some regional headers. For > > example, current displays might look something > like > > this: > > > > Denmark > > Finland > > France > > Germany > > Portugal > > Spain > > > > Alaska > > Arizona > > Florida > > Montana > > Wyoming > > > > I want to somehow modify my MySQL tables and/or > the > > PHP scripts I use to display data so that I have > the > > option of displaying data just like above, OR like > > this: > > > > NORTHERN EUROPE > > Denmark > > Finland > > WESTERN EUROPE > > France > > Germany > > SOUTHERN EUROPE > > Portugal > > Spain > > > > FAR NORTH > > Alaska > > SOUTHWEST > > Arizona > > SOUTH > > Florida > > ROCKY MOUNTAINS > > Montana > > Wyoming > > > > You can see an example of my ultimate goal at > > > http://www.geoworld.org/about/guide/world/eur/remote.php? > > order=2&direction=0&submit=Submit > > I'm designing this page to function as a pop-up > > window that stays open as people click from nation > to > > nation. It uses a PHP script to order the nations > > alphabetically or by population or other topics. > > > > Eventually, I'd like to learn how to modify the > switch > > so that it can display nations or states with > headings > > - like ROCKY MOUNTAINS - then if you click another > > setting, the nations are rearranged and the > headings > > aren't even visible. > > > > Anyway, I just wanted to ask for tips about > > incorporating these regional headings into my > > database. My first hunch is to simply add them to > one > > of "basics" tables, like this: > > > > NAMEIDTYPE > > United States usa Nation > > Rocky Mountains rm Region > > Colorado co State > > Montana mt State > > > > That way, I can simply display the entire table as > is > > - complete with the headins - or I can use PHP to > > block every row where TYPE = Region. > > > > Does this sound like sensible plan, or are there > > better options? Thanks. > > > > __ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > protection around > > http://mail.yahoo.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subject Headings in Tables
OK; thanks! --- [EMAIL PROTECTED] wrote: > Normalize, David. Normalize. Your Regions get their > own table. If a > country/state can belong to more than one region you > will need another > table to hold that association. > > I can't remember if this was ever recommended to > you but I had a chance > to re-read it recently and think this could help > you to better understand > the basic concepts of normalization. > > http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html > > Don't get caught up in the terminology he uses but > pay close attention to > how he organizes his sample data. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > David Blomstrom <[EMAIL PROTECTED]> wrote on > 11/08/2004 09:30:41 > PM: > > > I have a series of database tables focusing on the > > world's nations. There are basic tables that give > each > > nation's abbreviation and parent (e.g. Eurasia) > and > > other tables that focus on information about the > > people, government, etc. > > > > Now I want to introduce some regional headers. For > > example, current displays might look something > like > > this: > > > > Denmark > > Finland > > France > > Germany > > Portugal > > Spain > > > > Alaska > > Arizona > > Florida > > Montana > > Wyoming > > > > I want to somehow modify my MySQL tables and/or > the > > PHP scripts I use to display data so that I have > the > > option of displaying data just like above, OR like > > this: > > > > NORTHERN EUROPE > > Denmark > > Finland > > WESTERN EUROPE > > France > > Germany > > SOUTHERN EUROPE > > Portugal > > Spain > > > > FAR NORTH > > Alaska > > SOUTHWEST > > Arizona > > SOUTH > > Florida > > ROCKY MOUNTAINS > > Montana > > Wyoming > > > > You can see an example of my ultimate goal at > > > http://www.geoworld.org/about/guide/world/eur/remote.php? > > order=2&direction=0&submit=Submit > > I'm designing this page to function as a pop-up > > window that stays open as people click from nation > to > > nation. It uses a PHP script to order the nations > > alphabetically or by population or other topics. > > > > Eventually, I'd like to learn how to modify the > switch > > so that it can display nations or states with > headings > > - like ROCKY MOUNTAINS - then if you click another > > setting, the nations are rearranged and the > headings > > aren't even visible. > > > > Anyway, I just wanted to ask for tips about > > incorporating these regional headings into my > > database. My first hunch is to simply add them to > one > > of "basics" tables, like this: > > > > NAMEIDTYPE > > United States usa Nation > > Rocky Mountains rm Region > > Colorado co State > > Montana mt State > > > > That way, I can simply display the entire table as > is > > - complete with the headins - or I can use PHP to > > block every row where TYPE = Region. > > > > Does this sound like sensible plan, or are there > > better options? Thanks. > > > > __ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > protection around > > http://mail.yahoo.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subject Headings in Tables
I have a series of database tables focusing on the world's nations. There are basic tables that give each nation's abbreviation and parent (e.g. Eurasia) and other tables that focus on information about the people, government, etc. Now I want to introduce some regional headers. For example, current displays might look something like this: Denmark Finland France Germany Portugal Spain Alaska Arizona Florida Montana Wyoming I want to somehow modify my MySQL tables and/or the PHP scripts I use to display data so that I have the option of displaying data just like above, OR like this: NORTHERN EUROPE Denmark Finland WESTERN EUROPE France Germany SOUTHERN EUROPE Portugal Spain FAR NORTH Alaska SOUTHWEST Arizona SOUTH Florida ROCKY MOUNTAINS Montana Wyoming You can see an example of my ultimate goal at http://www.geoworld.org/about/guide/world/eur/remote.php?order=2&direction=0&submit=Submit I'm designing this page to function as a pop-up window that stays open as people click from nation to nation. It uses a PHP script to order the nations alphabetically or by population or other topics. Eventually, I'd like to learn how to modify the switch so that it can display nations or states with headings - like ROCKY MOUNTAINS - then if you click another setting, the nations are rearranged and the headings aren't even visible. Anyway, I just wanted to ask for tips about incorporating these regional headings into my database. My first hunch is to simply add them to one of "basics" tables, like this: NAMEIDTYPE United States usa Nation Rocky Mountains rm Region Colorado co State Montana mt State That way, I can simply display the entire table as is - complete with the headins - or I can use PHP to block every row where TYPE = Region. Does this sound like sensible plan, or are there better options? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Databases or One?
I'm working on several websites that will be driven primarily by two databases - Geography and Animals. The Geography database will feature information about nations, provinces and states, such as capitals, population, etc. The Animals database features lots of taxonomic tables (orders, families, species, etc.), along with information about diet, distribution, etc. I would guess each database could ultimately have as many as two dozen tables or more. Some of my sites will need a third database (or extra tables in one of the existing databases). For example, I'm working on a big Symbols database table. Anyway, I thought I was getting to the point where I'd better split all of my tables into two or more databases to help me keep organized. Then I realized that it could be a lot of trouble figuring out how to connect to and manipulate two or three databases. In the long run, it might be easier to just dump everything into one big database. It occurred to me that as I learn more about MySQL, there may be database-wide operations I'll want to perform on all my Animals tables, but not on my Geography tables. If I do put everything in one table, is there some naming scheme I could use to facilitate this? In other words, if all my Animals tables feature the same prefix or suffix, would it help me perform operations that affect only the Animals tables? Thanks. __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Analyze Dreaweaver's PHP/MySQL code
--- Robb Kerr <[EMAIL PROTECTED]> wrote: > I'm trying to inform myself about exactly what > Dreamweaver's PHP/MySQL code > is doing when creating a recordset and repeat > region. Please help if you > can. > > DW MX generates the following to create a simple > recordset... > Line 1) require_once('Connections/TBA.php'); > Line 2) mysql_select_db($database_TBA, $TBA); > Line 3) $query_RS_CourseEnrollment = "SELECT * FROM > tba_courseenrollment"; > Line 4) $RS_CourseEnrollment = > mysql_query($query_RS_CourseEnrollment, > $TBA) or die(mysql_error()); > Line 5) $row_RS_CourseEnrollment = > mysql_fetch_assoc($RS_CourseEnrollment); > Line 6) $totalRows_RS_CourseEnrollment = > mysql_num_rows($RS_CourseEnrollment); > > I understand (or think I understand) the > following... > Line 1 grabs the information from an external file > to connect to the > database. > Line 2 creates the connection to the database using > the data obtained in > line 1. > Line 3 creates the actual query and saves it to a > variable. > Line 4 executes the query and saves the result to a > variable (I think). > Line 5 grabs the currently "pointed to" record > (usually the first) and > assigns its contents to a PHP Array. > Line 6 stores the number of records in the recordset > to a variable. > > In a repeat region DM MX writes... > Line 1) > Lines 2 - ?) put something on the page > Line Last) mysql_fetch_assoc($RS_CourseEnrollment)); ?> > > I understand (or think I understand) the > following... > Line 1 starts the loop. > Lines 2 through ? draws stuff on the page. > Line Last restarts the loop by grabbing the next > record in the recordset. > > Here are my questions... > 1) Do I understand what's going on? > 2) When I ECHO the variable created in Line 4 of the > connection I get > "Resource id #4". Why don't I get "Array" or > something I can read? > 3) Because the last line of the repeat region > includes > "mysql_fetch_assoc($variableDefiningTheQuery)" does > this mean that the > database is queried every time through the loop? > 4) I can't find "mysql_fetch_assoc()" in the MySQL > documentation. What's > this command do? > > I know this is a long post and a lot of quesitons. > Just trying to > understand what's going on. And, maybe your answers > will help others on the > board. > > Thanx, > -- > Robb Kerr > Digital IGUANA > Helping Digital Artists Achieve their Dreams I think some people on this list would consider that more of a PHP question than MySQL. You might get better responses on one of the Dreamweaver lists; I've asked similar questions there. Unfortunately, I can't answer your question, because I never did figure this stuff out. I'm still relatively new to PHP and MySQL, and I found it all very confusing. I finally decided to ditch Dreamweaver's PHP functions and hand code everything. I think that was a good choice. I still don't have a good perspective on everything, but I THINK Dreamweaver's PHP functions are unfamiliar to many PHP coders who don't use Dreamweaver. So if you want to use the same language, it's better to learn to speak plain PHP. But I could be mistaken. Get some feedback from other groups, and if I'm wrong, please let me know! __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]