PupStar Posted January 24, 2021 Share Posted January 24, 2021 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 More sharing options...
♥raiwa Posted January 24, 2021 Share Posted January 24, 2021 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. About Me: http://www.oscommerce.com/forums/user/249059-raiwa/ Need help? How To Get The Help You Need Is your version of osC up to date? You'll find the latest osC community version CE Phoenix here. Public Phoenix Change Log Cheat Set on Google Sheets Link to comment Share on other sites More sharing options...
PupStar Posted January 24, 2021 Author Share Posted January 24, 2021 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 More sharing options...
♥raiwa Posted January 24, 2021 Share Posted January 24, 2021 Ok, but where is $row[categories_id defined? About Me: http://www.oscommerce.com/forums/user/249059-raiwa/ Need help? How To Get The Help You Need Is your version of osC up to date? You'll find the latest osC community version CE Phoenix here. Public Phoenix Change Log Cheat Set on Google Sheets Link to comment Share on other sites More sharing options...
Jack_mcs Posted January 25, 2021 Share Posted January 25, 2021 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 More sharing options...
PupStar Posted January 31, 2021 Author Share Posted January 31, 2021 @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 More sharing options...
♥raiwa Posted January 31, 2021 Share Posted January 31, 2021 I believe in the queries should be something like this: categories_id='" . $id . "' About Me: http://www.oscommerce.com/forums/user/249059-raiwa/ Need help? How To Get The Help You Need Is your version of osC up to date? You'll find the latest osC community version CE Phoenix here. Public Phoenix Change Log Cheat Set on Google Sheets Link to comment Share on other sites More sharing options...
PupStar Posted January 31, 2021 Author Share Posted January 31, 2021 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 Link to comment Share on other sites More sharing options...
♥ecartz Posted January 31, 2021 Share Posted January 31, 2021 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 More sharing options...
PupStar Posted January 31, 2021 Author Share Posted January 31, 2021 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.