Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Query from 2 database tables PHP

    I have two tables one is called classifieds and one is called users.

    I am allowing users to post classified ads and would like to give the specific user a way to update or delete there post.

    For example of what I want is this. I create a page called update that I want the user to have control over there post.

    My problem is I cannot show only that users post in the update page without showing everyone's classified adds.

    Here is what the tables look like:

    Classifieds Table:
    Query from 2 database tables PHP-classifieds.png

    Users table:
    Query from 2 database tables PHP-users.png

    Here is my current query that doesn't work and I could use help fixing:
    PHP Code:
    <?php 
    $id 
    = (int)$_GET['id'];
      
    $classifieds DB::getInstance()->query("SELECT * FROM knollsclassifieds JOIN users ON knollsclassifieds.id = users.id WHERE id=$id");


    foreach(
    $classifieds->results() as $c){ ?>

    <div data-id="id-<?php echo escape ($c->id); ?>">
    <article class="postwhite mb-25">
    <div class="notices-title"><?php echo escape($c->title); ?></div>
    <div class="newsdate" style="margin: 10px 0 !important;"><?php echo escape (date("M. d, Y"strtotime ($c->dates))); ?> - Posted by: <?php echo escape($c->username); ?></div>
    <div class="articletext"><style>.articletext img{width:100%; height:auto;}</style><?php echo ($c->description);?></div>
    </article>
    </div>



        <?php
    }
    ?>
    Any help would be appreciated.

  • #2
    Regular Coder
    Join Date
    Sep 2011
    Posts
    428
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Okay first off, delete the column username from the classifieds table, it's not how it should be done and the data isn't static enough.

    What I mean by that is by the looks of it, the `username` column holds the user's first and last name, which they can change at any time (I'm assuming so at least) which would mean you have the possibility of people having the same name messing with each other's stuff, and if a user were to change their name you'd either lose all that information or have to update the classifieds to their new name, and wouldn't know if it's really theirs or someone else's with the same name.

    What you should do is create a column, (`id_user`, `userid`, `uid`, or something similar, whichever you prefer) to hold the id of the user's account. Whenever dealing with connected data you MUST have a KEY to go by, the KEY in this case is the user ID. The key should be primary/unique so that there aren't any duplicates. From that, add there "where" clause to your select queries when needing to get the user's specific classifieds.
    For example:
    "SELECT * FROM `classifieds` WHERE `id_user`=1";
    This will get all the classifieds and data that belongs to the user with the ID of 1


    Technically, you could use the account username as the "link" between the user and classifieds, however changing usernames means updating data, so you should just stick with the ID.

  • Users who have thanked Dubz for this post:

    greenI (04-29-2014)

  • #3
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    What I mean by that is by the looks of it, the `username` column holds the user's first and last name, which they can change at any time
    Yes you are correct by saying that. When the user posts an ad I have the form setup like this pulling from the user table:
    PHP Code:
    <?php    
    $user 
    = new User();

    if(!
    $user->isLoggedIn()) {
        
    Redirect::to('index.php');
    }

    if(
    Input::exists()) {
        if(
    Token::check(Input::get('token'))) {
                    
    $user->query(array(
                        
    'first_name' => Input::get('first_name'),
                        
    'last_name' => Input::get('last_name')                    
                    ));
            }
        
    }

    ?>
         

    <form enctype="multipart/form-data" action="upload-listings.php" method="POST">


    Posted By: <input type="text" name="username" value="<?php echo escape($user->data()->first_name); echo " ";  echo escape($user->data()->last_name); ?>" readonly>
    As you see I have the form set to readonly. I want the public to know who is posting the ad. But you are correct again, the user can change there name and I just tried it out and it does't change in the classifieds ad. So how can I change it so the public can still see the name of the person posting the ad if I remove username from the classifieds table?

  • #4
    Regular Coder
    Join Date
    Sep 2011
    Posts
    428
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Like I said earlier, create a column to store the user's ID. It's static (meaning it's not going to change unless you change it) and will keep everything linked together. Then grab the user's info based on that ID.

  • Users who have thanked Dubz for this post:

    greenI (04-29-2014)

  • #5
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I got it half way working by using this sql statement and by creating a user_id column in my classifieds table.

    [p]$classifieds = DB::getInstance()->query("SELECT users.first_name, users.last_name,
    knollsclassifieds.title, knollsclassifieds.dates, knollsclassifieds.description
    FROM users LEFT JOIN knollsclassifieds ON users.id = knollsclassifieds.user_id
    WHERE knollsclassifieds.user_id=user_id");[/p]


    Now I'm having problems adding to the classifieds section and getting the users id to transfer into the user_id of the classifieds table.

    Here is what I'm trying:

    [p]<form enctype="multipart/form-data" action="upload-listings.php" method="POST">

    Listing Title*: <input type="text" name="title" placeholder="Enter title..." required>
    <br><br>

    Listing Details*:<br />
    <textarea id="tiny_mce" name="description" rows="8" placeholder="Enter details..."></textarea>
    <br><br>

    * = (required)
    <br><br>





    <input type="submit" class="btn green white-tx" value="Upload">

    <?php
    $id = (int)$_GET['id']; <--- THIS IS GIVING ME A Notice: Undefined index: id
    $user = DB::getInstance()->query("SELECT `id` FROM `users` WHERE ID = $id");
    foreach($user->results() as $u){
    ?>

    <input type="hidden" name="user_id" value="<?php echo escape ($u->id); ?>">
    <?php } ?>
    </form>
    [/p]

    and the action script "upload-listings.php" looks like this:

    [p]<?php
    include($_SERVER['DOCUMENT_ROOT'] . "/core/init.php");

    // new data
    $title = $_POST['title'];
    $description = $_POST['description'];
    $user_id = $_POST['user_id'];


    // query

    $addnotice = DB::getInstance()->insert('`knollsclassifieds`', array(
    'title' => $title,
    'description' => $description,
    'user_id' => $user_id
    ));


    echo "The classified listing was successfully uploaded and added to Knolls Classifieds!<br />
    <a href='/classifieds/add-listing.php'>Post another listing!</a><br />";

    ?>[/p]

    That notice is preventing the script to add the user id to the classified user_id column.

  • #6
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Shoot sorry about the php formating in the thread. I used lower case [p] instead of uppercase. I don't see an edit button...?

  • #7
    Regular Coder
    Join Date
    Sep 2011
    Posts
    428
    Thanks
    18
    Thanked 26 Times in 26 Posts
    You need to set the user's ID when they login (ex. $_SESSION['user_id'] = $user['id'];), don't use $_GET for that, then anyone can go and change the number to do what they please. You HAVE to VERIFY its that user, don't rely on data that's posted, use sessions/cookies to store authentication and check it for its validity.

    Also, the tag is [ PHP ][ /PHP ] (no spaces), not [P][/P]. The case doesn't matter (unless you're OCD).

  • Users who have thanked Dubz for this post:

    greenI (04-29-2014)

  • #8
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    When I use
    PHP Code:
    $_SESSION['user_id'] = $user['id']; 
    I get the following error
    Fatal error: Cannot use object of type User as array

  • #9
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    If I use this where it says 'id' =>Input...

    PHP Code:
    try {
                    
    $user->update(array(
                        
    'first_name' => Input::get('first_name'),
                        
    'last_name' => Input::get('last_name'),
                        
    'email' => Input::get('email'),
                        
    'unit' => Input::get('unit'),
                        
    'id' => Input::get('id'),
                                        
                    )); 
    And echo it out like this:
    PHP Code:
    <?php echo escape($user->data()->id); ?>
    I get the correct user id.

    How can I add that echo to the end of my statement:

    PHP Code:
    $userid "$user->data()->id);";
    $classifieds DB::getInstance()->query("SELECT users.id, users.first_name, users.last_name,
    knollsclassifieds.title, knollsclassifieds.dates, knollsclassifieds.description
    FROM users LEFT JOIN knollsclassifieds ON users.id = knollsclassifieds.user_id
    WHERE knollsclassifieds.user_id=$userid"
    ); 
    I tried the $userid variable and it was unsuccessful.

  • #10
    Regular Coder
    Join Date
    Sep 2011
    Posts
    428
    Thanks
    18
    Thanked 26 Times in 26 Posts
    You have to set the variables and use the proper ones in your situations. The code I gave was just an example, you have to make your own for your case. All you are asking is for someone to tell you what to put where rather than learn how it works and do it yourself. If you want to learn how to do it, then research it. If you just want something done, consider hiring a programmer who knows what they're doing to do it for you. Asking for the answer to everything won't help you understand it.

    The forums are for help, not free solutions to everything. Know when to ask for help and when you should research it.

  • Users who have thanked Dubz for this post:

    greenI (04-29-2014)

  • #11
    New to the CF scene
    Join Date
    Apr 2014
    Posts
    7
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Point taken. Thanks for your help! I'll try and get it figured out on my own. Just don't have time on my side, therefore the 20 questions.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •