PHP Code help please

**Phoenix** · 10th September 2009, 03:43 PM

Ok, i'm trying to sort out the REMLR membership page.

I can get all of the info to display here

Member summary list - REMLR

and this is the code

<?php
$hostname="";
$username="";
$password="";
$dbname="richar3_remlr";
//connect to the database
$dbhandle = mysql_connect($hostname, $username, $password)or die("Unable to connect to MySQL");
//select a database to work with
$selected = mysql_select_db($dbname,$dbhandle) or die("Could not select examples");
$result = mysql_query("SELECT membership.membership_no, membership.name_public, membership.state, arn.ARN, census.census_short, arn.vehicle_name"
. " FROM arn INNER JOIN"
. " mem2arn ON arn.ARN = mem2arn.ARN INNER JOIN"
. " census ON arn.census = census.census RIGHT OUTER JOIN"
. " membership ON mem2arn.membership_no = membership.membership_no");
//fetch tha data from the database
echo "<center><table width=600 border=0 cellspacing=8 cellpadding=0>";
while ($row = mysql_fetch_array($result)) {echo "
<tr bgcolor=#CCCCCC>
<td colspan=4>REMLR No. ".$row{'membership_no'}."
</td>
</TR>
<TR>
<td colspan=4>".$row{'name_public'}."
</td>
</TR>
<tr>
<td width=150> 
</td>
<td>".$row{'ARN'}."
</td>
<td> -
</td>
<td> ".$row{'census_short'}. "
</td>
</tr>
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>

however it produces one entry per member. what I want to do is produce one member, and show each of their vehicles in a list, then go to thenext member.

I know I need another nested statement in there, but I can't seem to get it to work.

**Shonky** · 10th September 2009, 03:48 PM

"Boys, get daddy's exorcism bag..."

Ned Flanders

Greeny's posessed - he's talkin' in tongues!

mikehzz · 10th September 2009, 06:44 PM

Hello,

I think you need 2 queries instead of the 1 with the join. Loop through the members, and create the new query by fetching the cars for that member

Something like-

Loop1 from member list (select * from members)
print member
loop2 get car list for the member (select * from cars where member=".$row["memberid"])
print cars
end of loop2
end of loop1

Does this help?

Mike

**Phoenix** · 11th September 2009, 07:55 AM

Shonky,

. this is what i have to do to make these pages work. Not as easy as it appears on the outside!

Mike, I was trying to avoid that because it generates a lot more statements, so instead of one select statement, it will create 300ish. I know I can do it that way, just trying to avoid it.

**josh aka sean** · 11th September 2009, 08:13 AM

Can you give a list of the fields for each table from the database (just the relevant tables) just so that i can see what the links are (not visualising it very well at the time of the morning.

Cheers
Josh

**Phoenix** · 11th September 2009, 08:26 AM

No problems. the four tables involved are

arn, census, membership and mem2arn

arn has all the vehicle information and census code

census has census code and nomenaclature information

mem2arn has a list of arn's corresponding to member numbers

Membership has members name and accress and member no etc.

arn has:
arn, wheelbase, date, contract, series, census, engine, chassis, body, notes, disposal, tiresize, vehicle_name, service

census has:
census, nomenaclature, liability, notes, census short, type

mem2arn has:
membership_no, arn

Membership has:
membership_no, name, name_public, state, address, email, AULRO, phone, mobile, dob

in case anybody is looking, DOB is a new one to try and keep, so that we can reasonably presume a member has passed away if over 100 years. a good idea from a helpful REMLR member).

**josh aka sean** · 11th September 2009, 08:50 AM

On pondering it i think Mikes idea is the way to go. I don't quite understand what you mean when you said

Originally Posted by Phoenix

Mike, I was trying to avoid that because it generates a lot more statements, so instead of one select statement, it will create 300ish.

**Phoenix** · 11th September 2009, 09:20 AM

what I mean is, that it does another select statement for each member that it comes to, but at the moment there are already 300 members, or am I being overly pedantic about how many select statements are called?

**josh aka sean** · 11th September 2009, 09:34 AM

It will only require two queries that it will loop over and over but processing time should be quite small.

**josh aka sean** · 11th September 2009, 09:53 AM

Just so that i can make sure we're on the same page here is your code changed round a bit to do the 2 query 2 loop way.

<?php
$hostname="";
$username="";
$password="";
$dbname="richar3_remlr";
//connect to the database
$dbhandle = mysql_connect($hostname, $username, $password)or die("Unable to connect to MySQL");
//select a database to work with
$selected = mysql_select_db($dbname,$dbhandle) or die("Could not select examples");

THIS QUERY WILL GO TO SOMETHING SIMPLE LIKE

$result = mysql_query("SELECT membership.membership_no, membership.name_public, membership.state,
FROM membership);

THUS MAKING THIS QUERY NOT NEEDED
$result = mysql_query("SELECT membership.membership_no, membership.name_public, membership.state, arn.ARN, census.census_short, arn.vehicle_name"
. " FROM arn INNER JOIN"
. " mem2arn ON arn.ARN = mem2arn.ARN INNER JOIN"
. " census ON arn.census = census.census RIGHT OUTER JOIN"
. " membership ON mem2arn.membership_no = membership.membership_no");

//fetch tha data from the database
echo "<center><table width=600 border=0 cellspacing=8 cellpadding=0>";
while ($row = mysql_fetch_array($result)) {echo "
<tr bgcolor=#CCCCCC>
<td colspan=4>REMLR No. ".$row{'membership_no'}."
</td>
</TR>
<TR>
<td colspan=4>".$row{'name_public'}."
</td>
</TR>
ADD SECOND QUERY HERE
SOMTHING LIKE

$secondResult = mysql_query("SELECT arn.ARN, census.census_short, arn.vehicle_name"
. "FROM (all the fun join parts )"
."WHERE mem2arn.membership_no = " . $row{membership_no});

THEN ADD A SECOND WHILE
while ($secondRow = mysql_fetch_array($secondResult)) {echo

<tr>
<td width=150> 
</td>
<td>".$secondRow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondRow{'census_short'}. "

THEN ADD A CLOSE OF LOOP HERE ie }

</td>
</tr>
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>

It needs a bit of fixing up but think the general principles are their for it.

Thread: PHP Code help please

Thread Tools

PHP Code help please

Bookmarks

Bookmarks

Posting Permissions