multiple querries

[mmaserati]

I need to make multiple queries and the second and third rely on data retrieved from the first one. The 1st one finds a user in a table. If the user is there then we go on. However if they are not there all my other queries are failing because information is missing. I need to gracefully stop the sequential queries if the are not in the db. I am working internally and cant give a link but my code is below.

Thanks in advance.

//get the zip code from the form
 
 	$zipcode = ($_POST['zip']);
	echo "<br>Here are the results for zip code: <div class=seek>$zipcode</div><br> ";
	echo "<br><font color=#0072bc size= 3><b><u>Publisher Information</u></b></font><br><br>";
//select the db to use	
	$dbname = 'adnet';
	mysql_select_db($dbname);
	$query_publisher = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";

//echo"$query_publisher <br>";
	$result=mysql_query($query_publisher) or die ( "Couldn't execute query" );
	$num=mysql_numrows($result);

	if($num == 0 ){
  echo"</p><p>There are no Publishers using that zip code.</p>" ;

	else
}
//THIS IS WHERE I AM HAVING PROBLEMS

	$i=0;
	while ($i < $num) {
	
	$Publisher_id=mysql_result($result,$i,"pdata_id_publisher");
	$Owned_Zip=mysql_result($result,$i,"pdata_zipcodes");
	
//echo "<b>Publisher ID:</b>$Publisher_id<br><b>Zip Codes:</b>$Owned_Zip<br><br>";

	$i++;
	}
	
// Get the publishers details 
	$dbname = 'adnet';
	mysql_select_db($dbname);
	$query_publisher_details = "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d
	WHERE d.`id_publisher` = '$Publisher_id'";

//echo"$query_publisher_details <br>";
	$result=mysql_query($query_publisher_details) or die ( "Couldn't execute query" );
	$num=mysql_numrows($result);

	$i=0;
	while ($i < $num) {
	
	$name=mysql_result($result,$i,"contact");
	$phone=mysql_result($result,$i,"phone");
	$jobno=mysql_result($result,$i,"jobno");
	
echo "&nbsp;&nbsp;<b>Publisher:</b>$name<br>&nbsp;&nbsp;<b>Phone:</b>$phone<br>&nbsp;&nbsp;<b>Job No.</b>$jobno<br>";
	
	$i++;
	}

// Get the Publication details 
	$dbname = 'adnet2009';
	mysql_select_db($dbname);

	$query_publication = "SELECT d.community,d.code FROM db_publication_report d WHERE d.`zip` ='$zipcode' AND d.`id_publisher` = '$Publisher_id' and d.deleted is not null";

//echo"$query_publication <br>";
	$result=mysql_query($query_publication) or die ( "Couldn't execute query" );
	$num=mysql_numrows($result);

	$i=0;
	while ($i < $num) {
	
	$community=mysql_result($result,$i,"community");
	$code=mysql_result($result,$i,"code");
	
	echo "&nbsp;&nbsp;<b>Community:</b>$community<br>&nbsp;&nbsp;<b>Published Editions:</b>$code<br><br>";
	
	$i++;
	}

[Atli]

Hi.

To do that, you would check that there actually is any data available before you try to use it in the second query, using the the if construct.

For example:
[code=php]
// Assuming there is already an open MySQL connection.

$sql = "SELECT UserID FROM User WHERE something='keyw ord'";
$result = mysql_query($sq l) or die(mysql_error ());

// Check if there were any results
if(mysql_num_ro ws($result) > 0) {
$row = mysql_fetch_ass oc($result);
$userID = $row['USerID'];

$sql2 = "SELECT SomeStuff FROM SomeTable WHERE UserID = $userID";
$result2 = mysql_query($sq l2) or die(mysql_error ());

while($row = mysql_fetch_ass oc($result2)) {
echo $row['SomeStuff'] . PHP_EOL;
}
}
else {
// Print an error if no user is found.
echo "No user was found";
}[/code]

[mmaserati]

I took your advice but it seems to be erroring out when it tries to print out the fields just before the "no record found out put"

the error just says parse error no details.

//select the db to use

$dbname = 'adnet';
mysql_select_db($dbname);
$query_publisher = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";

//echo"$query_publisher <br>";
$result = mysql_query($query_publisher) or die("Couldn't execute query");
$row = mysql_fetch_assoc($result);


   $Publisher_id =  $row['pdata_id_publisher'];
   $Owned_Zip =  $row['pdata_zipcodes'];


//    echo "<b>Publisher ID:</b>$Publisher_id<br><b>Zip Codes:</b>$Owned_Zip<br><br>";



