PHP - Php/mysql Query To Block Duplicate Entries

Hi all,   Its been a long time since last help request from real professional from here but I'm again in trouble with a much more spectacular plan I'm working on.

For those who are interested in the plan then here it is:
My Idea was to make a new build starting from scratch and make it as dynamical as possible. So my goal is not to make almost anything fixed in the code. I have made a decision to make a one supper large table for multiple different entries so no more joining and no more views for me.! In this help request I'm having trouble with Posting values to a page processing page lets call it record_changer.php The sole purpose of this file is to get form posts and decide what to do. Either update, delete, or insert.

record_changer.php
 

<?php

include '../../config/config.inc.php';

if(is_ajax()){
# Checks if action value exists
  if(isset($_POST["action"]) && !empty($_POST["action"])){
    $action = $_POST["action"];
# Switch case for value of action
    switch($action){
			case "insert": datatable_insert_function();
			break;
			case "update": datatable_update_function();
			break;
			case "delete": datatable_delete_function();
			break;
    }
  }
}

# Function to check if the request is an AJAX request
function is_ajax(){ return isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest'; }

function datatable_insert_function(){
}

function datatable_update_function(){
}

function datatable_delete_function(){
}

?>

The problem.

The problem is that the $insert places two entries to the DB. I cant seem to understand why.?

	# Test _POST values
	$_POST['UserID'] = '2';
	$_POST['WorkID'] = '22';
	$_POST['Status'] = '1';
	$_POST['Code'] = '1';
	$_POST['Title'] = '1';


	$columns = array();
	foreach(array_keys($_POST) as $name){
		# Exclude Action and ID
		if($name == 'Action' || $name == 'ID' || $name == 'submit' ){ continue; }
		$columns[] = $name;
	}

	print_r($columns);

	echo "<br>";

	$data = array_fill_keys($columns, 'NULL');

	print_r($data);

	foreach($data as $key => $value){
		$data[$key] = empty($_POST[$key]) ? 'NULL' : "'".mysql_real_escape_string($_POST[$key])."'";
	}

	echo "<br>";

	print_r($data);

	$insert = mysql_query('INSERT INTO datatable (ID, '.implode(', ',$columns).')VALUES (null, '.implode(',',$data).')') or die(mysql_error());

No errors no nothing. Just two entries of correct data. 
PS. Sorry for a lot of prints in the code it is work and idea in the progress. The posts at the moment are fixed in the code so it is easier to refresh and debug.

Please help if you spot the problem. Im really out of ideas. Some fresh eyes might make a difference.

And Please for those who want to say it is a bad idea and why and why and so on.. Move a long.!!! Im not interested in whinging i have a great use for this and just having trouble with the two entries.

Thanks.
Edited by ztimer, 14 January 2015 - 03:27 PM.

MySQL returns an error in the form of a number, a state, and a message. Without parsing the message you will not be able to determine what column is duplicated.While parsing the error code, I have also notice that, if you have multiple unique fields as duplicates, only the first duplicate encountered will be returned in the message. This is not very helpful to the end user.   Is there any way to parse the returned error code to reflect all duplicate fields, please see sample code below?

$error=array();
$sql = 'INSERT INTO staff(username, email, phone)
VALUES (?, ?, ?)';
$stmt = $conn->stmt_init();
$stmt = $conn->prepare($sql);
// bind parameters and insert the details into the database
$stmt->bind_param('sss', $username, $email, $phone);
$stmt->execute();
if ($stmt->errno == 1062) {
$errors[] = "One of the fields is already in use.";
}

Hey guys just wondering if this piece of code is correct because i get a mysql error saying Duplicate entry '*********' for key 2 this is the code i have and table structure.

$sql = " INSERT INTO `IP_Address`
         VALUES(NULL,'".$ip."','".$date."','".$time."')
         ON DUPLICATE KEY UPDATE `date` =  `".$date."`, `time` = `".$time."`

";

Code: [Select]
$PlayerQuery = "SELECT FirstName, SurName FROM players p, rounds m, entrants e, games t WHERE p.PlayerID = e.PlayerID AND m.GameID = '$ID' AND e.EntrantID = m.Player";
$PlayerResult = mysql_query($PlayerQuery);
$PlayerRow = mysql_num_rows($PlayerResult);

Can someone suggest why this returns game*the number of results. For each new game it returns an extra duplicate result. For example for 2 games:
j Smith
j Smith
t John
t John etc

There are no duplicate entry's! and results should be unique.  Not in a loop (positive)

I'm working on a directory component and have kind of a tricky thing I need to do.

The list of results pop through ajax on the left side along with it's google map number tag, and the google map pops on the right side. To make it even trickier the results are popped through a radius search by proximity.

The problem is if there are multiple results with the same address you end up having all of the number tags on the map buried under the last tag with that address, plus it makes for kind of a long list.

What I need to do is run a check to see if the previous result is the same address, and if yes not pop that result in a new table cell, rather stick it in a jquery drop down div so that all of the following results with the same address are stuffed in the jquery drop down in the same table cell under the first result with that address.

In a perfect world all of the listings with the same addresses would be submitted in order so I could just do a check for the previous itemid's address, but of course that's not the case. I thought about running a query to grab ALL of the addresses and then check the item's address against it to see if it matches and if yes echo the jquery div instead of the <td>, but obviously that won't work because that will cause the first item with that address to be put in the div also. Plus I don't know if that's a very efficient way to go about it anyway.

Here's kind of the basic thing I'm working with:
Code: [Select]
$query = "SELECT data_txt FROM jos_sobi2_fields_data WHERE fieldid = 76 AND itemid = $mySobi->id";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$myaddress = $row['data_txt'];
}
echo "<td $style>";

