[SOLVED] help to optimize my query

[deepson2]

Hello there!

 

I just got stucked with my query. this query is not showing any result in MYSQL ,but showing me output on my php page.

 

when i use following query its working absoultly fine

$sql = $op->runsql(
"SELECT b.title,b.blogpath FROM bloginfo AS b, favourite AS f WHERE b.id = f.blogid
AND f.userid =='$blogdata->author'
ORDER BY f.id DESC ");
if(mysql_num_rows($sql) > 0){
while($row1 = $op->select($sql)){
echo $row1['title'];
}
}

 

when i optimize this code like this

 

$sql = $op->runsql(
"SELECT b.title,b.blogpath FROM bloginfo AS b, favourite AS f WHERE b.id = f.blogid
AND f.userid =='$blogdata->author'
ORDER BY f.id DESC ");
if(mysql_num_rows($sql) > 0){
while($row1 = $op->select($sql)){
$bloglist[$i]=$row1['title'];
$blogurl[$i]=$row1['blogpath'];
$i++;}
}else{
$row = $op->select($sql);


//echo $row1['title'];
}
}
$blogcount=count($bloglist);
if($blogcount>0){?>
<h3 class="interestText">My Favourite Blog(s)</h3>
<ul class="myaccountNav">
<?for ($j = 0; $j <$blogcount; $j++) {
   echo "<li><a href='/blogs/".$blogurl[$j]."'>".ucfirst(strtolower(stripslashes($bloglist[$j])))."</a></li>";
}
?></ul>

 

it showing me wrong result. I have two users

1) logged in user => $userid

2)the user who's profile logged in user watching => $blogdata->author

 

Now what is wrong here-

1)the user who's profile logged in user is watching doesn't have any favourite blog in his acc.( though he has his own, which i am getting as his fav. blog and that is problem.how can his own blog is there in his fav blogs??)

 

2) and the logged in user's fav. blog contain the person he was watching his profile as his fav blog. for this its working fine.

 

can anyone help me to optimize my query?

 

thanks in advance.

[Alt_F4]

have you tried this:

 

$sql = $op->runsql(
"SELECT b.title,b.blogpath FROM bloginfo AS b JOIN favourite AS f ON b.id = f.blogid
WHERE f.userid =='$blogdata->author'
ORDER BY f.id DESC ");

[deepson2]

Thanks for your reply Alt_F4,

 

your query is showing me exactly opposite result. its not showing me user's fav. blogs,its showing me users blog.

 

any suggestion?

[kickstart]

Hi

 

Couple of things.

 

In both bits of SQL you are using == rather than =.

 

You do not appear to have initialised $i to anything (probably should be set to 0). If $i happened to be 10 and you found 5 matching records then they would be put into the bloglist and blogurl arrays as elements 10, 11, 12, 13 and 14, but in your for loop at the bottom you would try and output them from 0, 1, 2, 3 and 4.

 

You are not specifying the join conditions (no ON clause), instead you are doing it in the WHERE clause.

 

There is an extra } after the else clause. Possibly this is required, but if so it suggests that it can get to the loop around bloglist[] / blogurl[] without executing the SQL and populating those arrays.

 

Alt_F4s code should give exactly the same result as you first (working) SQL.

 

All the best

 

Keith

[deepson2]

Hello Keith,

 

I did all the changes what you have mentioned like this

 

<?

