Page 1 of 1
displaying mysql query results
Posted: Fri Mar 17, 2006 9:03 am
by DaGoodGames
feyd | Please use Code: Select all
tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello,
I've gotten PHP to connect to a mySQL database successfully. But now I'm running into trouble. I'm trying to display the results of a query on a webpage. In the db, I've got a table named member. I've tried the following code inside my php page, but it doesn't work:
Code: Select all
$query="SELECT * FROM member";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br/><br/>";
$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"mnum");
$last=mysql_result($result,$i,"mname");
$phone=mysql_result($result,$i,"mbday");
$mobile=mysql_result($result,$i,"password");
$fax=mysql_result($result,$i,"description");
echo "<b>$first $last</b><br/>Phone: $phone<br/>Mobile: $mobile<br/>Fax: $fax<br/><hr/><br/>";
$i++;
It results in 5 of the following error messages (which are only different in that the page numbers align to the different mysql_results above):
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/da...etc
Any suggestions for what to try?
Thanks,
DaGoodGames
feyd | Please use Code: Select all
tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Fri Mar 17, 2006 9:17 am
by fastfingertips
Better make:
If you have more then one
Code: Select all
$i = 0;
$arrUser = array();
if(mysql_num_rows($result)) {
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$arrUser['mnum'] = $row['mnum'];
$arrUser['mname'] = $row['mname'];
# Add here the rest of the columns
$i++;
}
}
If you have just one
Code: Select all
if(mysql_num_rows($result)) {
$row = mysql_fetch_row($result);
$first = $row[0];
$last = $row[1];
# Add here the rest
}
Posted: Fri Mar 17, 2006 9:18 am
by rubberjohn
feyd | Please use Code: Select all
tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
only quick but it should be something like this assuming it is just for one row:
Code: Select all
$query=mysql_query("SELECT * FROM member")or die (mysql_error());
$num=mysql_num_rows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br/><br/>";
if($num > 0){
$result = mysql_fetch_array($query, MYSQL_ASSOC);
$first=$result['mnum'];
$last=$result['mname'];
$phone=$result['mbday'];
$mobile=$result['password'];
$fax=$result['description'];
echo "<b>$first $last</b><br/>Phone: $phone<br/>Mobile: $mobile<br/>Fax: $fax<br/><hr/><br/>";
}else{
echo "do something here if the query returns nothing";
}
rj
feyd | Please use Code: Select all
tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Fri Mar 17, 2006 9:30 am
by ol4pr0
only quick but it should be something like this assuming it is just for one row:
With your current query you will retrieve all rows (SELECT *(all) FROM member)
To retrieve only one row you query should look something like the following
Code: Select all
$query=mysql_query("SELECT * FROM member WHERE mnum='jack'")or die (mysql_error());
Code: Select all
echo '<table style="width:100px;text-decoration:Arial;">';
echo '<tr>';
while($row = mysql_fetch_array($result))
{
echo '<td style="width:50%;">';
echo $row['mnum'];
echo '</td>';
echo '<td style="width:50%;">';
echo $row['mname'];
echo '</td>';
}
echo '</tr>';
echo '</table>';
While PHP.net
MySql Functions PHP.net
That should do the thing

