Populating an html form with mysql data.

[fjm]

What class are you having difficulties instantiating? If you follow the post at the top of the Forum about enabling error messages, you will be given this information.

Hi Motoma,

I am so ignorant sometimes. :)

I am embarrased to say this, but I forgot the php tags. Arggg.. See what being a N-E-W-B-I-E gets ya? How long before this newbish thing goes away?

[fjm]

Motoma,

I am having luck with your script but I have to ask you. Does this look right to you?

foreach($resArr as $user) {
echo '        <tr>';
echo "\n";
echo '          <td colspan="2">';
echo "\n";
echo '            <table width="100%" cellpadding="0" cellspacing="0" border="0">';
echo "\n";
echo '              <tr>';
echo "\n";
echo '                <td>Incident Type:</td>';
echo "\n";
echo '                <td><p>'.$user['license_Number'].'</p></td>';
echo "\n";
echo '                <td>Dispatch Time:</td>';
echo "\n";
echo '                <td><p>'.$user['zip_Code'].'</p></td>';
echo "\n";
echo '              </tr>';
echo "\n";
echo '              <tr>';
echo "\n";
echo '                <td>Source of Call:</td>';
echo "\n";
echo '                <td><p>'.$user['license_Number'].'</p></td>';
echo "\n";
echo '                <td>10-97:</td>';
echo "\n";
echo '                <td><p>'.$user['zip_Code'].'</p></td>';
echo "\n";
echo '              </tr>';


echo "\n";
echo '            </table>';
echo "\n";
echo '          </td>';
echo "\n";
echo '        </tr>';
echo "\n";

Am I missing something or is this the way it should be done? I have 106 of these to do and I'm starting to get carple tunnel :)

[Motoma]

106 to do? Surely you can think of a way to develop these tables programmaticall y.

[fjm]

Generating these tables dynamically sounds like an absolutely fantastic idea. I have very little experience with php but I think I have enough to understand what you are getting at.

Are you talking about creating like a table class, storing it in mysql and calling it with a function?

That seems like the right way to go, I just don't know how to go about that. Could you point me in the right direction so I can do it? Again, my ultimate goal is to learn php.

BTW: your article on OOP was probably one of the best I have seen. Thank you for that.

[Motoma]

I guess what I am wondering is what you need to do that goes above and beyond a simple foreach loop?

[fjm]

I guess what I am wondering is what you need to do that goes above and beyond a simple foreach loop?

Well, I believe that a foreach loop is all I need. What I have is your code in the following manner:

<?php
foreach($resArr as $user) {
echo '
		<tr>
		 <td colspan="2">
			<table width="100%" cellpadding="0" cellspacing="0" border="0">
			 <tr class="yellow">
				<td><h6>REPORTING PARTY</h6></td>
			 </tr>
			</table>
		 </td>
		</tr>';
 
echo ' <tr><td></td></tr><tr><td>'.$user['license_Number'].'</td></tr>';
}
?>

This works very nicely and will echo the rows I need. What I am having an issue with now is the view. I have about 50 tables with about 200 fields. Of the 200 fields, I need a view that will handle about 100 fields and place that into a variable in your script.

In other words, where you have defined $resArr, I am using that variable that defines the sql for a small view that does work. However, I need to create another variable for an additional view and don't know how to go about that.

I'm not even sure what to google for. I'm lost.

[Motoma]

I don't quite understand, but it sounds like what you are trying to accomplish is paging. Tell me if this sounds right: You are returning huge sets of data, much more than you would want to display on a page. To combat this, you feel it would be best to show only a small portion of the data at one time.

If this is the case, what you typically do is use the LIMIT MySQL keyword, and keep track of the page you are on. For instance, if you are on page 1, and you want to view 30 entries, you would add "LIMIT 0, 30" to the end of your query. Now lets say you are on page 8, you would instead append "LIMIT 210, 30" to your query (($page-1) * $entriesperpage would be your invariant).

[fjm]

Hi Motoma,


Actually, I am not wanting to do paging, as in the paginator script. Let me see if I can explain a little clearer for everyone. Again, please keep in mind I am new at this so forgive me not explaining it programming terms.

I have a template form with approximately 100 *possible* fields that need to be populated from a mysql database. Now, I say possible because if there is no information for the particular heading of data, it sould not print anything. That is what I am using your foreach loop to do and that works great. Actually, let me give an example as it may help.

VICTIM (Heading)
(Now under this heading there *may* be or *may not be* a victim to add to my template form. The way I have your foreach loop set up is that it looks for data from the PK field of the victim table and prints or echos the victim's data if it is there. If not, it just goes on to the next php foreach loop for the [i]next[/i] heading. So we would have the VICTIM heading and the data just under that like so:
 
VICTIM
Name: (pull name from the database)
Address: (pull address from the database)
City: (pull city from the database)

I should mention that I have about 6 headers with similer information (Headers and fields) that needs to populate from the database. In total, there about 100 fields that fall under different heading topics.

I would like to have a seperate view for each heading if possible. I cannot write a view that works with so many (100+) fields.

Now, creating a seperate view for each heading would entail a new variable for each select statement. Correct? I think that this is what I need.

Thank you for taking the time to help me with this. It is greatly appriciated!

[Motoma]

If I am understanding you correctly I think there may be an easier solution:

With a proper JOIN in place, you could get all of your elements, joined with their appropriate Headers. An INNER JOIN will make it so that only Headers with data will be in the result set. An ORDER BY can be set up so that everything gets sorted by the Header first. Then in your code, your foreach loop keeps track of what the last Header was, compares it to the current header, and if they are different, adds the Header in the HTML output and updates the last header value.

[fjm]

With a proper JOIN in place, you could get all of your elements, joined with their appropriate Headers. An INNER JOIN will make it so that only Headers with data will be in the result set. An ORDER BY can be set up so that everything gets sorted by the Header first.

Ok, now that makes sense to me and sounds like it will definatly work!!

Then in your code, your foreach loop keeps track of what the last Header was, compares it to the current header, and if they are different, adds the Header in the HTML output and updates the last header value.

Now here is where you lose me because I have no idea how to do this. I count myself fortunate at this point that I was even able to get your code and the foreach loop to work in the first place. :)

Do you have an example or can you tell me what to google for maybe to find an answer?

Below is only a sample of my view of *a few* fields I am trying to populate. This is really getting wild.

SELECT
`customer_location`.`customer_Number_Seq`,
`customer`.`name`,
`address`.`address_1`,
`address`.`address_2`,
`city_zip`.`city`,
`city_zip`.`state`,
`address`.`zip`,
`address`.`address_Description`,
`address`.`district_Seq`,
`person`.`last_Name`,
`employee`.`employee_Seq`
FROM
`rel_customer_location_address`
Inner Join `customer_location` ON `customer_location`.`customer_Location_Seq` = `rel_customer_location_address`.`customer_Location_Seq`
Inner Join `address` ON `address`.`address_Seq` = `rel_customer_location_address`.`address_Seq`
Inner Join `customer` ON `customer`.`customer_Number_Seq` = `customer_location`.`customer_Number_Seq`
Inner Join `city_zip` ON `city_zip`.`zip` = `address`.`zip`
Inner Join `rel_person_address` ON `address`.`address_Seq` = `rel_person_address`.`address_Seq`
Inner Join `person` ON `rel_person_address`.`person_Seq` = `person`.`person_Seq`
Inner Join `role` ON `person`.`person_Seq` = `role`.`person_Seq`
Inner Join `employee` ON `role`.`role_Seq` = `employee`.`role_Seq`
Inner Join `rel_employee_incident` ON `employee`.`employee_Seq` = `rel_employee_incident`.`employee_Seq`

Thanks Motoma!

[Motoma]

My oh my. I reread through this entire thread and came to a realization that it has gone quite off track from what it started out as. This may be because I came in halfway through the thread, or it could be that I forget what I previously said last time I answered your question.

In an effort to get me caught up to speed once again, could you please start from square one, and delineate what you have done, what is working, and what you still need to do?

I promise I will pay attention this time.

[fjm]

My oh my. I reread through this entire thread and came to a realization that it has gone quite off track from what it started out as. This may be because I came in halfway through the thread, or it could be that I forget what I previously said last time I answered your question.

I think you were actually the one that felt the most sorry for me and stuck with me. :)