$i =0;
$sql = $op->runsql("SELECT b.title,b.blogpath FROM bloginfo AS b, favourite AS f ON b.id = f.blogid
AND f.userid ='$blogdata->author' ORDER BY f.id DESC ");
if(mysql_num_rows($sql) > 0){
while($row1 = $op->select($sql)){
$bloglist[$i]=$row1['title'];
$blogurl[$i]=$row1['blogpath'];
$i++;}
}else{
$row = $op->select($sql);
//echo $row1['title'];
}

$blogcount=count($bloglist);
if($blogcount>0){?>
<h3 class="interestText">My Favourite Blog(s)</h3>
<ul class="myaccountNav">
<?for ($j = 0; $j <$blogcount; $j++) {
   echo "<li><a href='/blogs/".$blogurl[$j]."'>".ucfirst(strtolower(stripslashes($bloglist[$j])))."</a></li>";
}
?></ul>

 

Getting same false result. and using JOIN i am getting same result. I am sorry Alt_F4, i didnt checked it properly.

 

What can i do now Keith?

please help me.

[deepson2]

I am sorry but with ON  not getting proper result

 

but with JOIN or WHERE i am getting half correct result(like i can see (if i am logged in user,my fav.blogs)

[kickstart]

Hi

 

Can you confirm exactly what you want.

 

As I understand it you are looking at $blogdata->author and want a list of their favourite blogs.

 

Change the code to the following and check what the SQL looks like and see if the message is put out that nothing has been found :-

 

<?
$i =0;
$sqlStr = "SELECT b.title,b.blogpath FROM bloginfo AS b JOIN favourite AS f ON b.id = f.blogid
WHERE f.userid ='".$blogdata->author."' ORDER BY f.id DESC ";
echo "$sqlStr<br />";
$sql = $op->runsql($sqlStr);
if(mysql_num_rows($sql) > 0)
{
while($row1 = $op->select($sql))
{
	$bloglist[$i]=$row1['title'];
	$blogurl[$i]=$row1['blogpath'];
	$i++;
}
}
else
{
$row = $op->select($sql);
//echo $row1['title'];
echo "No rows found<br />";
}

$blogcount=count($bloglist);
if($blogcount>0)
{
?>
<h3 class="interestText">My Favourite Blog(s)</h3>
<ul class="myaccountNav">
<?for ($j = 0; $j <$blogcount; $j++) #
{
   echo "<li><a href='/blogs/".$blogurl[$j]."'>".ucfirst(strtolower(stripslashes($bloglist[$j])))."</a></li>";
}
?>
</ul>
<?
}
?>

 

All the best

 

Keith

[deepson2]

when i tried this query in my SQL, its showing me empty result.

SELECT b.title, b.blogpath
FROM bloginfo AS b
JOIN favourite AS f ON b.id = f.blogid
WHERE f.userid = '75'
ORDER BY f.id DESC

 

and its true also userid='75' has his own blog. he doesn't have any fav blog. then why his own blog is getting displayed in his fav.blog??

 

if(mysql_num_rows($sql) > 0)

why my query is going further while there is no row for that particular user?

 

please help me keith

 

 

 

[kickstart]

Hi

 

I cannot see a reason from the code you have put up.

 

Afraid it will require going through the code and the data in a lot more detail.

 

All the best

 

Keith

[deepson2]

I have just put it in different ways. like this

 

<?


$i =0;
$sqlStr = "SELECT b.title,b.blogpath FROM bloginfo AS b JOIN favourite AS f ON b.id = f.blogid
WHERE f.userid ='".$blogdata->author."' ORDER BY f.id DESC ";
echo "$sqlStr<br />";
$sql = $op->runsql($sqlStr);
if(mysql_num_rows($sql) > 0)
{
while($row1 = $op->select($sql))
{
$bloglist[$i]=$row1['title'];
$blogurl[$i]=$row1['blogpath'];
$i++; ?>
<h3 class="interestText">My Favourite Blog(s)</h3>
<ul class="myaccountNav">
<?
$blogcount=count($bloglist);
if($blogcount>0)
{
for ($j = 0; $j <$blogcount; $j++)
{
echo "<li><a href='/blogs/".$blogurl[$j]."'>".ucfirst(strtolower(stripslashes($bloglist[$j])))."</a></li>";}}
?>
</ul>
<? }}else{
$row = $op->select($sql);
//echo $row1['title'];
echo "No rows found<br />";
}
//echo $row1['title'];
  ?>

 

so where there is no fav blogs are showing now No row found.(that i wanted) but where actually blogs are there getting result like this

 

My Favourite Blog(s)

 

    * Geo 12.97°n 77.56°e

My Favourite Blog(s)

 

    * Geo 12.97°n 77.56°e

    * Marathi kavita and poems

My Favourite Blog(s)

 

    * Geo 12.97°n 77.56°e

    * Marathi kavita and poems

    * My blog

 

it means every time its checking and adding blogs one by one. how can i get result only once.or unique?

 

[kickstart]

Hi

 

You are looping round adding to the arrays and outputing everything every time you add something to the arrays.

 

Try this:-

 

<?


$i =0;
$sqlStr = "SELECT b.title,b.blogpath FROM bloginfo AS b JOIN favourite AS f ON b.id = f.blogid
WHERE f.userid ='".$blogdata->author."' ORDER BY f.id DESC ";
echo "$sqlStr<br />";
$sql = $op->runsql($sqlStr);
if(mysql_num_rows($sql) > 0)
{
while($row1 = $op->select($sql))
{
	$bloglist[$i]=$row1['title'];
	$blogurl[$i]=$row1['blogpath'];
	$i++; 
}
?>
<h3 class="interestText">My Favourite Blog(s)</h3>
<ul class="myaccountNav">
<?
$blogcount=count($bloglist);
if($blogcount>0)
{
	for ($j = 0; $j <$blogcount; $j++)
	{
		echo "<li><a href='/blogs/".$blogurl[$j]."'>".ucfirst(strtolower(stripslashes($bloglist[$j])))."</a></li>";
	}
}
?>
</ul>
<? 
}
else
{
$row = $op->select($sql);
//echo $row1['title'];
echo "No rows found<br />";
}
//echo $row1['title'];
?>

 

However if you are not using the arrays elsewhere then something like this would be simper:-

 

<?


$i =0;
$sqlStr = "SELECT b.title,b.blogpath FROM bloginfo AS b JOIN favourite AS f ON b.id = f.blogid
WHERE f.userid ='".$blogdata->author."' ORDER BY f.id DESC ";
echo "$sqlStr<br />";
$sql = $op->runsql($sqlStr);
if(mysql_num_rows($sql) > 0)
{
?>
<h3 class="interestText">My Favourite Blog(s)</h3>
<ul class="myaccountNav">
<?
while($row1 = $op->select($sql))
{
	echo "<li><a href='/blogs/".$row1['blogpath']."'>".ucfirst(strtolower(stripslashes($row1['title'])))."</a></li>";
}
?>
</ul>
<? 
}
else
{
$row = $op->select($sql);
//echo $row1['title'];
echo "No rows found<br />";
}
//echo $row1['title'];
?>

 

All the best

 

Keith

[deepson2]

Keith, You are simply genius man!

 

Why i am here you are one of the reason. because you always help me!

 

Its working!!