Jump to content



Welcome to KnowledgeSutra - Dear Guest , Please Register here to get Your own website. - Ask a Question / Express Opinion / Reply w/o Sign-Up!
- - - - -

Mysql Data Output

Started by mrdee, Feb 09 2011 06:40 PM

4 replies to this topic

#1 mrdee

    Privileged Member

  • Kontributors
  • PipPipPipPipPipPipPipPipPip
  • 810 posts
  • Gender:Male
  • Location:Slough (UK)
  • myCENT:90.63

Posted 09 February 2011 - 06:40 PM

Hi,
I have a MySQL database with the following fields:
ID (speaks for itself), _sfm_form_submision_time_ (time form was submitted), _sfm_visitor_ip_ (visitor IP address), Name (Visitor's Name), Town (visitor's loacation), Emaiil (visitor's Email), Message (Text block to leave a message up to 500 chars).
(And I now see I made an error in a field name, by using Emaiil instead of Email).

However, I would like to display the submitted data, only for the fields Name, town and message in a table on my website.

I have a bit of code, but then get hopelessly stuck.
I would like the display to look something like the attached screenshot.

I already have a bit of code, but then I get hopelessly stuck.
This is what I have:
<?php
$username="mrdee";
$password="**********";
$database="mrdee_reloader";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);

mysql_close();

$query="SELECT * FROM rouw";
$result=mysql_query($query);
$num=mysql_numrows($result);

$i=0;
while ($i < $num) {

$Name=mysql_result($result,$i,"Name");
$Town=mysql_result($result,$i,"Town");
$Emaiil=mysql_result($result,$i,"Emaiil");

$i++;
};

?>
(Not sure whether that is all 100% correct).
But then, I get hopelessly stuck with the code to put those data in a table as in the screenshot below.

Anyone who could help, please?

Also, I have tha page with the form to enter the data, and I would like the display table to appear below the form.
The code for that page is as follows:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html lang="en">
  <head>
	<meta charset="utf-8">
	<meta name="generator" content="CoffeeCup HTML Editor (www.coffeecup.com)">
	<meta name="created" content="Wed, 09 Feb 2011 09:55:46 GMT">
	<meta name="description" content="">
	<meta name="keywords" content="">
	<title></title>
	
	<!--[if IE]>
	<script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
	<![endif]-->
  </head>
  <body>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<div align="center"><h1>Betuig Uw deelneming - Express your sympathy</h1>
De dood van een jonge, moedige vrouw kan niemand onberoerd laten.<br>
<br />Daarom heb ik, na het insturen van een bericht door een bezoeker, besloten hier een rouwregister te plaatsen.<br>
Dit zal, bij voldoende invoer, overgemaakt worden aan de nabestaanden van <i>Marie-Rose Morel</i>.<br>
Alle velden zijn verplicht, Uw e-postadres, echter, dient enkel om U een bevestiging te sturen, en wordt noch getoond, noch ergens anders voor gebruikt.<br>
Alvast hartelijk bedankt.<br>
De webmeester.<br /><br />
The death of a young, brave woman cannot leave anyone untouched.<br>
<br />That is why I, after a visitor sent a message of sympathy, have decided to put a condoleance register here.<br>
I will, if there is enough input, pass it on to <i>Marie-Rose Morel</i>'s loved ones.<br>
All fields are required, your e-mail address, however, is only needed to send you a confirmation, it will not be shown, nor used for anything else.<br>
Thank you very much in advance.<br>
The webmaster.<br /><br />
<iframe src='http://www.vlaanderen-flanders.org.uk/forms/rouw/rouw.php?sfm_from_iframe=1' frameborder='1' width='45%' height='640' allowtransparency='true'></iframe></div> <br /><br />
</div>
</body>
</html>

</body>
</html>

The "<iframe></iframe>" is the link to display the form, should I now, when I have the full code for formatting the table, use another iframe below it, or should I just put the PHP code below it, or should the code for the display table be written in HTML?

Sorry if my questions seem somewhat stupid and/or if my coding is lousy, but I am a COMPLETE novice when it comes to PHP, and I would desperately love to learn more..
Any help will be much appreciated.

PS. In the mean time I have corrected the field name error from 'Emaiil' to the correct 'Email'.

