MySQL Variables

[inuwolf]

I'm a newbie attempting something very simple, but I've had trouble because, for once, I haven't been able to Google a solution.

This is a simplified version of the code I have to search for all users (and their emails) who have selected Yes in a certain column of my DB. This "certain column" is my source of trouble; I want it to be a variable determined by the $_SESSION['user'], (so that the results match the user logged in) but I'm not sure how to insert the $VARIABLE in my preexisting script. How do I do this (the bold lines are really all I need help with)?

Preexisting script:
_____________________________________________________
$sql = "SELECT cat_id, cat_title FROM table
WHERE $VARIABLE='y'";
$result = mysql_query( $sql,$conn );
while( $row=mysql_fetch_row($result) )
{
$cat_titles[] = $row[1];
}
mysql_free_result( $result );
?>
_____________________________________________________
Again, variable: $_SESSION['user']

Thanks, I appreciate all the pointers I've been given here.

[coopster]

You could either assign it to a simple variable first ...

$VARIABLE= $_SESSION['user']; 
$sql = "SELECT cat_id, cat_title FROM table WHERE $VARIABLE='y'";

... or you could just stick it right into the query ...

$sql = "SELECT cat_id, cat_title FROM table WHERE {$_SESSION['user']}='y'";

Note the braces surrounding the superglobal variable since it is an array [php.net]

[inuwolf]

wow thanks, simpler than I thought

[inuwolf]

no, it wasn't simpler than I thought.

I tried and tweaked both methods, ensured I was connected, yet I get these errors:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/virtual/site110/fst/var/www/html/match.php on line 31

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/virtual/site110/fst/var/www/html/match.php on line 37

Warning: Invalid argument supplied for foreach() in /home/virtual/site110/fst/var/www/html/match.php on line 41

for this code:
__________________________________
$USERCOL = $_SESSION['user'];
$sql = "SELECT user, email FROM users WHERE $USERCOL = 'y'";
$result = mysql_query( $sql,$conn );
while( $row=mysql_fetch_assoc($result) )
{
$usercol[] = $row['usercol'];
// do stuff with other column
// data if we want
}
mysql_free_result( $result );
___________________________________

What does all this mean?

[coopster]

Have you tried dumping the query statement out to the browser yet to see what the problem may be?

[charlier]

Are you sure you have your Variable right. The way you have the query setup $VARIABLE would have to be the name of a field in your table. That is possible but it's pretty unusual. The nomal WHERE would be more like WHERE myfield = $VARIABLE.

Post a bit more about what you are tying to do and show a dump of your table structure.

Cheers
Charlie

[inuwolf]

I hope this is what you mean by a dump of the table structure:

CREATE TABLE users (
id int(11) NOT NULL auto_increment,
login varchar(25) NOT NULL default '',
pw varchar(32) NOT NULL default '',
real_name varchar(32) NOT NULL default '',
extra_info varchar(100) NOT NULL default '',
email varchar(50) NOT NULL default '',
tmp_mail varchar(50) NOT NULL default '',
active enum('y','n') NOT NULL default 'n',
PRIMARY KEY (id),
UNIQUE KEY mail (email),
UNIQUE KEY user (login)
) TYPE=MyISAM;

It might also help if I tell you exactly what I'm trying to do: After, the column 'active', there will be another column for each real_name, login, or id (preferably id, but whichever works), and the column will be enum('y','n') NOT NULL default 'n'.

I'm trying to make a page that automatically searches and prints to the screen the 'y' values in the logged-in user's column. In order to do this, I am querying the column of the user by using $_SESSION['user'] to match the logged-in user with the relevant column (because both the logged-in user and his or her column have the same name). If you guys know a better method to query this information for the relevant user, I would appreciate it.

Note: in my last post I wrote "user, email"; it should have been "real_name, email".

Thanks guys, I appreciate your help.

[charlier]

I think I see what you are trying to do now. It sounds like you want to be able to make a list of who's online as you see on some forums. I am not sure exactly how this is done and you might want to take a look at some open source software to see how they did it.

My guess would be that you want to add a field called 'last_visit '. You would then update the laat_visit time
whenever the user logs in and each time they load a page.

Now whenever any page is loaded by anyone you query the database to see who has a last_visit time of say less then 5 mins ago. You then just display a list of names from those records.

