PHP - Check Duplicate Before Insert Into Mysql

Hi, I need help to check multiple rows for many columns, so that the script won't re-insert a duplicate entry. For eg:


$isRowExist=mysql_query("SELECT (field1,field2) from table1 WHERE field1='$f1' AND field2='$f2' ");

if(mysql_num_rows($isRowExist)==1)
   print "already in table1!";
else
   //proceed to insert into table table1


*BUT I tried this and it says mysql_num_rows expects resource, and this warning only shows when I have multipel columns I want to compare (it works just fine with one column in the WHERE clause above)

Please help!

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 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! 

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')");

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.

Can anyone tell me why this is not INSERTing? My array data is coming out just fine.. I've tried everything I can think of and cannot get anything to insert.. Ahhhh!

<?php
  $query = "SELECT RegionID, City FROM geo_cities WHERE RegionID='135'";
  $results = mysqli_query($cxn, $query);
  $row_cnt = mysqli_num_rows($results);
  echo $row_cnt . " Total Records in Query.<br /><br />";
  if (mysqli_num_rows($results)) {
      while ($row = mysqli_fetch_array($results)) {
          $insert_city_query = "INSERT INTO all_illinois SET state_id=$row[RegionID], city_name=$row[City] WHERE id = null" or mysqli_error();
          $insert = mysqli_query($cxn, $insert_city_query);
          if (!$insert) {
              echo "INSERT is NOT working!";
              exit();
          }
          
          echo $row['City'] . "<br />";
          
          echo "<pre>";
          echo print_r($row);
          echo "</pre>";
      }
      //while ($rows = mysqli_fetch_array($results))
      
      } //if (mysqli_num_rows($results))
      
      else {
          echo "No results to get!";
      }
?>

Here is my all_illinois INSERT table structu

CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is my source table geo_cities structu

