SQL Query to PHP

[blakogre]

I know very little about PHP, but I've got the query I need, and want the display output into a web page. I've already got PHP set up on IIS.

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--](
SELECT T1.username AS member_name, sum( T3.member_earned + T3.member_adjustment - T3.member_spent ) AS member_sumdkp
FROM eqdkp_users T1, eqdkp_member_user T2, eqdkp_members T3
WHERE T1.user_id = T2.user_id
AND T2.member_id = T3.member_id
GROUP BY T1.username
)
UNION (

SELECT T5.member_name AS member_name, sum( T5.member_earned + T5.member_adjustment - T5.member_spent ) AS member_sumdkp
FROM eqdkp_member_user T4, eqdkp_members T5
WHERE T4.user_id = NULL
AND T4.member_id = T5.member_id
GROUP BY T4.member_id
)
ORDER BY member_sumdkp DESC[/quote]

I put it in the page:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]<?php

define('EQDKP_INC', true);
$eqdkp_root_path = './';
include_once($eqdkp_root_path . 'common.php');

// Formulate Query
// This is the best way to perform a SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("(
SELECT T1.username AS member_name, sum( T3.member_earned + T3.member_adjustment - T3.member_spent ) AS member_sumdkp
FROM eqdkp_users T1, eqdkp_member_user T2, eqdkp_members T3
WHERE T1.user_id = T2.user_id
AND T2.member_id = T3.member_id
GROUP BY T1.username
)
UNION (

SELECT T5.member_name AS member_name, sum( T5.member_earned + T5.member_adjustment - T5.member_spent ) AS member_sumdkp
FROM eqdkp_member_user T4, eqdkp_members T5
WHERE T4.user_id = NULL
AND T4.member_id = T5.member_id
GROUP BY T4.member_id
)
ORDER BY member_sumdkp DESC",

// Perform Query
[b]$result = mysql_query($query);[/b]

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
echo $row['member_name'];
echo $row['member_sumdkp'];

}

// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);
?>[/quote]

I tweaked syntax I got from another page.

I get this error:
Parse error: parse error, unexpected ';' in C:\Inetpub\boc\epix\altdkp.php on line 28

I bolded line 28 for easy reading. I am an utter PHP noob. I'm not sure if the sql query will even work in PHP. It works fine when executed in mysql admin tools

Any help appreciated. I'm simply trying to take the 2 columsn returned, and output them to a php page.

[.josh]

look at your line above line 28. you ended the line with a comma, not a semicolon.

[Buyocat]

It looks like you don't close the query string:

[code]
SELECT T5.member_name AS member_name, sum( T5.member_earned + T5.member_adjustment - T5.member_spent ) AS member_sumdkp
FROM eqdkp_member_user T4, eqdkp_members T5
WHERE T4.user_id = NULL
AND T4.member_id = T5.member_id
GROUP BY T4.member_id
)
ORDER BY member_sumdkp DESC", <<<<<<<<<<<
[/code]
The " is closed by then followed by a , not ; so that might be the error. Give that a try if it doesn't work and there is an error in the MySQL then add this to your $result = statement. In fact, regardless you should add something...

If that doesn't solve anything make the $result = this:
[code]
$result = mysql_query ($query) or die (mysql_error());
[/code]
That will print out what the MySQL error was. Otherwise if everything works make the $result = this:
[code]
$result = @mysql_query ($query);
[/code]
I believe the @ suppresses MySQL errors, so that strangers can't read the error messages.

[.josh]

p.s.- as far as i know, you can run any sql command through php. all php does is take the query string and send it to mysql or whatever db you are using. php itself doesn't process the query string, the db does.

[blakogre]

Found the typo:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]$query = sprintf("(
SELECT T1.username AS member_name, sum( T3.member_earned + T3.member_adjustment - T3.member_spent ) AS member_sumdkp
FROM eqdkp_users T1, eqdkp_member_user T2, eqdkp_members T3
WHERE T1.user_id = T2.user_id
AND T2.member_id = T3.member_id
GROUP BY T1.username
)
UNION (

SELECT T5.member_name AS member_name, sum( T5.member_earned + T5.member_adjustment - T5.member_spent ) AS member_sumdkp
FROM eqdkp_member_user T4, eqdkp_members T5
WHERE T4.user_id = NULL
AND T4.member_id = T5.member_id
GROUP BY T4.member_id
)
ORDER BY member_sumdkp DESC");[/quote]

Didn't finish off $query right.

Now: How can I format the results? I added a <br> after the echo $row with no effect but an error.

[!--quoteo(post=386547:date=Jun 21 2006, 02:22 PM:name=blakogre)--][div class=\'quotetop\']QUOTE(blakogre @ Jun 21 2006, 02:22 PM) [snapback]386547[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Found the typo:
Didn't finish off $query right.

Now: How can I format the results? I added a <br> after the echo $row with no effect but an error.
[/quote]

You guys are quick! :-)

I did some troubleshooting/poking it around and found the error. I just want to clean up the output now.

It's at "http://www.blakogre.com/epix/altdkp.php"

If I need to rework how I query and output, I can do that, let me know. Just remember i'm nub.

[Buyocat]

Ok so you've got the while loop going through result rows, I suggest something like this:
for just line breaks this will suffice...
echo $row['member_name'] . '<br/>';

You need the period and the quotes around the <br/>.
If you want it to be a little fancier, like a table with the first printed value in a row and the second one in another then try:
echo '<table>'
while ...
echo '<tr>';
echo '<td>' . $row[1] . '</td>';
echo '<td>' . $row[2] . '</td>';
etc etc
echo '</tr>';
...
echo '</table>';

I am just using the $row[1] etc as placeholders, be sure to put your own appropriate values in. Also keep in mind the </table> tag comes after the while loop is completed.

[blakogre]

[!--quoteo(post=386549:date=Jun 21 2006, 02:29 PM:name=Buyocat)--][div class=\'quotetop\']QUOTE(Buyocat @ Jun 21 2006, 02:29 PM) [snapback]386549[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Ok so you've got the while loop going through result rows, I suggest something like this:
for just line breaks this will suffice...
echo $row['member_name'] . '<br/>';

You need the period and the quotes around the <br/>.
If you want it to be a little fancier, like a table with the first printed value in a row and the second one in another then try:
echo '<table>'
while ...
echo '<tr>';
echo '<td>' . $row[1] . '</td>';
echo '<td>' . $row[2] . '</td>';
etc etc
echo '</tr>';
...
echo '</table>';

I am just using the $row[1] etc as placeholders, be sure to put your own appropriate values in. Also keep in mind the </table> tag comes after the while loop is completed.
[/quote]

Got it: tossed in the <br> and it's readable now. I'll make it pretty later, but it functions!

Thanks so much for the quick and accurate assistance!!!

[blakogre]

Tossed in your code, tweaked it, results are now:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--] echo '<div align="center"><table bordercolorlight="#000000" bordercolordark="#C0C0C0" border="1">';
echo '<tr><td width="115"><font face="Verdana" size="1"><b>Member</b></font></td><td><font face="Verdana" size="1"><b>DKP</b></font></td>';
while ($row = mysql_fetch_assoc($result)) {


echo '<b><tr><td width="115"><font face="Verdana" size="1">'.$row['member_name']. '</font></b></td>';
echo '<b><td width="50"><font face="Verdana" size="1">'.$row['member_sumdkp']. '</font></b></td></tr>';

}
echo '</table>';[/quote]

Added page/site header info. Needs a few more links, but done. again, th anks.