List results from two separate tables?

MySQL database
#1

Hello. I have recently started using User Cake for an online portal. I’ve primarily been working with one specific table (the user table), but I need to be able to reference another table based on a certain field.

Here’s the scenario. Each user is assigned an ID. I have a second “accounts” table, each having a field corresponding to a user (using that ID number). I have created a page that they can click on that will tell them how many accounts are linked to them, but I don’t have any idea where to start with the query that will return the results. Some users many have only 1 related account, while others may have 20. I think it needs to be something like select “accountname” from “accounts” where “id=($loggedInUser->id)” and then somehow tell it to list the information for me.

Some guidance would be helpful as I’m not sure of the exact syntax to use and the SQL tutorials that I’ve found on the internet are too vague for me.

#2

BUMP Can anyone assist?

#3

Not sure what you are asking. Do you just want to be able to query the “accounts” table using an index?

So, when you create your “accounts” table, you store a record which contains the “userid” field
that contains the user’s ID and other data as needed such as the account number they selected.
You end up with a list of rows of data something like:

userid account-number
1 1
1 99
3 1
3 1000
4 99

whatever was entered. To pull that data out, let’s say for user id #3, just query the table.
Something like this: SELECT * FROM accounts WHERE userid=‘3’ Would return 1,10000…
Number three user ID’s value could be a variable that you loaded when the user logs in.

Is that what you need?

#4

@ErnieAlex,
Thank you for your response. Let me clarify what it is I’m looking to do.

Table 1 (“uc_users”) is used for user access to an online portal and is for all of our sales reps to use. It has fields such as user name, rep id, address, email, etc.

Table 2 (“uc_accounts”) is used for storing our accounts that these reps (users) have signed up over the years. It has fields such as name, address, start date, rep id. The rep id in “uc_accounts” should correspond with some rep id from the “uc_users” table.

When a rep (user) is logged into our portal, I want them be able to go to the “my accounts” page which will display a list of all of the accounts that have the same rep id as their rep id.

Here is an example of the php webpage file that they would click on to get this information.

[php]<?php

require_once(“models/config.php”);
if (!securePage($_SERVER[‘PHP_SELF’])){die();}
require_once(“models/header.php”);

echo "

Welcome!

My Accounts Page

"; include("left-nav.php");

echo "

Below is a list of your accounts.

