JOINING tables

[Haggen]

Hi, i need to display data from 3 different tables combined.

The project is a detailed member list, is a list from a review comunity, what i want is ti display member information (thats already done) but i need to display all reviews a member has done.

The data is divided in these 3 tables:

table: mnl_members
- username, email, reviews (total review count), last_review_date, date_registered.

table: mnl_reviews
- review_title, review_score, reviewer_name, date_added.

table: mnl_items
- item (name of the item reviewed), item_id, cat_id, sub_cat_id.

What i need to display is something like this:

Member: User
member since: Date registered
Email: [e-mail]
Total Reviews: 21

Reviews by this user:
item (item's name): review title - review_score <-- All this will be a link to the actual review.

So fay my code is:

<?php
//Conexión a la base de datos
$con = mysql_connect("localhost","user","pass") or die (mysql_error());
mysql_select_db("database",$con) or die (mysql_error());




$query = "SELECT mnl_members.email,

        mnl_members.reviews,

        mnl_members.last_review_date,

        mnl_members.date_registered,

        mnl_reviews.review_score,

        mnl_reviews.review_title

        FROM mnl_members

        LEFT JOIN mnl_reviews ON mnl_reviews.review_id = mnl_members.last_review_id

        WHERE mnl_members.username='$username'";

$result = mysql_query($query);

while($row = mysql_fetch_array($result)) { 

$username = $_GET['username'];
$date_registered = $row['date_registered']; 
$reviews = $row['reviews'];
$review_score = $row['review_score'];
$review_title = $row['review_title'];
$email = $row['email'];

} 


echo " Hello, <b>$username</b><br> \n";
echo " member since: <b>$date_registered</b><bR> \n";
echo " email: <b>$email</b><bR> \n";
echo " Last Review Title: <b>$review_title - $review_score</b><br> \n";
echo " Total Reviews: <b>$reviews</b><br> \n";
 ?> 

You can see it live here: http://www.animerate.com/perfil.php?username=Anbu33

I only can display the LAST REVIEW by User 🙁

Any ideas? please help

[chyan]

Try This:

<?php 
//Conexión a la base de datos 
$con = mysql_connect("localhost","user","pass") or die (mysql_error()); 
mysql_select_db("database",$con) or die (mysql_error()); 




$query = "SELECT mnl_members.email, 

        mnl_members.reviews, 

        mnl_members.last_review_date, 

        mnl_members.date_registered, 

        mnl_reviews.review_score, 

        mnl_reviews.review_title 

        FROM mnl_members 

        LEFT JOIN mnl_reviews ON mnl_reviews.review_id = mnl_members.last_review_id 

        WHERE mnl_members.username='$username'"; 

$result = mysql_query($query); 

while($row = mysql_fetch_array($result)) {  

$user = $_GET['username']; 
$date_registered = $row['date_registered'];  

$reviews = $row['reviews']; 
$email = $row['email']; 


echo " Hello, <b>$username</b><br> \n"; 
echo " member since: <b>$date_registered</b><bR> \n"; 
echo " email: <b>$email</b><bR> \n"; 


$query2="select mnl_reviews.review_score, 

     mnl_reviews.review_title from mnl_member where reviewer_name='$user'

$result2 = mysql_query($query2); 

while($row2 = mysql_fetch_array($result2)) {  

$reviews_title = $row2['review_title']; 
$reviews_score = $row2['review_score']; 



echo " review by this user";
echo " <b>$review_title - $review_score</b><br> \n"; 

}  

echo "Total Reviews: <b>$reviews</b><br> \n"; 

}

?>  

[Haggen]

Ok, it seems like i could combine tables and work with them...

Now the problem is the display of this data, i can only display 1 row (of the reviews), obvius i need to display ALL reviews added by a single user, so i need to print all rows.

Any idea how?

[Haggen]

Ok, i dont how i did it :S but i could make to display all reviews the way i wanted. BUT there some blank data. I believe i did it the worng way.

Please check this page and you see what i mean (at the very beggining).

Here's my code:

<?php

require ( "config.php" );
require ( "common.php" );
require ( "includes/mysql.php" );
require ( "includes/interface.php" );
require ( "includes/inc_reviews.php" );
require ( "includes/functions.php" );
require ( "global_config.php" );


$query = "SELECT mnl_members.email,
            mnl_members.reviews,
            mnl_members.last_review_date,
            mnl_members.date_registered,
            mnl_reviews.review_score,
            mnl_reviews.review_title,
            mnl_reviews.reviewer_name,
            mnl_reviews.date_added,
            mnl_reviews.review_id,			
            mnl_items.cat_id ,
            mnl_items.sub_cat_id ,
            mnl_items.item ,
            mnl_items.item_id ,
            mnl_items.image_source 

        FROM mnl_members,mnl_reviews,mnl_items 
        WHERE mnl_reviews.reviewer_name = mnl_members.username and mnl_items.item_id = mnl_reviews.item_id  and mnl_members.username='$username' ORDER BY review_id DESC";


$result = mysql_query($query);  

while($row = mysql_fetch_array($result)){ 


echo " <br><br> <img src=\"$image_source\" border=1 width=70 height=70 align=left><b>$item</b> <i> $date_added</i><br>\n";
echo " Review title: <b> $review_title</> - <b>MY Score: </b>$review_score<br> \n";
echo " <a href=\"review.php?review_id=" . $review_id . "&item_id=" . $item_id . "&cat_id=" . $cat_id . "&sub_cat_id=" . $sub_cat_id . "&offset=" . $offset ."\"><b>Complete Review >></b></a>\n";

// Review content
$offset = $_GET['offset'];
$review_score = $row['review_score'];
$review_title = $row['review_title'];
$date_added = $row['date_added'];
$last_review_date = $row['last_review_date'];
$image_source = $row['image_source'];
$item = $row['item'];
$item_id = $row['item_id'];
$cat_id = $row['cat_id'];
$sub_cat_id = $row['sub_cat_id'];
$review_id = $row['review_id'];

}

?>

Where's the error?

[tsinka]

Hi,

move the complete "//Review Content" block where you assign the values to the variables to the very beginning of the while loop.

You have to assign the values to the variables before you echo the HTML code.

Thomas

[Haggen]

Cool man! tha fixed the problem!!! 😃

Now a little problema with offset i believe, please go to http://www.animerate.com/perfil.php?username=Ivto_2099 and click on the first Complete Review >> link, as you can see, the first review was written November 2nd, 2004 (its in spanish tho), and when you click the url and go to the real review, tha date changes to TODAY's date!

Ive been reading and i believe its an OFFSET issue, but i can figure out how to fix it.

Any idea?

Here's my code again:

<?php

require ( "config.php" );
require ( "common.php" );
require ( "includes/mysql.php" );
require ( "includes/interface.php" );
require ( "includes/inc_reviews.php" );
require ( "includes/functions.php" );
require ( "global_config.php" );


$query = "SELECT mnl_members.email,
mnl_members.reviews,
mnl_members.last_review_date,
mnl_members.date_registered,

mnl_reviews.review_score,
mnl_reviews.review_title,
mnl_reviews.reviewer_name,
mnl_reviews.date_added,
mnl_reviews.review_id,			
mnl_items.cat_id ,
mnl_items.sub_cat_id ,
mnl_items.item ,
mnl_items.item_id ,
mnl_items.image_source 

FROM mnl_members,mnl_reviews,mnl_items WHERE
mnl_reviews.reviewer_name = mnl_members.username and
mnl_items.item_id = mnl_reviews.item_id  and
mnl_members.username='$username' ORDER BY review_id DESC";


$result = mysql_query($query);  

while($row = mysql_fetch_array($result)){ 
// Review content
$offset = 0;
$review_score = $row['review_score'];
$review_title = $row['review_title'];
$date_added = $row['date_added'];
$last_review_date = $row['last_review_date'];
$image_source = $row['image_source'];
$item = $row['item'];
$item_id = $row['item_id'];
$cat_id = $row['cat_id'];
$sub_cat_id = $row['sub_cat_id'];
$review_id = $row['review_id'];



echo " <br><br> <img src=\"$image_source\" border=1 width=70 height=70 align=left><b>$item</b> <i> $date_added</i><br>\n";
echo " Review title: <b> $review_title</> - <b>MY Score: </b>$review_score<br> \n";
echo " <a href=\"review.php?review_id=" . $review_id . "&item_id=" . $item_id . "&cat_id=" . $cat_id . "&sub_cat_id=" . $sub_cat_id . "&offset=" . $offset ."\"><b>Complete Review >></b></a>\n";


}

?>

[tsinka]

What does $offset do ? you define $offest inside the loop but you don't do more with it (e.g. increase or something like that).

EDIT: Post the part of review.php that prints the review data.

Thomas

[Haggen]

Thanks for the help.. im newbie 🙁

Can you more explicit, and write an example? please?

EDIT: I think i got you, i checked my review.php, and found thise code...

global $db;

$review_id = $_GET['review_id'];

$item_id = $_GET['item_id'];

$cat_id = $_GET['cat_id'];

$sub_cat_id = $_GET['sub_cat_id'];

$offset = $_GET['offset'];

Way below is this:

if ( empty ( $offset ) ) 

{ 

$offset = 0; 

} 

I changed:

$offset = $_GET['offset'];

to:

$offset = $_POST['offset'];

And had the same problem. Any other idea?

[tsinka]

Ok,

hard to guess anything ... please post the review script or at least the part that does something with the $offset variable (e.g. use it in a SQL query or something like that) and the part that prints the database info.

$POST : post variables
$GET : get variables

So don't use $_POST unless the data comes from a form with method="post"

Thomas

[Haggen]

OKie. .. here's the whole code for my review.php file.

<?php

require ( "config.php" );

require ( "common.php" );

require ( "includes/mysql.php" );

require ( "includes/interface.php" );

require ( "includes/inc_reviews.php" );

require ( "includes/functions.php" );

require ( "global_config.php");



session_start();



global $db;

$review_id = $_GET['review_id'];

$item_id = $_GET['item_id'];

$cat_id = $_GET['cat_id'];

$sub_cat_id = $_GET['sub_cat_id'];

$offset = $_GET['offset'];





$limit = $CONFIG_VAR['num_records_display'];

$show_item_url = $CONFIG_VAR['item_url'];



// set the configurable field variables

$item_config_field_1 = $CONFIG_VAR['item_config_field_1'];

$item_config_field_2 = $CONFIG_VAR['item_config_field_2'];

$item_config_field_3 = $CONFIG_VAR['item_config_field_3'];

$item_config_field_4 = $CONFIG_VAR['item_config_field_4'];

$item_config_field_5 = $CONFIG_VAR['item_config_field_5'];

$item_config_field_6 = $CONFIG_VAR['item_config_field_6'];

$item_config_field_7 = $CONFIG_VAR['item_config_field_7'];

$item_config_field_8 = $CONFIG_VAR['item_config_field_8'];





// Find the category and sub category names

$query = "SELECT mnl_categories.category,

                mnl_sub_categories.sub_category

                FROM mnl_categories

                INNER JOIN mnl_sub_categories ON mnl_categories.cat_id = mnl_sub_categories.cat_id

                WHERE mnl_sub_categories.cat_id='$cat_id' AND mnl_sub_categories.sub_cat_id='$sub_cat_id'";

$result = $db->get_row ( $query );



$category = $result->category;

$sub_category = $result->sub_category;



$page = "review";



include_template ( $CONFIG_VAR['page_header_template'] );



$Prev = "Anterior"; 

$Next = "Siguiente"; 



// This creates the start of the main body table

echo "<!-- Start of main body table -->\n\n";



echo "<table width=\"" . $CONFIG_VAR['body_width_value'] . "\" align=\"center\" cellspacing=\"0\" border=\"0\" cellpadding=\"0\">\n";

echo "<tr>\n";





// This creates the lefthand menu column

include_template ( $CONFIG_VAR['left_column_template'] );



// This creates the middle table

echo "<!-- Start of middle column -->\n\n";

echo "<td valign=\"top\">\n";





if ( empty ( $offset ) ) 

{ 

$offset = 0; 

} 



//Find the item name from the item_id

$query = "SELECT item,

        description,

        item_url,

        url_click_through,

        cost,

        config_field_1,

        config_field_2,

        config_field_3,

        config_field_4,

        config_field_5,

        config_field_6,

        config_field_7,

        config_field_8,

        image_source,

        image_alt_text

        FROM mnl_items

        WHERE item_id='$item_id'";



$item_result = $db->get_row( $query );

$item = ucwords ( $item_result->item );



if ( $db->num_rows == 0 )

{

// This creates the breadcrumb trail

breadcrumbs( "error" );



print_message ( "Cannot process request", "This " . ucwords ( $CONFIG_VAR['item_type'] ) . " could not be found" );

die;    

}



$query = "SELECT review_title, 

            review_text,

            score_1,

            score_2,

            score_3,

            score_4,

            score_5,

            score_6,

            score_7,

            score_8,

            review_score,

            recommend,

            reviewer_name,

            date_added,

            display_name,

            hits

            FROM mnl_reviews

            WHERE review_id='$review_id'";



$review_result = $db->get_row ( $query );



if ( $db->num_rows > 0 )

{



// This creates the breadcrumb trail

breadcrumbs( $page );



// Increment the review hits by one

$db->query( "UPDATE mnl_reviews SET hits=hits+1 WHERE review_id='$review_id'" );



// echo out the item details

item_details ( $item_id, $item, $item_result->description, $item_result->item_url, $item_result->url_click_through, $item_result->cost, $item_result->config_field_1, $item_result->config_field_2, $item_result->config_field_3, $item_result->config_field_4, $item_result->config_field_5, $item_result->config_field_6, $item_result->config_field_7, $item_result->config_field_8, $show_item_url, $item_config_field_1, $item_config_field_2, $item_config_field_3, $item_config_field_4, $item_config_field_5, $item_config_field_6, $item_config_field_7, $item_config_field_8, $item_result->image_source, $item_result->image_alt_text );



// echo out the full review

print_review( "full", $cat_id, $item_id, $review_result->review_title, $review_result->review_text, $review_result->score_1, $review_result->score_2, $review_result->score_3, $review_result->score_4, $review_result->score_5, $review_result->score_6, $review_result->score_7, $review_result->score_8, $review_result->review_score, $review_result->recommend, $review_result->reviewer_name, $review_result->date_added, $review_result->display_name, $review_result->hits + 1 );

}

else

{

breadcrumbs( "error" );

print_message ( "Cannot process request", "This review could not be found" );

die;    

}





//This creates the main menu along the bottom of the main column

bottom_menu( "yes" );



echo "</td>\n";

echo "<!-- End of middle column -->\n\n";



// This creates the right hand menu column

include_template ( $CONFIG_VAR['right_column_template'] );



// This ends the main body table

echo "</tr>\n";

echo "</table>\n";



//This creates the page footer

include_template ( $CONFIG_VAR['page_footer_template'] );

?>

Hope it helps.

[tsinka]

Post the print_review function. You can paste the code in a text file and attach that file to your post.

Thomas

[Haggen]

Hmm, heres the code for print_review

  // echo out the full review

print_review( "full", $cat_id, $item_id, $review_result->review_title, $review_result->review_text, $review_result->score_1, $review_result->score_2, $review_result->score_3, $review_result->score_4, $review_result->score_5, $review_result->score_6, $review_result->score_7, $review_result->score_8, $review_result->review_score, $review_result->recommend, $review_result->reviewer_name, $review_result->date_added, $review_result->display_name, $review_result->hits + 1 );

}