// Get the publishers details
$dbname = 'adnet';
mysql_select_db($dbname);

   $sql2 = "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d	WHERE d.`id_publisher` = '$Publisher_id'";
   $result2  = mysql_query($sql2) or die(mysql_error());
  
   while($row = mysql_fetch_assoc($result2)) {
     echo $row['contact'] . PHP_EOL;
   }
 }
 else {
   // Print an error if no user is found.
   echo "No user was found";
 }

[Atli]

It must give you some details, like the line number and file name?
What exactly does it say?

The problem, however, seems to be that you only have the else clause there. I can't see the if clause it goes with. Did you accidentally delete it or something?

[mmaserati]

here is exactly what I have written and the error message is:

Parse error: parse error in C:\Program Files\Apache Software Foundation\Apac he2.2\htdocs\Co py [2] of ron.php on line 45


thank you again....

<?php 

//update to reflect LIVE server
require_once('incs/conn.php'); ?>
</head>

<body>
<?php


//get the zip code from the form

$zipcode = ($_POST['zip']);
echo "<br>Here are the results for zip code: <div class=seek>$zipcode</div><br> ";
echo "<br><font color=#0072bc size= 3><b><u>Publisher Information</u></b></font><br><br>";
//select the db to use

$dbname = 'adnet';
mysql_select_db($dbname);
$query_publisher = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";

//echo"$query_publisher <br>";
$result = mysql_query($query_publisher) or die("Couldn't execute query");
$row = mysql_fetch_assoc($result);


   $Publisher_id =  $row['pdata_id_publisher'];
   $Owned_Zip =  $row['pdata_zipcodes'];


//    echo "<b>Publisher ID:</b>$Publisher_id<br><b>Zip Codes:</b>$Owned_Zip<br><br>";



// Get the publishers details
$dbname = 'adnet';
mysql_select_db($dbname);

   $sql2 = "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d	WHERE d.`id_publisher` = '$Publisher_id'";
   $result2  = mysql_query($sql2) or die(mysql_error());
  
   while($row = mysql_fetch_assoc($result2)) {
     echo $row['phone'] . PHP_EOL;
   }
 }
 else {
   // Print an error if no user is found.
   echo "No user was found";
 }

  




/*
$query_publisher_details =
    "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d
	WHERE d.`id_publisher` = '$Publisher_id'";

//echo"$query_publisher_details <br>";
$result = mysql_query($query_publisher_details) or die("Couldn't execute query");
$num = mysql_numrows($result);

$i = 0;
while ($i < $num) {

    $name = mysql_result($result, $i, "contact");
    $phone = mysql_result($result, $i, "phone");
    $jobno = mysql_result($result, $i, "jobno");

    echo "&nbsp;&nbsp;<b>Publisher:</b>$name<br>&nbsp;&nbsp;<b>Phone:</b>$phone<br>&nbsp;&nbsp;<b>Job No.</b>$jobno<br><br>";

    $i++;
}

// Get the Calendars details
$dbname = 'adnet2009';
mysql_select_db($dbname);

$query_publication =
    "SELECT d.community,d.code FROM db_publication_report d WHERE d.`zip` LIKE '%$zipcode%' AND d.`id_publisher` = '$Publisher_id' and d.deleted is not null";

//echo"$query_publication <br>";
$result = mysql_query($query_publication) or die("Couldn't execute query");
$num = mysql_numrows($result);

$i = 0;
while ($i < $num) {

    $community = mysql_result($result, $i, "community");
    $code = mysql_result($result, $i, "code");

    echo "&nbsp;&nbsp;<b>Community:</b>$community<br>&nbsp;&nbsp;<b>Published Editions:</b>$code<br><br>";

    $i++;
}

*/
?>
<div class="search"><br><strong>Search Again</strong>
<form name="form1" method="post" action="ron.php">
  <label>
  <input name="zip" type="text" id="zip" maxlength="5">
  </label>
  <label>
  <input type="submit" name="button" id="button" value="Submit">
  </label>
</form></div>

</body>
</html>

[hoopy]

You have a } else { but no if previous so the "}" on line 45 is unexpected.

[Atli]

Yea, hoopy is right.

It should be: if(something) { ... } else { ... }
Not just: } else { ... }

Check out my previous example again. That's how it should look.

[mmaserati]

added another querry

thanks that worked..

I broke it by adding a 3RD Querry though.

I wasnt sure about the WHILE part??

do you possibly have a chance to take a second look?

Parse error: parse error in C:\Program Files\Apache Software Foundation\Apac he2.2\htdocs\ro n.php on line 95

<?php 

