exploding keywords and adding to database

[HDFilmMaker2112]

I'm having a few issues with the following code:

 

<?php
$host="localhost"; // Host name 
$username="username"; // Mysql username 
$password="password"; // Mysql password 
$db_name="zyquo_ghp"; // Database name 
$tbl_name="products"; // Table name 1
$tbl_name2="keywords"; // Table name 2

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$product_name=$_POST['product_name'];
$product_price=$_POST['product_price'];
$product_category=$_POST['product_category'];
$product_link=$_POST['product_link'];
$product_image=$_POST['product_image'];
$product_tag=$_POST['product_tag'];
$keywords=$_POST['keyword'];
$keywords=explode(",",$keywords);
$product_features=$_POST['product_features'];
$product_pros=$_POST['product_pros'];
$product_cons=$_POST['product_cons'];
$product_description=$_POST['product_description'];
$product_notes=$_POST['product_notes'];

if($_GET['do']=="edit"){
}
elseif($_GET['do']=="add"){
$sql10="INSERT INTO $tbl_name (product_name, product_price, product_category, product_link, product_image, product_tag, product_features, product_pros, product_cons, product_description, product_notes) VALUES ('$product_name', '$product_price', '$product_category', '$product_link', '$product_image', '$product_tag', '$product_features', '$product_pros', '$product_cons', '$product_description', '$product_notes')";
mysql_query($sql10);
$sql11="SELECT product_id FROM $tbl_name WHERE product_name='".$product_name."'";
$result11=mysql_query($sql11);
while($row11=mysql_fetch_array($result11)){
$product_id2=$row11['product_id'];
foreach($keywords as $keyword){
$sql13="SELECT keyword_id FROM $tbl_name2 WHERE keyword='".$keyword."'";
$result13=mysql_query($sql13);
$row13=mysql_fetch_array($sql13);
$keyword_id=$row13['keyword_id'];
if(mysql_num_rows($result13) != 0){
$sql12="INSERT INTO $tbl_name2 (keyword_id,keyword,product_id) VALUES ('$keyword_id','$keyword','$product_id2')";
mysql_query($sql12);
}
else{
$sql14="INSERT INTO $tbl_name2 (keyword,product_id) VALUES ('$keyword','$product_id2')";
mysql_query($sql14);
}
}
}
}
else{
}
?>

 

It should be taking a list of keywords coming from the form on the previous page (in a,b,c,d, ect. list form), breaking them up and putting them into a $keywords array. From there, it should be looping through each keyword, under the foreach statement, until it runs out of keywords. Under the foreach statement, it should be checking to see if the keyword is already in the database; and if not, create a new entry with a new keyword_id. If is already in the database add another entry with same keyword_id.

 

Now for what it's actually doing. It seems to be looping through once (I am only adding one keyword right now, so this makes sense) but it throws an error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result on line 39

 

Line 39 is: $row13=mysql_fetch_array($sql13)

 

It does add the information to the database while it throws that error. However, it doesn't seem to detect that the keyword is already in the database, so it creates a new keyword_id when it should take the existing id for the existing keyword in the database.

[phppaper]

$row13=mysql_fetch_array($sql13) <- wrong,

 

should be $row13=mysql_fetch_array($result13)

[HDFilmMaker2112]

Alright, that solved the mysql error. Now, the problem is, if the keyword already exists, it is not being added to the database. I just added a new product with an existing keyword, and it submitted to the products table, but nothing was added to the keywords table... No entry with a new keyword_id nor a new entry with the same keyword_id.

[HDFilmMaker2112]

Alright, that solved the mysql error. Now, the problem is, if the keyword already exists, it is not being added to the database. I just added a new product with an existing keyword, and it submitted to the products table, but nothing was added to the keywords table... No entry with a new keyword_id nor a new entry with the same keyword_id.

 

Just wanted to provide some additional details, if this makes any difference:

 

keyword_id is structured as an int(11) field with Auto_Increment.

[HDFilmMaker2112]

Well after some research it seems like my problem is that keyword_id is the primary key in the table. So I created a new column and made that the primary key, freeing up the keyword_id.

 

But I have a new issue now. In order to generate a new keyword_id for a keyword that is new in the table, I need to get the current max keyword_id and increment by 1.

 

else{
$sql15="SELECT MAX(keyword_id) FROM keywords";
$result15=mysql_query($sql15);
while($row15=mysql_fetch_array($result15)){
$max_keyword_id=$row15['keyword_id'];
$keyword_id=$max_keyword_id++;
}
$sql14="INSERT INTO $tbl_name2 (keyword_id,keyword,product_id) VALUES ('$keyword_id','$keyword','$product_id2')";
mysql_query($sql14);
}

 

when I run the above it generates a keyword_id of 0, it should be 6 as the highest keyword_id in the table is 5.

[mikosiko]

I'm kind of lost... for better help, could you explain what exactly are you trying to do... seems to me that: (correct me if I understood incorrectly)

- You are capturing products and associated information.

- Part of that additional information seems to be a series of "keywords" that in some way could help to describe that specific product

- Next you want to create the record in the table product  and along with that also insert all the "keywords" associated to that product in the table "keywords"

 

Did I understand your objective correctly?

[HDFilmMaker2112]

I'm kind of lost... for better help, could you explain what exactly are you trying to do... seems to me that: (correct me if I understood incorrectly)

- You are capturing products and associated information.

- Part of that additional information seems to be a series of "keywords" that in some way could help to describe that specific product

- Next you want to create the record in the table product  and along with that also insert all the "keywords" associated to that product in the table "keywords"

 

Did I understand your objective correctly?

 

That is indeed what I was trying to accomplish, and I just managed to get it to work 2 minutes ago.

[mikosiko]

why are you using 2 id's for the table keywords?... doesn't make sense to me... but of course I can be wrong understanding that part of your objectives.

[HDFilmMaker2112]

why are you using 2 id's for the table keywords?... doesn't make sense to me... but of course I can be wrong understanding that part of your objectives.

 

What do you mean by 2 ids?

 

I have one for product_ids and one for keyword_ids

 

so my table looks like this:

 

keyword_id | keyword | product_id

1 |test | 1

1 |test | 4

2 |test2 | 1

2 |test2 | 5

3 |test3 | 2

3 |test3 | 3

ect.

 

I also had to add a fourth column to free up keyword_id to permit duplicate entries. So that fourth column is now the primary key.

[mikosiko]

I was referring exactly to this:

"I also had to add a fourth column to free up keyword_id to permit duplicate entries. So that fourth column is now the primary key."

not sure why you need those 2 columns...  to me seems logical to have only one... the auto-generated

Id  | keyword_id | keyword | product_id

 

 

and BTW... I'm asking because I believe that you code could be improved... but of course you can be happy with what you have already