Need help with php / mysql wether to display or not

[.josh]

okay it echoes 4 because you tell it to echo num_rows here:

 

echo mysql_num_rows($result);

 

and then your while loop never gets executed because you kill your script with this:

 

die();

[jnerotrix]

fully functional thank you so much it took 1.5 days to get this to work but you figured it thank you so much

[jnerotrix]

code not fully functional

 

 

I tested site on account admin completed all 4 surveys

 

and then went to test on a different account and there were no surveys to complete

[jnerotrix]

code not fully functional

 

 

I tested site on account admin completed all 4 surveys

 

and then went to test on a different account and there were no surveys to complete

[premiso]

$query = "select id, title, adlink from survey where id not in (select survey_id from completed_surveys WHERE member_id = '" . $member_id . "')"

 

replace $member_id with whatever variable houses the current member_id.

[jnerotrix]

now surveys never dissapear after completing

 

also the sessions user id whatever is hashed

[.josh]

A left join would work too.

 

table1

id

1

2

3

 

table2

id name

1  a

2  b

3  c

4  d

5  e

 

He has 2 tables basically like that.  He wants to return all rows from table2 where table1.id != table2.id so returned results would be

 

4 d

5 e

 

 

I'm not sure I understand how a left join would work? If I were to do

 

select id, name from table2 left join table1 on table2.id = table1.id

I'd get

1  a

2  b

3  c

 

So I assume if I did

 

select id, name from table2 left join table1 on table2.id != table1.id

 

I would get

 

4 d

5 e

 

But that's not the case... I guess I'm not understanding the concept...

[.josh]

now surveys never dissapear after completing

 

also the sessions user id whatever is hashed

 

Show your code again let's see what you have now.

[premiso]

now surveys never dissapear after completing

 

also the sessions user id whatever is hashed

 

What is the current code?

 

Logically if you select the completed surveys for that user and show only the ones NOT IN the completed_survey table then it should return correctly.

 

If it doesn't check your logic.

 

[fenway]

I'm not sure I understand how a left join would work?

...

But that's not the case... I guess I'm not understanding the concept...

You would run this query:

 

select table2.id, table2.name from table2 left join table1 on ( table1.id = table2.id ) WHERE table1.id IS NULL 

[.josh]

I'm not sure I understand how a left join would work?

...

But that's not the case... I guess I'm not understanding the concept...

You would run this query:

 

select table2.id, table2.name from table2 left join table1 on ( table1.id = table2.id ) WHERE table1.id IS NULL 

 

Ah! Now why didn't I think of that.  I was staring at my test table results with the NULLs and it just didn't occur to me... you are the man.

[jnerotrix]

ok sorry for the long wait for post 

 

I tried

 

select table2.id, table2.name from table2 left join table1 on ( table1.id = table2.id ) WHERE table1.id IS NULL 

 

It Displays

 

Table 'sex1800_loginbux.table2' doesn't exist

 

---------------

here is the code i had before i added he the left join code

 

<?php
mysql_connect("localhost", "sex1800_admin", "PASSWORD") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$query = "select id, title, adlink from survey where id not in (select survey_id from completed_surveys WHERE member_id = '" . $member_id . "')";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

 

Hope you can help this is the website

 

http://epicbux.com/

 

 

[jnerotrix]

Ok I ran an sql query in phpmyadmin

 

 

select table2.id, table2.name from table2 left join table1 on ( table1.id = table2.id ) WHERE table1.id IS NULL 

 

and it Showed This

 

SQL query: Documentation

SELECT table2.id, table2.name
FROM table2
LEFT JOIN table1 ON ( table1.id = table2.id )
WHERE table1.id IS NULL
LIMIT 0 , 30

MySQL said: Documentation
#1146 - Table 'sex1800_loginbux.table2' doesn't exist 

 

=====================================

 

Here is the tables and fields i have in the database:

 

- = table
* = field

- completed_surveys
*member_id  <-- When user Completes a Survey their member_id is added
*survey_id <-- When user Completes a the Survey id Goes Here

- survey
*id <-- This Is The Survey ID
*title <-- This is the Survey title used in displaying Survey
*adlink <-- This is the Survey Ad Link Used in displaying Survey

- users
*userid <-- This is Where the Users Id is Stored

 

==================================

 

How would i make it so when php displays the surveys in the table survey

for each user

 

to not show ones that are in the survey_completed table by the userid

 

Hope I have explained this well enough for everyone to understand

 

 

Here is the code i have so far

 

