I have a phone book database,in mysql,but when i search one person with his name or his surname,php shows me all person with this name,or all with this surname. How can i configure when search this name in this city,php displayes me only this names in this city,or only this surnames in this city. I have 5 columns in database : name,surname,phone nr,city,address. php code : <html> <head><title>phone book</title></head> <body> <?php $emri=$_POST['name']; $mbiemri=$_POST['surname']; $numer=$_POST['phone_nr']; $qyteti=$_POST['city']; $adresa=$_POST['address']; $db="phonebook"; $lidhja= mysql_connect("localhost","xxxxxxx","xxxxxxx"); if (! $connect) die(" MySQL connection error"); mysql_select_db($db , $connect) or die("Error,canot acces $db: ".mysql_error()); $result = mysql_query( "SELECT `name` , `surname` , `NR_TEL` , `city` , `addres` FROM `phonebook` WHERE `name` = '$name' OR NR_TEL = '$phone_nr' OR surname = '$surname'" ) or die("SELECT Error: ".mysql_error()); print "<center>"; print "<table width='800' border=1 bordercolor='midnightblue'>"; print "<tr bgcolor='midnightblue'> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>NAme</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Surname</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Nr.Tel</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>city</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>address</b></font></center></td>"; print "</tr>"; while ($get_info = mysql_fetch_row($result)){ print "<tr>\n"; foreach ($get_info as $field) print "\t<td><font face=arial/><div align=center><b>$field</b></div></font></td>\n"; print "</tr>\n"; } print "</table>\n"; mysql_close($connect); ?> <center> </center> </body> </html>
I'm a bit confused by your question but if you are saying that the user always has to specify a city and also one of the other pieces of the information so... city and name city and phone number city and surname then either of the following will do it... WHERE (`name` = '$name' AND `city`='$city') OR (NR_TEL = '$phone_nr' AND `city`='$city') OR (surname = '$surname' AND `city`='$city')" or.... WHERE `city`='$city' AND (`name` = '$name' OR NR_TEL = '$phone_nr' OR surname = '$surname')"
Yes that's it. City is the main selection ,or obligatet selection,and another selections are: name , surname,or phone number. And the result to get is only those names in the selected city,ore surnames, or phone nr.
Ok,But and one litle. Where i Search for exact person : Name AND Surname, in autput we take All members with this name and all members with this surname (normaly in this city) I make this: WHERE `city`='$city' AND (`name` = '$name' OR NR_TEL = '$phone_nr' OR surname = '$surname' OR (`name` = '$name' AND surname = '$surname' )) But dont worked.
You'll need a different statement if they put in both name and surname (because you're ORing with the other statements so they will still kick in). If they enter John Doe, the part that says "or surname =" will return all Doe's, regardless of firstname. It's probably easiest just to build two or more WHERE clauses.... if (!isempty($name) && !isempty($surname){ $result = mysql_query( "SELECT `name` , `surname` , `NR_TEL` , `city` , `addres` FROM `phonebook` WHERE `city`='$city' AND ((`name` = '$name' AND surname = '$surname') OR NR_TEL = '$phone_nr'))") or die("SELECT Error: ".mysql_error()); }else{ $result = mysql_query( "SELECT `name` , `surname` , `NR_TEL` , `city` , `addres` FROM `phonebook` WHERE `city`='$city' AND (`name` = '$name' OR NR_TEL = '$phone_nr' OR surname = '$surname' )") or die("SELECT Error: ".mysql_error()); } PHP: Or build the whole where clause dynamically so... $whereclause = " WHERE city='$city'"; if(!isempty($name)){ $whereclause .= " AND name='$name'"; } if(!isempty($surname)){ $whereclause .= " AND surname='$surname'"; } if(!isempty($phone_nr)){ $whereclause .= " AND NR_TEL='$phone_nr'"; } $result = mysql_query( "SELECT `name` , `surname` , `NR_TEL` , `city` , `addres` FROM `phonebook` ".$whereclause ) or die("SELECT Error: ".mysql_error()); PHP:
ecentricNick thanks at all. Please can you type all the php code , because I can't do it to work properly.
Well, there was a typo in my first example. It should read... if (!isempty($name) && !isempty($surname)){ PHP: (extra close parenthesis on the end) I could type it all except I am confused by one part of your code... You do this... $emri=$_POST['name']; $mbiemri=$_POST['surname']; $numer=$_POST['phone_nr']; $qyteti=$_POST['city']; $adresa=$_POST['address']; PHP: yet in your where statement you don't use those variable names?
My php is: <html> <head><title>phone book</title></head> <body> <?php $emri=$_POST['emer']; $mbiemri=$_POST['mbiemer']; $numer=$_POST['numer']; $adresa=$_POST['adresa']; $qyteti=$_POST['qyteti']; $db="numerator"; $lidhja= mysql_connect("localhost","xxxxxx","xxxxxxxx"); if (! $lidhja) die("Nuk mund të lidheni me MySQL"); mysql_select_db($db , $lidhja) or die("Nuk mund të hapet $db: ".mysql_error()); $result = mysql_query( "SELECT `EMRI` , `MBIEMRI` , `NR_TEL` , `QYTETI` , `RRUGA` FROM `numerator` WHERE (`QYTETI` = '$qyteti' AND `EMRI` = '$emri' )OR (NR_TEL = '$numer' AND `QYTETI` = '$qyteti' )OR (MBIEMRI = '$mbiemri' AND `QYTETI` = '$qyteti') order by QYTETI" ) or die("SELECT Error: ".mysql_error()); print "<center>"; print "<table width='800' border=1 bordercolor='midnightblue'>"; print "<tr bgcolor='midnightblue'> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Emri</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Mbiemri</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Nr.Tel</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Qyteti</b></font></center></td> <td><center><font face='Verdana, Arial, Helvetica' size='1' color='ffcc00'><b>Adresa</b></font></center></td>"; print "</tr>"; while ($get_info = mysql_fetch_row($result)){ print "<tr>\n"; foreach ($get_info as $field) print "\t<td><font face=arial/><div align=center><b>$field</b></div></font></td>\n"; print "</tr>\n"; } print "</table>\n"; mysql_close($lidhja); ?> <center> </center> </body> </html> This is ok,but only to search city and name,or city and surname,not city AND name AND surname. Another is in my database i have some characters like ç,ë how can i make to use this,because they looket liek A*; with unknown characters. Thanks ecentricNick.