Inserting data from one table to another table almost working

Question

I have two tables in my database, the first table called users and it contains: id, firstname, lastname, email, password, hash, active.

The second table called listing and it contains: user_id, user_email, listing_id, listing_title, list_description, listing _country, listing_city, listing_image.

I want to make sure that when a user is logged in to the website and creates a new listing, the listing will be recognized by id and email.

So basically i want to take the information from id and email that is on the users table and to insert it to the user_id and user_email columns on the listing table.

And i did it, sort of.

The problem is that if i'm logged in to the website and create a listing, the database shows the wrong user_id and user_email on the listing table. So for example:

if i'm logged in as [email protected] with the user id of 1 and i will create a new listing, when i go to the listing table i can see that the user_id and user_email are different and it pulls data from the latest row on the users table and not the user that is actually logged in.

Hope i made myself clear.

Here is the code:

this is a file called 'new-listing-functions.php'

<?php 
error_reporting(E_ALL);
require 'db.php';

if(!isset($_SESSION)) {
session_start();
}



$result = $mysqli->query("SELECT * FROM users");
$query = "SELECT id, email FROM users";


if ($result = $mysqli->query($query)) {
/* Fetch associative array */ 
while ($row = $result->fetch_assoc()) {
    $user_id_from_users_table = $row['id'];
    $user_email_from_users_table = $row['email'];
}
$result->free();
}


$_SESSION['id'] = $_POST['user_id'];
$_SESSION['email'] = $_POST['user_email'];
$_SESSION['listing_title'] = $_POST['listing_title'];
$_SESSION['listing_description'] = $_POST['listing_description'];
$_SESSION['listing_country'] = $_POST['listing_country'];
$_SESSION['listing_city'] = $_POST['listing_city'];
$_SESSION['listing_image'] = $_POST['listing_image'];


$listing_title = $mysqli->escape_string($_POST['listing_title']);
$listing_description = $mysqli>escape_string($_POST['listing_description']);
$listing_country = $mysqli->escape_string($_POST['listing_country']);
$listing_city = $mysqli->escape_string($_POST['listing_city']);
$listing_image = $mysqli->escape_string($_POST['listing_image']);



$sql = "INSERT INTO listing (user_id, user_email, listing_title, 
listing_description, listing_country, listing_city, listing_image)".
"VALUES('$user_id_from_users_table','$user_email_from_users_table',
'$listing_title', '$listing_description', '$listing_country', 
'$listing_city', '$listing_image')";




    if($mysqli->query($sql)) {
    $_SESSION['message'] = "Thank you for creating a listing " 
    .$_SESSION['firstname'];
    //header("location: home.php"); 
}



?>

And this is the code from the page with the form ('new-listing.php'):

<?php 

require 'db.php';
session_start();


if(!$_SESSION['logged_in']) {
header("location: home.php");
}

require 'head.php';


?>

<?php 

if($_SERVER['REQUREST_METHOD'] = 'POST') {
if(isset($_POST['post_listing'])) {
    require 'new-listing-functions.php';
}
}


?>



<body id="new-listing">



<div class="new-listing-container">
    <form action="#" method="post">
        <input type="hidden" name="user_id">
        <input type="hidden" name="user_email">
        <input type="text" name="listing_title" placeholder="Listing Title">
<br>
        <textarea name="listing_description" id="" cols="30" rows="20" placeholder="Listing Description"></textarea>
        <input type="text" name="listing_country" placeholder="Country"><br>
        <input type="text" name="listing_city" placeholder="City"><br>
        <input type="file" name="listing_image""><br>
        <button name="post_listing">Proceed</button>
    </form>
</div><!-- new-listing-container -->

</body>
</html>

Thanks in advance for every help!

Chris J Chris J · Accepted Answer · 2017-10-05T22:43:14

Firstly, you should think about using prepared statements (look up mysqli::prepare or pdo) instead of trying to sanitise and escape input.

That aside, in your insert you are using $user_id_from_users_table and $user_email_from_users_table to populate your listing details.

Based on your code, you should be using $_SESSION['id'] and $_SESSION['email'] instead, although these need sanitising or you need to switch to prepared statements before you put raw post data in an INSERT

This is because the $_user_*_from_users_table values look like they are literally the last obtained value from your users table:

$result = $mysqli->query("SELECT * FROM users");
$query = "SELECT id, email FROM users";


if ($result = $mysqli->query($query)) {
/* Fetch associative array */ 
while ($row = $result->fetch_assoc()) {
    $user_id_from_users_table = $row['id'];
    $user_email_from_users_table = $row['email'];
}
$result->free();
}

That being said, I'm a bit confused as to what this is supposed to be achieving, or why you expect this to be any sort of session specific data?

Inserting data from one table to another table almost working

2 Answers