Populating MySQL Data in my webpage

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jeffyyy
Forum Newbie
Posts: 17
Joined: Sat Apr 24, 2010 9:06 pm

Populating MySQL Data in my webpage

Post by jeffyyy »

OK, hopefully someone here is able to help. I have a table with the following columns: tag, message, and date.
Right now, I have successfully setup a script that will:
A) Find record(s) matching the 'tag' when entered into a form
B) Insert a new record using a form.

What I would like to do is to know how to setup my page so that it will pull data from the SQL table and fill it into an HTML table so I can style the page properly. (I will be creating an iPhone webapp and would like to show the messages that match the 'tag' entered to show up in a table where a user will be able to click it to view more details.)

Any help would be greatly appreciated...and I have attached my code below:

Index.html

Code: Select all

<html>
	<head>
		<title>Check Your Tag</title>
    <link href="style.css" rel="stylesheet" type="text/css">
	</head>

	<body bgcolor="#333" style="color:white;">
	<div id="container"> 
	  <table width="100%" border="0" align="center">
	    <tr>
	      <td width="50%">Use the search form below to see if anybody has left a message for your license plate! </td>
	      <td width="50%">Use the form below to send a message to a license plate that you saw on the road:</td>
        </tr>
	    <tr>
	      <td><div align="center" id="checkmessages"><form action="search.php" method="post">
	    Search:
	    <input type="text" name="term" />
	    <br />
	    <input type="submit" name="submit" value="Submit" />
	      </form></div></td>
	      <td><div align="center" id="sendmessage"><form action="insert.php" method="post">
	      License Plate:
	      <input type="text" name="tag" />
	      Message:
	      <input type="text" name="message" />
	      <input type="submit" />
	      </form></div></td>
        </tr>
      </table>
	  <p></p>
    </div>
	</body>
</html>
Search.php

Code: Select all

<?php
mysql_connect ("localhost", "text_tag","qivbz7d6")  or die (mysql_error());
mysql_select_db ("text_tag");

$term = $_POST['term'];

$sql = mysql_query("select * from testtable where tag like '$term'");

while ($row = mysql_fetch_array($sql)){
	echo 'Your Tag: '.$row['tag'];
	echo '<br/> Message: '.$row['message'];
	echo '<br/><br/>';
	}

?>
insert.php

Code: Select all

<?php
$con = mysql_connect("localhost","text_tag","qivbz7d6");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("text_tag", $con);

$sql="INSERT INTO testtable (tag, message)
VALUES
('$_POST[tag]','$_POST[message]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Your message has been sent";

mysql_close($con)
?> 
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Populating MySQL Data in my webpage

Post by mecha_godzilla »

You could do this in two ways:

1. Generate all the HTML from within your PHP script (which is what I'd do if the page was simple)

2. Generate all your table code from within the PHP script, load it into a variable (which is appended so every time you write more code to it so the previous values aren't wiped out), then load in your HTML template (into another variable) and substitute a placeholder value in the template with the variable that holds all the table code, then echo out the variable. An alternative (but more difficult) way to do this would be to put some custom code in your HTML template that tells the PHP script which element of the page is supposed to repeat, then it will generate that section of code for each result - this is the 'professional' approach and would certainly make things easier to maintain :) As an example, you would want the <table> tag written once, the <tr><td></td></tr> tags repeated for as many results as you have, then the final </table> tag written once. You should also put some code in there if no results are found.

I'm not sure how much knowledge of PHP you have so if you need help with any of that please let me know and I should be able to help. On another point, I'm not sure whether the scripts you've added to your post are "mission grade" or not but you'll want to do some input validation to stop people messing with the script. Also, it would be better to keep your login values in a single file if possible (having everything in a single place makes it much quicker to update in the future) but you need to be careful about how and where you store that file - again, I can probably give you some pointers if you need them.

HTH,

Mecha Godzilla
jeffyyy
Forum Newbie
Posts: 17
Joined: Sat Apr 24, 2010 9:06 pm

Re: Populating MySQL Data in my webpage

Post by jeffyyy »

Actually, some help would be great...I'm just diving into the world of PHP and I am a fast learner, i just need to get pointed in the right direction, I want to make sure that I am doing this the best way possible, so a couple of questions:

1) To put the database constants in one file, I would just create a file (named constant.php) and put the constants in there, then just include that file in all scripts that use the db?
2) What kind of custom code would determine which element is repeating and which is not?
3) What is the best way to store that file in order to keep the database secure?
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Populating MySQL Data in my webpage

Post by mecha_godzilla »

Sorry about the delay in replying...

Answering your questions:

1. Yes - you can put all the database login details in one file and call it with include_once or require_once as necessary (require_once is better because the script will refuse to run if it doesn't find it). To make this file safe, you should save it to a directory below that of your publicly accessible pages. For example, if your site structure looks something like

/
/public_html/
/public_html/images/

put the file in the root directory (/) and then use a relative call from your scripts. So, if you have a file called show_results.php in the root folder of your web site (/public_html) you put a bit of code in it that looks like:

Code: Select all

<?php
require_once('../config.php');
?>
You could also use an absolute (rather than relative) link to your file. To find your full server path, try the following code:

Code: Select all

<?php
echo $_SERVER['DOCUMENT_ROOT'];
?>
A couple of other suggestions - your database details are constants but I'd shy away from creating a file actually called constant.php (this is sort of a "security by obscurity" approach). This is more relevant if the file has to be in the main web folder for any reason, because somebody could write a script that requests 'constant.php' or 'constants.php' from your server (phpBB v2 and BBClone both have a 'constants.php' file). Another point is that you don't want anybody to actually be able to run this script through a browser (again, this is only an issue if the file is in the web folder). What you can do to prevent this is set a constant value in the main script, then test to see whether this has been set in your included file. As an example, in your main script you could have:

Code: Select all

<?php
define('SPECIAL_SITE_VALUE', 1);
?>
then in your included file you'd put this:

Code: Select all

<?php
if (!defined('SPECIAL_SITE_VALUE') or !constant('SPECIAL_SITE_VALUE')) die('Stop this nasty h4x0r attempt at once!');
?>
Another alternative is to use a different suffix (so use 'db_config.inc' rather than 'db_config.php') and then create a rule in your .htaccess file that stops anyone from requesting it directly - if you want more details on how to do this I can try and find them for you.

2. Your custom code can be anything - it's up to you to decide how it's supposed to work. It's a little bit long-winded to go into but you could create some custom tags that look like this:

Code: Select all

[start_table]
<table width="100%" border="0">
<tr><th>Item</th><th>Price</th></tr>
[repeat]
<tr><td>$item</td><td>$price</td></tr>
[/repeat]
</table>
[end_table]
To make it work, you have to create some routines in your PHP script that open this file, save it to a variable (or more likely use the explode() function to sort everything into an array) and then search through this to find the relevant section of code. Once you know where the start and end sections of the code are, you can use these as pointers to retrieve the information inside them, then you just use echo() to output the HTML code. Again, this is a bit complicated to go into in a lot of detail but there might be some tutorials available if you want to go down this route, or take a look at the PHP manual for explode() to see what I'm talking about:

http://uk3.php.net/manual/en/function.explode.php

3. I think I probably answered this in answer 1.

Anything else please let me know :)

Mecha Godzilla
Post Reply