Putting retrieved data in to a table

**creckless** · 07-01-2004, 05:25 AM

Hello all, got my script working, woohoo!!! But im struggling to put it in to a table, in fact im well and truly stuck.

At present my code is as follows

/* Connecting, selecting database */
$link = mysql_connect("localhost", "db", "pass")
or die("Could not connect : " . mysql_error());
echo "<strong>Your Search Results</strong> <br> Age - Home Team - Away Team - Date (YY/MM/DD)";
mysql_select_db("cndjfl_fixtures") or die("Could not select database");
$age=
$team=
$date=
/* Performing SQL query */
$query = "SELECT * FROM fixtures
WHERE age LIKE'$age'
AND ((hometeam LIKE '$team') OR (awayteam LIKE '$team'))
AND date LIKE '$date' ORDER BY date";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

/* Printing results in HTML */
echo "<table>\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($reult);

/* Closing connection */
mysql_close($link);
?></td>
</tr>
</table>
<br>

And this puts my results out, one results on each line. However there are 4 fields per line (age, hometeam, awayteam, date) and i want them in a table where each one is in its own cell!! I also want the table to be colour:#215908 with a 1pixel border but i am only a beginner and dont know what to do!!

So it should display as follows:

<table width="80%" border="1" cellpadding="1" cellspacing="0" bordercolor="#215908">
<tr>
<td>Age</td>
<td>Home Team </td>
<td>Away Team</td>
<td>Date</td>
</tr>
</table>

I hope someone can help me

**dbzguy** · 07-01-2004, 08:44 PM

PHP Code:


			
/* Connecting, selecting database */

$link = mysql_connect("localhost", "db", "pass")

or die("Could not connect : " . mysql_error());

echo "<strong>Your Search Results</strong> <br> Age - Home Team - Away Team - Date (YY/MM/DD)";

mysql_select_db("cndjfl_fixtures") or die("Could not select database");

$age=

$team=

$date=

/* Performing SQL query */

$query = "SELECT * FROM fixtures 

WHERE age LIKE'$age' 

AND ((hometeam LIKE '$team') OR (awayteam LIKE '$team')) 

AND date LIKE '$date' ORDER BY date";

$result = mysql_query($query) or die("Query failed : " . mysql_error());





/* Printing results in HTML */

echo "<table>\n";

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

echo "\t<tr>\n";

foreach ($line as $col_value) {

echo "\t\t<td>";

print_r($col_value);

echo "</td>\n";

}

echo "\t</tr>\n";

}

echo "</table>\n";

not sure zend doesnt say nything is wrong. so i just edited some things.

**celticbrue** · 07-02-2004, 05:15 AM

Hi creckless,

I assume the variables "age", "team" and "date" will be passed to the script via a form of some description. If so the following code will help you set up the page to display the results.

You can add any html you like before the php code starts, so you can tinker with the layout of the page until it suits you. I have kept this aspect very simple as the important thing is to get the php code sorted.

PHP Code:


			
<html>

<head>

<title>My Results Page</title>

<style type="text/css" media="screen">

    #header {background:#c0ffc0; font-family:arial, sans-serif; color:#215908; font-weight:bold; size:100%;}

    #data {background:#ffffc0; font-family:arial, sans-serif; color:#215908; font-weight:bold; size:90%; text-align:center;}

</style>

</head>

<body>



<!-- Set up the table to receive the results -->

<table width="80%" border="1" cellpadding="1" cellspacing="1" bordercolor="#215908">

<tr>

<th id="header">Age</th>

<th id="header">Home Team</th>

<th id="header">Away Team</th>

<th id="header">Date</th>

</tr>



<?php

//Define Database Variables

$host = "localhost";

$user = "";

$pass ="";

$dbname = "cndjfl_fixtures";



//These variables would normally be passed from the search form - uncomment them to check this script on its own

/*

$age="%";

$team="%";

$date="%";

*/





//Connect to database

$link = mysql_connect($host, $user, $pass)

or die("Could not connect : " . mysql_error());



//Run the query

$query = "SELECT * FROM fixtures 

WHERE age LIKE'$age' 

AND ((hometeam LIKE '$team') OR (awayteam LIKE '$team')) 

AND date LIKE '$date' ORDER BY date";



//Get the Result

$result = mysql_db_query($dbname, $query, $link) or die("Query failed : " . mysql_error()); 