[/url][/quote]
Posted: Fri Mar 17, 2006 9:36 am
by rubberjohn
i was in the middle of another post when i put that up - it was just something to point him in the right direction
rj
Posted: Fri Mar 17, 2006 12:44 pm
by DaGoodGames
I tried both codes and ran into trouble with both of them. For Fastfingertips' code, I got the following error message:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/da...etc.
For Rubberjohn's, I got something different:
I'm not sure which problem is easier to debug, but I'll go with the Rubberjohn version, because no database selected sounds like a big problem : ) Below is the code (with the username and password changed):
Code: Select all
<?php // Script 2.3 - variables.php
$user = "username";
$host = "localhost";
$password = "*******";
$database = "dagoodga_dagoodstuff";
mysql_connect($host,$user,$password);
mysql_select_db($database);
$query=mysql_query("SELECT * FROM member")or die (mysql_error());
$num=mysql_num_rows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br/><br/>";
if($num > 0){
$result = mysql_fetch_array($query, MYSQL_ASSOC);
$first=$result['mnum'];
$last=$result['mname'];
$phone=$result['mbday'];
$mobile=$result['password'];
$fax=$result['description'];
echo "<b>$first $last</b><br/>Phone: $phone<br/>Mobile: $mobile<br/>Fax: $fax<br/><hr/><br/>";
}else{
echo "do something here if the query returns nothing";
}
mysql_close();
?>
Does anyone see any obvious mistakes?
Thanks,
DaGoodGames
Posted: Fri Mar 17, 2006 1:38 pm
by ol4pr0
How about the mysql close in the middle of all of it.
You make a connection.
than you make the query.
than you close the connection, and than you are trying to retrieve the rows.
Code: Select all
#$num=mysql_num_rows($result);
#mysql_close();
#echo "<b><center>Database Output</center></b><br/><br/>";
#if($num > 0){
// however try this.
mysql_connect($host,$user,$password) or die (mysql_error());
mysql_select_db($database) or die (mysql_error());
# the following will/should show you all results. use WHERE if you'd like just one row
$query=mysql_query("SELECT * FROM member") or die (mysql_error());
$result = mysql_fetch_array($query);
if ($result) {
while($row = mysql_fetch_array($result)) {
echo $row['mnum']; # and all other rows
}
}
Posted: Fri Mar 17, 2006 2:35 pm
by DaGoodGames
Hmmm ... removed that line and it still gives me the same message saying no database selected ... is there something wrong with my connecting code?
Code: Select all
<?php // Script 2.3 - variables.php
$user = "username";
$host = "localhost";
$password = "*******";
$database = "dagoodga_dagoodstuff";
mysql_connect($host,$user,$password);
mysql_select_db($database);
DaGoodGames
Posted: Fri Mar 17, 2006 5:14 pm
by timvw
You can always do the following:
Code: Select all
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', TRUE);
$db = mysql_connect('host', 'user', 'password');
if (!$db) {
echo "could not connect to database";
} else {
mysql_select_db('database', $db) or die(mysql_error());
// perform other code
mysql_close($db);
}
?>
Posted: Fri Mar 17, 2006 8:11 pm
by DaGoodGames
Thanks. That results in an error message showing the access is denied for the user. I think this means I have the wrong username and password. If so, this may be getting beyond the scope of this particular forum. But I have three questions:
(1) Is there anything else that error message can mean besides I've got the username and/or password incorrect? (the one that says
Access denied for user 'dagoodga_dagoodg'@'localhost' to database 'database'
)
(2) Does it matter where the PHP is located on the server? Like, would it cause problems or not work correctly if it was in the wrong folder (oops, I mean Directory ... I think I just blew my cover and gave away that I'm a window's user - but I don't use windows for a server!)
or (3) Is there anything else you could/should use instead of localhost? Are there some circumstances where that doesn't work and you need something else (or does that always work as long as the code is on the same server as the database?)
Thanks a lot for putting up with all my neebie questions,
DaGoodGames
Posted: Fri Mar 17, 2006 8:19 pm
by feyd
- That means your user doesn't have access rights for that database. You will need to either adjust the rights for the user in your control panel, or ask your host for help on that front.
- php itself? as long as the server knows where it is, it rarely makes a difference where php itself is located. If you're talking about the file, it only matters when you are making reference to file system paths or certain other things that your code, as yet shown, does not use.
- Although rare, I have seen using 127.0.0.1 (the IP version of localhost, basically) can work better for various weird reasons. The circumstances that it fails are very specific as far as I know...
Posted: Fri Mar 17, 2006 8:54 pm
by DaGoodGames
Thanks so much! I figured out the problem with the username and password - I had created the username, but I hadn't assigned it to the database. After that, I went back over the suggestions for displaying query results, and I got a code that worked! I'm so excited! Thanks so much! In case anyone else is have the same problems, here's the complete code (with situation-specific info changed, of course) that worked for me:
Code: Select all
<?php // Script 2.3 - variables.php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', TRUE);
$dbh=mysql_connect ("localhost", "USERNAME", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
if (!$dbh) {
echo "could not connect to database";
} else {
mysql_select_db('DATABASE_NAME', $dbh) or die(mysql_error());
$query="SELECT * FROM someTable"; //or any mySQL query
$result=mysql_query($query);
while($row = mysql_fetch_array($result))
{
echo '<td style="width:50%;">';
echo $row['mnum']; //or any field name instead of mnum
echo '</td>';
echo '<td style="width:50%;">';
echo $row['mname']; //any other field name instead of mname
echo '</td>';
}
mysql_close($dbh);
}
?>
DaGoodGames
(if I knew a way to close this thread, or mark it as "solved" I would, but I don't see any way ...)