Page 1 of 1

noob question about puting results into table...

Posted: Sun Nov 12, 2006 4:21 pm
by me!
Ok I have the code here returning a table with all my clubs user info I want, but how do I go about doing the following...

I want the columns to have titles (member name, address, etc...)

Also on the far right side of the table I would like to a an image that is a link for “edit” “view” and “delete” I have the code to do the action just don’t know how to get the image in the table?

Last item that is only for looks, what is the best way to get each row to be a different background color?

Code: Select all

<?php
// Connecting, selecting database
// left blank 

// set the date for future use
$today = date("j-n-Y");

echo '<p>&nbsp;</p>';

// Performing SQL query
$query = 'SELECT pn_name, pn_uname, pn_phone, pn_duesexdate, barc_id FROM _users WHERE pn_duesexdate > 100 ORDER BY pn_name ASC, barc_id ASC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table width=\"100%\"  border=\"1\" cellspacing=\"0\" cellpadding=\"3\"><caption><b>Members, $today</b></caption>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

?>


Thanks,

Me

Posted: Sun Nov 12, 2006 5:44 pm
by me!
I found this example, is it the bast way to do it?

Code: Select all

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($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>";
?>

Re: noob question about puting results into table...

Posted: Sun Nov 12, 2006 9:27 pm
by califdon
I want the columns to have titles (member name, address, etc...)

Put in your column headers in HTML before you start any PHP. Or you can use echo from within PHP.

You are using very awkward syntax:

Code: Select all

// Printing results in HTML
echo "<table width=\"100%\"  border=\"1\" cellspacing=\"0\" cellpadding=\"3\"><caption><b>Members, $today</b></caption>\n";
For one thing, you should not be using Unix endline characters ("\n") or tab characters ("\t") in HTML!
I would write:

Code: Select all

?>
<div align="center"><b>Members <?php echo $today; ?></b></div>
<table width="100%" border="1" cellspacing="0" cellpadding="3">
<tr><td>Member Name</td><td>Address</td> ... </tr>
<?
while ($row=mysql_fetch_assoc($result)) {
   echo "<tr><td>$membername</td><td>$address</td> ... </tr>";
}
Also on the far right side of the table I would like to a an image that is a link for “edit” “view” and “delete” I have the code to do the action just don’t know how to get the image in the table?
Why would you store images in the table?? They will be the same for every line, won't they? Just echo the HTML <img ... > tags and their anchor <a ... >...</a> tags prior to the end of the row.
Last item that is only for looks, what is the best way to get each row to be a different background color?
I suggest that you first learn the basics of outputting data in HTML format. When you can do that, you can tackle the more difficult issue of counting odd and even lines and specifying the color.

Posted: Sun Nov 12, 2006 10:53 pm
by me!
Why would you store images in the table?? They will be the same for every line, won't they? Just echo the HTML <img ... > tags and their anchor <a ... >...</a> tags prior to the end of the row.
That is what I am trying to do... I just dont know how to output that with the results.

You are using very awkward syntax:
Code:
// Printing results in HTML
echo "<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"3\"><caption><b>Members, $today</b></caption>\n";

For one thing, you should not be using Unix endline characters ("\n") or tab characters ("\t") in HTML!
Oops did not notice that :oops:
I suggest that you first learn the basics of outputting data in HTML format. When you can do that, you can tackle the more difficult issue of counting odd and even lines and specifying the color.
Ya, Thanks but that does not help much, I can doe exactaly what I am asking in HTML, just am not sure how to get the PHP to play with it :wink:

Posted: Mon Nov 13, 2006 1:36 am
by ryuuka
first of all i recommend that you go to this http://www.tutorialized.com and http://www.gotAPI.com site
read up a little it will help alot.
to awnser your question:

Code: Select all

<?php 
  // creating the tags for the table
  echo "<table>";
  echo "  <tr>";
  echo "    <th></th>"; // open collum
  echo "    <th>title 1</th>"; //first title
  echo "    <th>title 2</th>"; // second title
  echo "    <th>title 3</th>"; // third title
  echo "    <th>title 4</th>"; // fourth title
  echo " <tr>"; // end of this row

  while (!$rs->EOF) { // start loop

    $a_Row = $rs->fields;     // put records in an array

    $s_title1   = $a_Row['ServerName'  ]; //store data in variables
    $s_title2   = $a_Row['Status'      ];
    $s_title3   = $a_Row['LastCheck'   ];

      echo "<td>$s_title1</td>";
      echo "<td>$s_title2</td>"
      echo "<td>$s_title3</td>"

    $rs->MoveNext(); //end table
  
?>
i put intentional errors in this (remmember we want you to learn something)

also this table was made with adodb and should be used as a guideline for future tables[/url]

Posted: Mon Nov 13, 2006 4:28 pm
by me!
Thanks for the links, I have a PHP book that I actually did read, cover to cover (not normal for me :) ) but find Google much better when I don’t know.

Some more question;
I is it best to put the results into variables? I have don’t this in other tables and done code almost the same as what you just provided, but I was under the impression that I was doing it wrong that way?

I understand everything except

Code: Select all

while (!$rs->EOF) { // start loop
What is EOF?

Code: Select all

$rs->MoveNext(); //end table
This does what?

As for the errors, I only notice one at this time. It needs a } at the end to make the if work.

Posted: Mon Nov 13, 2006 4:33 pm
by RobertGonzalez
Those are ADODB commands.

This means that while you are not at the end of the recordset...

Code: Select all

while (!$rs->EOF) { // start loop
This means to move to the next row in the recordset...

Code: Select all

$rs->MoveNext(); //end table

Posted: Mon Nov 13, 2006 4:49 pm
by me!
Ok so should I start learning ADODB also :o

Advantages? Reasons?

Posted: Mon Nov 13, 2006 4:58 pm
by RobertGonzalez
Walk before you run, crawl before you walk.

Learn what the inherent PHP functions do before getting into something like ADODB. After you have a somewhat solid grasp of the what the PHP functions accept and return, then move into objects that handle DB interaction.

Posted: Mon Nov 13, 2006 8:13 pm
by me!
Ok the ADODB stuff had this effect :roll:

So I will look at that at a later date :)

So after all the help here I came up with this, it works and I understant it (took the last few hours :!: ) Please take a look and let me know of any potental "problems"

Thanks all

Code: Select all

<?php
// Connecting, selecting database
$link = mysql_connect('localhost', 'xxxxxxx', 'xxxxxxxx')
   or die('Could not connect: ' . mysql_error());
mysql_select_db('xxxxxxx') or die('Could not select database');

// set the date for future use
$today = date("j-n-Y");

// Performing SQL query
$query = 'SELECT pn_name, pn_uname, pn_phone, pn_duesexdate, barc_id FROM _users WHERE pn_duesexdate > 100 ORDER BY pn_name ASC, barc_id ASC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Format the results table
echo "<table width=\"100%\"  border=\"1\" cellspacing=\"0\" cellpadding=\"3\"><caption><b>Members, $today</b></caption>";

// Set the headings
echo "<tr><th>Member Name</th><th>Call Sign</th><th>Phone</th><th>Mem. Expites</th><th>Mem. ID</th><th>Options</th><tr>";

// Poop out some date!
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$pn_uname = $line['pn_uname']; // need this for the, in table.
	   echo "<tr>";
   foreach ($line as $col_value) {
       echo "<td>$col_value</td>";
   }
   echo "<td><form action=\"search.php\" method=\"post\"><input type=\"hidden\" name=\"call\" value=\"$pn_uname\" /><input type=\"submit\" value=\"Edit\" /></form></td>";
   echo "</tr>";
}
echo "</table>";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link); 
?>

