Best Approach?

[HDFilmMaker2112]

The below is code that is called from an AJAX function. It get's the additional credits available for purchase based on the credits already bought. Basically if you already bought Credit A for Film 1, it would list Credit A for films 2 and 3. Now I've run into problem... what would I do if they already bought Credit A for Films 1 and 2. How do I make it so it's only showing the third one as an additional purchase option?

 

My table is currently set-up as follows:

 

donors_credits

id|credit|film_number|donor_id

 

credits_listing (contains all the credits available for purchase)

id | cost | position | item_id | quantity | film_number

 

Should I add another query to donors_credit to find what films the person already bought or could I some how use the existing code below to pull that data without query the DB again?

 

<?php
require_once 'db_select.php';
$credit=$_GET['credit'];
$credit=explode('-',$credit);
$credit=$credit[0];
$film_number=$credit[1];
if($film_number==1){

}
elseif($film_number==2){

}

elseif($film_number==3){

}
  $sql_second_credit="SELECT * FROM $tbl_name4 WHERE item_id='Seq$credit' OR item_id='Tril$credit'";
  $result_second_credit=mysql_query($sql_second_credit) or die(mysql_error());
  
  $select.='<select name="new_credit">'."\n";
  $select.='<option value="">Select Credit</option>'."\n";                
  while($second_row_credits=mysql_fetch_array($result_second_credit)){
  extract($second_row_credits);
       $select.='<option value="'.ucfirst($item_id).'">'.ucfirst($position).' - Film # '.$film_number.'</option>'."\n";
  }
$select.='</select> ';

echo $select;
?>

 

 

 

This is the page where the AJAX call (calls the code above) is displayed:

<?php
session_start();
$myusername=$_SESSION['myusername2'];
$mypassword=$_SESSION['mypassword2'];

require_once 'db_select.php';

$content='
<div class="main">
<div class="main_header">Purchase Additional Credit</div>';
if($_GET['e']==t){
$content.='<p class="green clear">
Request Sent. You should be contact with a discounted crew credit button specifically for you, in the next two business days.
</p>
';
}
else{
$content.='
<p>
If you wish to purchase an additional credit, use the form below to select the credit you wish to buy, 
the name in which the original credit is listed under (as listed on the donors page), and the e-mail address in which the original purchase came from. 
This is a great to get your name back on the top 100 donors list. Once you submit this form, we will review the submitted material, 
create a new paypal purchase button specifically for you, and send you the link to that button. 
From there, you will be able to purchase the additional credit.';
if($_GET['e']==f){
$content.='<p class="red">
The email address you entered doesn\'t match the email in the database for this account. 
Please try again. <br />
If you keep having problems, please contact us at <a href="mailto:general@makethemoviehappen.com">
general@makethemoviehappen.com</a>. 
</p>';
}
$content.='
<form action="" method="post">
    <p><label>Name Credit is Listed Under:</label> <input type="text" name="name" size="30" /></p>
    <p><label>E-Mail of Original Purchase:</label> <input type="text" name="email" size="32" /></p>
    <p><label>Original Credit Purchased:</label>';
    $sql_credit="SELECT $tbl_name2.credit,$tbl_name.donor_id,$tbl_name2.film_number FROM $tbl_name JOIN $tbl_name2 USING (donor_id) WHERE $tbl_name.username='$myusername' AND $tbl_name.password='$mypassword'";
    
    $result_credit=mysql_query($sql_credit) or die(mysql_error());  
    $menu_generate="";
    
    while($credit_rows=mysql_fetch_array($result_credit)){
extract($credit_rows);
$menu_generate.='<option value="'.$credit.'-'.$film_number.'" >'.ucwords($credit).' - Film # '.$film_number.'</option>'."\n" ;
    }


$content.="<select name=\"credit\" onchange=\"sndReq(this.value);\">";
$content.='<option value="">Select Credit</option>'."\n";
$content.=$menu_generate;
$content.="</select>";
$content.='<p><label>Second Credit to Purchase:</label>
<span id="ajaxout"></span></p>';
$content.='<p><input type="submit" value="Submit" name="Submit" /></p>
    </form>
';
}
$content.="
</div>
<br />";
?>

[HDFilmMaker2112]

I just decided second query.

 

$sql_films="SELECT $tbl_name2.credit,$tbl_name.donor_id,$tbl_name2.film_number,
$tbl_name4.item_id FROM $tbl_name JOIN $tbl_name2 JOIN $tbl_name4 
ON ($tbl_name2.donor_id=$tbl_name.donor_id AND $tbl_name2.credit=$tbl_name4.position 
AND $tbl_name2.film_number=$tbl_name4.film_number) 
WHERE $tbl_name.username='$myusername' AND $tbl_name.password='$mypassword'";
$result_films=mysql_query($sql_films) or die(mysql_error());  
  
while($films_row=mysql_fetch_array($result_films)){
extract($films_row);

}

 

That's working... now my question is I can get the film numbers, what do I do with them to process them to be used in this query:

 

$sql_second_credit="SELECT * FROM $tbl_name4 WHERE item_id='Seq$credit' OR item_id='Tril$credit'";

 

