Jump to content


Photo

SQL Query to PHP


  • Please log in to reply
7 replies to this topic

#1 blakogre

blakogre
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 June 2006 - 07:06 PM

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
$result = mysql_query($query);

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



#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 21 June 2006 - 07:13 PM

look at your line above line 28. you ended the line with a comma, not a semicolon.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 21 June 2006 - 07:14 PM

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

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", <<<<<<<<<<<
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:
$result = mysql_query ($query) or die (mysql_error());
That will print out what the MySQL error was. Otherwise if everything works make the $result = this:
$result = @mysql_query ($query);
I believe the @ suppresses MySQL errors, so that strangers can't read the error messages.
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 21 June 2006 - 07:18 PM

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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 blakogre

blakogre
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 June 2006 - 07:23 PM

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) View Post[/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.

#6 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 21 June 2006 - 07:29 PM

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.
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#7 blakogre

blakogre
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 June 2006 - 07:33 PM

[!--quoteo(post=386549:date=Jun 21 2006, 02:29 PM:name=Buyocat)--][div class=\'quotetop\']QUOTE(Buyocat @ Jun 21 2006, 02:29 PM) View Post[/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!!!

#8 blakogre

blakogre
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 June 2006 - 08:40 PM

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users