Posted: Tue Nov 14, 2006 10:21 am
by RobertGonzalez
If your strings are going to have double quotes in them, wrap the strings in single quotes so you don't have to escape everything...

Code: Select all

<?php
// Connecting, selecting database
$link = mysql_connect('localhost', 'xxxxxxx', 'xxxxxxxx') or die('Could not connect: ' . mysql_error());
mysql_select_db('xxxxxxx') or die('Could not select database');

// set the date for future use
$today = date("j-n-Y");

// Performing SQL query
$query = 'SELECT pn_name, pn_uname, pn_phone, pn_duesexdate, barc_id FROM _users WHERE pn_duesexdate > 100 ORDER BY pn_name ASC, barc_id ASC';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Format the results table
echo '<table width="100%"  border="1" cellspacing="0" cellpadding="3"><caption><b>Members, ' . $today . '</b></caption>';

// Set the headings
echo '<tr><th>Member Name</th><th>Call Sign</th><th>Phone</th><th>Mem. Expites</th><th>Mem. ID</th><th>Options</th><tr>';

// Poop out some date!
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo '<tr>';
    foreach ($line as $col_value) {
        echo '<td>' . $col_value . '</td>';
    }
    echo '<td><form action="search.php" method="post"><input type="hidden" name="call" value="' . $pn_uname .'" /><input type="submit" value="Edit" /></form></td>';
    echo '</tr>';
}
echo '</table>';

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

