Jump to content
PupStar

Copy Script Issues

Recommended Posts

I am having a very boring lockdown sunday afternoon so having a play about.

Working locally not specifically on oscommerce but using osc tables.

I am trying to copy categories from one table to another.

<a href="update_live_categories.php?id=$row[categories_id]"><button class="btn btn-primary" type="submit">Update Categories</button></a>

<?php
$databaseHost = 'localhost';   //your db host
$databaseName = 'tbl_temp_products';  //your db name
$databaseUsername = 'root';    //your db username
$databasePassword = '';//   db password

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName);

$id = $_GET['categories_id'];

$sql="select * from tbl_temp_categories where (categories_id='$id')";//  check id is already copied

      $result=mysqli_query($mysqli,$sql);

      if (mysqli_num_rows($result) > 0) {
        // output data of each row
        $row = mysqli_fetch_assoc($result);
        if($id==$row['categories_id'])
        {
         echo "Already copied"; //error message if already copied
        }

       } else{

        $query = "INSERT INTO categories SELECT * FROM tbl_temp_categories WHERE categories_id=$id";

         if (mysqli_query($mysqli, $query)) {
          echo "New record created successfully";
         } else {
          echo "Error: " . $query . "" . mysqli_error($mysqli);
         }

	   }
?>

This script is throwing 1 notice and 1 error

Quote

Notice: Undefined index: categories_id

on this line

$id = $_GET['categories_id'];

and

Quote

Error: INSERT INTO categories SELECT * FROM tbl_temp_categories WHERE categories_id=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

on this line

$query = "INSERT INTO categories SELECT * FROM tbl_temp_categories WHERE categories_id=$id";

Any ideas on a resolve?

Thank you

Share this post


Link to post
Share on other sites

If this is your entire script, there is no form which submits the categories_id.

Or add a form with an input field or selection for it. Or hard code the id for the category you wish to apply:

1 hour ago, PupStar said:

$id = 1;

Anyway the variable as used in your query doesn’t look correct.

Maybe do it directly in the database with an sql script.

Edited by raiwa

Share this post


Link to post
Share on other sites
37 minutes ago, raiwa said:

If this is your entire script, there is no form which submits the categories_id.

Or add a form with an input field or selection for it. Or hard code the id for the category you wish to apply:

Anyway the variable as used in your query doesn’t look correct.

Maybe do it directly in the database with an sql script.

@raiwaNot sure why it would need a form as once you click the button it should just copy data from one table to another.

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites
9 hours ago, PupStar said:

This script is throwing 1 notice and 1 error

The notice is saying $GET['categories_id'] isn't set. The error is because you are trying to use that missing value. The button is setting the parameter "id", not "categories_id".

Share this post


Link to post
Share on other sites

@Jack_mcs @raiwa

Thanks for the help and sorry about the late reply, the day job is taking its toll during this lockdown

I have modified the code and now there are no errors or notices however nothing is being copied to the new table.

<a href="update_live_categories.php?categories_id=$row[categories_id]"><button class="btn btn-primary" type="submit">Update Categories</button></a>

<?php
$databaseHost = 'localhost';   //your db host
$databaseName = 'tbl_temp_products';  //your db name
$databaseUsername = 'root';    //your db username
$databasePassword = '';//   db password

$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName);

$id = 1;

$sql="select parent_id, categories_id from tbl_temp_categories where (categories_id='$id')";//  check id is already copied

      $result=mysqli_query($mysqli,$sql);

      if (mysqli_num_rows($result) > 0) {
        // output data of each row
        $row = mysqli_fetch_assoc($result);
        if($id==$row['categories_id'])
        {
         echo "Already copied"; //error message if already copied

       }else{

        $query = "INSERT INTO categories SELECT parent_id, categories_id FROM tbl_temp_categories WHERE categories_id=$id";

         if (mysqli_query($mysqli, $query)) {
          echo "New record created successfully";
         } else {
          echo "Error: " . $query . "" . mysqli_error($mysqli);
         }
        }
	   }
?>

 

Share this post


Link to post
Share on other sites

I believe in the queries should be something like this:

categories_id='" . $id . "'

 

Share this post


Link to post
Share on other sites
15 minutes ago, raiwa said:

I believe in the queries should be something like this:


categories_id='" . $id . "'

 

@raiwa

Thank you but unfortunately it made no difference

Share this post


Link to post
Share on other sites
1 hour ago, PupStar said:

$sql="select parent_id, categories_id from tbl_temp_categories where (categories_id='$id')";// check id is already copied

 

That line says, "Select only rows from the temp table where the category ID is $id."

1 hour ago, PupStar said:

if ($id==$row['categories_id']) {
  echo "Already copied"; //error message if already copied
} else {
  $query = "INSERT INTO categories SELECT parent_id, categories_id FROM tbl_temp_categories WHERE categories_id=$id";

 

Those lines say, "Only insert if the category ID is not $id."

Those two things are never  simultaneously true, so the insert is never called.  The logic would make more sense if the first line was

$sql = "select parent_id, categories_id from categories where (categories_id='$id')";// check id is already copied

But even then, I don't think it would work without changing more of the logic.  The else should be on mysqli_num_rows not inside it. 

Note that if you dumped PHP and just did this in phpMyAdmin, the whole thing could just be

INSERT INTO categories SELECT t.* FROM categories c RIGHT JOIN tbl_temp_categories t ON c.categories_id = t.categories_id WHERE c.categories_id = NULL AND t.categories_id = 3

replace 3 with whatever category ID.  That line does nothing if either the row already exists in categories or does not exist tbl_temp_categories.  If it does not exist in categories but does exist in tbl_temp_categories, it copies.  And if you got rid of the AND clause, you'd just have

INSERT INTO categories SELECT t.* FROM categories c RIGHT JOIN tbl_temp_categories t ON c.categories_id = t.categories_id WHERE c.categories_id = NULL

which would copy everything at once.  I.e. all rows that are in tbl_temp_categories but not categories. 


Always back up before making changes.

Share this post


Link to post
Share on other sites
20 minutes ago, ecartz said:

That line says, "Select only rows from the temp table where the category ID is $id."

Those lines say, "Only insert if the category ID is not $id."

Those two things are never  simultaneously true, so the insert is never called.  The logic would make more sense if the first line was


$sql = "select parent_id, categories_id from categories where (categories_id='$id')";// check id is already copied

But even then, I don't think it would work without changing more of the logic.  The else should be on mysqli_num_rows not inside it. 

Note that if you dumped PHP and just did this in phpMyAdmin, the whole thing could just be


INSERT INTO categories SELECT t.* FROM categories c RIGHT JOIN tbl_temp_categories t ON c.categories_id = t.categories_id WHERE c.categories_id = NULL AND t.categories_id = 3

replace 3 with whatever category ID.  That line does nothing if either the row already exists in categories or does not exist tbl_temp_categories.  If it does not exist in categories but does exist in tbl_temp_categories, it copies.  And if you got rid of the AND clause, you'd just have


INSERT INTO categories SELECT t.* FROM categories c RIGHT JOIN tbl_temp_categories t ON c.categories_id = t.categories_id WHERE c.categories_id = NULL

which would copy everything at once.  I.e. all rows that are in tbl_temp_categories but not categories. 


@ecartz

Thanks Matt but I must admit that the logic way over my head lol

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×