Page 1 of 1

adding content to mysql database

Posted: Thu Oct 28, 2004 11:56 pm
by jasemhi
Hello- I have a site that pulls up images based specific styles. For example, a user chooses style 1 from a pull down menu and a list of images comes up. I would like to add the image to the user's table by having them double click on the image and a pop up asks if they want to add the image to thier list (after php checks if the image is already in the user's list and determines it's not). If the image is in the user's list already, the pop up would ask the user if they would like to remove the image from their list. Is this possible?

I have a membership system in place, but have not even started with adding images to the user's table. How can I get started?

Thanks for your help.

Posted: Sun Oct 31, 2004 10:14 am
by swdev
add a column to the users table, say varchar(255) type column. This can hold the path to the image fiile.
When the user double clicks on the image, you run a query that retrieves this image link column from the database. If it is empty, they you can add the filename of the image to the databse with a simple update query. If it is not empty, then yiu can ask the user whether they want to delete the exsiting image.

If the user can have multiple images, then you would need a separate table to hold the list of images for each user.

adding content to mysql database

Posted: Wed Nov 03, 2004 3:46 pm
by jasemhi
The user can have multiple images in their table. Therefore, I have a users table that has all the information on the user. It's primary key is user_id . I have a users_image table that has a unique key for users_id and image_id. I want to be able to join the two tables by the user's user_id key, find if the image is in their users_image table, and add it if it isn't, or delete it if it is. How do I go about doing this? I'm thinking that this code (that doesn't work) is the way to get started:


Code: Select all

$id = $_GETї'id']; 
$sql = "SELECT users.id, users_image.image_id
FROM users
LEFT JOIN users_image ON users.id = users_image.id
WHERE users_image.id = $id";

$result = mysql_query($sql);
$row = mysql_fetch_array($result);

$users_id = $rowїusers_"id"];
$image_id = $rowї"image_id"];

if($image_id != $users_id){ 
Code.....
How do I go about the if statement to look for the image_id in the image_id column and then add the image_id to the list if it isn't there? Any help on this is much appreciated. :)

Posted: Wed Nov 03, 2004 4:31 pm
by swdev
In this case, there is no nned for a JOIN.

I am going to assume that by the time you need to do this check you have both the user_id and the image_id.

I haven't tried out this code, it is straight of the top of my head,so apologies for typing mistakes

Code: Select all

// code here get the user's id into $user_id
// code here to get the image id into $img_id;

$sql  = 'SELECT ';
$sql .= ' users_id, image_id ';
$sql .= ' FROM ';
$sql .= 'users_image ';
$sql .= ' WHERE ';
$sql .= '( users_id = ' . $user_id . ') ' ;
$sql .= ' AND ';
$sql .= '( image_id = ' . $img_id . ') ' ;

// code here to connect to the database if not already connected
$result = mysql_result($sql);
if (false != $result)
{
  $num_rows = mysql_num_rows($result);
  if (0 == $num_rows)
  {
     // no data found, so INSERT this user_id / img_id to the database
  }
  else
  {
    // image found for this user, so DELETE this user_id / img_id from the database
  }
}
else
{
  // some kind of sql error
}
Hope this helps

Posted: Wed Nov 03, 2004 5:01 pm
by John Cartwright
This is bad practice if i've seen it....

Code: Select all

<?php
$sql  = 'SELECT ';
$sql .= ' users_id, image_id ';
$sql .= ' FROM ';
$sql .= 'users_image ';
$sql .= ' WHERE ';
$sql .= '( users_id = ' . $user_id . ') ' ;
$sql .= ' AND ';
$sql .= '( image_id = ' . $img_id . ') ' ;
?>

Posted: Wed Nov 03, 2004 5:18 pm
by jasemhi
Thanks for the reply swdev. I will work with it and let you know.

Phenom- how what would you code it?

Posted: Wed Nov 03, 2004 5:33 pm
by John Cartwright
no need to keep adding to the string when you can do it in one blow...

Posted: Wed Nov 03, 2004 7:24 pm
by jasemhi
How do I update a table by having a user click yes? I have the id for both the image and the user, I now want to update the table if the user hits 'yes' for the question "add this image to your list of images?"