PHP - Sanitised Code Stopping At &

I have below script. it does the while loop 100%. it updates the mysql database one at a time as it should. the problem I have now is that the while loop does not end and go to the next statement as it should. it keeps pollong the database. so when in back end you change to 0 it automatically updates again.

Please see if you can help me to see where I can stop this while loop when there are no more loops

// Here I select the amount of rows

$sql_query = "SELECT ae FROM `debitorderrejectionimport` WHERE ae = '0'"; 
$rowCount = mysqli_query($conn,$sql_query); 
$rowCountUpdate = mysqli_num_rows($rowCount); 
echo $rowCountUpdate; 
while($rowCountUpdate > 0)
{ $sql = "UPDATE `ttee`.`au1` 
INNER JOIN `ttee`.`au` ON (`au1`.`id` = `au`.`id`) 
INNER JOIN `ttee`.`ae1` ON (`ae1`.`idd` = `au1`.`idd`) 
INNER JOIN `ttee`.`debitorderrejectionimport` ON (`debitorderrejectionimport`.`nr` = `ae1`.`id`) 
SET `au`.`amount` = `ae1`.`amount` + `au1`.`amount`, `debitorderrejectionimport`.`ae` = au.id ;"; 

$result = mysqli_query($conn, $sql);
$updated = mysqli_affected_rows($conn); 
$rowCountUpdate - ($updated);}

// if it finished updating and there is no more rows it must continue with below query

