Help With Sql Statement Returning Empty Records

mallen · October 19, 2012

I have this code that is listing names of states with agent, company, address ect. But i don't want to list a state that doesn't have any agent associated to it. Looking at what I have here can someone point me in the right direction how to achieve this? I am thinking right after the line #12 foreach($states as $st create a statment that only will print if there is data. The trouble is everything I have tried returns true becuase every thing has a value. "Agent locations" table includes locs_id, agent_id, state_id and country_id. I need somethign that says find this data if it finds all of these only.

$sql = "SELECT DISTINCT(cty.name), cty.has_state, cty.id FROM agent_countires AS cty
LEFT JOIN agent_locations AS ag
ON ag.country_id= cty.id";
$res = $wpdb->get_results($sql, ARRAY_A);
foreach($res as $r){
?>
<h3><?php echo $r['name'];?></h3>
<?php if($r['has_state'] == "1") :

$states = $wpdb->get_results("SELECT `id`,`state_name` FROM wp_agent_states WHERE `country`='{$r['id']}' ORDER BY state_name ASC", ARRAY_A);

foreach($states as $st){
//need statement here if state doesn't have anything associated done print, or else ...
echo "<h5>". $st['state_name'] . "</h5>"; //<<<<It prints state names that don't have any results


$newQuery = "SELECT `agent`.`company_name` , `agent`.`address` , `agent`.`city` , `agent`.`zip` , `agent`.`phone` , `agent`.`fax` , `agent`.`email` , `agent`.`website` , `agent`.`territory` , `agent`.`contact_name`, `agent`.`country`, `agent`.`state`
FROM wp_find_agent AS agent
LEFT JOIN agent_locations AS locs ON agent.id = locs.agent_id
WHERE locs.state_id ='". $st['id'] ."'";

$newLocs = $wpdb->get_results($newQuery, ARRAY_A);
if(count($newLocs) > 0) :
foreach($newLocs as $a)
{

echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$a['company_name']}</strong><br/>\n";
 echo $a['address'] ."<br/>\n";
 echo $a['city'] . ", ". countryOrState($a['state'], $a['country']) . " " . $a['zip'] . "<br/>\n";
 echo "Phone: ". $a['phone'] . "<br/>\n";

mikosiko · October 19, 2012

change your logic.... running queries in loops is generally a bad idea... in your case seems that you can use just ONE query (for all the involved tables) using JOIN's instead of LEFT JOINS... after that is just a matter of logic to display the data properly

mallen · October 19, 2012

I put the display errors in, but of course there were no errors displayed. I think the person before me who wrote this set it up so it would display the name of the state onnce, then display all listings below that. Becuase I tried changing it around and it would list a state name each time.

I could query the find_agent table but displaying the state name with multiple listings is the issue. This table includes id, company_name, address, state(numeral) ect.....

Edited October 19, 2012 by mallen

mikosiko · October 19, 2012

did you understand my previous answer?... doesn't look like...

and just in case:

the ini_set() and error_reporting() that you see in my previous post is part of my signature and not part of the answer.

test this (no tested on my side)... and see if you can came up with some new ideas:

"SELECT cty.id,
       cty.name, 
       states.id,
       states.state_name,
       `agent`.`company_name`, 
       `agent`.`address` , 
       `agent`.`city` , 
       `agent`.`zip` , 
       `agent`.`phone` , 
       `agent`.`fax` , 
       `agent`.`email` , 
       `agent`.`website` , 
       `agent`.`territory` , 
       `agent`.`contact_name`, 
       `agent`.`country`, 
       `agent`.`state`
 FROM wp_find_agent AS agent
      JOIN agent_locations AS locs ON agent.id = locs.agent_id
      JOIN wp_agent_states AS states ON states.id = locs.state_id 
      JOIN agent_countires AS cty ON locs.country_id = cty.id
 WHERE cty.has_state = 1
 ORDER BY cty.name, states.state_name ASC";

mallen · October 19, 2012

Thisis what I have. Its not displaying any results or errors.

ini_set("display_errors", 1);
error_reporting(-1);
function displayFrontAgents(){

?>
<div id='agentLocation'>
<?php
$sql="SELECT cty.id,
		 cty.name,
		 states.id,
		 states.state_name,
		 `agent`.`company_name`,
		 `agent`.`address` ,
		 `agent`.`city` ,
		 `agent`.`zip` ,
		 `agent`.`phone` ,
		 `agent`.`fax` ,
		 `agent`.`email` ,
		 `agent`.`website` ,
		 `agent`.`territory` ,
		 `agent`.`contact_name`,
		 `agent`.`country`,
		 `agent`.`state`
FROM wp_find_agent AS agent
	 JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id
	 JOIN wp_agent_states AS states ON states.id = locs.state_id
	 JOIN wp_agent_countires AS cty ON locs.country_id = cty.id
WHERE cty.has_state = 1
ORDER BY cty.name, states.state_name ASC";
$res = $wpdb->get_results($sql, ARRAY_A);
foreach($res as $r){
 echo "<h5>". $r['states.state_name'] . "</h5>";

 echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n";
 echo $r['address'] ."<br/>\n";
 echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n";
 echo "Phone: ". $r['phone'] . "<br/>\n";
	 echo "Fax: ". $r['fax'] . "<br/>\n";
 echo "Email: <a href='mailto:" . $a['email'] . "' target='_blank'>". $a['contact_name'] ."</a><br/>\n";
 echo "Website: <a href='http://{$a['website']}' target='_blank'>{$a['website']}</a><br/>\n";
 echo "Territory: ". $a['territory'] ."<br/><br/>\n</p>";
}

}

Edited October 19, 2012 by mallen

mikosiko · October 19, 2012

did you run the query outside of PHP (in whatever tool that you use... PhpMyadmin, Workwench, etc)... did produce results?.. that is the first thing to check.

secondly, where in your code are you calling the function displayFrontAgents() ??

mallen · October 19, 2012

Sorry I didn't think of doing it that way. Put it into PHPMyAdmin and corrected a few typos and it worked. Now I just need to figure how to list a state only once if there are multiple listings for a state.

$sql= "SELECT cty.id,
		 cty.name,
		 states.id,
		 states.state_name,
		 `agent`.`company_name`,
		 `agent`.`address` ,
		 `agent`.`city` ,
		 `agent`.`zip` ,
		 `agent`.`phone` ,
		 `agent`.`fax` ,
		 `agent`.`email` ,
		 `agent`.`website` ,
		 `agent`.`territory` ,
		 `agent`.`contact_name`,
		 `agent`.`country`,
		 `agent`.`state`
FROM wp_find_agent AS agent
	 JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id
	 JOIN wp_agent_states AS states ON states.id = locs.state_id
	 JOIN wp_agent_countries AS cty ON locs.country_id = cty.id
WHERE cty.has_state = 1
ORDER BY cty.name, states.state_name ASC";
$res = $wpdb->get_results($sql, ARRAY_A);
foreach($res as $r){
 echo "<h5>". $r['state_name'] . "</h5>";
 echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n";
 echo $r['address'] ."<br/>\n";
 echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n";
 echo "Phone: ". $r['phone'] . "<br/>\n";
 echo "Fax: ". $r['fax'] . "<br/>\n";
	 echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n";
 echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n";
 echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>";

}

}

Edited October 19, 2012 by mallen

mikosiko · October 19, 2012

general idea:


....
....
$res = $wpdb->get_results($sql, ARRAY_A);

$prev_state = '';

foreach($res as $r){
  if ($prev_state != $r['state_name']) {
      // Print the state
     echo "<h5>". $r['state_name'] . "</h5>";
   $prev_state = $r['state_name'];
  }
 // Rest of your code
}

Edited October 19, 2012 by mikosiko

mallen · October 19, 2012

Thanks that worked! At first I was not sure where to place inside my code. Also was duplicating the state name because I had an echo statement in there twice. Now next week I will try to get it to display The country, such as United States, then list of states, then Mexico, listing under Mexico like I had it.

Can you explain this ?

states.id,

states.state_name,

Why does 'states.id' have to be used instead of state.id

Barand · October 19, 2012

Why does 'states.id' have to be used instead of state.id

Because you declared the table alias to be "states"

mallen · October 22, 2012

$sql="SELECT cty.id,cty.name,states.id, states.state_name,`agent`.`company_name`,

Sorry I am just not knowledgeable about complex SQL statements. Why does agent need to be in single quotes and the others do not?

Barand · October 22, 2012

Those are known as backticks, not single quotes.

In this case they are not necessary. You only need them if your column name is a mysql reserved word or contains a space or other special characters.

mallen · October 22, 2012

Great now I know what those are called. I know they weren't quotes becuase I see it on my keyboard just didn't know what to call them.

Continiring on from what Mikosiko was helping me with. I was able to build on what Mikosiko showed me to display the state once. Now I got it to display the country. The old version of my code I was able to display Mexico, Puerto Rico and India. It is not displaying these countries which have a value of "0" in the has_state. I have this function at the bottom.

$sql= "SELECT cty.id,
		    cty.name,
		    states.id,
		    states.state_name,
		    `agent`.`company_name`,
		    `agent`.`address` ,
		    `agent`.`city` ,
		    `agent`.`zip` ,
		    `agent`.`phone` ,
		    `agent`.`fax` ,
		    `agent`.`email` ,
		    `agent`.`website` ,
		    `agent`.`territory` ,
		    `agent`.`contact_name`,
		    `agent`.`country`,
		    `agent`.`state`
 FROM wp_find_agent AS agent
	   JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id
	   JOIN wp_agent_states AS states ON states.id = locs.state_id
	   JOIN wp_agent_countries AS cty ON locs.country_id = cty.id
 WHERE cty.has_state = 1
 ORDER BY cty.name, states.state_name ASC";




 $res = $wpdb->get_results($sql, ARRAY_A);
 $prev_state = '';
  foreach($res as $r){
 if ($prev_country != $r['name']) {
		 // Print the country

 $prev_country = $r['name'];

		 echo "<h5>". $r['name'] . "</h5>";

 if ($prev_state != $r['state_name']) {
		 // Print the state

 $prev_state = $r['state_name'];

		 echo "<h5>". $r['state_name'] . "</h5>";

  }
 }
  echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n";
  echo $r['address'] ."<br/>\n";
  echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n";
  echo "Phone: ". $r['phone'] . "<br/>\n";
  echo "Fax: ". $r['fax'] . "<br/>\n";
	 echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n";
  echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n";
  echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>";


 }

  }