<?php
mysql_connect("localhost", "sex1800_admin", "PASSWORD") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$query = "select table2.id, table2.name from table2 left join table1 on ( table1.id = table2.id ) WHERE table1.id IS NULL ";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

 

 

[.josh]

oh come on now...

 

It tells you table2 doesn't exist because table2 is obviously not the name of your table.

[fenway]

Please don't fork new topics when the problem has generated 3 pages worth of responses!  Sounds like you're in the wrong DB, DB not selected, etc.

[jnerotrix]

Ok well i dont know mysql

====================

what do i change these tables to in this code

 

select table2.id, table2.name from table2 left join table1 on ( table1.id = table2.id ) WHERE table1.id IS NULL 

 

Here are the tables

===================

 

- = table

* = field

 

- completed_surveys

*member_id  <-- When user Completes a Survey their member_id is added

*survey_id <-- When user Completes a the Survey id Goes Here

 

- survey

*id <-- This Is The Survey ID

*title <-- This is the Survey title used in displaying Survey

*adlink <-- This is the Survey Ad Link Used in displaying Survey

 

- users

*userid <-- This is Where the Users Id is Stored

[jnerotrix]

I tried this but it doesnt work it doesnt error but theres nothing

 

<?php
mysql_connect("localhost", "sex1800_admin", "12921993") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$query = "select survey.id, survey.title, survey.adlink from survey left join completed_surveys on ( survey.id = completed_surveys.survey_id ) WHERE survey.id IS NULL ";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

[fenway]

The non-joined table will never have null values as the result of a left join -- try

 

$query = "select surveys.id, survey.title, survey.adlink from survey left join completed_surveys on ( survey.id = completed_surveys.survey_id ) WHERE completed_surveys.survey_id IS NULL ";

[jnerotrix]

ok its almost perfect but now

 

if

 

user1 completes a survey

it disappears from user1's survey list

but it also disappears from user2's list it should only disappear from the user that took the survey

so their should be something with member id

 

member id is stored in

users.userid

and when a user completes a survey it saves their id in

completed_surveys.member_id

 

or u can use

 

$_GET['id']

 

to get the id

[jnerotrix]

it shouldnt show if survey wasnt completed it should show if survey wasnt completed by user

[jnerotrix]

Heres the code i have so far

 

 

<?php
mysql_connect("localhost", "sex1800_admin", "12921993") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$query = "select surveys.id, survey.title, survey.adlink from survey left join completed_surveys on ( survey.id = completed_surveys.survey_id ) WHERE completed_surveys.survey_id IS NULL ";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

 

it needs

 

to check the userid for completed surveys

 

this code makes it so if any1 completes the survey it dissapears off of all users surveys list

[gevans]

<?php
mysql_connect("localhost", "sex1800_admin", "12921993") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$memberid = (is_numeric($_GET['id'])) ? $_GET['id'] : 0;
$query = "select surveys.id, survey.title, survey.adlink from survey left join completed_surveys on ( survey.id = completed_surveys.survey_id ) WHERE completed_surveys.survey_id IS NULL AND completed_surveys.member_id=$memberid";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

 

try that

[jnerotrix]

Doesnt display surveys still i tried changing the $memberid code bt still didnt work

 

heres the code so far

 

<?php
mysql_connect("localhost", "sex1800_admin", "12921993") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$memberid = mysql_real_escape_string($_SESSION['userid']);
$query = "select survey.id, survey.title, survey.adlink from survey left join completed_surveys on ( survey.id = completed_surveys.survey_id ) WHERE completed_surveys.survey_id IS NULL AND completed_surveys.member_id = $memberid";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

[gevans]

<?php
mysql_connect("localhost", "sex1800_admin", "12921993") or die(mysql_error()); //add you password
mysql_select_db("sex1800_loginbux") or die(mysql_error());

echo $memberid = mysql_real_escape_string($_SESSION['userid']);
echo ' Member Id<br /><br />';
$query = "SELECT member_id FROM completed_surveys";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['member_id'];
}
die('end of test');
$query = "select survey.id, survey.title, survey.adlink from survey left join completed_surveys on ( survey.id = completed_surveys.survey_id ) WHERE completed_surveys.survey_id IS NULL AND completed_surveys.member_id = $memberid";
$result = mysql_query($query) or die(mysql_error());
?>
Number Of Surveys Available = <?php echo mysql_num_rows($result); ?>

<?php
while($row = mysql_fetch_array($result)){
echo "<tr><td><a href=\"survey.php?id={$row['id']}\">{$row['title']}</a></td></tr>";
}
?>

 

Run this so I can see what's going on

[jnerotrix]

READ NEXT POST