mysqli_query($conn, " 
INSERT INTO `sataxicrm754`.`debitorderrejectionimport_back` 
( `Outbound`, `Allocation`, `AccountName`, `QueryComplaintType`,
`QueryStatus`, `Querytypeoption`, `Description`, `DealID`, 
`Deals`, `Assignedusername`, `Teams`, `CampaignName`, `CampaignID`, 
`inserted`, `idnumber`, `nr`, `datew`, `premium`, `policynumber`,
`ContactNumber`, `CollectionType`, `OpportunityAmount`, `Broker`, 
`impref`, `id` ) SELECT `Outbound`, `Allocation`, `AccountName`, 
`QueryComplaintType`, `QueryStatus`, `Querytypeoption`, `Description`, 
`DealID`, `Deals`, `Assignedusername`, `Teams`, `CampaignName`, 
`CampaignID`, `inserted`, `idnumber`, `nr`, `datew`, `premium`, 
`policynumber`, `ContactNumber`, `CollectionType`, `OpportunityAmount`, 
`Broker`, `impref`, `id` FROM `sataxicrm754`.`debitorderrejectionimport` 
WHERE QueryComplaintType <> 'QueryComplaintType' ");

 

I have the following code that cycles though and prints out the day of the week and stops printing after the seventh day is reached, but it keeps looping. How do I stop it from looping after 7? I thought I had it right, but it's not. Can anyone help?


Code: [Select]

<?php


$weekdays = array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday");


echo current($weekdays) . "<br />";
while (count($weekdays) < 8) {
echo next($weekdays) . "<br />";
}


?>

I have a script which registers people to a database, however it doing something very strange.  Whenever I place it into even a single CSS div it adds the registration to the dabase but stops loading the next page (index.php)

I find this totally bizarre as I haven't seen anything like this behave before.  With CSS affecting how PHP works.  Especially when the CSS is not inside the <php> of <form> tags.

Code: [Select]
<?php

include("connect.php");

if($_POST['submit']) {
    $username = mysql_real_escape_string(trim($_POST['username']));
    $password = trim($_POST['password']);
    $password2 = trim($_POST['password2']);
    $email = mysql_real_escape_string(trim($_POST['email']));
    $error = false;
    
    if(!isset($username) || empty($username)) {
        $error = "You need to enter a username.";
    }
    $query = mysql_query("SELECT id FROM users WHERE username = '".$username."' LIMIT 1");
    if(mysql_num_rows($query) > 0 && !$error) {
        $error = "Sorry, that username is already taken!";
    }
    
    if((!isset($password) || empty($password)) && !$error) {
        $error = "You need to enter a password.";
    }
    if((!isset($password2) || empty($password2)) && !$error) {
        $error = "You need to enter your password twice.";
    }
    if($password != $password2 && !$error) {
        $error = "The passwords you entered did not match.";
    }
    
    if((!isset($email) || empty($email)) && !$error) {
        $error = "You need to enter an email.";
    }
    if(preg_match("/[a-zA-Z0-9-.+]+@[a-zA-Z0-9-]+.[a-zA-Z]+/", $email) == 0 && !$error) {
        $error = "The email you entered is not valid.";
    }
    $query = mysql_query("SELECT id FROM users WHERE email = '".$email."' LIMIT 1");
    if(mysql_num_rows($query) > 0 && !$error) {
        $error = "Sorry, that email is already in use!";
    }
    
    if(!$error) {
        $query = mysql_query("INSERT INTO users (username, password, email) VALUES ('".$username."', '".mysql_real_escape_string(md5($password))."', '".$email."')");
        if($query) {
            $message = "Hello ".$_POST['username'].",\r\n\r\nThanks for registering! We hope you enjoy your stay.\r\n\r\nThanks,\r\nJohn Doe";
            $headers = "From: ".$website['name']." <".$website['email'].">\r\n";
            mail($_POST['email'], "Welcome", $message, $headers);
            setcookie("user", mysql_insert_id(), $time);
            setcookie("pass", mysql_real_escape_string(md5($password)), $time);
            header("Location: index.php");
        } else {
            $error = "There was a problem with the registration. Please try again.";
        }
    }
}

?><html>

<head>
<title>Register</title>
</head>

<body>
<form action="" method="post">
    <?php if($error) echo "<span style=\"color:#ff0000;\">".$error."</span><br /><br />"; ?>
    <label for="username">Username: </label> <input type="text" name="username" value="<?php if($_POST['username']) echo $_POST['username']; ?>" /><br />
    <label for="password">Password: </label> <input type="password" name="password" value="<?php if($_POST['password']) echo $_POST['password']; ?>" /><br />
    <label for="password2">Retype Password: </label> <input type="password" name="password2" value="<?php if($_POST['password2']) echo $_POST['password2']; ?>" /><br />
    <label for="email">Email: </label> <input type="text" name="email" value="<?php if($_POST['email']) echo $_POST['email']; ?>" /><br /><br />
    <input type="submit" name="submit" value="Register" />
</form>
</body>

I'm writing PHP for my son's cub scout pack.  It populates the MySQL db users table from data contained in a csv file. 
So far, it puts the scout's info in just fine...until I try to run another query as a nested query to retrieve the auto-generated userID for the scout that was just placed in the DB.

The result of this if I don't include the second query--the one trying to get the new userID for that scout, it iterates through the whole file as expected and puts all the scouts in the users table.  The second I uncomment the 2nd query and try to get it to run, it only puts the 1st scout in the DB, properly retrieves the newly generated userID for him, and then stops. 

I need to have this userID for the scout to properly associate that scout with the parents later on and in other sections of this project. 

the db_connect() function just creates the PDO object and contains the username, etc for the connection.  That part is working fine so I'm not posting that function here.

I've tried renaming the connection, query, etc to add "ID" to the end to ensure I wasn't trampling on var names, but that hasn't made a difference


Code: [Select]
//include necessary files:
 include_once("../../sys/php/includes/data_fns.php");
 
 //start session
 session_start();
$conn = db_connect();
$bufferConn = $conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);



////////////////////////////////////////
//  Read through the .csv file and populate the users table
/*  Uses the model from the php documentation on fgetcsv:

 *  $row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}

 */