CREATE TABLE IF NOT EXISTS `1` (
  `CityId` varchar(255) NOT NULL,
  `CountryID` varchar(255) NOT NULL,
  `RegionID` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  `TimeZone` varchar(255) NOT NULL,
  `DmaId` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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!

The only 2 tables relevant to what I want to do are "User" and "Scores." Basically this database holds high scores for a project I've been working on. I only want to display 10 scores, all from different users. No 2 or more scores from the same user. This is my db structure within phpmyadmin:

Quote

database -> table -> X / User / Score / X / X / X / X / X / X / X / X


If possible I'd like to run a query where it checks for duplicate entries by the same user and only show the highest score. The current query I'm running is below:

$result = mysql_query("SELECT * FROM table ORDER BY Score DESC LIMIT 0, 10");

Is this possible? I can only seem to find information regarding the INSERT IGNORE clause and that obviously won't help me as the users and scores are already in the database. Thanks.

Hello PHP World,
I don't have access to the database yet for the project I am working on so I created a work around until I get full access to the mysql database.

I Code: [Select]
<?php

$xml_file = "affiliate.xml";

// OPEN XML FILE ######################################################

if(!$xml=simplexml_load_file($xml_file)){
    trigger_error('Error reading XML file',E_affiliate_ERROR);
}
if(!empty($_GET['add'])){

           
$u_name = stripslashes($_POST['name']);
$u_company = stripslashes($_POST['company']);
$u_address = stripslashes($_POST['address']);
$u_city = stripslashes($_POST['city']);
$u_state = stripslashes($_POST['state']);
$u_phone = stripslashes($_POST['phone']);
$u_email = stripslashes($_POST['email']);
$u_desc = stripslashes($_POST['description']);
$u_region = stripslashes($_POST['region']);

// Generate new (appended) ID

foreach($xml as $affiliate){
    $last_id = $affiliate->uid;
}

$id = $last_id+1;

// Add node

$x = $xml->addChild("affiliate");
$x->addChild("uid",$id);
$x->addChild("name",$u_name);
$x->addChild("company",$u_company);
$x->addChild("address",$u_address);
$x->addChild("city",$u_city);
$x->addChild("state",$u_state);
$x->addChild("phone",$u_phone);
$x->addChild("email",$u_email);
$x->addChild("description",$u_desc);
$x->addChild("region",$u_region);

$xml->asXML($xml_file);

}
?>


html form

Code: [Select]
<br />
<br />

<h1>Affiliate </h1>

<form name="xml_writing" method="post" action="?add=t">
<table width="393">
<tr><td width="56">
Name:<br /></td><td width="206">
<input type="text" name="name" width="150"/></td>
<td>Region:</td>
<td><select name="region" value="options">
<option value="resellerNE">NorthEast</option>
<option value="resellerSE">SouthEast</option>
<option value="resellerC">Central</option>
<option value="resellerNE">NorthWest</option>
<option value="resellerNE">SouthWest</option>
<option value="resellerNE">International</option>
</select></td>
</tr>
<tr>
<td>
Company:<br /></td><td>
<input type="text" name="company" width="150" /></td>
</tr>
<tr><td>
Address:<br /></td><td>
<input type="text" name="address" width="150"/></td>
</tr>
<tr><td height="45">
City:<br /></td><td>
<input type="text" name="city" width="150"/></td>
<tr><td>
State:<br /></td><td>
<input type="text" name="state" width="150"/></td>
<tr><td>
Phone:<br /></td><td>
<input type="text" name="phone" width="150"/></td>
<tr><td>
Email:<br /></td><td>
<input type="text" name="email" width="150"/></td>
</tr>

<tr><td colspan="2">
Short description of yourself, your practice, your services, and other information of interest to potential clients or collaborators.<br /></td>
<tr><td colspan="2">
<textarea name="description" style="width: 406px; height: 136px;"></textarea></td>
</tr>
<tr><td></td><td>
<input type="submit" value="Submit" /></td>
</tr>
</table></form>

What I am trying to do is have on submit check against the email node in the xml file to see if email exist.
if exist string already a member else insert.

if you can help with code or point to something that will help.


"Everyone needs a mentor to lead them into success."
Quote by Shelby Poston

//database
create table mydata (
	id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fname varchar(20),	
    phoneno int(12) NOT NULL
    /*......*/
);
//class my data php

<?php

include('connect.php');
class InsertMydata {
    public function insertnow($fname, $phoneno) {
        $connect = new Connect;
        $insrt = $db -> prepare('INSERT INTO mydata (fname, phoneno) VALUES (?,?)');
		$insrt -> execute(array($fname, $phoneno));		
	}
}
?>


//insernow validate form

<?php
include('../classs/mydata.php');
//Declare data and error arrays
$errors = [];
$mydara = [];
if(!preg_match('/^[a-zA-Z]{4,15}$/', $_POST['fname'])) {
	$errors['fname'] = 'Enter full name!';
}
//this block not working even the phone exist
$connect = new Connect;
$phoneno = $_POST['phoneno'];
$checkiexist = $connect -> prepare('SELECT * FROM mydata WHERE phoneno = ?');
$checkiexist -> execute([$phoneno]);
if($checkiexist->rowCount() > 0) {
      $errors['phonenoexist'] = 'Try another phone number!';
 }
if(!empty($errors)){
      $data['success'] = false;
      $data['errors'] = $errors;
}else{
      $data['success'] = true;
      $data['message'] = 'success message!';  
      $mydata = new InsertMydata;
      $mydata -> insertnow($fname, $phoneno);
}
echo json_encode($data);

?>

//my ajax

$("#insertbtn").click( function(e) {
     var fname = $('#fname').val(),
         phoneno = $('#phoneno').val(),
           
       $.ajax({
           url: 'insertnow.php',
           type: 'POST',
           data: {fname:fname, phoneno:phoneno},
           dataType: "JSON",
           encode: true,
        }).done( function (data) {
         
         if (data.success == false) {
           if (data.errors.fname) {
                 $('#fname').append('<p class="text-danger">' + data.errors.fname + '</p>');
            } 
            if (data.errors.phonenoexist) {
              $('.card-header').append('<div class="alert alert-info alert-dismissible" role="alert">
                <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>'+data.errors.phonenoexist+'</div>');
            }
            } else {
             $('.card-header').append('<div class="alert alert-success alert-dismissible" role="alert"><button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>'+data.message+'</div>');
        }
    });
       e.preventDefault();
    });


//the problem is, the code insert data even if the phone exist why?

the problem is, the code insert data even if the phone exist why?

Edited April 8 by mahenda

I have a register script, and I am wanting to make it so that if the username field contains, lets say "mod", "ass", and more, then it'll return an error and wont let them register.

I have displayed check box values(ugroup field) from ugroups table.now what i want to do is,when user select multiple check boxes and submit it should be insert into relavent feild in table.now it's insert check boxes values.but not in relevant field.this is my code.Please help me.

//select ugroup's from group table.
<?php
$result = "SELECT id,ugroup FROM group";
$res_result = db::getInstance()->query($result);
?>

<form action="db_sql/db_add_page.php" method="get">
Tittle :<input type="text" size="100" name="tittle" />
Description :<textarea cols="80" id="editor1" name="description" rows="10"></textarea>

//Display ugroups in textboxes and checkboxes
       <?php 
while( $line=$res_result->fetch(PDO::FETCH_ASSOC)) {
echo '<input type="checkbox" name="group[]" value=" '. $line['ugroup'] .'" />';
echo'<input type="text" name="ugroup" disabled="disabled" value=" '. $line['ugroup'] .'" size="7" "/>';
echo ' ';
}
?><input type="submit" value="Submit">
</form>

db_add_page.php

if(isset($_POST))
{


$tittle = $_POST['tittle'];
$description = $_POST['description'];
$ugroup = $_POST['group'];
$acc_status = "INSERT INTO add_services (id,tittle,description,g1,g2,g3,g4,g5,g6,g7,g8)
 VALUES(NULL,'".$tittle."','".$description."','".$ugroup[0]."','".$ugroup[1]."','".$ugroup[2]."','
".$ugroup[3]."','".$ugroup[4]."','".$ugroup[5]."','".$ugroup[6]."','".$ugroup[7]."')";
$rate = db::getInstance()->exec($acc_status); 
if(!$rate){
echo '<script type="text/javascript">alert("Update Error !");</script>';
}else{
header('Location:../add_page.php');
echo '<script type="text/javascript">alert("Successfuly Updated User Group !");</script>'; 



}

}

Attached Files  group.jpg   22.14KB   0 downloads  add_services.jpg   21.36KB   0 downloads

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);

?>

Hello, I'm having a bit of a problem here, all help to this issues would be much appreciated

I am trying to use text boxes to insert numbers into the database based on what is inputed.
If I have a string, like this for example:
$variable = 09385493;

And I want to insert it into the database like this:
mysql_query("INSERT INTO integers(number)
VALUES ('$variable')");

When checking the integers table in my database, looking at the number field, the $variable that was inserted is outputted as
9385493

Notice the number zero was taken out of the front of the number. If the number is double 0's (009385493), both of those zero's would disappear, too.

Thanks

I don't understand where the empty value is. I've substituted the variables for text and still have the same problem.

Code:
Code: [Select]
$sql = "INSERT INTO courses (course#, name, subject, semester, ap)VALUES('$courseNum', '$courseName', '$subject', '$semester', '$ap')";
Error:
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 '' at line 1