UPDATE / INSERT / JOIN problems

[triole1]

Hello,

 

I am using MySQL 5.1. I have a php form to upload files. I have a table just for that where i also want to store the user's ID, name, and a link to the file. The upload form is ok (it populates the db with the link and the file's going to the server), but I can't i pull the users info from the signup table where the rest of the information is (ID, name, email)

 

Can you please help me?

 

Thanks a lot!

[Muddy_Funster]

can we see your current SQL please?

 

Also, you already have the name attached to the id in the users table, you do not need to store it again in the files one.  How are you identifying the user that is using the upload form? are you using sessions or is the user id passed through the form as well?

[triole1]

HI, Thanks for your reply,

 

I want to pull the name and email from india to photo_india to the same line that the user is uploading the photo. The form is only a 'choose file' button. I am using sessions to identify the user. 

 

I would like the photo_india to be something like:

 

ID  Name  email  link

1      a          b        c

2      a          b        d 

3      e          r        t

4      e          r        j

 

If you know a better way to do this, please tell me.

 

This is the table where i store the signup information:

 

CREATE TABLE `india` (

`id` varchar(65) COLLATE latin1_general_ci NOT NULL,

`name` varchar(50) COLLATE latin1_general_ci NOT NULL,

`password` varchar(50) COLLATE latin1_general_ci NOT NULL,

`email` varchar(50) COLLATE latin1_general_ci NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

 

 

This is the table where I want to store the id, name, email and photo's link:

CREATE TABLE `photo_india` (

`id` varchar(65) COLLATE latin1_general_ci NOT NULL,

`name` varchar(65) COLLATE latin1_general_ci NOT NULL,

`email` varchar(65) COLLATE latin1_general_ci NOT NULL,

`photo` varchar(65) COLLATE latin1_general_ci NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

[Muddy_Funster]

Like I said, as you already have the name and email info in the india table it makes sense not to double up on that info (google data normalisation when you have a couple hours free).

So, your photo_india table only has ID, userID and URL.  Use the following replacing the PHP variables with the ones that you are using:

INSERT INTO photo_india (userID, URL) VALUES ($_SESSION['userID'], '$url')

to insert the info into the table.

When you want to pull the name and email of the user and attach it to the image use:

SELECT name, email, url FROM india LEFT OUTER JOIN photo_india ON (india.ID = photo_india.userID) where india.ID = $user_id_to_show

[triole1]

Thanks a lot but something is going wrong, my mistake for sure!.. can you find what it is??

 

here goes the code:

 

<?php

 

  // Connects to your Database through config.php

include_once 'config.php';

 

session_start();

 

//This is the directory where images will be saved

$target = "images/";

$target = $target . basename( $_FILES['photo']['name']); 

 

//This gets all the other information from the form

$pic=($_FILES['photo']['name']);

 

//Writes the information to the database

mysql_query("INSERT INTO photo_india (id,photo) VALUES (".$_SESSION['id'].", '$pic') ") ;

 

//Writes the photo to the server

if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))

{

 

//Tells you if its all ok

echo "The file  has been uploaded, and your information has been added to the directory";

echo "<br /><a href='upconf.php'>Confirm</a>";

 

}

else {

 

//Gives and error if its not

echo "Sorry, there was a problem uploading your file.";

echo "<br /><a href='index.php'>Try again</a>";

}

 

?>

[Muddy_Funster]

What is going wrong and where is it going wrong?

for better error checking in development you should always use something to capture any possible errors in your database queries.  The easiest way is to use the OR DIE function call:

mysql_query("INSERT INTO photo_india (id,photo) VALUES (".$_SESSION['id'].", '$pic') ") or die (mysql_error()); 

This can also be expanded on if you assign your query string to a variable before trying to execute it:

$sql = "INSERT INTO photo_india (id, photo) VALUES ({$_SESSION['id']}, '$pic')";
mysql_query($sql) or die ("The query : <br>$sql<br><br> Returned the following error :<br>".mysql_error());