Page 1 of 2

query works in phpmyadmin but not from my code[Solved]

Posted: Sun Mar 28, 2010 11:33 am
by KrypticNinja
I hate to clutter up the board with questions like this but I am at my wits end. Unfortunately the host I was using had problems with their servers, their phpmyadmin was crawling to a hault so I found another host.

When I made the switch I ran into a few syntax problems with my mysql statements, probably from the hosts using different versions, but for some reason I'm not able to get a simple login script to work.

here is the query in my code:

SELECT * FROM Players WHERE Name='$nme' AND Password='$pss'

when I echo it out it comes to this:

SELECT * FROM Players WHERE Name='Zer0' AND Password='pass111'

and i get this message:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/a8089435/public_html/Login.php on line 23

Whitch means mysql is returning empty, I think anyway I get the same message if I use this query:

SELECT * FROM Players

and yet, both of those queries produce the desired result set when I send the query in phpmyadmin, any thoughts?

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 12:18 pm
by califdon
My first guess is that you are not connecting to the MySQL server. Do you use " or die(mysql_error());" after your call to mysql_connect()? If the query works in phpMyAdmin, it surely will work from your script if you are connected and there's nothing else wrong.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 1:29 pm
by KrypticNinja
well thats just it I use the same function to send all my queries and other queries work just fine. here is my connection and sending functions:

Code: Select all


function ConDb()
{
 $con = mysql_connect("SERVER", "USER", "PASSWORD");
 if(!$con)
  {
    die('Could not Connect!' . mysql_error());
  }
return $con;
}


function SendQ($query)
{
  $con = ConDb();
  mysql_select_db("a8089435_uni1",$con);
  
  return mysql_query($query,$con) or die(mysql_error());
}

obviously I changed the login info for my server, but as I said I have no problem with other queries, this is the only one giving me problems. For example this query works fine:

SELECT * FROM $sol WHERE ID=$ID

this seems to have no problems, and works exactly the way I need it to. They are on seperate pages, though that shouldn't really matter.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 1:34 pm
by Benjamin
@Kryptic, your code creates a database connection for every query ran using your code. I would modify the following line, so that this no longer happens.

Change

Code: Select all

 $con = mysql_connect("SERVER", "USER", "PASSWORD");
To

Code: Select all

static $con = mysql_connect("SERVER", "USER", "PASSWORD");

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 3:14 pm
by KrypticNinja
Well I added that as you suggested Benjamin, but it gave me a syntax error message. So I don't think thats the problem.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 3:32 pm
by Benjamin
Yeah that won't work. I would call your database connection method once in a file that is included on all pages. Then you can execute your query function as you normally do. mysql_query will use the connection last opened even if it's not specified in the argument list.

As for your queries that are failing, echo the actual query so you can see it. You can add an echo statement to your query function.

Code: Select all

<?php
function ConDb() {
    $con = mysql_connect("SERVER", "USER", "PASSWORD");
    if(!$con) {
        die('Could not Connect!' . mysql_error());
    }

    mysql_select_db("a8089435_uni1",$con);
    return $con;
}


function SendQ($query) {
    return mysql_query($query) or die(mysql_error());
}

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 4:25 pm
by KrypticNinja
I do echo out the query, i have literally copy and pasted the query that was echoed into phpmyadmin, and it worked just fine.

I see your point about the connection though, and I updated my code, thanks for your help with that.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 4:32 pm
by Benjamin
Post your Login.php code please.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 4:34 pm
by KrypticNinja

Code: Select all

<?php 
session_start();
include("Main.php");
Init(); ?>


<HTML>
<link rel="stylesheet" type="text/css" href="Layout.css">
<body>
<center><br><br>
<?php

if(isset($_POST['user']))
{
   $nme = $_POST['user'];
   $pss = $_POST['pass'];

   $qry = "SELECT * FROM Players WHERE Name='$nme' AND Password='$pss'";

   echo $qry . "<br>";

   $res = SendQ($qry);
   
   echo $res['User_ID'] . "<BR>";

     if(mysql_num_rows($res) >0)
     {
          echo "<table class='styled'><td>Login Successful, You will be redirected to your Main Planet. . . </td></table>";
          LoadUser($res);

          echo "<script language=\"javascript\" type=\"text/javascript\">
          <!--
          window.setTimeout('top.location=\"Frames.html\"; ',3000);
          // -->
          </script>";
     }
     else
     {
         echo "<table class='styled'><td>Username and/or Password not found!!!</td></table>";
     }
}