Any ideas?

Hello. Many thanks for your help.

I am writing a PHP/MySQL dating-site and have hit a programming impass.

I have a database full of members and a search form consisting of checkboxes.

So to search, a member ticks say...gender: female; age: 21,22,23,24,25,26; height: 5'4",5'5",5'6",5'7"; county: cornwall,devon,somerset

How can a run a check on the database selecting all entries that fall into the selected criteria. For example a 23 year old female of 5'5" living in Cornwall and a 26 year old female of 5'4" living in Somerset?

The key index of my database is 'id' and the fields a age,height,county
The names of the form checkboxes a
Gender: male, female; Age: 21,22,23,24 etc; Height: 5_4,5_5,5_6 etc; county: cornwall,devon etc

Dear All,

  I have attached here with a schema, of MySql DB format i am refering to for the topic i need help on.

I want to know what is the best way to achieve this problem / task.

I have a profile table, and a category table, and a table to map profiles to category.
Which allows me to define/add into the category_profile_mapping table, profile ids against a category id.

Each category can have anywhere between 2 to 1,00,000 profiles mapped to it.

I have another table called sms_out. hence on the site, when i select a category and submit the form value with message, i want to know what is the best way to populate sms_out with MSISDN from the table "Profile" and the message from the form.
The list of MSISDN will be based on the category id selected, hence the profile id's will be available in the category_profile_mapping table.

I am bad at explaining the problem, however incase you folks need more info do let me know. i shall be glad to provide the same to .. all of you guys who are willing to help me

PS:  I am asking this question, cuz i want to knw the best solution to achieve this, when there is entries in millions to be done at one form submit.

[attachment deleted by admin]

Here is one of those purely conceptual questions, which involves no code.  I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range.  I have a column in the table where the members' information are stored which records their ages.  Which brings us to the problem.  On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically.  I can't think of any way to automatically update this column on a daily basis.  So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range.  This leads to the second problem.  Imagine there are thousands of users using the website.  At any given instance, there could be hundreds of members, trying to search others based on age.  This means hundreds of users will be updating a single column (the age column) in one table at the same time.  Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses.

When I move threads on my forum, I want there to be a old thread in the section it was moved from saying "Moved: new link". To do this, I'm inserting a new thread in the database with the value moved equal to the new location, so when threads are being displayed users can see the thread in the new section.

Is there a quick function in MySQL that will allow me to create a duplicate row in MySQL, and then just run a quick update statement?

I don't want to have to insert a whole new thread like this (unless it's the only way):


//insert new thread
mysql_query("INSERT INTO `threads` VALUES (null, '$id','$title', '$content', '". getUsername() ."', NOW(), '". qfc() ."', NOW(), '". getUsername() ."', '','{$_SERVER['REMOTE_ADDR']}', '0', '0', '0', '', '0')");

