Failing MySQL queries off PHP but works within MyPHPadmin - help!

southbeach · Jun 3, 2013

I am running PHP scripts where MYSQL queries are used. A simple query

Code:

SELECT * FROM `mydb`.`users` WHERE `users`.`userCode` = '1111' LIMIT 1;

if ran within PHP it returns 0 rows but works fine if I ran the same query within PHPmyAdmin.

I am not using PDO (not familiar with it) and I am running PHP 5.3 in a Windows box

Any idea as to why this would be?

Thanks!

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

vacunita · Jun 3, 2013

Can you show us the PHP code you are using?

As it stands the best we could do is guess at why its happening.

Perhaps your query in PHP is not exactly the same as the one you are using outside.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

southbeach · Jun 3, 2013

Here is the routine I use to execute the query command

Code:

function DoQuery($sql)
{
/*
	$myFile = "c:/appl/posDebug.txt";
	$fh = fopen($myFile, 'w') or die("can't open file");
	$stringData = "Query String\n".$sql."\n";
	fwrite($fh, $stringData);
*/
	$query = mysql_real_escape_string($sql);

/*	$stringData = "query content is \n".print_r($query)."\n";
	fwrite($fh, $stringData);
*/	
	$trackthis = "insert into queries (`date`, `query`, `user`) values (now(), '%s','%s')";

/*	$stringData = $trackthis."\n";
	fwrite($fh, $stringData);
	fwrite($fh, "User ID Set To: ".$_SESSION['user_id']."\n");
*/
	if(isset($_SESSION['user_id'])) {
		mysql_query(sprintf($trackthis, $query, $_SESSION['user_id'])) or die (mysql_error());
	} else {
		mysql_query(sprintf($trackthis, $query, '0')) or die (mysql_error());
	}
	$Results = mysql_query($sql) or die (mysql_error());				// Run SQL Query

//	fwrite($fh, "Query Results Is:\n".print_r($Results)."\n");
	return $Results; 													// Return Query Results
}

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

feherke · Jun 4, 2013

Hi

[tt]mysql_real_escape_string()[/tt] for the entire statement ? That is a bad idea. It has to be used only on the involved values. For example on that 1111, in case it comes from a variable.

Feherke.
feherke.github.io

jpadie · Jun 4, 2013

agree with feherke generally.

however it looks as though, in this function, you are just storing the whole sql command in a string field. In that case, it would be correct to run the escape function over the whole sql statement being stored.

you use a print_r to output the content of $query. this indicates that $query may be an array (otherwise use echo or print). If so, then the mysql_real_escape_string may not worked as it takes a string as an argument.

so I guess we need to be sure which of the sql queries is failing. the first or the second. add more footprinting to the or die() statements to differentiate and help us out by posting the actual mysql error message and the query.

of course, be sure that the $sql passed as an argument is properly escaped as to the values used.

in this line

Code:

$Results = mysql_query($sql) or die (mysql_error());

the result is a resource handle (or false) and so not capable of being written to a text file. you need to do mysql_fetch_* commands against the resource handle to get meaningful information from it.

personally I don't like the idea of passing a resource handle back from a function but I guess it's not a problem. perhaps better to do this

Code:

$Results = mysql_query($sql);
if(!$Results):
 echo '<pre>';
 echo 'Query was: ' . $sql . "\n";
 echo mysql_error();
 exit;
