mysql_query returns no results -- copy and paste does

[rbraunm]

Alright, I'm kind of at a bit of an road-block here.  I've got the following code in place to dynamically display products based on the manufacturer, and it works perfectly fine except for 2 entries that end with an all rights reserved symbol.  Obviously I think that has something to do with it, but anyway, here is the code and an explanation of what I've tried.

 

PHP: 5.2.6

MySQL: 5.0.45

 

//Check for manufacturer in URL
if($_GET['man'] != "")
{
echo '<div id="manlist">';
	echo '<p>Select a product below that is produced by '.$_GET['man'].'.</p>';
	$sql = "SELECT n.vid,
			n.title,
			ctsp.field_company_name_value,
			nr.teaser
			FROM node n
			INNER JOIN content_type_skincare_product ctsp ON ctsp.vid = n.vid
			INNER JOIN node_revisions nr ON nr.vid = ctsp.vid
			WHERE ctsp.field_company_name_value LIKE '%".mysql_real_escape_string($_GET['man'])."%'
			GROUP BY n.title
			ORDER BY n.title";
	$results = mysql_query($sql);

	while($manproduct = mysql_fetch_array($results, MYSQL_ASSOC))
	{
		echo '<div id="uselandingpage">';
			echo '<div id="uselandingpageproduct">';
				$productname = fixoutput($manproduct['title']);
				echo '<a href="skin-care.html?'.$menu.'vid='.$manproduct['vid'].'">'.$productname.'</a>';
			echo '</div>';
		echo '</div>';
	}

echo '</div>';
}

 

Like I said, all variables return the proper results and list the products for the manufacturers except for Ti-Silc® and Z-Silc®.

 

If I add an echo $sql; line in, copy and paste the query into the SQL form in phpMyAdmin, it works fine and returns the proper rows, even with those two manufacturers...  I'm kind of at a loss here, as I've never had a problem remotely like this.  I've built quite a lot of queries and they generally either work or spit back an error for me to resolve.

 

I would assume that MySQL couldn't handle the ® symbols, but the copy and paste works fine... which points me to some issue with the actual PHP.  Any help would be appreciated...

[alexweber15]

try encoding the special characters using utf8_encode/decode functions before sending to mysql

[gffg4574fghsDSGDGKJYM]

If it work in phpmyadmin and don't work in PHP it's a charset issue.

 

Use that right after you login into the database and select it :

 

<?php
$connect = mysql_connect('hostipaddress','username','password') or die ("Unable to connect to the database.");
$base = mysql_select_db('databasename', $connect) or die ("Unable to select the database.");
mysql_query("SET NAMES 'utf8';", $connect) or die ("Unable to set names.");
mysql_query("SET CHARACTER SET 'utf8';", $connect) or die ("Unable to set charset.");
?>

 

Replace hostipaddress, username, password, databasename with your login data and replace UTF8 with the charset you are using.

 

PHP don't know what charset mysql use and if you don't specifi one it use the default, if your database is encoded into another charset it fail just like it did for you. You need to told him what charset it need to use for the connection with SET NAMES and SET CHARACTER SET.

 

Now that you haven't used this before some data in the database can be stored using different charset, and some special char like the copyright can get broke when you will use this.

 

It's always better to use this before entering any value in the database.

 

Html entities can be used to convert some text from one charset to another one and fix this in the database :

 

<?php
   $text = htmlentities($text, ENT_NOQUOTES, 'UTF-8');
   $text = html_entity_decode($text, ENT_NOQUOTES, 'ISO-8859-1');
?>

 

It's better to fix it in the database instead of only fixing the ouput. If you only fix the output you will be able to see it correctly but you will be unable to search (or others action) special characters in the database.

[rbraunm]

Thanks for the replies! I'll give it a try and let you know if it resolves the problem.