Mysql database query

[gp1001]

I have a .php file loaded on my server that returns the current dkp for a user.
I am trying to get it to work from within innerspace as a script.

here is the working .php file.
how it would work is you would open up your browser and point it to the site.
www.dkpsite.com/currentdkp.php?name=Playername
and it would return the results for Playername.

<?php
$username="Username";
$password="PASSWORD";
$database="DATABASENAME";

// Make a MySQL Connection
$db = mysql_connect(localhost,$username,$password);
@mysql_select_db($database, $db) or die( "Unable to select database");

$name = stripslashes(strip_tags($_REQUEST['name']));

// Retrieve all the data from the "example" table
$query="SELECT (eqdkp_members.member_earned - eqdkp_members.member_spent) + eqdkp_members.member_adjustment AS member_current, eqdkp_members.member_name FROM eqdkp_members WHERE eqdkp_members.member_name = '$name' GROUP BY eqdkp_members.member_name ORDER BY eqdkp_members.member_name ASC";

//print $query . "\r\n";
//echo mysql_errno($db) . ": " . mysql_error($db). "\n";

// store the record of the "example" table into $row
$result=mysql_query($query) or die('stupid error: '.mysql_error());;
//echo mysql_errno($db) . ": " . mysql_error($db). "\n";
//echo "<pre>";
//var_dump($result);
//echo "</pre>";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){

//echo "<pre>";
//echo mysql_errno($db) . ": " . mysql_error($db). "\n";

// Print out the contents of the entry 

echo "<b>Name:</b> ".$row['member_name'].
"<br><b>DKP:</b> ".$row['member_current'];
}

?>

Here is what I have come up with using the example from the Wiki.

/*
 * Require MySQL module during preprocessing, so the variables can be created as part of script structure
 * instead of using DeclareVariable
*/
#if !${LavishScript:LoadModule[MySQL](exists)}
#error MySQL module for LavishScript required.
#endif

function main()
{
   variable mysql DB
   variable mysqlresult Result
   
if !${DB:Connect[192.168.1.75,dkpbot,PASSWORD,dkpDB(exists)}
   {
      echo Could not connect to MySQL database
      return
   }
   
if !${DB:Query["SELECT (eqdkp_members.member_earned - eqdkp_members.member_spent) + eqdkp_members.member_adjustment AS member_current, eqdkp_members.member_name FROM eqdkp_members WHERE eqdkp_members.member_name = '$name' GROUP BY eqdkp_members.member_name ORDER BY eqdkp_members.member_name ASC",Result](exists)}
  {
     echo Query failed
     return
  }

	
	while ${Result:FetchRow(exists)}
    
  {
     echo Player : ${Result.Getstring['member_name']}
	 echo DKP : ${Result.Getstring['member_current']}
	 
  }    
}

so far i just want to be able to run currentdkp playername and have it echo back the results for playername.

Any help from anyone would be greatly appreciated.

 

[Pygar]

You didn't mention what happens when you run this, but let me wager a guess...

SELECT (eqdkp_members.member_earned - eqdkp_members.member_spent) + eqdkp_members.member_adjustment AS member_current, eqdkp_members.member_name FROM eqdkp_members WHERE eqdkp_members.member_name = '$name' GROUP BY eqdkp_members.member_name ORDER BY eqdkp_members.member_name ASC

I think your query isn't reterning anything, as you have no member_name $name. $name needs to be an IS variable likely.

I would use a sample query that always returns a row, like:
Select 12 as member_current, 'MyName' as member_name from DUAL;

Just select some static values with the query and see if they output.

 

[chemical77]

Since the format of a basic select is 'SELECT <column(s)> FROM <table>', I'd be interested to know what columns are being queried when there's a calculation in there:

(eqdkp_members.member_earned - eqdkp_members.member_spent...

I'm no SQL expert so maybe this is valid, but thought it looked strange.

I had no problems connecting and returning info with simple queries, so maybe try something less complex first.

** - Nevermind, I get it

 

[Pygar]

Yeah, I didn't test, but I'm 99% sure its cause he left the php var for $name in the query string, and that of course isn't working between IS and MySql.

 

[gp1001]

Since the format of a basic select is 'SELECT <column(s)> FROM <table>', I'd be interested to know what columns are being queried when there's a calculation in there:

(eqdkp_members.member_earned - eqdkp_members.member_spent...

I'm no SQL expert so maybe this is valid, but thought it looked strange.

I had no problems connecting and returning info with simple queries, so maybe try something less complex first.

** - Nevermind, I get it

it subtracts spent from earned to get your current dkp.

 

[gp1001]

Yeah, I didn't test, but I'm 99% sure its cause he left the php var for $name in the query string, and that of course isn't working between IS and MySql.

Yeah, I replaced that var $name with my toons name and it still didnt return any results..

 

[gp1001]

You didn't mention what happens when you run this, but let me wager a guess...

SELECT (eqdkp_members.member_earned - eqdkp_members.member_spent) + eqdkp_members.member_adjustment AS member_current, eqdkp_members.member_name FROM eqdkp_members WHERE eqdkp_members.member_name = '$name' GROUP BY eqdkp_members.member_name ORDER BY eqdkp_members.member_name ASC

I think your query isn't reterning anything, as you have no member_name $name. $name needs to be an IS variable likely.

I would use a sample query that always returns a row, like:
Select 12 as member_current, 'MyName' as member_name from DUAL;

Just select some static values with the query and see if they output.

Its supposed to deduct spent from earned and make that value member_current.
I am a real noob to scripting and I am not sure how to write an array for mysql in IS.


The .php code works flawlessly, I just need to work it out in IS.

Thanks for the replies guys!