By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

Populating an html form with mysql data.

100+
P: 348
Hi all, thanks for helping. I'm sure what I want to do is amongst the basic in php. I know ehat 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. here is a small example of the form.

Expand|Select|Wrap|Line Numbers
  1. ANIMALS <-header
  2. dog <-field
  3. cat <-field
  4. bird <-field
  5.  
  6. FRUIT<-header
  7. apple <-field
  8. orange <-field
  9. cherry <-field
  10.  
  11. HUMANS<-header
  12. man <-field
  13. woman <-field
  14.  
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.

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

Thnaks guys!
May 30 '07 #1
Share this Question
Share on Google+
32 Replies


pbmods
Expert 5K+
P: 5,821
What's your MySQL table structure like? How are the headers stored? How are the field values stored?

My initial reaction would be to set up a template, but this might be tricky depending on how you have your database set up.
May 30 '07 #2

ak1dnar
Expert 100+
P: 1,584
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.
Please post this form that you made, then we can find out what you are looking for.
May 30 '07 #3

100+
P: 348
fjm
What's your MySQL table structure like? How are the headers stored? How are the field values stored?
Hi pbmods, thanks for the response.

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, the "HUMAN" heading would have height, weight, eye color, etc. Those attributes may be broken out over different tables in the database. Following is some sample html from my template that I would use under a heading of "RESPONSIBLE PERSON". Again, that heading is not stored in the db but is only there to make the html template more readable to the end user.

The brackets denote where I want the fields to draw from the the db.

Expand|Select|Wrap|Line Numbers
  1. Age: {age}
  2.  Cell Phone: {cell phone}
  3.  
  4. Sex: {sex}
  5.  
  6. Home Address: {home address}
  7.  Place of Birth: {place of birth}
  8.  
  9. Apartment: {apartment}
  10.  SSN: {ssn}
  11.  
  12. City: {city}
  13.  
  14. State: {state}
  15.  
  16. Zip: {zip}
  17.  

Thanks again for any input.
May 30 '07 #4

100+
P: 348
fjm
Does anyone have a take on this? I'm really stuck here.
May 31 '07 #5

pbmods
Expert 5K+
P: 5,821
The brackets denote where I want the fields to draw from the the db.

Expand|Select|Wrap|Line Numbers
  1. Age: {age}
  2.  Cell Phone: {cell phone}
  3.  
  4. Sex: {sex}
  5.  
  6. Home Address: {home address}
  7.  Place of Birth: {place of birth}
  8.  
  9. Apartment: {apartment}
  10.  SSN: {ssn}
  11.  
  12. City: {city}
  13.  
  14. State: {state}
  15.  
  16. Zip: {zip}
  17.  
Congratulations! You've just designed your first templating system!

Check this out:

Expand|Select|Wrap|Line Numbers
  1. $output = '';
  2. preg_match_all('{(\w+)}', $template, $tags);
  3. foreach($mysql_results as $row => $data) {
  4.     $rowOut = $template;
  5.  
  6.     foreach($tags[1] as $idx => $tagName)
  7.         $rowOut = str_replace($tags[0][$idx], $data[$tagName], $rowOut);
  8.  
  9.     $output .= $rowOut;
  10. }
  11.  
First, we run through $template and cache all our {tags}.
Then, for each row in our MySQL results, we run through it and append a processed copy of the $template:

For each tag that we found before, run through and replace any instances of it with the same-named column in our MySQL data.

Welcome to the basics of creating your own templating engine. From here, it's up to you (e.g., handling backslashes, attributes, meta-tags, etc.).

If you don't want to do all that work, you could just use Smarty.
May 31 '07 #6

100+
P: 348
fjm
pbmods, thank you for the vote of confidence on creating my first template system. :) The experience is bittersweet though. You provided the code but I am not really sure where to put it. If I may ask a couple more questions..

My template is written in html with a .html extension. Right now, there is no php in the html at all. I believe that I would place your code in the html template. Am I correct on this? Of course I have the mysql connection and sql script. I am assuming that I would place all of the code as such:

