I'd like to display the contents of some fields in a MySql table as a table on a webpage. I found sample scripts, but I'm doing something wrong in trying to adapt them. Can anyone give me a link to a webpage that displays a table like this, so I can view the source code and see where I'm going wrong?
TIA
Tearose49

Member Avatar for Zagga

I'd like to display the contents of some fields in a MySql table as a table on a webpage. I found sample scripts, but I'm doing something wrong in trying to adapt them. Can anyone give me a link to a webpage that displays a table like this, so I can view the source code and see where I'm going wrong?
TIA
Tearose49

Hi,
by viewing the source code of a page you will not be able to see the PHP that generated the page, only the result of what the PHP did.

Where are you having the problem? Is it retrieving the data from the database, or displaying it in the table?


Zagga

Well, the sample I used put the php on the html page. And I don't know where the problem lies, which is why I'm trying to see a working example. I think it's not connecting to the database, as we added a couple echo statements that should tell me if if got to the db, then to the table. It doesn't print them, so I guess that's the first problem, but I don't know if some of the fill in parts were filled in right either. What I need to see is a sample using an actual database name and table name, login and password. They can be fictional, of course. And what I'm trying for the host is "localhost:/tmp/mysql5.sock" . The database is on the same server as the website.
Tearose49

Hi,
by viewing the source code of a page you will not be able to see the PHP that generated the page, only the result of what the PHP did.

Where are you having the problem? Is it retrieving the data from the database, or displaying it in the table?


Zagga

.I think it's not connecting to the database, as we added a couple echo statements that should tell me if if got to the db, then to the table. It doesn't print them, so I guess that's the first problem.

Would you mind posting said code here for review?

We are happy to help, but we are not here to spoon feed you code.

You will learn more from having mistakes corrected than just being told the answer.

Thanks. I've got a busy weekend, so I might not get to it till Monday.
Tearose49

Would you mind posting said code here for review?
We are happy to help, but we are not here to spoon feed you code.
You will learn more from having mistakes corrected than just being told the answer.

OK, I've changed a couple names on the page so I'm not using the actual password, etc. This is what I put together using a couple samples I found on the web. The only thing that actually shows up is the header line of the table, so I know it's not getting to the database. That may just be what I put in for localhost, but I'd like to know if I've done other things wrong as well. I do know that the phpMySQL Admin on the webhosting site shows the database"garden-Fence" and it contains the table "ABC roses".

<html>

<head>
<title>program testing page</title>


</head>

<body>


<?php
$username="garden";
$password="10625983";
$database="garden_Fence";

mysql_connect("localhost:/tmp/mysql5.sock","garden","10625983")or die(mysql_error());
echo "Connected to MySQL";

mysql_select_db("garden_Fence") or die( "Unable to select database");
echo "Connected to Database";


$query="SELECT * FROM ABC Roses";

$result = mysql_query($query) or die(mysql_error());

$num = mysql_numrows($result);

mysql_close();
?>

<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th>LOCATION</font></th>
<th>ROSE</font></th>
<th>YEAR</font></th>
<th>CLASS</font></th>
<th>HYBRIDIZER</font></th>
</tr>

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

$f1=mysql_result($result,$i,"Location");
$f2=mysql_result($result,$i,"Rose");
$f3=mysql_result($result,$i,"Class");
$f4=mysql_result($result,$i,"Year");
$f5=mysql_result($result,$i,"Breeder");
?>

<tr>

<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo $f5; ?></td>
</tr>

<?php
$i++;
}
?>
</table>

</body></html>

I have made some progress, and the echo statements show it's connecting to the database. There is an error in this line:

$num = mysql_numrows($result);

The message is:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in ....

A friend sent me this sample, but I'm not sure if that's what I need, or how to incorporate it into what I've got.

$query = "SELECT * FROM test"; 

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}
?>

It needs some way of figuring out how many rows there are in the table, so it know when it gets to the end.

Member Avatar for Zagga

Hi Tearose49,

Try changing the query statement from:

$query="SELECT * FROM ABC Roses";

to:

$query="SELECT * FROM ABC";

if you want to select everything from the table ABC,
or:

$query="SELECT * FROM ABC WHERE MyColumn='MyRoses'";

if you want to select everything that matches MyRoses in MyColumn.


The WHILE statement you use will only perform the echo WHILE it's condition is true (while there are any rows with data in them), so you don't need to worry about counting the rows.

Zagga

Zagga, you are confusing me. The table name is 'ABC roses'. Is there a problem about it being more than one word? The names of the columns I want to display are defined above as f1 through f5. There are actually 10 fields in the table, but I only want to display 5 of them on the webpage. Thanks,
Tearose49

Member Avatar for Zagga

Hi again Tearose49,

MySQL will let you create a database with multi-word tables but PHP doesn't like it, so you could change your table to ABC_Roses, then try the following code:

<html>
<head>
<title>program testing page</title>
</head>
<body>

<?php
$username="garden";
$password="10625983";
$database="garden_Fence";

mysql_connect("localhost:/tmp/mysql5.sock",$username,$password)or die(mysql_error());
echo "Connected to MySQL";

mysql_select_db($database) or die( "Unable to select database");
echo "Connected to Database";

$query="SELECT * FROM ABC_Roses";

$result = mysql_query($query) or die(mysql_error());

mysql_close();
?>

<table border="1" cellspacing="2" cellpadding="2">
<tr>
<th>LOCATION</font></th>
<th>ROSE</font></th>
<th>YEAR</font></th>
<th>CLASS</font></th>
<th>HYBRIDIZER</font></th>
</tr>

<?php
while ($row = mysql_fetch_array($result)){
	echo "<tr>";
	echo "<td>" . $row['Location'] . "</td>";
	echo "<td>" . $row['Rose'] . "</td>";
	echo "<td>" . $row['Class'] . "</td>";
	echo "<td>" . $row['Year'] . "</td>";
	echo "<td>" . $row['Breeder'] . "</td>";
	echo "</tr>";
}
?>

</table>

</body>
</html>

Zagga
p.s. Your table headings don't match the database columns (the Class will be displayed in the Year field, the Year will be displayed in the Class field and the Breeder will be displayed in the Hybridizer field).

THANK YOU ZAGGA- IT WORKS!!!!

With luck, I can get the rest of the page ready to go public before the roses are in full bloom.

But I have one more question- Currently, if someone were to view the source, they could see my login information. How can I hide that?
Thanks,
Tearose49

Member Avatar for Zagga

Hi again,

The easiest way is to create a seperate PHP page that only holds your database login details, no HTML.
For example:

<?php
$host="localhost";
$username="myusername";
$password="mypassword";
?>

You can then include this page in your main page (where you currently have your login details):

Include ("dbconfig.php");

This will safely include your login details without them being able to be seen in your source code.

Zagga

Yes, that works. No php in the source code. Thank you again, Zagga

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.