[SOLVED] Populating template with mySQL database fields...?

[lucretia23]

Hi! I'm a totally newbie, as will be immediately obvious, but very keen to learn! What I'm trying to do is so basic, but I can't find an answer anywhere - books, help files, online - this must be obvious to everyone but me.

 

I have a Dreamweaver 8 template page for about 300 job profiles I want to generate dynamically. Sprinkled throughout the page are places where I want to insert data from a mySQL database, fields containing information about each job position, such as job title, department, direct reports, key accountabilities. These places look like this:

 

<?php echo $row_Recordset1['ReportsTo']; ?>

 

Naturally, the page uses the first recordset in the database. That much works beautifully.

 

I have another page where the user will choose which job they want to look at, using a form with drop-downs for JobTitle and Dept. I use the GET method because I need to generate unique URLs for these job profiles, so I know I want to use the URL variable to tell the page which recordset to look at.

 

A possible complication is that the database primary key thing is a combination of both fields, JobTitle and Dept, so I need to use both fields to decide which row to get.

 

Here's the code Dreamweaver gave me:

 

<?php

mysql_select_db($database_CompanyJobProfiles, $CompanyJobProfiles);

$query_Recordset1 = "SELECT * FROM jobprofiledata";

$Recordset1 = mysql_query($query_Recordset1, $CompanyJobProfiles) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

 

I feel so silly! How do I tell the application which recordset to get? Thank you!!

[.josh]

not really sure what you mean by "which recordset to get" do you mean how do you for instance echo out the results of your query?

 

echo $_row_Recordset1['columnnamehere']; // put the column name in the quotes

[lucretia23]

Thanks for looking at this!

 

Maybe "recordset" isn't the right term - I mean, which row in the database to use to populate the template. The db looks like this:

 

JobTitle                      Dept                        ReportsTo                  (etc.)

Account Manager        Finance                    Supervisor

Area Manager              Marketing                  Vice President

(etc.)

 

I want the user to select, say, Account Manager, Finance, and then to see the template page filled out with the fields from that row.

 

Everything I've seen just tells me how to display the whole row at once, like in a table or something. I want each field in a selected row to go into the template where I want it. Do you see what I mean?

[.josh]

Okay well it still sounds to me like what I told you before...

 

from your code:

<?php
mysql_select_db($database_CompanyJobProfiles, $CompanyJobProfiles);
$query_Recordset1 = "SELECT * FROM jobprofiledata";
$Recordset1 = mysql_query($query_Recordset1, $CompanyJobProfiles) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>

 

Your code makes $row_Recordset1 and array of all the info in the row selected.  Well, it takes the first row returned. Your query returns all of the columns in all of the rows of your table, and your mysql_fetch_assoc assigns the first row in the list to $row_Recordset1 as an array.  Each element corresponds to the column so with your code, you can now use $row_Recordset1['columnnamehere'] wherever you want to in your script.  You would just echo that out in the part you want it to show.  Just replace 'columnnamehere' with the column name like $row_Recordset1['JobTitle'].

 

Ideally you're going to want to reduce the results returned to only what you need, though. 

[lucretia23]

"Ideally you're going to want to reduce the results returned to only what you need, though."

 

That's exactly what I don't know how to do! 

 

"SELECT * from jobprofiledata", does that mean "select all" from that table? So how do I tell it to select only the row whose primary key matches whatever's in the URL? (The primary key for that table is a combination of JobTitle and Dept.) For instance, from my form I will get a URL that looks like this:

 

http://example.com/job_profile.php?JobTitle=Director&Dept=Finance

 

What do I do to limit the results of my query to just that row?

 

Thank you so much for your patience!

[.josh]

"SELECT * from jobprofiledata", does that mean "select all" from that table?

Yes, that's exactly what that means. Select all the columns and all the rows of the table.

 

The primary key for that table is a combination of JobTitle and Dept.)

So...you have a column named what, Id? And the data in that column is what, JobTitle+Dept? Like "DirectorFinance" would be in one of the cells of that table?

 

<?php

mysql_select_db($database_CompanyJobProfiles, $CompanyJobProfiles);


// get the vars from the url
$JobTitle = mysql_real_escape_string($_GET['JobTitle']);
$Dept = mysql_real_escape_string($_GET['Finance']);

$Id = $JobTitle . $Dept;

$query_Recordset1 = "SELECT * FROM jobprofiledata WHERE Id = '$Id'";
$Recordset1 = mysql_query($query_Recordset1, $CompanyJobProfiles) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

 

Or am I understanding that wrong?  Are you just wanting to select the row where jobtitle = director and dept = finance? 

<?php

mysql_select_db($database_CompanyJobProfiles, $CompanyJobProfiles);


// get the vars from the url
$JobTitle = mysql_real_escape_string($_GET['JobTitle']);
$Dept = mysql_real_escape_string($_GET['Finance']);

$query_Recordset1 = "SELECT * FROM jobprofiledata WHERE JobTitle = '$JobTitle' AND Dept = '$Dept'";
$Recordset1 = mysql_query($query_Recordset1, $CompanyJobProfiles) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

[lucretia23]

WOOHOO!!  That's it, thank you!! And it works, it works!

 

In case you're curious - your second one was correct, and btw the table name is jobprofiledata. The only thing is, there's a little typo  here:

 

// get the vars from the url

$JobTitle = mysql_real_escape_string($_GET['JobTitle']);

$Dept = mysql_real_escape_string($_GET['Finance']);

 

The line should be:

$Dept = mysql_real_escape_string($_GET['Dept']);

 

- which I'm so happy that at least I could figure that much out by myself, so thanks for that too!

 

You are my favorite Crayon!