Basic query question

[yuckysocks]

Hi,

 

I have an html table which is drawn from a MySQL table like so:

 

$query = 'SELECT schoolname, schoolcity, state FROM casestudies';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table class=\"sortable\">\n";
echo "<th>School Name</th> <th>School City</th> <th>State</th>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $schoolname) {
        echo "\t\t<td>$schoolname</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";

 

This pulls only three fields from the database for each school (out of ~60)... what I'd like to do is have the schoolname field in the html table be a link to a page which shows -all- of the row from the database. How is the best way to do this?

 

If I change

foreach ($line as $schoolname) {
        echo "\t\t<td>$schoolname</td>\n";

 

into

 

foreach ($line as $schoolname) {
        echo "\t\t<td><a href=\"$schoolname.html\"</a></td>\n";

 

I could just create an .html file for each page, but I want this to happen automatically. I don't know enough about php to know how to start this. Thanks a lot for any help,

 

Alex

[mb81]

Don't create a new page for every school, you need to make sure your casestudies table has a primary key (usually named id and it can autoincrement itself), then you create a link like schoolinfo.php?id={id}

[yuckysocks]

Ok, I put in the id field which autoincrements. Since the table generates itself, how could I write a link that uses the id number of the database row? I added "id" to the query line, and now it just shows up in the table.

 

I guess what I really need is a deeper understanding of what exactly this script is doing. (It's from php.net)

 

What I really want is for the script to pull the three columns it is right now, PLUS the ID field, but not display all 4 (just show the three it already does, that is, schoolname, schoolcity, and state). Then I want to plug that id in as something like schoolinfo.php?id={$id}, right?

 

 

[mb81]

Here is some code that will help:

 

--With only 3 fields displaying, you don't really need a foreach loop to go through them, so below I broke out each of the fields

--Each time you go through the while loop, line becomes an associative array, meaning the keys are the fieldnames, so $line['id'] is the id, $line['schoolname'] is the schoolname, etc.

-- The link is created by putting the id in there, on the schoolinfo page, you will need to grab that id using $_REQUEST['id'] or $_GET['id'] variable, then use that to look up your school name and information.

 

$query = 'SELECT id,schoolname, schoolcity, state FROM casestudies';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table class=\"sortable\">\n";
echo "<th>School Name</th> <th>School City</th> <th>State</th>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    echo "\t\t<td><A HREF='schooinfo.php?id=".$line['id']."'>".$line['schoolname']."</a></td>\n";
    echo "\t\t<td>".$line['schoolcity']."</td>\n";
    echo "\t\t<td>".$line['state']."</td>\n";
    echo "\t</tr>\n";
}
echo "</table>\n";

 

 

[Barand]

something like

<?php 
$query = 'SELECT schoolID, schoolname, schoolcity, state FROM casestudies';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table class=\"sortable\">\n";
echo "<th>School Name</th> <th>School City</th> <th>State</th>";
while (list($id, $name, $city, $state) = mysql_fetch_row($result)) {
    echo "\t<tr>\n";
    echo "\t<td><a href='schoolinfo.php?id=$id'>$name</a></td>\n";
    echo "\t<td>$city</td>\n";
    echo "\t<td>$state</td>\n";
    echo "\t</tr>\n";
}
echo "</table>\n";
?>

 

A very basic "schoolinfo.php" would be

<?php
$id = intval($_GET['id']);

$query = "SELECT * FROM casestudies
            WHERE schoolID = $id";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row = mysql_fetch_assoc($result);

    foreach ($row as $fld => $val)
    {
        echo "$fld : $val <br/>";
    }

?>

[yuckysocks]

I can't believe I got such timely help. Thanks so much.

 

 

(I want to mark this "solved" like I'm supposed to, but can't see the link...)