function countryOrState($s, $c)
{
global $wpdb;
if($r !== "0")
 return $wpdb->get_var("SELECT `state_name` FROM wp_agent_states WHERE `id`='$s'");
else
 return $wpdb->get_var("SELECT `name` FROM wp_agent_countries WHERE `id` ='$c'");
}
?>

Barand · October 22, 2012

perhaps this is why

WHERE cty.has_state = 1

mallen · October 22, 2012

Yes after posting that I realized that I removed that Where clause and that didn't work.

mikosiko · October 22, 2012

try using a LEFT JOIN in this 2 lines, and post back your results

JOIN wp_agent_states AS states ON states.id = locs.state_id

JOIN wp_agent_countries AS cty ON locs.country_id = cty.id

mallen · October 22, 2012

I get Canada and United States listed. Only Arkasas title shows as a title. I get all the listings just not a title for other states. Ans Puerto Rico, Mexico and India do not show a a listing.

$sql= "SELECT cty.id,
		    cty.name,
		    states.id,
		    states.state_name,
		    `agent`.`company_name`,
		    `agent`.`address` ,
		    `agent`.`city` ,
		    `agent`.`zip` ,
		    `agent`.`phone` ,
		    `agent`.`fax` ,
		    `agent`.`email` ,
		    `agent`.`website` ,
		    `agent`.`territory` ,
		    `agent`.`contact_name`,
		    `agent`.`country`,
		    `agent`.`state`
 FROM wp_find_agent AS agent
	   JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id
	   LEFT JOIN wp_agent_states AS states ON states.id = locs.state_id
	   LEFT JOIN wp_agent_countries AS cty ON locs.country_id = cty.id
 WHERE cty.has_state = 1
 ORDER BY cty.name, states.state_name ASC";




 $res = $wpdb->get_results($sql, ARRAY_A);
 $prev_state = '';
  foreach($res as $r){
   if ($prev_country != $r['name']) {
		 // Print the country

 $prev_country = $r['name'];

		 echo "<h5>". $r['name'] . "</h5>";

 if ($prev_state != $r['state_name']) {
		 // Print the state

 $prev_state = $r['state_name'];

		 echo "<h5>". $r['state_name'] . "</h5>";

  }
 }
  echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n";
  echo $r['address'] ."<br/>\n";
  echo $r['city'] . ", ". countryOrState($r['state'], $r['country']) . " " . $r['zip'] . "<br/>\n";
  echo "Phone: ". $r['phone'] . "<br/>\n";
  echo "Fax: ". $r['fax'] . "<br/>\n";
	 echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n";
  echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n";
  echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>";


 }

  }