?>


</body></HTML>

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 4:41 pm
by Benjamin
Add this snippet to your code and post the results please. I want to see the query and the return value of the SendQ function.

Code: Select all

   $qry = "SELECT * FROM Players WHERE Name='$nme' AND Password='$pss'";

   echo $qry . "<br>";

   $res = SendQ($qry);
   echo '<pre>', var_dump($res), '</pre>';
   echo $res['User_ID'] . "<BR>";

     if(mysql_num_rows($res) >0)

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 4:44 pm
by KrypticNinja
looks like it prints out: bool(true)

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 5:01 pm
by Benjamin
SendQ is returning true, which means the code in it is different than the code that you posted. It should be returning the result resource from mysql_query. Also, I'm not sure why you are trying to echo $res['User_Id']. A result resource is not an array nor does it contain any data returned by the query.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 5:11 pm
by KrypticNinja
I know, I thought maybe since it was just the one record it might return an array instead. obviously i was wrong, but I've been testing different theories since last night, I ran out of practical ones after an hour or two ha ha.

Heres my Main.php file, you'll have to excuse the mess, ha ha. Structure was never my strong point.

Code: Select all

<?php
error_reporting(E_ALL);
include("Player.php");

function Init()
{
set_error_handler("ErrHandle", E_ALL);
ConDb();

}

//#################
//
// Author: Michael Cline
//
//############################



//

function ConDb()
{
  $con = mysql_connect("mysql4.000webhost.com", "a8089435_zero", "pass111");
 if(!$con)
  {
    die('Could not Connect!' . mysql_error());
  }
  mysql_select_db("a8089435_uni1",$con);
  return $con;
}



function ErrHandle($errnum, $errmsg)
{
echo "<b>Error:</b> [$errnum]  $errmsg<br />";
echo "Ending Script. . . ";
die();
}

function Update($Table, $Field, $ID, $Value)
{
SendQ("UPDATE {$Table} SET $Field = $Value WHERE ID = $ID");
}


//##########################
//
//    Begin Create Test Uni
//
//##########################

function CreateTestUni()
{//1
//$con = ConDb();
//mysql_select_db("bbprototype_zzl_uni1",ConDb());

$Num = 0;
$i = 0;

while($Num<3)
{//2
  while($i<3)
  {//3

$SolSys = "Sol" . FixNum($Num) . FixNum($i);

$tbl = "CREATE TABLE $SolSys(ID INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID),Name VARCHAR(30),Owner VARCHAR(15),Metal BIGINT,Crystal BIGINT,Power BIGINT,Buildings TEXT,Ships TEXT,bStart TIME,sBegin TIME,BuildQ TEXT,ShipQ TEXT)";

//mysql_select_db("bbprototype_zzl_uni1", ConDb());
//mysql_query($tbl, $con);
SendQ($tbl);

AddPlanets($SolSys);
   $i++;
  }//3
  $Num++;
  $i=0;
}//2
}//1

function AddPlanets($Sol)
{
$n = 0;


while($n<10)
{
$nme = "Planet " . $n;
$blnk = '';
$qry = "INSERT INTO $Sol (Name) VALUES('$nme')";

SendQ($qry);
$n++;
}
}

//#####################
//
// Finished Creating Uni
//
//#######################



//########################
//
//  General Functions
//
//#########################



function PlanetBox()
{
$arr = explode(":",$_SESSION['uPlanets']);
   echo "<form action='Planet.php' method='post' target='Main'> <select name='pNum'>";
$z = 0;
while($z < count($arr))
{
 $var = "P" . $z;
 $c = explode("-",$_SESSION[$var]);
 echo "<option value=" . $z . "> " . GetName($c[0],$c[1],$c[2]) . "</option>";
$z++;
}
  echo "</select>";
   echo "<input type='submit' value='Change' class='button'> ";
}

function mes($qry)
{
 return mysql_real_escape_string($qry);
}

function SendQ($query)
{
  return mysql_query($query) or die(mysql_error());
}


function GetShips()
{
   $qry = SendQ("SELECT * FROM Ships");
   
   return mysql_num_rows($qry);
}

function GetFleet($Gal,$Sol,$pos)
{
$tbl = "Sol" . $Gal . $Sol;
$pos = intval($pos);
$qry = "SELECT ID,Name FROM $tbl WHERE ID=$pos";

$Rec = SendQ($qry);

return $Rec[1];
}

