[SOLVED] Is there a better way to do this? Hitting database too much.

[datafan]

I am a very new php programmer and getting in way over my head on a lot of stuff.

 

$zips is an array of zipcodes and mileage info pulled from another database table based on a mileage radius.

 

So I am showing my user all the stylists from the table "users" that have a zipcode matching a $zips value.

 

It works, but it is accessing the "users" table a LOT of times, especially if there are a lot of values in $zips.

 

I think this line is the source of the problem, but not sure:  $query = sprintf("SELECT * FROM users WHERE zipcode = '$key'");

 

Is there a better way to do this?

 

or can someone tell me, is the way I have written this making multiple database hits when it's not needed?

 

Thanks a ton for any help :-)

 

 

foreach ($zips as $key => $value) {
    	
CleanUpDB(); 

if ($_POST['type']=='stylist'){
$query = sprintf("SELECT * FROM users WHERE zipcode = '$key'"); 	

GetMyConnection() or die(mysql_error());
$result = mysql_query($query);
	if (!$result) {
		$message  = 'Invalid query: ' . mysql_error() . "\n";
		$message .= 'Whole query: ' . $query;
		die($message);
	}
		while ($row = mysql_fetch_assoc($result)) {
			$thumb = "userimages/" .$row['thpicname1'];
			echo "<table cellpadding='4' rows='3' cols=='5'>"; 
			echo "<tr><td rowspan='3' width='100'><a href='profileview.php?find=".$row['username']."' target='_self'><img src='".$thumb."'></a></td><td>" .$row['realname']. "</td></tr><tr><td>"  . $row['city'] . " , " . $row['state'] . " " . $row[zipcode] . "</td></tr><tr><td>Current Rating " . $row['currentrate'] . "</td></tr>";
			echo "</table><br>";
		}
}
if ($_POST['type']=='salon'){
$query = sprintf("SELECT * FROM biz WHERE zipcode = '$key'");

GetMyConnection() or die(mysql_error());
$result = mysql_query($query);
	if (!$result) {
		$message  = 'Invalid query: ' . mysql_error() . "\n";
		$message .= 'Whole query: ' . $query;
		die($message);
	}
		while ($row = mysql_fetch_assoc($result)) {
			$thumb = "userimages/" .$row['thpicname1'];
			echo "<table cellpadding='4' rows='3' cols=='5'>"; 
			echo "<tr><td rowspan='3' width='100'><a href='bizprofileview.php?find=".$row['bizid']."' target='_self'><img src='".$thumb."'></a></td><td>" .$row['bizname']. "</td></tr><tr><td>"  . $row['city'] . " , " . $row['state'] . " " . $row[zipcode] . "</td></tr><tr><td>Current Rating " . $row['currentrate'] . "</td></tr>";
			echo "</table><br>";
		}		
}		

}   

[trq]

Yes... quierying the database within a loop is generally a bad idea. You could use something like...

 

<?php

  if ($_POST['type']=='stylist') {
    $query = "SELECT * FROM users WHERE zipcode IN('" .implode("','" $zips) . "')";
  elseif  ($_POST['type']=='salon') {
    $query = "SELECT * FROM biz WHERE zipcode IN('" .implode("','" $zips) . "')";
  }

  GetMyConnection() or die(mysql_error());
  $result = mysql_query($query);
  if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
  }

  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
      $thumb = "userimages/" .$row['thpicname1'];
      echo "<table cellpadding='4' rows='3' cols=='5'>"; 
      echo "<tr><td rowspan='3' width='100'><a href='profileview.php?find=".$row['username']."' target='_self'>";
      echo "<img src='".$thumb."'></a></td><td>" .$row['realname']. "</td></tr><tr>";
      echo "<td>"  . $row['city'] . " , " . $row['state'] . " " . $row[zipcode] . "</td></tr><tr><td>Current Rating " . $row['currentrate'] . "</td></tr>";
      echo "</table><br>";
    }
  }

