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>"; } } } Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/ 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>"; } } ?> Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-297831 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! Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-297834 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? Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-297947 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.... Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-298172 Share on other sites More sharing options...
keeB Posted July 14, 2007 Share Posted July 14, 2007 In this case, you have to. Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-298194 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? Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-298204 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. Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-298216 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 Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-298230 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 :-) Link to comment https://forums.phpfreaks.com/topic/59890-solved-is-there-a-better-way-to-do-this-hitting-database-too-much/#findComment-298242 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.