Professional Web Applications Themes

search across multiple records? - MySQL

Hello, I have an HTML file with <form etc in which the names of composers are listed. Say Beethoven, Mozart, etc. In the MySQL database I have records composer1 Beethoven composer1work1 Symphony No.4 composer2 Mozart composer2work1 Piano Sonata No.2 etc etc. How can I form the SQL statement so that the search will find composers and their works in different records? Any pointers please?! Cheers, Geoff...

  1. #1

    Default search across multiple records?

    Hello,

    I have an HTML file with <form etc in which the names of composers are
    listed. Say Beethoven, Mozart, etc.

    In the MySQL database I have records

    composer1 Beethoven
    composer1work1 Symphony No.4

    composer2 Mozart
    composer2work1 Piano Sonata No.2

    etc etc.

    How can I form the SQL statement so that the search will find
    composers and their works in different records?

    Any pointers please?!

    Cheers,

    Geoff




    Geoff Guest

  2. #2

    Default Re: search across multiple records?

    Geoff <Cox> wrote: 


    Set it up like this:

    TABLE composers
    composer_id int
    composer_name varchar

    TABLE works
    work_id int
    work_name varchar
    composer_id int

    Or possibly, for allowing several composers for a work, move composer_id
    to a relational table:

    TABLE work_composers
    work_id
    composer_id

    List composers:
    SELECT composer_id,composer_name FROM composers

    List works of a particular composer:
    SELECT work_id, work,name FROM works WHERE composer_id = <id of the
    composer>

    Or, if you went with the relational table:
    SELECT wc.work_id, w.work_name
    FROM work_composers wc
    JOIN works w
    ON w.work_id = wc.work_id
    WHERE wc.composer_id = <id of the composer>

    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

  3. #3

    Default Re: search across multiple records?

    On Sat, 10 Mar 2007 17:31:32 +0100, Rik <com>
    wrote:
     
    >
    >
    >Set it up like this:
    >
    >TABLE composers
    > composer_id int
    > composer_name varchar
    >
    >TABLE works
    > work_id int
    > work_name varchar
    > composer_id int
    >
    >Or possibly, for allowing several composers for a work, move composer_id
    >to a relational table:
    >
    >TABLE work_composers
    > work_id
    > composer_id[/ref]

    Food for thought! Thanks Rik.

    Cheers

    Geoff











     
    Geoff Guest

  4. #4

    Default Re: search across multiple records?

    Rik,

    Just thought of another, simpler way,

    I have added a field to each record in which I have added the names of
    the composers so I can now make a "Like" select on this field and get
    all the records for each particular composer.

    Seems to work OK - see any problems?!

    Cheers

    Geoff
    Geoff Guest

  5. #5

    Default Re: search across multiple records?

    Geoff <Cox> wrote:
     

    Yes, consistency:

    J.S. Bach
    Johan Sebastian Bach
    JS Bach
    Bach
    j.s. bach

    Seems the same composer to me, not to your current database setup. Look up
    normalisation among others. Also, you idea is not necessarily a 'simpler'
    way. True, you have only ont table, but getting good results from it is
    tricky. What if you want a list of composers and the number of works for
    instance? Would be much simpler, and above all blindingly faster with the
    proposed setup.
    --
    Rik Wasmus
    Posted on Usenet, not any forum you might see this in.
    Ask Smart Questions: http://tinyurl.com/anel
    Rik Guest

  6. #6

    Default Re: search across multiple records?

    On Sat, 10 Mar 2007 19:37:24 +0100, Rik <com>
    wrote:
     
    >
    >Yes, consistency:
    >
    >J.S. Bach
    >Johan Sebastian Bach
    >JS Bach
    >Bach
    >j.s. bach
    >
    >Seems the same composer to me, not to your current database setup. Look up
    >normalisation among others. Also, you idea is not necessarily a 'simpler'
    >way. True, you have only ont table, but getting good results from it is
    >tricky. What if you want a list of composers and the number of works for
    >instance? Would be much simpler, and above all blindingly faster with the
    >proposed setup.[/ref]

    OK! Had better have a go with your approach.

    Cheers

    Geoff
    Geoff Guest

Similar Threads

  1. CF Multiple Queries, Multiple Records
    By OnDemand Junkie in forum Macromedia ColdFusion
    Replies: 2
    Last Post: July 4th, 07:44 AM
  2. update multiple records in multiple tables from one form
    By Anj01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 17th, 05:54 AM
  3. ANN: InterAKT Site Search - search in multiple tables
    By Alexandro Colorado in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 29th, 04:23 PM
  4. Search / Filter Records
    By jeffreycope in forum ASP.NET General
    Replies: 0
    Last Post: July 25th, 02:53 AM
  5. Search records in forms
    By Nathalie Hildesheim in forum Microsoft Access
    Replies: 1
    Last Post: July 14th, 07:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139