Attached File  condtable.jpg   36.53K   6 downloads

Edited by moderator, 30 April 2012 - 12:00 PM.
programming language used should be in code tags

#2 sonesay

    |||[ n00b King ]|||

  • Kontributors
  • PipPipPipPipPipPipPipPipPip
  • 958 posts
  • Gender:Male
  • Location:Auckland
  • myCENT:68.27

Posted 25 February 2011 - 11:15 PM

View Postmrdee, on 09 February 2011 - 06:40 PM, said:

Hi,
I have a MySQL database with the following fields:
ID (speaks for itself), _sfm_form_submision_time_ (time form was submitted), _sfm_visitor_ip_ (visitor IP address), Name (Visitor's Name), Town (visitor's loacation), Emaiil (visitor's Email), Message (Text block to leave a message up to 500 chars).
(And I now see I made an error in a field name, by using Emaiil instead of Email).

However, I would like to display the submitted data, only for the fields Name, town and message in a table on my website.

I have a bit of code, but then get hopelessly stuck.
I would like the display to look something like the attached screenshot.

I already have a bit of code, but then I get hopelessly stuck.
This is what I have:



(Not sure whether that is all 100% correct).
But then, I get hopelessly stuck with the code to put those data in a table as in the screenshot below.

Anyone who could help, please?

Also, I have tha page with the form to enter the data, and I would like the display table to appear below the form.
The code for that page is as follows:



The "<iframe></iframe>" is the link to display the form, should I now, when I have the full code for formatting the table, use another iframe below it, or should I just put the PHP code below it, or should the code for the display table be written in HTML?

Sorry if my questions seem somewhat stupid and/or if my coding is lousy, but I am a COMPLETE novice when it comes to PHP, and I would desperately love to learn more..
Any help will be much appreciated.

PS. In the mean time I have corrected the field name error from 'Emaiil' to the correct 'Email'.

Attachment condtable.jpg


Does your while loop work? That's the first time I've seen variables being assigned that way in your loop. There is also a problem with your 'localhost'. it should be a string not just localhost which is a constant undefined. no $ symbol means it will look for the constant localhost.

This is how I would of done it
 <?php
$username="mrdee";
$password="**********";
$database="mrdee_reloader";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM rouw";
$result=mysql_query($query);
// loop through array and output till its finished
while($row = mysql_fetch_assoc($result)) {
    echo $row["Name"] . "<br />";
    echo $row["Town"] . "<br />";
    echo $row["Email"] . "<br />";
}


?>
You will need to place the while loop where you desire the output to occur. This would be in your html page etc.

#3 mrdee

    Privileged Member

  • Kontributors
  • PipPipPipPipPipPipPipPipPip
  • 810 posts
  • Gender:Male
  • Location:Slough (UK)
  • myCENT:90.63

Posted 28 February 2011 - 01:32 AM

Thank you for your help, sonesay,
I used a script (thanks to some copying and pasting) which does what you explained, but also formats the data in a table, this is what I came up with:

<html>
<body>
<div align="center"><h1>Rouwregister - Condolence Register</h1></div>
<div align="center">
<?php
$username="mrdee";
$password="***********";
$database="mrdee_reloader";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM rouw";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Naam/Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Plaats/Town</font></th>
<th><font face="Arial, Helvetica, sans-serif">Boodschap/Message</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"Name");
$f2=mysql_result($result,$i,"Town");
$f3=mysql_result($result,$i,"Message");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
</tr>

<?php
$i++;
}
?></div>
</body>
</html>

But, once again, like I said, it works, and I did it with copy and paste from a PHP tutorial, but I just find it impossible to understand all the variables in it, or to see how exactly the loop works, unlike the good old "FOR/NEXT" loops in the old days of BASIC.

I have been doing so much research, reading lots of sites, books and ebooks, hopint to learn PHP properly, but it still all looks like Chinese to me.

I must also say, the Dummies in this world must all possess a PhD or something, as the "for Dummies" books I read are usually so complicated that I can't make heads or tails of most of the stuff written in them, like the book "PHP and MySQL for Dummies" by Janet Valade I have.

I went for this book, hoping to learn, but some bits get so complicated from the very beginning that my whole nervous system seems to freeze.

