simcoweb Posted August 15, 2006 Share Posted August 15, 2006 First, thanks to all those who offer help in these forums. You rock.I'm hell bent to learn PHP, bought a book 'Learn PHP in 24 Hours (Sam's)' ... have owned it for 24 months. I've learned more in the past 40 days hanging around here and, through the help of the forum people, online tutorials, etc. and was able to write my first fully functional (and kinda cool may I add) script that featured an admin login, IP tracking, IP banning, page redirection, database connection, data retrieval and display, ability to convert data to CSV format, create a Zip file and download it. Plus a few extras. SO! It IS possible to learn this although it is a challenge!What i've learned is the BEST way to learn is to actually code. SO... i'm starting a new project for a client and wanted to start this thread in order to have a running dialogue for help instead of posting a couple of dozen threads. Hope this is ok.Here's the project outline to start this out. The first step is some guidance on the right way to start and the components. Opinions welcome :)Summary:I have a group of professionals ( doctors, lawyers, indian chiefs ) that have a networking group to promote each other's services. I need to create a system that includes:* category listings of services ( ex: medical, legal, real estate, etc. )* profiles of each member which includes: * name, title, business name, email (clickable), url to their personal site (clickable), summary of their business, list of their specialties, photo of the personThe category view will be like a link directory just showing the titles of the categories, when clicked on it takes them to a listing view of the contents of that category. These will be a short summary with a thumbnail. The summary will show their name, company name, and specialties then a link to View Full ProfileAn admin area will need to contain ability to add/edit/delete profiles as well as categories. Beyond that there's not a whole lot the admin has to provide. Therefore it's pretty straight forward.I plan to use a common template for the profiles (just done in HTML and summoned by something like view-profile.php). I've already experiemented with this using another thread for help. I created a simple model of the profile adding as a guide/experience. I need to expand on it to include these additional info items, however, which I can do now that I know how to create the form and parse the data into the database.So, first questions are:1. Is there a simple way to create the template system or should I just use an HTML template and wrap it in a PHP file to parse it?2. I am not clear on how to code repititive results into an HTML environment. In other words, what's the best way to display multiple profiles/results in a repetitive display using tables? ( ie: the summary page under the category clicked).3. This will also need to be paginated so the page doesn't grow a mile long. I'm working through the pagination tutorial (fine work might I add) but just wondered if there's an easier way to integrate it into this project while coding all the other functions.That's it for now. Looking for a bit of guidance on the best way to approach this. I'll be posting code questions as I start bumping into problems. Trying hard to do this myself until I get stuck and then getting help as I go. Thanks in advance! Quote Link to comment Share on other sites More sharing options...
newb Posted August 15, 2006 Share Posted August 15, 2006 i started with this<?php include ("file.php"); ?> Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 19, 2006 Author Share Posted August 19, 2006 Ok, here's the first request for some help :)I'm creating a 'template' and within that template is the code to summon the display of a person's profile. I've created the HTML for the template which consists of a table nested in another table whereas the various fields of the database will be displayed.In the template is the mysql query as well. There's several fields involved in each profile:nametitlecompanyphoneemaildetailsspecialtiesimage (their photo)Now, when I summon all these, this is what I have so far but don't know if this will work:[code]//need to open database and run query for this personmysql_connect($dbhost, $dbuser, $dbpass) or die('Database has gone bye bye');mysql_select_db($dbname) or die('Where oh where is that database');$sql="SELECT * FROM members WHERE name == '$name'";$results = mysql_query($sql);$num = mysql_num_rows($results);[/code]What I need is for each field to be pulled from the database and then displayed in this HTML that I have set in an echo statement:[code]<div align="center"> <table border="0" cellpadding="0" style="border-collapse: collapse" width="530"> <tr> <td colspan="2"> <img border="0" src="images/top.gif" width="530" height="35"></td> </tr> <tr> <td height="15" colspan="2"> <h2>$name </h2> </td> </tr> <tr> <td style="padding-left: 10px; padding-right: 10px; padding-top:5px; padding-bottom:5px" width="229" valign="top">$image</td> <td style="padding-left: 10px; padding-right: 10px" width="261"> <div align="center"> <table border="0" cellpadding="0" style="border-collapse: collapse" width="100%"> <tr> <td style="border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top: 1px dotted #666633; border-bottom-width: 1px; padding: 4px" width="46%" bgcolor="#E3E1E1"><font class="bodytext"><i> • $title </i></td> </tr> <tr> <td style="border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px" width="46%" bgcolor="#EFEDED"><font class="bodytext"> • $phone </td> </tr> <tr> <td style="border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px" width="46%" bgcolor="#E3E1E1"><font class="bodytext"> • $email </td> </tr> <tr> <td style="border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px" width="46%" bgcolor="#EFEDED"><font class="bodytext"> • $url </td> </tr> <tr> <td style="border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px" width="46%" bgcolor="#E3E1E1"> • <font class="bodytext"><a href="contact.php">Click here to contact me</a> </td> </tr> </table> </div> </td> </tr> <tr> <td style="padding: 4px" colspan="2"><font class="bodytext"> About My Services</td> </tr> <tr> <td style="padding: 4px" colspan="2"><font class="bodytext"> My Specialties</td> </tr> <tr> <td style="padding-left: 0px; padding-right: 0px; padding-top: 4px; padding-bottom: 4px" background="images/bottom.gif" height="37" colspan="2"> <p align="center" class="bodytext">You can use our <a style="color: #0000FF" href="search.php">key word search</a> option also.</td> </tr> </table> </div>[/code]I'm not 100% the right syntax to make the query pull all fields and then display them in their respective locations in the HTML. Any guidance would be great :) Quote Link to comment Share on other sites More sharing options...
corbin Posted August 19, 2006 Share Posted August 19, 2006 $sql="SELECT * FROM members WHERE name == '$name'";Should be $sql="SELECT * FROM `members` WHERE `name` = '$name'";And what happens if the name is the same for 2 people... Sounds like its a fairly large amount of people so theres bound to be one set of the same name... Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 19, 2006 Author Share Posted August 19, 2006 It's about 25-40 people max. It's quite possible they could have the same first name but not the same last name/combo. The 'name' field is the main id field, though. Is there a better way to handle this? Quote Link to comment Share on other sites More sharing options...
corbin Posted August 19, 2006 Share Posted August 19, 2006 Hmmm never mind i figured it was more people than that... You should be fine that way... Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 19, 2006 Author Share Posted August 19, 2006 Cool. Ok, i've worked up a test script in order to test the display of the user's profiles. The problem is it displays the HTML just fine but no data inserted. You can view this at www.plateauprofessionals.com/display.php[code]<?php // test to display addbiz resultsinclude 'config.php';mysql_connect($dbhost, $dbuser, $dbpass) or die('No database here, boss');mysql_select_db($dbname) or die('That database can not be found, boss.');$sql=("SELECT * FROM members");$result = mysql_query($sql) or die('Can not find that database today');while ($row = mysql_fetch_array($result)) {echo <<<HTML<div align='center'><table border='0' cellpadding='0' style='border-collapse: collapse' width='530'><tr><td colspan='2'><img border='0' src='http://www.plateauprofessionals.com/images/top.gif' width='530' height='35'></td></tr><tr><td height='15' colspan='2'><h2>$name</h2></td></tr><tr><td style='padding-left: 10px; padding-right: 10px; padding-top:5px; padding-bottom:5px' width='229' valign='top'>$image</td><td style='padding-left: 10px; padding-right: 10px' width='261'><div align='center'><table border='0' cellpadding='0' style='border-collapse: collapse' width='100%'><tr><td style='border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top: 1px dotted #666633; border-bottom-width: 1px; padding: 4px' width='46%' bgcolor='#E3E1E1'><font class='bodytext'><i>• $title</i></td></tr><tr><td style='border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px' width='46%' bgcolor='#EFEDED'><font class='bodytext'>• $phone</td></tr><tr><td style='border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px' width='46%' bgcolor='#E3E1E1'><font class='bodytext'>• $email</td></tr><tr><td style='border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px' width='46%' bgcolor='#EFEDED'><font class='bodytext'>• $url</td></tr><tr><td style='border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px dotted #666633; padding: 4px' width='46%' bgcolor='#E3E1E1'>• <font class='bodytext'><a href='contact.php'>Click here to contact $name</a></td></tr></table></div> </td></tr><tr><td style='padding: 4px' colspan='2'><font class='bodytext'>About My Services</td></tr><tr><td style='padding: 4px' colspan='2'><font class='bodytext'>My Specialties</td></tr><tr><td style='padding-left: 0px; padding-right: 0px; padding-top: 4px; padding-bottom: 4px' background='images/bottom.gif' height='37' colspan='2'><p align='center' class='bodytext'>You can use our <a style='color: #0000FF' href='search.php'>key word search</a> option also.</td></tr></table></div>HTML;} ?> [/code] Quote Link to comment Share on other sites More sharing options...
corbin Posted August 19, 2006 Share Posted August 19, 2006 $name and $url and any other variables pulled thru $row = mysql_fetch_array($result)) should be in the format of$row['column_name'] such as $row['name'] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 19, 2006 Author Share Posted August 19, 2006 First, thanks for your responses :)Second, can you elaborate on that a bit, please? I'm not sure how you mean that would apply to printing that data into the various locations i've laid out in the HTML. Thanks! Quote Link to comment Share on other sites More sharing options...
corbin Posted August 19, 2006 Share Posted August 19, 2006 Well the basic gist of mysql_fetch_array or mysql_fetch_assoc is that it returns an array... If a table had 2 colums, first and last, and 2 rows, Bob | Smith, and Fred | Jackson, mysql_fetch array would return an array that would have first storing bob as key 0 and smith as key 1 and last storing Smith and Jackson... Gah i just read what i typed and it didnt make sense... Lol if that didnt make sense just ignore it... Anyways mysql_fetch_array returns an array of the database values... With the column as the key and the row as the value...[code]<font class='bodytext'><a href='contact.php'>Click here to contact $name</a>[/code]I dont see where $name is set so I'm assuming that your trying to pull name from the database... But instead of setting $name it sets $row['name'] because of the way it returns the array. If you had [code]while($monkey = mysql_fetch....[/code]You would use $monkey['name']If I remember right you can add extract($row); under the while statement and it will let you use variables such as name and so on... Only problem with that is that variables can clash and that can cause issues so I just always keep them in the array... Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2006 Share Posted August 19, 2006 I prefer to start with the data. This structure will allow a lawyer who works for a real estate firm to be listed under Leagal and Real Estate categories. It also allows him to have several specialites such as Commercial Properties, Divorce, Injury Claims etc.[code]category member_cat members specialties ========== =========== ========= ============categoryid --+ id +-- memberid --+ idcategory | memberid >-+ name +-< memberid +-< categoryid title speciality company phone email details image[/code]And yes, use id's. It's far more efficient than using names as keys and eliminates possibilty of duplication with John Smith the Lawyer and John Smith the Doctor. Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 19, 2006 Author Share Posted August 19, 2006 Corbin, thanks for that explanation. After reading Barand's post I just think i'm using the wrong method. Basically the data presenation flow should go like this:* visitor comes to the home page and is presented with 4 category choices (let's say A, B, C and D for fun)* visitor clicks on D* I need all profiles associated with D to show up (actually it will be a brief mini-listing)* when link in mini-listing is clicked on it will take the visitor to the member's full profileI'm not sure if I should be using a 'while' or a 'foreach' function.Barand, in your outline then i'd create these additional tables as well as set up an auto-incremental field for the id's.. correct? Now, in the profile form I need to, in some cases, pass the data into more than one table. Not sure how to handle that part. Also, quick note, in the 'Specialties' field I was going to make that just a text field (text area). I'm assuming it would still be workable the way you have it structured. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2006 Share Posted August 19, 2006 [quote]Also, quick note, in the 'Specialties' field I was going to make that just a text field (text area). I'm assuming it would still be workable the way you have it structured.[/quote]A text field is ok if all you ever want to do is just print it out. If you use my way it's easier to search/sort by speciality.Writing to multiple tables. First you insert the the member record, get it's new id and write that id to the record(s) in the member_cat table[code]<?php$sql = "INSERT INTO member (name, email, etc) VALUES ('$name', '$email', '$etc')";mysql_query($sql);$newid = mysql_unsert_id();$sql = "INSERT INTO members_cat (memberid, categoryid) VALUES ('$newid', '$catid')";mysql_query($sql);?>[/code]To get your list of those in category D[code]<?php$cat = 'D';$sql = "SELECT m.name, m.memberid, m.title, m.emeil FROM member m INNER JOIN member_cat mc ON m.memberid = mc.memberid WHERE mc,categoryid = '$cat'";$res = mysql_query($sql) or die(mysql_error());echo "<TABLE border='1'>\n";while (list($name,$id,$title,$email) = mysql_fetch_row($res)) { echo "<TR> <TD>$title $name</TD> <TD>$email</TD> <TD><a href='profile.php?id=$id'>View profile</a></TD> </TR>\n";}echo '</TABLE>';?>[/code] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 19, 2006 Author Share Posted August 19, 2006 Thanks for those great examples :)Now, as far as the 'specialties', instead of a textarea then what would you recommend? Perhaps a series of text boxes labeled like 'specialty1', 'specialty2', etc. ? I'm not 100% clear on your example for separating those to be more searchable. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 19, 2006 Share Posted August 19, 2006 [quote]Now, as far as the 'specialties', instead of a textarea then what would you recommend? Perhaps a series of text boxes labeled like 'specialty1', 'specialty2', etc.[/quote]Yes, but only if you want to squeeze more functionality from them other than just displaying them.It means you can do something like this if you are looking for, say, a divorce lawyer (similar to the category search)[code]SELECT m.id FROM member m INNER JOIN speciality sON m.memberid = s.memberidWHERE s.speciality IN ('Divorce', 'Family Law', 'Matrimonial')ORDER BY s.speciality[/code]Another possibility is to have a table of specialities linked to the category table then if the 'Legal' category is selected provide a list of possible specialities with checkboxes and provide text boxes for "others" not in the list.eg[pre]catid | category 1 | Legal 2 | Medical id | Catid | Speciality 1 | 1 | Divorce 2 | 1 | Family Law 3 | 1 | Corporate Law 4 | 2 | Paediatrics 5 | 2 | Geriatrics 6 | 2 | Cosmetic surgery[/pre] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 20, 2006 Author Share Posted August 20, 2006 Hmmm, ok... so what I could do is possibly create a series of checkboxes as a method of them selecting both the category they belong in AND the specialties they offer. Then those would be written to the database accordingly and make the connection between them. Or a similar way of selecting the specialties other than checkboxes. That could be a LOT of checkboxes. Also, if you could, please.. as i'm learning studiously as I go along... explain just a bit of this code you posted. The 'm.id' for example. I'm not family with that syntax and any explanations you can offer are greatly appreciated as to how you utilize this code. Thanks![code]SELECT m.id FROM member m INNER JOIN speciality sON m.memberid = s.memberidWHERE s.speciality IN ('Divorce', 'Family Law', 'Matrimonial')ORDER BY s.speciality[/code] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 20, 2006 Author Share Posted August 20, 2006 Also, I think it's important to understand how the database will interact with the visitors. Basically like this:* visitor comes to site, views home page, is presented with the 4 main categories.* visitor clicks on a category and is taken to a page that displays a 'summary' of each profile in that category (up to 'x' then pagination takes over)* summary will contain a thumbnail, name, company name and title with a link to their full profile* full profile will contain pic, name, title, company, location, email, url to their site, details of their services and list of specialtiesA search function will be in place for them to search by keyword as well. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2006 Share Posted August 20, 2006 [quote author=simcoweb link=topic=104388.msg418782#msg418782 date=1156050177]Hmmm, ok... so what I could do is possibly create a series of checkboxes as a method of them selecting both the category they belong in AND the specialties they offer. Then those would be written to the database accordingly and make the connection between them. Or a similar way of selecting the specialties other than checkboxes. That could be a LOT of checkboxes.[/quote]Just a suggestion. Or multiselect list as used in job search pages? [quote author=simcoweb link=topic=104388.msg418782#msg418782 date=1156050177]Also, if you could, please.. as i'm learning studiously as I go along... explain just a bit of this code you posted. The 'm.id' for example. I'm not family with that syntax and any explanations you can offer are greatly appreciated as to how you utilize this code. Thanks![code]SELECT m.id FROM member m INNER JOIN speciality sON m.memberid = s.memberidWHERE s.speciality IN ('Divorce', 'Family Law', 'Matrimonial')ORDER BY s.speciality[/code][/quote]User enters Divorce, Family Law, Matrimonial as keywords in a search form.m and s are aliases for the member and speciality tables.IN (list) is an alternative to "OR". So the query could've been written like this[code]SELECT member.id FROM member INNER JOIN specialityON member.memberid = speciality.memberidWHERE (speciality.speciality = 'Divorce') OR (speciality.speciality = 'Family Law')OR (speciality.speciality = 'Matrimonial')ORDER BY speciality.speciality[/code] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 20, 2006 Author Share Posted August 20, 2006 Ok, thanks for adding some clarification to that :)I'm setting up the query to create the multiple tables to handle the members, specialties, categories, and member_catid as you've laid out.The part of the project i'm working on right now is the right syntax to get the current data that I have and display it inside the HTML that I have laid out on that example page [url=http://www.plateauprofessionals.com/display.php]http://www.plateauprofessionals.com/display.php[/url] Right now i'm using a while statement as so:[code]mysql_connect($dbhost, $dbuser, $dbpass) or die('No database here, boss');mysql_select_db($dbname) or die('That database can not be found, boss.');$sql=("SELECT * FROM members");$result = mysql_query($sql) or die('Can not find that database today');while ($row = mysql_fetch_array($result)) {echo <<<HTML[/code]But no results show in the output. I'm using variables throughout the HTML like so:<tr><td style='border-left-width: 1px; border-right-style: solid; border-right-width: 1px; border-top: 1px dotted #666633; border-bottom-width: 1px; padding: 4px' width='46%' bgcolor='#E3E1E1'><font class='bodytext'><i>• $title</i></td></tr>The HTML outputs fine with no errors. Just no data populates the HTML. I've concluded that using the 'while' loop is probably not the right way to go and i'm thinking a 'foreach' function instead. Once again, the goal is to have the HTML repeat for each profile pulled from the database. So, in a simple logical code example (most likely NOT proper syntax):[code]foreach ($row = mysql_fetch_array($result)) {echo <<<HTML// all the HTML here} [/code]All the examples i've viewed in tutorials show simple table useage for the results and using the .$row['name'] syntax like this:[code]echo "<table border='1'>";echo "<tr> <th>Name</th> <th>Age</th> </tr>";// keeps getting the next row until there are no more to getwhile($row = mysql_fetch_array( $result )) { // Print out the contents of each row into a table echo "<tr><td>"; echo $row['name']; echo "</td><td>"; echo $row['age']; echo "</td></tr>"; } echo "</table>";[/code]I'm using one universal 'echo' statement instead of doing it for separate lines. Once again, looking for a little guidance in this :) Quote Link to comment Share on other sites More sharing options...
AndyB Posted August 20, 2006 Share Posted August 20, 2006 Unless I'm misreading your post, it looks as though you are trying to echo the value of non-existent variables. $title is part of the $row array and only available by individually abstracting row data, i.e. $title = $row['the_actual_name_of_the_title_field'], or by using the fieldnames AFTER using extract($row) to make the variables accessible. Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 20, 2006 Author Share Posted August 20, 2006 Hi AndyB. Can you give me a quick snippet as an example of what you mean? I understand it logically but i'm the visual type if you know what I mean. You're right on with the problem. You can tell I put it together with a 'logical' approach but not proper syntax. That's what i'm looking to learn. :) Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 20, 2006 Author Share Posted August 20, 2006 Ok, this is an update. I went to PHP.net and reviewed the 'extract' info then added this line:[code]extract(mysql_fetch_array($result,EXTR_SKIP));[/code]Now it displays the results utilizing the variables I had in place in the HTML ( $name, $title, etc.)Only problem is it's repeating the first entry in the database instead of one display for each entry. There's two entries in the db.I set up a simple query to display the content of the database: [url=http://www.plateauprofessionals.com/testdata.php]http://www.plateauprofessionals.com/testdata.php[/url]Those are the two entries. Here's the output of the full display after inserting the 'extract()' function: [url=http://www.plateauprofessionals.com/display.php]http://www.plateauprofessionals.com/display.php[/url]The 'while' loop is still in there as so:[code]while ($row = mysql_fetch_array($result)) {echo <<<HTML[/code]Not sure why it's not extracting both entries. Ideas? Quote Link to comment Share on other sites More sharing options...
redarrow Posted August 20, 2006 Share Posted August 20, 2006 [code]while ($row = mysql_fetch_array($result)) {echo <<<HTML[/code]to[code]while ($row = mysql_fetch_assoc($result)) {echo <<<HTML[/code] Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2006 Share Posted August 20, 2006 I gave you an example in of how to do this in reply #12 in this thread. Quote Link to comment Share on other sites More sharing options...
simcoweb Posted August 20, 2006 Author Share Posted August 20, 2006 Barand, I thought that code was for displaying the 'summary' page from a particular category. Maybe I misunderstood that. I've snagged that bit of code to work as a model for the summary pages.What i'm trying to figure out right now is how to get all entries of the database to display in the HTML format i've layed out in the url I provided (display.php). Or is this the same method you implied in reply #12?[b]redarrow[/b], I made that change but the display still shows the same entry twice instead of the two separate entries. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.