Solved

MySQL Query Issue

Posted on 2011-03-20
11
266 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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.

863 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

16 Experts available now in Live!

Get 1:1 Help Now