[Resolved] [Resolved] query data not displaying

Barb54

Hi,
I'm still working on this one, lol. Ok, I still don't have the "overall" nick totals, but will try that after I get this part. My query isn't displaying and I don't know why 😕 . I am a newbie at both php and mysql so I will share what I have so far.

Thanks for any help in advance.

Error msg I get is : Warning: mysql_result(): supplied argument is not a valid MySQL result resource in addy/detail_scores2.php on lines 27-32 (which are the lines referencing the "mysql_result" code).

<?
include("header.php");
include("dbconnect.php");
?>

<?
//put table fields data in array
$sql = "SELECT nick,gameid,point_type,point_id,SUM(pointamt) AS points,SUM(pointcount) AS count FROM records AS Totals GROUP BY nick, gameid, point_tpe, point_id ORDER BY nick,";

$resultall = MYSQL_QUERY($sql);
?>

Scoreboard </center>
 
<center>
<table width=60% border=0 cellspacing=2 cellpadding=2>
<tr>
<td align=left>NickName</td>
<td align=center>Game</td>
<td align=center>Type</td>
<td align=center>ID</td>
<td align=right>Amt</td>
<td align=right>Count</td>
</tr>

<?
// Retreiving data and putting it in local variables for each row
$nick=mysql_result($resultall,"nick");
$gameid=mysql_result($resultall,"gameid");
$point_type=mysql_result($resultall,"point_type");
$point_id=mysql_result($resultall,"point_id");
$pointamt=mysql_result($resultall,"pointamt");
$pointcount=mysql_result($resultall,"pointcount");

?>
<tr height=30>
<td align=left><? echo $nick; ?></td>
<td align=center><? echo $gameid; ?></td>
<td align=center><? echo $point_type; ?></td>
<td align=center><? echo $point_id; ?></td>
<td align=right><? echo $pointamt; ?></td>
<td align=right><? echo $pointcount; ?></td>
</tr>

<?
include("footer.php");
?>

Can someone tell me what it is I'm doing wrong? I can't look at it anymore, lol. It's probably one of those DUH's lol.

Thanks in advance,
Barb

Sxooter

Anytime you get an error about "not a valid resource" it usually means your query didn't work.

Add a mysql_error($connection); right after the query is fired to see the error.

Barb54

Hi,
I tried the change and error was the same. It doesn't like my "result" query. I'm not sure if I even set it up properly.

This I DO know:
I AM connecting to my db with the dbconnect.php and it has error checking in that file. I use that same connect file for several scripts and it works fine.

Field names ARE correct, unless I have messed with it so long now I have missed something, lol.

So it has to be the query and I am lost when it comes to that. Does it have anything to do with the fact that I am grouping/sorting/totaling some fields? Just not sure here.

Thanks again for your help in advance.

Barb

csn

Use mysql_fetch_array instead of all those mysql_result's. Or better yet, pg_fetch_array() ;P

Barb54

Hi,
I really appreciate the help but it's still not working:

I now have:
Ln 9 $resultall = mysql_fetch_array($sql);

instead of:
Ln 9 $resultall = MYSQL_QUERY($sql);

Error:

Warning: Supplied argument is not a valid MySQL result resource in addy/detail_scores2.php on line 9

and also:
Error:

Warning: Supplied argument is not a valid MySQL result resource in addy/detail_scores2.php on line 28-33 which are

      Ln28  $nick=mysql_result($resultall,"nick"); 
      Ln29  $gameid=mysql_result($resultall,"gameid"); 
      Ln30  $point_type=mysql_result($resultall,"point_type"); 
      Ln31  $point_id=mysql_result($resultall,"point_id"); 
      Ln32  $pointamt=mysql_result($resultall,"pointamt"); 
      Ln33  $pointcount=mysql_result($resultall,"pointcount");

What am I doing wrong with this? lol

Thanks again,
Barb

Sxooter

Have you tried the mysql_error() yet?

Cause I can see your error by looking but it would be nice if you could find it by seeing the mysql error coming out of it.

Barb54

Here is a link to a page that shows how I want this report to look. http://icabgf.net/cb_db/scoreboard.php It is basically an Excel spreadsheet saved as html.

I have to dump the table from mysql then import it into Excel right now. Then I delete columns I don't want, sort, group, get totals with formulas. This is fine except my group is growing and I need information current from the database instead of weekly, which is how often I do the dump now.

Other groups could benefit from this type of report as well. So if I can get this to work, I will share it with some friends that have larger groups than me.

