Copy Script Issues

[PupStar]

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

[♥raiwa]

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.

[PupStar]

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.

[♥raiwa]

Ok, but where is

$row[categories_id

defined?

[Jack_mcs]

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".

[PupStar]

@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);
         }
        }
	   }
?>

 

[♥raiwa]

I believe in the queries should be something like this:

categories_id='" . $id . "'

 

[PupStar]

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

[♥ecartz]

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. 

[PupStar]

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