function PlanetCount($Gal,$Sol)
{
  $tbl = "Sol" . $Gal . $Sol;
  $qry = "SELECT * FROM {$tbl}";
  $Recs = SendQ($qry);
  return mysql_num_rows($Recs);
}

//##############################
//
//   Galactic Navigation Code
//
//##############################


function DisplaySol($galaxy, $sol)
{

echo "<center><table class='styled'>";
$_SESSION['cGal'] = $galaxy;
$_SESSION['cSol'] = $sol;

$tbl =  "Sol" . FixNum($galaxy) . FixNum($sol);

echo "<tr><td id='hed' colspan='2'><center>" . FixNum($galaxy) . " >< " . FixNum($sol) . "</td></tr>";
echo "<tr><td> Planet Name </td><td> Owner </td>";

$gal = SendQ("SELECT * FROM $tbl");

while($result = mysql_fetch_array($gal)){

echo "<tr><td> ";
echo "<img src=planets/planet.png width=25 height=25>      ";
echo $result['Name'];
echo "</a></td><td>";
echo $result['Owner'];
echo "</td></tr>";

}
echo "</table>";
}



function DisplayTrans()
{
echo "</table>";
}


function FixNum($num)
{
if($num < 10){
$ret = "00" . intval($num);
return $ret;
}
elseif($num > 10 && $num < 100){
$ret = "0" . intval($num);
return $ret;
}
}


//####################
//
//   End NavCode
//
//####################

function GetName($gal, $sol, $planet)
{

$tbl =  "Sol" . FixNum($gal) . FixNum($sol);

$qry = "SELECT ID,Name FROM $tbl WHERE ID=$planet";

$nme = SendQ($qry);
$row = mysql_fetch_row($nme);

return $row[1];
}




//###################### Testing Code

function GetLF($Table, $pos)
{
 $qry = "SELECT * FROM $Table WHERE ID=$pos";
 $Rec = SendQ($qry);
 $row = mysql_fetch_assoc($Rec);
 //echo $Rec[7];
 $ships = explode(",",$row['Ships']);
 $i = 0;
    while($i<=count($ships))
    {
      if(isset($ships[$i]))
      {
         $tmp = explode(":",$ships[$i]);
         if($tmp[0] == "LF")
         {
            return intval($tmp[1]);
         }
      }
      $i++;
    }
}


// Updates Ship Table on specified planet
function UpdateShips($Table,$NewShips, $ID)
{
   $qry = "UPDATE LOW_PRIORITY $Table SET Ships='$NewShips' WHERE ID=$ID";
    echo $qry;
   return SendQ($qry);
}


// Turns Array of Shipinfo LF:### back into string
function ReWriteShips($Ships)
{
 $x = 0;
 $tmp = '';
   while($x<count($Ships))
   {
       if($Ships[$x] != '')
            $tmp = $tmp . $Ships[$x] . ",";
       $x++;
   }
   
   return $tmp;
}



function AddToQueue($shCode, $shNum, $Table, $pID)
{
    $shNfo = SendQ("SELECT * FROM $Table WHERE ID=$pID");
    $shRec = mysql_fetch_assoc($shNfo);
    if(strlen($shRec['Buildings']) > 0)
    {
       $tmp = $shRec['Buildings'] . $shCode . ":" . $shNum . ",";
       Update($Table,'Buildings', $pID, $tmp);

    }
    else
    {
       $tmp = $shCode . ":" . $shNum . ",";
       Update($Table,'Buildings', $pID, $tmp);
       Update($Table,'sBegin', $pID, 'NOW()');
    }
}

?>

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 5:18 pm
by Benjamin
If the SendQ function consists of the following code:

Code: Select all

function SendQ($query)
{
  return mysql_query($query) or die(mysql_error());
}
And Login.php calls it with the following code:

Code: Select all

   $qry = "SELECT * FROM Players WHERE Name='$nme' AND Password='$pss'";

   echo $qry . "<br>";

   $res = SendQ($qry);
Then the value of $res would not be bool(true).

Per the manual, mysql_query returns the following:

[text]Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query. [/text]

Therefore I suspect the code being executed is different then the code you are posting.

Re: query works in phpmyadmin but not from my code

Posted: Sun Mar 28, 2010 5:26 pm
by KrypticNinja
but thats the exact code I'm running. you can see why I'm a little frustrated.

Maybe it's just something with my computer or browser.

Index Page

The login is user: Zer0 or Admin password: pass111

I double checked the code saved on the host, and it's exactly as I've posted.