Expand|Select|Wrap|Line Numbers
  1. <?php 
  2.  
  3. function connectDb($user, $pass, $host, $db) { 
  4.     //this function connects to a mysql server 
  5.     $sock = mysql_connect($host, $user, $pass); 
  6.  
  7.     //this function connects to a mysql database, once a server has been reached. 
  8.     if(isset($sock)) { 
  9.         if(!mysql_select_db($db, $sock)) { 
  10.             echo mysql_error(); 
  11.         } 
  12.     } 
  13.     return $sock; 
  14.  
  15.  
  16. //simple use of the function 
  17. $socket = connectDb('user','password','localhost','template'); 
  18.  
  19. //create an sql query 
  20. $sql = "SELECT *  FROM address LIMIT 10"; 
  21.  
  22. //perform the query and return a resource identifier to $query 
  23. $query = mysql_query($sql, $socket); 
  24.  
  25. //show all the data via a while loop 
  26. while($data = mysql_fetch_assoc($query)) { 
  27.     //using foreach, list all the data that was returned in the $data array from mysql_fetch_assoc 
  28.     foreach($data as $key => $value) { 
  29.         echo $value.'<br />'; 
  30.     } 
  31.     echo '<br />'; 
  32.  
  33. ?>
  34.  
  35. <?php
  36.  
  37. $output = '';
  38. preg_match_all('{(\w+)}', $template, $tags);
  39. foreach($mysql_results as $row => $data) {
  40.     $rowOut = $template;
  41.  
  42.     foreach($tags[1] as $idx => $tagName)
  43.         $rowOut = str_replace($tags[0][$idx], $data[$tagName], $rowOut);
  44.  
  45.     $output .= $rowOut;
  46. }
  47.  
  48. ?>
  49. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  50. <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
  51. <head>
  52.     <titl
  53.  
  54.  
Also I have a question about *where* or rather how the php code you provided will know what db field to insert into the html. For example, my template has 106 fields that need to be populated with db info. How does the php code know, for example to insert the data that equals "man" into the template in the correct spot?

I know this may sound really dumb but I was originally thinking about creating 106 different sql select statements and placing that variable into the correct spot in my template. See.. stupid huh. :)

Please forgive my ignorance but when I say newbie I mean N-E-W-B-I-E. :)

Thanks again for the help and patience!!
May 31 '07 #7

pbmods
Expert 5K+
P: 5,821
My template is written in html with a .html extension. Right now, there is no php in the html at all. I believe that I would place your code in the html template. Am I correct on this? Of course I have the mysql connection and sql script. I am assuming that I would place all of the code as such:
Think of it like this: In your template, all you're worrying about is the structure. So if you had a (simple) template that looked like this:

Expand|Select|Wrap|Line Numbers
  1. <div id="{recordid}">My name is {name}.  I like {fruit}s.</div>
  2.  
The idea here is that we will take that template and parse it once for every entry in your search results. So if you ran a MySQL query, and you got back these results:

Expand|Select|Wrap|Line Numbers
  1. array(
  2.     0    =>    array(
  3.         'recordid'    =>    1,
  4.         'name'        =>    'Peter',
  5.         'fruit'        =>    'apple'
  6.     ),
  7.  
  8.     1    =>    array(
  9.         'recordid'    =>    2,
  10.         'name'        =>    'Paul',
  11.         'fruit'        =>    'orange'
  12.     ),
  13.  
  14.     2    =>    array(
  15.         'recordid'    =>    3,
  16.         'name'        =>    'Mary',
  17.         'fruit'        =>    'artificial strawberry'
  18.     )
  19. );
  20.  
The goal is to end up with this after you run the template/content through your engine:

Expand|Select|Wrap|Line Numbers
  1. <div id="1">My name is Peter.  I like apples.</div>
  2. <div id="2">My name is Paul.  I like oranges.</div>
  3. <div id="3">My name is Mary.  I like artificial strawberrys.</div>
  4.  
