bruckerrlb Posted March 31, 2010 Share Posted March 31, 2010 Hey Guys, I'm working on calling data back from my database. I am basically dealing with three tables here, companies, products and licenses I'm trying to get all products to show up in one cell of the overall company information and if they have a license, I'd like to get it to show up in the corresponding cell of the products. I've set my database up so that this will work, basically it looks like this: Products product_id product_name company company_id company_name license license_id license_name product_id company_id license_numbers (really amount of licenses a company owns) So, with that knowledge, I have my form set up like this <form action="<?php echo $PHP_SELF;?>" method="post" name="modcompany"> <input type=hidden name="id" value="<?php echo $company_id ?>"> <table class="floattable"> <tr> <td class="row2">Company ID</td><td class="row2"><?php echo $company_id; ?></td> </tr> <tr> <td class="row1">Company Name</td><td class="row1"><input type="text" name="company_name" value="<?php echo $company_name; ?>" /></td> </tr> <tr> <td class="row1">Address</td><td class="row1"><input type="text" name="address" value="<?php echo $address; ?>" /></td> </tr> <tr> <td class="row1">City</td><td class="row1"><input type="text" name="city" value="<?php echo $city; ?>" /></td> </tr> <tr> <td class="row1">State</td><td class="row1"><input type="text" name="state" value="<?php echo $state; ?>" /></td> </tr> <tr> <td class="row1">Zip</td><td class="row1"><input type="text" name="zip" value="<?php echo $zip; ?>" /></td> </tr> <tr> <td class="row1">Country</td><td class="row1"><input type="text" name="country" value="<?php echo $country; ?>" /></td> </tr> <tr> <td class="row1">Phone</td><td class="row1"><input type="text" name="phone" value="<?php echo $phone; ?>" /></td> </tr> <tr> <td class="row1">Contact</td><td class="row1"><input type="text" name="contact" value="<?php echo $contact; ?>" /></td> </tr> <tr> <td class="row1">Status</td><td class="row1"><input type="text" name="status" value="<?php echo $status; ?>" /></td> </tr> <tr> <td class="row1">Kayako Link</td><td class="row1"><input type="text" name="kayako_link" value="<?php echo $kayako_link; ?>" /></td> </tr> <tr> <td class="row1">Vtiger Link</td><td class="row1"><input type="text" name="vtiger_link" value="<?php echo $vtiger_link; ?>" /></td> </tr> <tr> <td class="row1">Internal Link</td><td class="row1"><input type="text" name="internal_link" value="<?php echo $internal_link; ?>" /></td> </tr> <tr> <td class="row1">Notes</td><td class="row1"><textarea name="notes" rows="5" cols="5"><?php echo $notes; ?></textarea></td> </tr> <?php //this gets all of my products to show up, which is great for adding but need to figure something out for editing $sql_license = "SELECT * FROM products"; $result_license = mysql_query($sql_license); while($row_license = mysql_fetch_array($result_license)) { $product_name = $row_license['product_name']; $product_id = $row_license['product_id']; ?> <tr> <td class="row1"><?php echo $product_name; ?></td><td class="row1"><input type="text" name="product<?php echo $product_id; ?>" /></td> </tr><?php } ?> </table> And, thanks to another user, I have found a way to add the values to my db if (isset($_POST['modcompany'])) { $company_name = $_POST["company_name"]; $address = $_POST['address']; $city = $_POST['city']; $state = $_POST['state']; $zip = $_POST['zip']; $country = $_POST['country']; $phone = $_POST['phone']; $contact = $_POST['contact']; $status = $_POST['status']; $kayako_link = $_POST['kayako_link']; $vtiger_link = $_POST['vtiger_link']; $internal_link = $_POST['internal_link']; $notes = $_POST['notes']; $sql = "UPDATE company SET company_name='$company_name',address='$address',city='$city', state='$state', zip='$zip', country='$country', phone='$phone', contact='$contact', status='$status', kayako_link='$kayako_link', vtiger_link='$vtiger_link', internal_link='$internal_link', notes='$notes' WHERE company_id = $id"; $result = mysql_query($sql); echo "Thank you! Information updated."; $id = mysql_insert_id(); foreach($_POST as $key => $value) { // looping through each post variable. $key is the textbox name // check if the the variable name starts with 'product' which indicates it's a product$id variable if(strpos($key, 'product') === 0) { // found one, need to parse the ID off the end $prod_id = substr($key, 7); // 7 because 'product' is 7 chars long echo "<br />Product id = $prod_id"; echo "<br />Text Box = $value <hr />"; if($value != '') { $query = "INSERT INTO license (company_id, product_id, license_numbers) VALUES ('$id', '$prod_id', '$value')" or die('mysql_error'); mysql_query($query) or die('error'); /* at this point you now have the product id in $id and the value of the textbox in $value so you can now add to database or do anything else you want */ } } } So, right now with this code, I'm able to have my companies show up and all of my products show up which then get inserted into the license table, but I'm not sure how I could set this up to edit it, which means - have all products show up, and if they have an amount of licenses, that shows up as well, but all the products show up so that users can add licenses to that. I tried doing a table join, but realized it didn't make sense as it wouldn't help me to get all of the products out SELECT products.product_id as product_id, products.product_name as product_name, license.license_id as license_id, license.company_id as l_company_id, license.product_id as l_product_id, license.license_notes as license_notes FROM products LEFT JOIN license ON products.product_id = license.product_id Any ideas? Quote Link to comment Share on other sites More sharing options...
deansaddigh Posted March 31, 2010 Share Posted March 31, 2010 Should you have done a left join? http://www.w3schools.com/sql/sql_join_left.asp Quote Link to comment Share on other sites More sharing options...
bruckerrlb Posted March 31, 2010 Author Share Posted March 31, 2010 Well, I had this crazy left join written out but then I realized it wouldn't help me bring out all the tables of products weather they had licenses or not, not sure if that's something I need to do in mysql or php though My left join which joins licenses and products looks like this SELECT products.product_id AS product_id, products.product_name AS product_name, license.license_id AS license_id, license.company_id AS l_company_id, license.product_id AS l_product_id, license.license_notes AS license_notes FROM products LEFT JOIN license ON products.product_id = license.product_id WHERE license.company_id =1 LIMIT 0 , 30 So, I need to bring back all products on this company edit page, and a field for number of licenses which gets inserted into the license table. If the product doesn't have any licenses, that's fine it can be blank, but still needs to show up in case the user wants to add one. I appreciate the left join suggestion, but I think it's a little out of range for this solution, but thanks though! any other ideas? Quote Link to comment Share on other sites More sharing options...
ignace Posted March 31, 2010 Share Posted March 31, 2010 The reason you are having a hard time is because your db has been badly designed: products ----------- product_id product_name company ----------- company_id company_name license ----------- license_id license_name product_id company_id license_numbers Your company can have many products but not each product may have a license. company --(1..*)--> product --(0..*)--> license products ----------- product_id product_name company_id companies ----------- company_id company_name licenses ------------ license_id license_name license_sum product_id Now you can query these by using: SELECT * FROM companies c -- use LEFT JOIN if you want to show up companies that have no products -- in this case the relation becomes company --(0..*)--> product JOIN products p ON c.company_id = p.company_id -- if the product has no license all fields for licenses will return NULL LEFT JOIN licenses l ON p.product_id = l.product_id Quote Link to comment Share on other sites More sharing options...
bruckerrlb Posted April 1, 2010 Author Share Posted April 1, 2010 I appreciate that, and would say your right, my db logic is probably a little screwed up here!! I was studying the db logic you drew out here and it looks really good, like a really good start, the only thing I saw here was the products ----------- product_id product_name [b]company_id[/b] companies ----------- company_id company_name licenses ------------ license_id license_name license_sum product_id company_id in the products table, that wouldn't work because companies can have many products, companies can also have the same product, so things would get a little hairy in the db. I appreciate the re-design though and agree with you that it needs to be fixed up I"m wondering if this would even be possible to do with my current database layout because while I"m not opposed to changing the db design completely, it would cause me to have to start from scratch and if I could figure out a way to do it from how the db is now, that would be awesome. If it's not possible, then it's just not possible though Quote Link to comment Share on other sites More sharing options...
ignace Posted April 1, 2010 Share Posted April 1, 2010 companies can also have the same product I am not aware of all your business rules/-requirements as I am not in charge of your project but you could solve this by: companies_has_products ------------------ company_id product_id Quote Link to comment Share on other sites More sharing options...
bruckerrlb Posted April 1, 2010 Author Share Posted April 1, 2010 That's a great idea, and I'm thinking it might be the best way to go, quick question for you on a kind of unrelated note, I have a foreach statement here <?php /////////////bring back all licenses and their products $sql_license = "SELECT products.product_id as product_id, products.product_name as product_name , license.license_numbers as l_number, license.company_id as l_company_id FROM products LEFT JOIN license ON products.product_id = license.product_id WHERE license.company_id = '$id'"; $result_license = mysql_query($sql_license); while($row_license = mysql_fetch_array($result_license)) { $product_name = $row_license['product_name']; $product_id = $row_license['product_id']; $l_number = $row_license['l_number']; ?> <tr> <td class="row1"><?php echo $product_name; ?></td><td class="row1"><?php echo $l_number; ?> </td><td class="row1"> <a href="#">Delete</a></td> </tr> <?php } //////end getting back all products with licenses ?> <?php foreach($result_license as $key => $value) { ///Get All Products that don't have licenses $sql_license = "SELECT * FROM products"; $result_license = mysql_query($sql_license); while($row_license = mysql_fetch_array($result_license)) { $prod_name = $row_license['product_name']; $prod_id = $row_license['product_id']; ?> <tr> <td class="row1"><?php echo $prod_name; ?></td><td class="row1"><input type="text" name="product<?php echo $prod_id; ?>" /></td> </tr><?php } } //end getting all products that don't have licenses ?> I'm trying to bring back the first query as an array and put it in this foreach statement so that I can get all of the values for $product_id but I keep getting an error that says: Invalid argument supplied for foreach() in /Applications/MAMP/htdocs/websites/lmanager/admin/modify_accounts.php on line 103 How can I replace the variable $result_license so that it's a variable. At first I thought it would be row_license but that didn't work either. I know it's an array because I'm calling the mysql_fetch_array but it's not working, any ideas? Thanks again for all the help! Quote Link to comment Share on other sites More sharing options...
bruckerrlb Posted April 1, 2010 Author Share Posted April 1, 2010 I just posted my code, but it doesn't make sense without this part, so just wanted to post it so you can see what I"m trying to do here <?php /////////////bring back all licenses and their products $sql_license = "SELECT products.product_id as product_id, products.product_name as product_name , license.license_numbers as l_number, license.company_id as l_company_id FROM products LEFT JOIN license ON products.product_id = license.product_id WHERE license.company_id = '$id'"; $result_license = mysql_query($sql_license); while($row_license = mysql_fetch_array($result_license)) { $product_name = $row_license['product_name']; $product_id = $row_license['product_id']; $l_number = $row_license['l_number']; ?> <tr> <td class="row1"><?php echo $product_name; ?></td><td class="row1"><?php echo $l_number; ?> </td><td class="row1"> <a href="#">Delete</a></td> </tr> <?php } //////end getting back all products with licenses ?> <?php //turn the query into an array for the foreach statement ?> <?php $thequery = mysql_fetch_array($result_license); //get the array of product_id is this right? $product_id = $thequery['product_id']; ?> <?php foreach($thequery as $key => $value) { ///Get All Products that don't have licenses $sql_license = "SELECT * FROM products"; $result_license = mysql_query($sql_license); while($row_license = mysql_fetch_array($result_license)) { $prod_name = $row_license['product_name']; $prod_id = $row_license['product_id']; ?> <tr> <?php //with this method, if the prod_id from the newest query and product_id from the first query are not the same, return those rows if($product_id != $prod_id) { ?> <td class="row1"><?php echo $prod_name; ?></td><td class="row1"><input type="text" name="product<?php echo $prod_id; ?>" /></td> </tr><?php } } } //end getting all products that don't have licenses ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.