mysql query

[andyd34]

I have a column in a database which i want to fetch 2 differing values from depending on the access rights of a member.

 

I have used

 

$countRes = mysql_query('SELECT * FROM table WHERE user = "'.$member.'"') or die(mysql_error());
$count=mysql_num_rows($countRes);
if(empty($count)) 
{
$file_stat[1] = 'public';
}
else
{
$file_stat[1] = 'public';
$file_stat[2] = 'friends';
}
mysql_free_result($countRes);
$result = mysql_query('SELECT * FROM table WHERE user = "'.$member.'" AND fileStatus IN("'.implode('", "', $file_stat).'")') or die(mysql_error())

 

This has worked for me before but doesn't seem to be working this time. Does anyone have a suggestion.

[ngreenwood6]

Are you getting an error or what isnt working? It usually helps if you give information on what is or is not happening that is supposed to or not supposed to.

[andyd34]

When the user isn't veryfied its showing public results only and when they are verifified it's only showing public results. I have added the following

 

if(empty($count)) 
{
$file_stat[1] = 'public';
echo 'Unverified';
}
else
{
$file_stat[1] = 'public';
$file_stat[2] = 'friends';
echo 'Verified';
}

 

When not logged in it shows public files and echoes unverified

 

When logged in it echoes verified and only shows public files.

 

There are no errors or warnings, if you didn't know better you'd think it was working fine

[ngreenwood6]

I have never used the fileStatus IN mysql but could it be because it is being called after you are trying to use the variables? Could you move the line before the if/else statement?

[andyd34]

the  "fileStatus" is a column in the database which hold information whether a file is for public viewing for friends viewing only. Basically what it is, is

 

SELECT & FROM table WHERE file_is_either IN("public, friends")

 

I cannot find anything on the mysql 'IN' function and was hopeing someone could assist

[kickstart]

Hi

 

That would be:-

 

SELECT & FROM table WHERE file_is_either IN("public", "friends")

 

All the best

 

Keith

[sasa]

try

$result = mysql_query("SELECT * FROM table WHERE user = '".$member."' AND fileStatus IN('".implode("', '", $file_stat)."')") or die(mysql_error());

swap single and double quotes

[ngreenwood6]

Sasa are you using [ code ] tags or [ php ] tags because I can only see a horizontal scroll bar on this one too.

[Philip]

Sasa are you using [ code ] tags or [ php ] tags because I can only see a horizontal scroll bar on this one too.

 

that's the php tag

[ngreenwood6]

oh ok you might want to use the code tags then because i havent been able to read any of your posts lol

 

edit: OP sorry for interrupting.

[sasa]

try

$result = mysql_query("SELECT * FROM table WHERE user = '".$member."' AND fileStatus IN('".implode("', '", $file_stat)."')") or die(mysql_error());

 

swap single and double quotes

 

report error to administrator

 

 

 

[andyd34]

Thanks for all the replies, they're much appreciated but none worked.

[andyd34]

I am really sorry to mess everyone around who took the time to read this post but both

 

$result = mysql_query("SELECT * FROM table WHERE user = '".$member."' AND fileStatus IN('".implode("', '", $file_stat)."')") or die(mysql_error());

 

and

 

 

$result = mysql_query('SELECT * FROM table WHERE user = "'.$member.'" AND fileStatus IN("'.implode('", "', $file_stat).'")') or die(mysql_error())

 

work fine, the trouble was there is a third setting in the fileStatus table (private), there where no friends just public and private so that's where it was going wrong/right. Aparently it was the idiot on the keyboard that was coding it that was the error

[sasa]

i tell that swap quotes because

 

"If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotes because a string quoted within double quotes is interpreted as an identifier." (from mysql manual)