[Resolved] Need Assistance on a Complex MySQL Query

[user-7256]

Need Assistance on a Complex MySQL Query

I'm currently improving a site of mine called Commenty, and need some help with a large SQL query:

$bigq = mysql_query("SELECT * FROM tablename WHERE fname='$fname' AND lname='$lname' OR cid='$cid' OR email='$email' OR street='$address' AND city='$city' AND state='$state' AND zip='$zip' OR aim='$aim' ORDER BY id DESC LIMIT 50") or die(mysql_error());

I need to display comments about the user based on a few working combinations of info:

- First Name & Last Name
- CID (unique Commenty ID #)
- Email Address
- Street & City & State & Zip Code
- AIM Handle

If any one of these either/or/and criteria are met, then it can display that comment, because it is enough to (99%+ most likely) identify a single person. My goal with this is to keep the comments private; and to only be read by the intended recipient.

However, I am having cases where other people's comments are being shown to the wrong recipient; and I'm sure it has to do with this SQL query. My emergency comment privacy buffer is catching all comments with unintended recipients and hiding the comments to maintain privacy; however, I really need to get this fixed.

I'm not getting any MySQL errors... so...

Any help appreciated. Thanks! :wave:

.chulium.

 

[Tree]

$bigq = mysql_query("SELECT * FROM tablename WHERE 
(fname='$fname' AND lname='$lname') 
OR cid='$cid' 
OR email='$email' 
OR (street='$address' AND city='$city' AND state='$state' AND zip='$zip') 
OR aim='$aim' 
ORDER BY id DESC LIMIT 50") or die(mysql_error());

Assuming that's how you want it to be selected, that should work. You can remove the line breaks if you want.

 

[user-7256]

That doesn't seem to fix the problem, unfortunately But I really appreciate the suggestion. Any other ideas?

 

[martialtiger]

What I would do is create separate sql statements and execute them based on what info is entered.

Good luck!

 

[user-7256]

What I would do is create separate sql statements and execute them based on what info is entered.

Good luck!

I would... but that would complicate things when displaying the comments in descending ID order. Isn't there a way within a single query?

 

[martialtiger]

The query that Tree wrote above should work. Can you show the code that are you using to get these values for the sql statement and also how you determine what comments recipients are allowed to see?

 

[user-7256]

Here's my retrieval code:

while ($row2 = mysql_fetch_array($bigq))
{
	$c = strip_tags($row2['content']);
	$ip = $row2['authorip'];
	$date = $row2['date'];
	$coid = $row2['id'];

	$tofname = $row2['fname'];
	$tolname = $row2['lname'];
	$toemail = $row2['email'];
	$toaim = $row2['aim'];
	$tostreet = $row2['street'];
	$tocity = $row2['city'];
	$tostate = $row2['state'];
	$tozip = $row2['zip'];
	$tocid = $row2['cid'];

//then comes the privacy buffer code (double-checks to make sure the "to" values
//and the values with the comment match; if not, it hides the comment)
//then after that, it simply displays the comment

Is that all you need?

 

[martialtiger]

Can you provide the snippet where you get the data and then use it to run the sql query?

 

[user-7256]

Here... this is done near the beginning of the page. ($cid is a session variable, which has already been made into a local variable from $_SESSION[], so it works.)

$q = mysql_query("SELECT * FROM memberstable WHERE cid='$cid' LIMIT 1");
while ($row = mysql_fetch_array($q))
{
	$fname = $row['fname'];
	$lname = $row['lname'];
	$email = $row['email'];
	$aim = $row['aim'];
	$street = $row['address'];
	$city = $row['city'];
	$state = $row['state'];
	$zip = $row['zip'];
}

 

[martialtiger]

I assume the other while loop is inside this while loop? Also is it possible that some of those value are returned NULL or 0?

 

[user-7256]

No. The last snippet I gave you is at the top of the page, outside of any loop. It gets the current user's info from the database. It knows what user is logged in by a special session variable.

And yeah, I guess it is possible that some could be null, or 0... but if they are, the variable would be likewise, right?

EDIT: Hey, that made me think of something odd enough that I've never had problems with before. I've accounted for that possibility, and it works!!

Thanks! Rep/NP$ sent (of what I can.)

Resolved.

 

[martialtiger]

Glad it's resolved now.

Good luck with the rest of your ventures!