Re: MySQL JOIN syntax question
On Wednesday 21 November 2001 03:01, James O'Brien wrote: I have been trying to perform a join with several tables and I am having no joy. error messages would increase the understanding of exactly what joy you aren't having ;-) Can some one send me a query (just a select * is fine) for these tables the ID's i'm using below aren't the real column names but they will do for the purpose of this email. select * from table1 left join table2 on table1.id=table2.id table2 left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 table3 left join table4 on table3.id3=table4.id3 table3 left join table5 on table3.id4=table5.id4 table3 left join table6 on table3.id5=table6.id5 table3 left join table7 on table3.id6=table7.id6 Have you tried something like this?: select * from table1 left join table2 on table1.id=table2.id left join table3 on table2.id1=table3.id2 and table2.id2=table3id2 left join table4 on table3.id3=table4.id3 left join table5 on table3.id4=table5.id4 left join table6 on table3.id5=table6.id5 left join table7 on table3.id6=table7.id6 HTH -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: problem with security and user access
On Wednesday 21 November 2001 10:10, behrens wrote: after editing the tables below , i'm still not able to access the Bisimis Database below with the user behrens, just getting the errormessage below. I've tried it with any combination of user/db/host but can't see the supposed logic error i've made,i'm stuck,anyone there to help me? thanks Have you flushed the privileges after changing the tables? FLUSH PRIVILEGES -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: strange behaviour
On Monday 19 November 2001 09:51, you wrote: Hello, started a new compiled MysqlDataBase on my local Machine everything okay sofar. added a ./bin/mysqladmin -u root password '' (password omitted) no problem I tried it several times to log in to create a database but only getting this: behrens@ws8:/home/behrens/behrens_local/mysql-3.23.44.bin cd behrens@ws8:/home/behrens mysqladmin -p create BIBLIOS; Enter password: * mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'behrens@localhost' (Using password: YES)' All permissions on the various files are for behrens, i don`t know what is wrong,newbie that i am Best regards and thanks already janB You probably haven't created a MySQL user 'behrens'. Try mysqladmin -uroot -p create BIBLIOS; to connect as the existing root user. Note: there is no correspondence between MySQL users and local system users. HTH -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Text formatting for HTML output.
On Thursday 04 October 2001 06:48, Neil Silvester wrote: Currently I am using a standard TEXTAREA/TEXTAREA field for information input to the MySQL database. When I display the allready information in the text fields to allow the user to manipulte the information, the formatting includes any carrige returns that have been entered. However when I display the text using the mysql_fetch_array the returns are no more, and instead are only displayed as a single space between the next word. I know this is due to the way that HTML reads whitespace, but how can I force the formatting of the text so it is displayed how the user intended. I have tried to use ADDSLASHES() to reformatt the variables befofe entering them into the tables, but I am unsure if this is the right method. And if so, how can I convert the \n to br? I am not sure if this is a question that I should be asking to this maillist or not, but I thought I would give it a shot. Well, why not? It is actually not a MySQL problem, because MySQL is faithfully reproducing the data input,and not a PHP problem, because PHP should be passing the text with carriage returns to the browser, and it is not a browser problem, because as you say this is the way carriage returns are usually treated in HTML. There are two solutions: either convert the carriage returns in the text for display on the fly in PHP into brs; or use pre/pre around the text in question, as this will display it exactly as intended. It would not be a good idea to convert the \n to br before insertion into the database, because these will then appear in any textarea fields. HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Hassle with standard SQL queries
On Wednesday 26 September 2001 16:34, you wrote: Howdy list! snip Nesting doesn't seem to work either: SELECT P_CODE, P_DESCRIPT, PRICE FROM INVENTRY WHERE PRICE = (SELECT MAX(PRICE)) FROM INVENTRY); - syntax error This is really weird. Why do You think it can happen? 1. Fatal Bracket Mismatch On Standard SQL Syntax 2. General RTFM Error At Client - Before posting, please check: http://www.mysql.com/manual.php (the manual) - http://www.mysql.com/doc/M/i/Missing_Sub-selects.html ;-) Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: UPDATE SELECT
On Tuesday 18 September 2001 14:14, Andrew Dixon wrote: Hi everyone. I'm trying to do the following update, which someone at Macromedia gave me, however I get a SQL error when I'm trying to use it with MySQL, any ideas on what is wrong. A general lack of support in MySQL for this kind of syntax. update general_users set rolling_total = sumUsr.rollingTotal from ( select id,sum(jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec) as rollingTotal from general_users group by id ) as sumUsr where general_users.id = sumUsr.id Take a look at the manual pages on www.mysql.com. HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Japanese text in mysql DB
On Friday 14 September 2001 12:43, Steve McKeogh wrote: How do I get Japanese text to be inputted into a mySQL database and get to pull out correctly on the page. I've tested it already by inserting some dummy Japanese text and it inputs itself into the database as complete jibberish. How can I solve this? Well, that's the first time I've ever heard of self-inserting Japanese text. If all my other data would do that ;-) AFAIK vanilla MySQL does not have any problems with 2-byte character data, even in CHAR/VARCHAR fields. You might want to find out whether your server was compiled with a specific character set. I have worked with native Japanese encodings and UTF8 in MySQL several times and any problems have been with the pre- and post-processing outside of MySQL. As you don't say what kind of Japanese text (EUC? JIS? SJIS? Unicode?) you are storing, and what method(s) you are using to store and retrieve it, it's hard to provide a solution... Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: DBI-1.18.tar.gz Can't gzip -cd
On Friday 14 September 2001 18:31, rjtalbo wrote: I have been trying to uncompress DBI-1.18.tar.gz which I downloaded together with Msql-Mysql-modules-1.2216.tar.gz Data-ShowTable-3.3.tar.gz from the MySQL down load page... I followed the direction in the DBE-1.18 README but if I use the pipe as instructed... gzip -cd DBI-1.18.tar.gz | tar xf -cd DBI-1.18 I get tar: Cannot open -cd: No such file or directory if I try w/o -cd ... gzip -cd DBI-1.18.tar.gz | tar xf DBI-1.18.tar.gz I get... tar: Hmm, this doesn't look like a tar archive tar: Skipping to next file header tar: Only read 3690 bytes from archive DBI-1.18.tar.gz tar: Error is not recoverable: exiting now broken pipe Same if I drop the last .tar.gz If I try gzip -cd DBI-1.18.tar.gz The entire archive just decompresses and flashes across the console never to disk.. Tried to uncompress on two machines same results Caldera Linux kernals 2.2.14 and 2.2.10 as you're using Linux, try tar xzf DBI-1.18.tar.gz which will unzip and unpack the whole caboodle into a directory called DBI-1.18 HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Size of DB
On Thursday 13 September 2001 03:12, Antoine E. Hall wrote: Hello, I'm new to MySQL and I was wondering if there was a command that you can issue to see the current size of a mysql database (in kb or MB)? Not in MySQL as such (unless I've missed something in the meantime). In Unix/Linux du -k /var/mysql/database_name (or wherever your database is located (*)) will get you the total size of all files for that database in kilobytes. (In Linux du -sh will produce an easier-to-read megabyte value if the total is = 1 MB). If MySQL is properly installed you will need root permissions to read the data directory. In Windows you can probably do something involving the mouse and some icon-thingies, or maybe a simple DIR at the command line. (*) If you are not sure, use the command SHOW VARIABLES or at the command line mysqladmin variables and look for 'datadir'. Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Size of DB
On Thursday 13 September 2001 15:36, Frank Fisher wrote: Ian Barwick wrote: In Windows you can probably do something involving the mouse and some icon-thingies, or maybe a simple DIR at the command line. Right-click on the MySQL database folder, selecting Properties. Under DOS, substitute your Unix command with DIR and look at the total at the end of the list, and use backslashes instead of forward slashes. And leaving out the command line switches. AFAIK the DOS DIR only shows totals in bytes (at least in NT4). Of course, you could do the icon-thingy with KDE, Gnome, etc. Yup, assuming the KDE, Gnome etc. user can read the database directory. Which means either the user is running an X-session as root (generally Not A Good Thing) or the directory rights have been changed to something other than a healthily paranoid 700. Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: mysql to oracle
On Thursday 13 September 2001 19:16, g g wrote: Hi. Is anyone aware of any tool to convert a mysql database to an oracle database? see: http://www.mysql.com/news/article-57.html Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: COUNT elements in a LIMIT query
On Friday 07 September 2001 15:37, Torgil Zechel wrote: Is it possible to get the total number of records when doing a select with LIMIT? No. As least, not in MySQL, unless I am missing something. For example, if I do: SELECT * FROM tbl LIMIT 0,10 I would like to get the total number of records in tbl along with the 10 first ones.. I need this to display showing 0 - 10 of 354, and my query is much more complex so I dont wan't to make two queries: SELECT COUNT(*) FROM tbl SELECT * FROM tbl LIMIT 0,10 COUNT is an aggregate function which works on the result set returned. You can therefore select a number of rows to be displayed and count these, or select all rows in the table and count these, but not both at once. (You can't have both your cake and eat it ;-) If UNION was supported, you could do something like SELECT field1, field2, ... fieldx LIMIT 0,10 FROM tbl UNION SELECT count(*), '', ... ''- provide x - 1 blank fields of the same datatype as 'fieldn' in the first SELECT which would provide you with the first 10 results as well as the total number of rows. This is however a pretty messy way of doing things and I would stick with two SELECT statements. BTW a simple COUNT(*) without a WHERE clause is optimized in MySQL. HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: intersection: join query...
On Friday 07 September 2001 23:37, Anthony E. wrote: i have two tables with similar structure... i want to find the intersection of data between two tables that have the same email address. I tried the following with an error (Unknown table 'USER' in where clause) the USER table is definitely there though: select count(email) from USER_TMP where USER_TMP.email = USER.email; Tell MySQL you want to access the table USER as well: SELECT COUNT(email) FROM USER_TMP, USER WHERE USER_TMP.email=USER.email (the FROM-clause contains the names of all tables you reference in the query, not just the ones you select) (...) HTH Ian Barwick -- Ian Barwick - Developer [EMAIL PROTECTED] http://www.akademie.de Luncheon meat tables with MySQL data - 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
Re: Question
On Saturday 08 September 2001 00:10, Mysql List wrote: Thanks, I already tried that, and I am only trying to insert about a dozen fields. Thanks for the response, I will wait and see if anyone else has an idea. The documentation: http://www.mysql.com/doc/C/R/CREATE_TABLE.html has the answer (but not very clearly). The AUTO_INCREMENT parameter is set on a per-table and not a per-row basis, so the definition comes after the row definitions, e.g.: CREATE TABLE mytable( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=1000; HTH Ian Barwick MaybeI guessThere's either too many datatypes or try putting like this (again, probably wont work) Auto_Increment=1000, . - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Mysql List [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Friday, September 07, 2001 4:26 PM Subject: Re: Question That didn't work either. The documentation seems to show that you have to use = 1000, but I can't get it to work. Try putting it like this :: AUTO_INCREMENT(1000), . Not sure but that should work - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Mysql List [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Friday, September 07, 2001 3:49 PM Subject: Question Hi, Why is mysql(ver 3.23.41) complaining about this: CREATE TABLE mytable(ID INT NOT NULL AUTO_INCREMENT = 1000,PRIMARY KEY (ID), I know it is not the entire statement, but mysql does not like the AUTO_INCREMENT = 1000 part, how do I do that so it is acceptable? Thanks, Chris - 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 - 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 - 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 - 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 -- Ian Barwick - Developer [EMAIL PROTECTED] http://www.akademie.de Luncheon meat tables with MySQL data - 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
Re: need help with select statements
On Thursday 30 August 2001 15:28, Andre Konopka wrote: Hi, I have, three tables (one,two, three) with the rows id/value. id is the key for all the tables. with select value from one where id=xx; select value from two where id=xx; select value from three where id=xx; I can select all values from the tables with the id=xx. How can I do this with one select statement. Notice, it's possible that there is an entry for id=xx in table one, but not in table two, or three. Of course ist's possible that there is only a value in table three. The output could be id one.value two.value three.value x1 233 2342 NULL x2 NULL 998NULL x3 33 232 23 Clear enough? A couple of LEFT JOINs should probably do the trick HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: autoincrement
On Tuesday 28 August 2001 12:36, [EMAIL PROTECTED] wrote: When you have just inserted a record into a table that autoincrements the primary key, is there a way of retrieving the id assigned at the same time? I'm using ASP to write the code in question , and I'm trying to find a better solution that looking at the max key value just before/after in code, You mean using SELECT LAST_INSERT_ID() ? and praying no other query was run at the same time. Try locking the table for writes just before your insertion, do the insert, SELECT LAST_INSERT_ID() and release the lock. This should prevent another insert being carried out before you retrieve the ID. HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: autoincrement
On Tuesday 28 August 2001 13:55, Harald Fuchs wrote: In article 01082813314101.11299@redgrave, Ian Barwick [EMAIL PROTECTED] writes: You mean using SELECT LAST_INSERT_ID() ? and praying no other query was run at the same time. Try locking the table for writes just before your insertion, do the insert, SELECT LAST_INSERT_ID() and release the lock. This should prevent another insert being carried out before you retrieve the ID. This is not necessary. LAST_INSERT_ID is client-specific; thus you get guaranteed the ID of the last INSERT issued by yourself, regardless of what other clients might do in the meantime. Aha, thanks, nice to know. I tend to assume the worst with MySQL (slight personal prejudice due to lack of referential integrity etc., please don't flame, I think it is excellent otherwise, else I would't be using it ;-) Note to self: RTFM more often... Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Selecting * from multiple tables
On Monday 27 August 2001 19:43, Dave Mittner wrote: I'm running into a snag... here's an example of the query I'm making from within Perl: SELECT * FROM table1,table2 I know it's not a nice way to do it, but I'm making an SQL webpage frontend which I'd like to support it just in case. I'm using fetchrow-hashref to pull it out and display it. The problem I'm running into is when table1 and table2 have similar column names. One of them would be overwritten in the hash reference to be replaced by the second one it encounters. So my question is this. Is there any way to force MySQL to ouput the table name with the column name instead of just the column name, ie. table1.column2. Not that I know of. What you could do is create aliases using AS, e.g. SELECT *, table2.column2 AS table2_column2 FROM table1,table2 but then you would need to know which columns to alias. Or how about using fetchrow_arrayref()? That would remove the duplicate hash key problem, although you would have to extrapolate the column names yourself. Hope your system has lots of memory to cope with that select statement on tables of any significant size ;-) HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: column_name
On Wednesday 22 August 2001 16:48, Jeremy Morano wrote: I was wondering if there was a way to retreive a column_name instead of a value in an sql select query? For example if my table has the fieldfirstname, lastname, telephone, movie1, movie2, movie3.. And my first record is JohnDoe 555- good bad bad My second record isJaneDoe 555- good goodbad I would like to know which movies Jane Doe rated good'...Is this possible and if so how? Ouch! This is possible - that's what databases are here for ;-). However, you'll want to think about normalizing your database structure. Hint: you'll need three tables. HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Aliasing
On Monday 20 August 2001 23:59, Hans Zaunere wrote: Is there a way to have MySQL label columns returned from a select query with the complete column name, in table.column format? AFAIK no. snip Sure I could alias each column, but if there is 30 columns, that's a lot of aliasing. Is there a way to alias a whole table, so each returned column is prefixed by a specified string? What I usually end up doing is setting an abbreviation for each table and prefix each column name with this abbreviation, e.g. (*) CREATE TABLE customer ( c_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c_name VARCHAR(100) ) CREATE TABLE customer_address ( ca_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ca_c_id INT UNSIGNED NOT NULL, ca_street VARCHAR(100) ) SELECT c.*, ca.* FROM customer c, customer_address ca WHERE ca.ca_c_id = c.c_id The table abbreviations end up doubled :-( but this guarantees the application gets unique table names and avoids the hassle of aliases. HTH Ian Barwick (*) Disclaimer: example code, may not be accurate, use at own risk ;-) -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Formatting the DATE field on a web page
Hi As the Perl hackers say, tmtowtdt (there's more than one way to do this) - you are free to do format your dates wherever you like. If you're that way inclined you could even do the formatting on the client side in JavaScript ;-) IIRC correctly PHP offers a lot of inbuilt date formatting wotsits. In SQL (at least MySQL, database developers also believe tmtowtdt ;-) you can use the DATE_FORMAT function, see: http://www.mysql.com/doc/D/a/Date_and_time_functions.html HTH Ian Barwick [EMAIL PROTECTED] On Monday 20 August 2001 18:02, pc wrote: Hi people I am using mySQL and PHP for my web page database management thingy. I've managed to get my date field from my Table into my page ($myDate). I was wondering, how would I format the date from -MM-DD format to something a bit nicer like DD/MM/YY ? Is this meant to be a SQL side thing or a server side language formatting thingy? -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: Problem with max() on select
On Friday 17 August 2001 07:13, Jim Lynn wrote: I have a database of bids on auctions that I keep separate from the auction information. The problem comes when I attempt to generate a list of auctions with highest bid information. I set the database for bids like this: ANum, varchar(20), the auction number Bidders, int(4), number of bidders at time of this bid Bidder, varchar(50), username of current bidder CPrice, decimal(9,2), current bid Typical records: 12345 1 bidder1 20.00 12345 2 bidder2 25.00 12345 3 bidder3 30.00 I tried: select ANum, max(Bidders), Bidder, CPrice from Bids group by ANum and it returns: 12345 3 bidder1 20.00 which gives me the higest bid number, but the information from the first bid in the database. How can I retrieve the records that reflect the highest bids for each auction??? AFAIK this can only be done in a single SQL statement with a subselect, eg: SELECT * FROM bids WHERE Bidder IN (SELECT MAX(Bidders) FROM bids GROUP BY ANum) Unfortunately MySQL does not yet support subselect (see: http://www.mysql.com/doc/M/i/Missing_Sub-selects.html ), so the only way round this I can see would be on the application side, e.g. SELECT MAX(Bidders), Bidder FROM bids GROUP BY ANum then step through the result set selecting the row for each auction individually using Bidder as the selection criterium. HTH in some way. (If there is a more elegant solution to this problem in MySQL I'd love to hear it) Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: sql query problem
On Thursday 16 August 2001 10:52, Fabian Groene wrote: (snip) My problem is quite simple indeed and only refers to the SQL query: There is a table with quite a lot of columns. But only two of them are really important for my query: One field is a date field and the other one is a group name. My aim is to create a query that only selects the item with the most recent date. That is simple and can be handled by max(date). But only the most recent item from each group shall be given as a result of my query. That's also fine. If I understand you correctly you have a table somewhat like this: +-++---+ | item| gdate | gname | +-++---+ | this| 2001-10-01 | ABC | | that| 2001-01-01 | ABC | | the | 2000-09-01 | Hello | | other | 2000-06-01 | Hello | | no idea | 2000-10-10 | Hello | +-++---+ (but probably more complex). For each group with in the column 'gname' you want to extract the item (or what other data you have) with the most recent date. So your desired result would be: +-++---+ | item| gdate | gname | +-++---+ | this| 2001-10-01 | ABC | | no idea | 2000-10-10 | Hello | +-++---+ (Or am I missing something?) With the query select groupname,max(date) from databasename Group By groupname I got what I wanted and was happy. For the above example the query and result set looks like this: mysql select gname, max(gdate) from groups group by gname; +---++ | gname | max(gdate) | +---++ | ABC | 2001-10-01 | | Hello | 2000-10-10 | +---++ Which, I agree, _looks_ right. BUT: As soon as I told the database to read out more fields it no longer worked to get the most recent item from the particular groups. Instead there were many items having the same group which I wanted to supress with max(date) My query was in the form of select groupname,field1,fieldn,max(date) from databasename Group By groupname, field1,fieldn So you ended up with something like this? mysql select gname, max(gdate), item, gdate from groups group by gname, item, gdate; +---++-++ | gname | max(gdate) | item| gdate | +---++-++ | ABC | 2001-01-01 | that| 2001-01-01 | | ABC | 2001-10-01 | this| 2001-10-01 | | Hello | 2000-10-10 | no idea | 2000-10-10 | | Hello | 2000-06-01 | other | 2000-06-01 | | Hello | 2000-09-01 | the | 2000-09-01 | +---++-++ Has anyone an idea how to overcome this problem? Yes and no. An obvious (but misleading solution) would be this: mysql select gname, max(gdate), item, gdate from groups group by gname; +---++--++ | gname | max(gdate) | item | gdate | +---++--++ | ABC | 2001-10-01 | this | 2001-10-01 | | Hello | 2000-10-10 | the | 2000-09-01 | +---++--++ (not sure whether that's valid SQL; applications like Oracle or PostgreSQL certainly would't accept the statement as it is). Looks almost right, but for the group Hello we have completely the wrong item and date :-( Unfortunately I don't think what you want to do can be done in MySQL. It's something I've run into once or twice and haven't been able to resolve. Thus far I've coded round it on the application side. Ideas anyone? The usual solution would be a subselect a la: select gname, item, gdate from groups where gdate in (select max(gdate) from groups group by gname) Alas no subselects yet in MySQL (see: http://www.mysql.com/doc/M/i/Missing_Sub-selects.html ) HTH in some way anyway Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: syntax error in SQL query that works when hard-coded in?
On Sunday 12 August 2001 01:01, Bennett Haselton wrote:
I've written a CGI script that runs a database query on a MySQL database (I
know that's risky -- it's password-protected though, for what it's
worth). (...)
It's a free world ;-)
When I run the script with the first of these two lines commented out, and
the second one uncommented:
#my $sql_query = $q-param('sql_query');
my $sql_query = SELECT * FROM newssites_dynamicdata;;
^
(rest of mail snipped)
Try leaving out the trailing semicolon from all queries.
Semicolons are only really useful when using the mysql client, where they
signify the end of a statement (alternatively you can use \g). In DBI
statements they will, more likely than not, cause errors. And as they aren't
in any way necessary, save yourself the trouble they might cause...
HTH
Ian Barwick
--
Ian Barwick - Developer - [EMAIL PROTECTED]
akademie.de asp GmbH - http://www.akademie.de
To query tables in a MySQL database is more fun than eating spam
-
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
Re: Add Character to Output
On Thursday 09 August 2001 17:58, Bernhard Doebler wrote: I have a select statement and want to add a Percent sign to the output of one expression. Do I have to use Concat-function or is there another way (for only one character)? AFAIK concat is the only way in MySQL, viz: SELECT CONCAT(vat, '%') AS vat_pc FROM price You could of course keep a copy of the numeric value with an added %-sign in a seperate text field, but I'm sure that would only complicate things further ;-) Now if only MySQL supported VIEWs ... (*) HTH Ian Barwick (*) See: http://www.mysql.com/doc/M/i/Missing_Views.html -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - 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
Re: INET_ATON
Hi On Tuesday 07 August 2001 07:23, mike cullerton wrote: i just tried using INET_ATON() and INET_NTOA() and i keep getting syntax errors near '(100.100.100.100)' at line 1 no manner of quoting that i can think of solves this. i tried the examples at the bottom of http://www.mysql.com/doc/M/i/Miscellaneous_functions.html and they got the same error. am i missing something easy here? is this a known issue? perhaps a version thing? i am running 3.22.32. Definitely a version thing, works fine on 3.23.37 (but doesn't on 3.22.32 - I just tried ;-) HTH Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de - 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
