Databases
StackOverflow2013
Postsdbo
POAdding a number to MySQL Value If Found in DB
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdding a number to MySQL Value If Found in DB
    primarykey
    data
    text
    <p>I'm trying to figure out how to add a 1 ,2 or a 3 ect to the end of the username if it already exists. This is what I have so far, but I can't quite get it to add a number. It is simply inputting the username over and over.</p> <pre><code>require_once 'connect_to_mysql.php'; $username = substr($payer_email, 0, strpos($payer_email, '@')); $sql = mysql_query("SELECT username FROM transactions WHERE username='$username'"); $numRows = mysql_num_rows($sql); if ($numRows &gt; 0) { $i = 0; while ($name_arr = mysqli_fetch_assoc($user_query)) { $name = $name_arr['username']; $after = substr($name, strlen($username)); if (ctype_digit($after)) { if (($after = (int) $after) &gt; $i) { $i = $after; } } } if ($i &gt; 0) { $username .= $i; } } </code></pre> <p><strong>EDIT:</strong></p> <p>I'm still having trouble establishing a way to add a number to the end. Within my logic all I can think of is using the row count and using that number to append to the username if a duplicate exists. This is what I have so far. Any help is much appreciated. I've been stuck on this for two days now and can't quite find anything in my search on stackoverflow.</p> <pre><code>require_once 'connect_to_mysql.php'; $username = substr($payer_email, 0, strpos($payer_email, '@')); $sql = mysql_query("SELECT username FROM transactions WHERE username='$username'"); $numRows = mysql_num_rows($sql); if ($numRows &gt; 0) { $i = 0; while ($row = mysqli_fetch_assoc($sql)) { $username = "$username"; if($counter &gt; 0){ $username = $usernamename[$count]; $counter++; } else { $username == "$username"; } } } </code></pre> <p>One more edit:</p> <p>I think I may have overlooked a possible solution. Would just adding the id to the username be a solution worth thinking about? There can't be a duplicate there and it would make sense, right?</p> <pre><code>require_once 'connect_to_mysql.php'; $username = substr($payer_email, 0, strpos($payer_email, '@')); $sql = mysql_query("SELECT username FROM transactions WHERE username='$username'"); $numRows = mysql_num_rows($sql); if ($numRows &gt; 0) { $i = 0; while ($row = mysqli_fetch_assoc($sql)) { $id = $row["id"]; $username = $row['username']; if($counter &gt; 0){ $username = '.$username.''.$id.'; } else $username == "$username"; } } </code></pre> <p>The next update:</p> <pre><code>&lt;?php // Check to see there are posted variables coming into the script if ($_SERVER['REQUEST_METHOD'] != "POST") die ("No Post Variables"); // Initialize the $req variable and add CMD key value pair $req = 'cmd=_notify-validate'; // Read the post from PayPal foreach ($_POST as $key =&gt; $value) { $value = urlencode(stripslashes($value)); $req .= "&amp;$key=$value"; } // Now Post all of that back to PayPal's server using curl, and validate everything with PayPal // We will use CURL instead of PHP for this for a more universally operable script (fsockopen has issues on some environments) //$url = "https://www.sandbox.paypal.com/cgi-bin/webscr"; $url = "https://www.paypal.com/cgi-bin/webscr"; $curl_result=$curl_err=''; $ch = curl_init(); curl_setopt($ch, CURLOPT_URL,$url); curl_setopt($ch, CURLOPT_RETURNTRANSFER,1); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_POSTFIELDS, $req); curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/x-www-form-urlencoded", "Content-Length: " . strlen($req))); curl_setopt($ch, CURLOPT_HEADER , 0); curl_setopt($ch, CURLOPT_VERBOSE, 1); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE); curl_setopt($ch, CURLOPT_TIMEOUT, 30); $curl_result = @curl_exec($ch); $curl_err = curl_error($ch); curl_close($ch); $req = str_replace("&amp;", "\n", $req); // Make it a nice list in case we want to email it to ourselves for reporting // Check that the result verifies if (strpos($curl_result, "VERIFIED") !== false) { $req .= "\n\nPaypal Verified OK"; } else { $req .= "\n\nData NOT verified from Paypal!"; mail("chris@.com", "IPN interaction not verified", "$req", "From: chris@.com" ); exit(); } /* CHECK THESE 4 THINGS BEFORE PROCESSING THE TRANSACTION, HANDLE THEM AS YOU WISH 1. Make sure that business email returned is your business email 2. Make sure that the transaction’s payment status is “completed” 3. Make sure there are no duplicate txn_id 4. Make sure the payment amount matches what you charge for items. (Defeat Price-Jacking) */ // Check Number 1 ------------------------------------------------------------------------------------------------------------ $receiver_email = $_POST['receiver_email']; if ($receiver_email != "chris@.com") { $message = "Investigate why and how receiver email is wrong. Email = " . $_POST['receiver_email'] . "\n\n\n$req"; mail("chris@.com", "Receiver Email is incorrect", $message, "From: chris@.com" ); exit(); // exit script } // Check number 2 ------------------------------------------------------------------------------------------------------------ if ($_POST['payment_status'] != "Completed") { // Handle how you think you should if a payment is not complete yet, a few scenarios can cause a transaction to be incomplete } // Connect to database ------------------------------------------------------------------------------------------------------ require_once 'connect_to_mysql.php'; // Check number 3 ------------------------------------------------------------------------------------------------------------ $this_txn = $_POST['txn_id']; $sql = mysql_query("SELECT id FROM transactions WHERE txn_id='$this_txn' LIMIT 1"); $numRows = mysql_num_rows($sql); if ($numRows &gt; 0) { $message = "Duplicate transaction ID occured so we killed the IPN script. \n\n\n$req"; mail("chris@.com", "Duplicate txn_id in the IPN system", $message, "From: chris@.com" ); exit(); // exit script } // Check number 4 ------------------------------------------------------------------------------------------------------------ $product_id_string = $_POST['custom']; $product_id_string = rtrim($product_id_string, ","); // remove last comma // Explode the string, make it an array, then query all the prices out, add them up, and make sure they match the payment_gross amount $id_str_array = explode(",", $product_id_string); // Uses Comma(,) as delimiter(break point) $fullAmount = 0; foreach ($id_str_array as $key =&gt; $value) { $id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate product ID from its quantity $product_id = $id_quantity_pair[0]; // Get the product ID $product_quantity = $id_quantity_pair[1]; // Get the quantity $sql = mysql_query("SELECT price FROM products WHERE id='$product_id' LIMIT 1"); while($row = mysql_fetch_array($sql)){ $product_price = $row["price"]; } $product_price = $product_price * $product_quantity; $fullAmount = $fullAmount + $product_price; } $fullAmount = number_format($fullAmount, 2); $grossAmount = $_POST['mc_gross']; if ($fullAmount != $grossAmount) { $message = "Possible Price Jack: " . $_POST['payment_gross'] . " != $fullAmount \n\n\n$req"; mail("chris@.com", "Price Jack or Bad Programming", $message, "From: chris@.com" ); exit(); // exit script } // // require_once 'connect_to_mysql.php'; //now to always get unique username $username = substr($payer_email, 0, strpos($payer_email, '@')); if ( ! uniqueName($username)) { $username = makeUniqueName($username); } //function to check if is the existing username function uniqueName($username) { $sql = mysql_query("SELECT username FROM transactions WHERE username='$username'"); $numRows = mysql_num_rows($sql); if ($numRows &gt; 0) { return false; } return true; } //function to generate new unique username function makeUniqueName($username) { //serch username string for number at the end //regexp makes sure all preceeding zeroes go to first match group if (preg_match('/^(\S*?0*)?(\d+?)$/', $username, $match)) { //we got digit from the end of string, just add 1 to the digit $username = $match[1] . ($match[2] + 1); } else { //no digit at the end of string, just add digit 1 at the end $username = $username . 1; } if (uniqueName($username)) { return $username; } return makeUniqueName($username); } // END ALL SECURITY CHECKS NOW IN THE DATABASE IT GOES ------------------------------------ //////////////////////////////////////////////////// // Homework - Examples of assigning local variables from the POST variables $txn_id = $_POST['txn_id']; $payer_email = $_POST['payer_email']; $custom = $_POST['custom']; $first_name = $_POST['first_name']; $last_name = $_POST['last_name']; $payment_date = $_POST['payment_date']; $mc_gross = $_POST['mc_gross']; $payment_currency = $_POST['payment_currency']; $txn_id = $_POST['txn_id']; $receiver_email = $_POST['receiver_email']; $payment_type = $_POST['payment_type']; $payment_status = $_POST['payment_status']; $txn_type = $_POST['txn_type']; $payer_status = $_POST['payer_status']; $address_street = $_POST['address_street']; $address_city = $_POST['address_city']; $address_state = $_POST['address_state']; $address_zip = $_POST['address_zip']; $address_country = $_POST['address_country']; $address_status = $_POST['address_status']; $notify_version = $_POST['notify_version']; $verify_sign = $_POST['verify_sign']; $payer_id = $_POST['payer_id']; $mc_currency = $_POST['mc_currency']; $mc_fee = $_POST['mc_fee']; $password = mt_rand(1000, 9999); $p_hash = md5($password); $username = $_POST['makeUniqueName']; // // Place the transaction into the database $sql = mysql_query("INSERT INTO transactions (product_id_array, payer_email, first_name, last_name, payment_date, mc_gross, payment_currency, txn_id, receiver_email, payment_type, payment_status, txn_type, payer_status, address_street, address_city, address_state, address_zip, address_country, address_status, notify_version, verify_sign, payer_id, mc_currency, mc_fee, password, ip, username) VALUES('$custom','$payer_email','$first_name','$last_name','$payment_date','$mc_gross','$payment_currency','$txn_id','$receiver_email','$payment_type','$payment_status','$txn_type','$payer_status','$address_street','$address_city','$address_state','$address_zip','$address_country','$address_status','$notify_version','$verify_sign','$payer_id','$mc_currency','$mc_fee','$p_hash','$ip','$username')") or die ("unable to execute the query"); $to = $payer_email; $subject = ' | Login Credentials'; $message = ' Your officially all ready to go. To login use the information below. Your account login information ------------------------- Email: '.$payer_email.' Password: '.$password.' ------------------------- You can now login at https://www..com/signin.php'; $headers = 'From:noreply@.com' . "\r\n"; mail($to, $subject, $message, $headers); mysql_close(); // Mail yourself the details mail("chris@.com", "NORMAL IPN RESULT YAY MONEY!", $req, "From: chris@.com"); ?&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChris
    1. USChris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COctype_digit returns true if the parameter is a digit - this will return false for names like "myusername" - meaning $i will remain at 0 and $username will not be appended to. Edit: Misread the code. Debug it and check the value of your variables and trace through your logic
      singulars
      1. POAdding a number to MySQL Value If Found in DB
      1. USandrewb
    2. COthanks for the comment andrew. I'm a bit confused on how to reslove this. Would it be something as simple as removing the ctype if?
      singulars
      1. POAdding a number to MySQL Value If Found in DB
      1. USChris
    3. COWell doesn't the username exist if the query returns a positive result? If so then you don't need all your checks, just append a number.
      singulars
      1. POAdding a number to MySQL Value If Found in DB
      1. USRevent
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload