datafan Posted July 14, 2007 Share Posted July 14, 2007 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>"; } } } Quote Link to comment Share on other sites More sharing options...
trq Posted July 14, 2007 Share Posted July 14, 2007 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>"; } } ?> Quote Link to comment Share on other sites More sharing options...
datafan Posted July 14, 2007 Author Share Posted July 14, 2007 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! Quote Link to comment Share on other sites More sharing options...
datafan Posted July 14, 2007 Author Share Posted July 14, 2007 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? Quote Link to comment Share on other sites More sharing options...
datafan Posted July 14, 2007 Author Share Posted July 14, 2007 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.... Quote Link to comment Share on other sites More sharing options...
keeB Posted July 14, 2007 Share Posted July 14, 2007 In this case, you have to. Quote Link to comment Share on other sites More sharing options...
datafan Posted July 14, 2007 Author Share Posted July 14, 2007 My angle is: is there some way I can manipulate the $zips array before I make my database comparison? Quote Link to comment Share on other sites More sharing options...
keeB Posted July 14, 2007 Share Posted July 14, 2007 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. Quote Link to comment Share on other sites More sharing options...
lur Posted July 14, 2007 Share Posted July 14, 2007 $query = "SELECT * FROM users WHERE zipcode IN('" .implode("','" ,array_keys($zips)) . "')"; http://php.net/array_keys Quote Link to comment Share on other sites More sharing options...
datafan Posted July 14, 2007 Author Share Posted July 14, 2007 $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 :-) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.