Foreign key in phpmyadmin giving error in newsfeed script

General PHP Help
#1

Hi, I am working on newsfeed and I hope I this right but I want that the image that is uploaded, is linked to the user who uploads it. I have 2 tables.

users in here the accounts are stored.

picas in here the uploaded images are stored.

I have in users a primary key called user_id and I have in the picas table a primary key called id.
I made in picas a new colomn called user_id and when an image is uploaded I tell PHP to get the id from the user who is logged in and then insert that within the user_id colomn from the table picas so I can see which user uploaded the image.

So my users table looks like this:

+-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | user_id | int(1) | NO | PRI | NONE | AUTO_INCREMENT | +-------------+-------------+------+-----+---------+----------------+

And my picas table looks like this:

+-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(1) | NO | PRI | NONE | AUTO_INCREMENT | +-------------+-------------+------+-----+---------+----------------+ | user_id | int(1) | NO | | NONE | | +-------------+-------------+------+-----+---------+----------------+

For now I have a problem with the newsfeed. It displays different users with their avatar and names, but if I post something when I’m logged in on an account, it posts for every user. So if I have 5 different users, I log in, I upload an image, then I see 5 posts instant. Check my screenshot here for better display --> http://i.imgur.com/VXwlbra.png

Same goes for the title if you click the image. If I give up a title, then it shows also on the other users.
My code when I upload:

uploadfile.php here I store the uploaded file into the database.

<?php


if (isset($_POST['submit_pica'])) {

	$newName = md5(time().$_FILES['pica']['tmp_name']).'.jpeg';

	$postTitle = $_POST['postTitle'];

    $ses_user = $_SESSION['username'];

    $getuserid = mysqli_fetch_assoc($mysqli->query("SELECT id FROM users WHERE username='$ses_user'"));
    $userid = $getuserid['id'];

	$result = $mysqli->query("INSERT INTO picas (id, name, title, created_at, user_id)
							VALUES (null, '$newName', '$postTitle', null, '$userid')");

    echo "<script>
            $('.upload_button').click(function() {
                $('#uploadform').slideUp(300);
            });
         </script>";

    move_uploaded_file($_FILES['pica']['tmp_name'], 'upload/'.$newName);

}

?>

showimages.php (here I output the images from the database.

<?php
$result = $mysqli->query("SELECT *
                          FROM picas, users
                          ORDER BY created_at DESC");


$ses_user = $_SESSION['username'];

while($pica = $result->fetch_assoc()) {

    echo '<div class="image_post">
            <div class="user_avatar"><img src="avatars/'.$pica['username'].'.jpeg" /></div>
                <div class="user_name">'.$pica['username'].'</div> <br><br><br>
                <div class="image_title">'.$pica['title'].'</div>
                <img src="upload/'.$pica['name'].'" />
    	  </div>';

}

?>

My friend says I had to create a foreign key so it created a relationship between the user_id from picas and user_id from users.

Apparently it didn’t work. I got the latest version from everything and the display I have when going to the relation view tab is this: http://i.imgur.com/2Ovir8R.png

I can only choose from: users or picas table (logic, because those are the only ones I have) and then in the option field right next to it, I can only choose id or user_id (depends on which table I choose).

It gives me the this error: http://i.imgur.com/2ukfKsH.png
In the imgur link you see also what I fill in. I go to the table: picas > structure > relation view

It isn’t working. I hope using the foreign key is the solution to this problem with the newsfeed, because I want that when the image is uploaded, it outputs the user who uploaded it with the uploaded image and not that it does that + every other user that is stored in the database. Please help me. I struggle with this for already a week. It’s driving me crazy!

#2

Well, it appears that your code is almost correct. Ah, well, sort of.

Since you have no validation and zero error checking, there could be a lot of problems in your code.
But, I do not see much wrong. You did not explain how you have debugged this as yet.

You should recode your routines to be a bit more standard and add in some error checking and some
validation. So, if you type in a user id and use the page to submit your info, any of your steps could
be causing the problem.

Normally, you would have it in general terms, something like this:

Open your connection
Check for errors, if errors, stop the process and display a message.

Open your database
check for errors, etc…

For this line:
$getuserid = mysqli_fetch_assoc($mysqli->query(“SELECT id FROM users WHERE username=’$ses_user’”));
and others, you never send one function to another without error checking it first.

How do you expect to pull valid data from a query if the query fails before you attempt to grab it’s results?
(See how messy that sounds…)

You do NOT need to use foreign keys. But, you when you insert a new record into a database, you never
force a NULL ID into a primary-auto-incremented field. So your insert line:
$result = $mysqli->query(“INSERT INTO picas (id, name, title, created_at, user_id)
VALUES (null, ‘$newName’, ‘$postTitle’, null, ‘$userid’)”);
should be more like:
[php]
$result = $mysqli->query(“INSERT INTO picas (name, title, user_id)
VALUES (’$newName’, ‘$postTitle’, ‘$userid’)”);
[/php]
No need for forcing nulls into the database. And, inserting the NULL ID is most likely the issue…

Try rewriting all of your routines to be validated and error-checked and post if you need more help.

Also, please place your code into the PHP tags. Otherwise it is harder to grab your code.

Let us know…

Sponsor our Newsletter | Privacy Policy | Terms of Service