[SOLVED] Problem with insert in correct format

[grumpy]

Hi--

I'm having trouble with an insert. My form has Slect Multiple field where users can select category names. This then goes to my processing page. I'm trying to then insert the categories into my table, cat_relations, by the ID matches the category (from my table work_cats), with each category ID in its own row. The insert is working but its inserting the category name rather then the ID that matches the category. I need it to insert the ID that matched the category selected not the category name string. Below is my code. What am I doing wrong? Thanks for your help.

 

// my form

<?

$sql = "SELECT * FROM work_cat ORDER BY cat_name ASC";

 

$result = mysql_query($sql);

 

 

 

?>

<form name="addcats" action="do_addcats2.php" method="POST">

<p>Select a first primary category<br>

<SELECT name="co_workcat[]" size="4" multiple>

<?php

while ($row = mysql_fetch_array($result)) {

$i = $row["cat_name"];

echo '<OPTION value="' . $i . '">' . $i . '</OPTION>';

}

?>

</select>

<p><input type=submit name=submit value=SUBMIT>

 

 

//my processing page

 

$ID = 4;

 

$cats = $_POST['co_workcat'];

foreach($cats as $cat) {

$cat_list1 .= $cat . ' ';

}

;

 

 

$query = mysql_query("SELECT ID FROM work_cat where cat_name = '$cat_list1'");

$result = $query;

// put data into drop-down list box

while ($row = mysql_fetch_array($result)) {

echo ($row["ID"]);

$cat_list1 = $row["ID"];

};

 

 

$sql = "INSERT INTO cat_relations SET relation_usr_id='$ID',relation_cat='$cat_list1'";

if (@mysql_query($sql)) {

echo ("Good");

 

} else {

echo("<p>Error adding cats: " .

mysql_error() . "</p>");

}

 

 

 

 

 

[rajivgonsalves]

here is where your going wrong

 

$i = $row["cat_name"]; 
echo '<OPTION value="' . $i . '">' . $i . '</OPTION>'; 

 

should be

 

$i = $row["cat_name"]; 
$strCatId = $row["cat_id"]; 
echo '<OPTION value="' . $strCatId . '">' . $i . '</OPTION>'; 

[grumpy]

Thanks, but still a problem. I made the change you suggested and now my insert inserts nothing, neither the category string or the ID. Do I nedd to make some other change?

[rajivgonsalves]

what is the column name for the id can you tell me

[grumpy]

The categories ID numbers should be inserted into table 'cat_relations' in column 'realtion_cat', one category ID per row.

[grumpy]

Correction: column 'relation_cat'

[rajivgonsalves]

sorry not reading too well lot of work i've done

 

i

 = $row["cat_name"]; 
$strCatId = $row['relation_cat']; 
echo '<OPTION value="' . $strCatId . '">' . $i . '</OPTION>'; 

 

relation_cart or whatever your cat id is just get it there and it should work

[grumpy]

Still inserting nothing

[grumpy]

Also if more than one category is selected, only one gets inserted.

[rajivgonsalves]

ok try this last try

 

$strCatId = $row['ID']; 
echo '<OPTION value="' . $strCatId . '">' . $i . '</OPTION>'; 

[rajivgonsalves]

well give me ur msn id we'll get it solved 

[grumpy]

Still not working. I don't have MSN

[rajivgonsalves]

hope this will work

 

for processing page

 

$

$ID = 4;
$cats = $_POST['co_workcat'];
foreach($cats as $cat) {
$cat_list1 .= $cat . ' ';
}
;


$query = mysql_query("SELECT ID FROM work_cat where cat_name = '$cat_list1'");
$result = $query;
// put data into drop-down list box
while ($row = mysql_fetch_array($result)) { 
echo ($row["ID"]);
$cat_list1 = $row["ID"];
};

foreach ($cats as $strKey => $strValue)
[
$sql = "INSERT INTO cat_relations SET relation_usr_id='$ID',relation_cat='$strValue'"; 
if (@mysql_query($sql)) { 
echo ("Good"); 

} else { 
echo("<p>Error adding cats: " . 
mysql_error() . "</p>"); 
}
}

 

[grumpy]

Nows its retrieving the ID's but:

1 its entering the category id's into the relation_usr_id column, one per row, instead of the relation_usr_id

2. its inserting all the category ID's into each row in the relation_cat column, insted of one category id per row

 

 

[rajivgonsalves]

Ok is there any way i can get you on chat (MSN/Yahoo/etc..)

[rajivgonsalves]

Actually I changed my post because it was wrong can you try could from my previous post

[grumpy]

THANK YOU!!! It works. I really appreciate all your help! I might need your help later when I do a page where the user can access an update page and see the categories stored in the database and then change them

[rajivgonsalves]

No problem dude!, anytime I'm here