////////////////////////////////////////
$row = 0; //set the row counter
$table1 = "<table id='csvTable'>"; //start the table HTML markup
if(($handle= fopen("../../sys/source/pack238.csv", "r"))!== FALSE) //open the csv file
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) //fgetcsv reads through the csv file line by line
{
//ignore the first row which has the header data--we don't want that in the database
if($row==0){$row++; continue;}
$num = count($data);
//get the scout's info, check to see if he's already in the db, then put it in if not--the scout should NOT already be in db when we run this for the first time, but later versions will only add new scouts.  For now, just alert us if they're already in and halt
//TODO  Figure out why this still runs if the table name doesn't exist!!
$query = "SELECT * FROM users WHERE fName=:firstName && lName=:lastName";
$stmt = $conn->prepare($query);
$stmt->execute(array(':firstName'=>$data[1],
    ':lastName'=>$data[0]));
$dbCount=0;
while ($stmt->fetch()){$dbCount++;}
if($dbCount >0) //we found this scout--he shouldn't be in the DB.  Halt the process and check what's going on
{
echo "<br/>The scout, $data[1] $data[0] was found in the database already.  We cannot continue. 
<b/>Please check this duplicate data and try running this setup again";
die();
}
else //he's not in the DB....put him in there
{
//get the Den unit as a number, dropping "Den"
$den = substr($data[10],4);
//get birthday as something to be used as PHP date object
if($data[7]!="")
{
$bdayO = date_create($data[7]);
$now = date_create("now");
$interval = date_diff($bdayO, $now);
$age = $interval->format('%y years');
$bday = $bdayO->format('Y-m-d');
}
else{
$bdayO = NULL;
$bday = NULL;
$age = NULL;
}
echo "<br/> Test message:  The scout $data[1] $data[0], Den:$den would be put into the database!  His bday is:$bday. AGE IS: $age";

$query = "insert into users (gen,fName,lName, nName,memberOf,bday) VALUES ('M',:firstName,:lastName,:nickName,:den,:bday)";
$stmt= $conn->prepare($query);
$stmt->execute(array(':firstName'=>$data[1],
':lastName'=>$data[0],
':nickName'=>$data[3],
':den'=>$den,
':bday'=>$bday));
//get the new userID for this scout now that he's in the db
$connID = db_connect();
$queryID = "SELECT userID from users WHERE fName=:firstName && lName=:lastName";
$stmtID = $connID->prepare($queryID);
$stmtID->execute(array(':firstName'=>$data[1],
':lastName'=>$data[0]));
while ($row = $stmtID->fetch())
{
$scoutUserID = $row[0];
echo "<br/>This scout's userID is: $scoutUserID";
}

}//end else
//get the father's info, check to see if he's already in the db, then put it in if not
//if he is in the db, get this scout's userID number and add it to the father's children array
$query = "SELECT * FROM users WHERE fName=:firstName && lName=:lastName";
$stmt = $conn->prepare($query);
$stmt->execute(array(':firstName'=>$data[18],
    ':lastName'=>$data[19]));
$dbCount=0;
while ($stmt->fetch()){$dbCount++;}
if($dbCount >0) //This father is already in the DB...we need to add this scout to his children array
{
echo "<br/>For testing purposes, this father is already in the DB...let's add this scout, ".$data[1]." to his children array";
//get the userID of the current scout
}
else //he's not in the DB....put him in there
{}
//get the mother's info, check to see if he's already in the db, then put it in if not
//if she is in the db, get this scout's userID number and add it ot the mother's children array
}// end while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
}//end if(($handle= fopen("../../sys/source/pack238.csv", "r"))!== FALSE)

how do i  stop multiple duplications in database on my PHP script?



ok i have attached a screenshot of what the database looks like after a few runs of my script. the script is designed to pull api information, input into 1 database and update another user table.
i have made it run as a cron job every 60 minutes.

here is my code:
<?php

/* You need multiple instances of this script. Each instance runs once every hour so 6 instances means one 
runs every 10 mins. Remember to change the API URL to reflect the different accounts or characters.*/
include "connect.php";

$columns = "`date` , `refID`, `refType`, `ownerName1`, `ownerName2`, `argName1`, `amount`, `balance`, `reason`";

//Live URL is 