else:
 $rows = array();
 while ($rows[] = mysql_fetch_assoc($Results): 
 endwhile;
 return $rows;
endif;

feherke · Jun 4, 2013

Hi

Oops, indeed. I see it now. Sorry, the earlier post was written BC ( before coffee [morning]

).

Feherke.
feherke.github.io

vacunita · Jun 4, 2013

Am I the only one that would like to see thew contents of $sql?

Other than that I agree with jpadie in that this:

Code:

$Results = mysql_query($sql) or die (mysql_error());				// Run SQL Query

//	fwrite($fh, "Query Results Is:\n".print_r($Results)."\n");
	return $Results;

Will never produce any results whatsoever.

You need to access the result handle with the appropriate functions to extract the actual results.

For example:

Code:

$rows= mysql_num_rows($Results);
echo "Number of rows returned was: " . $rows;
if($rows > 0)
{
[tab]while($row = mysql_fetch_assoc($Results))
[tab]{
[tab][tab]...
[tab]}
}

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

jpadie · Jun 4, 2013

@phil - the contents of the sql variable are in the first post.

vacunita · Jun 4, 2013

You are of course correct jpadie, though I meant more along the lines of how the OP is assigning that value to the $sql variable.

In any case, if there were any issues with the assignment there'd be an error, as feherke points out: morning coffee first, answering posts second.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

southbeach · Jun 5, 2013

This is the function which calls the function in my original post

Code:

function validKeyCode($usercode) {
	OpenDB($_SESSION['dbname']);
	$sql = "
		SELECT * FROM `".$_SESSION['dbname'].
		"`.`users` WHERE `users`.`userCode` = '" . 
		mysql_real_escape_string($usercode) . "' LIMIT 1;";
	$query = getQuery($sql);
	$data = mysql_fetch_assoc($query);

	if (mysql_num_rows($query) > 0) {
		$X=1;
		if(isset($data['userCode'])) 		{ $_SESSION['user_code'] 		= $data['userCode']; } else { $X =0; }
		if(isset($data['userID'])) 			{ $_SESSION['user_id'] 			= $data['userID']; } else { $X =0; }
		if(isset($data['userName']))		{ $_SESSION['user_name'] 		= $data['userName']; } else { $X =0; }
		if(isset($data['user_level']))		{ $_SESSION['user_level'] 		= $data['userLevel']; } else { $X =0; }
		if(isset($data['userEmail']))		{ $_SESSION['user_email']		= $data['userEmail']; } else { $X =0; }
		if(isset($data['userBranch']))		{ $_SESSION['user_branch']		= $data['userBranch']; } else { $X =0; }
		if(isset($data['userCompany']))		{ $_SESSION['user_company']		= $data['userCompany']; } else { $X =0; }
		if(isset($data['userPhone']))		{ $_SESSION['user_phone']		= $data['userPhone']; } else { $X =0; }
		if(isset($data['userVendorID']))	{ $_SESSION['user_vendID'] 		= $data['userVendorID']; } else { $X =0; }
		if(isset($data['userAddUser']))		{ $_SESSION['user_adduser'] 	= $data['userAddUser']; } else { $X =0; }
		if(isset($data['userEditUser']))	{ $_SESSION['user_edituser']	= $data['userEditUser']; } else { $X =0; }
		if(isset($data['userRremUser']))	{ $_SESSION['user_remuser']		= $data['userRemUser']; } else { $X =0; }
		if(isset($data['userAddCustomer'])) { $_SESSION['user_addcust']		= $data['userAddCustomer']; } else { $X =0; }
		if(isset($data['userEditCustomer'])){ $_SESSION['user_editcust']	= $data['userEditCustomer']; } else { $X =0; }
		if(isset($data['userremCustomer'])) { $_SESSION['user_remcust']		= $data['userRemCustomer']; } else { $X =0; }
		if(isset($data['userAddVendor']))	{ $_SESSION['user_addvend']		= $data['userAddVendor']; } else { $X =0; }
		if(isset($data['userEditVendor']))	{ $_SESSION['user_editvend']	= $data['userEditVendor']; } else { $X =0; }
		if(isset($data['userremVendor'])) 	{ $_SESSION['user_remvend']		= $data['userRemVendor']; } else { $X =0; }
		if(isset($data['userAddProduct'])) 	{ $_SESSION['user_addprod']		= $data['userAddProduct']; } else { $X =0; }
		if(isset($data['userEditProduct'])) { $_SESSION['user_editprod']	= $data['userEditProduct']; } else { $X =0; }
		if(isset($data['userRemProduct'])) 	{ $_SESSION['user_remprod']		= $data['userRemProduct']; } else { $X =0; }
		if(isset($data['userAddPurchase'])) { $_SESSION['user_addpo']		= $data['userAddPurchase']; } else { $X =0; }
		if(isset($data['userEditPurchase'])){ $_SESSION['user_editpo']		= $data['userEditPurchase']; } else { $X =0; }
		if(isset($data['userRemPurchase'])) { $_SESSION['user_rempo']		= $data['userRemPurchase']; } else { $X =0; }
		if(isset($data['userRowsPerPage'])) { $_SESSION['user_rowsperpage'] = $data['userRowsPerPage']; } else { $X =0; }
		return($X);
	} else { return(0); }

}
function DoQuery($sql)
{
/*
	$myFile = "c:/appl/posDebug.txt";
	$fh = fopen($myFile, 'w') or die("can't open file");
	$stringData = "Query String\n".$sql."\n";
	fwrite($fh, $stringData);
*/
	$query = mysql_real_escape_string($sql);

/*	$stringData = "query content is \n".print_r($query)."\n";
	fwrite($fh, $stringData);
*/	
	$trackthis = "insert into queries (`date`, `query`, `user`) values (now(), '%s','%s')";

/*	$stringData = $trackthis."\n";
	fwrite($fh, $stringData);
	fwrite($fh, "User ID Set To: ".$_SESSION['user_id']."\n");
*/
	if(isset($_SESSION['user_id'])) {
		mysql_query(sprintf($trackthis, $query, $_SESSION['user_id'])) or die (mysql_error());
	} else {
		mysql_query(sprintf($trackthis, $query, '0')) or die (mysql_error());
	}
	$Results = mysql_query($sql) or die (mysql_error());				// Run SQL Query

//	fwrite($fh, "Query Results Is:\n".print_r($Results)."\n");
	return $Results; 													// Return Query Results
}

I setup variable string with the SQL command; I then pass this string to the function which runs the SQL query and return the query's handle. If the handle is populated, I set session variables and return value TRUE or 1, otherwise, I return FALSE or 0.

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

jpadie · Jun 6, 2013

i would have thought you'd get an error. in the post above you are calling a function called getQuery(). In the earlier post the function is called doQuery(). if you are calling a function that does not exist you should get a fatal error. if the function does exist (getQuery()) then it may not be doing what you think.

southbeach · Jun 6, 2013

Since my original post I have been trying to move function from one script to another to see if I can get the errors to show since die() is not executing ...

To avoid duplicate functions, I copied and edited the original to this

Code:

function getQuery($sql) {
	$data = mysql_query ($sql) or die ("My sql string set to: ".$sql."<br />".mysql_error());
	return ($data);
}

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

vacunita · Jun 6, 2013

Lets try something simple. Assuming you are sure that your getQuery() function is actually running, try stepping through the code.

The first step, is to check that the query is actually returning something in your getQuery() function.

So try:

Code:

function getQuery($sql) {
[tab][COLOR=#A40000][b]echo "<pre>" . $sql . "</pre>";[/b][/color]
	$data = mysql_query ($sql) or die ("My sql string set to: ".$sql."<br />".mysql_error());
[tab] [COLOR=#A40000]$rows = mysql_num_rows($data);[/color]
[tab] [COLOR=#A40000]echo "THE ROWS RETURNED: " . $rows;[/color]
[tab] [COLOR=#A40000][b]die();[/b][/color]

	return ($data);
}

Just kill it there, and see if something is output from there. If you get a number of rows returned then we know there is data in your result and can move on to where your getQuery() function gets called and step again. Otherwise your query is not returning what you think it should and you would need to take a closer look at it.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

southbeach · Jun 6, 2013

Here is the resulting output

Code:

SELECT * FROM `mypos`.`users` WHERE `users`.`userCode` = '1968' LIMIT 1;
THE ROWS RETURNED: 1

After I removed the table and re-created it!

I must to have a special character within the table name or field(s) that caused the problem ... The functions and queries are working.

Thank you all for your assistance!

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

vacunita · Jun 6, 2013

Glad you got it working.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Failing MySQL queries off PHP but works within MyPHPadmin - help!

southbeach

Programmer

vacunita

Programmer

southbeach

Programmer

feherke

Programmer

jpadie

Technical User

feherke

Programmer

vacunita

Programmer

jpadie

Technical User

vacunita

Programmer

southbeach

Programmer

jpadie

Technical User

southbeach

Programmer

vacunita

Programmer

southbeach

Programmer

vacunita

Programmer

Similar threads

Part and Inventory Search

Sponsor