ordering search results
Can't find anything on the web that deals with my problem(s). I have to display thousands of book listings by title, sub_title with 10 books per page. The php/mysql code works fine - except: ASC or DESC does not change one iota. I have checked by commandline and find that it is not working at all how I would expect. From commandline, using just title and switching between ASC DESC give totally different results rather than displaying the same data in reverse order. The display is, as mentioned above, 10 books per output page: so, from what appears to me, the ordering seems to be done on the entire db not just on the search results (this is basically from a SELECT statement). Furthermore, not all the data is in 1 table; authors, categories publishers are in separate tables because of 1 to many many to 1 relationships. Still another problem is the use of a number of foreign languages which have those strange accent on many letters that do not order very well. Now, that I have spewed out my problems, would it be possible that there is someone out there who could suggest how to go about figuring this out? Thanks in advance. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ordering search results
Here's the query: SELECT * FROM book ORDER BY $sort $dir LIMIT $offset, $records_per_page; $sort == 'title, sub_title'; $dir==ASC (or DESC); $offset== (any multiple of 10); $records_per_page== 10; I could let the user change these, but I decided it is simpler to keep it at 10 books/page. Only the $dir is user settable. The character set is utf8-general; (I just went through some self-torture changing all those foreign curlicues to :cutesy_stuff; fortunately, the mysql replace() function helped - but six languages have quite a few weird accents. The title field can be fairly long - 182 chars; sub_title is 128, but it looks like I have to lengthen that too; found some truncated inputs. Another problem is the Thes - how do you exclude them from the ordering. Also: one output with just title for field returned a series of The Art.., The Birds..., The Birth...etc. in ASC; whereas DESC returned: Boats, Black Cumin, Birds of..., Biological..., Bioarchaeology.., Avaris... etc. Darryle Steplight wrote: You might have to change the collation you are currently using to one that best match the language of those weird accents you are referring too. That's part of the reason you may be getting unexpected results with your ORDER BY statement. Also, can you show us your select statements? On Fri, Jul 17, 2009 at 11:06 AM, PJaf.gour...@videotron.ca wrote: Can't find anything on the web that deals with my problem(s). I have to display thousands of book listings by title, sub_title with 10 books per page. The php/mysql code works fine - except: ASC or DESC does not change one iota. I have checked by commandline and find that it is not working at all how I would expect. From commandline, using just title and switching between ASC DESC give totally different results rather than displaying the same data in reverse order. The display is, as mentioned above, 10 books per output page: so, from what appears to me, the ordering seems to be done on the entire db not just on the search results (this is basically from a SELECT statement). Furthermore, not all the data is in 1 table; authors, categories publishers are in separate tables because of 1 to many many to 1 relationships. Still another problem is the use of a number of foreign languages which have those strange accent on many letters that do not order very well. Now, that I have spewed out my problems, would it be possible that there is someone out there who could suggest how to go about figuring this out? Thanks in advance. -- Herv� Kempf: Pour sauver la plan�te, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com � http://www.ptahhotep.com � http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: � �http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ignore accents in order by
Is there a way to order lists while ignoring the accents? So far, I have found nothing simple; and I need to keep the accents for output. The language is French (and québécois) :-) TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ignore accents in order by
Isart Montane wrote: Hi, I'm not having any problem on my local computer mysql select text,text2 from table1 order by text2 desc; +--+---+ | text | text2 | +--+---+ | a   |    1 | | �   |    0 | +--+---+ mysqlselect text,text2 from table1 order by text2 desc; +--+---+ | text | text2 | +--+---+ | �   |    1 | | a   |    0 | +--+---+ What Collation are you using? can you send us the table schema? thx! Isart On Thu, Jun 11, 2009 at 4:48 PM, PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca wrote: Is there a way to order lists while ignoring the accents? So far, I have found nothing simple; and I need to keep the accents for output. The language is French (and québécois) :-) TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com mailto:p...@ptahhotep.com  http://www.ptahhotep.com  http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:   http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com Let me put it this way, I am not having the problem. The problem seems to be withthe way that character encoding is set up on the internet - as confused and inconsistent as most everything else. You can put whatever charset you want in the header, in the collations in your database, your htmls... you see already that the options start to multiply rapidly... without even considering the browsers. So, I have tried about all combinations possible and there is no one way to implement display and use of accents. UTF-8 does not handle them very well at all; iso-8895-1 doesn't either; you can set the coding on your browser to whatever you want - when you update or reload the file the little black diamond devils come back or turn into little blank squares on IE8... so, do it any way you like... anarchy prevails! ;-) -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can a MyISAM DB hold /everything/?
Scott Haneda wrote: Unless you have very good reason to store binary data like an image in your database, do not. It may work well for a time, but always be prepared that your system will grow. If it grows a good deal relative to your hardware, and users, and connections etc, you will always be in a race to throw more hardware at the problem. In a simplistic terms, an image is a file, files should be stored on a file system. You can do some very nice and simple things later on to distribute image loads. If you store the path to the image, so example, images/date/time.jpg, and then need to grow your serving needs, you could adjust your code in the http request to call images.example.com as the hostname. You could set images.example.com to return round robin DNS records. Now all you have to do is mirror or sync your images directory to any number of high performance http servers. That is just one example of how I have avoided dealing with binary file load in MySql, or any database for that matter. Even a reltively small image stored in a database can become a lot of data very fast. You will also find it can be inefficient to fetch that data and show it to the end user. At the very least, you have to request the data, read it, rebuild it into a form that can be displayed to a user, which is a lot of work, compared to no work at all if just calling it from the file system. Your question about which is faster, always will be on disk binary storage, with the exception of deletes. Deletes will happen in near the same time, though you do have to locate the record to delete. This probably still has no impact, since you will locate on an index. Hope that was helpful. On May 27, 2009, at 9:50 AM, Pete Wilson wrote: I am new to MySQL and just laying out what I hope will be just one db that holds all the info for a list of forged or machined metal parts. Let's say there are 10,000 such parts, each with a unique part number. That part number is the primary index. Each part has at least one, and maybe several, accompanying photos. And each part has at least one, and perhaps as many as five, human-language descriptions. These descriptions might be as long as 5,000 characters each. I believe I have the choice of: 1. Storing everything -- photos and descriptions included -- in the database; or 2. Holding some info in the database; and storing photos and descriptions in normal-type disk files apart from the database, with names based on part numbers. So my questions: 1. Which scheme is faster and less resource-hogging in reading, updating, adding, and deleting? 2. I understand there are problems in storing photos in the db. Can one overcome these problems somehow? Could you clarify/expand on this a bit - I am setting up a site where I expect to have a lot of images, both still and flvs, and a lot of recipes (including ingredients, procedures ad text : ;images). I am storing the images in /images directory. If the amount of images gets rather large, you are suggesting to store them on another server, right? Now, with a lot of recipes, I understand that I should be storing them also on another server; and perhaps using XML to store the recipes. Does that sound like I have understood your advice? TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
dumb crash
Hydro Quebec just f***ed my server just as I was booting up three machines; XP is ok, FreeBSD 7.1 is the one with mysql problem, FreeBSD 4.10 - don't know, but boots ok. Result: can't access database. One table seems to abort mysqld. PhpMyAdmin connects to all databases except one. mysql CHECK TABLE producer; ERROR 2006 (HY000): Mysql server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: theproblem-one Error 2006 (HY000: Mysql server has gone away ERROR 2002 (HY000): Can't connect to local lMySQL server through socket '/tmp/mysql.sock' (61) ERROR: Can't connect to the server mysql ps shows mysqls is running in safe mode Can't find anything on googie I've tried SELECT INTO OUTFILE - same errors... WEBMIN shows all dbs except the fro producer and phpMyAdmin resets to login page when trying to access producer table. Any suggestions or bad experiences solved? -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - solved
Michael Dykman wrote: On Fri, May 22, 2009 at 12:26 AM, PJ af.gour...@videotron.ca wrote: Michael Dykman wrote: On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. I would suggest that the primary key is imoprtant. All relational tables need a primary key and, in this particular case, the primary key is what is preventing you from creating duplicate rows. If anything needs to go: KEY `fk_book_categories_books` (`bookID`), bookID, being the first part of your compound primary key, is effectively indexed already. The key listed above is quite unnecessary. Actually, that key (book_categories.bookID) references book.id; book_categories.categories_id references categories.id. Is'nt it necessary for both to have foreigh keys? Things so far are working fine... -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
cannot alter table - rather urgent
I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - solved
Michael Dykman wrote: On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Carlos Proal wrote: The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos On 5/14/2009 5:55 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... Pardon, for butting in, but are you seriou? unix slut ? My first impression based on that would be, man you've been hacked! :-D -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] How to deal with identical fields in db
Tom Worster wrote: On 5/5/09 4:42 PM, Richard S. Crawford rscrawf...@mossroot.com wrote: On Tue, May 5, 2009 at 1:34 PM, PJ af.gour...@videotron.ca wrote: I'm coming up with a bit of a quandry: how to enter and retrieve an identical book title with different authors. It is rather unbelievable what contortions one finds as authors :-( like editors, associations and then the unknowns and anon y mouses. I suppose one has to get really creative... don't forget to consider the handling of anthologies :-) Well, that usually comes under editors and I have a couple of options where to enter that info: sub_title or description fields and I can always add a nbsp; for author first_name and Various or whatever for last_name :-) Anyone for tea? yes please, i'd love some. What I've done for this sort of project in the past was create separate tables for authors, books, and author relationships (e.g., author, translator, editor), then linking tables for each of those. You seriously want to do some normalization on this task; otherwise, you end up with a giant table of books, with multiple rows duplicating the title of the book, leading to a huge books table, and nobody wants that. i have a db with 10s of millions of artists, disks, songs etc. i've tried it both ways. and after 5 years working with it i still can't make up my mind which way i prefer it. i keep finding pros and cons to each approach that differ depending on what functionality i'm programming. i will never have a simple answer. so i can't help answer the original question other than to say that, for me, personally, in my opinion, i don't accept the dogma that normal forms are always good for you. they might be. it depends. it's like being dogmatic about specific foods without taking the overall diet and lifestyle into account. despite the simple dogma some may espouse, whether or not a big mac with fries is bad for you depends on many factors. in any case, it's amazing what you can do these days with one huge table and some well chosen indexes. and it's amazing how mind bending it can get when joining 5 data tables using 3 join tables. ain't that the truth ! good luck, phil. Thank you guys, for the input. Never thought so many would help so few(little me). ;-) I'm really just a little shorter than BG at 6'5 :-D Actually, I started out and still am with the db normalized. It all works quite well, it's just frustrating to have to go through all the contortions to check things. I started out with just checking the title, then had to add a check to the sub_title, (already have a check for author) but now have to add another to go with the specific book... oh, well... all a part of the learning process. :-) -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A good US Hosting Site?
I've been using www.host45.com for some years. linux and they've always been reliable. :-) -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to join tables on multiple conditions for same column
I am trying to select names from a list which depends on another table for their ranking. I want to retrieve all names that start with a Capital letter in last_name and ranking of 1 and any other names that have a ranking of 2 but are both related to a third table. tables: book(id,...etc.), author(id,first_name,last_name), book_author(authID,bookID,ordinal) I can not figure out how this could be done without resorting to a separate query. I would like to do it all with just 1 query; unless it is more efficient to do it with several queries? The code below works fine for ranking of 1 but I can't get the results I want. I suspect that I need to a subquery but I haven't figured out just how to formulate it. Any suggestions would be welcome. SELECT b.*, c.publisher, a.first_name, a.last_name, ab.ordinal, CONCAT(first_name, ' ', last_name) AS Author FROM book AS b LEFT JOIN book_publisher AS abc ON b.id = abc.bookID LEFT JOIN publishers AS c ON abc.publishers_id = c.id LEFT JOIN book_author AS ab ON b.id = ab.bookID LEFT JOIN author AS a ON ab.authID = a.id WHERE LEFT(last_name, 1 ) = '$Auth' // $Auth = any capital letter, as A,B,... ORDER BY $sort $dir LIMIT $offset, $records_per_page ; This returns an array with all the correct data so I can echo either Author or concat the first_name last_name fields. But I cannot figure out how to get the ordinal 2 authors. -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how do I select multiple conditions from 1 table column?
I am trying to select all books whose authors last names begin with I, J or K. I have 3 tables: book, author and book_author. The following query works with one condition but not with three. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I')); This does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I' LEFT(last_name, 1 ) = 'J' LEFT(last_name, 1 ) = 'K')) ; But this produces irrational results - there are no author names with the last names starting with I, J or K. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')) ; I'm a little lost here. Could somebody explain, please? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how do I select multiple conditions from 1 table column?
David Giragosian wrote: On Wed, Apr 1, 2009 at 1:27 PM, PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca wrote: I am trying to select all books whose authors last names begin with I, J or K. I have 3 tables: book, author and book_author. The following query works with one condition but not with three. SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id http://author.id/     FROM author WHERE LEFT(last_name, 1 ) = 'I')); This does not work: SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id http://author.id/     FROM author WHERE LEFT(last_name, 1 ) = 'I' LEFT(last_name, 1 ) = 'J' LEFT(last_name, 1 ) = 'K')) ; But this produces irrational results - there are no author names with the last names starting with I, J or K. SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id http://author.id/     FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')) ; I'm a little lost here. Could somebody explain, please? Maybe the LIKE operator would be sufficient:  SELECT * from book  WHERE last_name LIKE I% OR last_name LIKE J% OR last_name LIKE K%;  David  Sorry bout it all. Ifigured it out and it is the OR that was not there = the AND just plain does not work! Thanks, though. -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls
mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. Somewhere out there, Achilles is gaining on the turtle -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 11:27 -0500, PJ wrote:
Jerry Schwartz wrote:
Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.
By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
[JS] Are you looking for something like LAST_INSERT_ID()? If you
INSERT a
record that has an auto-increment field, you can retrieve the value
that got
inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so
you'll always have your own value. You can then save it to reuse,
either
as a session variable or (more easily) as a hidden field on your form.
Thanks, Jerry,
You hit the nail on the head.:)
To refine my problem (and reduce my ignorance),here's what is happening
on the form page:
There is a series of INSERTs. The first inserts all the columns of
book table except for the id, which I do not specify as it if
auto-insert.
In subsequent tables I have to reference the book.id (for transitional
tables like book_author(refers authors to book) etc.
If I understand it correctly, I must retrieve (SELECT
LAST_INSERT_ID()) after the first INSERT and before the following
insert; and save the id as a string ($id)...e.g. $sql = SELECT
LAST_INSERT_ID() AS $id
I need clarification on the AS $id - should this be simply id(does
this have to be turned into a value into $id or does $id contain the
value? And how do I retrieve it to use the returned value for the next
$sql = INSERT ... - in other words, is the id or $id available for the
next directive or do I have to do something like $id = id?
I'm trying to figure this out with some trials but my insert does not
work from a php file - but it works from command-line... that's another
post.
Here's how I mostly do it (albeit simplified):
$query = INSERT INTO `sometable`(`title`,`content`)
VALUES('$title','$content');
$result = mysql_query($query);
$autoId = mysql_insert_id($result);
$query = INSERT INTO `another_table`(`link_id`,`value`)
VALUES($autoId,'$value');
$result = mysql_query($query);
No need to call another query to retrieve the last inserted id, as it is
tied to the last query executed within this session.
Ash
www.ashleysheridan.co.uk
For some reason or other $autoId = mysql_insert_id($result); just does
not work for me... Yet some of the data is inserted correctly...
I did find that it does not work on tables that are empty... so you
can't start with an empty table. I entered data it still did not work.
I tried on another duplicate database... doesn't work.
I have checked double checked the database, I have added checks to see
what is returned and the returns are 0 or null - as I get different
responses for slightly different functions.
sessions is on
mysql is 5.1.28
php5
here's what is parsed:
else { $sql1 = INSERT INTO book
( title, sub_title, descr, comment, bk_cover,
copyright, ISBN, language, sellers )
VALUES
('$titleIN', '$sub_titleIN', '$descrIN',
'$commentIN', '$bk_coverIN', '$copyrightIN',
'$ISBNIN', '$languageIN', '$sellersIN');
$result1 = mysql_query($sql1, $db);
$autoid = mysql_insert_id($result1);
$sql2 = INSERT INTO author (first_name, last_name) VALUES
('$first_nameIN', '$last_nameIN');
$result2 = mysql_query($sql2, $db);
$authorID = mysql_insert_id($result2);
$sql2a = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$authorID', '$autoid', '1');
$result2a = mysql_query($sql2a, $db);
$sql2b = INSERT INTO author (first_name, last_name) VALUES
('$first_name2IN', '$last_name2IN');
$result2b = mysql_query($sql2b, $db);
$author2ID = mysql_insert_id($result2b);
$sql2c = INSERT INTO book_author (authID, bookID, ordinal)
VALUES ( '$author2ID', '$autoid', '2');
$result2c = mysql_query($sql2c, $db);
$sql3 = INSERT INTO publishers (publisher) VALUES
('$publisherIN');
$result3 = mysql_query($sql3, $db);
$publisherID = mysql_insert_id($result3);
$sql3a = INSERT INTO book_publisher (bookID, publishers_id)
VALUES ( '$autoid', '$publisherID' );
$result3a = mysql_query($sql3a, $db);
foreach($_POST['categoriesIN'] as $category){
$sql4 = INSERT INTO book_categories (book_id, categories_id)
VALUES ($autoid, $category);
$result4 = mysql_query($sql4,$db);
}
echo $autoid; // shows: blank
echo $authorID; // shows: blank
echo $author2ID; // shows: blank
echo $publisherID; // shows: blank
echo brautoid
how to sanitize mysql_connect?
I hope this is the right list as it concerns mysql and php ???
I just realized that an open connection is necessary to
usemysql_real_escape_string...So how do you sanitize an include page
used to open a connection
This is the page
// db1.php
// SQL login parameters for local environment
$local_dbhost = localhost;// normally localhost
$local_dbuser = ;// your local database user name
$local_dbpass = ;// your local database password
$local_dbname = ;// your local database name
// SQL remote parameters for remote environment (ex: nomonthlyfees)
$remote_dbhost= localhost;// normally localhost
$remote_dbuser = ;// your remote database user name
$remote_dbpass = ;// your remote database password
$remote_dbname = ;// your remote database name
// Local server address
$LOCAL_SERVER = 127.0.0.1;
// CONNECT to DATABASE
if ($_SERVER[REMOTE_ADDR] == $LOCAL_SERVER) {
$dbhost = $local_dbhost;
$dbuser = $local_dbuser;
$dbpass = $local_dbpass;
$dbname = $local_dbname;
}
else {
$dbhost = $remote_dbhost;
$dbuser = $remote_dbuser;
$dbpass = $remote_dbpass;
$dbname = $remote_dbname;
}
$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname,$db);
echo $dbname;
echo br;
echo $dbhost;
echo $dbuser;
echo $dbpass;
if (!$db) {
echo( PUnable to connect to the .
database server at this time./P );
exit();
}
// Select the database
if (! mysql_select_db(biblane) ) {
echo( PUnable to locate the biblane .
database at this time./P );
exit();
}
?
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
whoa!
What is this supposed to mean from the manual: The use of |mysql_num_rows()| http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html depends on whether you use |mysql_store_result()| http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html or |mysql_use_result()| http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html to return the result set |mysql_num_rows()| http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html is intended for use with statements that return a result set, such as |SELECT| http://dev.mysql.com/doc/refman/5.0/en/select.html. Does this mean you have to use mysql_store_result() before using mysql_num_rows() ? kind of doesn't make sense to have to do that. And there are no clear cut examples or explanations... I do not wish to piss-and-moan but I do find that there is a lot to be desired in the manual; things that are very ;contradictory and/ or unclear and certainly lacking in examples. And please, somebody guide me to some tutorial or something where I can learn to set up proper error checking so you guys don't have to listen to my problems. :'( -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
is there another way
Is there another way to determine what the query found? The input is a
string whatever.
I'm looking for a return of a string or null or nuber of rows or
something that will permit to to channel the execution of the file.
The snippet below is good for (there is a match) and (there is no match)
but I get the feeling that there must be a quicker way to determine if
the $result is positive or negative. ??? :-\
while ( $row = mysql_fetch_array($result) ) {
echo(P . $row[title] . /P);
}
if ($row[title] == )
echo (Empty!)
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Jerry Schwartz wrote: Being rather new to all this, I understood from the MySql manual that the auto_increment is to b e used immediately after an insertion not intermittently. My application is for administrators (the site owner designates) to update the database from and administration directory, accessed by user/password login... so there's really very little possibility of 2 people accessing at the same time. By using MAX + 1 I keep the id number in the $idIn and can reuse it in other INSERTS [JS] Are you looking for something like LAST_INSERT_ID()? If you INSERT a record that has an auto-increment field, you can retrieve the value that got inserted with SELECT LAST_INSERT_ID(). It is connection-specific, so you'll always have your own value. You can then save it to reuse, either as a session variable or (more easily) as a hidden field on your form. Thanks, Jerry, You hit the nail on the head.:) To refine my problem (and reduce my ignorance),here's what is happening on the form page: There is a series of INSERTs. The first inserts all the columns of book table except for the id, which I do not specify as it if auto-insert. In subsequent tables I have to reference the book.id (for transitional tables like book_author(refers authors to book) etc. If I understand it correctly, I must retrieve (SELECT LAST_INSERT_ID()) after the first INSERT and before the following insert; and save the id as a string ($id)...e.g. $sql = SELECT LAST_INSERT_ID() AS $id I need clarification on the AS $id - should this be simply id(does this have to be turned into a value into $id or does $id contain the value? And how do I retrieve it to use the returned value for the next $sql = INSERT ... - in other words, is the id or $id available for the next directive or do I have to do something like $id = id? I'm trying to figure this out with some trials but my insert does not work from a php file - but it works from command-line... that's another post. -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
catch the error
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
It is commented out because I am using mysql_connect
I don't think it would be good to use both, since the db1 references
another db. But even when I use the db1.php and change the database and
table, I get the same error message.
But what I did miss is my typo in What is wrond with this file? :-)
Hi PJ,
Could it be that you have //include (lib/db1.php); commented
out? Try uncommenting that line and see what happens. The error
message will always print because the query is never executing
properly if you have the db connections file commented out.
On Thu, Feb 26, 2009 at 12:28 PM, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] catch the error
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 12:28 -0500, PJ wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php); // Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
I'd say it was the way you are trying to connect to your database. This
is how it's done:
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'database_name';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
You see, first you have to cerate a connection to the database server,
then you have to select your database on that connection. In your
example, 'biggie' is the name of a server where your database resides,
and 'test', well, what can I say? This 4th parameter should be a boolean
indicating whether or not a new connection should be made upon
successive calls to mysql_connect.
Ash
www.ashleysheridan.co.uk
OK, I see my error...understood and fixed... but it still does not work.
But I did have an error - the include was wrong - missing ../
Something is till amiss... the include configuration works, this does not?
Why?
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'myuser';
$db_pass = 'my_pwd';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
Jim Lyons wrote:
what's the error message?
it's in the script/// Error performing query: of Error performing 1st
query: - whatever I input.
But I had an error in the include location... that's fixed and it works,
but not the rest as corrected:
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
On Thu, Feb 26, 2009 at 11:46 AM, PJ af.gour...@videotron.ca
mailto:af.gour...@videotron.ca wrote:
It is commented out because I am using mysql_connect
I don't think it would be good to use both, since the db1 references
another db. But even when I use the db1.php and change the
database and
table, I get the same error message.
--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
[snip]
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
Darryle Steplight wrote:
ok, well if that's the case then do this
$db = mysql_connect('biggie', 'user', 'password', 'test');
Ashley pointed out that the 4th parameter is not right - belongs in
mysql_select_db. Here it is corrected: (but it still does not work)
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'my_user';
$db_pass = 'my_pass';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
That should fix the problem.
On Thu, Feb 26, 2009 at 12:46 PM, PJ af.gour...@videotron.ca wrote:
It is commented out because I am using mysql_connect
I don't think it would be good to use both, since the db1 references
another db. But even when I use the db1.php and change the database and
table, I get the same error message.
But what I did miss is my typo in What is wrond with this file? :-)
Hi PJ,
Could it be that you have //include (lib/db1.php); commented
out? Try uncommenting that line and see what happens. The error
message will always print because the query is never executing
properly if you have the db connections file commented out.
On Thu, Feb 26, 2009 at 12:28 PM, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
Jerry Schwartz wrote:
-Original Message-
From: PJ [mailto:af.gour...@videotron.ca]
Sent: Thursday, February 26, 2009 12:28 PM
To: php-gene...@lists.php.net; MySql
Subject: catch the error
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
[JS] You need
$db = mysql_connect('biggie', 'user', 'password', 'test');
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the-
infoshop.com
I think the problem here has been that this is such a basic operation
and most of us just are too busy with more complicated stuff...that we
didn't catch it...
?
//include (../lib/db1.php);// Connect to database
$db_host = '';
$db_user = '';
$db_pass = '';
$db_name = '';
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 3st query: .
mysql_error() . /P);
}
echo $sql1;
echo br /;
echo $db_select;
exit();
?
anyway, I am learning a lot...
thanks, guys... you're all great...
I have lots more coming... :-D
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Re: catch the error
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 13:34 -0500, Darryle Steplight wrote:
Hi PJ,
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
Everyone here is trying to help you and that's cool, but EVERYONE on
this list may not be so nice. The above credentials is definitely the
type of information you want to keep private, unless you don't mind
people potentially accessing your database tables and doing whatever
they like with them.
I suggest doing something like
$db_host = 'localhost;
$db_user = 'foo';
$db_pass= ''bar;
$db_name =''xx;
if you are going to post it on the list.
On Thu, Feb 26, 2009 at 1:22 PM, PJ af.gour...@videotron.ca wrote:
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
[snip]
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
I agree. I wouldn't trust me at all! ;)
Ash
www.ashleysheridan.co.uk
Yeah very stupid of me...but I found the error: see if you can catch it:
?
//include (../lib/db1.php);// Connect to database
$db_host = 'xxx';
$db_user = 'xxx;
$db_pass = 'xxx';
$db_name = 'xxx';
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 3st query: .
mysql_error() . /P);
}
echo $sql1;
echo br /;
echo $db_select;
exit();
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Re: catch the error
Here's the working code...
?
//include (../lib/db1.php);// Connect to database
$db_host = '';
$db_user = '';
$db_pass = '';
$db_name = '';
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 3st query: .
mysql_error() . /P);
}
echo $sql1;
echo br /;
echo $db_select;
exit();
?
This works fine either as is or using the include... :-)
9el wrote:
But the question is PJ, have you got it out of errors yet? :)
www.twitter.com/nine_L http://www.twitter.com/nine_L
www.lenin9l.wordpress.com http://www.lenin9l.wordpress.com
---
Use FreeOpenSourceSoftwares, Stop piracy, Let the developers live. Get
a Free CD of Ubuntu mailed to your door without any cost. Visit :
www.ubuntu.com http://www.ubuntu.com
--
2009/2/27 Ashley Sheridan a...@ashleysheridan.co.uk
mailto:a...@ashleysheridan.co.uk
On Thu, 2009-02-26 at 13:34 -0500, Darryle Steplight wrote:
Hi PJ,
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
Everyone here is trying to help you and that's cool, but EVERYONE on
this list may not be so nice. The above credentials is
definitely the
type of information you want to keep private, unless you don't mind
people potentially accessing your database tables and doing whatever
they like with them.
I suggest doing something like
$db_host = 'localhost;
$db_user = 'foo';
$db_pass= ''bar;
$db_name =''xx;
if you are going to post it on the list.
On Thu, Feb 26, 2009 at 1:22 PM, PJ af.gour...@videotron.ca
mailto:af.gour...@videotron.ca wrote:
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca
mailto:af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works
from console
but not from this file :-(
[snip]
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test') or die(Error
connecting DB.mysql_error());
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db) or die(PError performing 1st
query: .mysql_error() . /P);
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley
corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com mailto:p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
I agree. I wouldn't trust me at all! ;)
Ash
www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 13:44 -0500, Jerry Schwartz wrote:
Here's how I mostly do it (albeit simplified):
$query = INSERT INTO `sometable`(`title`,`content`)
VALUES('$title','$content');
$result = mysql_query($query);
$autoId = mysql_insert_id($result);
$query = INSERT INTO `another_table`(`link_id`,`value`)
VALUES($autoId,'$value');
$result = mysql_query($query);
No need to call another query to retrieve the last inserted id, as it is
tied to the last query executed within this session.
Ash
www.ashleysheridan.co.uk
[JS] Ashley is absolutely right, I'd forgotten about the mysql_insert_id
shorthand. (I'm a one-man band, and for the last week or two I've been
immersed in VB for Access forms.) Not only is she right, but her way is
better. Presumably a language's internal code is maintained as the specific
database changes. You can make yourself more independent of the specific
database by using the PDO abstraction, although I would save that for a
rainy weekend.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
I just checked, and yep, I'm definitely still a he ;)
I never thought otherwise... but then I was wondering... there are too
many actresses with the same name... ;-)
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Re: catch the error
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 13:56 -0500, PJ wrote:
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 13:34 -0500, Darryle Steplight wrote:
Hi PJ,
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
Everyone here is trying to help you and that's cool, but EVERYONE on
this list may not be so nice. The above credentials is definitely the
type of information you want to keep private, unless you don't mind
people potentially accessing your database tables and doing whatever
they like with them.
I suggest doing something like
$db_host = 'localhost;
$db_user = 'foo';
$db_pass= ''bar;
$db_name =''xx;
if you are going to post it on the list.
On Thu, Feb 26, 2009 at 1:22 PM, PJ af.gour...@videotron.ca wrote:
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
[snip]
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
I agree. I wouldn't trust me at all! ;)
Ash
www.ashleysheridan.co.uk
Yeah very stupid of me...but I found the error: see if you can catch it:
?
//include (../lib/db1.php);// Connect to database
$db_host = 'xxx';
$db_user = 'xxx;
$db_pass = 'xxx';
$db_name = 'xxx';
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 3st query: .
mysql_error() . /P);
}
echo $sql1;
echo br /;
echo $db_select;
exit();
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
$db_user has not had the string terminated. pray tell was that the
answer you were looking for?!
Ash
www.ashleysheridan.co.uk
No. Damn those typos!
What seems to have made it work is just
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
not using mysql_select in a string
but would you use it in a string? how why?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Re: catch the error
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 14:15 -0500, PJ wrote:
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 13:56 -0500, PJ wrote:
Ashley Sheridan wrote:
On Thu, 2009-02-26 at 13:34 -0500, Darryle Steplight wrote:
Hi PJ,
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
Everyone here is trying to help you and that's cool, but EVERYONE on
this list may not be so nice. The above credentials is definitely the
type of information you want to keep private, unless you don't mind
people potentially accessing your database tables and doing whatever
they like with them.
I suggest doing something like
$db_host = 'localhost;
$db_user = 'foo';
$db_pass= ''bar;
$db_name =''xx;
if you are going to post it on the list.
On Thu, Feb 26, 2009 at 1:22 PM, PJ af.gour...@videotron.ca wrote:
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
[snip]
?
//include (lib/db1.php);// Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php);// Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
I agree. I wouldn't trust me at all! ;)
Ash
www.ashleysheridan.co.uk
Yeah very stupid of me...but I found the error: see if you can catch
it:
?
//include (../lib/db1.php);// Connect to database
$db_host = 'xxx';
$db_user = 'xxx;
$db_pass = 'xxx';
$db_name = 'xxx';
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 3st query: .
mysql_error() . /P);
}
echo $sql1;
echo br /;
echo $db_select;
exit();
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
$db_user has not had the string terminated. pray tell was that the
answer you were looking for?!
Ash
www.ashleysheridan.co.uk
No. Damn those typos!
What seems to have made it work is just
$db = mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name,$db);
not using mysql_select in a string
but would you use it in a string? how why?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
Yeah, you'd typo'd on the variable name. Also, the $ sign doesn't
actually denote a string, but a scaler variable, which can be any type,
complex or simple.
I type too fast and am too speedy... :-)
I'll have to look up about the variables.
Thanks good night. 'Til the morrow.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
non-auto increment question
I want to insert a new table entry 1 number higher than the highest in
the field (id). I cannot use auto-increment.
And I want to show the value of the field to be added in an input field
on the web page:
if (isset($_REQUEST[AddNewBooksRequest])) {
$SQL = SELECT MAX(id) FROM book;
$result = mysql_query($sql, $db);
$bookCount = mysql_num_rows($result);
for ($i=0; $i $bookCount; $i++) {
$row = mysql_fetch_array($result);
$idIN= $row[id]+1;
}
$idIN= $_POST[idIN];
$titleIN= $_POST[titleIN];
...snip...
td colspan=2
?
echo input type='text' name='titleIN' value='$idIN' disabled size='2';
?
/td
What am I doing wrong? (The query works and returns the right nr. but
what do I have to do to add 1 to that number and then display it in the
on page and post it to the table?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: non-auto increment question
Ashley Sheridan wrote:
On Wed, 2009-02-25 at 14:10 -0800, Gary W. Smith wrote:
Not sure that this is the problem BUT you should probably qualify the
name of the variable such that SELECT MAX(id) AS id FROM book. But
you don't want max(id) as id but rather max(id) + 1 as id. With
that you can then just return the final value. Also, if you don't
want to alias the value (or whatever it's called) you should use
$row[0] to get it by ordinal posistion.
As for now wanting to use autoincrement, you can run into a race
condition where two people are inserting at the same time, thus
having the same generated id.
Hope that helps.
From: PJ [mailto:af.gour...@videotron.ca]
Sent: Wed 2/25/2009 2:01 PM
To: MySql; php-gene...@lists.php.net
Subject: non-auto increment question
I want to insert a new table entry 1 number higher than the highest in
the field (id). I cannot use auto-increment.
And I want to show the value of the field to be added in an input field
on the web page:
if (isset($_REQUEST[AddNewBooksRequest])) {
$SQL = SELECT MAX(id) FROM book;
$result = mysql_query($sql, $db);
$bookCount = mysql_num_rows($result);
for ($i=0; $i $bookCount; $i++) {
$row = mysql_fetch_array($result);
$idIN = $row[id]+1;
Actually, I am wondering how to get rid of some of the code here as it
seems a little bloated
How do I get rid of the row counting - since there can never be more
than one row returned with this query.
}
$idIN = $_POST[idIN];
$titleIN = $_POST[titleIN];
...snip...
td colspan=2
?
echo input type='text' name='titleIN' value='$idIN' disabled
size='2';
?
/td
What am I doing wrong? (The query works and returns the right nr. but
what do I have to do to add 1 to that number and then display it in the
on page and post it to the table?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com http://www.ptahhotep.com/
http://www.chiccantine.com http://www.chiccantine.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=g...@primeexalia.com
Yeah, this sort of situation is really what auto increment is for. If
you get two people visiting the page with this code on at the same time
then you'll screw up your database.
Ash
www.ashleysheridan.co.uk
Being rather new to all this, I understood from the MySql manual that
the auto_increment is to b e used immediately after an insertion not
intermittently. My application is for administrators (the site owner
designates) to update the database from and administration directory,
accessed by user/password login... so there's really very little
possibility of 2 people accessing at the same time.
By using MAX + 1 I keep the id number in the $idIn and can reuse it in
other INSERTS
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to deal with multiple authors for one book
Reinhardt Christiansen wrote:
From: PJ af.gour...@videotron.ca
To: MySql mysql@lists.mysql.com
Subject: how to deal with multiple authors for one book
Date: Mon, 16 Feb 2009 17:20:54 -0500
In my db there are a number of books with several authors; so, I am
wondering how to set up a table on books and authors to be able to
insert (via php-mysql pages) data and retrieve and display these books
with several authors
I suspect that to insert data for a multiple author book I will have to
enter all data other than the author names into the book table and enter
the authors in the author tables with foreign keys to reference the
authors and their book.
Then to retrieve and display the book,I would have to use some kind of
join instruction with a where clause(regarding the position - 1st, 2nd,
3rd...) to retrieve the authors and their order. The order would
probably be done by a third field (e.g. f_name, l_name, position) in the
book_author table (tables in db - book, author, and book_author)
Am I on the right track, here?
Sort of, but not completely.
I think you would really benefit from a tutorial or course on data
normalization. I haven't looked for one in several years so I can't
suggest a specific tutorial but if you google it, you may well find
something that you like.
In a nutshell, you are trying to implement a many-to-many relationship
(a book can have several authors and an author can have several
books). These are not normally implemented directly in relational
databases. Instead, you typically have intermediate tables that are
usually called association tables (or intersection tables) that
sit between the other tables. In your case, you might see something
like this:
Book Table
===
Book_codeTitle
--- --
Z1 The Mote In God's Eye
Z2 Ringworld
Z3 Janissaries
Z4 War and Peace
Author Table
Author_code Author_name
-- --
1 Larry Niven
2 Jerry Pournelle
87Leo Tolstoy
Books Table (intersection table)
===
Book_code Author_code
--- --
Z11
Z12
Z21
Z32
Z487
In other words, the Books table identifies that The Mote in God's Eye
is written by Niven _and_ Pournelle; Ringworld is written by Niven
alone and Janissaries is written by Pournelle alone. And, of course,
War and Peace is written by Tolstoy.
You're going to want to do something very much like this.
A good tutorial will explain this well. I'm out of time; I have to go
now.
--
Rhino
Thank you for your clear explanation.
I have things set up rather well and have been able to generate a web
page to insert most of the data in the db and retrieve it to display in
another web page.
I say most because I have several small problems which I have posted
on mysql and php lists. Perhaps you can suggest something either where
and how to post or what to do.
Problem 1. How to SELECT and display multiple authors. Presently, my
book_author(intersection table contains fields authID, bookID and
ordinal. ordinal refers to the order of the author's name (1 if only 1
or first in line; 2 if 2nd in line). To retrieve the author's name I use
CONCAT_WS(' ', first_name, last_name) AS Author. So far, in my testing I
only have 10 books in the db with only single authors. Undoubtedly this
is not the way to go to retrieve 2 authors and display them as
(first_name1 last_name1 and first_name2 lastname or Joe Firstauthor adn
Bob Secondauthor).
The present query (works fine for 1 author):
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher as abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC ;
But to show 2 authors I think I need something of the order of:
CONCAT_WS (' ', (CONCAT_WS (' ', [(first_name, last_name)WHERE
book_author.ordinal = 1], [(first_name, last_name)WHERE
book_author.ordinal = 2]) AS Author
I suspect that one cannot nest the CONCAT_WS statement and I suspect the
WHERE is not in the right place either, but this seems to be fairly
logical... am I on the right track?
Problem 2... is similar to Problem 1 but deals with multiple categories
(62) and I'll deal with than when I get this one solved.
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
insert question
Is there a way to insert several rows into a table with one statement?
I have this:
$sql2 = INSERT INTO authors
(first_name, last_name, ordinal) VALUES
('$first_nameIN', '$last_nameIN', '1');
$result2 = mysql_query($sql2, $db);
I want to avoid doing another insert like:
$sql3 = INSERT INTO authors
(first_name, last_name, ordinal) VALUES
('$first_name2IN', '$last_name2IN', '2');
$result2 = mysql_query($sql2, $db);
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
multiple choice dropdown box puzzle
I think this is a tough one... and way above my head: PLEASE READ ALL OF THE ABOVE TO UNDERSTAND WHAT I AM TRYING TO DO. Having a bit of a rough time figuring out how to formulate php-mysql to insert data into fields using a multiple dropdown box in a form. to post I am using the following: snip... $categoriesIN = $_POST[categoriesIN]; ...snip... select name=$categoriesIN[] multiple=multiple OPTIONChoose Categories.../option OPTION VALUE=? echo $categoriesIN; ?1 OPTION VALUE=? echo $categoriesIN; ?2 OPTION VALUE=? echo $categoriesIN; ?3 OPTION VALUE=? echo $categoriesIN; ?4 OPTION VALUE=? echo $categoriesIN; ?5 /SELECT ...snip... $sql4 = FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ ) INSERT INTO temp (example) $categoriesIN[$ii] ; $result4 = mysql_query($sql4, $db); ...snip this does not work! The other posts work like a charm... but this... I cannot figure out what I should be entering where... I have tried several different configurations, but nothing seems to work... I found this as a model for entering the selections but can't figure out how to modify it for my needs: select name=branch_no[] multiple=multiple size=5 option Choose your location(s) /option option value=31003100/option option value=31053105/option option value=3503 3503/option option value=3504 3504/option /select What I would like to do is something like the following: select name=$categoriesIN[] multiple=multiple OPTIONChoose Categories.../option OPTION VALUE=1History OPTION VALUE=2Temples OPTION VALUE=2Pharaohs and Queens OPTION VALUE=4Cleopatra OPTION VALUE=4Mummies /SELECT and going further, I would like to be able to use a table that actually holds these values to feed them to the code above. I am sure this is possible but it must take some huge knowledge and experience to do it. BUT ... as I look at things, I am wondering if the FOR statement in the above should be used to do several INSERTs, that is, one $sql(number) per selected category... now, would that require many $sqls or many INSERTs within the $sql ? -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple choice dropdown box puzzle
Here's my test page and, so far, nothing works...
?
session_start();// Include sessions_start() in everything
include (db1.php);
$thisInterface = addNewBooks;
if (!isset($_SESSION[addNewBooks])) {
$categoriesIN= ;
// Save the input variables in session variables
$_SESSION[categoriesIN]= $categoriesIN;
// Remember that addNewBooks.php has been entered once
$_SESSION[addNewBooks] = 1;
}
else {
$categoriesIN= $_SESSION[categoriesIN];
}
?
form method=post action=multiple_category_insert.php
select name=?echo $categoriesIN?.'[]' multiple=multiple size=5
option Choose categories... /option
option value=1History/option
option value=2Temples/option
option value=3 Cleopatra/option
option value=4 Mummies/option
/select
br
input type=submit name=submit value=submit
/form
?
$sql4 = 'INSERT INTO test (example) VALUES (' . implode('),(',
$_POST[categoriesIN]) . ')';
$result4 = mysql_query($sql4, $db);
?
table align=center border=0 cellpadding=3 width=90%
tr
td align=centerb
input class=textbox type=submit name=AddNewBooksRequest
value=Insert New Books
/b
/td
/tr
/table
Brent Baisley wrote:
It's actually a very simple solution, and you should do it all in a
single INSERT. Putting INSERTs in a loop will kill your performance
when you try to scale.
$sql4 = 'INSERT INTO temp (example) VALUES (' . implode('),(',
$_POST[categoriesIN]) . ')';
$result4 = mysql_query($sql4, $db);
That example does not sanitize the data before inserting.
Brent
On Mon, Feb 23, 2009 at 10:25 AM, PJ af.gour...@videotron.ca wrote:
I think this is a tough one... and way above my head:
PLEASE READ ALL OF THE ABOVE TO UNDERSTAND WHAT I AM TRYING TO DO.
Having a bit of a rough time figuring out how to formulate php-mysql to
insert data into fields using a multiple dropdown box in a form.
to post I am using the following:
snip...
$categoriesIN = $_POST[categoriesIN];
...snip...
select name=$categoriesIN[] multiple=multiple
OPTIONChoose Categories.../option
OPTION VALUE=? echo $categoriesIN; ?1
OPTION VALUE=? echo $categoriesIN; ?2
OPTION VALUE=? echo $categoriesIN; ?3
OPTION VALUE=? echo $categoriesIN; ?4
OPTION VALUE=? echo $categoriesIN; ?5
/SELECT
...snip...
$sql4 = FOR ( $ii = 0 ; $ii count($categoriesIN) ; $ii++ )
INSERT INTO temp (example) $categoriesIN[$ii] ;
$result4 = mysql_query($sql4, $db);
...snip
this does not work! The other posts work like a charm... but this...
I cannot figure out what I should be entering where... I have tried several
different configurations, but nothing seems to work...
I found this as a model for entering the selections but can't figure out how
to modify it for my needs:
select name=branch_no[] multiple=multiple size=5
option Choose your location(s) /option
option value=31003100/option
option value=31053105/option
option value=3503 3503/option
option value=3504 3504/option
/select
What I would like to do is something like the following:
select name=$categoriesIN[] multiple=multiple
OPTIONChoose Categories.../option
OPTION VALUE=1History
OPTION VALUE=2Temples
OPTION VALUE=2Pharaohs and Queens
OPTION VALUE=4Cleopatra
OPTION VALUE=4Mummies
/SELECT
and going further, I would like to be able to use a table that actually
holds these values to feed them to the code above. I am sure this is
possible but it must take some huge knowledge and experience to do it.
BUT ...
as I look at things, I am wondering if the FOR statement in the above should
be used to do several INSERTs, that is, one $sql(number) per selected
category... now, would that require many $sqls or many INSERTs within the
$sql ?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple choice dropdown box puzzle
Brent Baisley wrote:
It's actually a very simple solution, and you should do it all in a
single INSERT. Putting INSERTs in a loop will kill your performance
when you try to scale.
$sql4 = 'INSERT INTO test (example) VALUES (' . implode('),(',
$_POST[categoriesIN]) . ')';
$result4 = mysql_query($sql4, $db);
Please explain this... I do not understand how the implode is being used
here.
As I understand it, the above code would aoutput or rather INSERT all
the choicees into one field as choice numbers separated by a , (like 1,
3, 4) or what? I suppose I could use that but I'm not sure if this is
the way to go. I understand the performance problem and would like to
keep it simple.
That example does not sanitize the data before inserting.
I know, but I am just testing locally for now. Will sanitize later.
Brent
Here is my test page: Questions below that.
html
head
titletest form/title
/head
body
/body
?
session_start();// Include sessions_start() in everything
include (db1.php);
$thisInterface = addNewBooks;
if (!isset($_SESSION[addNewBooks])) {
$categoriesIN= ;
// Save the input variables in session variables
$_SESSION[categoriesIN]= $categoriesIN;
// Remember that addNewBooks.php has been entered once
$_SESSION[addNewBooks] = 1;
}
else {$categoriesIN= $_SESSION[categoriesIN];
}
if (isset($_REQUEST[AddNewBooksRequest]))
$categoriesIN= $_POST[categoriesIN];
?
form method=post action=multiple_category_insert.php
select name=$categoriesIN[] multiple size=5
option Choose categories... /option
option value=1History/option
option value=2Temples/option
option value=3 Cleopatra/option
option value=4 Mummies/option
/select
input type=submit name=submit value=submit
/form
?
$sql4 = INSERT INTO test
(example) VALUES (' . implode('),(',
$_POST[$categoriesIN]) . ');
$result4 = mysql_query($sql4, $db);
?
table align=center border=0 cellpadding=3 width=90%
tr
td align=centerb
input class=textbox type=submit name=AddNewBooksRequest
value=Insert New Books
/b
/td
/tr
/table
/html
==
Question 1.
What should be entered into the name= something to make it work?
Question 2.
How should the $sql4 = INSERT.. be completed?
I know the code above is not working but I can't figure out what's wrong.
TIA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: left joins concat
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id http://b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables...
(i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
Re: left joins concat
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another
Re: left joins concat
Thank you gentlemen.
Questions /or comments annotated below:
Walter Heck wrote:
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
I do want to know, so I'll look more into it. :)
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
When I had my flash of enlightenment came when I noticed that in my many
concatenations I had used and posted the NATURAL join (which = NATURAL
LEFT join); with a little tweaking I got it to work.
But I still don't understand if aliasing AS a is any different from
aliasing AS ab (or, for that matter, if I substituted a number for the
table. I haven't experimented yet, but is there a difference between
using a or ab, cd, abc and just plain t1, t2, t3 or is this the same:
one two three. It may seem naive to be asking such questions, but they
are not readily answered in the tutorials.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
I have consulted w3shools and do find that they are not clear on a lot
of details... I may be picky about the small details but it is they that
make the whole possible.
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books
HOW TO QUERY(SELECT) and display MULTIPLE AUTHORS
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
the structure is rather simple:
book:id = primary key, field1 field8
author: id = primary key, first_name, last_name, ordinal
publishers: id = primary key, publisher
book_author:
authID = primary key, references author.id
bookID = primary key, references book.id
book_publisher:
bookID = primary key, references book.id
publishers_id = primary, key references publishers.id
Question 1 is: How to set up a query for a book with 2 authors?
There are a number of problems involved, but such issues as more than 2
authors, editors and authors in a dictionary of anthology or some
compilation can by revealed in the description field. So we come down to
the problem of just plain 2 authors: do we list the same book twice with
a different author in each instance with the ordinal field showing 1 for
the display priority of the first author and 2 for the second?
Question 2 is: How to retrieve and display the two authors on one line
as Author_1 and Author_2 without some incredible conditional
contortions to achieve the goal?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
book categories
Here's a tough one... In my library I have some 62 categories where a number of books can be in several categories. Now, to relate the categories table(AS c) to the book table (AS a), I assume that I need an intermediate table book_category (AS d) linking d.bookID to the b.bookID and d.categoryID to c.id. With so many categories and (probably) thousands of books d may become quite lengthy (but, I guess it's better than 62 * 100's of lengthy fields added to b). To enter the relationships I would add instructions on my addNewBooks.php form with the input as multiple choice dropdown box (listing the categories)... so far, so good (I hope)... Now, how do I SELECT and retrieve these categories to display on the web page? From the book_category table with a WHERE statement? If so, what then? CONCAT_WS the stuff to go into the html table td? I hope I'm on the right track... or am I in deep water? Help... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: book categories
Claudio Nanni wrote: Hi Phil, relax you are on track! First a little theory. In this case you have the so called N-to-M relationship. While if you need to relate one book to one author(given only one author per book!) you have the so called 1-to-N relationship, in this case you have the N-to-M relationship. If you are asking yourself why one book-one author is a 1-to-N relationship do not think of the single row/record, but reason this way(again, given only one author per book): OK, I understand. But in my case, there are a number of books with sever authors and I'm trying to figure that out in another thread. [HOW TO QUERY(SELECT) and display MULTIPLE AUTHORS] Take one row on the [books] table and ask yourself: How many possible authors have written this book? Ans: 1 Take one row on the [authors] table and ask yourself: How many possible books have been written by this authors Ans: Many (N any number) so you have a 1-to-N relationship in your example you have [books] and [categories] Take one row on the [books] table and ask yourself: To how many possible categories belongs this book? Ans: Many (N) Take one row on the [categories] table and ask yourself: How many books can belong to this category? Ans: Many (M , N is already taken!) so you have a N-to-M relationship while to implement a 1-to-N relationship you use a Foreign Key on one table, to implement a N-to-M relationship you need to use a table (cross reference) This means that while you still have the two original tables you will build a third table that connect the former two ones. This third table(Cross reference) it is just composed, at least, by two fields(two foreign keys) one referring to the primary key of the first table [books] and one referring to the primary kay of the second table [categories]. Just insert for each book as many rows as the categories to which the book belongs, for example, if you have a book with book_id=1 and various categories(1,2,3,4,5,6,7,8,9,10), to assign to the book the categories 1,3,8 insert three rows in the cross-reference table: book_id | category_id --- 1 | 1 1 | 3 1 | 8 This I understand too and I do appreciate the input which is quite clear and informative; but my real problem is to figure out how to use a dropdown table (62 choices) to enter the data into the reference table (linking categories and books from book_categories). In fact it would be great to be able to extract the information to select the categories from the categories table and use the id field as the value field and the category_name field as the for the option in the select section of the dropdown table. Otherwise it is rather tedious to enter by hand the id and the category. :-( and I am so lazy ;-) Once I can populate the book_category table from a php-mysql form page, the the problem remains on how to display the categories attributed to each book...This is not an earth-shaking need but rather a tough challenge since is is probably quite simple to do a php-mysql page to display the books based on the book_category table. And I am not sure that concat could work in this case... it still looks like the same problem as the multiple author thingie. Phil Jourdan AGAIN: CONCAT is just a string function, it is not a sql operator, and it is not giving anything that you don't already know, dont focus on CONCAT, forget it for now. Let me know Claudio Nanni 2009/2/22 PJ af.gour...@videotron.ca Here's a tough one... In my library I have some 62 categories where a number of books can be in several categories. Now, to relate the categories table(AS c) to the book table (AS a), I assume that I need an intermediate table book_category (AS d) linking d.bookID to the b.bookID and d.categoryID to c.id. With so many categories and (probably) thousands of books d may become quite lengthy (but, I guess it's better than 62 * 100's of lengthy fields added to b). To enter the relationships I would add instructions on my addNewBooks.php form with the input as multiple choice dropdown box (listing the categories)... so far, so good (I hope)... Now, how do I SELECT and retrieve these categories to display on the web page? From the book_category table with a WHERE statement? If so, what then? CONCAT_WS the stuff to go into the html table td? I hope I'm on the right track... or am I in deep water? Help... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
left joins concat
I have been searching and searching for a clear and logical explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables... (i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it is not very helpful in any explanations...
I sure would like to hear some clear explanations...
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to check for null in string
Johan De Meersman wrote:
In SQL, the correct syntax is IS NULL or IS NOT NULL.
Random programming languages more often than not have no decent
support for
NULL content, although your DB library might have an isnull() function or
something similar. Once you've exported a field into a regular variable,
however, most often NULL becomes indistinguishable from an emtpy string.
On Tue, Feb 17, 2009 at 2:47 AM, PJ af.gour...@videotron.ca wrote:
I know this is not working, but how can I check for NULL; or how can I
configure my field sub_title so I can check if $booksub_title contains
anything or is empty.
This problem as been breaking my back...don't know what to put in as a
default to be able to check against string input (VARCHAR) or should I
use another type?
if ($booksub_title != NULL) {
echo td width='400'b$booktitle[$i]/bbr
$booksub_title[$i]/b;
}
else {echo td width='400'b$booktitle[$i]/b;
}
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Problem resolved.
The hic in this was not the null but rather the variable. I finally
understood that the problem in the code was the variable $booksub_title
- since it comes up in a loop it is changed on every pass, therefore the
string should be $booksub_title[$i] to correspond to the same string in
the else statement... this is what works, now:
if (empty ($booksub_title[$i])) {
echo td width='400'b$booktitle[$i]/bbr;
}
else {echo td
width='400'b$booktitle[$i]/bbr$booksub_title[$i]br;
}
so there! Thanks all for your input which did help me to see the light,
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to deal with multiple authors for one book
In my db there are a number of books with several authors; so, I am wondering how to set up a table on books and authors to be able to insert (via php-mysql pages) data and retrieve and display these books with several authors I suspect that to insert data for a multiple author book I will have to enter all data other than the author names into the book table and enter the authors in the author tables with foreign keys to reference the authors and their book. Then to retrieve and display the book,I would have to use some kind of join instruction with a where clause(regarding the position - 1st, 2nd, 3rd...) to retrieve the authors and their order. The order would probably be done by a third field (e.g. f_name, l_name, position) in the book_author table (tables in db - book, author, and book_author) Am I on the right track, here? -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to deal with multiple authors for one book
Michael Dykman wrote: On Mon, Feb 16, 2009 at 5:20 PM, PJ af.gour...@videotron.ca wrote: In my db there are a number of books with several authors; so, I am wondering how to set up a table on books and authors to be able to insert (via php-mysql pages) data and retrieve and display these books with several authors I suspect that to insert data for a multiple author book I will have to enter all data other than the author names into the book table and enter the authors in the author tables with foreign keys to reference the authors and their book. Then to retrieve and display the book,I would have to use some kind of join instruction with a where clause(regarding the position - 1st, 2nd, 3rd...) to retrieve the authors and their order. The order would probably be done by a third field (e.g. f_name, l_name, position) in the book_author table (tables in db - book, author, and book_author) Am I on the right track, here? Close. You have to consider that this is a many-to-many relationship books may have many authors and authors may have many books. This will reuire the use of a linking table and it's there that I suggest you need too maintain your ordinal field (I use 'ordinal' by convention as 'order' is a SQL keyword) AUTHORS - authorid - name -- other fields BOOKS - bookid - title -- other fields A2B - bookid - authorid - ordinal What do you mean by ordinal is this a primary key??? Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to check for null in string
I know this is not working, but how can I check for NULL; or how can I
configure my field sub_title so I can check if $booksub_title contains
anything or is empty.
This problem as been breaking my back...don't know what to put in as a
default to be able to check against string input (VARCHAR) or should I
use another type?
if ($booksub_title != NULL) {
echo td width='400'b$booktitle[$i]/bbr
$booksub_title[$i]/b;
}
else {echo td width='400'b$booktitle[$i]/b;
}
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
query and insert data on multiple tables
I am trying to create a php-mysql page to POST new records to several tables from one php page and I have to retrieve records from several (like 4 to 8) tables in one query. Being quite new to php mysql, I am wondering what is the best way to go about this. My tables consist of * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=authorsgoto=tbl_structure.php authors http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=authorspos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=booksgoto=tbl_structure.php books http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=bookspos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_authorgoto=tbl_structure.php book_author http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_authorpos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_categoriesgoto=tbl_structure.php book_categories http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_categoriespos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_publishergoto=tbl_structure.php book_publisher http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_publisherpos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_sellersgoto=tbl_structure.php book_sellers http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=book_sellerspos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=categoriesgoto=tbl_structure.php categories http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=categoriespos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=languagegoto=tbl_structure.php language http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=languagepos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=publishersgoto=tbl_structure.php publishers http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=publisherspos=0 * Structure http://biggie/phpmyadmin/tbl_structure.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=sellersgoto=tbl_structure.php sellers http://biggie/phpmyadmin/sql.php?db=biblanetoken=6b20581bf9be2fb146f60d93cc87a296table=sellerspos=0 On top of this, I have a problem of how to deal with multiple authors for a number of books... formatting a page with several authors is not a prolem... but how do I POST and SELECT several authors for the same book? Categories is a similar problem... And then, there is the question of language - books are included from 5 different languages... This, I thought, could be handled by referencing the language table by the PK of the language table which is obviously numbers from 1 to 5 I am beginning to see the light... but I sure would like to shorten my learning curve by asking here... I would like to see examples, if possible. I have no problem formatting and outputting a great page from a simple SELECT * FROM my_table... but when it comes to querying several tables plus needing to do something like left joins, I am a little lost. How to do a multi-table query or how does one do several queries from one php page? How can multiple POSTs be done on one php page? TIA -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db setup - correction
Peter Brawley wrote: PJ, As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. And worse, some publishers re-use ISBM#s. In general, any PK dependency on the outside world is to be avoided unless the dependency guarantees uniqueness as robustly as the internal auto_increment facility. So, there is no author or category field in the books table, right? Right. Are you saying that the id PK of books, authors and books_authors are all the same? Yikes no. Each is entirely independent. But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? You write a standard master-detail form, which usually has a single form at top for the parent row, and a browsing multi-row form below for entry of multiple child rows. PB PJ wrote: Peter Brawley wrote: PJ Why do I need a third table? The clue is author (could be 2 or more) in your books column list. It is incorrect to store two author names or IDs in one column. The relationship is 1:many. So you need a bridge table: books(id PK, etc...) authors(id PK, etc...) books_authors(id PK, bid references books(id),...,aid references authors(id), listed_order smallint, etc...) Now one book with multiple authors has one books_authors row for each of its authors, and you retrieve book author info with a simple join. I did review normalization - I had read it before; it is a little clearer now, but so, now I'm getting very very confused... As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. So, there is no author or category field in the books table, right? Are you saying that the id PK of books, authors and books_authors are all the same? The authors table would have the fields auth_id, first_name, last_name. The books_authors table would have its own fields - id PK, bid, aid and listed_order (which would indicate iin which order to display the authors (?)) Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? From the looks of things I have to create some kind of php input function or group of functions to come up with a page with the fields necessary to enter all the data and then store the data in mySql. And to retrieve the information its a heap of functions to gather and populate a page with the info from mySql... PB - PJ wrote: Olaf Stein wrote: Just about the authors You need a separate table for them and then an table linking authors and books. You lose me here... :-( Why do I need a third table? I may have 2 or three books with 3 authors, quite a few with 2 authors and many with just 1 author. I can't see creating an extra table for every book that has more than 1 author... ? ? ? ? And wouldn't it be the same thing for the categories? Isn't the relationship between the author field in the books table and the authors table done by an foreign key? So you have table books, authors and rel_books_authors where rel_books authors has 3 entries for a book with 3 authors just using the book id and the author is's Olaf On 2/9/09 10:25 AM, PJ af.gour...@videotron.ca wrote: being a newbie to mysql, I'm a little confused about how to deal with the following: I am creating a database of books and have come down to this - table for books (books) including fields for id (primary key, auto-incr.) title author (could be 2 or more) category
Re: db setup - correction
Peter Brawley wrote: PJ But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Engine choice is another issue. InnoDB properly isolates FK enforcement in the database. The MyISAM tradeoff (for speed) is that you have to implement FK logic in code. Forgive my naiveté, but how do you do that? For normalisation, however, you need the A-B-AB setup no matter what engine you use. What is simpler? BTW, I have set up an EER diagram using MySQL Workbench... and exported this sql script... does it make sense? Strange that it created two instances to create the table book_author??? Maybe a glitch in Workbench -- - -- Table `language` -- - CREATE TABLE IF NOT EXISTS `language` ( `id` INT NOT NULL AUTO_INCREMENT , `language` VARCHAR(7) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `books` -- - CREATE TABLE IF NOT EXISTS `books` ( `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , `title` VARCHAR(148) NULL , `sub_title` VARCHAR(90) NULL , `descr` TINYTEXT NULL , `comment` TEXT NULL , `bk_cover` VARCHAR(32) NULL , `publish_date` YEAR NULL , `ISBN` BIGINT(13) NULL , `language_id` INT NULL , PRIMARY KEY (`id`) , INDEX `fk_books_language` (`language_id` ASC) , CONSTRAINT `fk_books_language` FOREIGN KEY (`language_id` ) REFERENCES `biblane`.`language` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `authors` -- - CREATE TABLE IF NOT EXISTS `authors` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `first_name` VARCHAR(32) NULL , `last_name` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `categories` -- - CREATE TABLE IF NOT EXISTS `categories` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `category` VARCHAR(70) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = MyISAM; -- - -- Table `sellers` -- - CREATE TABLE IF NOT EXISTS `sellers` ( `id` INT NOT NULL AUTO_INCREMENT , `seller_link` VARCHAR(128) NULL , `seller_img` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `publishers` -- - CREATE TABLE IF NOT EXISTS `publishers` ( `id` INT NOT NULL AUTO_INCREMENT , `publisher` VARCHAR(72) NOT NULL , `pub_link` NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `book_author` -- - CREATE TABLE IF NOT EXISTS `book_author` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `author_id` INT UNSIGNED NOT NULL , `list_order` TINYINT(1) NULL , PRIMARY KEY (`books_id`, `author_id`) , INDEX `fk_book_author_books` (`books_id` ASC) , INDEX `fk_book_author_authors` (`author_id` ASC) , CONSTRAINT `fk_book_author_books` FOREIGN KEY (`books_id` ) REFERENCES `biblane`.`books` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_book_author_authors` FOREIGN KEY (`author_id` ) REFERENCES `biblane`.`authors` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `book_author` -- - CREATE TABLE IF NOT EXISTS `book_author` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `author_id` INT UNSIGNED NOT NULL , `list_order` TINYINT(1) NULL , PRIMARY KEY (`books_id`, `author_id`) , INDEX `fk_book_author_books` (`books_id` ASC) , INDEX `fk_book_author_authors` (`author_id` ASC) , CONSTRAINT `fk_book_author_books` FOREIGN KEY (`books_id` ) REFERENCES `biblane`.`books` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_book_author_authors` FOREIGN KEY (`author_id` ) REFERENCES `biblane`.`authors` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `book_publisher` -- - CREATE TABLE IF NOT EXISTS `book_publisher` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `publishers_id` INT NOT NULL , PRIMARY KEY (`books_id
Re: db setup - correction
ddevaudre...@intellicare.com wrote: PJ af.gour...@videotron.ca wrote on 02/10/2009 12:44:04 PM: -- - -- Table `books` -- - CREATE TABLE IF NOT EXISTS `books` ( `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , `title` VARCHAR(148) NULL , `sub_title` VARCHAR(90) NULL , `descr` TINYTEXT NULL , `comment` TEXT NULL , `bk_cover` VARCHAR(32) NULL , `publish_date` YEAR NULL , `ISBN` BIGINT(13) NULL , `language_id` INT NULL , PRIMARY KEY (`id`) , INDEX `fk_books_language` (`language_id` ASC) , CONSTRAINT `fk_books_language` FOREIGN KEY (`language_id` ) REFERENCES `biblane`.`language` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; May I make one sugggestion? I noticed that the books.id column is defined as SMALLINT UNSIGNED. Unless your database is going to stay quite small, that is really going to limit the number of books. This column is used as a FK in a number of your other tables and if you later on have to change the data type to make it bigger, you'll have to change all the related tables. If I remember correctly, I had to drop all the FK constraints that referenced this column, do the alter tables, and then recreate the FK constraints. Save yourself the hassle and make it at least an Integer, if not a BIGINT (unsigned). http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Donna Thanks for the suggestion. I thought that 32 thousand would probably be enough for books on Egyptology... :-) But you're probably right. I don't imagine the site will ever reach that many, but.. And I'm still struggling to understand how I'm going to deal with inputting all the information ... it looks like I'll have to do some kind of php function to enter all the information in one page and then retrieve it to display it on another... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
db setup
being a newbie to mysql, I'm a little confused about how to deal with the following: I am creating a database of books and have come down to this - table for books (books) including fields for id (primary key, auto-incr.) title author (could be 2 or more) category (could be several - eg. youth, fiction, comics, and history, for same book) lang (eng, french, spanish, or german) descr (short summary, if avail.) comment (review(s)) publisher pub_link (publisher's web addr. if avail.) bk_cover (image, if avail.) publish_date ISBN buy_link (if avail.) The other tables would be authors, categories, and buy_links. My problem is how to deal with several authors, categories, and links to sellers. What do I enter in the author field when I have several authors? Do I set up references to the author's name(s) in another table, like (3, 7, 23) each number representing the name of an author in another table? That is, .e.g. a number_id is entered in the author field in the books table; in the author table, I enter John Smith in the author_id field in the author table? Is the field for the author_id a foreign key? Same question for categories... buy_link - there are not many, but generally include an image and related info to be shown next to the books info (here, I suppose, just a few numbers will suffice to reference the table containing the information) and this field (buy_link_id) would be a foreign key (?) referencing the buy_link table? So, in a query (search) I would be doing joins from the books table to the author, category, and buy_link tables using foreign keys? But how do I deal with a search for a book by author? If there are, say, 3 authors (Red, White, and Blue) and the search is for White, the display should probably be formulated with a php function to display the book listing by book title (the standard display which includes all the relevant information about the book). So the search should first find the name of the author in the authors table and if it exists, then the author_id should reveal the ids for the relevant books and then these should be printed (or echoed) in the output. Gets kind of complicated doesn't it? Or would it all be simpler to just enter all the authors' names in the author field and then do a search for an author within the fields as a string? I suspect this would be rather slow. Also, same for categories and sellers since the whole database has to be searched? Am I on the right track? TIA -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db setup - correction
Olaf Stein wrote: Just about the authors You need a separate table for them and then an table linking authors and books. You lose me here... :-( Why do I need a third table? I may have 2 or three books with 3 authors, quite a few with 2 authors and many with just 1 author. I can't see creating an extra table for every book that has more than 1 author... ? ? ? ? And wouldn't it be the same thing for the categories? Isn't the relationship between the author field in the books table and the authors table done by an foreign key? So you have table books, authors and rel_books_authors where rel_books authors has 3 entries for a book with 3 authors just using the book id and the author is's Olaf On 2/9/09 10:25 AM, PJ af.gour...@videotron.ca wrote: being a newbie to mysql, I'm a little confused about how to deal with the following: I am creating a database of books and have come down to this - table for books (books) including fields for id (primary key, auto-incr.) title author (could be 2 or more) category (could be several - eg. youth, fiction, comics, and history, for same book) lang (eng, french, spanish, or german) descr (short summary, if avail.) comment (review(s)) publisher pub_link (publisher's web addr. if avail.) bk_cover (image, if avail.) publish_date ISBN buy_link (if avail.) The other tables would be authors, categories, and buy_links. My problem is how to deal with several authors, categories, and links to sellers. What do I enter in the author field when I have several authors? Do I set up references to the author's name(s) in another table, like (3, 7, 23) each number representing the name of an author in another table? That is, .e.g. a number_id is entered in the author field in the books table; in the author table, I enter John Smith in the author_id field in the author table? Is the field for the author_id a foreign key? Same question for categories... buy_link - there are not many, but generally include an image and related info to be shown next to the books info (here, I suppose, just a few numbers will suffice to reference the table containing the information) and this field (buy_link_id) would be a foreign key (?) referencing the buy_link table? So, in a query (search) I would be doing joins from the books table to the author, category, and buy_link tables using foreign keys? But how do I deal with a search for a book by author? If there are, say, 3 authors (Red, White, and Blue) and the search is for White, the display should probably be formulated with a php function to display the book listing by book title (the standard display which includes all the relevant information about the book). So the search should first find the name of the author in the authors table and if it exists, then the author_id should reveal the ids for the relevant books and then these should be printed (or echoed) in the output. Gets kind of complicated doesn't it? Or would it all be simpler to just enter all the authors' names in the author field and then do a search for an author within the fields as a string? I suspect this would be rather slow. Also, same for categories and sellers since the whole database has to be searched? Am I on the right track? TIA - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db setup - correction
Peter Brawley wrote: PJ Why do I need a third table? The clue is author (could be 2 or more) in your books column list. It is incorrect to store two author names or IDs in one column. The relationship is 1:many. So you need a bridge table: books(id PK, etc...) authors(id PK, etc...) books_authors(id PK, bid references books(id),...,aid references authors(id), listed_order smallint, etc...) Now one book with multiple authors has one books_authors row for each of its authors, and you retrieve book author info with a simple join. I did review normalization - I had read it before; it is a little clearer now, but so, now I'm getting very very confused... As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. So, there is no author or category field in the books table, right? Are you saying that the id PK of books, authors and books_authors are all the same? The authors table would have the fields auth_id, first_name, last_name. The books_authors table would have its own fields - id PK, bid, aid and listed_order (which would indicate iin which order to display the authors (?)) Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? From the looks of things I have to create some kind of php input function or group of functions to come up with a page with the fields necessary to enter all the data and then store the data in mySql. And to retrieve the information its a heap of functions to gather and populate a page with the info from mySql... PB - PJ wrote: Olaf Stein wrote: Just about the authors You need a separate table for them and then an table linking authors and books. You lose me here... :-( Why do I need a third table? I may have 2 or three books with 3 authors, quite a few with 2 authors and many with just 1 author. I can't see creating an extra table for every book that has more than 1 author... ? ? ? ? And wouldn't it be the same thing for the categories? Isn't the relationship between the author field in the books table and the authors table done by an foreign key? So you have table books, authors and rel_books_authors where rel_books authors has 3 entries for a book with 3 authors just using the book id and the author is's Olaf On 2/9/09 10:25 AM, PJ af.gour...@videotron.ca wrote: being a newbie to mysql, I'm a little confused about how to deal with the following: I am creating a database of books and have come down to this - table for books (books) including fields for id (primary key, auto-incr.) title author (could be 2 or more) category (could be several - eg. youth, fiction, comics, and history, for same book) lang (eng, french, spanish, or german) descr (short summary, if avail.) comment (review(s)) publisher pub_link (publisher's web addr. if avail.) bk_cover (image, if avail.) publish_date ISBN buy_link (if avail.) The other tables would be authors, categories, and buy_links. My problem is how to deal with several authors, categories, and links to sellers. What do I enter in the author field when I have several authors? Do I set up references to the author's name(s) in another table, like (3, 7, 23) each number representing the name of an author in another table? That is, .e.g. a number_id is entered in the author field in the books table; in the author table, I enter John Smith in the author_id field in the author table? Is the field for the author_id a foreign key? Same question for categories... buy_link - there are not many, but generally include an image and related info to be shown next to the books info (here, I suppose, just a few numbers will suffice to reference the table containing the information) and this field (buy_link_id) would be a foreign key (?) referencing the buy_link table? So, in a query (search) I would be doing joins from the books table to the author, category, and buy_link tables using foreign keys? But how do I deal with a search for a book by author? If there are, say, 3 authors (Red, White, and Blue) and the search is for White, the display should probably be formulated with a php function to display the book listing by book title (the standard display which includes all the relevant information about the book). So the search should first find the name of the author in the authors table and if it exists
Re: how to design book db
My comments, questions explanations inserted below mos wrote: At 09:55 AM 1/6/2009, you wrote: -Original Message- From: c...@l-i-e.com [mailto:c...@l-i-e.com] Sent: Tuesday, January 06, 2009 9:47 AM To: mysql@lists.mysql.com Subject: RE: how to design book db Just theories here: The same book re-issued by another publisher might have a different ISBN. A book with an insert (e.g., CDROM) may have a different ISBN, but be the same for some purposes. And mistakes can be made... Ultimately, I suspect that the uniqueness of ISBN to what normal folks call the same book is not as clear as one would hope. [JS] I'm really glad I was able to eavesdrop on this conversation. I had no idea the ISBN issue was so murky. For better or worse, most of my publishers don't use ISBNs; many of them don't even assign product numbers. I guess the only way around it is to assign your own unique key. Not only do I have 2 ISBNs for a few books, but there is also the problem of books in foreign languages (French, Italian, German, Spanish) - my boss (my daughter who owns http:// www.ptahhotep.com ) tells me that foreign editions do have different ISBN numbers but not to worry, they are diffeerent but they are unique and would not conflict with the US numbers. BTW, it might be worth while for PJ to look at how Amazon stores its data. I don't think you can find an easier to use database for searching on books. It looks like they store 2 ISBN numbers per book. It also appears they use fulltext indexing on a lot of fields so the user can search on anything. I'll look at the Amazon stuff. Thanks for the suggestion. But this is where I get a little bit muddled: I have a problem (probably just understanding how things work) with categories. Since there are many categories (somewhere like 40+), how do I handle that? Would it be best to set up foreign keys for a categories table? But then there is the problem of fulltext indexing... it only works with MyISAM but foregn keys only work with InnoDB? So do I use MyISAM, a categories field, and just use 1 table for books with fulltext indexing on description, title, author, and categories tables? And what about fulltext indexing? Do I really need that? I thought it would be sufficient to search the fields for words or phrases (in the case of categories which often will be like second intermetiate period). In effect, what I expect to be doing is to use php to format the pages dynamically using the search functions of MySQL - so the categories would be on a static page with javascript dropdown fields for the different categories which would poinnt to a file that would do the required search and php would then set up the page to display the retrieved info. Also check out http://www.abebooks.com/ re: ISBN numbers. If this application is for a used bookstore then you're going to have to allow books without ISBN's because books from 40 years ago of course don't have ISBN's. No, this is not for a used bookstore... only for the ptahhotep site which is only a bibliography of books on Ancient Egypt. :-) - so you're right, there are old books in there. PJ Also see http://en.wikipedia.org/wiki/Isbn. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to design book db
mos wrote: At 08:17 AM 12/29/2008, you wrote: I am rather fresh to MySQL and am trying to fix and update a website - modifying from just plain html to css, php and MySQL. I'm working on FreeBSD 7.0, MySQL 5.1,30, PHP5.28 Apache 2.2.11. I need figure out how to set up (design) a database of books which gets rather complicated since I must implement searches of the database based on key words including categories, ISBN numbers, authors, dates, etc. etc. The problem is how to deal with duplication of the data - In other words, a book may have not only several authors, but also several ISBN numbers, fall under several categories, different dates (year of publication), several publishers I probably haven't yet seen all of the variables. I certainly do not want to enter the same book many times with just one of each different variable. I suppose that one way to do it is to enter one row with a lot of columns to store all the the different variables; a search would probably be simpler this way if the search criteria are limited to 1 word. Or would it? I rather do think that the search should be limited to 1 word anyway. :-) If the search would be for a category, for instance, would it make sense to use a column for category with an input of keywords for the different categories?; rather than a column for each category or another table of categories? Multiple publication years could probably be different row entries since there would not be more than 2 or would be a different publisher, language, or country. I really with to K.I.S.S this undertaking and would appreciate any help or suggestions. If it helps, you can see the site as it is at present http://www.ptahhotep.com - but since it is rather messed up at the moment, it is best viewed with IE. Some of the links and jscripts don't work on FireFox. TIA, PJ You can of course simplify things by putting the alternate ISBN number in the description of the book and put a full text index on it. Same with alternate authors etc.. It would be a catch all for items that you don't have columns for. How do I do that? I think the best line of attack is to work from an existing model. Why re-invent the wheel? You're right... I appreciate the suggestion and the links... There are a few bookstore/library schemas here: http://www.databaseanswers.org/data_models/ http://www.edumax.com/oracle-basics-06-normalization-and-sample-schema-creation-normalization-and-sample-schema-creation.html Mike Sorry for the long holiday delay in continuing... I checked the links below I think they will help ... but there are some things that are not clear in my mind: 1. I understand what the abbreviations PK and FK (primary key and foreign key) are but what is PF? (primary field??? - this is in the link http://www.databaseanswers.org/data_models/ uner Libraries and books 2. How can I deal with a primary key for books? ISBN would be great, except for the fact that it was only implemented at a certain date and books published before that date do not have an ISBN number. 3. And what about books that were written by several authors? 4. What do I need to fix in the tables below? +---+ | Tables_in_biblane | +---+ | authors | | books | | books_by_author | | books_by_category | | categories| +---+ mysql DESCRIBE authors; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | author_id | tinyint(4) | NO | PRI | NULL| | | auth_first_name | varchar(15) | NO | | NULL| | | auth_last_name | varchar(32) | NO | | NULL| | +-+-+--+-+-+---+ mysql DESCRIBE books; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | tinyint(11) | NO | PRI | NULL| auto_increment | | title | varchar(60) | NO | | NULL|| | auth_name | char(28)| NO | | NULL|| | auth_first | char(12)| NO | | NULL|| | yr | year(4) | YES | | NULL|| | lang | char(7) | YES | | NULL|| | descr | tinytext| NO | | NULL|| | comment| text| NO | | NULL|| | e-mail | varchar(50) | NO | | NULL|| | publisher | varchar(50) | NO | | NULL|| | pub_link | varchar(32) | NO | | NULL|| | publisher1 | varchar(50) | NO | | NULL|| | pub1_link | varchar(32) | NO | | NULL
how to design book db
I am rather fresh to MySQL and am trying to fix and update a website - modifying from just plain html to css, php and MySQL. I'm working on FreeBSD 7.0, MySQL 5.1,30, PHP5.28 Apache 2.2.11. I need figure out how to set up (design) a database of books which gets rather complicated since I must implement searches of the database based on key words including categories, ISBN numbers, authors, dates, etc. etc. The problem is how to deal with duplication of the data - In other words, a book may have not only several authors, but also several ISBN numbers, fall under several categories, different dates (year of publication), several publishers I probably haven't yet seen all of the variables. I certainly do not want to enter the same book many times with just one of each different variable. I suppose that one way to do it is to enter one row with a lot of columns to store all the the different variables; a search would probably be simpler this way if the search criteria are limited to 1 word. Or would it? I rather do think that the search should be limited to 1 word anyway. :-) If the search would be for a category, for instance, would it make sense to use a column for category with an input of keywords for the different categories?; rather than a column for each category or another table of categories? Multiple publication years could probably be different row entries since there would not be more than 2 or would be a different publisher, language, or country. I really with to K.I.S.S this undertaking and would appreciate any help or suggestions. If it helps, you can see the site as it is at present http://www.ptahhotep.com - but since it is rather messed up at the moment, it is best viewed with IE. Some of the links and jscripts don't work on FireFox. TIA, PJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
installation nightmare
I feel like one big fat stupid sheep problem SOLVED... It turns out that the root of all the problems was my own little forgetful brain... I had forgotten that I had changed the permissions on the apache22/data files to 766 which prevented the execution of all the php scripts which snowballed into incorrect readings of info.php and a whole bunch of stuff. Now, apache22, mysql, php, phpMySql, etc. etc. all work fine as out of the box Thanks for all the input from everyone and Happy Holidays to all :-[ :-! :-X -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: installation nightmare
This is the best response todate. ! John Daisley wrote: I have read through your message again because I really could not believe you were having so many issues then it struck me... Thank you for listening... :-) You installed MySQL 6.0.8, an Alpha release on to what seems like a production box Sorry to disappoint you, but I am an overzealous amateur with Unix in general. I think the thinking behind it all is fabulous; it's just that, I find, there is a kind-of smugness in the community about the systems and software: people assume that we amateur-idiots are supposed to know more than we do. I really do not like the idea of having to read through all the documentation of any program until I need to get into the details - most software, like Cups and Salmba are set up once and thereafter one need not even think about it; generally it works without problem. No, I have the setup strictly locally for my own pleasure and enlightenment and to help me with the creation and, sometimes, development of a website by more qualified people working for me... now, I'm just trying to help out my daughter to update her egyptology site (www.ptahhotep.com) with a database. and now you are having trouble getting other versions to work? Well this isn't going to be very helpful but I guess you are learning the hard way why you never install alpha software onto anything other than a box which you rely on only to collect dust. You're right... :-) - but it is helpful. I didn't realize that this was an alpha until I deinstalled... ok, ok, that's my fault... Seems to me the box you are working on is pretty messed up with Apache, Php and MySQL all having problems. Restore a backup from before it got messed up so you know you have a working base then try again one piece of software at a time. Other than the backup, which was not necessary, as this is a fresh install of FreeBSD7.0, I did exactly that: I deinstalled, deleted stray files and anything I could find related to MySQL, PHP5, phpMyAdmin, and even Webmin. I then updated the Ports files and started to reinstall everything. Well, I thought that would fix things... ehhh, no; not only did it not work, there was another wrench that was dropped by php5.2.8 - it is broken and phymyadmin cannot be installed and setup. Soo, as we say in French: rebelotte! - or in English back to square 1. So, now I am just rying to set up MySQL - first try, to start the server was a system freeze because I went for a directive from the Documentation. To tell you the truth, I have never done backups on the FreeBSD installations - the reasoning is quite simple... if it fails, I have backed up the work files and anything of importance on other machines and reinstallaing the OS and programs is not a big deal - at least, it never has been; until now. I have had a couple of crashes but it has exclusively been due to HDDs failing. Even on the last failure I was able to salvage all important files without any great problems. Other than that, as you are struggling with MySQL, PHP and Apache but are happy with commercial software offerings then go with what you are happy with. Get a copy of SQL Server and IIS. Everyone has there own preference and you have to go with what you are most comfortable and productive with. For me its Oracle10g/MySQL5 on Fedora, for you I'm sure its something totally different. What you don't want to do is try something you are not comfortable with, mess it up and then go onto a user group blaming the software particularly when its such an established piece of software as MySQL which can proudly boast millions of working installs spread across probably every conceivable platform. I couldn't agree more. For example, I have more problems with MS Office than you can imagine... OpenOfficeOrg is fabulous ... Only problem is the compatibility with MS files is the macros, but I don't often need them. Thats my rant for the day, now as its gone 1am Im off to bed. Sweet dreams, and have a nice holiday. Phil Jourdan John */John Daisley/* Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator http://www.mysql.com/certification/candidates.php?exam=4(CMDBA) MySQL Certified Developer http://www.mysql.com/certification/candidates.php?exam=3 (CMDEV) MySQL Certified Associate http://www.mysql.com/certification/candidates.php?exam=6 (CMA) Comptia A+ Certified Professional IT Technician On Fri, 2008-12-19 at 15:47 -0500, PJ wrote: Do what I can, there seems to be no way to install mysql 5.1.30 on FreeBSD 7.0 I even removed all files in reference to mysql5.0, 5.1, 6.0.8 from the Ports collection, cvsup'd the Ports, deinstalled php5, php5-mysql, phpMyAdmin, Webmin and followed the documentation as closely as possible (a number of contradictions in file names and locations) to install MySQL 5.1.30, run the install tables script, change the root password
installation nightmare
Do what I can, there seems to be no way to install mysql 5.1.30 on FreeBSD 7.0 I even removed all files in reference to mysql5.0, 5.1, 6.0.8 from the Ports collection, cvsup'd the Ports, deinstalled php5, php5-mysql, phpMyAdmin, Webmin and followed the documentation as closely as possible (a number of contradictions in file names and locations) to install MySQL 5.1.30, run the install tables script, change the root password both with and without the server name (it is never clear in the documentation as to just when to start or stop the server) - the mysql database is not created, access is denied to root (using password=NO) - now, why is one supposed to set up the password and then the password is not accepted - what is this nonsense; su mysql does not work how is it possible that I was originally able to set up mysql6.0.8 and get it running along with phpMyAdmin and Webmin... now I have updated even php5 to 5.2.8 and now phpMyAdmin cannot be installed because of a break in php5.2.8 (the php5-sld - or something like that) This is the most absurd mess that I have seen - it seems that the programmers have all gone loony and have forgotten that the essence of productivity and success is the old KISS principle - Keep It Simple Stupid. I think there must be a lot of people sleeping out there in computer never-never-land. No wonder Bill Gates is a genius among the so-called computer nerds out there. I detest Windows and all their bloated garbage - but you have to admit that their aim is to make it simple for us Idiots out here. My productivity has dropped to minus a few hundred percent since I started to set up mysql. Is there somebody out there who could help me figure out this mess. What error files, if any should I be looking for... O, yes - to add to all the shit, I now cannot access php (index) files on apache22 since upgrading to apache22 2.2.11 and php5.2.8 - now I have to try to figure what has changed in both those programs... man, this is no way to work with programs... look at Adobe and most of the commercial stuff - there are at times some running issues, but mostly they just keep adding features to a solid base... why are all these open source programs such a hassle... I suppose I should set up some older versions and just never update... seems that's the way to go. Sorry to be ranting, but this is just unbelievable... I have, in the past had some problems in installing such programs as cups and samba, but eventually I was able to fix things and now those programs are no longer a problem... but noweven pgsql was a breeze to install and update... the only reason I'm trying to install mysql is because my ISP Web host is using that... maybe not if I can help it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: installation nightmare
John Daisley wrote: Take a look at the online documentation, it will give you all the info you could dream of regarding error files, installation procedures and configuration issues. I have been looking at this stuff until my eyes pop out - perhaps there is too much of it. It certainly is very confusing. I have never installed MySQL on Freebsd so can't help you there but I have installed it on Redhat, Centos, Solaris, windows and Mac with the utmost ease and I really cannot see the FreeBSD install being anything different. Sounds like you have a lot of other software issues too, maybe you should look at the problems of the system as a whole! If something refuses to install its usually a fault with the system / software configuration you are trying to install too and rarely a fault with the installation procedure itself. As a matter of fact, I have set up this FreeBSD 7.0 box strictly for modifying and improving a couple of simple (??) websites. Everything was working perfectly util I tried to unistall MySQL6.0.8alpha and install v. 51 to be compatible with the ISP Website host. From what I see, there is a problem in the documentation (or my inability to comprehend) of MySQL. I have been trying for 4 days without any success. And things get more complicated when there is a problem in other software like php5.2.8. I just now reinstalled MySQL5.1.30; I ran mysql_install_db --user=mysql and that seemed to be ok; I then tried to start the server with bin/mysqld_safe -user=mysql and wow... wait... wait... wait how long am I supposed to wait ... the thing just hung up and I had to reboot... how am I supposed to start the thing - I have started it befor with mysql-server start from /usr/local/etc/rc.d/ and even by rebooting... that's it for today - tomorrow I'll try again.. absurdity seems to never end... :-( */John Daisley/* Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator http://www.mysql.com/certification/candidates.php?exam=4(CMDBA) MySQL Certified Developer http://www.mysql.com/certification/candidates.php?exam=3 (CMDEV) MySQL Certified Associate http://www.mysql.com/certification/candidates.php?exam=6 (CMA) Comptia A+ Certified Professional IT Technician On Fri, 2008-12-19 at 15:47 -0500, PJ wrote: Do what I can, there seems to be no way to install mysql 5.1.30 on FreeBSD 7.0 I even removed all files in reference to mysql5.0, 5.1, 6.0.8 from the Ports collection, cvsup'd the Ports, deinstalled php5, php5-mysql, phpMyAdmin, Webmin and followed the documentation as closely as possible (a number of contradictions in file names and locations) to install MySQL 5.1.30, run the install tables script, change the root password both with and without the server name (it is never clear in the documentation as to just when to start or stop the server) - the mysql database is not created, access is denied to root (using password=NO) - now, why is one supposed to set up the password and then the password is not accepted - what is this nonsense; su mysql does not work how is it possible that I was originally able to set up mysql6.0.8 and get it running along with phpMyAdmin and Webmin... now I have updated even php5 to 5.2.8 and now phpMyAdmin cannot be installed because of a break in php5.2.8 (the php5-sld - or something like that) This is the most absurd mess that I have seen - it seems that the programmers have all gone loony and have forgotten that the essence of productivity and success is the old KISS principle - Keep It Simple Stupid. I think there must be a lot of people sleeping out there in computer never-never-land. No wonder Bill Gates is a genius among the so-called computer nerds out there. I detest Windows and all their bloated garbage - but you have to admit that their aim is to make it simple for us Idiots out here. My productivity has dropped to minus a few hundred percent since I started to set up mysql. Is there somebody out there who could help me figure out this mess. What error files, if any should I be looking for... O, yes - to add to all the shit, I now cannot access php (index) files on apache22 since upgrading to apache22 2.2.11 and php5.2.8 - now I have to try to figure what has changed in both those programs... man, this is no way to work with programs... look at Adobe and most of the commercial stuff - there are at times some running issues, but mostly they just keep adding features to a solid base... why are all these open source programs such a hassle... I suppose I should set up some older versions and just never update... seems that's the way to go. Sorry to be ranting, but this is just unbelievable... I have, in the past had some problems in installing such programs as cups and samba, but eventually I was able to fix things and now those programs are no longer a problem... but noweven pgsql was a breeze to install and update
installation nightmare
Or should I say 4-day-mare... I have been trying to set up mysql for the last 4 days with weird success. I first installed mysql 6.0.8alpha not knowing I needed 5.0 compatibility for my ISP Website host. I go everything working pretty well... mysql6, php5, phpMyAdmin, all on FreeBSD 7.0 with apache2.2.9_5; all but the setup of the pba_history table for phpmyadmin - just no way. Anyway, I unistalled mysql6 and am now trying to install mySQL 5.1.30 from the FreeBSD ports collection. I have tried about everything I can think of besides the arcane documentation that is not at all accurate - for instance, there is no mysq-test.pl or something like that that is referenced in the database, but there is mysqltest in the .../bin directory. However, running that results in errors. When I do manage to set the root user password, it is not accepted... I just cannot understand what kind of a mess this is. The only thing I can provide is the error file: (biggie - is the name of the server) 081218 17:40:17 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:40:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:40:18 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:40:18 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:46:04 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:46:04 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:46:04 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:46:04 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:54:09 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:54:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:54:09 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:54:09 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended Either I am totally incompetent and cannot decipher the instructions or there is some kind of beatie running here... Hope somebody can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
