Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

accessing data from 2 sql tables on 1 page 1

Status
Not open for further replies.
jeellison

jeellison

IS-IT--Management
May 29, 2002
78
GB
Hello All,

I have two pages which each display data from 2 different tables. Table projects on 1 page and users on the other. Now id like data from them both on the same page. Below is the code from each page.

This is the User page :

include "./init.php";
$sscript = "";

if($user['user_acct_status'] == "V")
redirect("./validate.php");

$title = "Browsing all user profiles";
$cat_name = "";

add_to_online("Browsing all user profile","$root_path/browse_users.php");

$ipp = 20;

$rt = mysql_query("SELECT COUNT(*) as total FROM {$prefix}users WHERE user_acct_status='A'") or report();
$ta = mysql_fetch_assoc($rt);

if($ta["total"]==0) {
$list_data = "<center><font color=555555><b>No users found !</b></font></center>";
$pagination = "";
$total_res = "";
}

else {

$ta['total'] = $ta['total']-1;

if(!isset($_GET['start']))
$_GET['start'] = 1;

if($settings['set_seo_onoff']=='off') {
$start=1;
if(isset($_GET['start']))
$start=_html(intval($_GET['start']));
}

$cpage = $start;
$start = ($start * $ipp) - $ipp;

$pages = "";
if($ta['total'] > $ipp) {

$next = "";
$prev = "";

$pages = "<table cellspacing='5' cellpadding='0'>
<tr>";
$total_pages = ceil($ta['total'] / $ipp)+1;

$mystart = $start+1;

// lets make the next and previous buttons
if(($mystart == 1) && ($total_pages>1)) {

$next = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_users.php?start=".intval($_GET['start']+1)."'>Next ></a></div></td>";
$prev = "";

} else
if(($mystart == 1) && ($total_pages == 1)) {

$next = "";
$prev = "";

} else
if($mystart > ($total_pages+1)){

$next = "";
$prev = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_users.php?start=".intval($_GET['start']-1)."'>< Prev</a></div></td>";

} else {
$next = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_users.php?start=".intval($_GET['start']+1)."'>Next ></a></div></td>";
$prev = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_users.php?start=".intval($_GET['start']-1)."'>< Prev</a></div></td>";
}

$pages .= $prev;

for($i=1; $i<$total_pages; $i++) {

if($cpage == $i)
$pages .= "<td width=10 align=center stlye='cellpadding:5'><div align=center class=\"myPageSelected\"><b>$i</b></div></td>";

else {
$pages .= $settings['set_seo_onoff']=='on'

? " <td width=10 stlye='cellpadding:5'><div align=center class=\"myPage\"><a href='$root_path/browse_users/".plaintext("$i")."-$i.html'>$i</a></div></td>"
: " <td width=10 stlye='cellpadding:5'><div align=center class=\"myPage\"><a href='./browse_users.php?start=$i'>$i</a></div></td>";
}
}

$pages .= "$next</tr></table>";

}

$fres = mysql_query("SELECT * FROM {$prefix}users usr, {$prefix}groups gr WHERE usr.user_acct_status='A' AND gr.group_id=usr.user_group_id ORDER by usr.user_id DESC LIMIT $start,$ipp;")
or report("fetch all games : ");

if(mysql_num_rows($fres)==0)
$list_data = "<table width=100% align=center><tr><td><b>No users found !.</b><br><br></td></tr></table>";

else {

$list_data = "
<table width='100%' border='0' cellspacing='1' cellpadding='6'>
<tr bgcolor='$table_top_color'>
<td width='35%'><b>Username and Details</b></td>
<td width='5%'>
<div align='center'><b>Feedback</b></div>
</td>
<td width='9%'>
<div align='center'><b>Join Date</b></div>
</td>
</tr>";

while($f_art_row=mysql_fetch_assoc($fres)) {

if($f_art_row['user_id'] == 1) {

$list_data .="<tr bgcolor=$bg_color2>
</tr>";

} else {

if($f_art_row['user_certified'] == "Y")
$certified = "<img src='./images/certified.gif' valign=baseline border=0>";
else $certified = "";


$profile_url = $settings['set_seo_onoff'] =='on'
? "$root_path/profile/".plaintext($f_art_row['user_name'])."-$f_art_row[user_id].html"
: "$root_path/profile.php?id=$f_art_row[user_id]";

$feedback = get_feedback($f_art_row['user_id'],"");
$jdate = date($date_format, $f_art_row['user_join_date']);

$list_data .= "
<tr bgcolor=$bg_color2>
<td style='padding:0px'>

<div class=\"myProject\">
<a href=\"./profile.php?id=$f_art_row[user_id]\" title=\"$f_art_row[user_name]\">

<span class='projectTitle'>$f_art_row[user_name] $certified</span>
<span class=smalltext><b>Group :</b> $f_art_row[group_name]. <b>City :</b> $f_art_row[user_city]</span>
</a>
</div>

</td>
<td bgcolor='#F6F6EC'>
<div align='center'>$feedback</div>
</td>
<td bgcolor='#F6F6EC'>
<div align='center'>$jdate</div>
</td>
</tr>";
}
}

$list_data .= "</table>";
}

$total_res = "
<div align='right'>$pages</div>";

$pagination = $pages;

}

