Duplicating a table question
These spam filters are really annoying. It's hard to get an answer to my question when my message gets bounced 'cause it doesn't have MySQL or query in the message. In any event, Is there a way to duplicate a complete table. I am making some modifications and don't want to use the working table for testing. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data Infile command
Try this when your logging into mysql, not when starting mysql. This works for me on Linux. mysql --local-infile=1 -u username-p - Original Message - From: Jon Bertsch [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 12, 2002 12:02 PM Subject: Load Data Infile command HI all, I'm new to the list so I apologize if this subject has been answered already, I couldn't find a mailing list archive. We just upgraded (?) mysql to 3.23.49. We are running it on Linux Redhat 7.3 The load data local command is turned off by default. I have attempted to follow the instructions in the documentation (Ch. 4.2.4 and others) about this but it has been a dismal failure. I tried the following on restarting mysql: /etc/init.d/mysqld start --local-infile[=1] as in the manual. No luck. I also made some variations on this: --local-infile=1 --local-infile --local-infile(=1) --local-infile=[1] --local-infile=(1) None of these worked. I added the following two lines to the my.cnf file: [client] enable-local-infile and restarted. No luck. Has anyone been able to get this to work? I would really appreciate any help on this since I use the command to load data and it is part of several utility applications I use. Thanks in advance Jon Bertsch - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Displaying output from MySQL
Hi, Not sure if this is a PHP of a MySQL question, so I am sending it to both groups. Basically I have a list of numbers with two decimal places in the MySQL database, but I only want to display some of them with the decimal points. i.e. 70 (not 70.00) 87 51.5 46.75 12 29 5.5 -1 45 I know it's probably a weird request, but any thoughts on how one would do this either through PHP or MySQL. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
I am using PHP on my website, but this is certainly a MySQL question. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. The real value of $var is not inserted before it is sent to the server. REGEXP thinks that 'var' is part of the search pattern because $ is a reserved operator for REGEXP. So what gets sent to the server is totally different than what I want to be sent. There are no errors because the syntax is correct, its just not correct for the search I want it to perform. So the questions still remains, how do I get REGEXP to treat $var as a variable and not part of its own syntax. I have been looking at http://www.mysql.com/doc/en/Regexp.html, but it does not cover this. - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: Robert Citek [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:30 AM Subject: Re: Select * From table where name Like 'help'; Help Hello. On Mon 2002-12-09 at 01:00:33 -0500, [EMAIL PROTECTED] wrote: I am doing this search from a website, What does that mean? Which scripting language do you use? PHP? This is more a question for your website environment than about MySQL. and from what I can tell there is no way to use a variable (i.e. $var) with REGEXP as the $ sign has a special meaning to REGEXP. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
My mistake, I assumed the space was just for clarity and not really meaningful, but since it is between the ' ' it makes sense that is. I tried a few variations on this and think I have found what I need - where title like binary '%$var%. Notice the key word 'binary' which makes the world of difference. From the tests I have run this will find the occurrence of any word that begins with a capital and then matches any remaining characters. So if I search for One it will fine One Day, Day One, This One Day - however, if I search for On it will also find On The Water and Your On Third. The only thing I could do is to add a space after $var, but them partial searches won't be found. i.e. searching for beaufor will not find beauford. Until I can find a better way, one of these will suffice. Thanks for your correction as I did miss the space thing. Beauford - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Sent: Monday, December 09, 2002 9:31 AM Subject: Re: Select * From table where name Like 'help'; Help No it doesn't, since I introduce a space between % and one. So stone will not be returned :) Regards, Jocelyn - Original Message - From: Beauford.2003 [EMAIL PROTECTED] To: Jocelyn Fournier [EMAIL PROTECTED] Sent: Monday, December 09, 2002 2:26 PM Subject: Re: Select * From table where name Like 'help'; Help That is what I am using, however, it is not as detailed as I need it to be. Like I said in my original question, I don't want to get 'stone' when I search for 'one', and using LIKE does exactly this. Thanks, Beauford - Original Message - From: Jocelyn Fournier [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Sunday, December 08, 2002 7:57 PM Subject: Re: Select * From table where name Like 'help'; Help Hi, A dirty solution would be to search : field LIKE '% one %' or field LIKE 'one %' or field LIKE '% one' or field='one'; Regards, Jocelyn - Original Message - From: Beauford.2003 [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Tuesday, December 10, 2002 12:50 AM Subject: Select * From table where name Like 'help'; Help Hi, Hey, did this list change their spam filters? Now every time I send an email it gets bounced unless the word MySQL or Query is in the email. Anyway, Please read below for a recent problem I am having. I want to be able to search on my database and be able to get the following, but running into a few snags. If I search for the word - One - I want to see: Another One One Day One on One Your The One But not: Fashioned Stone Everyone Also, and maybe part of the solution - is there a way for the search to be case sensitive. TIA Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
Andy, I mentioned in my previous email that I am using PHP, and I have also tried putting quotes around $var (many different ways) with no better results. REGEXP just gives a syntax error when I do this. Through the suggestion of another list user I have found a way that suits my needs using the where name like binary '$var'. It's jury rigged, but works the way I need it to work for the most part. Thanks for your suggestions. - Original Message - From: Andy Bakun [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:08 PM Subject: Re: Select * From table where name Like 'help'; Help You still have not mentioned what language you are using to interface with MySQL. If you are using PHP or Perl, then things like single and double quotes will make a difference here, and looking in the MySQL manual will not help you. Check the string that contains the query, if it looks like this: $q = 'select from table where col regexp $var'; change it to $q = select from table where col regexp '$var'; The enclosing single quotes are keeping the value of $var from being interpolated into the string in the above example. See the manual for your programming language for more information on variable interpolation. On Mon, 2002-12-09 at 08:22, Beauford.2003 wrote: I am using PHP on my website, but this is certainly a MySQL question. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. The real value of $var is not inserted before it is sent to the server. REGEXP thinks that 'var' is part of the search pattern because $ is a reserved operator for REGEXP. So what gets sent to the server is totally different than what I want to be sent. There are no errors because the syntax is correct, its just not correct for the search I want it to perform. So the questions still remains, how do I get REGEXP to treat $var as a variable and not part of its own syntax. I have been looking at http://www.mysql.com/doc/en/Regexp.html, but it does not cover this. - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: Robert Citek [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Monday, December 09, 2002 3:30 AM Subject: Re: Select * From table where name Like 'help'; Help Hello. On Mon 2002-12-09 at 01:00:33 -0500, [EMAIL PROTECTED] wrote: I am doing this search from a website, What does that mean? Which scripting language do you use? PHP? This is more a question for your website environment than about MySQL. and from what I can tell there is no way to use a variable (i.e. $var) with REGEXP as the $ sign has a special meaning to REGEXP. That shouldn't matter, because the real value $var is inserted before the query is send to the MySQL server, where REGEXP of the query is evaluated. If there is a problem, you need to be much more specific. Quote the error message, show the relevant part of your code and so on. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
This is most definitely a quoting issue with your PHP string. If it is
quoted correctly, the MySQL server will never see the string $var
appearing after the keyword REGEXP, so if $ has meaning to a regular
expression wouldn't matter (unless the value of $var contained a $).
I've used REGEXP with (really complex) dynamicly built regular
expressions stored in PHP variables a number of times and have never had
a problem as you've described above.
MySQL is not seeing $var, but it is seeing var from what I can understand of
Regexp (the manual on this is not very good), $ means to match the end of a
string, so since I have $var, I am thinking that Regexp thinks that the end
of the string I want to search for ends with var. In any event, the actual
value of $var never gets sent to MySQL, so the search will always fail.
Maybe I am misunderstanding the meaning of this, but since I really can't
make any sense out of the manual, I am guessing at what I think they mean.
Let me give you and example:
$var=One ($var is passed the string from a form on a webpage)
There are 3 entries in the database with the word One
One Day
Day One
That's the One
There are also other words that contain the word one, like stone and anyone.
I want to be able to run a search that will just show the first 3 by typing
in the word One ('one' would result in the string not found').
Beauford
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Thanks
Thanks to all who recently answered some of my MySQL questions, although I am still having some problems with the concept of joins, but what I have figured out certainly does make it more efficient and versatile. I have managed to eliminate two tables (actually combine them into one) which made my searches easier. Thanks again, Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA command
If your using a Linux machine try using this when logging on to mysql: mysql --local-infile=1 -u username -p Not sure about Windows. There is apparently a security issue with this so it is not enable by default. Can't remember where I read this, but the above works for me. Beauford - Original Message - From: Insanely Great [EMAIL PROTECTED] To: Padma kuruganti [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, December 07, 2002 12:32 PM Subject: Re: LOAD DATA command Greetings... Are you using Windows to connect to the MySQL. Then you can try SQLyog at http://www.webyog.com/sqyog/download.html It will help you a lot in these works and you dont have to write queries also. Rgds Insane - Original Message - From: Padma kuruganti [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 07, 2002 12:46 AM Subject: LOAD DATA command Hi I just downloaded version 3.23.53 for evaluation. when I try to use LOAD DATA LOCAL INFILE 'some.txt into TABLE sometable I get a message saying 'the used command is not allowed in this version of mySQL. IS there any update on this? I could not find any info on your web site. Thanks KP __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select * From table where name Like 'help'; Help
Hi, Hey, did this list change their spam filters? Now every time I send an email it gets bounced unless the word MySQL or Query is in the email. Anyway, Please read below for a recent problem I am having. I want to be able to search on my database and be able to get the following, but running into a few snags. If I search for the word - One - I want to see: Another One One Day One on One Your The One But not: Fashioned Stone Everyone Also, and maybe part of the solution - is there a way for the search to be case sensitive. TIA Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * From table where name Like 'help'; Help
I am doing this search from a website, and from what I can tell there is no way to use a variable (i.e. $var) with REGEXP as the $ sign has a special meaning to REGEXP. Any other suggestions, or would you know a way around this. TIA - Original Message - From: Robert Citek [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Sunday, December 08, 2002 8:16 PM Subject: Re: Select * From table where name Like 'help'; Help Try the REGEXP modifier. For example: mysql select Another One regexp [[::]]One[[::]] ; mysql select Fashioned regexp [[::]]One[[::]] ; The format differs from that used in sed, grep, awk, perl, python, etc. http://www.mysql.com/documentation/mysql/bychapter/manual_Regexp.html#Regexp Regards, - Robert - At 07:50 PM 12/9/2002 -0500, Beauford.2003 wrote: If I search for the word - One - I want to see: Another One But not: Fashioned - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Join help.
Hi, Is there a good tutorial somewhere on the join command. No matter what I do it just doesn't work. Obviously I'm doing something wrong, but the MySQL manual just doesn't help at all. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song So 3.song appears on all 3 albums. Currently I have it set up with two tables as shown below, but I am thinking there has to be a better way to do this than to duplicate the name of the song three, four, or five times in the table. Table AlbumName AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3.song 3 7.song 3 etc. etc. So basically my search is - SELECT Album, Song FROM AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID; Given the setup above, is there a way that I can put in the SongTitle.ID field that song appears on more than one album. Maybe something like: SongID === == 3.song 1, 2, 3 But then what would my search be. Sorry for the length of this, but I am learning MySQL and trying to get a handle on all of it. My way works, but I'm sure there has to be a better way. Any thoughts are appreciated. TIA, Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
Thanks for the help, but I just can't grasp the concept (I'm not even sure I have my table layouts the way they should be to have this work). I'll have to look into this further, but for the time being the way I have it is working. Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 4:28 PM Subject: RE: Table setup question Let's assume you want the entire albums list which the song Day and Night by Billie Holiday is in. SELECT t1.title,t1.artist,t3.name FROM sings t1 INNER JOIN reference t2 ON t1.ids=t2.ids INNER JOIN album t3 ON t2.ida=t3.ida WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday' (or WHERE t1.ids=1 if you know the id of the song) Relationship is like: [songs] [album] \\// [reference] (this later one contains the list of songs of each album) This way you treat songs and album separately and link them through another table so you don't have any limit on the number of albums a song can belong to. If you know the id of a song all you have to do is Adolfo -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 3:42 PM To: Adolfo Bello Cc: [EMAIL PROTECTED] Subject: Re: Table setup question Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work. mysql describe songs; (this contains an ID field and the title of the mysql song and the artist.) ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | ids| int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL| | | artist | varchar(30) | YES | | NULL| | ++-+--+-+-+---+ 3 rows in set (0.00 sec) mysql describe album; (this contains an ID field and the name of the mysql album the above songs came from.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe reference; (this contains the ID's that corrspond to the above two tables.) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. SongAlbum 12 13 16 115 27 221 223 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford - Original Message - From: Adolfo Bello [EMAIL PROTECTED] To: 'Beauford.2003' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question Try something like CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; CREATE TABLE albumsongs( ida int not null, ids int not null, primary key(ida,ids), foreign key(ida) references album(ida), foreign key(ids) references songs(ids)); Querying for albums which have a songs is something like: SELECT title,song FROM album INNER JOIN albumsongs ON ... INNER JOIN songs ON ... WHERE song='your song name'; -Original Message- From: Beauford.2003 [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 01, 2002 9:59 PM To: [EMAIL PROTECTED] Subject: Table setup question Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs
Select, mysql_fetch_array, PHP question
Hi,
I'm trying to do a search on database through a web interface using PHP and
having one small problem. Example: If I search for items that cost $5.00 and
there are three of them, the query works and displays the information, but
if there is only one item the query works but doesn't display the item. I
just get a blank page.
SELECT price, item FROM list WHERE price=$5.00;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
If I do the same search from the MySQL command line the query will display
one or more items.
Ideas?
TIA
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
DL, Yep, I see what your saying. In my application it may work fine, but in larger applications where things may change, it would be easier to do it with the 3 tables. Thanks - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 7:56 AM Subject: Re: Table setup question Beauford, OK, I get it now. I thought there would need to be duplication in the AlbumSonglist DB. =indeed, the process of 'normalisation' (as mentioned earlier) helps to ascertain when and where 'duplication' is allowed/beneficial/should not happen. Just one other question though. What is the difference/benefits of doing it this way, than the way I have it. Currently I use two tables - the songlist table includes 2 id fields (one that corresponds with the album title, and one that is used for the song title). The difference between what you have said and what I have is that I have duplicated the actual song titles instead of a pointer to the song title This way I have eliminated one table. =try wording a query to get from album to song title, and then another query starting from song title and working to album. That's always a good test of two-way relationship situations. =if you have a pointer repeated then (usually) it will take up less storage space than a repeated song title. OTOH getting rid of a table and the join in each query will speed up processing time/throughput (by a v.small amount)... =if some data ever changes (admittedly I can't see that it would in your application - but you're the subject matter expert), eg someone remixes a song, so to be more precise you want to modify Leila to have Leila - original and Leila - remix as two different entries in the db; then how easy would it be to go through the song title data and make that change? In the m-m model the change would be needed only once (the Song Title tbl - all else unaffected). In your model, you would need to be sure that you changed every case - and without knowing how many changes is 'enough'! (now apply this to customer records and people changing addresses and you'll see why/which is the 'industry standard' approach). =however the ultimate answer to your question is: if it works for you/your users, then it is 'right'! Thanks again, Beauford =hope it helps you along your way to MySQL expertise, =dn - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 10:41 AM Subject: Re: Table setup question Beauford, [please reply to the list - there are always others 'lurking' who will learn from the discussion (and use of the archives is a wonderful dream...)] You are correct, there will be duplication in the AlbumSong table - but not within one field, only within one column. Album will consist of a unique ID (IDA) and each Title will, one assumes be unique (unless two groups use the same name - I guess that must be possible). Similarly Songlist will also consist of a unique ID (IDS) and once again, one assumes that any repetition of title will be two different songs that share the same name. These two ID 'uniqueness' rules must apply for the next to work, ie you will most likely define them as PRIMARY KEY columns. The 'translation' table, breaks the 'unique' 'rule', So if song Q apears on albums A, C, and E as you ask, then we will see: AlbumSong IDAIDS 11 21 31 Note that while 1 repeats in AlbumSong, it does not repeat (a) in Songlist, nor in (b) one row/field of AlbumSong, eg IDA IDS 1,2,31 THE ABOVE IS TOTALLY WRONG!!! In the case of AlbumSong the table will not be able to have a PRIMARY KEY column(s), but each of the individual columns should probably be INDEXed for read-speed. Be aware that AlbumSong contains no real 'data' from an end-user perspective. It is a table made up purely of 'links' or 'keys' or 'IDs' to the other two tables. The Album and Songlist tables do all the 'data' work, AlbumSong does all the 'heavy lifting' to relate Album's data to Songlist's, and/or vice-versa. Apologies if this was not clear, first time round, =dn DL, OK, that helps a lot, but I am still confused. I am still struggling with how you can not have duplication in at least one of the tables. A diagram here may help. So if song Q apears on albums A, C, and E - I don't quite understand how AlbumSong is going to be able to know which songs are duplicated without duplication of ID's. (There are 500 songs and only 36 albums). Thanks AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1
Re: Select, mysql_fetch_array, PHP question (sorry - typo in my original email)
Sorry, where it says echo $item should have read echo $line['item']; Simply
a typo, but the problem is still the same.
Hi,
I'm trying to do a search on database through a web interface using PHP and
having one small problem. Example: If I search for items that cost $5.00 and
there are three of them, the query works and displays the information, but
if there is only one item the query works but doesn't display the item. I
just get a blank page.
SELECT price, item FROM list WHERE price=$5.00;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
If I do the same search from the MySQL command line the query will display
one or more items.
Ideas?
TIA
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select, mysql_fetch_array, PHP question
Alex,
That's it - I have a if (mysql_fetch_row($result)) before the while loop.
I've changed it slightly and now it works fine. The little things are always
the ones that get you.
Thanks
- Original Message -
From: Alex Pukinskis [EMAIL PROTECTED]
To: Beauford.2003 [EMAIL PROTECTED]
Sent: Tuesday, December 03, 2002 7:41 PM
Subject: Re: Select, mysql_fetch_array, PHP question
Maybe you have an if ($line = mysql_fetch_array($result)) line
somewhere up above? That would cause the while loop to skip the first
record... I usually use a do...while loop with recordsets to avoid this
problem.
That's my best guess, without seeing more code.
-Alex
On Tuesday, December 3, 2002, at 01:40 PM, Beauford.2003 wrote:
That is what I have, a simple typo on my part.
- Original Message -
From: Alex Pukinskis [EMAIL PROTECTED]
To: Beauford.2003 [EMAIL PROTECTED]
Sent: Tuesday, December 03, 2002 5:04 PM
Subject: Re: Select, mysql_fetch_array, PHP question
To be honest, I'm not sure why what you're doing works at all. You
don't ever assign a value to the variable $item. Try changing
echo $item;
to
echo $line['item'];
Is there more code involved that you didn't include?
-Alex
On Tuesday, December 3, 2002, at 06:38 AM, Beauford.2003 wrote:
Hi,
I'm trying to do a search on database through a web interface using
PHP
and
having one small problem. Example: If I search for items that cost
$5.00 and
there are three of them, the query works and displays the information,
but
if there is only one item the query works but doesn't display the
item. I
just get a blank page.
SELECT price, item FROM list WHERE price=$5.00;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $item; }
If I do the same search from the MySQL command line the query will
display
one or more items.
Ideas?
TIA
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail mysql-unsubscribe-
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table setup question
DL, OK, I get it now. I thought there would need to be duplication in the AlbumSonglist DB. Just one other question though. What is the difference/benefits of doing it this way, than the way I have it. Currently I use two tables - the songlist table includes 2 id fields (one that corresponds with the album title, and one that is used for the song title). The difference between what you have said and what I have is that I have duplicated the actual song titles instead of a pointer to the song title This way I have eliminated one table. Thanks again, Beauford - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 02, 2002 10:41 AM Subject: Re: Table setup question Beauford, [please reply to the list - there are always others 'lurking' who will learn from the discussion (and use of the archives is a wonderful dream...)] You are correct, there will be duplication in the AlbumSong table - but not within one field, only within one column. Album will consist of a unique ID (IDA) and each Title will, one assumes be unique (unless two groups use the same name - I guess that must be possible). Similarly Songlist will also consist of a unique ID (IDS) and once again, one assumes that any repetition of title will be two different songs that share the same name. These two ID 'uniqueness' rules must apply for the next to work, ie you will most likely define them as PRIMARY KEY columns. The 'translation' table, breaks the 'unique' 'rule', So if song Q apears on albums A, C, and E as you ask, then we will see: AlbumSong IDAIDS 11 21 31 Note that while 1 repeats in AlbumSong, it does not repeat (a) in Songlist, nor in (b) one row/field of AlbumSong, eg IDA IDS 1,2,31 THE ABOVE IS TOTALLY WRONG!!! In the case of AlbumSong the table will not be able to have a PRIMARY KEY column(s), but each of the individual columns should probably be INDEXed for read-speed. Be aware that AlbumSong contains no real 'data' from an end-user perspective. It is a table made up purely of 'links' or 'keys' or 'IDs' to the other two tables. The Album and Songlist tables do all the 'data' work, AlbumSong does all the 'heavy lifting' to relate Album's data to Songlist's, and/or vice-versa. Apologies if this was not clear, first time round, =dn DL, OK, that helps a lot, but I am still confused. I am still struggling with how you can not have duplication in at least one of the tables. A diagram here may help. So if song Q apears on albums A, C, and E - I don't quite understand how AlbumSong is going to be able to know which songs are duplicated without duplication of ID's. (There are 500 songs and only 36 albums). Thanks AlbumSonglistAlbumSong IDATITLEIDSSongIDAIDS 1A 1Q?? 2B2R?? 3C3S?? 4D4T?? 5E 5V ?? ? ? ? ? ? ? - Original Message - From: DL Neil [EMAIL PROTECTED] To: Beauford.2003 [EMAIL PROTECTED]; Sent: Monday, December 02, 2002 4:32 AM Subject: Re: Table setup question Hi Beauford, You are on the right track. Yes you should remove the songs to a separate table. If you merely duplicate the first example (below) in two tables you have created a one-to-many relationship between the Album table and the Songs table - one album has many songs on it. Your query code applies. However the Songs table still has duplication in it, eg 3.song appears thrice, and we can't have that! The problem is, if you cut down the Songs table entries so that 3.song appears only once, how do you link to multiple Albums? So now you are into a many-to-many relationship between the Album table and the Songs table - one album has many songs on it AND one song may appear on several albums. Problem! You should not have a field with 'repetition' within it, eg for each album hold two fields: ID, and Songs - where songs is a list of IDs/titles. This is not 'relational'. You can read up about a process known as normalisation which teaches a basic three step process (some go to more than
Table setup question
Hi, I have a database of albums by a group and I want to be able to search on this table to find out what songs are duplicated on what albums (there are 36+ albums). The problem is how do I set up the database. Example: Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song So 3.song appears on all 3 albums. Currently I have it set up with two tables as shown below, but I am thinking there has to be a better way to do this than to duplicate the name of the song three, four, or five times in the table. Table AlbumName AlbumID == Album1 1 Album2 2 Album3 3 Table SongTitle SongID === == 3.song 1 3.song 2 3.song 3 7.song 3 etc. etc. So basically my search is - SELECT Album, Song FROM AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID; Given the setup above, is there a way that I can put in the SongTitle.ID field that song appears on more than one album. Maybe something like: SongID === == 3.song 1, 2, 3 But then what would my search be. Sorry for the length of this, but I am learning MySQL and trying to get a handle on all of it. My way works, but I'm sure there has to be a better way. Any thoughts are appreciated. TIA, Beauford - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
