Page 1 of 1

Can't display records when querying two tables

Posted: Wed Oct 28, 2009 12:14 pm
by dreeves
For my database, I enter a reimbursement request form which has multiple invoices on it. Each invoice is a unique record with a unique_ID and the corresponding request_number. Each individual invoice is stored in invoiced_activity(table) and the sum of the invoices are stored in reimbursement_requests(table). I would like to query both tables in the database so it will display each invoice and all information from the reimbursement_form.
Also, the Request_Number field is in both tables (foreign key)

Code: Select all

 
<?php
$query="SELECT * FROM invoiced_activity, reimbursement_requests WHERE Request_Number='90'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close($link);
 
$i=0;
while ($i < $num) {
$Name=mysql_result($result,$i,"Name"); // from [invoiced_activity}
$Number=mysql_result($result,$i,"Number"); // from [invoiced_activity}
$unique_ID=mysql_result($result,$i,"unique_ID"); // from [invoiced_activity}
$Total_Invoiced_Amount=mysql_result($result,$i,"Total_Invoiced_Amount"); // from [reimbursement_requests]
$Contractor_Name=mysql_result($result,$i,"Contractor_Name"); // from [reimbursement_requests]
 
<HTML>
 <TABLE BORDER="0">
  <TR>
   <TH>Name</TH>
   <TD><input type="text" name="Name" value="<?php echo $Name; ?>"/></TD>
   <TH>Number</TH>
   <TD><input type="int" name="Number" size="4" value="<?php echo $Number; ?>" /></TD>
   <TH>Unique ID</TH>
   <TD><Input type="int" name="Unique_ID" value="<?php echo $Unique_ID; ?>" /></TD>
   <TH>Total Invoiced Amount</TH>
   <TD><Input type="text" name="Total_invoiced Amount" value="<?php echo $Total_invoiced_Amount; ?>"size="10"/></TD>
   <TH>Contractor Name</TH>
   <TD><Input type="text" name="Contractor_Name" value="<?php echo $Contractor_Name; ?>"size="10"/></TD>
  </TR>
 </TABLE>
</HTML>
<?php
++$i;
}
?>
 
When executed, I receive the error:
Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\wamp\www\update_test.php on line 4

It should return 3 records(invoices) When I query only on one table only it's fields, it works fine. But wen I try to get data from another, I receive that error.

Re: Can't display records when querying two tables

Posted: Wed Oct 28, 2009 12:50 pm
by superdezign
http://php.net/mysql-query wrote:resource mysql_query ( string $query [, resource $link_identifier ] )
[...]
mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.
You do not have permission to perform that query. Where do you make your database connection, and why aren't you sending the connection resource to mysql_query()?

Re: Can't display records when querying two tables

Posted: Wed Oct 28, 2009 1:04 pm
by dreeves
I guess I left this part out when I was pasting:

Code: Select all

 
<?php
$username="username";
$password="password";
$database="fcp1";
 