In an effort to get me caught up to speed once again, could you please start from square one, and delineate what you have done, what is working, and what you still need to do?

I promise I will pay attention this time.

Sure, not a problem..

What I would like to accomplish is to have a "view" or a "front end" programmed in html and php for visitors coming to a website. This front end will serve the back end mysql database.

What I have are hardcoded fields and next to those fields are the dynamic fields that will draw information from a mysql database. So what I would have might me as follows:

<table>
<tr>
	<td>Name:</td>
	<td>mysql data name field</td>
</tr>
<tr>
	<td>Address:</td>
	<td>mysql data address field</td>
</tr>
<tr>
	<td>City:</td>
	<td>mysql data city field</td>
</tr>
</table>
 
Both of the following php codes are inside my html "template".

Now, I have used your OOP php code to iterate through the data and populate some of the data already. Whether it is the *correct* way to do it, I don't know. Here is a sample of what my html *template* looks like with your php code.
[php]
<?php

error_reporting (E_ALL);

ini_set('displa y_errors', True);

// Our Database Class

include("lib/database.php");

// Instantiate our Database Class

$db = new Database();

// Query!

$resArr = $db->query(" SELECT * FROM address WHERE zip_Code = '33060' ");

$otherVar = $db->query( "select * from license WHERE license_Number LIKE '%56%' " );

