inserting categories in one Cell - PHP mySQL

[lasha]

Hello,

 

I tried to write a script for a long time, but unfortunately failed ...

Please Help me, I think it should not be difficult but i can't reach a solution...

 

I have a profile that can belong to several categories ...

How can we do so that this selected categories placed in one cell of mySQL db?

Please help me in this problem to find a solution...

 

Best wishes, Lasha

[Jessica]

I have a profile that can belong to several categories ...

How can we do so that this selected categories placed in one cell of mySQL db?

 

You don't, you have another table with relations of products to categories.

[Psycho]

I have a profile that can belong to several categories ...

How can we do so that this selected categories placed in one cell of mySQL db?

 

You don't, you have another table with relations of products to categories.

 

To build upon what Jesirose has stated, your tables might look something like this:

 

Categories:

cat_id | cat_name
1        Cat 1
2        Cat 2
3        Cat 3

 

Users

user_id | name
1        Adam
2        Bob
3        Carl

 

user_cats (this associates users with one or more categories)

user_id | cat_id
1        2
2        1
2        3
3        1
3        2
3        3

 

So, from the above data you can see that:

User Adam is associated with Category 1

User Bob is associated with categories 1 & 3

User Carl is associated with categories 1, 2 & 3

[lasha]

Thanks for reply

 

Psycho,

I thought make such but I cant write code, i dont know how ((

 

May be have some example?

[Barand]

@Lasha

 

Except for storing the name instead of the user id, i gave you the code to do exactly that in your previous topic

 

http://forums.phpfreaks.com/index.php?topic=364717.msg1728061#msg1728061

[lasha]

Ok i see that i need something like that, but how to store ids of profile and categories in this new table ... ?

 

And even than i need retrieve it from database... can you show me please?

 

 

user_id | cat_id

1            2

2            1

2            3

3            1

3            2

3            3

[lasha]

:'( 2 days i am trying to make it but always error 

 

If it is not problem for you to show me how do it please  :-\

[Christian F.]

If you can post what you've attempted to write so far, then we can tell you what's wrong and why.

[Psycho]

:'( 2 days i am trying to make it but always error 

 

If it is not problem for you to show me how do it please  :-\

 

Lasha,

 

This forum is intended for people to get help on code they have written. It is expected that the person asking for help has the basic knowledge necessary to take advice given and implement it or only needs very specific help. The probable reason no one has responded is that, based on your comments, you don't have the necessary knowledge to implement an appropriate solution and we would either have to write it all for you or this thread would turn in to a long tutorial.

 

Having said that I will at least provide some guidance:

 

1. Remove the cat_id field from the table.

 

2. Create a new table called categories with two fields: cat_id (auto_increment) and cat_name

 

3. Create a new table called user_cats with two field: user_id and cat_id

 

4. When creating the form, run a query on the category table and create the checkboxes using something like this

while($row = mysql_fetch_assoc($result))
{
    echo "<input type='checkbox' name='cat_ids[]' value='{$row['cat_id']}'> {$row['cat_name']}<br>\n";
}

 

5. When the user submits the form you will create the record as I assume you are already doing for all the fields that are in that table. After running the insert query use mysql_insert_id() or mysqli_insert_id() to get the auto-increment id that was created for that record (i.e the user_id).

 

6. Using the user_id from the previous step and the submitted categories, create an insert statement to create the records in the user_cats table. Example code:

$values = array();
foeach($_POST['cat_ids'] as $cat_id)
{
    $cat_id = int_val($cat_id));
    $values[] = "('$user_id', '$cat_id')";
}
$query = "INSERT INTO user_cats (user_id, cat_id) VALUES " . implode(', ', $values);
$result = mysql_query($query);

 

Then, when you need to get the user's data along with their categories you can run a query such as this

SELECT user.name, user.surname, user.facebook_link, categories.name
FROM users
LEFT JOIN user_cats ON user.user_id = user_cats.user_id
LEFT JOIN categories ON user_cats.cat_id = categories.cat_id

[lasha]

Alright, thank all of you for help... 

 

Psycho, Barand you gave me a lot of knowledge in PHP mySQL programming, of course this was not perfect codes but it was "perfect help"... 

 

I know that, this forum is not for to give someone orders, i just wanted help and i get it... You helped not only me, this thread will see many people who needs help in coding... I was looking for help in google but this was everywhere unsolved problem... So thank you again and there is code that i performed with your Indications and this works perfectly (if something is not good written correct me please):

 

P.S. I am not a English speaker, sorry for that...

 

Tables

 

Table category --> user_id | cat_id

 

Table info --> id | name | sur

 

Table info_cats --> info_id | cat_id

 

 

 

This is form.php

 

<?php include ("db.php");
    $sql = "SELECT cat_id, cat_name FROM category";
    $res = mysql_query($sql) or die(mysql_error());
    
    $checkboxes = '';
    while (list($id, $cat) = mysql_fetch_row($res)) {
        $checkboxes .= "<input type='checkbox' name='category[]' value='$id' /> $cat<br />";
    }
?>

<form method = 'post' action = 'process.php' >
    Name: <input type='text' name='name' size='50' /><br />
    Surname: <input type='text' name='sur' size='50' /><br />
    Categories:<br />
    <?php echo $checkboxes ?>
    <input type='submit' name='btnSubmit' value='Submit' />
</form>

 

 

 

This is process.php which Produces inserting in DB

 

<?php include ("db.php"); 

if (isset($name) && isset($sur))
{
$result = mysql_query ("INSERT INTO info (name, sur) VALUES ('$name','$sur')");

if ($result == 'true') 
{
$caturi =  mysql_insert_id();
}

else {echo "<p>Your actions are not completed!</p>";}
}		 
else 
{
echo "<p>Enter information completely!</p>";
}

if (isset($_POST['category'])) {
        
foreach ($_POST['category'] as $cat) {
$values[]  = sprintf ("(%d, '%s')", intval($cat), mysql_real_escape_string($caturi));
}
    $sql = "INSERT INTO info_cats (cat_id, info_id) VALUES " . join(',', $values);
    mysql_query($sql);
}


?>

 

 

And last one for display from db by categories

 

<?php include ("db.php"); 

$result = mysql_query("
SELECT info.name, info.sur, category.cat_name
FROM info
LEFT JOIN info_cats ON info.id = info_cats.info_id
LEFT JOIN category ON info_cats.cat_id = category.cat_id
WHERE cat_name='category 3'",$db);

if (!$result)
{
echo "<p>The operation unsuccessful ... Please contact the administrator by e-mail: lasha.wm@gmail.com <br> <strong>Error:</strong></p>";
exit(mysql_error());
}
if(mysql_num_rows($result) > 0)
{
$myrow = mysql_fetch_array($result);
do
{
printf ("%s %s | %s<br>\n",$myrow["name"],$myrow["sur"],$myrow["cat_name"]);
}
while ($myrow = mysql_fetch_array($result));	
}
else
{
echo "<p>Information not found...</p>";
exit();
}


?>