Function Similarity (Ch. 4, p.114-115)

caljay · August 17th, 2011, 02:31 PM

Hello, everyone.

My question is about how to run a certain kind of function. In the Beginning PHP, Apache, MySQL Web Development book through pages 114-115, the Try It Out section displays a section of code including three new functions, all organized along the same structure.

I've included the first below.

Code:

< ?php 
// take in the id of a director and return his/her full name 
function get_director($director_id) { 
global $db; 
$query = âSELECT 
people_fullname 
FROM 
people 
WHERE 
people_id = â . $director_id; 
$result = mysql_query($query, $db) or die(mysql_error($db)); 
$row = mysql_fetch_assoc($result); 
extract($row); 
return $people_fullname; 
} 
?>

Now, at first I had no problems with this code. However, several months later, I found myself working on a program that required this same exact kind of function. So I hawked the code and modified it. A felicitous circumstance.

Code:

< ?php 
// take in the id of a director and return his/her full name 
function get_preludeopt1($preludeopt_a) { 
global $db; 
$query = âSELECT 
song_title 
FROM 
draftsongref 
WHERE 
song_id = â . $preludeopt_a; 
$result = mysql_query($query, $db) or die(mysql_error($db)); 
$row = mysql_fetch_assoc($result); 
extract($row); 
return $song_title; 
} 
?>

And from there, I also modified the original code:

Code:

// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$director = get_director($director_id);

...to get mine:

Code:

// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$prelude1 = get_preludeopt1($preludeopt_a);

Now, the details of the project.

I'm making a small planner that mainly consists of an HTML table that references two different MySQL tables. The first MySQL table has a "name" column for the day in particular, and then a number of other columns, each of which contains a numerical value. Those multi-columnal numerical values each correspond to a single column in a second table, which has information about the songs those numerical values refer to. At the moment, the only information I'm working to retrieve is the song title.

However, I run into a problem mainly when I attempt to run my version of the program.

I always get the message:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6".

As far as I can tell, this refers to the section of the function's query that goes:

Code:

...
FROM 
draftsongref
WHERE 
song_id = ' . $preludeopt_a;

...as when I remove it, leaving myself with this:

Code:

...
FROM 
draftsongref';

...it gives me no error message, though the table will appear incorrectly - when I attempt multiple columns of numbers (each of which is supposed to have its own version of the same function), it will simply output the same song name. To compare it to the original, it's as if Bruce Almighty's Lead Actor and Movie Type were "Tom Shadyac", not just the director. And he also apparently was all three for Grand Canyon and Office Space, too.

So, I found it incredibly vexing to try and figure this out - is it my chosen variable for the argument ("$preludeopt_a")? When I looked at the original, I couldn't find any value/column for $director_id in any of the tables I'd set up for the movie site (movie, movietype, people), so I assumed "$director_id" was just a random variable name that was simply chosen to elaborate on its purpose. I did that with "$preludeopt_a".

I don't get any PHP errors when I take out the following code, barring messed up output:

Code:

...
song_id = â . $preludeopt_a;

...so my assumption was it had to be this, but I couldn't get it to work with

Code:

...
song_id = $preludeopt_a';

...either, as MySQL thinks I'm referencing a column.

In case of it being a version issue, I'm using MySQL 5.1.45 and PHP 5.2.13.

I apologize if this post sounded awkward or rude, I was going for as much detail as possible in order to save time.

caljay · August 17th, 2011, 10:37 PM

Well, this is mildly embarassing.

I've solved my own problem.

Code:

// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$prelude1 = get_preludeopt1($preludeopt_a);

My repeat use of the function as above was actually mistaken - I had called the same random variable-argument that I had when I first defined the function. This was a mistake: I should've put in a variable from the select query I used.

Code:

// retrieve information
$query = âSELECT
day_name, prelude_opt1
FROM
draft_suggest
ORDER BY
day_name ASC,';
$result = mysql_query($query, $db) or die(mysql_error($db));

And then I should've dropped the prelude_opt1 in, like so:

Code:

// loop through the results 
while ($row = mysql_fetch_assoc($result)) { 
extract($row); 
$prelude1 = get_preludeopt1($prelude_opt1);

Now, it works just fine.

...Well, at least everyone gets to learn from my mistake. (That, and now we all have some more precise definition of the nature of the custom get_name_for_id style functions included for chapter 4, which would've been more useful).