/* SOME CODE HERE



"; ?> [/php]

I need to determine what to put in the “some code here” part. I’ve tried just putting in: "SELECT * FROM uc_accounts WHERE repid= ‘$loggedInUser->id’ " but I know that there’s more to it than just that.

Does that help clarify my need?

#5

Well, that is kind of a loaded question…

First, how do you know which user is logged in when you go to this page?
You must have some sort of user-login where you save a value which would be
the rep ID for that user. So, let’s assume for now that value is user or rep ID # 3.

Here is how you would use PHP to read the MySQL database and load the data and
then display it back out. Not sure if that is what you want but…
[php]
// Assuming you have the rep/user id stored in a variable I will call $rep_id
$rep_id = 3; // I am setting that just for testing…

// Execute the query
$query = “SELECT * FROM uc_accounts WHERE rep_id=” . $rep_id;
$results = mysql_query($query);

// Check the results to see if it got any data, if not show an error (You would alter this for your own use)
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$results) {
$message = 'Invalid query: ’ . mysql_error() . “\n”;
$message .= 'Whole query: ’ . $query;
die($message);
}

// Query worked, now check for data, if none say so, otherwise display it
if ( mysql_num_rows($results) == 0 ) {
echo “No accounts found in your records!”;
} else {
while ($row = mysql_fetch_assoc($result)) {
echo "Account Name: " . $row[‘name’] . " - "; // Display whatever data fields you need
echo "Address: " . $row[‘address’] . " - ";
echo "Start Date: " . $row[‘start_date’] . “
”;
}
}
?>
[/php]
Note: I did not know your field names and for this test, you need to know one rep ID to test with.
Also, I just dummied this up and did not test it as I do not have your database available.
Most programmers would stick this info inside of a table so it is neatly displayed. Not in this example!

Is that what you were looking for?

#6

That’s precisely what I was looking for (fyi: the variable for the user is $loggedInUser->bankid). I think I owe you some flowers or wine or chocolate or something! :slight_smile:

…but I get the following error when I try to load the page.

Thoughts? Note: Line 24 is where the “$results = mysql_query($query)” is.

Warning: mysql_query() [function.mysql-query]: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) in /home/content/80/11264980/html/urepaccess/login/user_accounts_test.php on line 24

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/content/80/11264980/html/urepaccess/login/user_accounts_test.php on line 24
Invalid query: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) Whole query: SELECT * FROM uc_accounts WHERE rep_id=0

#7

Sorry, forgot to mention you still need your database connection included.

(I had assumed you knew that. Me bad!)

So, near the beginning of your other pages, you should have some sort of connection string that
is included in your PHP pages. You would need that.

It would look something like this:

<?php // Connect to the server database system... $sql_connect = mysql_connect('www.yoursite.com', 'your user id', 'your user password'); if (!$sql_connect) { die('Could not connect to server database: ' . mysql_error()); } // Connected to DB, now select a database to use... $return = mysql_select_db("table2", $sql_connect); // connected to table 2 in the DB, now rest of stuff.... ?>

But, usually you would already have that info in an include file that you just add to the beginning of
every page that uses the database. Hope that helps…

#8

That’s what is odd because I do have the include at the beginning of my doc…

[php]require_once(“models/config.php”);
if (!securePage($_SERVER[‘PHP_SELF’])){die();}
require_once(“models/header.php”);[/php]

and config.php has the database connection instructions, along with these includes…

[php]require_once(“class.mail.php”);
require_once(“class.user.php”);
require_once(“class.newuser.php”);
require_once(“funcs.php”);[/php]

I added the following to func.php because I saw it similar with the “users” table so I just mimicked the setup, but get the same error.

[php]function fetchAllAccounts()
{
global $mysqli,$db_table_prefix;
$stmt = $mysqli->prepare("SELECT
account,
mid,
date,
pmt,
repid
FROM ".$db_table_prefix.“account”);
$stmt->execute();
$stmt->bind_result($account, $mid, $date, $pmt, $repid);

while ($stmt->fetch()){
	$row[] = array('account' => $account, 'mid' => $mid, 'date' => $date, 'repid' => $repid);
}
$stmt->close();
return ($row);

}[/php]

Obviously, I’m missing something somewhere.

Also, I tried adding the $return command like how you suggested, but I get a result that say “mysql_select_db() expects parameter 2 to be resource, null given…”

#9

Well, it appears that you would have to do something like:

global $mysqli,$db_table_prefix;
$stmt = $mysqli->prepare("SELECT account, mid, date, pmt, repid FROM ".$db_table_prefix.“account” WHERE repid=$rep_id);
$stmt->execute();
$stmt->bind_result($account, $mid, $date, $pmt, $repid);
while ($stmt->fetch()){
$results[] = array(‘account’ => $account, ‘mid’ => $mid, ‘date’ => $date, ‘repid’ => $repid);
}
$stmt->close();

Then continue with checking $results to see if anything was in it and displaying if there was…
The difference is that you are not using MySQL queries, but, MySQLi queries. A big difference on how
they are used. (Hence all the => 's )
this was left like the sample you posted.

#10

Okay, I got passed the connection issue - but now I can’t get it to display any results.

I’m getting: mysql_fetch_assoc() expects parameter 1 to be resource, null given in…on line 53

From my understanding, the error is not with the code on line 53, but rather the $query itself. So, I pasted the query into myphpadmin and it provides the results that I’m looking for, so I don’t know why it’s getting this error. When I try to search the web, all of the results have to do with “boolean given” and not “null given”.

I turned off error reporting just to see if the query would pass, but i just get a blank page.

Thoughts?

#11

Okay, we are getting closer. So, which line is line 53? Or, give me the couple before and after, too…
I am sure it is something simple. You are nearly there. I’m glad cuz I have been helping everyone all day and need to take a break soon… Let me know those lines…

#12

Here’s what I got…

Above this is just my includes and some html.
[php]
$query = (“SELECT * FROM uc_accounts WHERE rep=’$loggedInUser->id’”);
$results = mysql_query($query);
if (!$results) {
$message = 'Invalid query: ’ . mysql_error() . “\n”;
$message .= 'Whole query: ’ . $query;
die($message);
}
if ( mysql_num_rows($results) == 0 ) {
echo "

You do not have any accounts yet.
Contact your coach to continue your training!

"; } else { while ($row = mysql_fetch_assoc($result)) {echo "
Account Name: " . $row['account'] . " -
Account ID: " . $row['mid'] . " -
Start Date: " . $row['date'] . " -
Payment Date: " . $row['pmt'] . "

; "; } }

?>
[/php]

#13

You said your error was line #53. But, you listed 25 lines. Which one is the real #53?

Ooooops ! ! ! Never mind… You pull the query into variable named “$results”.
But, you pull the associated data from the variable “$result”. These are two different variables!

Sorry I didn’t catch that sooner. Been gone for a couple days… Hope that fixes it for you…

#14

Whoooop Whooop! That was it!

Thanks!

#15

LOL, I really hate those little ones… Glad it is solved. I will mark it solve.

CYA in the next problem !

#16

@ErnieAlex…

can you by chance help me with one more thing…

I’ve edited the layout of the echo to show a html table instead of listing the data. The problem is that only the first record is returned as part of the table…the remaining records are not enclosed within the table (see attached image for sample).

What can I do to the table to leave it “open” so that the rest of the results are included? Here’s where i’m at with the code:

[php] echo “


Your Accounts








”;
while ($row = mysql_fetch_array($results))
{
echo "






";

}}}
?> [/php]

sample.JPG
sample.JPG766×134 13.1 KB

Account Name Merchant ID Start Date Payment Date
" .$row[‘merchant’]." " .$row[‘mid’]." " .$row[‘date’]." " .$row[‘pmt’]."
#17

Well, you end the table before looping thru all of the data. So, actually it is doing exactly what you
told it to do. LOL It’s just the placement of the ending brace for the WHILE loop…
[php]
while ($row = mysql_fetch_array($results))
{
echo "

" .$row['merchant']." " .$row['mid']." " .$row['date']." " .$row['pmt']." ; } // MISSING THE ENDING BRACE HERE. You need to loop thru all rows. "; }}} ?> [/php] ALSO, after the row ends (), you also have to end the table, then the div's before the end of the page. () and html ()

Hope that helps…

#18

Oooops, pressed send too fast! You are missing a brace, but, most likely have an extra one in the group of three of them.

You should learn to format your code. Indent all of the groups such as IF’s, WHILE’s, DIV’s, etc and then
it is MUCH easier to locate errors…
Like:

<?PHP some code WHILE (something) { More code... } ?>

In this manner, you can look at just the one small area that is causing the error. In which case, you
would have noticed the brace not being in the correct spot. Hope all this helps! Let us know…

Sponsor our Newsletter | Privacy Policy | Terms of Service