//Assumeing that they are only donating at this time and no one is being paid to reduce the balance. Balance reduction can be done in the prize claim script so its not API delayed.
if ( ($data[2] == "Player Donation") && ($data[4] == "Ship Lotto")){
$reUsed = mysql_query("SELECT * FROM bank WHERE refID='$data[1]';");
if(!empty($reUsed)){
$import="INSERT into bank($columns) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]')";
mysql_query($import) or die(mysql_error());
}

/*check to see if the player has already been credited. It checks the last recorded reference #
and checks to see if the new ref # is greater, else skips the processing. You need to check since 
the API gives you the last 1000 journal entries or 1 week, what ever is shorter. Not just what is 
new since last check. Check is performed by seeing if the record in the database for the user is 
less then or equal to the new once. This works only because CCP's reference #s are auto increasing 
so they only go up if they are newer, never down.*/
$name = $data[3];
//echo "Updating account of ".$name."<br />";
$queryLastRef = mysql_query("SELECT lastRef FROM users WHERE username='$name';") or die(mysql_error());
//echo $queryLastRef;
$arraylastRef = mysql_fetch_assoc($queryLastRef);
$lastRef = $arraylastRef["lastRef"];
//echo "The last reference # was: ".$lastRef."<br />";
$currentRef = $data[1];
//echo "The current reference # is: ".$currentRef."<br />";
if($lastRef<$currentRef){
$amount = $data[6];
//echo "Player deposited ISK in the amount of: ".$amount."<br />";
$queryBal = mysql_query("SELECT user_iskbalance FROM users WHERE username='$name';") or die(mysql_error());
//echo "Executing the SQL command to query balance ID#: ".$queryBal."<br />";
$getBal = mysql_fetch_assoc($queryBal);
//echo "Executing the SQL command to get balance amount: ".$getBal["user_iskbalance"]."<br />";
$deposit = $amount+$getBal["user_iskbalance"];
//echo "Depositing ISK in the ammount of: ".$deposit."<br />";
$importBal= "UPDATE users SET user_iskbalance=$deposit WHERE username='$name';";
//echo "Executing the SQL command to desposit: ".$importBal."<br />";
mysql_query($importBal) or die(mysql_error());
$importRefID= "UPDATE users SET lastRef='$currentRef' WHERE username='$name';";
//echo "Executing the SQL command to set the new reference: ".$currentRef."<br />";
mysql_query($importRefID) or die(mysql_error());
//echo "Success!"."<br />";

//For the sake of stats tracking update the total isk on deposit. The payout script will subtract.

$queryiskDeposit = mysql_query("SELECT iskDeposit FROM stats;") or die(mysql_error());
//echo "Executing the SQL command to query the ISK deposited : ".$queryiskDeposit."<br />";
$arrayiskDeposit = mysql_fetch_assoc($queryiskDeposit);
$getiskDeposit = $arrayiskDeposit["iskDeposit"];
//echo "Got total isk on deposit of: ".$getiskDeposit."<br />";
$iskDeposit = $getiskDeposit+$deposit;
//echo "Inserting: ".$iskDeposit." ISK"."<br />";
$importiskDeposit= "UPDATE stats SET iskDeposit='$iskDeposit';";
//echo "Executing the SQL command to desposit: ".$importBal."<br />";
mysql_query($importiskDeposit) or die(mysql_error());
//echo "<br />";
//echo "<br />";
//echo "NEXT!<br />";
//echo "<br />";
}
else{
//echo "There is no update for ".$name." because ".$lastRef." is not less then or equal to ".$currentRef."<br />";
//echo "<br />";
//echo "<br />";
//echo "NEXT!<br />";
//echo "<br />";
}
//echo "DEBUG for ".$name." lastRef ".$lastRef." and currentRef ".$currentRef."<br />";

//update the time that last update ran
$today = date("Ymd G:i");
mysql_query("UPDATE stats SET iskLastUpdate='$today';") or die(mysql_error());
//echo "Updating Date to: ".$today;
//echo "<br />";
//echo "<br />";
//echo "NEXT!<br />";
//echo "<br />";
}
}
?>


can anyone help me stop it duplicating the entries in the database please?