Query works in phpMyAdmin but doesn't when using php code

[five]

Hi all,

I’ve been trying to get this sql query to work from php, but i cannot seem to get it to. I am hoping that someone else has experienced this and knows a solution or can point out my mistakes.

I have a table in my sql db titled Parent_Child, and as you may imagine is just two IDs associating the two together. The two fields are Parent and Child.

I want to run a query like this:
[php]SELECT * FROM Parent_Child WHERE Child =‘8C8FDBCB-0984-49D8-BE14-1942E26600C4’[/php]

It works when I perform it in phpMyAdmin, but does not when I attempt to run the query using mysql_query.

[php]
//$g is the ID of the current child.
$sql= “SELECT * FROM Parent_Child WHERE Child =’”.$g."’";
$result= mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
echo $row[‘Parent’];
}
[/php]

It’s weird because when i echo $sql, it outputs the query, and when I paste that directly into phpMyAdmin query, it works… If I passed $g as 8C8FDBCB-0984-49D8-BE14-1942E26600C4, it would give me my desired output in the variable $sql as: SELECT * FROM Parent_Child WHERE Child =‘8C8FDBCB-0984-49D8-BE14-1942E26600C4’

note: I have the mysql_connect stuff also but that isn’t the focus of the problem. I know it connects to the DB it just doesn’t execute the query properly.

Any thoughts?

[smokeys_13]

Hi there,

Is it completing the query at all? Above the script put:
[php]error_reporting(E_ALL);[/php]

And change this bit:
[php]$result= mysql_query($sql);
while ($row = mysql_fetch_array($result))[/php]

to:
[php]$result= mysql_query($sql);
if($result === false) echo mysql_error();
while ($row = mysql_fetch_array($result))[/php]

[five]

Hiya,

Just tried that and it doesn’t return anything. It just outputs a blank line. What would be stopping it from completing the query? Here’s the entire function in case its somewhere else that I’m going wrong with what you asked me to do.

[php]
function getParent($g)
{

	error_reporting(E_ALL);
	

 	$con=mysql_connect("localhost", "someuser", "somepass");
	if(!$con)
	{
		die('Error: ' . mysql_error());
	}

	@mysql_select_db("someproject", $con) or die("Unable to connect to database");
	
	$sql= "SELECT * FROM Parent_Child WHERE Child ='".$g."'";
	
	$result=mysql_query($sql);
	if($result === false) echo mysql_error();
	while ($row = mysql_fetch_array($result))
	{
		echo $row['Parent'];
	}

}
[/php]

The output is:
[php]8C8FDBCB-0984-49D8-BE14-1942E26600C4
(and a blank line here)
[/php]

[smokeys_13]

If that’s the output of the code you have posted, then surely the “8C8FDBCB-0984-49D8-BE14-1942E26600C4” is being done by “echo $row[‘Parent’];”

To make sure that the echo is being called try changing that line to say:
[php]echo ‘[’.$row[‘Parent’].’]’;[/php]

Oh and I’m assuming of course that the die() functions didn’t get called and that no errors were displayed after adding error_reporting?

[five]

Oh oops, i accidentally deleted a line from the previous post… so that output is from the echo which is just $g. I printed it previously to help myself debug and see if it was even getting the right variable.

[php]echo $g[/php]

[five]

To make sure that the echo is being called try changing that line to say:
[php]echo ‘[’.$row[‘Parent’].’]’;[/php]

Oh and I’m assuming of course that the die() functions didn’t get called and that no errors were displayed after adding error_reporting?

Changed the line. still no luck. And yeah the die() functions were not called and no errors are displayed.

[smokeys_13]

By still no luck I will assume that you mean that there is no output.

Firstly, try changing the sql to just "SELECT * FROM Parent_Child" (and make sure that this table name is EXACTLY the same as it appears in phpmyadmin - same capitals etc)

Secondly, have you ever managed to get an SQL query running on the server you are using?

[five]

Right, still no output.

I did change the SQL statement to "Select * FROM Parent_Child", and it did return all of the Parent/Child ID’s from the database. I’m sure that I checked on the case sensitivity of them.