//update to reflect LIVE server
require_once('incs/conn.php'); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Zip Code Results</title>
<style type="text/css">
<!--
body,td,th {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
	color: #000066;
}
body {
	background-color: #CCCCCC;
}
.search {
	width: 300px;
	left: 100px;
	position: absolute;
}
.seek {
	background-color: #FFFFFF;
	font-weight: bold;
	font-variant: small-caps;
	width: 100px;
	display: inline;
}
-->
</style>
</head>

<body>
<?php


//get the zip code from the form
if ($_POST['zip'] > '') {
$zipcode = ($_POST['zip']);
}
echo "<br>Here are the results for zip code: <div class=seek>$zipcode</div><br> ";
echo "<br><font color=#0072bc size= 3><b><u>Publisher Information</u></b></font><br><br>";
//Find the publishers using that zipcode
$dbname = 'adnet';
mysql_select_db($dbname);
$query_publisher = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";

//echo"$query_publisher <br>";
$result = mysql_query($query_publisher) or die("Couldn't execute query");
if(mysql_num_rows($result) > 0) {
$row = mysql_fetch_assoc($result);
$Publisher_id =  $row['pdata_id_publisher'];
$Owned_Zip =  $row['pdata_zipcodes'];


//    echo "<b>Publisher ID:</b>$Publisher_id<br><b>Zip Codes:</b>$Owned_Zip<br><br>";



// Get the publishers details
$dbname = 'adnet';
mysql_select_db($dbname);

   $sql2 = "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d	WHERE d.`id_publisher` = '$Publisher_id'";
   $result2  = mysql_query($sql2) or die(mysql_error());
  
   while($row = mysql_fetch_assoc($result2)) {
    
	$contact=$row['contact'];
	$phone=$row['phone'];
	$jobno=$row['jobno'];
	
	
echo "&nbsp;&nbsp;<b>Publisher:</b>$name<br>&nbsp;&nbsp;<b>Phone:</b>$phone<br>&nbsp;&nbsp;<b>Job No.</b>$jobno<br><br>";
   }
 }


// Get the Calendars details
$dbname = 'adnet2009';
mysql_select_db($dbname);

$sql3 ="SELECT d.community,d.code,d.deleted  FROM db_publication_report d WHERE d.`zip` LIKE '%$zipcode%' AND d.`id_publisher` = '$Publisher_id'";
$result3 = mysql_query($sql3) or die(mysql_error());
if(mysql_num_rows($result) > 0) {
 while($row = mysql_fetch_assoc($result3)) {

	
	$community = $row['community'];
    $code = $row['code'];
	$deleted $row['deleted'];
	
	if ($deleted == 1){
	$deleted = "Inactive";
	}
	
	if ($deleted == ''){
	$deleted = "Active";
	}
    
	echo "&nbsp;&nbsp;<b>Community:</b>$community<br>&nbsp;&nbsp;<b>Published Editions:</b>$code<br>&nbsp;&nbsp;<b>Status:</b>$deleted<br><br>";
   }
 }
 else {
// Print an error if no user is found.
   echo "No Publisher was found in that zipcode";
 }
?>
<div class="search"><br><strong>Search Again</strong>
<form name="form1" method="post" action="ron.php">
  <label>
  <input name="zip" type="text" id="zip" maxlength="5">
  </label>
  <label>
  <input type="submit" name="button" id="button" value="Submit">
  </label>
</form></div>

</body>
</html>

[hoopy]

  $sql3 ="SELECT d.community,d.code,d.deleted  FROM db_publication_report d " . 
    "WHERE d.`zip` LIKE '%$zipcode%' AND d.`id_publisher` = '$Publisher_id'";
  $result3 = mysql_query($sql3) or die(mysql_error());
  if(mysql_num_rows($result) > 0) 
  {
    while($row = mysql_fetch_assoc($result3)) 
    {
      $community = $row['community'];
      $code = $row['code'];
      $deleted $row['deleted'];
      if ($deleted == 1){
        $deleted = "Inactive";
      }
      if ($deleted == ''){
        $deleted = "Active";
      }
      echo "&nbsp;&nbsp;<b>Community:</b>$community<br>&nbsp;&nbsp;<b>" . 
      "Published Editions:</b>$code<br>&nbsp;&nbsp;<b>Status:</b>$deleted<br><br>";
      }
    }
  } else {
    // Print an error if no user is found.
    echo "No Publisher was found in that zipcode";
  }

Close the While as above. Try and use indentation when dealing with loops or conditional statements as its easier to see whats going on.

Cheers.

[mmaserati]

I am wondering if there is a way to include the 3rd query in that first if statement.

