ForEach question

bruckerrlb · 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?

deansaddigh · March 31, 2010

Should you have done a left join?

http://www.w3schools.com/sql/sql_join_left.asp

bruckerrlb · 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?

ignace · 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

bruckerrlb · 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

ignace · 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

bruckerrlb · 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!

bruckerrlb · 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
  ?>

Sign In

ForEach question

Recommended Posts

bruckerrlb

Link to comment

Share on other sites

deansaddigh

Link to comment

Share on other sites

bruckerrlb

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

bruckerrlb

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

bruckerrlb

Link to comment

Share on other sites

bruckerrlb

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information