Jump to content


Photo

Select all but - is it possible


  • Please log in to reply
4 replies to this topic

#1 sanosuke001

sanosuke001
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 20 September 2006 - 08:36 PM

I have a table with probably 200 columns. I want to view all but a handful of them. Is there a way without typing them all out in a select col1, col2 ... col200 from table; kinda way?

Or better yet, how can I compare two different databases. What I am trying to do is see the differences between two databases and so far, the only thing I can think of is to print them all out to a text file and then run a diff on them. An answer for either would be awesome. Thanks for the help!


Stephen

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 12:40 AM

Not really... but you could DESCRIBE the table in PHP, get back a hash of column names, and then remove the ones you don't want, and have the script compose the desired column list.  Same goes for comparisons.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 21 September 2006 - 01:46 AM

another idea, and i'm sure fenway will have a better idea whether or not this is optimal, but if you're doing a ton of queries like that, why not split your table into two so that the most used columns are in one table? i know it's not really normalized, but the other option i can think of is to create a view with only those columns that you're wanting to select, then you only have to select * from the view.

fenway, would that be a reasonable solution when you're working with so many columns?
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 sanosuke001

sanosuke001
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 21 September 2006 - 12:36 PM

I'm not using it all that often. Even with a view, I'd have to type it all out. What I am doing is we have a device that stores patient data in a database and once in a while an error creeps into the DB. WE have to fix it and thats easy. The hard part is determining if any of the data was changed. I know its very unlikely that an update would change something other than what we told it to, but its patient data and we want to be certain. Also, we're using SQLite so I don't even know if there are views in it...


Thanks for the help though.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 01:29 PM

another idea, and i'm sure fenway will have a better idea whether or not this is optimal, but if you're doing a ton of queries like that, why not split your table into two so that the most used columns are in one table? i know it's not really normalized, but the other option i can think of is to create a view with only those columns that you're wanting to select, then you only have to select * from the view.

fenway, would that be a reasonable solution when you're working with so many columns?

There's nothing wrong with splitting the table horizontally, and in fact with InnoDB, it often makes a lot of sense... but views have their own issues (esp. with indexing), so they have many limitations.  Plus, it doesn't sound like it's going to be in constant use.

I would still recommend the dynamic column listing.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users