?>

[datafan]

Yes... quierying the database within a loop is generally a bad idea. You could use something like...

 

<?php

  if ($_POST['type']=='stylist') {
    $query = "SELECT * FROM users WHERE zipcode IN('" .implode("','" $zips) . "')";
  elseif  ($_POST['type']=='salon') {
    $query = "SELECT * FROM biz WHERE zipcode IN('" .implode("','" $zips) . "')";
  }

  GetMyConnection() or die(mysql_error());
  $result = mysql_query($query);
  if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
  }

  if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
      $thumb = "userimages/" .$row['thpicname1'];
      echo "<table cellpadding='4' rows='3' cols=='5'>"; 
      echo "<tr><td rowspan='3' width='100'><a href='profileview.php?find=".$row['username']."' target='_self'>";
      echo "<img src='".$thumb."'></a></td><td>" .$row['realname']. "</td></tr><tr>";
      echo "<td>"  . $row['city'] . " , " . $row['state'] . " " . $row[zipcode] . "</td></tr><tr><td>Current Rating " . $row['currentrate'] . "</td></tr>";
      echo "</table><br>";
    }
  }

?>

 

 

This is very cool, and just what I needed. Thanks a bunch!

[datafan]

I thought we had it, but I still need some help. 

My $zips array is apparently multidimentional and the zipcodes are the $keys.  The only way (that I know how) to access the $zips array is:

 

<?php
foreach ($zips as $key => $value) {
    echo "<font color='yellow'>Zip code <b>$key</b> is <b>$value</b> miles away from <b>". $_POST['myzip']."</b>.</font><br /><br />
?>

 

Which brings me back to the looping thing.

Any thoughts?

[datafan]

I thought we had it, but I still need some help. 

My $zips array is apparently multidimentional and the zipcodes are the $keys.  The only way (that I know how) to access the $zips array is:

 

<?php
foreach ($zips as $key => $value) {
    echo "<font color='yellow'>Zip code <b>$key</b> is <b>$value</b> miles away from <b>". $_POST['myzip']."</b>.</font><br /><br />
?>

 

Which brings me back to the looping thing.

Any thoughts?

 

As far as I can tell what it seems like to me is when I run this query:

<?php 
$query = "SELECT * FROM users WHERE zipcode IN('" .implode("','" ,$zips) . "')";
?>

 

I am comparing the users table zipcodes to the MILES in the $zips array instead of the zipcodes.

It seems like the $zips array is set up where the $keys are the zipcode and $value is the miles:

$key=>$value

55901=>10

55902=>12

55964=>14

 

I was up half the night trying stuff and still no luck, how can I compare without running my query in a loop?

Thanks....

[keeB]

In this case, you have to.

[datafan]

My angle is: is there some way I can manipulate the $zips array before I make my database comparison?

[keeB]

Your code is hard for me to read / i'm not interested in reading it any more...

 

Conceptually, you have the code there. What thorpe provided is a perfect example of how to achieve what you want.

 

You want to use the IN( ) call in your sql with a list of zip codes. What value you're pulling from $zips is the determining factor.

[lur]

$query = "SELECT * FROM users WHERE zipcode IN('" .implode("','" ,array_keys($zips)) . "')";

 

http://php.net/array_keys

[datafan]

$query = "SELECT * FROM users WHERE zipcode IN('" .implode("','" ,array_keys($zips)) . "')";

 

http://php.net/array_keys

 

Wooohoooo! Awesome! That exactly what I needed, works great and faster too! Thank YOU

 

I appreciate the help from eveyone else as well, and the feedback on "my" code. What can I do to make it easier to read? More comments? I am an "old" new programmer and appreciate constructive criticism to improve my work.

 

If anyone cares here is the code in action on my site http://www.goodstylist.com/files/search.php

but it's a new site so there are only stylists within 20 miles of 55964 at the moment :-)