?>
[/php]

One of the problems I am having is that because I have sooo many of these fields that need to be drawn from the database and inserted into the template/front-end, I can't seem to write a select statement that I can use as a view because the select statement is OUT OF CONTROL. It is too much to write. The above post was just a sample of my select statement. Again, I need about 100 fields to populate this form.

One thing that I did not yet mention in this post was that each "section" has headers to make the form more readable for the user. These headers are not stored in the database but the fields that go under them are. So I would have:

HEADER
Name: mysql data field
Address: mysql data field
City: mysql data field

I am currently using your foreach statement to iterate through the data to see if there is anything under that heading. If there is no accompanying data, your foreach loop leaves the header and table out completely. Here is the code for that:

[PHP]
<?php

//This foreach statement echos the heading if something is found//

//Such as select * from view where customerNumber= $customer and customerLocatio n=$location

foreach($resArr as $user) {

echo '

<tr>

<td colspan="2">

<table width="100%" cellpadding="0" cellspacing="0" border="0">

<tr class="yellow">

<td><h6>VICTI M</h6></td>

</tr>

</table>

</td>

</tr>';

echo '

<tr>

<td colspan="2">

<table width="100%" cellpadding="0" cellspacing="0" border="0">

<tr>

<td>Name:</td>

<td><p>{name} </p></td>

<td>Occupation: </td>

<td><p>{occupat ion}</p></td>

</tr>

<tr>

<td>Race:</td>

<td><p>{race} </p></td>

<td>Work Phone:</td>

<td><p>{work phone}</p></td>

</tr>

<tr>';
}
?>
[/PHP]

Because I cannot create a single view to grab all of my data because of how I have the database normalized, I must use additional views but I don't know how to implement that into the php code. Your $resArr suffices for just one of my views but not two or three.

Can you see my problem? Aside from the fact that I am php impared? :)

Can you see an easier or better way of doing this?

Thanks Motoma

[Motoma]

Let me see if I can help you out: PHP has this function called key() which I think, when combined with column renaming in your MySQL query, can solve all of your problems.

[code=php]
<?php

$db = new Database();

$victimArr = $db->query("SELEC T id AS `Victim Number:`, firstname AS `First Name:`, lastname AS `Last Name:` FROM victimquery");
$houseArr = $db->query("SELEC T id AS `House Number:`, address AS `Street Address:` FROM housequery");
...
...
...

if(count($victi mArr) > 0)
{
echo "<table><tr><th >VICTIMS</th></tr><tr><td><tab le>";
foreach($victim Arr as $victim)
{
echo "<tr>";
foreach($victim as $attr => $value)
{
echo "<th>".$att r."</th><td>".$value ."</td>";
}
echo "</tr>";
}
echo "</table></td></tr></table>";
}

if(count($house Arr) > 0)
{
echo "<table><tr><th >VICTIMS</th></tr><tr><td><tab le>";
foreach($houseA rr as $house)
{
echo "<tr>";
foreach($house as $attr => $value)
{
echo "<th>".$att r."</th><td>".$value ."</td>";
}
echo "</tr>";
}
echo "</table></td></tr></table>";
}
...
...
...


?>
[/code]

In fact, that didn't use key() at all. But it's good to know about the key() function anyway :P

[fjm]

Motoma,


Thank you. That worked out well for me. You know.. I feel a little better seeing this new code you posted because as I was sitting here for the past week trying to figure out exactly what I needed to get everything working. I mean, being a newbie and all, at least my mind was thinking in the right direction.

I *figured* I would need an if statement to draw the header and a foreach statement to draw the actual data. I wasn't too far off base but I just had no idea how to put it all together.

I need to create a login form where the customer will enter their customer number and password. When the user authenticates themselves, and based on their customer number the login form should *automatically* forward them to a second page with their details. I know that I need to use a session to do this.

Can you point me in the right direction please? Is it a session that I need to perform this action?

Thank you Motoma! You're the best.

[Motoma]

You should store a bit of information about the authenticated user in your session. Hopefully this information will be something that will uniquely identify your user (HINT HINT Primary Key HINT). Store that someplace like $_SESSION['userid'] and refer to that on every page and check that the user is authenticated.