displaying mysql query results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DaGoodGames
Forum Newbie
Posts: 6
Joined: Fri Mar 17, 2006 8:59 am

displaying mysql query results

Post by DaGoodGames »

feyd | Please use

Code: Select all

and

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

and

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]
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Post 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
}
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

feyd | Please use

Code: Select all

and

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

and

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]
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post 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]
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post 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
DaGoodGames
Forum Newbie
Posts: 6
Joined: Fri Mar 17, 2006 8:59 am

Post 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:

Code: Select all

No database selected
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
User avatar
ol4pr0
Forum Regular
Posts: 926
Joined: Thu Jan 08, 2004 11:22 am
Location: ecuador

Post 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
     }
}
DaGoodGames
Forum Newbie
Posts: 6
Joined: Fri Mar 17, 2006 8:59 am

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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);
}
?>
DaGoodGames
Forum Newbie
Posts: 6
Joined: Fri Mar 17, 2006 8:59 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. 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.
  2. 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.
  3. 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...
DaGoodGames
Forum Newbie
Posts: 6
Joined: Fri Mar 17, 2006 8:59 am

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