The problem is, I have not been able to find any evening classes or anything where you can go to learn PHP/MySQL as it seems to me self study is not really advisable, or maybe it is my age catching up with me or me getting past it.

But, anyway, thank you for your help.

Edited by mrdee, 28 February 2011 - 01:32 AM.

#4 8ennett

    Newbie [Level 1]

  • Kontributors
  • Pip
  • 16 posts

Posted 27 March 2011 - 02:26 PM

Ok, I have been through your initial sample code and made some changes/amendments. I've also never seen someone go through a sql result the way you did. Also while looping I noticed you keep assigning values to the same variable over and over, this will overwrite the information put in the variable when previously looped. Anyway here's my version of the code.

<?php
$username="mrdee";
$password="**********";
$database="mrdee_reloader";

mysql_connect('localhost', $username, $password);
mysql_select_db($database);

$result = mysql_query("SELECT Name, Town, Email FROM rouw");

if (mysql_num_rows($result) > 0){
  echo '<table><tr><td>Name</td><td>Town</td><td>Email</td></tr>';
  while($row = mysql_fetch_assoc($result)){
    echo '<tr><td>'.$row['Name'].'</td><td>'.$row['Town'].'</td><td>'.$row['Email'].'</td></tr>';
  }
  echo '</table>';
}
else {
  echo 'No results';
}

mysql_close();
?>

So the above code first opens the database like before, but I changed it around because for some reason you had a query run with no query data, then closed the database connection before running your real query.

Next we run the mysql_query and put the result in to the $result variable, however I put the query data straight in to the mysql_query() function because I never saw the need to put it in a seperate variable if you aren't modifying it first. I also modified the query so it only selects Name, Town and Email instead of * which is all. This should reduce query time significantly.

Next up we check to see if there are one or more rows returned from the query and if so we move on, if not then we echo No results.

Now we have confirmed results, first we echo the start of the html output for our table. After that we run a while loop, but instead of looping through the amount of rows returned by the query instead we loop through the query result itself using mysql_fetch_assoc and assign the results of each row to the $row variable. So now we can access each returned column value through $row which is now an array of 3, $row['Name'], $row['Town'], $row['Email'] and obviously there would be more if you chose to select more from the database.

So we insert these array items now in to a new table row (<tr></tr>) which constructs the table.

At the end of the loop we close off the table and that's it. Now we are returning the results of our query in to a html table.

If you don't want to echo the results then try using the following instead to place the table in to a variable instead.

<?php
$username="mrdee";
$password="**********";
$database="mrdee_reloader";

mysql_connect('localhost', $username, $password);
mysql_select_db($database);

$result = mysql_query("SELECT Name, Town, Email FROM rouw");

if (mysql_num_rows($result) > 0){
  $resulttable = '<table><tr><td>Name</td><td>Town</td><td>Email</td></tr>';
  while($row = mysql_fetch_assoc($result)){
    $resulttable .= '<tr><td>'.$row['Name'].'</td><td>'.$row['Town'].'</td><td>'.$row['Email'].'</td></tr>';
  }
  $resulttable .= '</table>';
}
else {
  $resulttable = 'No results';
}

mysql_close();
?>


If you use this code at the top of your php page then later on in the page if you want to display your new results table you would merely 'echo $resulttable;' and that's it.

I hope this helps you!

Edited by 8ennett, 27 March 2011 - 02:27 PM.

#5 mrdee

    Privileged Member

  • Kontributors
  • PipPipPipPipPipPipPipPipPip
  • 810 posts
  • Gender:Male
  • Location:Slough (UK)
  • myCENT:90.63

Posted 01 April 2011 - 08:09 PM

Quote

I've also never seen someone go through a sql result the way you did.
Well, I said I am a total beginner.

Nevertheless, your explanation is very clear and very detailed, much better than anything else I have read, where they just assume you know it all.

Quote

I hope this helps you!
It certainly has.
Thank you.
Back to PHP Programming · Next Unread Topic →

Reply to this topic


This post will need approval from a moderator before this post is shown.

  


1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users

  1. Discuss Anything
  2. → Main
  3. → Computers
  4. → Programming Languages
  5. → PHP Programming
  6. Welcome - Start here! - Free Web Hosting