Help with displaying data from MySQL database

[B_CooperA]

Hello everyone, I just registered here and have few major issues with my PHP, so I'm gonna need some help:)

Where should I start..? I have three tables: events, users, and user_events.. Table structure foreach of this tables are following:

 

 

Users:

 

• user_id PRIMARY KEY, AUTO_INCREMENT,
• name VARCHAR,
• email VARCHAR,
• password VARCHAR,
• dateofbirth, DATE

 

Events:

 

• event_id PRIMARY KEY, AUTO_INCREMENT,
• event_name VARCHAR,
• event_date DATETIME,
• creator FOREIGN KEY, REFERENCES users(user_id)

 

User_events:

 

• eventID FOREIGN KEY, REFERENCES events(event_id)
• userID FOREIGN KEY, REFERENCES users(user_id)

Background and Introduction

 

Basic idea for my web app is that registered users can add, edit, delete events and also attend in them (also can remove the attending).

 

 

Problem - How to echo the attended users into a javascript Modal?

 

I'm trying to simulate Facebook style of displaying all the user likes from particular status update. After you click on who's liking underneath the status update, and modal (Javascript) will open and display all the "likers" for that status update.

 

Underneath, I have posted my function to retrieve and fetch profile picture and name from the users table..

public function getAttendentsNames() {

if(isset($_GET['show_attendents'])) {

$query = "SELECT name, profilepic FROM users
INNER JOIN user_events ON user_events.userID = users.user_id
WHERE eventID = :event_id";

$stmt = $this->connect->prepare($query);
$stmt->execute(array(

':event_id' => $this->row['event_id']

));

$this->rows = $stmt->rowCount();
$i = 1;

while($i <= $this->rows) {

$this->row = $stmt->fetch(PDO::FETCH_ASSOC);

echo "<img src='images/thumbnails/".$this->row['profilepic']."' class='profilesmall'/>";
echo "<p>".$this->row['name']."</p>"; 
}

$i++;
}

}

And here's the form that will trigger the function above (or atleast it should):

<form action='".$_SERVER['PHP_SELF']."' method='get'><input type='hidden' value='".$this->row['event_id']."' />
<input type='submit' name='show_attendents' id='show_attendents' value='Show Attendents' />
</form>

I'm using this function on another PHP-file, so I'm instantiating the object in there like so:

<div class="show_attendents">

<?php
/* Instantiating the Event object */

$event = new Event($connect);

$event->getAttendentsNames();
?>
</div>

Problem is that it will open the modal, but the modal itself is empty.. There's no data in it, so something have must go wrong with this and I can't figure out what. So please guys, help me out here!

 

Thanks in advance

[jcbones]

Change your form:

 

<form action='".$_SERVER['PHP_SELF']."' method='get'><input type='hidden' name='event_id' value='".$this->row['event_id']."' />
<input type='submit' name='show_attendents' id='show_attendents' value='Show Attendents' />
</form>

 

Change your method:

 

