[RESOLVED] Populating an html form with mysql data.

fjm

Hi all, I am new to php and phpbuilders. This is my first post.

I'm sure what I want to do is amongst the most basic in php. I know what I need but I really don't where to begin.

What I have is a basic html form I have made consisting of header fields and fileds that will be populated from a mysql database. The brackets are the actual db fields and denote where I want the data to populate. Here is a small example of the form.

MY HEADER TO DESCRIBE TEXT BELOW
Age: {age}
Cell Phone: {cell phone}
Sex: {sex}
Home Address: {home address}
Place of Birth: {place of birth}
Apartment: {apartment}
SSN: {ssn}
City: {city}
State: {state}
Zip: {zip}

Can someone point me in the direction I need to go to populate the fields?

One other problem is that the headers, if there are no coorasponding field values, should be ommited from the html. Again, I have the html form already complete with the headers intact but I don't know how to omit them if there are no field values for that particular header.

My table structure is broken down to minimally 3nf and some tables are out to 4nf. There are about 45 tables in total but I am only using certain fields for this template I have created.

The headers are not stored in the database, they are just headers in the html that breaks up the classification of data. For example, a "HUMAN" heading would have height, weight, eye color, etc. Those attributes may be broken out over different tables in the database. Again, that heading is not stored in the db but is only there to make the html template more readable to the end user.

None of this form will need inserts, updates or delete sql. It is simply just a view in a pertty html form.

Thnaks guys!

rincewind456

The best way to learn would be to get a book or use Google to search for tutorials on the subject.

Without any idea of your table structures in MYSQL it is not possible to give an exact answer, however I can show you how to it's done in its most basic form.

<?php
error_reporting( E_ALL );

//database connection details here

// open the connection
$conn = mysql_connect('localhost', 'USERNAME', 'PASSWORD');
if (!$conn)
  die( 'Error connecting to SQL server: ' . mysql_error() );


// pick the database to use
if (!mysql_select_db('DATABASE_NAME',$conn))
  die( 'Error selecting database: ' . mysql_error() );

// run the database query

$result = mysql_query(
  'SELECT  

age
, cell_phone
, sex
, home_address
, place_of_birth
, apartment
, ssn
, city
, state
, zip
 FROM DATABASE_TABLE 
 WHERE CRITERIA_FOR CHOOSING = ' . $criteria // how you get this is up to you
);
if (!$result)
  die( 'Error selecting record data from database: ' . mysql_error() );


//now loop through the results to build HTML for display only the first 2 shown
// you should be able to work out the rest for yourself

//Start the table structure outside the loop
$html = "<table border=0 cellspacing=0 cellpadding=0 width='100%'>";

//now loop through the results check to see if the database entry is empty, 
//and if it isn't add it to the html variable
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	if ($row['age'] !='') {
	$html .= "<tr>
	<td>Age</td>
	<td>".$row['age']."</td>
	</tr>";
	}
		if ($row['cell_phone'] !='') {
	$html .= "<tr>
	<td>Cell Phone</td>
	<td>".$row['cell_phone']."</td>
	</tr>";
	}
	// and on and on until you have finished.
}
// add the end of the table structure
$html .= "</table";

// you can now echo or print $html where you want it to appear in your page i.e.

?>
<!doctype html public "-//w3c//dtd html 4.01//en"    "http://www.w3.org/tr/html4/strict.dtd">
<html>
<head>
<title></title>
</head>

<body>

<?php print $html; ?>

</body>
</html>

Hope this helps.

fjm

rincewind456, thanks for the script. Very nice indeed. I can use it! Unfortunately, I can't seem to use it for my template. I am beginnig to see what you mean about further info about the database. Here is how my database is broken down.

I have about 45 tables and have to use complex joins to get the data back together. The template has fields all over the place; kind of like the way that the mail merge option in M$ word functions.

I have done a little research on what I want to do and it appears that I am looking for a template system. Something that would allow me to place a delimiter within my html template and then would be populated with data. Kind of like this:

I like {software} for its ease.Which would translate to, after being populated with the mysql data:

I like php for its ease.

I have 106 fields that need to be populated with data and I was thinking about writing 106 select statements to get what I need. I am reasonably certain that this is the WRONG way to do it.

$name = select name from person
where name = "john";

$address = select address from person
where name = 'john';

Do you see what I mean? Sorry for my nubish glow.

rincewind456

I have 106 fields that need to be populated with data and I was thinking about writing 106 select statements to get what I need. I am reasonably certain that this is the WRONG way to do it.

You're right there, that is definitely not the way to do it.

But irrespective of the query, if your query gets you all the data you need then it will end up in the array that I have called $result in my example, from there you can manipulate it however you want.

I haven't used it myself, but if you want to use a template then you may want to look at Smarty

fjm

@. Thanks for the clarification. I actually see what you mean. So if I am understanding you correctly, the $result will produce the array and I can manipulate the results from there. Correct?

OMG: I completely missed this. NOW I see how I can manipulate the data! Thanks Rincewind!

if ($row['cell_phone'] !='')

I was going to ask you just how to manipulate the data and I totally didn't see the if statement.

Ok, let me ask you this: IF I am reading the php code correctly, I am getting that I would have to have an if statement for each and every one of the fields I need to populate. Am I correct on this?

rincewind456 wrote:
I haven't used it myself, but if you want to use a template then you may want to look at Smarty

Smartie is for dummies. 🙂 (Just kidding) Actually, I was told about smartie a few days back and I was tempted to try it but if I did that I would never really anything about php.

rincewind456

Yes to both questions, that is just one way of manipulating the data. There is an awful lot you can do within that while loop.

If your doing this project as a learning exercise, then I would suggest you continue on the way your going, using Smarty would be counter productive, because as you say you wouldn't be learning a great deal about PHP, but probably a lot about Smarty.

fjm

rincewind456,

Thank you for your help and explanations! I feel like I can handle this. It doesn't seem that bad really. Also, I agree, smartie, as tempting as it would be for me to use, would be counter productive. I really want to learn php and learn it well.

Since you are great at explanations, I hope you don't mind me asking you another question.

I have about 45 tables and need to use complex joins to get the data back together. What I was thinking about doing was to create a view and stick that into my sql variable in php to get my array. Is this acceptable? Do you think there would be another way of doing this?

If I didn't use a view, I really would need 106 select statements because it would need to pull from a lot of different tables.

Am I correct? Is there a better way of querying the data or getting the array into php? I hope I am making sense.

fjm

rincewind456,

Your a genius man! Thank you! I got it.

Here is what I did. I kept the connection and sql script seperate from my html template and placed it into a file called test.php.

I renamed my html template file from template.html to template.php then insidde the template.php file did:

<?php include("test.php"); ?>

then put the following, also in the template.php file, where I wanted my info placed.

<?php print $html; ?>

Worked like a charm. I have no words to tell you how stoked I am right now!! Man, php is really cool! Thanks again Rincewind! 😃

bradgrafelman

Don't forget to mark this thread resolved.