Hello everyone.

I'd like to know how to duplicate a product through a php back-office.

That product consists of an ID in the "products" table of my DB. Then we have another table called "sub_products" that links its sub-products through a column named "product_id".

So I have product with ID "1000", and sub-products associated with it on another table with ID's "2021", "2022", "2023".

What's the routine I can use to duplicate both the product and its sub-products? I need to duplicate them because they fit 2 categories so I'm changing the category column during this duplicate.

Could really use some help. Thank you in advance! 

Hello,

i've got this code, to insert some form data into my db, it checks for a value if it exists already, it won't insert, now i want to add another value to check, but i don't know how.

This is my code:

Code: [Select]
<?php
if(isset($_POST['submit'])){
   if (strlen($_POST['code']) == 12 && substr($_POST['code'],0,6) == '610147'){
      $code = $_POST['code'];
      $select_query = mysql_query("SELECT * FROM jupiter WHERE code = '$code'");
      if(mysql_num_rows($select_query) == 0){
         $remote_addr = $_SERVER['REMOTE_ADDR'];
         $secret = $_POST['euro'];
         mysql_query("INSERT INTO jupiter(code,euro,ip,date,used) VALUES('$code','$secret','$remote_addr',CURDATE(),'n')");
 
      }
  Print "<font color='green'>OK</font>"; 
} else { 
    
Print "<font color='red'>Error</font>"; 
}
   
}?>
This checks if "code" already exists, now i want to also have it check for "ip"

Can someone help me out here please.

Thanks !

while ( $categories = $category_result -> fetch_assoc() )
 {
   $menu_sql = "select * from menu where category_id = {$categories['category_id']}";
   
   $menu_result = $handle -> query( $menu_sql );
   
   $category2 = strtoupper($categories['category']);
   
   echo '<h3 id="' . $category2 . '">' . $category2 . '</h3>';
   
   
      while ($menu = $menu_result -> fetch_assoc())
      {
         
         echo $menu['item_name'] . ' ';


         $size_sql = "select distinct size from size where category_id = {$categories['category_id']} order by size desc";
         $size_result = $handle -> query( $size_sql );
         
         while ($size = $size_result -> fetch_row())
          {
            echo $size[0] . ' ';
         }
         
         //get base type from base_type table for the pizza category
         $type_sql = "select type from base_type where category_id = {$categories['category_id']}";
         $type_result = $handle -> query( $type_sql );
         
         while ($type = $type_result -> fetch_row())
          {
            echo $type[0] . ' ';
         }         
         
         
         //get price for each item from menu if it is not null   
         $price_sql = "select price from menu where item_name = '{$menu['item_name']}'";
         $price_result = $handle -> query( $price_sql );
         
         while ($price = $price_result -> fetch_row())
          {
            echo $price[0] . ' ';
            
            if( is_null( $price[0] ) )
            {
            //get price from size table if price is null in menu table;

                    //$categories = $category_result -> data_seek(0);               
               
               $size = "double";
               $category_id = $categories['category_id'];
               $item_id = $menu['item_id'];
               $type = 'deep';
               
               echo $menu['item_id'];
                   
               
               $price_size_sql = "select price from size where size = '$size' and category_id = $category_id and item_id = $item_id";
               $price_size_result = $handle -> query( $price_size_sql );
               
               while ($price_size = $price_size_result -> fetch_assoc())
               {
               
                  echo " {$price_size['price']} ";
                  
               }
            }
            
         }         
                     
         echo '<br />';
      
      }
    echo '<br />';
 }


I get duplicate vakues for $category_id = $categories['category_id'];
               $item_id = $menu['item_id'];

dont know whats wrong it works for pizzas but not burgers, please help me.

Hi there

I'm trying to work out how I'd go about searching my database for URLs that have been submitted by users that are duplicates.
Now this isn't as simple as http://www.example.com/something.php and http://www.example.com/something.php oh no!

People have entered links such as http://www.example.com//something.php or http://www.example.com///something.php

The script (I didn't write it) currently see's these links as unique, but they're not. They function exactly the same way.

Anyone have any advice on how to weed these out?

I was thinking maybe a straight forward LIKE '%//%' in the mySQL syntax, but this will bring all URLs up due to the http:// aspect of the link

Any feedback would be appreciated!

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>