How can i "POST" it? 🙁

[tsinka]

I need the code that declares that function (maybe in one of the included files).

Something like

function print_preview(....) {
....
....
}

Thomas

[Haggen]

OK, i did a gloabal search on the script to find "print_review"

I only find these:

On "add_review.php" (where users submit their reviews):

function print_review( $mode, $cat_id, $item_id, $review_title, $review_text, $score_1, $score_2, $score_3, $score_4, $score_5, $score_6, $score_7, $score_8, $review_score, $recommend, $reviewer_name, $date_added, $display_name, $hits )


{
global $review_id, $link, $_COOKIE, $_SESSION, $CONFIG_VAR;
global $item_id, $cat_id, $sub_cat_id, $offset;

On "review.php" (Where the user's review is displayed):

 // echo out the full review

print_review( "full", $cat_id, $item_id, $review_result->review_title, $review_result->review_text, $review_result->score_1, $review_result->score_2, $review_result->score_3, $review_result->score_4, $review_result->score_5, $review_result->score_6, $review_result->score_7, $review_result->score_8, $review_result->review_score, $review_result->recommend, $review_result->reviewer_name, $review_result->date_added, $review_result->display_name, $review_result->hits + 1 );

}

On "includes/inc_reviews.php" (i think this is the file that does the trick):

function print_review( $mode, $cat_id, $item_id, $review_title, $review_text, $score_1, $score_2, $score_3, $score_4, $score_5, $score_6, $score_7, $score_8, $review_score, $recommend, $reviewer_name, $date_added, $display_name, $hits )


{
global $review_id, $link, $_COOKIE, $_SESSION, $CONFIG_VAR;
global $item_id, $cat_id, $sub_cat_id, $offset;

I hope that helps

[tsinka]

That's a good start 🙂

I need at least the code within that function that prints the HTML code, especially the date info.

Thomas

[Haggen]

Hmm, im not sure about what your asking.. but heres the code for the /includes/inc_reviews.php

function print_review( $mode, $cat_id, $item_id, $review_title, $review_text, $score_1, $score_2, $score_3, $score_4, $score_5, $score_6, $score_7, $score_8, $review_score, $recommend, $reviewer_name, $date_added, $display_name, $hits )


{
global $review_id, $link, $_COOKIE, $_SESSION, $CONFIG_VAR;
global $item_id, $cat_id, $sub_cat_id, $offset;

Below is this:

    $date_added = setlocale (LC_TIME,"spanish");
$date_added = str_replace("De","de",ucwords(strftime("%A, %d de %B de %Y")));

This is the code that prints the date on the review table:

    // start of the review title table
     echo "<span class=\"mnlreviewheadlinetext2\"> ___<i>\"$review_title\"</i></span>\n";
    echo "<table width=\"94%\" border=\"0\"  cellpadding=\"1\"  cellspacing=\"1\" align=\"center\" >\n";
    echo "<tr><td colspan=\"3\">\n";
	echo "<table width=\"100%\" border=\"0\"  cellpadding=\"1\"  cellspacing=\"1\" align=\"center\" >\n";
    echo "<tr bgcolor=061376>\n";
    echo "<td width=\"50%\" height=\"20\" class=\"txtwhite\" colspan=\"2\"><b>$reviewer_name_text: " . ucwords ( $reviewer_name ) . "</b></td>\n";
    echo "<td width=\"50%\" height=\"20\" align=\"right\"  class=\"txtwhite\">$date_added</td>\n";
    echo "</tr></table>\n";	
    echo "</td></tr>\n";
	echo "<tr>\n";
    echo "<td width=\"33%\" align=\"left\" class=\"tbodysmall\"><b><font color=\"#3344C3\">Mi Veredicto:</font></b> <img src=\"$overall_score_stars\" width=\"55\" height=\"16\" align= \"absmiddle\" alt=\"Calificacion: " . $review_score . "\"></td>\n";
    echo "<td width=\"34%\" align=\"center\"  class=\"tbodysmall\"><b><font color=\"#3344C3\">¿La recomiendo?:</b></font> " . ucwords ( $recommend ) . " </td>\n";
    echo "<td width=\"33%\" align=\"right\"  class=\"tbodysmall\">Lecturas: " . $hits . "</td>\n";
    echo "</tr>\n";
    echo "</table>\n";

[tsinka]

That's exactly what I needed. Look at the with the strftime command,

strftime takes a second argument, a timestamp. strftime uses the current timestamp if you don't provide that second argument. That timestamp has to be a unix timestamp so you might need to modify the date_added string to be a unix timestamp. You can do that with e.g. UNIX_TIMESTAMP(date_added) in the SQL query.

That depends on how the date is stored in the database.

Thomas

[Haggen]

Didnt worked..

I changes my query from:

$query = "SELECT review_title, 
                review_text,
                score_1,
                score_2,
                score_3,
                score_4,
                score_5,
                score_6,
                score_7,
                score_8,
                review_score,
                recommend,
                reviewer_name,
                date_added,
                display_name,
                hits
                FROM mnl_reviews
                WHERE review_id='$review_id'";

To:

$query = "SELECT review_title, 
                review_text,
                score_1,
                score_2,
                score_3,
                score_4,
                score_5,
                score_6,
                score_7,
                score_8,
                review_score,
                recommend,
                reviewer_name,
                UNIX_TIMESTAMP(date_added),
                display_name,
                hits
                FROM mnl_reviews
                WHERE review_id='$review_id'";

And the date on a review written on November 3rd, changed to today's date. 🙁

[tsinka]

Additionally,

change
$date_added = str_replace("De","de",ucwords(strftime("%A, %d de %B de %Y")));
to
$date_added = str_replace("De","de",ucwords(strftime("%A, %d de %B de %Y",$date_added)));

[Haggen]

Ok, i changed the query to (in review.php):
UNIX_TIMESTAMP(date_added),

and the code (includes/inc_review.php):

$date_added = str_replace("De","de",ucwords(strftime("%A, %d de %B de %Y")));

to

$date_added = str_replace("De","de",ucwords(strftime("%A, %d de %B de %Y",$date_added)));

I accesed the reviews, and a reviews written on Nov. 4th 2004 it was shown as written on December 31th 1969! 🙁

Any idea?

EDIT: Checking my database, on my "reviews" table, i have a "rec_timestamp" field, maybe this has something to do.