[RESOLVED] multiple queries

[roscor]

Hi I have a relational database as such

tenant_details
| id | surname | firstname | etc...................

tenant_data
| id | tenant_detailsid | county | etc...............

I display the results using

include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tenant_data WHERE county='$county' ORDER by date DESC";
$result=mysql_query($query);
$num=mysql_num_rows($result);
if ($num==0) {
$empty = "No Matching";
}
 else {
$num_rows = mysql_num_rows($result);
}



$bg_color1 = '#EEEEEE';
$bg_color2 = '#FFFFFFF';


$i = 0;

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

$bg_color = (($i%2 == 0 || $i == 0) ? $bg_color1 : $bg_color2);

$tenant_detailsid = $row["tenant_detailsid"];  

// further rows etc
?>
<form>
<td><input type="hidden" size="150" value="<? echo $tenant_detailsid; ?>" name="id"><? echo "<td bgcolor=$bg_color align='center'><input type='submit' value='$tenant_detailsid'></td>"; ?></form>
<?
$i++;
}
echo "</table>";
?>

This gets the results from the database as required.
I have been trying to use a second query using the tenant_detailsid to get the surname and firstname from the table tenant_details. I then want to display the details within the submit button as such
<input type='submit' value='$first $last'>.

I have tried a second query and have been looking through the forum for the last two days trying different code etc with little joy. any help is much appreciated,

thanks in advance roscor

[Piranha]

It is much better to get all the information in one query. I have changed a few things in your code, not that I haven't tried it so there may be one or two small bugs.

include("dbinfo.inc.php");
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT tenant_data.tenant_detailsid, tenant_details.firstname, tenant_details.surname 
			FROM tenant_data 
			LEFT JOIN tenant_details ON tenant_data.tenant_detailsid = tenant_details.id 
			WHERE county='$county' 
			ORDER by date DESC";

$result=mysql_query($query);
$num=mysql_num_rows($result);
if ($num==0) 
{
	$empty = "No Matching";
}
else 
{
	$num_rows = mysql_num_rows($result);
}



$bg_color1 = '#EEEEEE';
$bg_color2 = '#FFFFFF';


$i = 0;

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

$bg_color = (($i%2 == 0 || $i == 0) ? $bg_color1 : $bg_color2);

$tenant_detailsid = $row["tenant_detailsid"];  
// further rows etc
?>
	<form><td><input type="hidden" size="150" value="<?php echo $tenant_detailsid; ?>" name="id">
<?php 
	echo "<td bgcolor=$bg_color align='center'><input type='submit' value='$firstname $surname'></td>"; 
?>
	</form>
<?php
$i++;
}
echo "</table>";
?> 

Note that I have changed a few other things as well. First I have selected only what is needed from the query, it will speed the query up a bit. Second I changed $bg_color2. Third I changed all <? to <?php. Depending on the server settings <? may or may not work. And it is possible that it won't be supported in future versions, that's why <?php should always be used.

But there is one thing I don't understand. What will the submit button do? There is no action set to the form.

[roscor]

many thanks for the reply Piranha,

I was experimenting with several queries one being the approach you have gone along, I have not tried the code yet.

I do use <? at present but will take your advice and change to <?PHP.

The results are shown in a table where the user can then select the appropiate record, basicaly, first search by county which displays all records, the names then displayed in a submit button which when sellected will dislplay a full record on that person. The form has no action in the post I just added it in my post for clarity, my own code has action and method included.

many thanks roscor

[roscor]

I tried thee code and got the error
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource ....
where the line is
$num=mysql_num_rows($result);
and also
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
where the line is
while ($row = mysql_fetch_array($result)) {

I will keep trying

[Piranha]

I haven't changed anything that can result in such errors except maybe the query. On the development server try this to spot problems:

$result=mysql_query($query) or die(mysql_error());

Oh, and look at [man]mysql_real_escape_string[/man] to avoid database injection.

[roscor]

I now realise where the problem is, the error showed -
'Column 'date' in order clause is ambiguous'

I took out ORDER BY DATE DESC and it sort of worked, in the query i need to display with date DESC with the most current record being displayed first, How do i include that in the query as I have little idea ?

[roscor]

sorted it,

$query="SELECT tenant_data.tenant_detailsid, tenant_details.firstname, tenant_details.surname, tenant_details.date
            FROM tenant_data
            LEFT JOIN tenant_details ON tenant_data.tenant_detailsid = tenant_details.id
            WHERE county='$county'
            ORDER by date DESC";

I just had to look at how the query worked, thanks Piranha