You can use this same schemce to ehance other aspects of your pages as well, for instance if you collect the last_visit time when the user logs in you can make a little line saying 'Your last visit was on ....

You can also keep a time field when you update your pages and show a list of 'Articles since your last vist.

I hope this is relavent to what you are doing if not feel free to sticky me with more details of what you actually want to do.

[Salsa]

I think that what coopster ment was to simply echo out your query something like:

$sql = "SELECT user, email FROM users WHERE $USERCOL = 'y'"; 
echo "\$sql == '$sql'<br>\n";

...so you can see if the query is properly formed.

BUT, as long as you dumped the table here, one problem I see is that there is no column "user" in the table and yet you've used it in your query. That's at least one reason why there is no result set, causing the first error, and everything tumbles down from there.

Even with that correction, I also noticed that your line

$usercol[] = $row['usercol'];

...will cause you problems because there is no 'usercol' in either your query or table.

I'd also suggest that you try including the mysql_error() function when making any MySQL queries, like:

if (!$result = mysql_query($sql,$conn))die(mysql_error());

...instead of simply:

$result = mysql_query( $sql,$conn );

That will give you a more meaningful message as to what's wrong.

I hope this helps.

[inuwolf]

Thanks, it definitely does help. I changed the field user to real_name, which actually does exist, added the error function, and did some other tweaks to makes sure the changes were right.

Now what I get is this:
Warning: Invalid argument supplied for foreach() in /home/virtual/site110/fst/var/www/html/match.php on line 41
$sql == 'SELECT real_name, email FROM users WHERE inuwolf = 'y''

This comes from this line:
$sql = "SELECT real_name, email FROM users WHERE inuwolf = 'y'";

For now, I got rid of the variable and replaced it with inuwolf, an existing column, so it bothers me that it still doesn't work. Charlier, unfortunately I'm not looking for an "I'm Online" indicator; that would probably be a lot easier for me to find. I'm working on a service where users select each other from a list, and when two people select each other, both are notified. What you see here is my (attempted) mechanism to check when the user has a mutual selection with someone else.

I would be forever greatful if you guys kept helping me here, I'm so close to success I can feel it!

[charlier]

I think I understand a bit better now. I suspect you want to allow for several selections for each person so for instance person A might select person H,K,M,and T. Then person T selects A,E and H. Then you want to notify T & H that they are a match. This is called the many to many problem in SQL. Probably a good idea to take a look at some of the online tutorials to see some examples of how to handle it. (search for SQL 'many to many' with the quotes) Essentially you need to add a second table, containing two columns, to your database . Call one column say 'members' and one column 'selections' where each field will contain the id of a member in your users table. Now you can have several records for each member each containing one of his/her selections.

Now what you need to do is

1. Do a query for each member and then extract the member ids of his/her selections from the selections column.

2. Take each of the member ids from step 1 and do a query to get their selections.

3. Compare the selections for the member from step 2 to the original member id from step one. If you find the member id from step one in the list from step 2 you have a match.

[inuwolf]

That's definitely the most helpful chunk of info I've gotten since I started my project, thanks charlier. Many-to-many is exactly what I have been looking for, and it sounds a lot cleaner than what I had planned.

I guess it's only peripheral now, but I could still use help with my problematic SQL query, because even though this is a new beginning, I'll probably face the same problem again.

Thanks

Edit: If anyone knows a good, simple many-to-many script, please post or mail it. I'm looking around, but most many-to-many tutorials are strictly conceptual--actual scripts are scarce thus far.

[coopster]

I remembered a post similar to what you are asking here regarding online users so I ran a quick search over the forums and found it:

[MySQL] Extend Who's online system [webmasterworld.com]

Lots of information to absorb there but it may be something that interests you.

[inuwolf]

1. Do a query for each member and then extract the member ids of his/her selections from the selections column.

2. Take each of the member ids from step 1 and do a query to get their selections.

3. Compare the selections for the member from step 2 to the original member id from step one. If you find the member id from step one in the list from step 2 you have a match.

This seems like such an essential peice of PHP, why can't I find a script for it anywhere. A lot of what I have found use three tables and are much more complex than necessary, as well as too complex for me to tweak for my own needs.

Anyone?