Need help with php / mysql wether to display or not

[jnerotrix]

here are the tables and fields in my mysql dataabse sex1800_loginbux

 

- = 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

 

to not show ones that are in the survey_completed table

 

 

Hope I have explained this well enough for everyone to understand

 

 

[premiso]

SELECT s.* FROM completed_surveys cs, surveys s WHERE cs.id != s.survey_id

 

 

[jnerotrix]

Table 'sex1800_loginbux.surveys' doesn't exist

 

was it trying to open the table survey not surveys

 

Heres The 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 s.* FROM completed_surveys cs, surveys s WHERE cs.id != s.survey_id";
$result = mysql_query($query) or die(mysql_error());
echo mysql_num_rows($result);


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

[.josh]

select s.* from survey as s, completed_surveys as c where s.id != c.survey_id

 

edit: oops you said NOT so query string changed to !=

[jnerotrix]

Now it just displays 0 where the surveys should be displayed

 

Heres the site if you want to check it out

 

http://epicbux.com/

[jnerotrix]

It Still Displays 0 even with your edit

[.josh]

would help if you provided login info to a test account that has surveys completed....

 

also, did you try putting the query directly into the db from command line or phpmyadmin?

[jnerotrix]

Login

demo1 - username

demo1 - password

 

im not sure how to put the query in directly

[.josh]

how did you setup your tables in the first place?

 

[jnerotrix]

DOUBLE POST SORRY READ NEXT POST

[jnerotrix]

CREATE TABLE `sex1800_loginbux`.`surveys` (

`id` INT NOT NULL AUTO_INCREMENT ,

`title` VARCHAR( 32 ) NOT NULL ,

`adlink` VARCHAR( 32 ) NOT NULL ,

PRIMARY KEY ( `id` )

) ENGINE = MYISAM

 

CREATE TABLE `sex1800_loginbux`.`completed_surveys` (

`member_id` VARCHAR( 32 ) NOT NULL ,

`survey_id` NOT NULL AUTO_INCREMENT ,

) ENGINE = MYISAM

[.josh]

No I mean, where did you set it up? When you created your tables, that's the code, but how did you give the db that code? From a command line? Through phpmyadmin or some other interface?  by using mysql_query in a php script?

[jnerotrix]

ok if your asking how added the tables i added them by sql injection through phpmyadmin

 

I got the sql codes from premiso (Another user who helped on most of this)

 

[jnerotrix]

So Basicly

Premiso Wrote the Sql code

 

then i injected with phpmyAdmin

[.josh]

okay so go to phpmyadmin and run

 

select s.* from survey as s, completed_surveys as c where s.id != c.survey_id

[jnerotrix]

umm how do i do that do i import it or use the sql inject

[.josh]

select your survey table from the list of tables, and at the top where it says

 

    * Browse    * Structure    * SQL    * Search    * Insert    * Export    * Import    * Operations    * Empty    * Drop

 

click the SQL tab.

 

There should be a huge textarea box labeled "Run SQL query/queries on database <database name>:

 

Put the query string in it and on the bottom right, click the "Go" button.

[jnerotrix]

Ok it returned

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)

[.josh]

Okay...so the only reason I can think of that that would happen, is if completed_surveys has a row for all the surveys in it - that is, all your surveys are complete.

[jnerotrix]

it needs to go by user if i create a new user then visit the home page still displays 0

 

http://epicbux.com/

 

you can log in with this account i just made

 

user: demo1

pass: demo1

 

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

Is it possible to do what i want it to do ...

 

Well it must be possible because thats what other survey sites do

 

if their is another way to do what i want it to do please let me know

[jnerotrix]

their is also another page called addsurvey.php

 

heres the codes:

 

<?php
include("include/session.php");
?>

<?php



if (isset($_POST['action'])) {

mysql_connect("localhost", "sex1800_admin", "PASSWORD") or die(mysql_error());
mysql_select_db("sex1800_loginbux") or die(mysql_error());

$title = mysql_real_escape_string($_POST['title']);
$link = mysql_real_escape_string($_POST['link']);

mysql_query("INSERT INTO survey 
(title, adlink) VALUES('$title', '$link') ") 
or die(mysql_error());  

echo "Survey Added!";

}

?>


<html>
<head>
</head>
<title> Add a Survey </title>
<center>
<?php 

if($session->isAdmin()){

?>



<table border="1">
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
<tr>
<td>Survey Title: <input type="text" name="title"><br>Ad Link: <input type="text" name="link"></td>
</tr>
<tr>
<td><input type="submit" name="action" value="Add Survey"></td>
<input type='hidden' name='submitted' value='yes'>
</tr>
</form>
</table>



<?php
} else {

echo 'You Must Be An Administrator to add a Survey';

}

?>

 

 

 

[.josh]

Here try this query instead:

 

select id, title, adlink from survey where id not in (select survey_id from completed_surveys)

 

 

[fenway]

A left join would work too.

[jnerotrix]

ohh i think it worked not sure but it soundeded correct

 

result

 

Showing rows 0 - 3 (4 total, Query took 0.1366 sec)

[jnerotrix]

ok i tried running

 

select id, title, adlink from survey where id not in (select survey_id from completed_surveys)

 

in

 

this but now it just displays the number 4

 

<?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 id, title, adlink from survey where id not in (select survey_id from completed_surveys)";
$result = mysql_query($query) or die(mysql_error());
echo mysql_num_rows($result);


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

 

it needs to actually display survey like this

 

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