Javrixx Posted October 5, 2006 Share Posted October 5, 2006 Ok, all I need to is perform what sounds like a simple command, but maybe it's the way it is setup that is making it hard for me.I have 5 tables in my database. The only two relevant tables for this is the "users" table and the "results" table. The users table holds all information, id, username, password, etc. Results is where the actual information for what I am doing is kept. Filenames, foldernames, etc.So there are 2 things I need to do...1)On one page I need to display how many results someone has. The way I have set this up is each user has an id, say user 50. Then in the results, I create a new row with that users information, in the results there is also a user field. So say user 50 has 5 results or files, each one is entered with 50 in the user field. All I need to do on this page is say "Welcome <name>, you have <XX> amounts of results ready to view/download." I already have the <name> part working, I only need the XX amounts to work.So I need a code snippet to insert into this page that will check the results table for how many in that table under users is equal to 50. Hopefully this makes sense, and I've tried tons of stuff and I just cannot get it to work.2)Kind of similar but much simpler. On this page, I need to have a link to a user's folder. Each user is assigned a folder according to their userid. The folder is in the "results" table. When on this page they are already logged in, so all I need to do is pull the userid with whatever user they are logged into...So say user "steve" is logged in and he is user 8. He logs in with his information, on this page I need it to be dynamic (everyone views this page, but I need the link to their specific folder to be different). So I need it first identify what the user id is from the "users" table. Then I need to find that user id in the "results" table. In the results table is a column called "folder". I need it to dynamically put that folder information into the page.I hope someone can help, I know this is pretty basic stuff. I'm using templates for both pages, so I need to do all of that above and then define it as a variable and use it, which that part I know how to do.Any help is MUCH appreciated. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted October 5, 2006 Share Posted October 5, 2006 1) "SELECT COUNT(*) AS Num FROM results WHERE user=$user_id GROUP BY user";2) In the results table, does the folder column have the same value for a user in all of that user's rows? I.E. if Steve's folder is "steve," does every row in the results table have the value "steve" where the user is also steve? Or is it possible that Steve's results are stored in multiple, different folders? Quote Link to comment Share on other sites More sharing options...
Javrixx Posted October 5, 2006 Author Share Posted October 5, 2006 [quote author=roopurt18 link=topic=110614.msg447427#msg447427 date=1160074818]2) In the results table, does the folder column have the same value for a user in all of that user's rows? I.E. if Steve's folder is "steve," does every row in the results table have the value "steve" where the user is also steve? Or is it possible that Steve's results are stored in multiple, different folders?[/quote]Thank you so much for the response, I won't have time to try your suggestion until tomorrow or really late today, but it looks good, exactly what I was looking for.Ok I'll try to explain this the best I can, but it might be complicated. So in table "users" steve is unique, his id is let's say 8. In the "results" table, the way I link it to the user is by their id. The "results" table has the following columns: id, folder, date completed, and file. So if user steve, or user 8, has 3 results, I would make something like:id | folder | date | file8 | stevefolder/ | 10/03/06 | filename.xls8 | stevefolder/ | 10/04/06 | filename2.xls8 | stevefolder/ | 10/05/06 | filename3.xlsThe id and folder will always be the same. So what I need is when steve logs in with his username, I need to be able to pull his id, which is 8, and then use that to pull the folder location from the results table by his id.Basically I need a link on the page that will change for every user that points to that folder. I really hope this makes sense and I'm not being confusing. Thanks again for the help. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted October 5, 2006 Share Posted October 5, 2006 So why are you listing folder in every row for Steve if it's always the same? folder sounds like a column more suited for the users table IMO, otherwise you're just wasting space. Quote Link to comment Share on other sites More sharing options...
Javrixx Posted October 6, 2006 Author Share Posted October 6, 2006 I guess that's a good point. I'll move the folder to the users table. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted October 6, 2006 Share Posted October 6, 2006 Are you familiar with joins on MySQL tables? Quote Link to comment Share on other sites More sharing options...
Javrixx Posted October 10, 2006 Author Share Posted October 10, 2006 [quote author=roopurt18 link=topic=110614.msg448181#msg448181 date=1160159498]Are you familiar with joins on MySQL tables?[/quote]Not at all, sorry. :( Quote Link to comment Share on other sites More sharing options...
Javrixx Posted October 11, 2006 Author Share Posted October 11, 2006 Well I got the second issue fixed, but I'm still working on the first issue. I think I'm close to getting it solved but not yet. :( Any other suggestions or help is much appreciated. I've got the user's id and I think my count code is correct, but I need to define it as a variable so I can later use it in a page and I guess I'm not doing it right because all of the things I have tried aren't working. Any help is much appreciated.[code=php:0]$sql = 'SELECT * FROM '.$mysql['prefix'].'users WHERE username="'.$_SESSION['username'].'"';if(!$result = mysql_query($sql)){ die('The following MySQL query failed. User data could not be retrieved. '.$sql);}// assign the user info to variableswhile (($row = mysql_fetch_array($result)) != false){ $user_folder = $row['user_folder']; $id = $row['id'];}$sql2 = "SELECT count(*) FROM results WHERE id=$id";[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.