• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

MySQL Query Issue

Hi All,

I am having an issue with my query (when I run this query in SQL directly it works perfectly and displays the correct results when I use it as is written below in a typical while loop it is repeating my data set over and over again.

Also I cannot seem to add an ORDER BY at the end to this query without generating an error.
I want to ORDER BY bp_post_table.p_id DESC

Here is the code:
SELECT bp_post_table.*, bp_post_table.date_created AS TimeSpent, public_details_tab.p_Image, 
				CONCAT(`public_details_tab`.`firstname`,'  ',`public_details_tab`.`lastname`) bname
		 		FROM bp_post_table INNER JOIN public_details_tab 
		 		ON bp_post_table.userip = public_details_tab.userid
		 		WHERE bp_post_table.p_id = '$log_table_id' 
		 		AND public_details_tab.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$getUserData') 
		 		OR public_details_tab.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$getUserData') 
		 		OR bp_post_table.p_id = '$log_table_id' AND bp_post_table.userip = '$getUserData'
		 	
		 		UNION ALL
		  
		  		SELECT bp_post_table.*, bp_post_table.date_created AS TimeSpent, bp_details_tab.b_Image p_Image, 
		  		bp_details_tab.bname
		 		FROM bp_post_table INNER JOIN bp_details_tab 
		 		ON bp_post_table.userip = bp_details_tab.userid 
		 		WHERE bp_post_table.p_id ='$log_table_id' 
		 		AND bp_details_tab.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$getUserData') 
		 		OR bp_details_tab.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$getUserData') 
		 		OR bp_post_table.p_id = '$log_table_id' AND bp_post_table.userip = '$getUserData'

Open in new window


Thanks In Advance,
PD
0
piixeldesigns
Asked:
piixeldesigns
1 Solution
 
13cactusCommented:
It might help if you were to provide the snippet of code you used, now it leafs us guessing... my guess is your parameters are not supplied into the SQL query string because perhaps.
0
 
piixeldesignsAuthor Commented:
My Variables are being defined at the top of the document and I am using a basic while loop:
$result = mysql_query("
				SELECT bp_post_table.*, bp_post_table.date_created AS TimeSpent, public_details_tab.p_Image, 
				CONCAT(`public_details_tab`.`firstname`,'  ',`public_details_tab`.`lastname`) bname
		 		FROM bp_post_table INNER JOIN public_details_tab 
		 		ON bp_post_table.userip = public_details_tab.userid
		 		WHERE bp_post_table.p_id = '$log_table_id' OR bp_post_table.userip = '$getUserData'
		 		AND public_details_tab.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$getUserData') 
		 		OR public_details_tab.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$getUserData')
		 		 
		 		
		 	
		 		UNION ALL
		  
		  		SELECT bp_post_table.*, bp_post_table.date_created AS TimeSpent, bp_details_tab.b_Image p_Image, 
		  		bp_details_tab.bname
		 		FROM bp_post_table INNER JOIN bp_details_tab 
		 		ON bp_post_table.userip = bp_details_tab.userid 
		 		WHERE bp_post_table.p_id ='$log_table_id' OR bp_post_table.userip = '$getUserData'
		 		AND bp_details_tab.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$getUserData') 
		 		OR bp_details_tab.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$getUserData')
		 		
		 	");
		
			while ($row = mysql_fetch_array($result)) {
				//something here....

Open in new window

0
 
Beverley PortlockCommented:
I think the point the other expert is making is that if there is a mistake in your PHP syntax, how are we supposed to help you solve it without seeing the PHP code?

For instance a common mistake is to overwrite variables in the loop rather than append them to an array or a string, but without the PHP code we cannot say where it is going wrong.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
piixeldesignsAuthor Commented:
that is the only PHP code I am using that is getting affected. I understand that but that is all I can basically show really.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Also I cannot seem to add an ORDER BY at the end to this query without generating an error.

as it's a UNION query, the ORDER BY will only allow you to use the output column aliases, aka ORDER BY p_id  ... however, for that to work, you need to include that column in (both) SELECT

in regards to the other issue, I have to presume that somehow you either don't connect to the same db, or you do have an issue in the php code in the loop
0
 
piixeldesignsAuthor Commented:
When I try that with the ORDER BY I get the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in the line where the while loop starts on.

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for me, you can stay here, I have no problems continuing to explain until we solve this.

so, what is the exact error message you get, and what code you use right now, based on the input you got from me
0
 
piixeldesignsAuthor Commented:
OK thanks well please look at the below and I will try and explain it better.

I have a query which I will attach below and as you will see I have multiple AND/IN arguements in this query. My problem is I would like to group these together so that they get seen as one arguement so that I can avoid repeating of the same data set in my while loop.

Here is my query:
SELECT bp_post_table.p_id, bp_post_table.post, bp_post_table.userip, bp_post_table.userprofileid, 
				bp_post_table.date_created AS TimeSpent, public_details_tab.p_Image, 
				CONCAT(`public_details_tab`.`firstname`,'  ',`public_details_tab`.`lastname`) bname
		 		FROM bp_post_table INNER JOIN public_details_tab 
		 		ON bp_post_table.userip = public_details_tab.userid
		 		
		 		WHERE public_details_tab.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$userData')
		 		OR public_details_tab.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$userData')
		 		OR bp_post_table.p_id = '$log_tab_id' AND bp_post_table.userip = '$userData'
		 	
		 		UNION ALL
		  
		  		SELECT bp_post_table.p_id, bp_post_table.post, bp_post_table.userip, bp_post_table.userprofileid, 
		  		bp_post_table.date_created AS TimeSpent, bp_details_tab.b_Image p_Image, bp_details_tab.bname
		 		FROM bp_post_table INNER JOIN bp_details_tab 
		 		ON bp_post_table.userip = bp_details_tab.userid 
		 		
		 		WHERE bp_details_tab.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$userData') 
		 		OR bp_details_tab.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$userData')
		 		OR bp_post_table.p_id = '$log_tab_id' AND bp_post_table.userip = '$userData'
		 		
		 		ORDER BY p_id DESC

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's see if this works out:


SELECT t.p_id
     , t.post
     , t.userip
     , t.userprofileid
     , t.date_created AS TimeSpent
     , p.p_Image
     , CONCAT(p.`firstname`,'  ',p.`lastname`) public_name
     , d.b_Image p_Image
     , d.bname image_name

  FROM bp_post_table t
  LEFT JOIN public_details_tab p
    ON p.userid = t.userip 
  JOIN bp_details_tab d
    ON d.userid = t.userip 
 WHERE t.p_id = '$log_tab_id' 
   AND t.userip = '$userData'
   AND ( t.userid IN (SELECT fuid FROM bp_frieds WHERE uid = '$userData')
      OR t.userid IN (SELECT uid FROM bp_frieds WHERE fuid = '$userData')
       )
                              
ORDER BY t.p_id DESC

Open in new window

0
 
piixeldesignsAuthor Commented:
Hey Guys,

I have fixed this problem myself (I delved deeper into the system and found to fix this problem I had to re-build another module which was affecting it.

I have done this and it works fine now - Thanks Again for your help.
0
 
piixeldesignsAuthor Commented:
I fixed the problem myself by re-building another module.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now