// adds a new line to table for each record in database



while ($row=mysql_fetch_array($result)) {

  print("<tr><td id=\"data\">$row[age]</td>\n<td id=\"data\">$row[hometeam]</td>\n<td id=\"data\">$row[awayteam]</td>\n<td 



id=\"data\">$row[date]</td>\n</tr>\n");

    }



//Close the table



print("</table>\n");



//Close the database connection



mysql_close($link);



?>



<!-- close html -->



</body>

</html>

Hope this is useful to you. Need any more help, drop a line

Ian.

**Sam Stockley** · 08:34 AM

I hope you don't mind if I use the code you posted, too.

I used code and it works! Thing is, it retrieves all the info in the way it is set out in the database.

Are you able to tell me how I only retrieve certain columns and place them in the html?

Thanks.

**celticbrue** · 07-09-2004, 09:07 AM

Hi Sam,

Glad you like the code snippets above

To just take data from specific columns, you would just need to change the select statement: -

$query = "SELECT column1,column2 FROM tablename";

So to select just age and date from the table in the example above, you would do this

$query ="SELECT age,date FROM fixtures";

This would collect all rows in the database, but just select data from the age and date columns. You would then need to adjust the html code in the table set up to show just two columns, age and date instead of the four, and change the loop to just print the returned values into the table: -

PHP Code:


			
<html> 

<head> 

<title>My Results Page</title> 

<style type="text/css" media="screen"> 

    #header {background:#c0ffc0; font-family:arial, sans-serif; color:#215908; font-weight:bold; size:100%;} 

    #data {background:#ffffc0; font-family:arial, sans-serif; color:#215908; font-weight:bold; size:90%; text-align:center;} 

</style> 

</head> 

<body> 



<!-- Set up the table to receive the results --> 

<table width="80%" border="1" cellpadding="1" cellspacing="1" bordercolor="#215908"> 

<!-- only tow columns now -->

<tr> 

<th id="header">Age</th> 

<th id="header">Date</th> 

</tr> 



<?php 

//Define Database Variables 

$host = "localhost"; 

$user = ""; 

$pass =""; 

$dbname = "cndjfl_fixtures"; 



//These variables would normally be passed from the search form - uncomment them to check this script on its own 

/* 

$age="%"; 

$team="%"; 

$date="%"; 

*/ 





//Connect to database 

$link = mysql_connect($host, $user, $pass) 

or die("Could not connect : " . mysql_error()); 



//Run the query - Now it selects age and date from all rows in table 

$query = "SELECT age,date FROM fixtures"; 



//Get the Result 

$result = mysql_db_query($dbname, $query, $link) or die("Query failed : " . mysql_error()); 



// adds a new line to table for each record in database

//Now prints just the two values returned from the database 



while ($row=mysql_fetch_array($result)) { 

  print("<tr><td id=\"data\">$row[age]</td>\n<td id=\"data\">$row[date]</td>\n</tr>\n"); 

    } 



//Close the table 



print("</table>\n"); 



//Close the database connection 



mysql_close($link); 



?> 



<!-- close html --> 



</body> 

</html>

Hope this helps -

Ian

**Sam Stockley** · 07-10-2004, 06:18 PM

That's worked really well. Thanks a million, Ian.

Does anyone know how I might be able to do the following things:

1. Allow querying of three tables to look for results, and when displaying results, have a variable to correspond to each table? Also, I want there to be a form (which I have) which can search either all three or one of the three. How?

2. Have 10 results PER PAGE. How can I set up a multi-page results shower?

Any help is much appreciated!

Sam

**Veter** · 07-10-2004, 06:38 PM

1. Use for all querries different variables:
like:

PHP Code:


			
$query = "SELECT * FROM fixtures ....

$query1 = "SELECT * FROM fixtures1 ...

$query2 = "SELECT * FROM fixtures2 ...

.......



$result = mysql_query($query)

$result1 = mysql_query($query2)

$result2 = mysql_query($query2)

.....

etc.

2.

PHP Code:


			
$limit=10;

if(!isset($startrec)){$startrec=0;}

$nextpage=$limit+$startrec;

sql = "SELECT * FROM `yourtablehere`  LIMIT $startrec ,

 $limit";

.......

.......

//Code for link to the the next page

echo "<a href='list.php?startrec=".$nextpage."'>Next 10 

records</a>"