Posted: Tue Nov 14, 2006 12:53 pm
by califdon
There's certainly nothing wrong with doing it that way, but I prefer to simply use HTML (not within the PHP brackets) whenever there's a long stretch of plain HTML to output. Why require the PHP parser to process all that? And it will be easier to read and debug, too, not to mention not worrying about escaping the quotes. Example:

Code: Select all

...
?>  ////
<!-- Format the results table-->
<table width="100%"  border="1" cellspacing="0" cellpadding="3"><caption><b>Members, 
<?php  ////
echo $today;
?>  ////
</b></caption>

<!-- Set the headings-->
<tr><th>Member Name</th><th>Call Sign</th><th>Phone</th><th>Mem. Expites</th><th>Mem. ID</th><th>Options</th><tr>
<?php  ////
// Poop out some date!
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $pn_uname = $line['pn_uname']; // need this for the, in table.
           echo "<tr>";
   foreach ($line as $col_value) {
       echo "<td>$col_value</td>";
   }
?>  ////
   <td><form action="search.php" method="post"><input type="hidden" name="call" value="$pn_uname" /><input type="submit" value="Edit" /></form></td>
   </tr>
<?php  ////
} 
...

Posted: Tue Nov 14, 2006 12:57 pm
by RobertGonzalez
That is certainly another way to do it. Although it makes it bit more spaghetti like in nature, but it is another way of achieving the same result.

Posted: Tue Nov 14, 2006 1:10 pm
by califdon
Oops, I didn't respond to this:
me! wrote:
Why would you store images in the table?? They will be the same for every line, won't they? Just echo the HTML <img ... > tags and their anchor <a ... >...</a> tags prior to the end of the row.
That is what I am trying to do... I just dont know how to output that with the results.
Try something like this:

Code: Select all

foreach ($line as $col_value) {
       echo "<td>$col_value</td>"; 
   echo "<td><a href='foo_edit.php?id=".$line['pn_uid']."><img src='foo_edit.gif'></a></td>";
   echo "<td><a href='bar_del.php?id=".$line['pn_uid']."><img src='foo_del.gif'></a></td>";

Posted: Tue Nov 14, 2006 4:26 pm
by me!
f your strings are going to have double quotes in them, wrap the strings in single quotes so you don't have to escape everything...
I did not know that was an option! Thanks! :D

Oops, I didn't respond to this:
me! wrote:
Quote:
Why would you store images in the table?? They will be the same for every line, won't they? Just echo the HTML <img ... > tags and their anchor <a ... >...</a> tags prior to the end of the row.
That is what I am trying to do... I just dont know how to output that with the results.
Try something like this:

Code: Select all

foreach ($line as $col_value) {
       echo "<td>$col_value</td>";
   echo "<td><a href='foo_edit.php?id=".$line['pn_uid']."><img src='foo_edit.gif'></a></td>";
   echo "<td><a href='bar_del.php?id=".$line['pn_uid']."><img src='foo_del.gif'></a></td>";
Thanks, once i relised that I could alter the loop and stick in html I figured it out. But placement was a little tricky! Took three times to get "valid" page output even thow the table looked ok.

Thanks guys for all the help thus far. I know there will be a lot mere questions to come