$temp = template("./templates/browse_users");
eval("\$right = \"$temp\";");

$latest_projects_header = get_project_titles(4);
$categories = list_categories(15);

$temp = template("./templates/overall_header");
eval("\$overall_header = \"$temp\";");

$temp = template("./templates/overall_footer");
eval("\$overall_footer = \"$temp\";");

$temp = template("./templates/header");
eval("\$header = \"$temp\";");

if($user){

$temp = template("./templates/left_provider");
eval("\$login_data = \"$temp\";");

} else {
$temp = template("./templates/left_login");
eval("\$login_data = \"$temp\";");
}

$temp = template("./templates/left");
eval("\$left = \"$temp\";");

$temp = template("./templates/footer");
eval("\$footer = \"$temp\";");

$temp = template("./templates/main");
eval("\$main = \"$temp\";");

echo $main;
cdie();

?>



And this is the project page : Please see it is here I am wanting the City field from the users table to be displayed along with the other data but no matter what I try it will not work.




include "./init.php";
$sscript = "";

if($user['user_acct_status'] == "V")
redirect("./validate.php");

$title = "Browsing all projects";
$cat_name = "";

add_to_online("Browsing all projects","$root_path/browse_projects.php");

$ipp = $settings['set_projects_per_page']; // projects per page


$rt = mysql_query("SELECT COUNT(*) as total FROM {$prefix}projects WHERE project_status='O'") or report();
$ta = mysql_fetch_assoc($rt);

if($ta["total"]==0) {
$list_data = "<center><font color=555555><b>No projects found !</b></font></center>";
$pagination = "";
$total_res = "";
}

