Help! querying a mysql database from a form select menu

[icue]

Hi,

 

I have a mysql database of music venues and a PHP scripts to display all the venues

in a div. The page also has a form with a select menu of music genres. What I would

like to do is have the user choose a genre (jazz for example) from the select menu

and have the div update without reloading the page to only show venues that play jazz.

 

I searched and found a tutorial here: http://www.w3schools.com/php/php_ajax_database.asp

but I am not sure how to convert it to work with my database as it appears the 'id' is

required to get it to work and my database is not setup to do anything with the 'id'.

 

I am not that familliar with databases and have stored my data in the following way:

id:

incremental

 

Cat_id:

can be 1, 2 or 3 depending on if the venue is a club, bar or live music venue

 

Name:

Venue Name

 

Address:

Venue Address

 

img:

c3.jpg

 

Desc

Music Played: Jazz, Blues, R&B, House etc., (will be different for each venue)

 

Info:

Detailed venue info

 

url:

Web Address

 

Is it possible to use the 'desc' field as it is to perform the genre search?

 

This is the PHP I am using to display the venues on the page:

 

<?php

require_once '../includes/db_connect.php';

 

$result = mysql_query("SELECT * FROM search ORDER BY name");

if (!$result) die ("Database access failed: " . mysql_error());

$rows = mysql_num_rows($result);

for ($i = 0 ; $i < $rows ; ++$i)

{

$img = mysql_result($result,$i,'img');

$name = mysql_result($result,$i,'name');

$address = mysql_result($result,$i,'address');

$desc = mysql_result($result,$i,'desc');

$info = mysql_result($result,$i,'info');

 

if($i % 2 == 0) {

 

echo "<div class='textBlock_club'>";

}else{

echo "<div class='textBlock2_club'>";

}

echo "<img width='95' height='95' src='../images/all/" .$img."' alt=$name />

<p><span class=\"add_bold\">$name</span> <br />  <span class=\"add_colorClubs\">$address</span> <br /> $desc <br /> $info</p><a class=\"fancybox fancybox.iframe\" href=slide-div-test-2.html><div class=\"more\"><span class=\"more_color\">more info&#8230;</span></div></a></div>";

}

?>

 

Could I please either get some advice, links to tutorial sites or possible script examples?

 

Thanks in advance.

 

[Barand]

I would recommend that you create a table for you genre types (see attached data model)

and store the id of the relevant genre in your search records. Use this id for your searches by

genre and not the description field.

 

You can then use this genre table to generate the genre selection dropdown eg

 

Do the same for your categories.

 

<?php
$sql = "SELECT idgenre, genre_name
        FROM genre ORDER BY genre_name";
$res = mysql_query($sql);

echo "<select name='genre' id='genre'>
    <option value='0'>- select genre -</option>";
while (list($id, $name) = mysql_fetch_row($res)) {
    echo "<option value='$id'> $name</option>";
}
echo "</select>\n";
?>                                                         

[Barand]

PS sorry, attachment won't load. Appparently a 16K png file is too large

[Barand]

In the absence of an image

 

genre                      search                    category

+--------------+            +--------------+          +--------------+

| idgenre      |------+    | id          |    +-----| idcat        |

| genre_name  |      |    | name        |    |    | category    |

+--------------+      |    | cat_id      |-----+    +--------------+

                      |    | description  |

                      +-----| genre_id    |

                            | etc          |

                            +--------------+

[icue]

Thanks Barand for your answer though I am not sure I fully understand.

 

I think what you are saying is that I should create a table to give each genre an id and its name then

link that to my search table by the same id number. I am not quite sure how to implement the 'cat_id' in

the search table and the 'idcat' in category table. Since a venue can have multiple genres  (jazz, blues, latin...)

do I need to create a separate field in my search table for each genre?

 

Also from your sample code, it looks like you are generating a select genre menu, but I already have a

menu in a form with the full list of genre options. Do I need to use PHP to create the menu?

 

Thanks again.

[Barand]

To handle multiple genres you remove the genre from the search table and create an extra table, say "venue_genre", contining a row for each genre that the venue hosts

 

So if venue 1 hosts genres 1,2 and 3, venue 2 hosts genres 2 and 4 then the table would contain

+------------+------------+
| venue_id   |  genre_id  |
+------------+------------+
|     1      |     1      |
|     1      |     2      |
|     1      |     3      |
|     2      |     2      |
|     2      |     4      |
+------------+------------+


genre                       venue_genre                search                     category
+--------------+                                       +--------------+           +--------------+
| idgenre      |------+                        +------ | id           |     +-----| idcat        |
| genre_name   |      |                        |       | name         |     |     | category     |
+--------------+      |      +-----------+     |       | cat_id       |-----+     +--------------+
                      |      | venue_id  |-----+       | description  |
                      +----- | genre_id  |             |      |
                             +-----------+             | etc          |
                                                       +--------------+

 

At the moment, if the database changes and you add a new genre, you have change your code. If you build the menus from you data then you code still works - picking up any changes automatically.

[icue]

Thanks Barand, I think I get it now. I like the idea of not having to change my code if new

genres are added.

 

The tutorial I found here - http://www.w3schools.com/php/php_ajax_database.asp had a fixed

selection menu but I think your option is much better. I hope I can get it all to work.

 

Thanks again.