Retrieving urls through MySQL Query using IN keyword

[quischen]

Hello all,

 

I have an SQL query executing inside a PHP page which retrieves a list of urls through the use of the IN keyword.  However, for some reason, one of the urls which is listed in the IN keyword list does not seem to be read into the PHP page.  When I execute the same query from my PHP page inside the database's interface, the url missing is returned.

 

My exact SQL code is as follows:

$sql_kcnc = mysql_query("SELECT count(*) as count, 
			CASE url
			WHEN 'http://kindervision.org/kcnc_the_greatest_save/' THEN '<b class=\"red\">KCNC TGS Page</b>'
			WHEN 'http://kindervision.org/kindiclub/retrieve.php?state=CO&city=Denver' THEN '<b class=\"red\">KCNC Kindclub Quiz Page</b>'
			WHEN 'http://kindervision.org/kindiclub/score.php?state=CO&city=Denver' THEN '<b class=\"red\">KCNC Kindclub Score Page</b>'				                ELSE url
				END AS url
				FROM statTracker
			WHERE 
			url IN('http://kindervision.org/kcnc_the_greatest_save/',
			'http://kindervision.org/kindiclub/retrieve.php?state=CO&city=Denver',
			'http://kindervision.org/kindiclub/score.php?state=CO&city=Denver',
			'http://kindervision.org/wt/wt_retrieve.php?state=CO&city=Denver'
			)
			AND thedate_visited = '$begin_date'
				GROUP BY url
			ORDER BY count DESC") 
			or die(mysql_error());

 

However, the line

'http://kindervision.org/kcnc_the_greatest_save/',

does not seem to return any results when there are rows within the database that contain it.  How can I fix this line so that it returns data like it should?

 

Note: the query above has been shortened due to size and relevancy since I don't think the rest of the query is at fault.

 

Thank you,

 

Quischen

[fenway]

Man, that's ugly looking... you should really do that differently... but you're saying that it's not respecting your IN clause?

[quischen]

Yes.  It is returning results for every url inside the IN clause except for the url

'http://kindervision.org/kcnc_the_greatest_save/'

.

[artacus]

I agree with fenway, that's the ugliest query I've ever seen. Just do your querying in SQL, decide what color it should be in PHP.

 

Anyhow, does it return anything when you do a LIKE with a "%" at the end of your url?

[quischen]

That doesn't seem to work either.  Could it be something wrong with the logic of the way my PHP page is outputting the results of the SQL query?  It is insertting it into a table.  This is the only other problem that I could see having an effect on it.  I know the query is ugly, I do intend to fix it.  It is very much a work in progress.

 

$row_count = 0;  //To keep track of row number 
if (mysql_fetch_assoc($sql_kcnc) > 0) 
{
while($results = mysql_fetch_assoc($sql_kcnc)) 
{
		// Decide which colours to alternate for the rows If Remainder of $row_count divided by 2 == 0. 
	$row_color = (($row_count % 2) == 0) ? $colour_even : $colour_odd; 
	echo '<tr bgcolor="' . $row_color . '"> 
			<td width=\"20\"><p>' .$results['count'] . '</p></td> 
			<td width=\"260\"><p>' .$results['url'] . '</p></td>
  		</tr>'; 

	// Increment the row count 
	$row_count++;
}
}
elseif (mysql_fetch_assoc($sql_kcnc) == 0)
{
		echo '<tr>
				<td colspan=\"2\"><p class=\"red\">No records found</p></td>
			  </tr>';
}
// Free the MySQL resource 
mysql_free_result($sql_kcnc);

[fenway]

First count the records you get back with mysql_num_rows() -- deal with the PHP after.