• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

multiple query and explode using PHP / MySQL

I am trying to write a form result where a person enters a zipcode and it queries a db and finds out what area a publisher is publishing in and what areas he is licensed to publish in and if no one is pub;lishing in that area to say it is available. I seem to have the looping wrong as I only get the results for one publisher and I cant the exploded part to be used in the 3 query.

The 1st Query is to get the zip codes and publisher id based on a zipcode submitted

The 2nd Query takes the id and gets the name, Phone and job number for each publisher

The 3rd query takes a look at a license table to see if the published zip code (from the 1st query) and make sure they are in there or not

The explode part is what I thought I need to use to make the 3rd query. All zipcodes are stored in a field and are comma separated.
$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>";
$Publisher_id= '';
$Owned_Zip= '';
 
//Get the Pulishers ID , Zip Codes they own
$dbname = 'adnet';
mysql_select_db($dbname);
$sql = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";
// echo"$sql<br><br>";
$result = mysql_query($sql) or die(mysql_error());
 
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $row = mysql_fetch_assoc($result);
    //process result row
  
  $Publisher_id =  $row['pdata_id_publisher'];
  $Owned_Zip =  $row['pdata_zipcodes'];
    
 
	
//print_r (explode(" ",$Owned_Zip));
 
$ziparray=(explode(" ",$Owned_Zip));
	
}	
//Get Publishers Name, Phone and Job Number
$dbname = 'adnet';
mysql_select_db($dbname);
$sql2 = "SELECT d.`jobno`, d.`contact`, d.`phone` FROM db_publishers d  
    WHERE d.`id_publisher` = '$Publisher_id'";
 echo"<br>$sql2<br><br>";
$result2  = mysql_query($sql2) or die(mysql_error());
 
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $row2 = mysql_fetch_assoc($result);
    //process result row
	
    $contact=$row2['contact'];
    $phone=$row2['phone'];
    $jobno=$row2['jobno'];
 
    echo "<br>License Info:&nbsp;&nbsp;<b>Publisher:&nbsp;</b>$contact<br>&nbsp;&nbsp;&nbsp;&nbsp;<b>Zip Codes Owned:&nbsp;</b>$Owned_Zip<br><b>Phone:&nbsp;</b>$phone<br>&nbsp;&nbsp;<b>Job No.:&nbsp;</b>$jobno<br><br>";
  }
 
// Get the Calendars details
$dbname = 'adnet2010';
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'";
echo"<br>$sql3<br><br>";
$result3 = mysql_query($sql3) or die(mysql_error());
 
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    $row3 = mysql_fetch_assoc($result);
    //process result row
	
    $community = $row3['community'];
    $code = $row3['code'];
    $deleted = $row3['deleted'];
	$zipcode = $row3['zipcode'];
 
    if ($deleted == 1){
    $deleted = "Inactive";
    }
 
    if ($deleted == ''){
    $deleted = "Active";
    }
 
    echo "Publishing Info:&nbsp;&nbsp;<b>Community:</b>$community<br>&nbsp;&nbsp;<b>Published 
      Editions:</b>$code<br>&nbsp;&nbsp;<b>Status:</b>$deleted<br>&nbsp;&nbsp;<b>Zip Codes:</b>$deleted<br><br>";
  }

Open in new window

0
MasCon
Asked:
MasCon
  • 8
  • 5
1 Solution
 
agamalCommented:
May i have a dump of sample DB to test the code locally ....
0
 
MasConAuthor Commented:
sure.
0
 
agamalCommented:
Waiting your DB dump
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MasConAuthor Commented:
hmm I thought I attached it to my last post ?
File containing sensitive data removed by Netminder 15 Jun 2010

Open in new window

0
 
agamalCommented:
Working on it will get back to you ...
0
 
agamalCommented:
in your first select statement .... you don't have a table called publisher_data
$sql = "SELECT p.`pdata_id_publisher`, p.`pdata_zipcodes` FROM publisher_data p where `pdata_zipcodes` LIKE '%$zipcode%';";

Open in new window

0
 
MasConAuthor Commented:
??  try this
PubBak.txt
0
 
agamalCommented:
i will work on it and will let you know
0
 
MasConAuthor Commented:
havent heard back and I have this but I am getting an error saying the table cant be found.
It is looking in the DB adnet and the table I want to look in for the data is in the DB adnet2010


$zipcode = ($_POST['zip']);
settype($zipcode,'string');
 
print "<b>Here are the results for zip code: </b><div class=seek>$zipcode</div><br><br>";
 
$dbname = 'adnet';
$dbname2 = 'adnet2010';
 
mysql_select_db($dbname);
 
$sql = "SELECT `pdata_id_publisher`, `pdata_zipcodes` FROM `publisher_data` WHERE `pdata_zipcodes` LIKE '%$zipcode%';";
$result = mysql_query($sql) or die("Couldn't execute query");
$publishers = array();
while($row = mysql_fetch_assoc($result)) {
    $publishers[$row['pdata_id_publisher']] = array($row['pdata_id_publisher'],'zips' => $row['pdata_zipcodes'], 'lic' => 0);
}
 
 
foreach ($publishers as $pubid => $pubinfo) {
 
mysql_select_db($dbname2);
        $sql = "SELECT `zip`,`id_publisher` FROM `db_publication_report` WHERE `id_publisher` = '$pubid'";
        $result  = mysql_query($sql) or die(mysql_error());
        while($row = mysql_fetch_assoc($result)) {
 
            $zippos = strpos($row['zip'],$zipcode);
            $publishers[$pubid]['zips'] .= ' , ' . $row['zip'];
            $publishers[$pubid]['lic']  += $zippos;
        }
}
 
