php mysql query problem

[shossain]

Hi,

I am working with a project. I already write a script. The script has no error but problem is i  am not getting my desired output. I have a mysql database which contains id(ing), strname(varchar), lat(float), lng(float), btcount(int), time(datetime). In my table i have multiple time lat and lng in a single address.

What i did first i findout all the strname single time and put it in an array and no of time appair each address.  Then i run a query with strname array for all the lat lng of a single address. Here my am not getting my output. It is showing mysql_num_rows($cordresult) is 0. But it should not be.

[Note: My strname column contains some address which have swidesh character – å, ö and ä]

Here is my output and the script. Can anybody help me to solve this issue??

Output:

Blickagången 4, Huddinge Municipality, Sweden No of time appair: 5
Hanstavägen 53, Stockholm County, Sweden No of time appair: 3
Hanstavägen 57, Stockholm County, Sweden No of time appair: 5

 

The output should be all the lat and lng in same address

 

Blickagången 4, Huddinge Municipality, Sweden
Number of rows effected: 0
Hanstavägen 53, Stockholm County, Sweden
Number of rows effected: 0
Hanstavägen 57, Stockholm County, Sweden
Number of rows effected: 0

 

 

Script:

<?php
require('phpsqlinfo_dbinfo.php');

// Opens a connection to a MySQL server.
$connection = mysql_connect ("localhost", $username, $password) or die('Not connected : ' . mysql_error());; 

// Sets the active MySQL database.
$db_selected = mysql_select_db($database, $connection) or  die ('Can\'t use db : ' . mysql_error());
// Selects all the rows in the traffic table.
$strncquery = 'SELECT `strname` , count( strname ) AS bpcount FROM `traffic` GROUP BY `strname` ;';
$strncresult = mysql_query($strncquery) or die('Invalid strncquery: ' . mysql_error());;

$strnamearray = array();
$bpcountarray = array();
while ($strncrow = @mysql_fetch_assoc($strncresult)) 
{
$strnamearray[] = htmlentities($strncrow['strname']);
$bpcountarray[] =  htmlentities($strncrow['bpcount']);
}
//Debug output---------------------
for($j=0; $j<count($strnamearray); $j++){
echo $strnamearray[$j]."         <b>No of time appair: </b>".$bpcountarray[$j]."<br />";
}

echo "<br /><br /><br /><br /><br />";
echo "The output should be all the lat and lng in same address";
echo "<br /><br /><br />";
for($i=0; $i<count($strnamearray); $i++){
//Debug output---------------------
echo $strnamearray[$i]."<br />";
//This query is not working
$cordcquery = "SELECT `lat` , `lng` FROM `traffic` WHERE `strname` = '{$strnamearray[$i]}';";
$cordresult = mysql_query($cordcquery) or die('Invalid strncquery: ' . mysql_error());;

//Debug output---------------------
echo "<b>Number of rows effected: </b>".mysql_num_rows($cordresult);

while($cordrow = @mysql_fetch_assoc($cordresult)){

	echo $cordrow['lng'].",".$cordrow['lat'];

}

echo "<br />";

}

?>

Database table:

CREATE TABLE IF NOT EXISTS `traffic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `strname` varchar(60) DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lng` float(10,6) DEFAULT NULL,
  `bpoint` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=110 ;

--
-- Dumping data for table `traffic`
--

INSERT INTO `traffic` (`id`, `strname`, `lat`, `lng`, `bpoint`, `time`) VALUES
(104, 'Hanstavägen 57, Stockholm County, Sweden', 59.404354, 17.943651, 1, '2011-05-10 23:43:44'),
(103, 'Hanstavägen 57, Stockholm County, Sweden', 59.404354, 17.943651, 1, '2011-05-10 23:43:42'),
(102, 'Hanstavägen 57, Stockholm County, Sweden', 59.404354, 17.943651, 1, '2011-05-10 23:43:41'),
(101, 'Hanstavägen 57, Stockholm County, Sweden', 59.404354, 17.943651, 1, '2011-05-10 23:43:39'),
(100, 'Hanstavägen 57, Stockholm County, Sweden', 59.404354, 17.943651, 1, '2011-05-10 23:43:37'),
(99, 'Hanstavägen 53, Stockholm County, Sweden', 59.404343, 17.943674, 1, '2011-05-10 23:43:05'),
(98, 'Hanstavägen 53, Stockholm County, Sweden', 59.404369, 17.943638, 1, '2011-05-10 23:41:49'),
(109, 'Blickagången 4, Huddinge Municipality, Sweden', 59.219440, 17.942228, 1, '2011-05-11 22:08:16'),
(108, 'Blickagången 4, Huddinge Municipality, Sweden', 59.219440, 17.942228, 1, '2011-05-11 22:08:14'),
(107, 'Blickagången 4, Huddinge Municipality, Sweden', 59.219440, 17.942228, 1, '2011-05-11 22:08:13'),
(85, 'Hanstavägen 53, Stockholm County, Sweden', 59.404408, 17.943659, 1, '2011-05-10 23:30:16'),
(106, 'Blickagången 4, Huddinge Municipality, Sweden', 59.219440, 17.942228, 1, '2011-05-11 22:08:11'),
(105, 'Blickagången 4, Huddinge Municipality, Sweden', 59.219440, 17.942228, 1, '2011-05-11 22:08:09');

[DavidAM]

 while ($strncrow = @mysql_fetch_assoc($strncresult))
{
   $strnamearray[] = htmlentities($strncrow['strname']);
   $bpcountarray[] =  htmlentities($strncrow['bpcount']);
}

 

You have run htmlentities() on the value from the database and stored it in the array. Then later on:

 

   $cordcquery = "SELECT `lat` , `lng` FROM `traffic` WHERE `strname` = '{$strnamearray[$i]}';";

 

You are using the array value in the query. Since there are Swedish characters in the strname, they have been converted to htmlentities and the value no longer matches the value in the database.

 

Only use htmlentities() when you are send the string to the page. In the script you should always work with the raw value.

[shossain]

thanks.. i got my result.. thanks again...

[fenway]

That's great -- but next time, use code tags.