Solved

MySQL Query Issue

Posted on 2011-03-20
11
265 Views
Last Modified: 2012-05-11
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
Comment
Question by:piixeldesigns
11 Comments
 

Expert Comment

by:13cactus
ID: 35175554
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
 
LVL 1

Author Comment

by:piixeldesigns
ID: 35175580
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35175642
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
 
LVL 1

Author Comment

by:piixeldesigns
ID: 35175662
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35176178
>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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:piixeldesigns
ID: 35177344
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35189743
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
 
LVL 1

Author Comment

by:piixeldesigns
ID: 35189858
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35205821
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
 
LVL 1

Accepted Solution

by:
piixeldesigns earned 0 total points
ID: 35207028
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
 
LVL 1

Author Closing Comment

by:piixeldesigns
ID: 35239048
I fixed the problem myself by re-building another module.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now