PDA

View Full Version : 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 (http://www.remlr.com/remlrchassis.php)

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>&nbsp;
</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! :o
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, :p . 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




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>&nbsp;
</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.
Phoenix
11th September 2009, 10:04 AM
that looks like a much better start than what I had.

I'll give it a whirl and let you all know how I go.
Phoenix
11th September 2009, 11:03 AM
ok, I am getting a 500 internal server error, so most likely i've got a syntax problem.

The code as it stands (the trial is online at remlrchassis2 and 3)

//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"
. " FROM membership ");
//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>
$result2 = mysql_query("SELECT 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"
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " ORDER BY arn.ARN");
while ($row = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</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>";
?>
josh aka sean
11th September 2009, 11:19 AM
Yeah missing " and . i've added them in in red (hopefully all of them)


ok, I am getting a 500 internal server error, so most likely i've got a syntax problem.

The code as it stands (the trial is online at remlrchassis2 and 3)

//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT 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"
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " ORDER BY arn.ARN");
while ($row = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$row{'ARN'}."
</td>
<td> -
</td>
<td> ".$row{'census_short'}. "
</td>
</tr>";
}
echo "
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>


Those changes should fix it
josh aka sean
11th September 2009, 11:28 AM
btw these changes (in red again) also need to be made otherwise you will change the value of $row and not be able to write $row(state)


Yeah missing " and . i've added them in in red (hopefully all of them)


ok, I am getting a 500 internal server error, so most likely i've got a syntax problem.

The code as it stands (the trial is online at remlrchassis2 and 3)

//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"
. " FROM membership ");
//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>"
$result2 = mysql_query("SELECT 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"
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " ORDER BY arn.ARN");
while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>
Phoenix
11th September 2009, 12:08 PM
Josh aka sean, many thanks for your help with this :D :D :thumbsup:

Ok, the page loads correctly now :D

Member summary list - REMLR (http://www.remlr.com/remlrchassis3.php)

Names load up :D

but no vehicles :(

Current code

//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT 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"
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " ORDER BY arn.ARN");
while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
echo"
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>
josh aka sean
11th September 2009, 12:23 PM
Hi no probs for the help. Can you add the line of code in red just to check if results are being returned by the second query.


Josh aka sean, many thanks for your help with this :D :D :thumbsup:

Ok, the page loads correctly now :D

Member summary list - REMLR (http://www.remlr.com/remlrchassis3.php)

Names load up :D

but no vehicles :(

Current code

//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT 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"
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " ORDER BY arn.ARN");

echo "number of rows " . mysql_num_rows($result2);

while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
echo"
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>
Phoenix
11th September 2009, 12:33 PM
done

Member summary list - REMLR (http://www.remlr.com/remlrchassis3.php)

lots of number of rows, but that's it.
josh aka sean
11th September 2009, 01:27 PM
Lol yeah sorry put the code in the wrong spot. 2 Thing this time 1st one is to put the code in the right spot and second is a change to the second query as a test so keep the original sql for it.


//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT arn.ARN, arn.vehicle_name"
. " FROM arn, mem2arn.membership
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " AND mem2arn.ARN = arn.ARN"
. " ORDER BY arn.ARN");



while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
echo"
<TR>
<td colspan=4 align=right>".$row{'state'}." . "number of rows " . mysql_num_rows($result2);
</td>
</TR>";
}
echo "</table></center>";
?>
Phoenix
11th September 2009, 01:44 PM
Mmm, no worky....

//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT arn.ARN, arn.vehicle_name"
. " FROM arn, mem2arn.membership
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " AND mem2arn.ARN = arn.ARN"
. " ORDER BY arn.ARN");
while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
echo"
<TR>
<td colspan=4 align=right>".$row{'state'}.". "number of rows " . mysql_num_rows($result2);
</td>
</TR>";
}
echo "</table></center>";
?>
josh aka sean
11th September 2009, 01:49 PM
Yeah sorry try the updated red part (silly " again haha)


Mmm, no worky....

//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT arn.ARN, arn.vehicle_name"
. " FROM arn, mem2arn.membership
. " WHERE mem2arn.membership_no = $row{membership_no}"
. " AND mem2arn.ARN = arn.ARN"
. " ORDER BY arn.ARN");
while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
echo"
<TR>
<td colspan=4 align=right>".$row{'state'}. " number of rows " . mysql_num_rows($result2) . "
</td>
</TR>";
}
echo "</table></center>";
?>
Phoenix
11th September 2009, 02:06 PM
they are a pain aren't they!!!

http 500 error now :( DOH!!
josh aka sean
11th September 2009, 02:40 PM
Think i got the fix for it all hahaha (famous last words) once again silly " go back to the code from a while back (should just be copy and paste and see the fix in red.

Cheers
Josh




//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"
. " FROM membership ");
//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>";
$result2 = mysql_query("SELECT 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"
. " WHERE mem2arn.membership_no = " . $row{membership_no}
. " ORDER BY arn.ARN");
while ($secondrow = mysql_fetch_array($result2)) {echo "
<tr>
<td width=150>&nbsp;
</td>
<td>".$secondrow{'ARN'}."
</td>
<td> -
</td>
<td> ".$secondrow{'census_short'}. "
</td>
</tr>";
}
echo"
<TR>
<td colspan=4 align=right>".$row{'state'}."
</td>
</TR>";
}
echo "</table></center>";
?>
Phoenix
11th September 2009, 03:32 PM
You are a LEGEND my friend, it works!!!!!!!!!!!!!
josh aka sean
11th September 2009, 03:34 PM
No problems a pleasure to help
Shonky
11th September 2009, 04:11 PM
No problems a pleasure to help

I have let the thanks button do the talking. :)

Good on you for pitching in! ;)
mikehzz
12th September 2009, 07:27 PM
Sorry, I have been away all day so couldn't follow up on my first post.
Well done Josh, thats exactly what I meant. Thanks for taking the time to untangle the queries.

Mike