yuckysocks Posted March 21, 2008 Share Posted March 21, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/97273-basic-query-question/ Share on other sites More sharing options...
mb81 Posted March 21, 2008 Share Posted March 21, 2008 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} Quote Link to comment https://forums.phpfreaks.com/topic/97273-basic-query-question/#findComment-497765 Share on other sites More sharing options...
yuckysocks Posted March 21, 2008 Author Share Posted March 21, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/97273-basic-query-question/#findComment-497778 Share on other sites More sharing options...
mb81 Posted March 21, 2008 Share Posted March 21, 2008 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"; Quote Link to comment https://forums.phpfreaks.com/topic/97273-basic-query-question/#findComment-497792 Share on other sites More sharing options...
Barand Posted March 21, 2008 Share Posted March 21, 2008 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/>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/97273-basic-query-question/#findComment-497799 Share on other sites More sharing options...
yuckysocks Posted March 21, 2008 Author Share Posted March 21, 2008 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...) Quote Link to comment https://forums.phpfreaks.com/topic/97273-basic-query-question/#findComment-497806 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.