else {

if(!isset($_GET['start']))
$_GET['start'] = 1;

if($settings['set_seo_onoff']=='off') {
$start=1;
if(isset($_GET['start']))
$start=_html(intval($_GET['start']));
}

$cpage = $start;
$start = ($start * $ipp) - $ipp;

$pages = "";
if($ta['total'] > $ipp) {

$next = "";
$prev = "";

$pages = "<table cellspacing='5' cellpadding='0'>
<tr>";
$total_pages = ceil($ta['total'] / $ipp)+1;

$mystart = $start+1;

//$rt = mysql_query("SELECT COUNT(*) as total FROM {$prefix}users WHERE user_acct_status='A'") or report();

// lets make the next and previous buttons
if(($mystart == 1) && ($total_pages>1)) {

$next = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_projects.php?start=".intval($_GET['start']+1)."'>Next ></a></div></td>";
$prev = "";

} else
if(($mystart == 1) && ($total_pages == 1)) {

$next = "";
$prev = "";

} else
if($mystart > ($total_pages+1)){

$next = "";
$prev = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_projects.php?start=".intval($_GET['start']-1)."'>< Prev</a></div></td>";

} else {
$next = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_projects.php?start=".intval($_GET['start']+1)."'>Next ></a></div></td>";
$prev = "<td width=10 stlye='cellpadding:5'><div align=center class=\"myPagePN\"><a href='$root_path/browse_projects.php?start=".intval($_GET['start']-1)."'>< Prev</a></div></td>";
}

$pages .= $prev;

for($i=1; $i<$total_pages; $i++) {

if($cpage == $i)
$pages .= "<td width=10 align=center stlye='cellpadding:5'><div align=center class=\"myPageSelected\"><b>$i</b></div></td>";

else {
$pages .= $settings['set_seo_onoff']=='on'

? " <td width=10 stlye='cellpadding:5'><div align=center class=\"myPage\"><a href='$root_path/browse_projects/".plaintext("$i")."-$i.html'>$i</a></div></td>"
: " <td width=10 stlye='cellpadding:5'><div align=center class=\"myPage\"><a href='./browse_projects.php?start=$i'>$i</a></div></td>";
}
}

$pages .= "$next</tr></table>";

}

$fres = mysql_query("SELECT * FROM {$prefix}projects WHERE project_status='O' ORDER by project_id DESC LIMIT $start,$ipp;")
or report("fetch all games : ");

if(mysql_num_rows($fres)==0)
$list_data = "<table width=100% align=center><tr><td><b>No project found !.</b><br><br></td></tr></table>";

else {

$list_data = "
<table width='100%' border='0' cellspacing='1' cellpadding='6'>
<tr bgcolor='$table_top_color'>
<td width='35%'><b>These are our latest Projects - Are you a Tradesman? Sign up now to bid on these jobs</b></td>
<td width='5%'>
<div align='center'><b>Bids</b></div>
</td>
<td width='7%'>
<div align='center'><b>Avg Bid</b></div>
</td>
</tr>";

while($f_art_row=mysql_fetch_assoc($fres)) {

if($f_art_row['project_featured'] == "Y")
$featured = "<img src='./images/featured.gif' valign=baseline border=0>";
else $featured = "";

if($f_art_row['project_urgent'] == "Y")
$urgent = "<img src='./images/urgent.gif' valign=baseline border=0>";
else $urgent = "";

$bids = get_total_bids($f_art_row['project_id']);
$avg_bid = get_avg_bid($f_art_row['project_id']);

$f_art_row['project_cat_names'] = str_replace("^^^"," - ",$f_art_row['project_cat_names']);


$project_url = $settings['set_seo_onoff'] =='on'
? "$root_path/project/".plaintext($f_art_row['project_title'])."-$f_art_row[project_id].html"
: "$root_path/projects.php?id=$f_art_row[project_id]";

$list_data .= "
<tr bgcolor=$bg_color2>
<td style='padding:0px'>

<div class=\"myProject\">
<a href=\"./project.php?id=$f_art_row[project_id]\" title=\"$f_art_row[project_title]\">

<span class='projectTitle'>$f_art_row[project_title] $urgent$featured</span>
<span class=smalltext><b>Category :</b> $f_art_row[project_cat_names].<b> City :</b> $f_art_row[user_city]</span>
</a>
</div>

</td>
<td bgcolor='#F6F6EC'>
<div align='center'>$bids</div>
</td>
<td bgcolor='#F6F6EC'>
<div align='right'>&pound;$avg_bid</div>
</td>
</tr>";
}

$list_data .= "</table>";
}

$total_res = "
<div align='right'>$pages</div>";

$pagination = $pages;

}

$temp = template("./templates/browse_projects");
eval("\$right = \"$temp\";");

$latest_projects_header = get_project_titles(4);
$categories = list_categories(15);

$temp = template("./templates/overall_header");
eval("\$overall_header = \"$temp\";");

$temp = template("./templates/overall_footer");
eval("\$overall_footer = \"$temp\";");

$temp = template("./templates/header");
eval("\$header = \"$temp\";");

if($user){

$temp = template("./templates/left_provider");
eval("\$login_data = \"$temp\";");

} else {
$temp = template("./templates/left_login");
eval("\$login_data = \"$temp\";");
}

$temp = template("./templates/left");
eval("\$left = \"$temp\";");

$temp = template("./templates/footer");
eval("\$footer = \"$temp\";");

$temp = template("./templates/main");
eval("\$main = \"$temp\";");

echo $main;
cdie();

?>

Im hoping this is simple but everything I have tried is not working.

Any advice on this would be greattly appreciated.

Best Regards
James
 
