experience40

Superb, thanks again

Slightly different to how i thought it would be done, but with a bit of tweaking it works Why is there an ampersand on the function call "function retrieve_category_path($pdo, $id, &$cpa)" ? Also what purpose does the $cpa serve below, is it sending the "$cpa[] = $row['name']" array data back round to the function call for the next loop, i.e. is the built array data from the 1st loop sent to the function call to be included on each following loop? As for not including DB connection inside functions, i'll have to look into how to do this as i'm not aware of any other way to do it! I'm picking up PHP as i go along, i've picked it up on this method from code examples which has stuck with me as standard practice so i'll have to figure out how to do it more efficiently Many thanks for your help, i've sent you a small donation for a drink

SQL DB Category Table Basically you will call the function with category id (retrieve_category_path($id) and it will loop through the database returning the category data until the parent id = 0 (ID for top Parent = 0, Parent ID > 0 = Child) Just looking to combine the returned arrays into a single array (Parent/Child/Child/Child) rather than (Parent/) (Child/) (Child/) (Child/)

I'm stuck on trying to generate the full Category Path / Breadcrumb path for a category including its parent categories e.g. Cars / Sports Cars / Petrol / 2 Door I have a category ID such as 123 for "2 Door" in the path example above, which is sent to the function below in hope of getting the full category path including parent categories. function retrieve_category_path($id){ // DB connection require("../../config/db/connection.php"); // Company ID $company_id = 1 ; $stmt = $conn->prepare("SELECT id, name, parent, position FROM category WHERE company_id = ? AND id = ?"); $stmt->bind_param("ii", $company_id, $id); $stmt->execute(); $result = $stmt->get_result(); $category_path_array = []; while($row=$result->fetch_assoc()){ $parent =$row['parent']; $name = $row['name']; $position = $row['position']; $position_seperator = '::'; $seperator = '/'; $category_path_array = [$name, $position_seperator, $position, $seperator]; if($parent > 0){ retrieve_category_path($parent); } } // return $category_path_array; var_dump($category_path_array); } This creates an array: which when imploded, the array has split to 4 seperate array rows: However i want the rows combined into a single row such as: ideally with the end forward slash removed to Can anyone please advise where i'm going wrong? Many thanks

Ah the pennies dropped 🙂 I didnt realise array() initialized a new empty array, i thought $data_array[] was enough to create an array but i guess the [] adds items to the array rather than creating a new one on each loop Many thanks Danish, you've saved me ALOT of hair pulling on why i couldnt get this to work Superb, much appreciated!

I'm trying to add data to a CSV file with dynamic variable data but getting stuck on the 2nd loop which retrieves the data which gets the error Uncaught TypeError: Illegal offset type Dynamic Variables: Loop 1: $value which contains column names for the data e.g. SKU, Price, Quantity.. Loop 2: $row2 which contains data for the above columns Loop 1 Column Names: $column_names_array[] =$value; // Array ( [0] => SKU [1] => Price [2] => Quantity ) // This is dynamic array so variables above can vary Loop2: Data for the column names $data_array = array($row2[$column_names_array]); // This wont work as $column_names_array from Loop1 is already in its own array // Looking for a way to add $row2 to $column_names_array and combine into an array such as: // $data_array = array($row2['SKU'], $row2['Quantity'], $row2['sell_price']); // but this needs to be dynamic such as $data_array = array($row2[$column_names_array]); Code: fputcsv($csvfile, $export_column_name_array, $delimiter); $stmt2 = $conn->prepare($sql_built_statement); //$stmt2->bind_param("ii", $company_id, $company_id); WILL ADD THIS LATER ONCE WORKING $stmt2->execute(); $result2 = $stmt2->get_result(); if($result2 != NULL){ // Add column names to array foreach ($export_column_name_array as $value) { $column_names_array[] =$value; } //print_r($column_names_array); //Array ( [0] => SKU [1] => Price [2] => Quantity ) // Retrieve data for each column while($row2=$result2->fetch_assoc()){ // MANUALLY SETTING VARIABLES WORKS e.g.: // $data_array = array($row2['SKU'], $row2['Quantity'], $row2['sell_price']); // print_r($data_array); // Array ( [0] => ABC123 [1] => 99 [2] => 123.00 ) $data_array = array($row2[$column_names_array]); // Uncaught TypeError: Illegal offset type } //print_r ($data_array); fputcsv($csvfile, $data_array, $delimiter); } fclose($csvfile); I have tried combining the 2 loops which semi works, however the data replicates into each array on each loop such as: Code: fputcsv($csvfile, $export_column_name_array, $delimiter); $stmt2 = $conn->prepare($sql_built_statement); //$stmt2->bind_param("ii", $company_id, $company_id); WILL ADD THIS LATER ONCE WORKING $stmt2->execute(); $result2 = $stmt2->get_result(); if($result2 != NULL){ // Retrieve data while($row2=$result2->fetch_assoc()){ // Add column names and data to array foreach ($export_column_name_array as $value) { $column_names_array[] =$row2[$value]; } //print_r($column_names_array); } fputcsv($csvfile, $column_names_array, $delimiter); } fclose($csvfile);

Thanks for the advice, very much appreciated I thought including the 2nd query in a loop was creating unneccesary calls to the database as the results from the 1st query could contain 100's of rows, leading to the 2nd query updating the database 100s of times so thought i was doing it wrong but it works I tried combining the 2 queries into 1 as you have advised which works, however my database doesnt like it as the update has gone from 7 seconds to 111 seconds so without indexing the easiest solution is to keep it as it is but thanks for the suggestion! I've got into a bad habit of writing my queries on one row which has stuck with me as i've learnt the language, i'll have to break the habit!! @Barand I noticed on the single query you suggested that you amended " .$company_id ." to {$company_id}, is {$company_id} more efficient or just the correct way to include it and also is it as safe to use to protect from injection?

Trying to make this faster, have tried using a results array but am struggling on how to do it so looking for advice to check if it is possible to use the 2nd query outside of the loop or not? I have 2 SQL statements, the 1st query retrieves stock_id and brand, i want to use the results from this in the 2nd query How can i use the result outside of the loop so i'm not making multiple update queries for each result row? $company_id = 1; $stmt = $conn->prepare("SELECT s.stock_id, s.csv_data AS brand FROM stock_" .$company_id ." s WHERE s.company_id = ? AND s.attribute_id = 5"); $stmt->bind_param("i", $company_id); $stmt->execute(); $result = $stmt->get_result(); if($result != NULL){ while($row=$result->fetch_assoc()){ $brand =$row['brand']; $stock_id =$row['stock_id']; $stmt2 = $conn->prepare("UPDATE prices_" .$company_id ." p INNER JOIN price_rules pr ON p.company_id = pr.company_id INNER JOIN feeds f on f.id = p.feed_id SET p.brand_markup_percentage = (p.stock_price / 100) * pr.brand_markup_percentage WHERE pr.company_id = ? AND f.disable_price_rules = 0 AND pr.brand = ? AND p.stock_id = ? AND pr.brand_price_or_percent = 1 AND p.stock_price BETWEEN pr.min_price AND pr.max_price AND p.price_profile = ?"); $stmt2->bind_param("isii", $company_id, $brand, $stock_id, $price_profile); $stmt2->execute(); } }

thanks for your advice Barand, very much appreciated! I managed to fix this in the end, looking more carefully through the code i had 2 loops (small loop in a larger loop) which was affecting the call to the stock_id and affecting the incrementing I have now moved the initial call outside both loops and then made a reference to it in the larger loop which appears to worked

Each CSV column is inserted into a seperate DB row so i'm unable to leave it to the DB to auto_increment, so the columns needs to link by being assigned a stock_id

I'm importing products from a CSV file and each imported 'product row' is assigned a stock_id which increments as each row is imported I need to retrieve the existing highest stock_id from the DB and use this as my start value for each row when importing another file so i dont duplicate stock_id's (multiple columns on the CSV so each column on the same row needs to have the same stock_id) function get_stock_id() { //DB connection require("../config/db/connection.php"); $get_stock_id = $conn->prepare("SELECT MAX(stock_id) AS max_stock_id FROM stock_1"); $get_stock_id->execute(); $stock_id_result=$get_stock_id->get_result(); $row = mysqli_fetch_assoc($stock_id_result); $stock_id = $row['max_stock_id']; } As each row is imported the stock_id increases +1 (stock_id ++;), and when using the above code the next row rechecks the 'current' max stock_id leading to increasing start values for the stock_id so a bit like a dog chasing its tail Instead of assigning stock_id to all columns on the same row for example to 1 1 1 1 1 its assigning 1 2 3 4 5 so all columns have different stock_id's If i manually set the stock_id = x; or use the above code and add to a session with $_SESSION["stock_id"] = $stock_id; the stock_id start value is set and all row columns have the same ID Looking for the best way to get the highest stock_id value and use this a start value for the whole loop which doesnt increment for each row

Thanks for the advice Phi11W I think your suggestion of using an array with data key-value pairs sounds good and hopefully will fit my requirements

thanks Phill I think the way you've suggested is to run the 'html' code directly from the backend php file via echo which wont work if there are several template designs, however i can see you have seperated the functions which was one of the things i was wondering about Basically there will be 2 files: 1x html / php (frontend) - there may be several 'template' layout designs depending on the users choice 1x php (backend) The frontend will need to display the data from the backend Frontend example <?php require "/functions/data.php"; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" name="viewport" content="width=device-width"/> <link rel="stylesheet" type="text/css" href="/resources/css/bootstrap.css"/> </head> <body> <table border="1" cellpadding="1" cellspacing="1"> <tr> <td> <?php data($title); ?> </td> <td><?php data($description); ?></td> <td><?php data($category); ?></td> <td><?php data(image_link); ?></td> </tr> </table> </body> </html> Based on your example i'm unsure how i would retrieve the data bearing in mind i may need just one of the variables not all four and so forth so would need to be able to display/pull them individually So would need to be able to call description / category / image_link / title seperately if needed

Wondering how i can call specific variables from the same function which i can then use for a HTML layout template If i have a 'data' function with several variables how can i call the variables seperately? function data () { //Abbrieviated code.... SELECT title, category, description, image_link FROM TABLE1 WHERE product_id=1; $title = $row['title']; $category = $row['category']; $description = $row['description']; $image_link = $row['image_link']; ... }

Every CSV differs, but the below gives a good idea of the data

thanks, i have an attribute table setup as below: Slightly struggling to understand this, are you able to give an example? Stock Table id user_id feed_id 1 1 1 Stock Attribute Table id stock_id attribute_id data 1 1 4 99.99

this is how the original code imports into the stock table with seperate rows for the same item This is how i want it to import on the same row

Unfortunately this wont work as i dont know how many columns there are as it varies for each user, the column is a variable i.e. user 1 may have 3 attributes (price, quantity, name) user 2 may have 5 attributes (name, colour, size, weight, part_code)...

thanks for the replies Basically i have price stock CSV Excel files (all different) which contain columns such as price, part_code, manufacturer, name, description etc.. and i want to import the data into the correct database column in the stock table hence a map table which maps the CSV column number to the appropriate database stock table column Map Table | feed_id | user_id | attribute_code | csvcolumn | When the CSV data is imported into the DB, if price is mapped as CSVcolumn '2' then column '2' data will import into stock table column name '2' etc Each CSV is different and each user has different attributes, where one user may have weight, colour etc.. another user may have size, thickness so the attributes on the insert command varie and cannot be hard code. One user may have attributes 1-5, another user attributes 1-50. The idea is that the user can add as many attributes as they want depending on their needs The code below half works, however its creating seperate rows $insertstock = $conn->prepare("INSERT INTO stock (userid, feed_id, `$A_ID`) VALUES (?, ?, ?)"); $insertstock->bind_param("iis", $userid, $feed_id, $row[$CSV_COL]); The 2 variables in the map table $A_ID = $dbrow['attribute_id']; // THIS IS THE ATTRIBUTE ID $CSV_COL = $dbrow['csvcolumn']; // THIS IS THE CSV COLUMN NUMBER

Hi I have a Map DB Table which maps CSV column numbers and i'm trying to import the CSV data into the stock table based on the CSVColumn number. However when i insert the data into the stock table, it imports the different CSV column data onto seperate rows rather than the same row for the same product The column names in the stock table are named after the attribute_code on the Map table Wondering the best way to tackle this, possibly merge the first SELECT query into a single row result? Map Table | feed_id | user_id|attribute_code | csvcolumn | |1|1|1|3| |1|1|10|1| |1|1|6|7| Stock Table |feed_id|user_id|0|1|2|3|4|5|6|7|8|9|10| I want to import the data as such: Stock Table |feed_id|user_id|0|1|2|3|4|5|6|7|8|9|10| |1|1||CSVData||CSVData||||CSVData|||| However it imports as seperate rows Stock Table |feed_id|user_id|0|1|2|3|4|5|6|7|8|9|10| |1|1||||CSVData|||||||| |1|1||CSVData|||||||||| |1|1||||||||CSVData|||| Abrievated summary of the code i'm trying below: // RETRIEVE MAP COLUMN NUMBERS $feed_db = $conn->prepare("SELECT m.attribute_id, m.csvcolumn, f.filename, f.header_row FROM map m INNER JOIN ftp f WHERE f.id = m.feed_id AND user_id = ? AND feed_id = ?"); $feed_db->bind_param("ii", $userid, $feed_id); $feed_db->execute(); $feedresult=$feed_db->get_result(); while($dbrow=$feedresult->fetch_assoc()) { $A_ID = $dbrow['attribute_id']; $CSV_COL = $dbrow['csvcolumn']; // OPEN CSV $file = fopen($local_file_path, "r"); fgetcsv($file); while (($row = fgetcsv($file, 0, $delimiter)) !== FALSE) { // INSERT CSV DATA $insertstock = $conn->prepare("INSERT INTO stock (userid, feed_id, `$A_ID`) VALUES (?, ?, ?)"); $insertstock->bind_param("iis", $userid, $feed_id, $row[$CSV_COL]); $insertstock->execute(); } }