Should I just do a bunch of if/else statements? Would seem like it could be done more efficiently, because I'd need about 6 different ones. 1,2,3, 1 and 2, 1 and 3, 2 and 3,

[HDFilmMaker2112]

Here's my most recent updated code:

 

<?php
session_start();
$myusername=$_SESSION['myusername2'];
$mypassword=$_SESSION['mypassword2'];

require_once 'db_select.php';
$credit=$_GET['credit'];
$credit=explode('-',$credit);
$credit=$credit[0];

$sql_films="SELECT $tbl_name2.credit,$tbl_name.donor_id,$tbl_name2.film_number,$tbl_name4.item_id FROM $tbl_name JOIN $tbl_name2 JOIN $tbl_name4 ON ($tbl_name2.donor_id=$tbl_name.donor_id AND $tbl_name2.credit=$tbl_name4.position AND $tbl_name2.film_number=$tbl_name4.film_number) WHERE $tbl_name.username='$myusername' AND $tbl_name.password='$mypassword'";
$result_films=mysql_query($sql_films) or die(mysql_error());  
  
while($films_row=mysql_fetch_array($result_films)){
extract($films_row);
if($film_number==1 && $film_number==3){
$where="item_id='Seq$credit'";
}
elseif($film_number==1 && $film_number==2){
$where="item_id='Tril$credit'";
}
elseif($film_number==3 && $film_number==2){
$where="item_id='$credit'";
}
elseif($film_number==1){
$where="item_id='Seq$credit' OR item_id='Tril$credit'";
}
elseif($film_number==2){
$where="item_id='$credit' OR item_id='Tril$credit'";
}

elseif($film_number==3){
$where="item_id='$credit' OR item_id='Seq$credit'";
}
}



  $sql_second_credit="SELECT * FROM $tbl_name4 WHERE $where";
  $result_second_credit=mysql_query($sql_second_credit) or die(mysql_error());
  
  $select.='<select name="new_credit">'."\n";
  $select.='<option value="">Select Credit</option>'."\n";                
  while($second_row_credits=mysql_fetch_array($result_second_credit)){
  extract($second_row_credits);
       $select.='<option value="'.ucfirst($item_id).'">'.ucfirst($position).' - Film # '.$film_number.'</option>'."\n";
  }
$select.='</select> ';

echo $select;
?>

 

Still no luck in removing duplicates from the query.

 

I have Credit A for Films 1 and 2 in the database and it's populating the Select menu with Credit A for Films 1 and 3, when it should only be for film 3.

[HDFilmMaker2112]

Most recent:

 

<?php
session_start();
$myusername=$_SESSION['myusername2'];
$mypassword=$_SESSION['mypassword2'];

require_once 'db_select.php';
$credit=$_GET['credit'];
$credit=explode('-',$credit);
$credit2=$credit[0];

$sql_films="SELECT $tbl_name2.credit,$tbl_name.donor_id,$tbl_name2.film_number,$tbl_name4.item_id FROM $tbl_name JOIN $tbl_name2 JOIN $tbl_name4 ON ($tbl_name2.donor_id=$tbl_name.donor_id AND $tbl_name2.credit=$tbl_name4.position AND $tbl_name2.film_number=$tbl_name4.film_number) WHERE $tbl_name.username='$myusername' AND $tbl_name.password='$mypassword'";
$result_films=mysql_query($sql_films) or die(mysql_error());  
  
while($films_row=mysql_fetch_array($result_films)){
extract($films_row);
if($film_number==1 && $film_number==3){
$where="film_number='2'";
}
elseif($film_number==1 && $film_number==2){
$where="film_number='3'";
}
elseif($film_number==3 && $film_number==2){
$where="film_number='1'";
}
elseif($film_number==1){
$where="film_number='2' AND film_number='3'";
}
elseif($film_number==2){
$where="film_number='1' AND film_number='3'";
}
elseif($film_number==3){
$where="film_number='1' AND film_number='2'";
}
}

$sql_find_other_credits="SELECT $tbl_name4.*,$tbl_name2.* FROM $tbl_name4 JOIN $tbl_name2 ON($tbl_name4.position=$tbl_name2.credit) WHERE $where AND credit='$credit2'";
$result_find_other_credits=mysql_query($sql_find_other_credits);
$where2="";
while($other_credits_row=mysql_fetch_array($result_find_other_credits)){
extract($other_credits_row);
$where2.="item_id='$item_id'";
}

  $sql_second_credit="SELECT * FROM $tbl_name4 WHERE $where2";
  $result_second_credit=mysql_query($sql_second_credit) or die(mysql_error());
  
  $select.='<select name="new_credit">'."\n";
  $select.='<option value="">Select Credit</option>'."\n";                
  while($second_row_credits=mysql_fetch_array($result_second_credit)){
  extract($second_row_credits);
       $select.='<option value="'.ucfirst($item_id).'">'.ucfirst($position).' - Film # '.$film_number.'</option>'."\n";
  }
$select.='</select> ';

echo $select;
?>

 

Still having same issues.

[HDFilmMaker2112]

Any ideas?

 

Basically I need some way to get the credits that haven't already been bought. That could be up two credits. If they already bought film 1, then options for 2 and 3 should be there. If they already bought 1 and 2 then an option for only 3 should be there.