Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Query Issue

Posted on 2011-03-20
11
Medium Priority
?
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 143

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
 
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 143

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 143

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

636 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