//var_dump($sql);

function countryOrState($r, $c)
{
global $wpdb;
if($r !== "0")
 return $wpdb->get_var("SELECT `state_name` FROM wp_agent_states WHERE `id`='$r'");
else
return $wpdb->get_var("SELECT `name` FROM wp_agent_countries WHERE `id` ='$c'");
}
?>

mikosiko · October 22, 2012

you still having this line in the query

WHERE cty.has_state = 1

remove it ... if that doesn't work you need top post a REAL example of the data of each table and the expected results.

mallen · October 22, 2012

Ok getting closer. It shows all the states and countries. But it is listing Alberta Canada, British Columbia, Ontario, Qubec, India, Mexico, Puerto Rico, and United States in that order. Now I want to list like this. Or atleast United States on top:

United States:

Then all the states

Canada:

India:

Mexico:

Puerto Rico

<?php

$sql= "SELECT cty.id,
		    cty.name,
		    states.id,
		    states.state_name,
		    `agent`.`company_name`,
		    `agent`.`address` ,
		    `agent`.`city` ,
		    `agent`.`zip` ,
		    `agent`.`phone` ,
		    `agent`.`fax` ,
		    `agent`.`email` ,
		    `agent`.`website` ,
		    `agent`.`territory` ,
		    `agent`.`contact_name`,
		    `agent`.`country`,
		    `agent`.`state`
 FROM wp_find_agent AS agent
	   JOIN wp_agent_locations AS locs ON agent.id = locs.agent_id
	   LEFT JOIN wp_agent_states AS states ON states.id = locs.state_id
	   LEFT JOIN wp_agent_countries AS cty ON locs.country_id = cty.id

 ORDER BY cty.name, states.state_name ASC";




 $res = $wpdb->get_results($sql, ARRAY_A);
 $prev_state = '';
 $prev_country = '';
  foreach($res as $r){

 if ($prev_state != $r['state_name']) {
		 // Print the state
 echo "<h5>". $r['state_name'] . "</h5>";
 $prev_state = $r['state_name'];
 }
		 if ($prev_country != $r['name']) {
		 // Print the country
 echo "<h5>". $r['name'] . "</h5>";
 $prev_country = $r['name'];
 }



  echo"<p class='agentAddress' style='margin-top:0px;'><strong>{$r['company_name']}</strong><br/>\n";
  echo $r['address'] ."<br/>\n";
  echo $r['city'] . ", ". countryOrState($r['state'], $c['country']) . " " . $r['zip'] . "<br/>\n";
  echo "Phone: ". $r['phone'] . "<br/>\n";
  echo "Fax: ". $r['fax'] . "<br/>\n";
	 echo "Email: <a href='mailto:" . $r['email'] . "' target='_blank'>". $r['contact_name'] ."</a><br/>\n";
  echo "Website: <a href='http://{$r['website']}' target='_blank'>{$r['website']}</a><br/>\n";
  echo "Territory: ". $r['territory'] ."<br/><br/>\n</p>";


 }

  }



function countryOrState($r, $c)
{
global $wpdb;
if($r !== "0")
 return $wpdb->get_var("SELECT `state_name` FROM wp_agent_states WHERE `id`='$r'");
else
return $wpdb->get_var("SELECT `name` FROM wp_agent_countries WHERE `id` ='$c'");
}
?>

mallen · October 22, 2012

If I change to :

ORDER BY agent.country, cty.name, states.state_name ASC

I get Mexico, then United States and so on. The agent_countries table is

US id is 1, Canada is 2, Mexico is 3 and Puerto Rico is 4.

I changed it to ORDER BY cty.id ASC and its in the order I needed.

Edited October 22, 2012 by mallen

Sign In

Help With Sql Statement Returning Empty Records

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information