Table planning
Hi, I have posted a question here previously regarding the query of talbles that contain duplicate entries in rows with only the id being different. My question was how to display or abtain ONLY the row with the highest id value in the result set along with all the other destinct rows in the table. For a little more background: The table hold data on properties, name, address,owner,property_code etc. What happens is that from time to time the data for a specific property is updated or changed or simply re-entered (on purpose or by mistake). The table needs to hold a record of each of these updates, erroneous or not, so I can't simply do an update when someone enters data. Now I need to pull a list of all the properties with some details like the address etc. So, obviously only the last entry for each property in the table will be of any value. I do have a date field, but sometimes the next update happens on the same day as the previous, so it won't work to test for the maximum date. Anyway, most of the replies I had all say I must create a new table and do joins etc, in my sql. Now, my real question. How are you supposed to construct a db for reading data like this. ie, it sounded to me like I was never supposed to read the data into one table but rather relational tables; making the selects possible without having to create a new table. So, I need some help in showing me how to create tables for such a system, plus some explanations on why etc. I know I can and should find these things in books, but my MySQL book is en-route from amazon, and I need some help on this rather urgently! Your help is really appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL help plz
Hi I need help with an sql string: have a table similar to this: idkeynamedate 1 123name1 date1 2 123name1 date2 3 111name2 date1 4 111name2 date2 5 123name1 date3 Now, I need sql to report the following result: name1 date3 name2 date2 thus, report all the names in the table but only the ones with the latest date. The sql MUST use the key to compare the different rows with each other and NOT the name, as the name is prone to spelling errors (ok, the key as well, but it's easier to make a typo with letters than with digits and I already have a couple of entries where the names of the entries are different. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 13
Hi I have a table that I want to add a new field to while running on the server, however, when I try to add a new field, it comes up with the error13 message, saying it doesn't have permission, yet the files and folder are all chmod 777. Can it be due to the fact that there is a permanent connection to this table from another server and that mysql is preventing any major changes? Should I just try to make a copy of the table and make the changes to it and then overwrite the old one? Is there a cleaner way of managing your tables and to make changes without having to resort to this manual way? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
reading field and data into a table from another
Hi, have two tables. One contains the names of ppl along with an id for each person and a date of the entry. The second table contains some added info for those ppl, and I would like to have one of the fields in the 2nd table to be in the first table in irder to make it easier to manage. The problem is that the order of the ppl in the two tables are not the same and it might be that there are more than one entry for a specific person ( an update of details on a later date ). Is there a quick fix sql string for this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
complicated select
Hi, I have a table that contains something like follows: id prop_name status action_date prop_type address 1 name1 ok 2001-12-12 1 addy1forname1 2 name2 ok 2001-12-13 1 addy1forname2 3 name1 ok 2001-12-13 1 addy2(new)forname1 4 name3 ok 2001-12-14 2 addy1forname3 5 name2 ok 2001-12-13 1 addy2(new)forname2 If I am looking for all prop_names of type=1 I must get the following list only: name1 with id=3 and name2 with id=5. the other name1's and name2's and name3 should not be included as they are either older or not of type 1. Also, another twist. The name1 and name2 should only be listed if their action_date is not older than 52 weeks (1 year). A bit more background to clarify. The table holds data for properties which gets updated from time to time. I have a search function on a website using php that allows the user to search for all type 1's and I only want the latest entry for each prop_name to appear on the list and only if the data is not older than a year. My existing code works fine, but it doesn't work when a new entry is made to the table ( it shows both or all of the previous entries as well). I am looking for an sql string solution here that will only return the rows I'm looking for as I have already written all the code to run through these rows and create hyperlinks etc, so I would ideally not want to change that if possible. So, basically my problem is that my current sql string returns incorrect rows along with correct ones and I would like to remove the incorrect ones by using a different sql string rather than write more if's and then's to try to sort out which rows are garbage and which should be echoed. Thanks alot. Please yell if I can make the problem more clear. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
duplicating collumn
Hi I have two tables, one contains a persons name and an ID number. The other table contains the ID number and another field. Is there a way of moving this collumn to the first table to corrlate with the ID's, ie, table 1 must now contains name, ID and another field that is specific to the person. The two tables are not chronological ie. the 1st entry in the one table is not nescessarily the first in the second table... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sorting through results
HI
This is possibly very easy, but I can't seem to get it right.
I have a table that hold some data about properties. The data gets
updated from time to time, but each time a new update is done, it
get's a new row. The only thing in some cases setting the different
entries appart is an action_date field I added that holds the date of
the entry. The reason why I make a new entry is to keep a record of each
time I receive data about a property for future reference. Now, my
problem. I need to pull the data for a specific property (prop_name)
that is a) not older than 52 weeks from the current date b) must have
success in the status field, and c) must be the latest entry in case
there are more than one entry for the same property matching all these
criteria ( thus, I could have received a new entry within the 52 week
period).
So, how would the query string look that would only return ONE set of
data for that specific property?
A possible solution from a different source suggest the following, but
I'm not sure it will work as it looks to me as if it still returns more
than one row(possibly).
$sql = select * from table where prop_name='$prop_name' and
prop_type='$prop_type' and status='success' and
action_date('.date(Y-m-d).',INTERVAL 12 MONTH group by ID order by
action_date desc);
PS, This is for a PHP page, but I guess it won't matter right?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with ODBC and BLOB
HI I installed MyODBC on an NT server to connect via the internet to my Linux box that runs mysql3.23. I have already populated tables in a db that I am trying to link to the NT server via ODBC, but I am getting errors when trying to do this. The table is created with an id field ( BIGINT (255)) , a key field varchar(16) and then a whole bunch of other fields of type text. All have Null = No yet some of the fields do have null entries (not sure if this is where the problem comes in). So, when we tried to connect, MS SQL complains about a fieldname that has no entry and quits. As soon as I temporarily put something in that field for that specific row, it goes further, to the next empty field and then complains about it. So it seems like MS SQL doesn't like the fact that I either used text as the type, or it doesn't like the fact that some fields are empty. Strangest of all, when I do a statis import in access of the same table as is, it imports it 100%. How can I fix this? Is this a problem of conversion between mysql and ms-sql? Should I fix this on the mysql side or on the ms-sql side? What is the correct type to use for my fields ( I only read normal form text field data from websites, so I though text was the correct type to use as I don't really cae if some of the fields are empty), and what should my fields be then, null or not null? I normally use phpMyAdmin to create new tables on the fly and it's default setting is Null = no, and it always worked fine for me so far. Please can someone help me to fix this, I don't want to change my field types fi not absolutely necessary due to incorrect practise on my side. I already have an entire system running on these tables and wouldn't want to go and fiddle around with field types and run the danger that some obscure sql string somewhere in my long forgotten code will now fail to add to some tables because of that, so, basically I'd like to see a ms-sql fix for this unless, like I said, you can clearly show me that I used improper protocol to create my tables from the start, and that to conform to accepted SQL standards, I should change field types... Thanks for your help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL / ODBC BLOB problems
HI I installed MyODBC on an NT server to connect via the internet to my Linux box that runs mysql3.23. I have already populated tables in a db that I am trying to link to the NT server via ODBC, but I am getting errors when trying to do this. The table is created with an id field ( BIGINT (255)) , a key field varchar(16) and then a whole bunch of other fields of type text. All have Null = No yet some of the fields do have null entries (not sure if this is where the problem comes in). So, when we tried to connect, MS SQL complains about a fieldname that has no entry and quits. As soon as I temporarily put something in that field for that specific row, it goes further, to the next empty field and then complains about it. So it seems like MS SQL doesn't like the fact that I either used text as the type, or it doesn't like the fact that some fields are empty. Strangest of all, when I do a statis import in access of the same table as is, it imports it 100%. How can I fix this? Is this a problem of conversion between mysql and ms-sql? Should I fix this on the mysql side or on the ms-sql side? What is the correct type to use for my fields ( I only read normal form text field data from websites, so I though text was the correct type to use as I don't really cae if some of the fields are empty), and what should my fields be then, null or not null? I normally use phpMyAdmin to create new tables on the fly and it's default setting is Null = no, and it always worked fine for me so far. Please can someone help me to fix this, I don't want to change my field types fi not absolutely necessary due to incorrect practise on my side. I already have an entire system running on these tables and wouldn't want to go and fiddle around with field types and run the danger that some obscure sql string somewhere in my long forgotten code will now fail to add to some tables because of that, so, basically I'd like to see a ms-sql fix for this unless, like I said, you can clearly show me that I used improper protocol to create my tables from the start, and that to conform to accepted SQL standards, I should change field types... Thanks for your help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
heavy load configuration
I have a Linux box running mysql and apache, and we are expecting quite a load on the 1st of June. We have an application form whose data will be written into a db on the same server and I would like to know what I can do to make sure things go smooth. The machine is an AMD 500, with 320MB RAM, 256MB SWAP a 20GB IDE with the OS on and a 18,2GB SCSI 1rpm for backup and location of /var/lib/mysql in order to facilitate high speed writes of the tables to disk. How will I know if my server is up to it, or should I rather say, how many consecutive users will this box be able to handle? The line shouldn't be a problem, I think it sits on a couple of 100 MB/s line. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