Sort by date Sort by votes
First, surround your code with [ignore]
Code:
[/ignore] tags.
Second: How is the city field connected to the other data?

Third I see several queries there which one is the one that is supposed to produce the city field in the project page?

I see a call to $f_art_row[user_city] is this the one you want? Does that field actually exist in the {$prefix}projects table? Or does it exist in the {$prefix}users table only?

Its very difficult to sift through all that.

Perhaps you can narrow it down to the query that you are trying to modify, and maybe explain a bit more what it is you want.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Hello,
The user page works and calls user_city fine.

It is in the Projects page where this doesnt work.

<code>
<span class=smalltext><b>Category :</b> $f_art_row[project_cat_names].<b> City :</b> $f_art_row[user_city]</span>
</code>

Thanks
James
 
Upvote 0 Downvote
So again I ask does the user_city field exist only in the {$prefix}users table in the first part of the code.

Or is it also supposed to exist in the table used in the project page code: {$prefix}projects

If it exists only in the {$prefix}users table, then something in the projects table has to point to it so they can be joined.

I cannot just guess how your database tables are set up. To know exactly how to join them. You need to tell us more.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Sorry about the lack of info, im struggling here im afraid.

The user_city field exist only in the {$prefix}users table.

BUT i want to call this in the projects page too.

Best Regards
James

 
Upvote 0 Downvote

O.K. its clear this is not your code nor do you know that much PHP and MYSQL, so that makes things harder.

Lets see if I can illustrate with an example:

Lets suppose for a minute you have the following tables with the following fields in them:
Code: 
 ---------------           -------------
|Projects       |         | Users       |
+++++++++++++++++         +++++++++++++++
|project_id     |         |  user_id    |
|user_id        |         |  user_name  |
|project_info   |         |user_password|
|etc...         |         |  user_city  |
 ---------------           -------------

If I wanted to join these 2 tables to get the user_city displayed alongside the project info I would do something like this:
Code: 
SELECT *FROM Projects,Users WHERE Projects.project_id=1 AND Users.user_id=Projects.user_id

In this case the user_id would link both tables and let me get the user city for the project who's ID is 1.

I can do the join because I know the structure of the table.
On the other hand I have no idea how your tables are structured.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Hello Phil,

Thank you again for your help.

SO i now have :

Code: 
$rt = mysql_query("SELECT COUNT(*) as total FROM {$prefix}projects, {$prefix}users WHERE project_status='O' AND {$prefix}projects.project_user_id = {$prefix}users.user_id")

user_id in the users table is the same as project_user_id from the projects table.

The sql seems to work but it is still not displaying the

Code: 
<b> City :</b> $f_art_row[user_city]

Frustrating as I fell im so close.

Best Regards
James
 
Upvote 0 Downvote
See that's the question;

{$prefix}projects.project_user_id = {$prefix}users.user_id")

Is there a user_id in both tables?

Do the ID's match?

Are you getting an error?









----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Hi again,

No not getting a error and Yes they do match.

There is no user_id in Projects, only in Users.

Best Regards
James
 
Upvote 0 Downvote
Then there has to be some other field that can join both of them. A field that exists in both tables and that would be used to link the data.

Otherwise, how would you know user_city belongs to which project?




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
All working now thanks, you really helped.

I was changing the wrong query.

This is what i now working :

Code: 
$fres = mysql_query("SELECT * FROM {$prefix}projects, {$prefix}users WHERE project_status='O' AND {$prefix}users.user_acct_status='A' AND {$prefix}users.user_id = {$prefix}projects.project_user_id ORDER by project_id DESC LIMIT $start,$ipp;")

And projects.project_user_id is the same as users.user_id.

This is where they are linked I take it.

Thank you for your time.

Best Regards
James Ellison

 
Upvote 0 Downvote
Glad I could help.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
284
ersatz
ersatz
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
185
spamjim
spamjim
Brianfree
  • Locked
  • Question
Dropdown list data generator
Replies
3
Views
184
Brianfree
Brianfree
P
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
224
Chris Miller
Chris Miller
c0deM0nK424
  • Locked
  • Question
Gutenberg Block Editor - page edits and updates not working since wp site migration
Replies
1
Views
417
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top