PHP - Constraining The # Of Columns Of An Existing Table

I have a table with lots of records. My table has the following field, with records containing either NULL or '1' that I need to modify.

I'm racking my brain trying to figure out the most effective way (any way) to insert some information into a table if it hasn't been previously inserted. Here's a simplified sample that I'm working with:
--

Column 1      Column 2     Coloumn3
Sue               Cat              Adopted
Jack               Dog             Adopted
Cujo              Dog             Not Adopted

--

The column I'm trying to scrub my loop against is column 3. Here's what I have so far...


<?PHP 

$question = "ADOPTED";

$res = mysql_query("SELECT file FROM temp_data");

while($rw = mysql_fetch_array($res)){

$arr[] = $rw['file'];

if (in_array($question, $arr)){
 echo "ADOPTED";

//Leave it alone...

} else {
echo "NOT ADOPTED<br />";

//Insert My New Data

        }
?>



I think I'm on the right path by using the 'in_array' function to check if '$question' variable is within the the '$arr' array, however, logically (to me) it makes sense but it is not working. Suggestions?

I should also mention that I'm not receiving any error messages but rather, a completely inaccurate result instead.

Im not much of a coder, and I obviously did this wrong since it is not working. Im trying to bring a new field value in to an existing php file. I created a new table, and an admin page to control that field. I cant, however, figure out how to use that value in a different php file. Actually, there are 2 files. Each using a different field from that new table. I will post what my current code is below with the additions I tried to add. I bolded the lines that contain the tags Im trying to use. Any and help you can give in fixing this to make it work is greatly appreciated.

Code: [Select]
<?php

# INCLUDE ALL NECESSARY FILES.
include_once("Constants.php");
include_once("Database.php");
include_once("SiteSetting.php");

[b]$query = "SELECT arcade_multiplier FROM tbl_point_rewards";
mysql_query($query) or die("Invalid query". mysql_error() );
$result = mysql_fetch_array($result);
$arcade_multiplier = $result["arcade_multiplier"];[/b]

$dbObj = new database();

# SAVE ARCADE GAME POINTS INTO THE DATABASE
function saveGamePoints( $game_id, $user_id, $game_score, $calculate_score_flag, $play_date ){
global $dbObj;

$user_max_plays = getUserMaxPlaysLimit( $user_id, $game_id );
//$_SESSION["arcade_game"]["save_score"] = ($user_max_plays < $_SESSION["arcade_game"]["max_plays"]) ? "yes" : "no" ;

if($user_max_plays < $_SESSION["arcade_game"][$_SESSION["url_gameid"]]["max_plays"]){

$total_points = calculatePointRatio($game_id, $game_score);
//$total_points = $game_score;

$x_gameid = md5($game_id);
if($_SESSION["arcade_game"][$x_gameid]["play_time"] > 0){
$playTime['start'] = $_SESSION["arcade_game"][$x_gameid]["play_time"];
$playTime['end'] = time();
$playTime = ($playTime['end'] - $playTime['start']);
}
else
{
$playTime = 0;
}

# update total points into main score
[b] $update_sql = "update tbl_users set points_gained = (points_gained+(".ceil($total_points)." * $arcade_multiplier)) where usrid={$user_id}";[/b]
$rs = mysql_query($update_sql) or die("Invalid query: {$update_sql} - ". mysql_error());

$table  = "tbl_arcade_game_score";
$fields  = array("game_id", "user_id", "game_score", "calculate_score_flag", "play_date", "game_time");
$values  = array($game_id, $user_id, $game_score, $calculate_score_flag, $play_date, $playTime);

//  return $dbObj->cgi( $table, $fields, $values, 1 );

$insertid = $dbObj->cgi( $table, $fields, $values, "" );

$ceiling_point =  getCeiling($game_id);

if($game_score >= $ceiling_point)
{
$chk_authourity = isAlreadyTrophyOwner($user_id, $game_id);
//if($chk_authority < 1)
if($chk_authourity < 1)
{
# store award details into the database (tbl_trophy_award) 
//$trophy_id  = getTrophy( $game_id );
$trophy = getTrophyDetails($game_id);
$trophy_id = $trophy['ctoon_id'];
$tbl_fields = array('userid', 'game_id', 'award_date', 'trophy' );
$tbl_values = array($user_id, $game_id, $play_date,  $trophy_id );
$insert_tbl_trophy_award_id = $dbObj->cgi('tbl_trophy_award', $tbl_fields, $tbl_values , "" );

# award trophy to user (tbl_users_ctoons)
$table  = "tbl_users_ctoons";
$fl = array("usrid", "ctoon_id", "del_status", "ctoons_auction", "auction_id", "location", "batpts");
$vl = array($user_id, $trophy_id, '0', '', 0, 1, 0);
$dbObj->cgi($table, $fl, $vl , "" );

# send a pm to the user about the trophy
include_once("send_pm_message.php");
$game = getGameDetails($game_id);

$msgSub  = "Grats! You just got a trophy!";
$message = "Grats! You got the ".$trophy["toon_name"]." trophy for the  ".$game["game_name"]." game!<br>
<img src=\"{$game['path']}\">";

  //echo $message;
  //echo "<br><hr><br>";
sendMessage($user_id, 0, 0, $msgSub, $message, 1);
sendMessage(2034, $user_id, 0, $msgSub, $message, 1);

}
}

return $insertid;
}
}//end function

function getCeiling($game_id)
{
$sql = "SELECT ceiling FROM tbl_game WHERE game_id = '".$game_id."'"; 

$rs = mysql_query($sql);

$result = mysql_fetch_assoc($rs);

return $result['ceiling'];
}

function isAlreadyTrophyOwner($user_id, $game_id)
{
$sql  = "SELECT * FROM tbl_trophy_award WHERE game_id = {$game_id} and userid = {$user_id}"; 

$rs  = mysql_query($sql);
$num_rows  = 0;
$num_rows  = mysql_num_rows($rs);

if($num_rows > 0)
{
return $num_rows;
}
else
{
return $num_rows;
}
}

# calculate game ratio
function calculatePointRatio($game_id, $game_score){
//return $game_score;
$sql = "select gs.* from arcade_game_setting as gs where gs.game_id={$game_id}";
$rs = mysql_query($sql) or die( "Invalid query". mysql_error() );
if($rs){
$row = mysql_fetch_array($rs);
switch($row["calculation_type"]){

case "divide":
$x_score =  round($game_score / $row["r_no"]);
break;

case "multiply":
$x_score = round($game_score * $row["r_no"]);
break;

default :
$x_score = ceil($game_score);
break;

}//switch
$row["r_no"];
if($row["game_cap"] > 0){
if($x_score > $row["game_cap"]){
$x_score = $row["game_cap"];
}
}
return $x_score;
}//if
}// end function


# get user total plays 
function getUserMaxPlaysLimit($user_id, $game_id){
global $d_start_time, $d_end_time;
$sql = "select * from tbl_arcade_game_score where user_id={$user_id} and game_id={$game_id} and play_date between {$d_start_time} and {$d_end_time}";
$rs = mysql_query($sql) or die("Invalid query :". mysql_error() );
if($rs){
return mysql_num_rows($rs);

}//if
}

# get game details
function getGameDetails($game_id){
$sql = "select g.game_id, g.game_name, g.award_ctoonid, c.toon_name, c.toon_cat, c.toon_subcat, c.toon_small_img from tbl_game as g, tbl_ctoon as c where g.game_id = {$game_id} and g.award_ctoonid=c.ctoon_id";
$rs = mysql_query($sql);
if($rs){
$row = mysql_fetch_array($rs);

if($row['toon_cat']!="" && $row['toon_subcat']!=0)
$tpth="imgtoon/".$row['toon_cat']."/".$row['toon_subcat']."/thumb";
elseif($row['toon_cat']!="" && $row['toon_subcat']==0)
$tpth="imgtoon/".$row['toon_cat']."/thumb";

$game["game_name"]  = $row["game_name"];
$game["award_ctoon_id"]  = $row["award_ctoonid"];
$game["toon_name"] = $row["toon_name"];
$game["path"] = SITEROOT ."/" . $tpth ."/" . $row["toon_small_img"];


return $game;
}
else
{
return false;
}

}

# send pm message to winner
function sendPMToWinner($user_id, $game_id, $game_score){
include_once("send_pm_message.php");
$g = $game_id;
//$point_prize = calculatePointRatio($game_id, $game_score);
$point_prize = $game_score;
$game = getGameDetails($game_id);

//# select ctoon
//$sql = "select award_ctoonid from tbl_game where game_id=".$g;
//$rsctoon = mysql_query($sql) or die("Invalid query: {$sql} - ". mysql_error());
//$row_ctoon = mysql_fetch_array($rsctoon);
//$ctoon_id = $row_ctoon["award_ctoonid"];

//# award ctoon
//$insert_user_ctoon = "insert into tbl_users_ctoons (usrid, ctoon_id, del_status, ctoons_auction, auction_id, location, batpts)
//values('{$user_id}', '{$ctoon_id}', '0', '', '', '1', '0')";
//mysql_query($insert_user_ctoon) or die("Invalid insert query:".mysql_error() );

//# update points
$update_points_sql = "update tbl_users set points_gained = (points_gained+1500) where usrid={$user_id}";
mysql_query($update_points_sql) or die("Invalid updates points query". mysql_error() );


$dbObj  = new database();
$msgSub  = "Grats! You got a Top Score for ".$game["game_name"]." !";
$message = "Grats! You had one of the Top 10 scores in the ".$game["game_name"]." game last month, and got a 1500 point bonus!<br>";
//<img src=\"{$game['path']}\">";

//  echo $message;
//  echo "<br><hr><br>";
sendMessage($user_id, 0, 0, $msgSub, $message, 1);

}



/**
* function getTrophy( $game_id )
* @param $game_id
*/
function getTrophy( $game_id ){
$sql = "select award_ctoonid from tbl_game where game_id=".$game_id;
$rs = mysql_query( $sql ) or die( mysql_error() );
if( $rs ){
$row = mysql_fetch_assoc( $rs );
}

return $row["award_ctoonid"];

}

// Updated function from above
// Will get more information about the trophy
function getTrophyDetails($game_id)
{
$sql = "select b.ctoon_id,b.toon_name,b.toon_small_img from tbl_game as a,tbl_ctoon as b where a.game_id = '$game_id' and b.ctoon_id = a.award_ctoonid LIMIT 1";
$rs = mysql_query( $sql ) or die( mysql_error() );
if( $rs ){
$row = mysql_fetch_assoc( $rs );
}

return $row;
}

?>

Code: [Select]
<?php

include_once("Constants.php");
include_once("Database.php");
include_once("SiteSetting.php");

/**************************************************/
# SAVE DONETION IN DATABASE
# @param $uid
# @param $donation_amt
/**************************************************/
function saveDonetion($uid, $donation_amt, $transaction_id, $subscr_id='', $type){

if($transaction_id != "")
        {
    $flag = checkExist($uid);
         
    if($flag > 0){
    updateDonetion($uid, $donation_amt, $transaction_id, $subscr_id='', $type);
    }else{
    addDonetion($uid, $donation_amt, $transaction_id, $subscr_id='', $type);
    }
        }

}
/**************************************************/


/**************************************************/
# CHECK IS RECORD EXITS IN DONETION TABLE
# @param $uid
/**************************************************/
function checkExist($uid){

$dbObj = new database();

$sql = "select user_id from tbl_donation where user_id={$uid}";
$dbObj->myquery($sql);

if($dbObj->row_count > 0){
return $dbObj->f_user_id;
}else{
return 0;
}
}
/**************************************************/



/**************************************************/
# INSERT DONETION RECORD IN tbl_donation
# @param $uid
# @param $donation_amt
/**************************************************/

[b]$query = "SELECT arcade_multiplier FROM tbl_point_rewards";
mysql_query($query) or die("Invalid query". mysql_error() );
$result = mysql_fetch_array($result);
$arcade_multiplier = $result["arcade_multiplier"];[/b]


function addDonetion($uid, $donation_amt, $transaction_id, $subscr_id='', $type){

$dbObj = new database();

$months = floor($donation_amt / 5);
$exp_time = mktime(date("H"),date("i"),date("s"),date("m") + $months,date("d"),date("Y"));

$fields = array("transaction_id", "subscr_id", "user_id", "donation_amt", "donation_date", "exp_date", "donation_type", "transaction_details");
$values = array($transaction_id, $subscr_id, $uid, $donation_amt, time(), $exp_time, $type, serialize($_SESSION["recurring_td"]));

# update points
[b]$update_points_sql = "update tbl_users set points_gained = (points_gained+(($donation_amt / 5) * $donation_pts)) where usrid={$uid}";/[b]
mysql_query($update_points_sql) or die("Invalid updates points query". mysql_error() );


if($donation_amt < 5){
// The user donated less than $5

}else{
upgradeAccount($uid);
}

return $dbObj->cgi("tbl_donation", $fields, $values, false);
}
/**************************************************/


/**************************************************/
# UPDATE EXISTING DONETION RECORD
# @param $uid
# @param $donation_amt
/**************************************************/
function updateDonetion($uid, $donation_amt, $transaction_id, $subscr_id='', $type){

if(strlen($subscr_id) > 0){
$cd = " AND subscr_id='".$subscr_id."'";
}
$sql_select = "select exp_date, donation_amt from tbl_donation where user_id=".$uid;
//$sql_select = "select exp_date, donation_amt from tbl_donation where user_id=".$uid." and donation_type like '%recurring%'";
$rs_select  = mysql_query($sql_select) or die("Invalid query".mysql_error());
if($rs_select){
$row  = mysql_fetch_array($rs_select);
$expdt  = $row["exp_date"];
$damt  = ($row["donation_amt"] + $donation_amt);
$months = floor($donation_amt / 5);
$exp_time = mktime(23, 59, 59, date("m",$row["exp_date"]) + $months, date("d", $row["exp_date"]), date("Y", $row["exp_date"]));
}

$dbObj = new database();
$sql = "update tbl_donation set 
transaction_id='".$transaction_id."', 
donation_amt=(donation_amt + {$donation_amt}), 
donation_date = ".time().", 
exp_date=".$exp_time.", 
donation_type='".$type."', 
transaction_details='".serialize($_SESSION["recurring_td"])."' 
where user_id={$uid}";

# update points
[b]$update_points_sql = "update tbl_users set points_gained = (points_gained+(($donation_amt / 5) * $donation_pts)) where usrid={$uid}";[/b]
mysql_query($update_points_sql) or die("Invalid updates points query". mysql_error() );

$dbObj->myquery($sql, 1);

if($donation_amt < 5){
// The user donated less than $5

}else{
upgradeAccount($uid);
}

}
/**************************************************/




/**************************************************/
# UPDATE EXISTING RECURRING DONETION RECORD
# @param $uid
# @param $donation_amt
/**************************************************/
function updateRecurringDonetion($subscr_id, $transaction_amt){

$sql_subscr = "select donation_id, transaction_id, user_id from tbl_donation where subscr_id = '".$subscr_id."'";
$rs_subscr  = mysql_query($sql_subscr) or die("Invalid query".mysql_error());
if(strlen($rs_subscr) > 0){
$row_subscr = mysql_fetch_assoc($rs_subscr);
$uid = $row_subscr["user_id"];
$donation_amt = $transaction_amt;
$transaction_id = $row_subscr["transaction_id"];
$subscr_id = $subscr_id;
$type = "Recurring";
updateDonetion($uid, $donation_amt, $transaction_id, $subscr_id='', $type);
}
}
/**************************************************/






/**************************************************/
# UPDATE USER STATUS FROM STANDERED TO PREMIUM
# @param $uid
/**************************************************/
function upgradeAccount($uid){

$dbObj = new database();

$sql = "UPDATE tbl_users SET acc_type = 'P' WHERE usrid={$uid} LIMIT 1 ";

# update points
$update_points_sql = "update tbl_users set points_gained = (points_gained+0) where usrid={$uid}";
mysql_query($update_points_sql) or die("Invalid updates points query". mysql_error() );

$dbObj->myquery($sql, 1);

}//function upgradeDonation($uid)

?>

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=348130.0

Hi guys, im inserting data into the table using drop-down list & multi select list,well it works very well. but i need to make sure i should not insert same StudentID & CourseID twice. here my code for you could anyone tell me pls where should i write code to check existing data?
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


  mysql_select_db("uni", $con)or trigger_error('MySQL error: ' . mysql_error());
$result  = mysql_query("SELECT * FROM student") or trigger_error('MySQL error: ' . mysql_error());


echo '<select name="sid">';

while($row = mysql_fetch_array($result))
{

    echo '<option value="' . $row['StudentID'] . '">' . $row['StudentName'] . '</option>';
}

echo '</select>';

// ----------------

?>
</div>
<div class="style41" id="Layer7">

<?php
$result  = mysql_query("SELECT * FROM course") or trigger_error('MySQL error: ' . mysql_error());


echo '<select name ="cid[]" multiple="multiple" size="10">';

while($row = mysql_fetch_array($result))
{
    echo '<option value="' . $row['CourseID'] . '">' . $row['CourseName'] . '</option>';
}

echo '</select>';
mysql_close($con);
?>


------------------------------------
<?php

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
  mysql_select_db("uni", $con)or trigger_error('MySQL error: ' . mysql_error());
   
 if (!empty($_POST['sid']) && !empty($_POST['cid'])) 
{
    $ct = 0;
    $student = $_POST['sid'];
    foreach ($_POST['cid'] as $key => $course)
    {
     $sql = "INSERT INTO take (StudentID, CourseID) VALUES('".mysql_real_escape_string($student)."',
 '".mysql_real_escape_string($course)."')";
     $query = mysql_query($sql) or trigger_error('MySQL error: ' . mysql_error());
     if (mysql_affected_rows() > 0){$ct++;}
     }
    echo $ct . ' rows added.';
 }  

mysql_close($con);

?>