Hi All
I am working on a web based helper site for my office it wont be on the internet but running on wamp on a networked PC, I have a myisam mysql database with 3 tables.
each table holds information that I want to be able to search but I cant get the syntax right to "join" allowing me to search from all tables and rows.
table 1 is stores
I want to be able to search rows store_name and store_number
table 2 is zone_managers
I want to search row managers_name
table 3 is area_managers
I want to search row managers_name
I have tried to select * from store,zone_managers,area_managers where like %term%
and using the join function but I cant get it right.
And if I search for "a" the script returns many results and I would like to limit to say 10 and have a "next 10 results" link/button if possible.
As you will see from the code below the results form a link but I need to work out how to remove the spaces for instance
a link would be formed like <a href="stores/zone2/region21/central croyden"> using the search results but the space stops the link from working. I might try to add another row to the stores table that contains the link but there are over 800 entries so I was hoping to avoid this.
also I cant work out how to echo "Nothing found" if the search comes back with nothing.
Here is the relivant snippet of code -
<?php
mysql_connect ("localhost", "username","password") or die (mysql_error());
mysql_select_db ("database");
$term = $_POST['term'];
if ($term == '')
{
echo "<meta http-equiv=\"refresh\" content=\"4; url=imain.php\">";
echo "<font face=\"Verdana\" size=\"2\"><strong>No Search Term Entered</strong><br><br>";
echo "Returning to main page...</font>";
} else {
echo "<font face=\"Verdana\" size=\"2\">The following entries were found for your search term "<strong>$term</strong>"<hr></font><blockquote>";
$sql = mysql_query("select * from stores where store_name like '%$term%'");
while ($row = mysql_fetch_array($sql)){
echo "<font face=\"Verdana\" size=\"2\"><a href=\"stores/zone" .$row['store_zone'];
echo "/region" .$row['store_region'];
echo "/" .$row['store_name'];
echo "\"><strong><u>" .$row['store_name'];
echo "</u></strong><br>";
echo "Store Number - " .$row['store_number'];
echo "<br>";
echo "Tel - " .$row['store_tel'];
echo "<br>";
echo "Zone <strong>" .$row['store_zone'];
echo "</strong> Region <strong>" .$row['store_region'];
echo "</strong></a></font><br><br>";
}
}
?>
I am sure there are errors in my code but I am just learning and I am having trouble with this,, I no there is a lot that I am asking but any help would be very much appreciated..
With thanks
Dave K
I Just figured out the "nothing found" part, not sure if its right but it seems to work
I added
if ($row =='');
echo "Nothing Found";
at the end.
PS Thanks Ratchetr for the fast responce, how would I code for 3 select queries and loop ??
Just fixed the removing spaces problem and converted to lower case for the links using
$link = $row["store_name"];
$link = str_replace(' ', '', $link);
$link = strtolower($link);
Getting there
I am working on a web based helper site for my office it wont be on the internet but running on wamp on a networked PC, I have a myisam mysql database with 3 tables.
each table holds information that I want to be able to search but I cant get the syntax right to "join" allowing me to search from all tables and rows.
table 1 is stores
I want to be able to search rows store_name and store_number
table 2 is zone_managers
I want to search row managers_name
table 3 is area_managers
I want to search row managers_name
I have tried to select * from store,zone_managers,area_managers where like %term%
and using the join function but I cant get it right.
And if I search for "a" the script returns many results and I would like to limit to say 10 and have a "next 10 results" link/button if possible.
As you will see from the code below the results form a link but I need to work out how to remove the spaces for instance
a link would be formed like <a href="stores/zone2/region21/central croyden"> using the search results but the space stops the link from working. I might try to add another row to the stores table that contains the link but there are over 800 entries so I was hoping to avoid this.
also I cant work out how to echo "Nothing found" if the search comes back with nothing.
Here is the relivant snippet of code -
<?php
mysql_connect ("localhost", "username","password") or die (mysql_error());
mysql_select_db ("database");
$term = $_POST['term'];
if ($term == '')
{
echo "<meta http-equiv=\"refresh\" content=\"4; url=imain.php\">";
echo "<font face=\"Verdana\" size=\"2\"><strong>No Search Term Entered</strong><br><br>";
echo "Returning to main page...</font>";
} else {
echo "<font face=\"Verdana\" size=\"2\">The following entries were found for your search term "<strong>$term</strong>"<hr></font><blockquote>";
$sql = mysql_query("select * from stores where store_name like '%$term%'");
while ($row = mysql_fetch_array($sql)){
echo "<font face=\"Verdana\" size=\"2\"><a href=\"stores/zone" .$row['store_zone'];
echo "/region" .$row['store_region'];
echo "/" .$row['store_name'];
echo "\"><strong><u>" .$row['store_name'];
echo "</u></strong><br>";
echo "Store Number - " .$row['store_number'];
echo "<br>";
echo "Tel - " .$row['store_tel'];
echo "<br>";
echo "Zone <strong>" .$row['store_zone'];
echo "</strong> Region <strong>" .$row['store_region'];
echo "</strong></a></font><br><br>";
}
}
?>
I am sure there are errors in my code but I am just learning and I am having trouble with this,, I no there is a lot that I am asking but any help would be very much appreciated..
With thanks
Dave K
I Just figured out the "nothing found" part, not sure if its right but it seems to work
I added
if ($row =='');
echo "Nothing Found";
at the end.
PS Thanks Ratchetr for the fast responce, how would I code for 3 select queries and loop ??
Just fixed the removing spaces problem and converted to lower case for the links using
$link = $row["store_name"];
$link = str_replace(' ', '', $link);
$link = strtolower($link);
Getting there