[RESOLVED] Total n00b - pls don't laugh at me :S

[kevo1162]

Hello,

Recently I've gotten into the LAMP package because of a project i've been doing at work... Never really used Linux before and i setup my lamp machine with ubuntu 7.10 server. A real no-brainer IMO, problem is everything i did was already pre-made for me, I used php-syslog-ng for viewing syslog events via a web page that accesses a MySQL DB that stores the events. So now that my interest is peaked in this area i want to actually learn about it and understand. Among the books I've purchased what I'm focusing on at the moment is 'beginning PHP5, Apache, MySQL web development' by wiley/wrox. Very nice book but because it's a combination of 3 things I'm finding it doesn't go into great depth with some things.

I'm at the end of chapter 3 'Using PHP with SQL' and trying to do the exercises. First one says:
'Create a PHP script that displays the lead actor and director for each movie in the database' ( I should mention that I've created a database with 3 tables). I don't think I need to explain the structure of the database because I just want to know if someone can explain the following code to me. This is what they have as the solution:

<?php
//connect to MySQL
$connect = mysql_connect(“localhost”, “root”, “mysqlpass”) or
     die (“Hey loser, check your server connection.”);
//make sure we’re using the right database
mysql_select_db (“wiley”);
//create function to get lead actor
function get_leadactor($lead_actor) {
global $actorname;
     $query2=”SELECT people_fullname
          FROM people
          where people.people_id = $lead_actor”;
          $results=mysql_query($query2)
               or die(mysql_error());
          $rows=mysql_fetch_array($results);
          extract ($rows);
          $actorname=$people_fullname;
}
//create a function to get director
function get_director($director) {
global $directorname;
     $query2=”SELECT people_fullname
          FROM people
          where people.people_id = ‘$director’”;
          $results=mysql_query($query2)
               or die(mysql_error());
          $rows=mysql_fetch_array($results);
          extract($rows);
          $directorname = $people_fullname;
}
//add a header row
echo “<table border=’1’>\n”;
echo “<tr>\n”;
echo “<td><strong>Movie Name</strong></td>”;
echo “<td><strong>Lead Actor</strong></td>”;
echo “<td><strong>Director</strong></td>”;
echo “</tr>”;
//get the movies
$query=”SELECT * FROM movie”;
$results=mysql_query($query)
     or die(mysql_error());
while ($rows=mysql_fetch_assoc($results)) {
extract ($rows);
//call our functions to get specific info
get_leadactor($movie_leadactor);
get_director($movie_director);
//build the table
echo “<tr>\n”;
     echo “<td>\n”;
     echo $movie_name;
     echo “</td>\n”;
     echo “<td>”;
     echo $actorname;
     echo “</td>\n”;
     echo “<td>\n”;
     echo $directorname;
     echo “</td>\n”;
echo “</tr>\n”;
}
echo “</table>\n”;
?>

What I don't understand is $query2 in the 2 functions... I understand everything up to 'FROM people', the book doesn't explain using periods in the query syntax (i.e people.people_id = '$director') So I don't understand how that works exactly. What the function does (i assume) is takes the 'people_id' from the 'movie' table and relates that to an actual name in the 'people' table, how exactly it's doing that has me confused.

Any help is appreciated

[NogDog]

In a SQL query, you can prepend a table name to a column name using the "." with the purpose of specifying which table is being referred to for that particular column. So "people.people_id" refers to the people_id column within the people table. In this particular example, using the table specifier in this manner is not necessary since only one table is being queried, so there can be no ambiguity as to which table is meant when the column name "people_id" is used. It is often required though in relational queries against two or more tables which may have certain column names in common. However, it does not hurt anything to use the table specifier even if it is redundant, and in complex queries it can be useful to do so in order to provide information to the programmer when viewing updating the code.

[kevo1162]

Thank you for that, just one more question RE the code:

where people.people_id = ‘$director’

Is that pattern matching based on the text string 'director' in the field name or is it a variable? When i was runing through the mysql tutorial in the reference manual it said pattern matching could be done via _, %, LIKE, NOT LIKE, etc... But i don't remember seeing $.

[laserlight]

That's variable interpolation in a double quoted PHP string.

[kevo1162]

laserlight wrote:

That's variable interpolation in a double quoted PHP string.

Ok i'm just not wrapping my head around this... I ran through some of the mysql tutorials on executing queries, but this code is defining the criteria (WHERE) on a variable? The variable wasn't given a value, so how does the query know what i'm looking for?

Sorry, i know this is basic but i'd rather know then not know. Even if someone could point me to a tutorial covering this, i'm not finding much (due to not knowing what exactly to be searching for.)

Thanks for your patience.

[NogDog]

$director is a PHP variable, not a SQL variable. So starting with this PHP code:

$query2="SELECT people_fullname
          FROM people
          where people.people_id = '$director'";

If $director contains the value "Oliver Stone", then $query2 is set to the value:

SELECT people_fullname
          FROM people
          where people.people_id = 'Oliver Stone'

That string (assigned to variable $query2) is then passed to MySQL via the mysql_query() command. Therefore MySQL never sees the variable name $director, but rather sees the value stored in that variable by PHP.

PS: As a side note, be careful with your quotes. You have directional quotes in your code sample (left and right quotes), but PHP is looking for non-directional quotes. You therefore probably want to use a text editor designed for writing code, rather than a word processor which may have a "smart quotes" setting or such that converts straight quotes into directional quotes, and in so doing totally screws up your PHP code.

[kevo1162]

PS: As a side note, be careful with your quotes. You have directional quotes in your code sample (left and right quotes), but PHP is looking for non-direction quotes. You therefore probably want to use a text editor designed for writing code, rather than a word processor which may have a "smart quotes" setting or such that converts straight quotes into directional quotes, and in so doing totally screw up your PHP code.

Yeah, i just copied that from the e-book version not the actual source i'm working on which doesn't have the directional quotes.

You're explanation has cleared my road block and greatly aided my understanding of those functions. Thank you for taking the time to draw the picture for me.

[NogDog]

You're welcome. If you feel this topic has been successfully resolved, please use the "Thread Tools" link at the top of the thread and select the "Mark Thread Resolved" option. And welcome to PHPBuilder. 🙂

[kevo1162]

Ok i understand what you have told me, but as i was going along i came across some more code that didn't quite make sense to me.

I guess i should show what my tables look like, it will probably help me more than anything...

This is the 'movie' table:

|movie_id|movie_name|movie_type|movie_year|movie_leadactor|movie_director|

movie_id = int(11) auto increment
movie_name = varchar(255)
movie_type = tinyint(2)
movie_leadactor = int(11)
movie_director = int(11)

This is the 'people' table:

|people_id|people_fullname|people_isactor|people_isdirector|

people_id = int(11) - auto increment
people_fullname - varchar(25) - (this is the actor/director full name)
people_isactor - tinyint(1) - (1 = actor, 0 = not actor)
people_isdirector - tinyint(1) - (1 = director, 0 = not director)

This is the 'movietype' table:

|movietype_id|movietype_label|

movietype_id = tinyint(11) - auto increment
movietype_label = varchar(100) - (this is the genre i.e comedy, drama, etc...)

So where the code actually starts doing anything is when it begins pulling all the information from the movie table

$query=”SELECT * FROM movie”; 
$results=mysql_query($query) 
     or die(mysql_error()); 

so if i understand at this point $query is the query parameters stored in a variable and the mysql_query($query) is sending those parameters to the server, the results are then stored in a temporary array ($results)... am i on the right track?

What i don't understand, and this book doesn't illustrate very well is how this script is associating movie_leadactor and movie_director from the 'movie' table with the people_id field in the 'people' table.

[kevo1162]

Ok, ignore my last post.... After carefully examining this code and following how it flows i think i've got it.

ok so when this is executed

get_leadactor($movie_leadactor);

The integer value in 'movie_leadactor' for the current row is stored in the variable $lead_actor and the function looks for that value in the people table via:

 WHERE people.people_id = $lead_actor”;

when it finds it the value, the adjacent cell ('people_fullname') is saved in $actorname via:

$actorname=$people_fullname;

Then it's simply echoed out at the end of the code... Is that right? am i missing anything or not using proper terminology?