$link= mysql_connect('localhost',$username,$password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
 
@mysql_select_db($database) or die( "Unable to select database");
 
With it all combined, it works fine when I query only one table. When I involve another table and its fields, I get the musql_numrows() error.

Re: Can't display records when querying two tables

Posted: Wed Oct 28, 2009 3:19 pm
by califdon
Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\wamp\www\update_test.php on line 4
When a query fails, there is no resource to be returned, so mysql_query() returns False, which is a boolean value.

Since you didn't use or die(mysql_error()) after your query, you have no way to know why your query failed. Hint: use or die(mysql_error()) after your query so you will know why your query failed.

Re: Can't display records when querying two tables

Posted: Wed Oct 28, 2009 3:50 pm
by dreeves
I added that and it got us a little closer to solving the problem. It returned:
Retrieve record failed - 1052: Column 'Request_Number' in where clause is ambiguous
Column 'Request_Number' is in both tables, so I was hoping I wouldn't need to specify the column twice. Maybe I do. How would I do that?

Code: Select all

 
$query="SELECT * FROM invoiced_activity, reimbursement_requests WHERE Request_Number='100'";
 
Returned: Retrieve record failed - 1052: Column 'Request_Number' in where clause is ambiguous

or maybe like this

Code: Select all

 
$query="SELECT * FROM invoiced_activity, reimbursement_requests WHERE invoiced_activity.Request_Number='100', reimbursement_requests.Request_Number='100'";
 
Returned: Retrieve record failed - 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' reimbursement_requests.Request_Number='100'' at line 1

Re: Can't display records when querying two tables

Posted: Wed Oct 28, 2009 4:21 pm
by califdon

Code: Select all

$query="SELECT * FROM invoiced_activity INNER JOIN reimbursement_requests 
WHERE invoiced_activity.Request_Number='100';
 
 
or

Code: Select all

$query="SELECT * FROM invoiced_activity, reimbursement_requests 
WHERE invoiced_activity.Request_Number=reimbursement_requests.Request_Number AND invoiced_activity.Request_Number='100';
 
 
which are equivalent.

Returning to your original post, you mentioned "Each individual invoice is stored in invoiced_activity(table) and the sum of the invoices are stored in reimbursement_requests(table)." That raises a serious question about the normalization of your data. In most circumstances, you should never store calculated data, such as the sum of values stored elsewhere in the database. There can be exceptions, but usually it is a serious mistake, making the database vulnerable to inconsistencies and difficult queries. I suggest that you read a few articles on relational database data normalization. Using those terms to search on Google, you will find ample references.

Re: Can't display records when querying two tables

Posted: Thu Oct 29, 2009 9:55 am
by dreeves
I am making a serious effort today to normalize my database and trying not to have redundant entries. If I include the sum of the invoices in the invoiced_activity table, would it be efficient to repeat the sum, say 3 times?
Example: Invoiced_Activity(table) "Unique_ID" is the primary key

Code: Select all

 
FORM_# | Unique_ID | Invoiced_Amount | FORM_Total
   3         65            $10            $65
   3         66            $25            $65
   3         67            $30            $65
   4         68            $45            $45
 
the old way I was storing it had a table identical to the above example, minus the Form_Total column, plus the following table.

Form_Requests(table) "FORM_#" is the primary key and the foreign key to the previous table.

Code: Select all

 
FORM_# | FORM_Total
   3         $65
   4         $45
 
I'm not quite sure how to resolve this normalization issue.

Re: Can't display records when querying two tables

Posted: Thu Oct 29, 2009 12:42 pm
by califdon
To even begin to talk about normalization, you must begin with your data. Could you show us exactly how your tables are structured (before making any changes in them) in a format like this:

Code: Select all

[b]invoiced_activity[/b]:
   (list field names here)
   ...
 
[b]reimbursed_requests[/b]:
   (list field names here)
   ...
As well as I could understand what you said in your post, you are still trying to store the aggregate or sum of invoices in a table. That's what's wrong. Use queries to sum the invoices, don't store the sums at all.

Re: Can't display records when querying two tables

Posted: Thu Oct 29, 2009 1:25 pm
by dreeves
Thanks for your help. Here are my tables and a little explanation of each field. For your own info, the total_invoiced_amount isn't calculated. It is calculated by the client and filled in on a hard copy. We are just taking the hard copy and adding it to our database.
One request can have multiple invoices within it. That is why I separated it into two tables.

Code: Select all

 
[b]invoiced_activity:[/b]
   request_number
   invoiced_activity_id [primary key]
   invoice_date
   practice_code
   Units_performed
   unit_of_measure
   rate_per_unit
   invoiced_amount
 
[b]reimbursement_request:[/b] (all of these fields will be the same for each invoice)
   request_number [primary key] & [foreign key]
   date_entered
   added_by (the employee who enters the info into the DB)
   Name (client name)
   Number (client number)
   total_invoiced_amount (sum of all the invoices)
   admin_fee
   direct_pay
   contractor_name
   contractor_address
 
One request could have 4 invoices. If all of this information was in one table, then all 4 invoices would have the same date_entered, added_by, name, number, total_invoiced_amount, admin_fee, direct_pay, contractor_name & contractor_address. Repeating that data four times seemed redundant.