Need working example MySQL database display

Question

tearose49 0 Newbie Poster

16 Years Ago

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

mysql php web-design

3 Contributors
12 Replies
119 Views
6 Days Discussion Span
Latest Post 16 Years Ago Latest Post by tearose49

Dani AI

Generated 10 Months Ago

A few quick, practical points that resolve the common problems seen in this thread (query failing, mysql_numrows() warnings, credentials exposed), plus a short modern example.

The symptom you saw — a warning that mysql_numrows() received an invalid resource — means the SQL query failed and returned false. Common causes are: a SQL syntax error (multi-word table names need quoting), wrong column names, or a permissions/host/socket mismatch. @Zagga’s advice to avoid spaces in table names is sound; alternatively you can quote the identifier. Also confirm the page is served as PHP (saved with .php) and that the DB user has rights to the database.

Modern, safer approach (use PDO and prepared queries; store credentials outside the web root). Example pattern:

// dbconfig.php (store this outside webroot)
$dsn = 'mysql:host=127.0.0.1;dbname=garden_Fence;charset=utf8mb4';
$pdo = new PDO($dsn, 'garden', 'secret', [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);

$stmt = $pdo->query("SELECT `Location`,`Rose`,`Year`,`Class`,`Breeder` FROM `ABC roses`");
foreach ($stmt as $row) {
    echo '<tr>';
    echo '<td>'.htmlspecialchars($row['Location']).'</td>';
    // ...
    echo '</tr>';
}

Troubleshooting checklist

Check the actual SQL error: turn on exceptions (PDO) or echo mysql_error() during development.
Try host 127.0.0.1 instead of localhost to force TCP if the socket path is wrong.
Verify exact table and column names (case can matter on Linux).
Hide credentials by including a config file stored outside the webroot and set strict file permissions (600).
Migrate off deprecated mysql_* functions to PDO or MySQLi — PHP 7+ removes them.

These steps address the root causes raised by and build on @Zagga’s suggestions while improving security and future compatibility.

All 12 Replies

Member #671080

16 Years Ago

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

Will Gresham 81 Master Poster

16 Years Ago

.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.

Member #671080

16 Years Ago

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

Reply to this topic

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.

tearose49 0 Newbie Poster · Answer 1 · 2010-03-27T00:40:46+00:00

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

tearose49 0 Newbie Poster · Answer 2 · 2010-03-27T05:16:19+00:00

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

tearose49 0 Newbie Poster · Answer 3 · 2010-03-29T23:28:24+00:00

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>

tearose49 0 Newbie Poster · Answer 4 · 2010-03-30T06:04:46+00:00

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 #671080 · Answer 5 · 2010-03-30T19:13:21+00:00

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

tearose49 0 Newbie Poster · Answer 6 · 2010-03-30T21:35:50+00:00

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 #671080 · Answer 7 · 2010-03-31T06:21:26+00:00

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).

tearose49 0 Newbie Poster · Answer 8 · 2010-03-31T11:00:24+00:00

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

tearose49 0 Newbie Poster · Answer 9 · 2010-03-31T23:34:19+00:00

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

Need working example MySQL database display

Dani AI

Recommended Answers Collapse Answers

All 12 Replies

Recommended Answers