And yeah, I’m able to run other SQL queries which makes me believe that something is wrong with my syntax in $sql.

[smokeys_13]

In which case it might have been the use of quotes or maybe the fact that the WHERE clause was left out. As a test try using the same code you started with but use the quotes:
[php]$sql = “SELECT * FROM Parent_Child WHERE Child = '”.$g."’";[/php]

[five]

In which case it might have been the use of quotes or maybe the fact that the WHERE clause was left out. As a test try using the same code you started with but use the quotes:
[php]$sql = “SELECT * FROM Parent_Child WHERE Child = '”.$g."’";[/php]

Still no output :o

I also tried to remove the usage of ` around Child and from Parent_Child and one with `` and the other without. Unfortunately, still no output.

[smokeys_13]

And there is DEFINITELY a row in the table Parent_Child where the field Child is EXACTLY equal to ‘8C8FDBCB-0984-49D8-BE14-1942E26600C4’ ?

This is very strange that this is not working…

Once you have checked that the row I mentioned does exist, and it does, try changing the query to say:
[php]$sql = “SELECT * FROM Parent_Child WHERE Child LIKE ‘%8C8FDBCB%’”;[/php]

And in the loop do:
[php]echo $row[‘Parent’];
echo ‘
’;
echo $row[‘Child’];[/php]

[five]

Once you have checked that the row I mentioned does exist, and it does, try changing the query to say:
[php]$sql = “SELECT * FROM Parent_Child WHERE Child LIKE ‘%8C8FDBCB%’”;[/php]

And in the loop do:
[php]echo $row[‘Parent’];
echo ‘
’;
echo $row[‘Child’];[/php]

Changed the query to what you suggested and it gave me the Parent and Child.
[php]
33BD0276-50E2-4F6D-A5DD-0B588D1DD33F
8C8FDBCB-0984-49D8-BE14-1942E26600C4
[/php]

I also tried taking what you did using LIKE and replacing the = with it, and it gave me no output.
[php]
$sql = “SELECT * FROM Parent_Child WHERE Child LIKE '”.$g."’";[/php]

[smokeys_13]

Okay, I think I know the solution then… fingers crossed!
[php]$sql = “SELECT * FROM Parent_Child WHERE Child LIKE '%”.$g."%’";[/php]

Try that and let me know!

[five]

Nope didn’t work.

[smokeys_13]

Damn, okay…
[php]$sql = “SELECT * FROM Parent_Child WHERE Child LIKE '%”.trim(strtoupper($g))."%’";[/php]

[five]

Damn, okay…
[php]$sql = “SELECT * FROM Parent_Child WHERE Child LIKE '%”.trim(strtoupper($g))."%’";[/php]

This didn’t work either . So I think we’ve narrowed down the issue to being the variable $g causing the issue right? Not sure if this helps at all but both Parent and Child are Varchars with max length 255.

[smokeys_13]

Yea looks like $g is playing games with us. Hmm that shouldn’t make a difference, especially if you say this works if you put the value into phpmyadmin and it works… what if you do this? (we’ll get there eventually!)
[php]$childlookup = trim(strtoupper(str_replace(’-’,’%’,$g))); //So many functions, thought might make the $sql= line a bit too long!
$sql = “SELECT * FROM Parent_Child WHERE Child LIKE '%”.$childlookup."%’";[/php]

[five]

Dang still no luck with this one either…

[smokeys_13]

Things just got stranger… I recreated your table structure locally and ran the same script that you are and all is working fine.

And just to reconfirm, if you literally only delete from “WHERE …” onwards and run the script the same it works and returns all the rows. And as soon as you put the WHERE clause back in it breaks?

If this is the case then you may need to check how you are passing the string to the function.

[five]

Yeah removing everything from WHERE onwards returns all the rows.

I’m calling this getParent method in another method as such:

[php]public function printParent()
{
$this->getParent($this->ID);
}[/php]

Where ID is a public variable declared in this class.
[php]public $ID = ‘’;[/php]