Multiple SQL Queries under PHP

[theriddla1019]

Im running multiple sql statements to bring in data from 3 tables in a single database. My first two sets of queries accesses the correct tables and returns the data to its correct position within the HTML document. Building the 3rd query though has given me an err about
mysql_fetch_assoc(): Supplied argument is not a valid MySql Result Resource in (Page Location) - Now im assuming I should be using subqueries to compile my statements but im not sure how to bring them together. Here are my first two queries in order followed by the 3rd one that does not work.

[edited by: theriddla1019 at 5:49 pm (utc) on Jan. 8, 2004]

[coopster]

I'm guessing it is probably because your UPIN field is not numeric, probably of character or date column type. Those column types need to be enclosed in quotation marks.

$Query1 = "SELECT * FROM upininfo WHERE UPIN = '" . $Row['UPIN'] . "'";  

However, you won't have to concern yourself with that if you use a single statement as follows (based on your assumption):

>>Now im assuming I should be using subqueries to compile my statements but im not sure how to bring them together.

Not subqueries in this case, but a JOIN [mysql.com] statement. You could get one result set by using a single query statement such as:

$sql = "SELECT * FROM poc  
INNER JOIN patientdata USING (MR)  
INNER JOIN upininfo USING (UPIN)  
WHERE POCID = '" . $key"; 

I assumed your POCID is a numeric column type.

[theriddla1019]

Half of this page was built by an editor ASAP. Its done some funny things, I understand that that code will work but in this situation it doesnt(probably because im not using it correctly), let me post the full code it may make a difference to the situation.

*Or how do i reference the rows from the joined tables?
Cause i need to populate the variables from the tables to distribute throughout the form that follows this php script.

modnote: see charter [webmasterworld.com] re code posts

[edited by: jatar_k at 6:10 pm (utc) on Jan. 8, 2004]

[jatar_k]

I trimmed your code to a more manageable size and found some strange braces that are not attributed to anything

.....

$itemNumber = $HTTP_GET_VARS['itemNumber'];
if (isset($HTTP_GET_VARS['key'])) $key = $HTTP_GET_VARS['key'];

.....

require("POC_connect.php");
$Link = mysql_pconnect($Host, $User, $Password);
mysql_select_db($DBName, $Link);

if (isset($HTTP_GET_VARS['edit'])) {

$Query = "SELECT * FROM poc WHERE POCID = " . $key;
$Result = mysql_query($Query, $Link);
$Row = mysql_fetch_assoc($Result);

< 45 or so assignment statements here >

} else {

$Query = "SELECT * FROM poc " . $whereclause . $fullOrderBy . " LIMIT " . $itemNumber . ", 1";
$Result = mysql_query($Query, $Link);
$Row = mysql_fetch_assoc($Result);

$key = $Row['POCID'];
< 45 or so assignment statements here >
}

$URL = "Form485.php?key=" . $key . "&itemNumber=" . $itemNumber;

{

$Query2 = "SELECT * FROM patientdata WHERE MR = " . $MR;
$Result2 = mysql_query($Query2, $Link);
$Row2 = mysql_fetch_assoc($Result2);

< 13 or so assignment statements here >

}
{

$Query1 = "SELECT * FROM upininfo WHERE UPIN = '" . $Row['UPIN'] . "'";
$Result1 = mysql_query($Query1, $Link);
$row1 = mysql_fetch_assoc($Query1);

$PhyLast = $Row1['PhyLast'];
< 13 or so assignment statements here >

}

also in your final fetch_assoc you have the var $row1 but all of your assignments follow are using $Row1, that won't work since variables are case sensitive

[coopster]

>>also in your final fetch_assoc you have the var $row1 but all of your
>>assignments follow are using $Row1, that won't work since variables are case sensitive

I didn't see that in the original chunk of code that was there...nice catch jatar_k.

>>*Or how do i reference the rows from the joined tables?

The same way you were before. The result set will still contain all the fields named as key=>value pairs when you use mysql_fetch_assoc to process the result set. It's just that they are now in one result set as opposed to three.

[theriddla1019]

Ok ive updated my sql queries using both the include style and another style I learned in college(had to look it up). Still not working here is my new sql statement followed by some variable population -
{
$Result = mysql_query ("Select * FROM poc, upininfo, patientdata
WHERE poc.POCID LIKE '$key' AND poc.MR =
patientdata.MR AND poc.UPIN = upininfo.UPIN");
$Row = mysql_fetch_assoc($Result, $Link);
$MR = $Row['MR'];
$PrimaryDx = $Row['PrimaryDx'];
$SurgicalDx = $Row['SurgicalDx'];
{More population of variables}
}
Else
{
$Result = mysql_query ("Select * FROM poc, upininfo, patientdata
WHERE poc.POCID LIKE " . $whereclause . $fullOrderBy . " LIMIT " . $itemNumber . ", 1"
AND poc.MR = patientdata.MR AND poc.UPIN = upininfo.UPIN");

$Row = mysql_fetch_assoc($Result, $Link);

$ProviderID = $Row['ProviderID'];
$MR = $Row['MR'];
{More variables populating}
}

[coopster]

What is the issue? I noticed you are now using a LIKE string comparison function. Are you sure your $key variable in your first query is correct? And in your second query the LIKE is followed by a variable named $whereclause. If it is indeed a WHERE clause, you may be getting an error message here.

Here is how I tend to troubleshoot SQL statements. First, use a variable to contain the entire SQL statement. Then, right before you execute the statement, exit the code and print the variable -- odds are you will be able to spot your issues quite quickly:

$sql = "Select * FROM poc, 
 upininfo, patientdata 
 WHERE poc.POCID LIKE '$key'  
 AND poc.MR = patientdata.MR AND poc.UPIN = upininfo.UPIN"; 
//exit($sql); // Uncomment this line for troubleshooting! 
$result = mysql_query($sql); 

Then if you feel you have it correct, you can cut and paste it to a MySQL command line interface and try to run it. MySQL will give you the results or tell you where the issue starts.

[theriddla1019]

Thanks Coop,
Between your suggestion and taking a good look through the code making sure my i's are dotted and t's are crossed i got it up and running.
Thanks again :)