[Fladrif]

First of all I am quite new to PHP. The site I am currently working on is my first time using PHP, don't know why I didn't try it out before..... I love it.....

Problem:
I have a CSV file with the following fields (first name, last name, address, and year of graduation). What I would like to have happen is: when a graduation year is selected in the drop down, it will return all of the people who graduated that year.

If it would be easier I can import the CSV into mySQL.

Is this do-able? I tried searching google, but I guess the problem is that I don't really know what I'm looking for. =(

Thank you for your help.

[blaw]

Hello there,

If your list is/will be large, you should consider using MySQL over your CSV file. There are many reasons to this, which you'll learn as you go along, but one major thing is the database's oraganizing ability and thus faster access to and better control of the data.

If you need to learn how to retrieve and show results from the database (MySQL) with PHP from the scratch, try this:

http://hotwired.lycos.com/webmonkey/...tutorial4.html

and then to this:

http://ca2.php.net/mysql_connect

After that, you should know which function to look up next. It's not a big list to learn, given what you can do with it. There are many tutorials available for PHP/MySQL both online and books, so you can take a look at them, if the above links don't make sense to you.

Also, this lets you import your CSV file into MySQL database:

http://www.mysql.com/doc/en/LOAD_DATA.html

Good luck.

[Fladrif]

Thank you, that got me started. I was able to import the data in to MySQL and display that data on the page I wanted....

The problem I'm having now is how do I select the data to be shown based on a match in one of the rows in my database?

Here is the script that I'm using now and it works great for showing all of the data in the DB.


********************************
<?php

$db = mysql_connect("localhost", "user", "password");

mysql_select_db("databasename",$db);

$result = mysql_query("SELECT * FROM address",$db);

if ($header = mysql_fetch_array($result)) {

echo "<table border=1>\n";

echo "<tr><td>Name</td><td>Year</td></tr>\n";

do {

printf("<tr><td>%s %s</td><td>%s</tr>\n", $header["First"], $header["Last"], $header["Class"]);

} while ($header = mysql_fetch_array($result));

echo "</table>\n";

} else {

echo "Sorry, no one from that year has registered";

}

?>
********************************


Thank you for your time.

[Fladrif]

I figure it out!! My first query!! I know it's not much, but we all have to start somewhere

$result = mysql_query("SELECT * FROM address WHERE Class='89'");


With that done now I need to figure out how to make the Class= value equal to the selection in the drop down on my form?

So if some one selects 1993 my query will be:
$result = mysql_query("SELECT * FROM address WHERE Class='93'");

Can anyone point me in the right direction?



Thank you for your time.

[blaw]

Hello again,

That's great you figured it out. Keep it on.

One trick I seem to be always recommending in a situation like yours is to pass a parameter to the query in one way or another.

If your combobox is like this:

PHP Code:

<select name="year">

      <option value="92">1992</option>

      <option value="93">1993</option>

      <option value="94">1994</option>

      <option value="95">1995</option>

</select> 

Then pass the value of <option> to your script that contains the query whenever a user selects it. One way to do this is to use an onChange event (JavaScript). Or you can also give the users a submit button next to this combobox and upon submission, pass the parameter.

Either GET or POST, once you receive the parameter, you can substitute the concrete number in your WHERE clause (i.e. "93" in the above example) with the passed variable:

PHP Code:

// Passed via URL like this: [url]www.yoursite.com/script.php?year=93[/url]

// Parameter within a URL is GET method. You can get this value like this:

$year = $_GET['year'];



$result = mysql_query("SELECT * FROM address WHERE Class='$year'"); 

This is a good short intro to HTML forms with PHP if you're interested:

http://www.w3schools.com/php/php_forms.asp

Or you can take a look at this longer, but perhaps more useful tutorial:

http://www-106.ibm.com/developerwork...ry/wa-phpform/

Good luck, then.

[Fladrif]

Thank you again Blaw....

You should have seen me jump up and down when it finally worked....

I was able to get this to work exactly as I wanted, I was even able to use the $_GET['year'] to work in my include statement!!

Which made the coding the output page nice and easy.

Now I'm still having a problem pulling the data from my data base 1/2 of the time. The following script works great for odd numbers (i.e. 1991. 1993, 1995, and so forth), but if I try to grab an even year (i.e. 1990, 1992, 1994, and so forth) nothing is displayed not even the error message....

<?php

//Log on to Database
$host = "localhost";
$user = "username";
$pass = "pass";
$dbname = "db name";
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);
//query for data from class field
$year = $_GET['year'];
$result = mysql_query("SELECT * FROM address WHERE Class='$year'");

if ($header = mysql_fetch_array($result)) {
//create table
echo "<table align=center border=0 cellpadding=0 cellspacing=0>\n";

//echo "<tr><td align=center><strong>%s</strong><hr></td></tr>\n", $header["Class"]);
printf( "<tr><td align=center><strong>%s</strong><hr></td></tr>\n", $header["Class"]);

do {
//write data from class field to new table
printf("<tr><td align=center><br>%s %s</td></tr>\n", $header["First"], $header["Last"]);

} while ($header = mysql_fetch_array($result));


//if there is no data in the db user will get this error
} else {
//echo "Sorry, no one from $year is registered in the alumni database"


printf( "Sorry, no one from<strong> $year </strong>has registered in the alumni database\n", $header["Class"]);
echo "</table>\n";
}
?>

Like I said this works great for any odd numbered year.

Any idea what would cause this to happen? I tried changing the field in my DB to int and year(4), but it didn't solve the problem.

Thank you for your time.