Because the 2nd and 3rd query both need the publisher id it seemed to work with 2 but like I said a 3rd it breaks. I used the above code but still got an error.

BTW the indent is a lot easier to read, thanks for that tip ;o)

[hoopy]

This code has no syntax errors: you did have a couple of bugs in that previous code you had pasted. Anyway see if this works:

<?php  
require_once('incs/conn.php'); 
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Zip Code Results</title>
<style type="text/css">
<!--
body,td,th {
    font-family: Verdana, Arial, Helvetica, sans-serif;
    font-size: 12px;
    color: #000066;
}
body {
    background-color: #CCCCCC;
}
.search {
    width: 300px;
    left: 100px;
    position: absolute;
}
.seek {
    background-color: #FFFFFF;
    font-weight: bold;
    font-variant: small-caps;
    width: 100px;
    display: inline;
}
-->
</style>
</head>
 
<body>
<?php
 
if ($_POST['zip'] > '') {
  $zipcode = ($_POST['zip']);
}
echo "<br>Here are the results for zip code: <div class=seek>$zipcode</div><br>
  <br><font color=#0072bc size= 3><b><u>Publisher Information</u></b></font><br><br>";

$dbname = 'adnet';
mysql_select_db($dbname);
$query_publisher = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` 
  FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";

$result = mysql_query($query_publisher) 
  or die("Couldn't execute query");
if(mysql_num_rows($result) > 0) 
{
  $row = mysql_fetch_assoc($result);
  $Publisher_id =  $row['pdata_id_publisher'];
  $Owned_Zip =  $row['pdata_zipcodes'];
  
  $dbname = 'adnet';
  mysql_select_db($dbname);
 
  $sql2 = "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d  
    WHERE d.`id_publisher` = '$Publisher_id'";
  $result2  = mysql_query($sql2) or die(mysql_error());
 
  while($row = mysql_fetch_assoc($result2)) 
  {
    $contact=$row['contact'];
    $phone=$row['phone'];
    $jobno=$row['jobno'];
 
    echo "&nbsp;&nbsp;<b>Publisher:</b>$name<br>&nbsp;&nbsp;<b> 
      Phone:</b>$phone<br>&nbsp;&nbsp;<b>Job No.</b>$jobno<br><br>";
  }
}
 
 
// Get the Calendars details
$dbname = 'adnet2009';
mysql_select_db($dbname);
 
$sql3 ="SELECT d.community,d.code,d.deleted  FROM db_publication_report d 
  WHERE d.`zip` LIKE '%$zipcode%' AND d.`id_publisher` = '$Publisher_id'";
$result3 = mysql_query($sql3) or die(mysql_error());
if(mysql_num_rows($result) > 0) 
{
  while($row = mysql_fetch_assoc($result3)) 
  {
    $community = $row['community'];
    $code = $row['code'];
    $deleted = $row['deleted'];

    if ($deleted == 1){
    $deleted = "Inactive";
    }

    if ($deleted == ''){
    $deleted = "Active";
    }

    echo "&nbsp;&nbsp;<b>Community:</b>$community<br>&nbsp;&nbsp;<b>Published 
      Editions:</b>$code<br>&nbsp;&nbsp;<b>Status:</b>$deleted<br><br>";
  }
} else {
  // Print an error if no user is found.
  echo "No Publisher was found in that zipcode";
}
?>
<div class="search"><br><strong>Search Again</strong>
<form name="form1" method="post" action="ron.php">
<label>
<input name="zip" type="text" id="zip" maxlength="5">
</label>
<label>
<input type="submit" name="button" id="button" value="Submit">
</label>
</form></div>

</body>
</html>

[mmaserati]

for some reason it cant find a variable "$Publisher _id" to run the last querry.
I think it looks for on in the first query and if it doesnt find one it prints out
No publisher was found


the error now is:


Notice: Undefined variable: Publisher_id in C:\Program Files\Apache Software Foundation\Apac he2.2\htdocs\ro n.php on line 89

[hoopy]

Can you give me an SQL dump of how your database is laid out and I will have a look at it.

Cheers.

[mmaserati]

I didn't build the DB just inherited them. I would redesign the table layouts but that is for a total rewrite.



This is what I am trying to accomplish.

the user to type in a zip code through a web form to see if any publisher owns the zip code. If a match is found, the results will return the job number, contact name, phone number, status (active or inactive) of the publisher(s) owning the code. It should also display the calendar edition(s) covered by the zip code, and whether the edition(s) are active or inactive. If no match is found it will return a message saying that the zip code is available.

[hoopy]

OK this dump only has the table: db_publication_ report none of the other tables.

Do you the publisher one (db_publishers) as well?