Well, close enough. What do computers know about strawberrys, anyway?

At any rate, the point here is that you want to keep your template separate from your content. Your template doesn't know [/ doesn't care] about the content. All it does is tell you where the tags go. Your content doesn't care about your template; all it does is tell you what you're working with.

All the template engine has to do is match up tags to data and merge them properly.

So... suppose you create this nifty little template and you store it in an HTML file (which is a perfectly acceptable way to do it; after all, you might want to use it with more than one page). How do you get at it?

Why do we love PHP? Because there's a function for EVERYTHING!

Expand|Select|Wrap|Line Numbers
  1. $template = file_get_contents('template.html');
  2.  
http://php.net/file_get_contents

Also I have a question about *where* or rather how the php code you provided will know what db field to insert into the html. For example, my template has 106 fields that need to be populated with db info. How does the php code know, for example to insert the data that equals "man" into the template in the correct spot?
You have two options. The easy way (and the way the code I gave you is set up) is to use the same name for your tags as the DB field that you want. So in other words, {name} would correspond to the 'name' field from your results.

Mapping arbitrary tag names to field names (or functions!) would require a third array, and that's probably getting a little more complicated than you're looking for.

Incidentally, while we're on the subject of reinventing the wheel, you are welcome to check out Smarty, which is a very nice templating system that has already been developed.

I'm happy with everything I've learned (if the hard way) by developing my own (maybe I should call it Brillant...), and I'm sure you will, too. But it's going to take awhile to get it right.

I know this may sound really dumb but I was originally thinking about creating 106 different sql select statements and placing that variable into the correct spot in my template. See.. stupid huh. :)
You should have seen my code before learned what object-oriented programming is. Same concept.

Please forgive my ignorance but when I say newbie I mean N-E-W-B-I-E. :)
We've all been there. You teach me some patience, and I'll teach you some code.
May 31 '07 #8

100+
P: 348
fjm
pbmods,

Well, I wish I could say I had luck getting it to work. Its gonna be a very long road I can see and yet I have so much more work to do on this.

Let me ask you this, my template file is in the form of template.html, *not* .php. So any php tags I put into the html will not be parsed. The code below you gave me, I put into a seperate php file and called it foo.php.

The
Expand|Select|Wrap|Line Numbers
  1. $template = file_get_contents('template.html');
I have edited my html as well to include the exact field names from the db but still no go. I have ran both the template.html and foo.php files and get no change. I do however get my output from the database in this form:

Expand|Select|Wrap|Line Numbers
  1. 1000
  2.  
  3.  
  4.  
  5.  
  6.  
  7. 77077
  8. 1
  9.  
  10. 1001
  11.  
  12.  
  13.  
  14.  
  15.  
  16. 71006
  17. 2
  18.  
  19. 1002
  20.  
What am I doing wrong? Thanks again for the guidence.
Also, I could use smartie, but I wouldn't learn anything about php. I figure since I have a real need to complete this form, I should'nt take the easy way out. :)
Jun 1 '07 #9

100+
P: 348
fjm
I forgot to include the contents of the foo.php file:

