Simple I'm Sure

[mcfmullen]

Hello,

 

I'm just getting into MySQL and PHP and have come across something that I can't find any tutorials on: creating dynamic links.

 

I'm trying to build a database of films that have basic things like title, length, genre as well as credits. This is fine. However, I also want to be able to click on any person's name to display a page listing contact information and all films this person has worked on (kinda like how IMDB works). This is how I have my database setup:

 

Films table: ID, filmTitle, filmGenre, FilmLength

Credits table: ID, filmTitle, creditJob, personName

Person table: ID, personName, eMail, photo, bio

 

so I understand how to join these three tables together and as you can see, I'm setting up filmTitle and personName as the primary keys to relate all the tables together. I can build a page displaying a given film title, it's genre and length and list all credits (job and person's name) for that given film, and I can build a page displaying a person's name, photo and contact info, but how do I make it so that I can link the person's name in the film page to his detailed contact page?

 

I'm assuming this is very simple, but as I said, there's no tutorials out there explaining this...

[Psycho]

Well, first off you need to modify your table structures. The reason you use an ID in your tables is so that you can reference that unique piece of data to tie information together. So, your associative table (Credits) only needs to use IDs. Also, there is no need to use an ID on that table.

 

Films table: filmID, filmTitle, filmGenre, FilmLength

Credits table: filmID, creditJob, personID

Person table: personID, personName, eMail, photo, bio

 

Now let's say you are going to query the data for your film page. you are going to want to query for the credits info on that page as well. While doing so you will want to get the person ID as well to use in the links.

 

Sample code:

<?php

$query = "SELECT * FROM films
          JOIN credits ON films.id = credits.filmID
          JOIN persons ON credits.personID = persons.personID
          WHERE filmID = 5";
$result = mysql_query($query) or die (mysql_error());

$first = true;
while ($film = mysql_fetch_assoc($result)) {

  if ($first) {
    $first = false;
    echo "Title: " . $film['title'] . "<br>";
    echo "Genre: " . $film['genre'] . "<br>";
    echo "Length: " . $film['length'] . "<br>";
    echo "Credist:<br>";
  }

  echo "< a href=\"showperson.php?id=".$film['personID']."\">".$film['personName']."</a><br>";
}

?>

[mcfmullen]

Thank you very much for your quick response! I'm just wondering about one thing, if I wanted to format the output of showperson.php?id=".$film['personID'], would it be a matter of creating a showperson.php file with the appropriate formatting or is there more to it than that? If anyone can point me to a tutorial that explains the concept of ?id= that would be great too.

[revraz]

Yes, you would create the showperson.php script and use $_GET['id'] to retreive the id= part of the URL passed.

[DeanWhitehouse]

e.g.

if(isset($_GET['test']))

{

//this is what happens when the URL ending is ?test

}

[mcfmullen]

okay, new problem:

 

In phpmyadmin, I'm trying to import data from a csv file made in Excel. The table I'm importing into is set up the same as the table in Excel. When I go to "Insert data from a text file into the table", I select my csv file, change "Fields terminated by" with a comma, but when I import the data, it will only import the top row and ignore the rest of the data.

 

How do I get it to import more than just the top row?

[BlueSkyIS]

what are you using for 'Fields enclosed by'? Excel typically exports to CSV with single-quote'd fields (not double-quote).

 

what are you using for 'Lines terminated by'? it's probably set to auto, but you might want back-slash r and/or back-slash n:

\r and/or \n

 

what operating system was the file created on (Windows, Mac, Linux, etc.)?

[mcfmullen]

It was created on Mac. I'm using fields enclosed by double quotes because that seems to be what the csv file is using (though i tried single quotes with the same results) and lines terminated by \r and \n...

 

I'm really perplexed as to why this isn't working.

[BlueSkyIS]

have you tried lines terminated with \r\n (both together)?

 

you might also want to open the file in BBEdit (my preference) and save as..., then click Options and specify Unix line breaks.

[mcfmullen]

I did try \r and \n  together (it's the default) as well as separately with no change in results. I did as you said in BBEdit and still no difference. Furthermore, when I specify to replace table contents with the data in the csv, it won't. No matter what I do, it will always take the first row only and add it to a new row...