[SOLVED] Records from 2 tables

deepermethod · November 9, 2008

Kind of hard to explain in words, but here goes...

I have two tables:

1. images

Fields: image_id, directory, image, url, displayname, default_pic

2. profiles

Fields: profile_id, directory, dark_color, light_color, panel_text, table_color, font_color, font_style, font_size, profile_url, page_name, ratings, p_name, location, gender, age, marital, about_me, hobbies

The file is a list New Members by their display name and their uploaded image. That file works fine.

<?php
$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
$sql = "SELECT * FROM $tbl_images WHERE displayname != \"Community\" AND default_pic = \"yes\"
ORDER BY image_id DESC LIMIT 0, 2";
$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");
$num=mysql_num_rows($result);
if($num < 1){

echo "<center>No Members with pictures found.</center>";

} else {

?>
<table align="center" cellpadding="4" cellspacing="0" width="100%">
<?
while ($row = mysql_fetch_array($result)) {
$url = $row['url'];
$image = $row['image'];
$member = $row['displayname'];
$fullurl = "$userurl/$url";
$src = "$userdir/$url/$image";
echo "<tr>
        <td width=\"52\" valign=\"middle\" class=\"newmem\">
            <p><a href=\"$fullurl\"><img src=\"thumbs/phpThumb.php?src=/$src&w=50\" border=\"0\"></a></p>
        </td>
        <td valign=\"middle\" class=\"newmem\">
            <p><a href=\"$fullurl\">$member</a></p>
        </td>
    </tr>";
}
?>
</table>
<? } ?>

I wanted to add some other information (members age) from the profiles table. I need to search and match the members image and displayname from the images table with their age from the profiles table.

How can I search and match the current information with the correct information from the profiles table?

corbin · November 9, 2008

How exactly are the two tables related?

deepermethod · November 10, 2008

The image_id (image table) and the profile_id (profile table) are relative to each member (they match).

corbin · November 10, 2008

Oh.

Don't feel like writing out the entire query, but the basic structure would be:

SELECT p.*, i.* FROM profile p LEFT JOIN image i ON i.image_id = p.profile_id;

deepermethod · November 10, 2008

Thanks corbin, I am REALLY new to php/mysql. I really don't understand JOIN. When you get time could you write something up so I can study it (that's how I learn the best - by dissecting the code, reading directions is not my thing).

Any help is greatly appreciated.

deepermethod · November 10, 2008

SELECT p.*, i.* FROM profile p LEFT JOIN image i ON i.image_id = p.profile_id;

Is that to be done in a separate query than the image/displayname query?