foreach ($publishers as $pubid => $pubinfo) {
 
    $sql = "SELECT `jobno`, `contact`, `phone` FROM `db_publishers` WHERE `id_publisher` = '" . $pubid . "'";
    $result  = mysql_query($sql) or die(mysql_error());
    while($row = mysql_fetch_assoc($result)) {
 
        $contact=$row['contact'];
        $phone=$row['phone'];
        $jobno=$row['jobno'];
 
        if ($pubinfo['lic'] > 0) {  // publisher IS licensed
            print "Licensed ";
        } else {
            print "<font color= red><b>NOT licensed</font></b> ";
        }
		//(publisher ID $pubid)
        print "for zip code $zipcode <br><br>";
        print "<b>Publisher:</b> $contact<br><br>";
        print "<b>Phone:</b> $phone<br><br>";
        print "<b>Job No.:</b> $jobno<br><br>";
 
        $ziptok = strtok($pubinfo['zips'],',');
        $ziplist = '';
        while ($ziptok !== false) {
            $ziptok = trim($ziptok);
            if ($ziptok != '') {
                $ziplist .= $ziptok . ' ';
            }
            $ziptok = strtok(',');
        }
        print "<b>Zip Codes Owned:</b> ";
        print trim($ziplist) . "<br><br><br><br>";
    }
} 

Open in new window

0
 
agamalCommented:
sorry for being late .. but frankly i lost the question link and luckily you send me this update .... will get back to you ... today
0
 
agamalCommented:
i got this error
Table 'adnet2010.db_publishers' doesn't exist


i need this table dump db_publishers from database adnet2010

0
 
MasConAuthor Commented:
See the attachment for the 2 db and their structure.

the table to query in the DB adnet are

db_publishers and publisher_data


the table to query in the DB adnet2010
is db_publication_report
0
 
agamalCommented:
test it now .....

waiting your reply

<?php
//$zipcode = ($_POST['zip']);
$zipcode = "11";
settype($zipcode,'string');
 
print "<b>Here are the results for zip code: </b><div class=seek>$zipcode</div><br><br>";
 
$dbname = 'adnet';
$dbname2 = 'adnet2010';
$con = mysql_connect('localhost', 'root', 'root') or die (mysql_error());
mysql_select_db($dbname);
 
$sql = "SELECT `pdata_id_publisher`, `pdata_zipcodes` FROM `publisher_data` WHERE `pdata_zipcodes` LIKE '%$zipcode%';";
$result = mysql_query($sql) or die("Couldn't execute query");
$publishers = array();
while($row = mysql_fetch_assoc($result)) {
    $publishers[$row['pdata_id_publisher']] = array($row['pdata_id_publisher'],'zips' => $row['pdata_zipcodes'], 'lic' => 0);
}
 
 
foreach ($publishers as $pubid => $pubinfo) {
 
mysql_select_db($dbname2);
        $sql = "SELECT `zip`,`id_publisher` FROM `db_publication_report` WHERE `id_publisher` = '$pubid'";
        $result  = mysql_query($sql) or die(mysql_error());
        while($row = mysql_fetch_assoc($result)) {
 
            $zippos = strpos($row['zip'],$zipcode);
            $publishers[$pubid]['zips'] .= ' , ' . $row['zip'];
            $publishers[$pubid]['lic']  += $zippos;
        }
}
mysql_select_db($dbname);
foreach ($publishers as $pubid => $pubinfo) {
 
    $sql = "SELECT `jobno`, `contact`, `phone` FROM `db_publishers` WHERE `id_publisher` = '" . $pubid . "'";
    $result  = mysql_query($sql) or die(mysql_error());
    while($row = mysql_fetch_assoc($result)) {
 
        $contact=$row['contact'];
        $phone=$row['phone'];
        $jobno=$row['jobno'];
 
        if ($pubinfo['lic'] > 0) {  // publisher IS licensed
            print "Licensed ";
        } else {
            print "<font color= red><b>NOT licensed</font></b> ";
        }
                //(publisher ID $pubid)
        print "for zip code $zipcode <br><br>";
        print "<b>Publisher:</b> $contact<br><br>";
        print "<b>Phone:</b> $phone<br><br>";
        print "<b>Job No.:</b> $jobno<br><br>";
 
        $ziptok = strtok($pubinfo['zips'],',');
        $ziplist = '';
        while ($ziptok !== false) {
            $ziptok = trim($ziptok);
            if ($ziptok != '') {
                $ziplist .= $ziptok . ' ';
            }
            $ziptok = strtok(',');
        }
        print "<b>Zip Codes Owned:</b> ";
        print trim($ziplist) . "<br><br><br><br>";
    }
}
?>

Open in new window

0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now