Expand|Select|Wrap|Line Numbers
  1. <?php 
  2.  
  3. function connectDb($user, $pass, $host, $db) { 
  4.     //this function connects to a mysql server 
  5.     $sock = mysql_connect($host, $user, $pass); 
  6.  
  7.     //this function connects to a mysql database, once a server has been reached. 
  8.     if(isset($sock)) { 
  9.         if(!mysql_select_db($db, $sock)) { 
  10.             echo mysql_error(); 
  11.         } 
  12.     } 
  13.     return $sock; 
  14.  
  15.  
  16. //simple use of the function 
  17. $socket = connectDb('usa','password','usa.com','ffl'  ); 
  18.  
  19. //create an sql query 
  20. $sql = "SELECT *  FROM address where address_Seq = '1'"; 
  21.  
  22. //perform the query and return a resource identifier to $query 
  23. $query = mysql_query($sql, $socket); 
  24.  
  25. //show all the data via a while loop 
  26. while($data = mysql_fetch_assoc($query)) { 
  27.     //using foreach, list all the data that was returned in the $data array from mysql_fetch_assoc 
  28.     foreach($data as $key => $value) { 
  29.         echo $value.'<br />'; 
  30.     } 
  31.     echo '<br />'; 
  32.  
  33. ?>
  34.  
  35. <?php
  36. $template = file_get_contents('test.html');
  37. $output = 'test.html';
  38. preg_match_all('{(\w+)}', $template, $tags);
  39. foreach($mysql_results as $row => $data) {
  40.     $rowOut = $template;
  41.  
  42.     foreach($tags[1] as $idx => $tagName)
  43.         $rowOut = str_replace($tags[0][$idx], $data[$tagName], $rowOut);
  44.  
  45.     $output .= $rowOut;
  46. }
  47.  
  48. ?>
Here is the contents of the test.html file:

Expand|Select|Wrap|Line Numbers
  1. <div id="{address_Seq}">The license Number is {license_Number}.</div>
I am looking at the code and am not sure if I may see the problem, but does the line $mysql_results have anything to do with the template not being parsed? I mean, if I am reading the connection code properly, the results are output to a variable called $value.

I changed that variable in your code to $value but it still didn;t work. I'm trying. :)
Jun 1 '07 #10

rfresh
P: 2
@fjm

Maybe I'm over simplfying this, but why don't you just populate your web page HTML objects from your DB after fetching their values? I would just populate your HTML objects directly from the DB fields; something like this:

Expand|Select|Wrap|Line Numbers
  1. $frm_name = $php_row->name;
  2.  
Jun 1 '07 #11

100+
P: 348
fjm
rfresh,

Thanks for the pointer. Do you mean to have the html dynamically generated?
Jun 1 '07 #12

Motoma
Expert 2.5K+
P: 3,235
I posted a fantastic tutorial that has an example of populating an HTML table from a database.
Jun 1 '07 #13

100+
P: 348
fjm
I posted a fantastic tutorial that has an example of populating an HTML table from a database.
Thank you Motoma. I will definately have a look at that! I really appriciate everyone that is taking the time to help me! Thanks guys!

Hey Motoma, I get this message in my php log:
Expand|Select|Wrap|Line Numbers
  1. Cannot instantiate non-existent class:
I googled it and continually come up with issures relating to permissions. Both of your files have read/write access and still nothing. Any ideas?
Jun 1 '07 #14

Motoma
Expert 2.5K+
P: 3,235
Thank you Motoma. I will definately have a look at that! I really appriciate everyone that is taking the time to help me! Thanks guys!

Hey Motoma, I get this message in my php log:
Expand|Select|Wrap|Line Numbers
  1. Cannot instantiate non-existent class:
I googled it and continually come up with issures relating to permissions. Both of your files have read/write access and still nothing. Any ideas?
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.
Jun 2 '07 #15

100+
P: 348
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?
Jun 2 '07 #16

100+
P: 348
fjm
Motoma,

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

Expand|Select|Wrap|Line Numbers
  1. foreach($resArr as $user) {
  2. echo '        <tr>';
  3. echo "\n";
  4. echo '          <td colspan="2">';
  5. echo "\n";
  6. echo '            <table width="100%" cellpadding="0" cellspacing="0" border="0">';
  7. echo "\n";
  8. echo '              <tr>';
  9. echo "\n";
  10. echo '                <td>Incident Type:</td>';
  11. echo "\n";
  12. echo '                <td><p>'.$user['license_Number'].'</p></td>';
  13. echo "\n";
  14. echo '                <td>Dispatch Time:</td>';
  15. echo "\n";
  16. echo '                <td><p>'.$user['zip_Code'].'</p></td>';
  17. echo "\n";
  18. echo '              </tr>';
  19. echo "\n";
  20. echo '              <tr>';
  21. echo "\n";
  22. echo '                <td>Source of Call:</td>';
  23. echo "\n";
  24. echo '                <td><p>'.$user['license_Number'].'</p></td>';
  25. echo "\n";
  26. echo '                <td>10-97:</td>';
  27. echo "\n";
  28. echo '                <td><p>'.$user['zip_Code'].'</p></td>';
  29. echo "\n";
  30. echo '              </tr>';
  31.  
  32.  
  33. echo "\n";
  34. echo '            </table>';
  35. echo "\n";
  36. echo '          </td>';
  37. echo "\n";
  38. echo '        </tr>';
  39. 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 :)
Jun 2 '07 #17

Motoma
Expert 2.5K+
P: 3,235
106 to do? Surely you can think of a way to develop these tables programmatically.
Jun 2 '07 #18

100+
P: 348
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.
Jun 2 '07 #19

Motoma
Expert 2.5K+
P: 3,235
I guess what I am wondering is what you need to do that goes above and beyond a simple foreach loop?
Jun 5 '07 #20

100+
P: 348
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:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. foreach($resArr as $user) {
  3. echo '
  4.         <tr>
  5.          <td colspan="2">
  6.             <table width="100%" cellpadding="0" cellspacing="0" border="0">
  7.              <tr class="yellow">
  8.                 <td><h6>REPORTING PARTY</h6></td>
  9.              </tr>
  10.             </table>
  11.          </td>
  12.         </tr>';
  13.  
  14. echo ' <tr><td></td></tr><tr><td>'.$user['license_Number'].'</td></tr>';
  15. }
  16. ?>
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.
Jun 6 '07 #21

Motoma
Expert 2.5K+
P: 3,235
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).
Jun 6 '07 #22

100+
P: 348
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.

Expand|Select|Wrap|Line Numbers
  1. VICTIM (Heading)
  2. (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 next heading. So we would have the VICTIM heading and the data just under that like so:
  3.  
  4. VICTIM
  5. Name: (pull name from the database)
  6. Address: (pull address from the database)
  7. City: (pull city from the database)
  8.  
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!
Jun 6 '07 #23

Motoma
Expert 2.5K+
P: 3,235
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.
Jun 6 '07 #24

100+
P: 348
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2. `customer_location`.`customer_Number_Seq`,
  3. `customer`.`name`,
  4. `address`.`address_1`,
  5. `address`.`address_2`,
  6. `city_zip`.`city`,
  7. `city_zip`.`state`,
  8. `address`.`zip`,
  9. `address`.`address_Description`,
  10. `address`.`district_Seq`,
  11. `person`.`last_Name`,
  12. `employee`.`employee_Seq`
  13. FROM
  14. `rel_customer_location_address`
  15. Inner Join `customer_location` ON `customer_location`.`customer_Location_Seq` = `rel_customer_location_address`.`customer_Location_Seq`
  16. Inner Join `address` ON `address`.`address_Seq` = `rel_customer_location_address`.`address_Seq`
  17. Inner Join `customer` ON `customer`.`customer_Number_Seq` = `customer_location`.`customer_Number_Seq`
  18. Inner Join `city_zip` ON `city_zip`.`zip` = `address`.`zip`
  19. Inner Join `rel_person_address` ON `address`.`address_Seq` = `rel_person_address`.`address_Seq`
  20. Inner Join `person` ON `rel_person_address`.`person_Seq` = `person`.`person_Seq`
  21. Inner Join `role` ON `person`.`person_Seq` = `role`.`person_Seq`
  22. Inner Join `employee` ON `role`.`role_Seq` = `employee`.`role_Seq`
  23. Inner Join `rel_employee_incident` ON `employee`.`employee_Seq` = `rel_employee_incident`.`employee_Seq`
  24.  
Thanks Motoma!
Jun 7 '07 #25

Motoma
Expert 2.5K+
P: 3,235
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.
Jun 7 '07 #26

100+
P: 348
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:

Expand|Select|Wrap|Line Numbers
  1. <table>
  2. <tr>
  3.     <td>Name:</td>
  4.     <td>mysql data name field</td>
  5. </tr>
  6. <tr>
  7.     <td>Address:</td>
  8.     <td>mysql data address field</td>
  9. </tr>
  10. <tr>
  11.     <td>City:</td>
  12.     <td>mysql data city field</td>
  13. </tr>
  14. </table>
  15.  
  16. Both of the following php codes are inside my html "template".
  17.  
  18.  
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('display_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 customerLocation=$location

foreach($resArr as $user) {

echo '

<tr>

<td colspan="2">

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

<tr class="yellow">

<td><h6>VICTIM</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>{occupation}</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
Jun 7 '07 #27

Motoma
Expert 2.5K+
P: 3,235
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.

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. $db = new Database();
  4.  
  5. $victimArr = $db->query("SELECT id AS `Victim Number:`, firstname AS `First Name:`, lastname AS `Last Name:` FROM victimquery");
  6. $houseArr = $db->query("SELECT id AS `House Number:`, address AS `Street Address:` FROM housequery");
  7. ...
  8. ...
  9. ...
  10.  
  11. if(count($victimArr) > 0)
  12. {
  13.   echo "<table><tr><th>VICTIMS</th></tr><tr><td><table>";
  14.   foreach($victimArr as $victim)
  15.   {
  16.     echo "<tr>";
  17.     foreach($victim as $attr => $value)
  18.     {
  19.       echo "<th>".$attr."</th><td>".$value."</td>";
  20.     }
  21.     echo "</tr>";
  22.   }
  23.   echo "</table></td></tr></table>";
  24. }
  25.  
  26. if(count($houseArr) > 0)
  27. {
  28.   echo "<table><tr><th>VICTIMS</th></tr><tr><td><table>";
  29.   foreach($houseArr as $house)
  30.   {
  31.     echo "<tr>";
  32.     foreach($house as $attr => $value)
  33.     {
  34.       echo "<th>".$attr."</th><td>".$value."</td>";
  35.     }
  36.     echo "</tr>";
  37.   }
  38.   echo "</table></td></tr></table>";
  39. }
  40. ...
  41. ...
  42. ...
  43.  
  44.  
  45. ?>
  46.  
In fact, that didn't use key() at all. But it's good to know about the key() function anyway :P
Jun 7 '07 #28

100+
P: 348
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.
Jun 7 '07 #29

Motoma
Expert 2.5K+
P: 3,235
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.
Jun 7 '07 #30

100+
P: 348
fjm
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).
Ok, so if I am understanding you correctly, you are saying that I need to use the session with the primary key from the database.

This is good because I had originally planned on making the user log in with their customer number which will coencide with the primary key auto-integer number.
Store that someplace like $_SESSION['userid'] and refer to that on every page and check that the user is authenticated.
So it would be like this then:
[PHP]
<?php
$_session['customer_Number']
?>
[/PHP]
Am I correct?

I am thinking that in the login form, there will be a select statement for the customer number. That customer number can then be stored in a customer variable like this:
[PHP]
<?php
$customer_Number = $db->query("SELECT customer_Number FROM customer WHERE customer_Number = $_POST['userid']");
[/PHP]

Is this right?
Jun 7 '07 #31

Motoma
Expert 2.5K+
P: 3,235
Pretty close: you will need to add session_start() at the beginning of your code, and refer to the session array as $_SESSION not $_session.

I would definitely do some research on data validation, as well as SQL Injection.
Jun 8 '07 #32

100+
P: 348
fjm
Pretty close: you will need to add session_start() at the beginning of your code, and refer to the session array as $_SESSION not $_session.

I would definitely do some research on data validation, as well as SQL Injection.
Thanks Motoma. I have heard of SQL injection and I know its not good. I hope that creating the login side does not become too difficult as a result of preventing injection attacks.
Jun 8 '07 #33

Post your reply

Sign in to post your reply or Sign up for a free account.