Adding several values to a column/row in PHP & MySQL

[DanC]

I have a script that creates a 'product' in a database and then shows it on a PHP page.

 

 

When a new product is added, a 'generated code' is added to that product, using a few rand functions. A generated code is assigned to each product made. However, as you can see above, I have a 'generate new code' button. Here is what the phpmyadmin view looks like:

 

 

I want to make it so that when you press the 'generate new code' button, it goes to that product in the database and ADDS another code. So I can have multiple generated codes attached to a single product.

 

Is this possible? I have created the generating file & the adding products file, I just need help with the SQL PHP commands to get this 'multiple value add' to work.

 

Thanks!

 

Dan.

[smerny]

sounds like you need to create another database table just for generated codes... have the ID's for the product match them up

[Zane]

yeah..what smermny said

 

Make a separate table for all of these generated codes and fill it up the same way you would the products table.

CREATE TABLE `gcodes` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`gcode` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

 

 

$query = mysql_query('INSERT into gcodes (`gcode`) VALUES ("{$somerandomgeneratedcode}")) or die(mysql_error());
$somerandomgeneratedcode = mysql_insert_id();
$query = mysql_query('INSERT into products (`gen`) VALUES ("{$somerandomgeneratedcode}") WHERE id = 1)) or die(mysql_error());

 

 

 

[DanC]

Ok that works now, thanks, however, how would I go about matching the two ID's and displaying the codes?

 

Here is what I did:

 

mysql_query("INSERT INTO $maintab ($nametab, $linktab, $comptab) VALUES('$name', '$link', '$comp')") or die(mysql_error());

mysql_query("INSERT into $gcodetab ($codetab) VALUES ('$finalshuffle')") or die(mysql_error());

$codegen = mysql_insert_id();

mysql_query("UPDATE $maintab SET gen=$codegen WHERE gen='fill'") or die(mysql_error());

 

Thanks.

[smerny]

http://dev.mysql.com/doc/refman/5.0/en/join.html

[Farmgirl]

I don't think the JOIN would work in this case since the idea was for the PRODUCT to have MANY codes.  At the moment the PRODUCT  has only ONE generated code attached to it. Surely the GEN column needs to be dropped from the PRODUCT table and a another table made which contains ProductID and Gen_ID,  that way they would marry up when being called in a query.

This new table would be in addition to the GENCODE-Table...called something like PROD_GEN.

 

Just my thoughts...but then I am learning too.

 

 

[DanC]

I'm new to MySQL coding, and I was wondering how to do this.

 

 

How would I go about calling the NAME, and then getting the contents of the "GEN" column from the row of the "NAME". I was thinking maybe this:

 

("SELECT gen FROM info WHERE name='new1' ");

 

Is this correct?

 

 

[Farmgirl]

I wouldn't do it that way...since you could run into problems as in your data you may have more than one product called 'new1'.  In relational databases, which MySQL is, you need to use the primary KEY which is UNIQUE and is usually in the first column of your table. So in your case it would be the 'incr' value 1, 2, 3, ...

 

So depending on which 'gen code' for whichever product called 'new1' you want to SELECT from your database you use the Primary KEY for that row...so code would be something like...

 

$sql="SELECT info.gen FROM info WHERE info.incr = 2";

 

 

[DanC]

Hi, thanks for your reply, i'll be sure to use that. However, I have one more question.

 

 

How would I go about selecting and printing all of the 'gcode' codes with the 'gen' of '23361207'.

As you can see, some of the 'gcode' codes have the same 'gen' number. I want to print all of the codes with the gen number of '23361207'.

 

Thanks.

[redarrow]

Not tested, should work, good luck!

 

<?php

//database conection.

$num_to_get=23361207;

$sql="SELECT gcode FROM info WHERE gen='$num_to_get'";
$res=mysql_query($sql) or die("select error".mysql_error());

if(mysql_num_rows($res)>0){

while($data=mysql_fetch_assoc($res)){

echo "$data['gcode'] <br />";
exit;
}
}else{

echo"The $num >$num< you wanted is not in the database corrently"; 
exit;
}
?>