if(isset($_GET['show_attendents'])) {
 $event_id = (int)$_GET['event_id'];

And

$stmt->execute(array(

':event_id' => $event_id

));

 

See if that gives you anything (errors or otherwise).  Please post back!

[B_CooperA]

Okay did what you posted above.. There might be something else wrong too. I'm using the form inside of my another function, which will show all the events.. Here's the code for that (nevermind about the language i'm echoing, it's finnish):

 

public function showAllEvents() {


$query =  "SELECT * FROM events INNER JOIN users ON users.user_id = events.creatoruserid
WHERE events.creatoruserid != :user_id AND CURDATE() <= events.event_date
ORDER BY events.event_creation_date DESC"; 


$stmt = $this->connect->prepare($query);


$stmt->execute(array(


':user_id' => $_SESSION['user_id']


));


$this->rows = $stmt->rowCount();


if($this->rows >= 1) {


$i = 1;
while($i <= $this->rows) {


$this->row = $stmt->fetch(PDO::FETCH_ASSOC);


$today = strtotime(date('Y-m-d')); 
$event_date = strtotime($this->row['event_date']);
$this->days = (abs($event_date - $today) / 86400);


if ($this->days == 0) {


$this->days = " Tänään";


} else if ($this->days == 1) {


$this->days = $this->days. " päivä";


} else {


$this->days = $this->days. " päivää";


}


echo "<div class='black-show-name'><a href='profile.php?id=".$this->row['user_id']."'>".$this->row['name']."</a>
<div class='tri_angle'></div></div>


<div class='user-event-header' >
<input type='hidden' value='".$this->row['event_id']."' />


<img src='images/thumbnails/".$this->row['profilepic']."' class='profilesmall'/>


<h2><ul class='event-heading'>
<li class='event_city' title='Paikkakunta'>".ucfirst($this->row['event_city']). '</li>
<li class="event_name" title="Tapahtuma">' .ucfirst($this->row['event_name']). '</li>
<li class="event-date" title="Päivämäärä">'. date("d.m.Y", strtotime($this->row['event_date'])). ' '. date("G:i", strtotime($this->row['event_time'])). '</li>
<li class="event-created" title="Alkuun">Milloin: '. $this->days. " </li>
<li class='event-attend' title='Näytä lisää'><button id='show-more' class='medium-button-new'>+</button></li>
</ul></h2>
</div>


<div class='event'><p>Osoite: " .ucfirst($this->row['event_address']). "</p>
<p>Tapahtuman päivämäärä -ja kellonaika: " .date('d.m.Y', strtotime($this->row['event_date']))." " .$this->row['event_time']. "
<p>Tapahtuman osoite - ja kaupunki: " .ucfirst($this->row['event_address']). ", ".ucfirst($this->row['event_city'])."</p>
<p>Tapahtuman kuvaus: " .ucfirst($this->row['event_description']). "</p>




<div class='right_area'>
<form action='".$_SERVER['PHP_SELF']."' method='get'>
<input type='hidden' name='event_id' value='".$this->row['event_id']."' />
<input type='submit' name='show_attendents' id='show_attendents' value='Show Attendents' />
</form>";




if($this->isAttending() == 1) {
  echo "<form action='".$_SERVER['PHP_SELF']."' method='post'>
<input type='hidden' name='event_id' value='".$this->row['event_id']."'/>
<input type='submit' name='remove_attend' value='Peru osallistuminen' class='medium-button-remind' />
</form>";


  } else {
  echo "<form action='".$_SERVER['PHP_SELF']."' method='post'>
<input type='hidden' name='event_id' value='".$this->row['event_id']."'/>
<input type='submit' name='attend_event' value='Osallistu' class='medium-button-attend' />
</form>";
}


echo "</div></div>";


$i++;
}




} else {
echo "<p>Ei näytettäviä tapahtumia</p>";


}


}

I'm instantiating this inside my events.php like so (There's also that getAttendentsNames - method inside of that profile-wrapper):

 

<div id="profile-wrapper">


<div class="show_attendents">
<?php $event->getAttendentsNames();?>
</div>


<h2>» Viimeisimmät tapahtumat, joihin et ole vielä osallistunut</h2>
<?php


$event->showAllEvents();
?>
</div>

Here's the problem now: The very first event, where is that "Show Attendents" - button, will open that modal (although it's still empty), but every other events is trying to redirect me to an address something like this:

http://www.nvmaboutthis.com/events.php?event_id=36&show_attendents=Show+Attendents

So it's getting that event_id value, yes, but also get the value from that submit button(?) and redirects me to a completely blank page although It should open that modal after I have pressed "Show Attendents" - button. Any ideas now?

Edited October 1, 2013 by B_CooperA

[jcbones]

You are most likely looking for AJAX.  Since the form is acting as it should with the page request.

[B_CooperA]

You are most likely looking for AJAX.  Since the form is acting as it should with the page request.

 

But how come it doesn't return anything? Just a blank page?

[B_CooperA]

Okay, I got it work now, without the modal part though, it's now fetching and echoing all the attendants from that particular eventID. However, there's something strange still going on, nothing major but very annoying. So, here's my function now:

 

public function getAttendentsNames() {


if(isset($_GET['show_attendents'])) {


$event_id = (int)$_GET['event_id'];


  $query = "SELECT name, profilepic FROM users INNER JOIN user_events
ON user_events.userID = users.user_id
WHERE user_events.eventID = :event_id";


$stmt = $this->connect->prepare($query);
$stmt->execute(array(
':event_id' => $event_id
));


$rows = $stmt->rowCount();


if($rows >= 1) {
$i = 1;


while($i <= $this->rows) {


$this->row = $stmt->fetch(PDO::FETCH_ASSOC);


echo "<ul> 
<li><img src='images/thumbnails/".$this->row['profilepic']."' class='profilesmall'/>".$this->row['name'] ." 
</li></ul>";


$i++;


}




} else {


echo "Ei osallistujia";
}


 }


}

And when it echos those attendants, It will always echo out at least three images, few of them are blank. So in source code, it looks like this:

<div>
	<ul> 
	<li><img src='images/thumbnails/default-profile-pic.PNG' class='profilesmall'/>Testaaja Tero 
	</li></ul><ul> 
	<li><img src='images/thumbnails/' class='profilesmall'/> 
	</li></ul><ul> 
	<li><img src='images/thumbnails/' class='profilesmall'/> 
	</li></ul>	
</div>

So for some unknown reason, it tries to display more images than it should be. I've tested this with only echoing the name and it works. Any suggestions?

Edited October 2, 2013 by B_CooperA