Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Copy Script Issues


PupStar

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

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

Link to comment
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".

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
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);
         }
        }
	   }
?>

 

Link to comment
Share on other sites

I believe in the queries should be something like this:

categories_id='" . $id . "'

 

Link to comment
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.

Link to comment
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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...