Getting Records using a link

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
wmowat
Forum Newbie
Posts: 6
Joined: Sun Sep 21, 2003 3:29 pm
Location: North Bay, ON, CA

Getting Records using a link

Post by wmowat »

Hi all,

Was wondering if I could get some help. I'm very new to php and want to make a database driven site of horror movies.

So far this is what I have http://www.nearnorthweb.com/one.php

I want to be able to click on the title of the movie, and it will display
the complete record. we have a rating out of 10, comments, cast, directors, and so on. How would I go about doing this....we had it all set up in asp but now are trying to do it in php and mysql because my server won't support access and asp

My page code so far...

Code: Select all

<html>
 <head>
  <title>PHP Test</title>
 </head>
 <body>
 <?php $dbh=mysql_connect ("localhost", "wmowat_horrordb", "horror") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("wmowat_horror");

/* Performing SQL query */
   $query = "SELECT Movies.MovieTitle FROM Movies";
    $result = mysql_query($query) or die("Query failed : " . mysql_error()); 
	
	 /* Printing results in HTML */
    print "<table width=100%>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) &#123;
        print "\t<tr>\n";
        foreach ($line as $col_value) &#123;
            print "\t\t</tr><td>$col_value</td><tr>\n";
        &#125;
        print "\t</tr>\n";
    &#125;
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($dbh);
?>
 </body>
</html>
Any help would be greatly appreciated

Thanks,

Will Mowat
wmowat@nearnorthweb.com
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post by Nay »

IMHO, create another page called details.php.

replace the current:

Code: Select all

print "\t\t</tr><td>$col_value</td><tr>\n";
with

Code: Select all

print "\t\t</tr><td><a href="details.php?col=$col_value">$col_value</a></td><tr>\n";
and then in details.php

Code: Select all

<?

$movie = $_GET['col'];

// connect to mysql

// i'm not writing the entire code here, i need to go soon

$q = "SELECT * FROM 'movies' WHERE title = '$movie'";

$result = mysql_query($q, $con);

for($return=mysql_fetch_array($result)) {
echo <<< END

<table cellpadding="0" cellspacing="0" border="0">\n
<tr><td>Name: $movie</td></tr>\n
<tr><td>Director: $return['director']</td></tr>\n
<tr><td>Cast: $return['cast']</td></tr>\n
<tr><td>Rating: $return['rating']</td></tr>\n
</table>\n\n

END;
}

?>
I'm not sure about 100% functionality, i just wrote it here on the spot - but I hope it helps.

-Nay
wmowat
Forum Newbie
Posts: 6
Joined: Sun Sep 21, 2003 3:29 pm
Location: North Bay, ON, CA

Post by wmowat »

It sure will, all I need is the basics. I'm trying to learn php....
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

As Nay states, a seperate details.php page would be worth looking into (it wont stop you from learning either). If you go with the one-page solution, you could instead of using the movie-titles, use their id's (if you are using that in the database...

Code: Select all

ID      NAME
--      ----
 1       Foo
 2       Bar
 3       Kittythrow
Using links with the corrensponding ID's (example above) will ease things up.

Code: Select all

<a href="index.php?number=1">Foo</a>
<a href="index.php?number=2">Bar</a>
<a href="index.php?number=3">Kittythrow</a>
<?php
if (isset($_GET['number'])) {
 $result = mysql_query("select * from table where id = '$_GET[number]'");
 while ($row = mysql_fetch_array($result)) {
  // code
 }
} else {
 echo 'Select a number';
}
?>
...or somethign similiar. The details.php solution is still preferred tho. Have fun.
wmowat
Forum Newbie
Posts: 6
Joined: Sun Sep 21, 2003 3:29 pm
Location: North Bay, ON, CA

Post by wmowat »

Jam,

If you view the website you will see that I have around 300+ records for my movie database. So I think the best method for this would be to use a details.php. I think, unless you can somehow generate that linking code from a looped array?

Here is my code so far, but I get a parse error...


This board is excellent by the way, it's the best resource I've found by far.

Code: Select all

<?php 

$dbh = mysql_connect ("localhost", "wmowat_horrordb", "horror") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("wmowat_horror");

$movie = $_GET&#1111;'col']; 

$q = "SELECT * FROM 'Movies' WHERE MovieTitle = '$movie'"; 

$result = mysql_query($q,$dbh); 

for($return=mysql_fetch_array($result)) &#123; 
echo <<< END 

<table cellpadding=0 cellspacing=0 border=0>\n 
<tr><td>Name: $movie</td></tr>\n 
<tr><td>Director: $return&#1111;'director']</td></tr>\n 
<tr><td>Cast: $return&#1111;'cast']</td></tr>\n 
<tr><td>Rating: $return&#1111;'rating']</td></tr>\n 
</table>\n\n" 

END; 
&#125; 

    mysql_close($dbh);

?>
The parse error is here

Code: Select all

for($return=mysql_fetch_array($result)) &#123;
I think I have fixed all the fields and stuff in details.php but I can't figure out what this error means!

Code for one.php

Code: Select all

<html>
 <head>
  <title>PHP Test</title>
 </head>
 <body>
 <?php $dbh=mysql_connect ("localhost", "wmowat_horrordb", "horror") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("wmowat_horror");

/* Performing SQL query */
   $query = "SELECT Movies.MovieTitle FROM Movies";
    $result = mysql_query($query) or die("Query failed : " . mysql_error()); 
	
	 /* Printing results in HTML */
    print "<table width=100%>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) &#123;
        print "\t<tr>\n";
        foreach ($line as $col_value) &#123;
            print "\t\t</tr><td><a href="details.php?col=$col_value">$col_value</a></td><tr>\n";
        &#125;
        print "\t</tr>\n";
    &#125;
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($dbh);
?>
 </body>
</html>
Appears to be working fine...
Post Reply