$sql = "SELECT * FROM $tbl_images WHERE displayname != \"Community\" AND default_pic = \"yes\"
ORDER BY image_id DESC LIMIT 0, 2";
$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");
$num=mysql_num_rows($result);
if($num < 1){

echo "<center>No Members with pictures found.</center>";

} else {

Thanks again.

corbin · November 10, 2008

Nope.

SELECT p.*, i.* FROM images i JOIN profile p ON p.profile_id = i.image_id WHERE i.displayname != 'Community' AND default_pic = 'yes';

http://www.tizag.com/mysqlTutorial/mysqljoins.php

http://dev.mysql.com/doc/refman/5.0/en/join.html

deepermethod · November 10, 2008

Got an error: Couldn't execute query.

Here's the full new code:

<?php



//Prevent the included file from being called directly.

if(basename($_SERVER['PHP_SELF']) == "newestmember.inc.php") {

   header("Location: /index.php");

   exit;

}
$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
$sql = "SELECT p.*, i.* FROM images i JOIN profile p ON p.profile_id = i.image_id WHERE i.displayname != 'Community' AND default_pic = 'yes'
ORDER BY image_id DESC LIMIT 0, 5";
$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");
$num=mysql_num_rows($result);
if($num < 1){
echo "<center>No Members with pictures found.</center>";
} else {
?>
<table align="center" cellpadding="4" cellspacing="0" width="100%">
<?
while ($row = mysql_fetch_array($result)) {
$url = $row['url'];
$image = $row['image'];
$member = $row['displayname'];
$fullurl = "$userurl/$url";
$src = "$userdir/$url/$image";

echo "<table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
  <tbody>
    <tr>
      <td>new to lounge{in}</td>
    </tr>
  </tbody>
</table>
<table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
  <tbody>
    <tr>
      <td><a href=\'$fullurl\'><img src=\'thumbs/phpThumb.php?src=/$src&w=50\' border=\'0\' alt=\'$member\'></a></td>
      <td align='undefined' valign='undefined'>
      <table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
        <tbody>
          <tr>
            <td><a href=\"$fullurl\">$member</a> $age</td>
          </tr>
          <tr>
            <td align='undefined' valign='undefined'>location</td>
          </tr>
        </tbody>
      </table>
      </td>
    </tr>
    <tr>
      <td align='undefined' valign='undefined'>sgl div</td>
      <td align='undefined' valign='undefined'>sgl div</td>
    </tr>
  </tbody>
</table>";
}
?>
<? } ?>

Also, how do I output the age from the profiles table? Sorry to be a bother, this is driving me nuts. I have been trying to read how to fix this literally all day.

Thanks.

deepermethod · November 10, 2008

I have bookmarked http://www.tizag.com/mysqlTutorial/mysqljoins.php. That seems like a very simple example. Mine seems a lot more involved. Still, it is great reading.

Thanks

corbin · November 10, 2008

Change

$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");

to

$result = @mysql_query($sql,$connection) or die(mysql_error());

I probably had a typo in there somewhere or something.

deepermethod · November 10, 2008

Thanks, it works but just outputs what the original file did (displayname and image). How do I tie in the age from the profiles table (you can see the fields in my first post).

corbin · November 10, 2008

You'll need to change the HTML....

deepermethod · November 10, 2008

Change the HTML? So, the query matched the image_id and the profile_id. How do I pull the age from the profiles table? If you look in the code below I want to have the age appear where it says INSERT AGE HERE (in the HTML part). How do I define the age and the print it?

<?php



//Prevent the included file from being called directly.

if(basename($_SERVER['PHP_SELF']) == "newestmember.inc.php") {

   header("Location: /index.php");

   exit;

}
$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");
$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
$sql = "SELECT p.*, i.* FROM images i JOIN profile p ON p.profile_id = i.image_id WHERE i.displayname != 'Community' AND default_pic = 'yes'
ORDER BY image_id DESC LIMIT 0, 5";
$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");
$num=mysql_num_rows($result);
if($num < 1){
echo "<center>No Members with pictures found.</center>";
} else {
?>
<table align="center" cellpadding="4" cellspacing="0" width="100%">
<?
while ($row = mysql_fetch_array($result)) {
$url = $row['url'];
$image = $row['image'];
$member = $row['displayname'];
$fullurl = "$userurl/$url";
$src = "$userdir/$url/$image";

echo "<table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
  <tbody>
    <tr>
      <td>new to lounge{in}</td>
    </tr>
  </tbody>
</table>
<table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
  <tbody>
    <tr>
      <td><a href=\'$fullurl\'><img src=\'thumbs/phpThumb.php?src=/$src&w=50\' border=\'0\' alt=\'$member\'></a></td>
      <td align='undefined' valign='undefined'>
      <table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
        <tbody>
          <tr>
            <td><a href=\"$fullurl\">$member</a>INSERT AGE HERE</td>
          </tr>
          <tr>
            <td align='undefined' valign='undefined'>location</td>
          </tr>
        </tbody>
      </table>
      </td>
    </tr>
    <tr>
      <td align='undefined' valign='undefined'>sgl div</td>
      <td align='undefined' valign='undefined'>sgl div</td>
    </tr>
  </tbody>
</table>";
}
?>
<? } ?>

corbin · November 10, 2008

Replace INSERT AGE HERE with $row['age'].

Don't take this the wrong way, but consider reading a tutorial on PHP+MySQL.

deepermethod · November 10, 2008

Sorry to bother you corbin.

Can someone tell me why I am getting this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/xxxxx/public_html/xxxxx/include/main_new_members.inc.php on line 61

The code is below. See "LINE 61 IS HERE"

<?php



//Prevent the included file from being called directly.

if(basename($_SERVER['PHP_SELF']) == "newestmember.inc.php") {

   header("Location: /index.php");

   exit;

}



$connection = @mysql_connect("$db_host", "$db_user", "$db_pass") or die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");



$sql = "SELECT p.*, i.* FROM images i JOIN profiles p ON p.profile_id = i.image_id WHERE i.displayname != 'Community' AND default_pic = 'yes'

ORDER BY image_id DESC LIMIT 0, 2";

$result = @mysql_query($sql,$connection) or die(mysql_error());

$num=mysql_num_rows($result);

if($num < 1){

echo "<center>No Members with pictures found.</center>";

} else {

?>
<table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
  <tbody>
    <tr>
      <td>new to lounge{in}</td>
    </tr>
  </tbody>
</table>
<?

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



$url = $row['url'];

$image = $row['image'];

$member = $row['displayname'];

$fullurl = "$userurl/$url";

$src = "$userdir/$url/$image";

LINE 61 IS HERE --> $age = "$row['age']";

echo "
<table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
  <tbody>
    <tr>
      <td><a href=\"$fullurl\"><img src=\"thumbs/phpThumb.php?src=/$src&w=50\" border=\"0\"></a></td>
      <td align='undefined' valign='undefined'>
      <table style='text-align: left; width: 100%;' border='0' cellpadding='0' cellspacing='0'>
        <tbody>
          <tr>
            <td><a href=\"$fullurl\">$member</a>$age </td>
          </tr>
          <tr>
            <td align='undefined' valign='undefined'>location</td>
          </tr>
        </tbody>
      </table>
      </td>
    </tr>
    <tr>
      <td align='undefined' valign='undefined'>sgl div</td>
      <td align='undefined' valign='undefined'>sgl div</td>
    </tr>
  </tbody>
</table>
";

}

?>

</table>

<? } ?>

corbin · November 10, 2008

$age = "$row['age']";

$age = $row['age'];

deepermethod · November 10, 2008

You have made my day corbin. Now that you got it working, I will go and read the tut's that you posted so I can understand it better.

I am sorry for any inconvenience.

corbin · November 10, 2008

No problem, and it's no inconvenience. It's a forum; how could it inconvenience me? ;p

Sign In

[SOLVED] Records from 2 tables

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information