7 {{ upvoteCount | shortNum }}
7 {{ upvoteCount | shortNum }}

Query from 2 database tables PHP

by cwd
6 replies
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:


Users table:


Here is my current query that doesn't work and I could use help fixing.
PHP Code:
PHP Code:
<?php 
$id = (int)$_GET['id'];
  $classifieds = DB::getInstance()->query("SELECT  `id`, `dates`, `username`, `title`, `description` FROM classifieds JOIN users ON classifieds.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.
#database #php #query #tables
  • Profile picture of the author ClicProject
    What you want is a field called "user_id" in your classifieds table, rather than "username"

    Then you would pull all of the ads of a specific user with the following query:

    SELECT `id`, `dates`, `title`, `description` FROM classifieds where user_id = $id


    When you are displaying the classifieds, and want to show the username next to it, do a left join:

    Select a.id, a.dates, a.title,a.description, a.user_id,b.username from classifieds as a LEFT JOIN users as b on b.id = a.user_id.


    Bear in mind you are going to want to implement some security here, such as making your users log in before they can edit their adverts.
    {{ DiscussionBoard.errors[9144811].message }}
  • Profile picture of the author cwd
    Thank you for the reply that helps out a ton. BTW I do have the users log in before they can post any adverts. That brings up another issues.

    I realized. the username column pulls the first and last name from the classifieds table, but I have the update section setup to where the user can also change their first name and last name. If the user does this, it doesn't update the first and last name in the advert.

    Here is the add-classifieds.php form. Notice username is readonly:
    PHP Code:
    <?php    
         = new User();

    if(!()) {
            Redirect::to('index.php');
    }

    if(    Input::exists()) {
        if(    Token::check(Input::get('token'))) {
                    (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(()->first_name); echo " ";  echo escape(()->last_name); ?>" readonly>
    <br><br>



    Listing Title*: <input type="text" name="title" placeholder="Enter title..." required>
    <br><br> etc.....
    If I drop the username in my classifieds table and How can I get the users first and last name to display on the advert by using the users table?
    {{ DiscussionBoard.errors[9144898].message }}
    • Profile picture of the author Artur Golisz
      I made some examle for you (if I correct understand you):

      SQL Fiddle

      So anytime when User will change his name or surname new data will available.
      Signature
      Need help with programming (WordPress, PHP, jQuery and more) ? Just send me PM.
      {{ DiscussionBoard.errors[9144998].message }}
      • Profile picture of the author cwd
        @Artur Golisz

        Thank you for helping. I still don't 100% understand. Should I not use LEFT JOIN?

        I guess I'm needing 2 different queries.
        One that displays all the users advert on the classifieds page and grabs there first and last name from the users table.

        And a 2nd one that displays only a single users adverts. This would be displayed only to that person on there profile / update page.

        Is there anyway you could give me and example with my code?

        So 1 would look like this:

        Test post 1
        Apr. 23, 2014 - Posted by: Some Girl
        This is test copy. This is test copy. This is test copy.
        This is test copy. This is test copy. This is test copy.

        Test post 2
        Apr. 24, 2014 - Posted by: Some Guy
        This is test copy. This is test copy. This is test copy.
        This is test copy. This is test copy. This is test copy.

        Test post 3
        Apr. 25, 2014 - Posted by: Some Girl
        This is test copy. This is test copy. This is test copy.
        This is test copy. This is test copy. This is test copy.

        Test post 4
        Apr. 25, 2014 - Posted by: Some Guy
        This is test copy. This is test copy. This is test copy.
        This is test copy. This is test copy. This is test copy.


        Then 2 would look like this:

        Test post 1
        Apr. 23, 2014 - Posted by: Some Girl
        This is test copy. This is test copy. This is test copy.
        This is test copy. This is test copy. This is test copy.

        Test post 3
        Apr. 25, 2014 - Posted by: Some Girl
        This is test copy. This is test copy. This is test copy.
        This is test copy. This is test copy. This is test copy.
        {{ DiscussionBoard.errors[9145056].message }}
        • Profile picture of the author Artur Golisz
          Please see this exaple:
          SQL Fiddle

          First query return all adverts, second query return all adverts which belongs to one user.
          Signature
          Need help with programming (WordPress, PHP, jQuery and more) ? Just send me PM.
          {{ DiscussionBoard.errors[9145293].message }}
  • Profile picture of the author cwd
    Where am I going wrong here?

    PHP Code:
     = DB::getInstance()->query("Select `classifieds`.`id`,`classifieds`.`user_id`,`classifieds`.`dates`,`classifieds`.`title`,`classifieds`.`description`,`users`.`first_name`,`users`.`last_name` FROM `classifieds` LEFT JOIN `users` ON `classifieds`.`id`=`users`.`id`");


    foreach(() as ){ ?>

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



        <?php
    }
    ?>
    For some reason the text editor here isn't displaying my code right. This is what it should look like.

    $classifieds = DB::getInstance()->query("Select `classifieds`.`id`,`classifieds`.`user_id`,`classi fieds`.`dates`,`classifieds`.`title`,`classifieds` .`description`,`users`.`first_name`,`users`.`last_ name` FROM `classifieds` LEFT JOIN `users` ON `classifieds`.`id`=`users`.`id`");


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

    <div data-id="id-<?php echo escape ($c->user_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->first_name) . " "; echo escape($c->last_name);?></div>
    <div class="articletext"><style>.articletext img{width:100%; height:auto;}</style><?php echo ($c->description);?></div>
    </article>
    </div>



    <?php
    }
    ?>
    {{ DiscussionBoard.errors[9144980].message }}

Trending Topics