select query and while loop

amar3d

Ok, i have a form with a select query and while loop

$query1 = "SELECT * FROM members WHERE customer_id='$cid' ";
$result1 = mysql_query($query1);
$num = mysql_num_rows($result1);

while ($row = mysql_fetch_array($result1)) {
$email = $row['email'];
$title = $row['title'];
$first_name = $row['first_name'];
$second_name = $row['second_name'];
$postcode = $row['postcode'];
}

now within this loop i wish to put in anther query which allows me to view a post-codes table in the databse;

$query2 = "SELECT * FROM postcode WHERE postcode='$postcode' ";

does anyone know how i can insert this so that the other query runs also?

If u wish me to post anymore code i can do so, thanks in advance

-Amar

Crave

Try this:

$query1 = "select members.col1, members.col2, members.col3, postcode.col1, postcode.col2 from members inner join postcode on members.postcode = postcode.postcode where members.customer_id = '$cid'";

Where members.col1, members.col2, etc are the coloums for the members table you want to show, and postcode.col1, postcode.col2 are the coloums from the postal code table that you want.

In the Postcode table, is each postal code in there only once?

amar3d

Hi, thank you for the reply, i tried your way and go the following error messages;

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\CustomerUpdate\update_customer_form.php on line 19

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\CustomerUpdate\update_customer_form.php on line 23

I've posted all my code below have i input the method u said in the right way?.(oh and the post code does only appear in the DB once)-Thanks for your help so far

<html><head><title></title></head>
<body>
<?
$cid=$_POST['customer_id'];
$db="test";
//mysql_connect(localhost);
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");

mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());

$query1 = "select members.email, members.title, members.first_name, members.postcode, postcode.street
from members inner join postcode on members.postcode = postcode.postcode where members.customer_id = '$cid'";

$result1 = mysql_query($query1);
$num = mysql_num_rows($result1);
while ($row = mysql_fetch_array($result1)) {
$email = $row['email'];
$title = $row['title'];
$first_name = $row['first_name'];
$second_name = $row['second_name'];
$house_number = $row['house_number'];
$membership_valid = $row['membership_valid'];
$password1 = $row['password1'];

$notes = $row['notes'];
$postcode = $row['postcode'];
$street= $row['postcode'];
}

<TABLE cellSpacing=0 cellPadding=0 width=400 border=0>
<form action="update_customer.php" method="post">
<input type="hidden" name="cud_id" value="<? print "$cid"?>">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<tr>
<td width="100%">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" height="1">
<tr>
<td width="26%" nowrap height="25" colspan="2">E-mail:               
<input name=email size="50" value="<? print $email ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=email></td>
<td width="74%" nowrap height="25">   </td>
</tr>
<tr>
<td width="2%" nowrap height="30">Title                   
<SELECT
style="FONT-SIZE: 11px; WIDTH: 50; HEIGHT: 50"
input name=title value="<? echo "$title" ?>">
<OPTION value=""
selected></OPTION>
<OPTION value="Mr"
selected>Mr</OPTION>
<OPTION
value=Mrs>Mrs</OPTION>
<OPTION
value=Ms>Ms</OPTION>
<option value="Miss">Miss</option>
</SELECT>   </td>
<td width="24%" nowrap height="30"> </td>
<td width="74%" nowrap height="30"> </td>
</tr>
<tr>
<td width="11%" nowrap height="27">First Name         <input name=first_name size="20" value="<? print "$first_name" ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=cv2></td>
<td width="15%" nowrap height="27">  Second Name <input name=second_name size="20" value="<? print "$second_name" ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=cv2></td>
<td width="74%" nowrap height="27"> </td>
</tr>
<tr>
<td width="26%" nowrap height="30" colspan="2">House Number: 
<input name=house_number size="15" value="<? echo "$house_number" ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=house_number></td>
<td width="74%" nowrap height="30"> </td>
</tr>
<tr>
<td width="26%" nowrap height="28" colspan="2">Street:               
<input name=street size="20" value="<? print "$street" ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=street></td>
<td width="74%" nowrap height="56" rowspan="2"> </td>
</tr>
<tr>
<td width="10%" nowrap height="29"> Town:               <input name=town size="20"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
input name=town></td>
<td width="16%" nowrap height="29">   Post Code:    
<input name=post_code size="20" value="<? print "$post_code" ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
input name=postcode></td>
</tr>
<tr>
<td width="17%" nowrap height="21"> </td>
<td width="83%" nowrap height="21" colspan="2"> </td>
</tr>
</table>
</td>
</tr>
</table>

<p><font size="4" color="#FF0000">Membership Details</font></p>

<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<tr>
<td width="74%" nowrap height="30">    Password:             <input name=password1 size="15"
value="<? print "$password1" ?>"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=password1></td>>
</tr>
<tr>
<td width="74%" nowrap height="27">    Membership valid:
<SELECT
style="FONT-SIZE: 11px; WIDTH: 50; HEIGHT: 50"
input name=membership_valid value="<? print "$membership_valid" ?>">
<OPTION value=""
selected></OPTION>
<OPTION value="Yes"
selected>Yes</OPTION>
<OPTION
value=No>No</OPTION>
</SELECT></td>
</tr>
<tr>
<td width="100%">    Notes                   <input name=notes size="3"
style="FONT-SIZE: 11px; WIDTH: 90px" size=15
name=notes value="<? print "$notes" ?>"></td>
</tr>
</table>

</body>
</html>

Crave

I tried that exact code, only modifying it to fit my test database (which is MS Sql, not MySQL).
The code excuted properly, with no errors about mssql_num_rows. I noticed that you assign the $num variable to it, yet you don't use that variable on the page, so perhaps you can just delete line 19. As for the other error, you need to check that the connection to the MySQL database is working properly, it looks like you need to remove the
//
from the line:
//mysql_connect(localhost);

Also, you use the variable $post_code once instead of $postcode on line 84.

What table are the house_number and town fields in?

amar3d

Hey, thanks for the help, u;ve put me on the right track and the form is now working....cheers.

-Amar