script problems with multiple tables again....

webgod

Ok, I have been trying to get the hang of multiple tables for a project. So far I have used an admin script with no problems when I used only one table.

I am slowly adding multiple table support to this script. I figure conquer one problem at a time... however each time I try to fix a problem, the more that pop up.

The script so far starts with a waning of Invalid argument supplied for foreach(). This most likely is my partial support for multiple tables, but I don't know where. I can update, but not insert and the update only works on the Business table so far.

Here is the line that seems to be the problem:

// update BUSINESS table

$SQL="UPDATE Business SET b_name='$b_name', b_address='$b_address', b_city='$b_city', b_state='$b_state', b_image='$b_image' WHERE bid=$bid";
$result=mysql_query($SQL,$dB);

// update BUSINESS_CATEGORY table

foreach($_POST['parent'] as $val)
{
	$SQL2="UPDATE Business_Category SET bid='$bid' WHERE cid='$val'";
	$result2=mysql_query($SQL2,$dB);
}

It could be a problem else where in the script, so I made a copy of everything for testing (please be nice):
http://atomiccoffeemedia.com/southms/admin/

I have added a "view source" link so you can see what is going on (and not post the whole code here🙂. The Category link works great, because it uses only one table. The Business Category link is the one that uses multiple tables, so that is what is going bad.

I know that there is probably a million things wrong here, but any help would be sooo much appreciated. I have been working on this for days, and fresh eyes are needed!

Here are my tables:

Business;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| bid             | mediumint(8) unsigned |      | PRI | NULL    | auto_increment |
| b_name          | varchar(50)           |      |     |         |                |
| b_address       | varchar(100)          |      |     |         |                |
| b_city          | varchar(30)           |      |     |         |                |
| b_state         | char(3)               |      |     |         |                |
| b_zip           | int(12)               |      |     | 0       |                |
| b_county        | varchar(30)           |      |     |         |                |
| b_phone         | int(11)               |      |     | 0       |                |
| b_contact_first | varchar(20)           |      |     |         |                |
| b_contact_last  | varchar(30)           |      |     |         |                |
| b_website       | varchar(100)          |      |     |         |                |
| b_email         | varchar(100)          |      |     |         |                |
| b_image         | varchar(100)          |      |     |         |                |
| b_paid          | char(3)               |      |     |         |                |
| b_comment       | text                  |      |     |         |                |
+-----------------+-----------------------+------+-----+---------+----------------+

Business_Category;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bid   | int(10) unsigned |      | PRI | 0       |       |
| cid   | int(10) unsigned |      | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+

Category;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| cid    | int(11)      |      | PRI | NULL    | auto_increment |
| name   | varchar(100) |      |     |         |                |
| parent | varchar(100) |      |     |         |                |
+--------+--------------+------+-----+---------+----------------+

barand

If you're getting a warning with foreach it's probably that $_POST['parent'] is not an array.

webgod

Hmmm... I think it should be. First off, I'm confused why I get the error on just loading the page.. the code that is having the problem should only occur on UPDATE. However, here is my form, the multiple select is supposed to be the array..

As I said, It might be in a different area of the form, if you have a spare moment to look at the code from the link I gave above, i would be SOOO grateful...

here is my form

<form name="author_admin" method="post" action="<?php echo $PHP_SELF;?>" onSubmit="return verify()" enctype="multipart/form-data">
<table width="600" border="0" cellspacing="2" cellpadding="2" class="content">
	<TR><TD>
	<table width="400" border="0" cellspacing="2" cellpadding="2" class="content">
		<TR><TD>Business: </TD><TD><input type="text" name="b_name" value="<?php echo $b_name;?>"></TD></TR>
		<TR><TD valign="top">Address: </TD><TD><input type="text" name="b_address" value="<?php echo $b_address;?>"></TD></TR>
		<TR><TD>City/State/Zip: </TD><TD><input type="text" name="b_city" value="<?php echo $b_city;?>"> <input type="text" name="b_state" value="<?php echo $b_state;?>" size="3"><input type="text" name="b_zip" value="<?php echo $b_zip;?>" size="6"></TD></TR>
		<TR><TD>County: </TD><TD><input type="text" name="b_county" value="<?php echo $b_county;?>"></TD></TR>
		<TR><TD>Phone: </TD><TD><input type="text" name="b_phone" value="<?php echo $b_phone;?>"></TD></TR>
		<TR><TD>Contact(First/last): </TD><TD><input type="text" name="b_contact_first" value="<?php echo $b_contact_firts;?>"> <input type="text" name="b_contact_last" value="<?php echo $b_contact_last;?>"></TD></TR>
		<TR><TD>Websote: </TD><TD><input type="text" name="b_website" value="<?php echo $b_website;?>"></TD></TR>
		<TR><TD>Email: </TD><TD><input type="text" name="b_email" value="<?php echo $b_email;?>"></TD></TR>
		<TR><TD>Picture: </TD><TD><?php if ($t != 4) { echo "<A HREF=\"../images/food/$Menu_pict\" onclick=\"NewWindow(this.href,'Photo','350','350','no');return false\">$Menu_pict</A>"; }?><BR><input type="file" name="Menu_pict"> (optional)</TD></TR>
		<TR><TD></TD><TD><input type="submit" name="submit" value="Insert/Update"><P>
		<input type="hidden" name="t" value="<?php echo $t_value;?>">
		<input type="hidden" name="bid" value="<?php echo $bid;?>">
		<a href="<?php echo $PHP_SELF;?>">Insert New Records</a></TD></TR>
	</TABLE>
	</TD><TD VALIGN="top" ALIGN="right">
	Categories<BR>
	<?php
	$sql_select ="SELECT DISTINCT name FROM Category";
	$sql_select_result =mysql_query ($sql_select,$dB) or die ("Couldn't execute 'select' query.");

echo "<select name=\"parent[]\" id=\"parent\" MULTIPLE>";
echo "<option value=\"PRIMARYCATEGORY\">PRIMARYCATEGORY</option>";
while ( $row =mysql_fetch_array ($sql_select_result )){
if ($parent == $row['name']) {
echo '<option value="'.$row['name'].'" selected>'.$row['name'].'</option>';
}
else {
	echo '<option value="'.$row['name'].'">'.$row['name'].'</option>';
	}
}
echo "</select>"
?>
</TD></TR></TABLE>
</form>

ahundiak

Looks like you have two primary keys in your business_category table. You need to make then just regular integers and then do an insert operation for each combination of business and category.

CREATE TABLE business_category
(
INTEGER bid,
INTEGER cid
);

-- This will prevent having more then one entry per bid-cid
CREATE UNIQUE INDEX bus_cat_index ON business_category (bid,cid);

webgod

Thanks for the table structure tip, however, this didn't really fix my problem. I did change the table structure though..

ahundiak

One problem is that you cannot always rely on $_POST['parent'] being set since if nothing was selected then nothing will be posted. This is what is causing the foreach errors. So do something like:

$parents = $_POST['parent'];
if ($parents == null) 
{
  echo 'NO Parents!'
}
else
{
  /* Do the insert thing */
  foreach($parents as $parent)
  {
    echo 'Parent ' . $parent;
  }
}

webgod

Ok.. to stop the page from complaining, I put a conditional
if ($_POST['parent'])

This stops the page from displaying the error, however, It still doesn't insert or update the data into the Business_Category tables.

Can anyone spot what is up with this? Here is the source code:

http://atomiccoffeemedia.com/southms/admin/view_business.php

barand

$cid is an integer and you are posting a name.

webgod

Oups... fixed that.. still no go:

	<?php
	$sql_select ="SELECT DISTINCT name, cid FROM Category";
	$sql_select_result =mysql_query ($sql_select,$dB) or die ("Couldn't execute 'select' query.");

echo "<select name=\"parent[]\" id=\"parent\" MULTIPLE>";
echo "<option value=\"PRIMARYCATEGORY\">PRIMARYCATEGORY</option>";
while ( $row =mysql_fetch_array ($sql_select_result )){
if ($parent == $row['name']) {
echo '<option value="'.$row['cid'].'" selected>'.$row['name'].'</option>';
}
else {
	echo '<option value="'.$row['cid'].'">'.$row['name'].'</option>';
	}
}
echo "</select>"
?>

Update doesnt still only updates one table..

Insert doesn't work at all, in fact it gives a warning of Call to undefined function: last_insert_id().

barand

Try [man]mysql_insert_id[/man]

ahundiak

Next thing to do is to add some error checking to your mysql functions. Something like

mysql_query($SQL);
if (mysq_errno()) die (mysql_error());

webgod

Hmm... I have tried to turn it on for insert and get this:

Column count doesn't match value count at row 1

Not sure about where or what that problem exists...

http://atomiccoffeemedia.com/southms/admin/view_business.php

ahundiak

It means that the number of items in your VALUES() clause does not match the number of columns in the table. Since you did not specify a list of column names, there must be an exact match.

You might try echoing out the $SQL as part of the die message to make it easier to see what is going on.

For autoincrement fields, use the word NULL instead of ''.

$SQL="INSERT INTO whatever VALUES(NULL,'col1value','col2value')";

webgod

Ah... I figured out two of the problems. First of, I am not really sure why last_insert_id() doesn't work and mysql_insert_id() does, but hey it works now.
The insert problem was due to not identifying all of the columns (I added a few)

So, now I can insert and delete just fine (WHOOHO), Thanks a bunch guys!

however, update still seems to be a problem...

I switced the update to look like this:

foreach($_POST['parent'] as $val)
{
	$SQL2="UPDATE Business_Category SET cid='$val' WHERE bid='$bid'";
	$result2=mysql_query($SQL2,$dB);

}

However, it seems to not take away or add variables. Let's say I had three categories. then I change it to just one category... the update instead of deleting two and changing one, just repeats the number three times..

+-----+-----+
| bid | cid |
+-----+-----+
| 662 |   2 |
| 671 |  78 |
| 671 |  80 |
| 671 |  71 |
| 671 |   0 |
+-----+-----+
5 rows in set (0.00 sec)

mysql> select * from Business_Category;
+-----+-----+
| bid | cid |
+-----+-----+
| 662 |   2 |
| 671 |  71 |
| 671 |  71 |
| 671 |  71 |
| 671 |  71 |
+-----+-----+

Full Source Code:
http://atomiccoffeemedia.com/southms/admin/view_business.php

ahundiak

Glad you are making progress.

I think you may have a conceptual error with respect to the bus_cat table. This is a many-to-many relationship table i.e. each business can have multiple categorys and each category can have multiple businesses. You are not going to be able to update this table that way you are trying to do so.

What you need to do (each time the user posts) is to first delete all entries for a given bid then insert new entries for each bid/cid combination.

webgod

Ahhh.... fixed. That was definately the key. Sometimes the simple things are the most troublesome.

I have one more quesiton (promise)....

when updating an item, my select box doesn't seem to select the items for each category... I tried about 12 things, but can't seem to figure this out...

	<?php
	$sql_select ="SELECT DISTINCT name, cid FROM Category";
	$sql_select_result =mysql_query ($sql_select,$dB) or die ("Couldn't execute 'select' query.");

echo "<select name=\"parent[]\" id=\"parent\" MULTIPLE>";
while ( $row =mysql_fetch_array ($sql_select_result )){
if ($parent == $row['cid']) {
echo '<option value="'.$row['cid'].'" selected>'.$row['name'].'</option>';
}
else {
	echo '<option value="'.$row['cid'].'">'.$row['name'].'</option>';
	}
}
echo "</select>"
?>

ahundiak

It should work. I noticed you were doing some javascript processing on submit. You might try just doing a plain submit to an almost empty page and just echo out the parent values.