Thanks again,
Barb

Sxooter

Note that the argument for mysql_error is NOT the result handle, but the connection handle. I.e.:

$conn = mysql_connect("paramsgohere");
$res = mysql_query("broken query goes here");
print mysql_error($conn);

Notice the argument for mysql_error.

Sxooter

Originally posted by Barb54
Hi,
I really appreciate the help but it's still not working:

I now have:
Ln 9 $resultall = mysql_fetch_array($sql);

instead of:
Ln 9 $resultall = MYSQL_QUERY($sql);

No, you leave the mysql_query in place, and replace the mysql_results with a single mysql_fetch_array() then just go through the array.

But, have you gotten any output from your mysql_error yet?

Barb54

Thanks Sxooter. I know you are trying to tell me something but I obviously just don't get it, lol.

I made the changes like this:

<?php
include("header.php");
include("dbconnect.php");
?>

$conn = mysql_connect($MySqlHostname, $MySqlUsername, $MySqlPassword);

//put table fields data in array
$sql = mysql_query("SELECT nick,gameid,point_type,point_id,SUM(pointamt) AS points,SUM(pointcount) AS count FROM records AS Totals GROUP BY nick, gameid, point_tpe, point_id ORDER BY nick,");

$resultall = MYSQL_QUERY($sql);

print mysql_error($conn);
?>

<?
// Whats WRONG with these lines?????? lol
//if i put mysql_fetch_array() as first line here QUERY comes up empty
//if I take out all these lines here, and only put the fetch array line, QUERY comes up empty again

//so the problem is a combo of these lines and the query? but what is it? lol

        $nick=mysql_query($resultall,"nick"); 
        $gameid=mysql_query($resultall,"gameid"); 
        $point_type=mysql_query($resultall,"point_type"); 
        $point_id=mysql_query($resultall,"point_id"); 
        $pointamt=mysql_query($resultall,"pointamt"); 
        $pointcount=mysql_query($resultall,"pointcount");

OMG, I have NO clue here lol. Im MELTINGggggggggggg lol!!!!

Barb

This is what I get for being a complete newbie, lol. I'm going back to Crystal Reports lol!

Sxooter

<?php
include("header.php");
include("dbconnect.php"); 
?>

<?

$conn = mysql_connect($MySqlHostname, $MySqlUsername, $MySqlPassword); 

//put table fields data in array

# The error here is the AS TOTALS affer the FROM records.  

$sql = mysql_query("SELECT nick,gameid,point_type,point_id,SUM(pointamt) AS points,SUM(pointcount) AS count FROM records AS Totals GROUP BY nick, gameid, point_tpe, point_id  ORDER BY nick,");  

$resultall = MYSQL_QUERY($sql);

SO, WHAT ERROR, if any are you getting here:

print mysql_error($conn);


// Whats WRONG with these lines?????? lol
//if i put mysql_fetch_array() as first line here QUERY comes up empty
//if I take out all these lines here, and only put the fetch array line, QUERY comes up empty again

//so the problem is a combo of these lines and the query? but what is it? lol

Here's the new way:

$row = mysql_fetch_array($resultall);
print $row['nick'];
print $row['gameid'];

and so on...

            $nick=mysql_query($resultall,"nick"); 
            $gameid=mysql_query($resultall,"gameid"); 
            $point_type=mysql_query($resultall,"point_type"); 
            $point_id=mysql_query($resultall,"point_id"); 
            $pointamt=mysql_query($resultall,"pointamt"); 
            $pointcount=mysql_query($resultall,"pointcount"); 

?>

This is what I get for being a complete newbie, lol. I'm going back to Crystal Reports lol!

Ackkk! no, no one deserves to have to make that beast happy. :-)

icaro

$sql = "SELECT nick,gameid,point_type,point_id,SUM(pointamt) AS points,SUM(pointcount) AS count FROM records AS Totals GROUP BY nick, gameid, point_tpe, point_id ORDER BY nick,";

group by .... point_tpe ??
you selected point_type -> tYpe
one must be wrong
and that coma at end is wrong -> ORDER BY nick,

it must be -> ORDER BY nick

this wrong query makes your 'resource id' to be invalid
try to paste it on your cliente db shell.. when it is surelly right, come back to your php and try again.

Sxooter

Yeah, when you're first starting out, try getting your query working in the monitor / cli program that your database uses (mysql, psql, isql etc...) THEN copy and paste it into your script.