[Resolved] LIKE parameter

[jwilley]

Hello again,
I am having trouble getting my search to give me the right data when i put in only half of a first name or something... i want it to be able to recognize only half a name and give me results based upon that if someone doesn't know thier whole name or something... here is my code...

$query="SELECT * FROM membinfo WHERE id > 1";

// default condition, im assuming u have a field in table called id, else use the primary key

if($lastname!="")
$query = $query." AND lastname like '".$lastname."'";

if($firstname!="")
$query = $query." AND firstname like '".$firstname."'";

if($district!="")
$query = $query." AND district = '".$district."'";

So is there a simple solution for making it give me the full name STEPHANIE badk if i put STEPH in the query field?
Jon

[ereptur]

Originally posted by jwilley

if($lastname!="")
$query = $query." AND lastname like '".$lastname."%'";

if($firstname!="")
$query = $query." AND firstname like '".$firstname."%'";

if($district!="")
$query = $query." AND district = '".$district."%'";

[/B]

See my changes above.

[jwilley]

That worked perfectly... DUH! i should really learn this stuff better... haha...

so here's my next issue... i wanna have a dropdown box on my form they use to search but it's not working when they use the "None" value.... is there a trick to this so that if they don't want to search with the district they don't have to and still be able to use the dropdown box?

<form name="form1" method="post" action="find.php">
<p><font size="3" face="Arial, Helvetica, sans-serif">Last Name:
    <input name="lastname" type="text" id="lastname">
</font></p>
<p><font size="3" face="Arial, Helvetica, sans-serif">First Name: 
    <input name="firstname" type="text" id="firstname">
</font></p>
<p><font size="3" face="Arial, Helvetica, sans-serif">District:</font>
  <select name="district" id="district">
    <option selected>None</option>
    <option value="NW">NW</option>
    <option value="NE">NE</option>
    <option value="C">C</option>
    <option value="SE">SE</option>
    <option value="SW">SW</option>
    <option value="E">E</option>
  </select>
</p>

[ereptur]

create a value for the none option. then test the posted results for this value.
i.e.

$district = $_POST['district'];
if($district == "None") {
} else {
  query .= " AND district like '$district%'";
}

[jwilley]

Here is what i have now:

  <? mysql_connect("localhost","oataorg_ohioata","atmedical"); 
@mysql_select_db("oataorg_memdb") or die( "Unable to select database"); 


if(mysql_error()) 
echo mysql_error(); 

$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];
$district = $_POST['district'];

$query="SELECT * FROM membinfo WHERE id > 1";

// default condition, im assuming u have a field in table called id, else use the primary key

if($lastname!="")
$query = $query." AND lastname like '".$lastname."%'";

if($firstname!="")
$query = $query." AND firstname like '".$firstname."%'";

if($district_== "None") { 
}else{ 
$query_=_$query."_AND_district_=_'".$district."%'";
}

$result=mysql_query($query) or die (mysql_error ()); 
if(mysql_num_rows($result)==0){ 
print ("No results found. Check your search parameters and try again."); 
}else{ 
// do the results display 
}
?>

And it is giving me a
Parse error: parse error, unexpected T_VARIABLE in /home/oataorg/public_html/find.php on line 35

line 35 is:

$query_=_$query."_AND_district_=_'".$district."%'";

any thoughts? did i totally miss a step?

[ereptur]

if($district == "None") { 

}else{ 

$query = $query." AND district = '".$district."%'";

}

You have a bunch of underscores (_) in your code. Get rid of them and you should be good to go.

Also, you're not showing it, but I am assuming that you have altered your form at <option selected>None</option> to read <option value="none" selected>None</option>

[jwilley]

ok, so apparently when i copy and paste form the PHP code here on the forum it adds some crap underscores to the code that i can't see in dreamweaver mx... i fixed them and it works for the most part now...

except that now when i choose NW distict and my name which should return a valid search it doesnt... hmmmmm

or if i just try and search with a value form the dropdown box it doesn't return any results...

hmmmmmmm

[ereptur]

well, since your district values are hard-coded and only available from a drop-down list, you can change the like parameter in your query to be a = without the % sign.

Try that and let me know if that fixes it.

[jwilley]

You are a genious my friend!

thanks for real though...

all is well...

and now for my next task.... i will attempt to use my limited knowledge to create a login session so each user can update his or her information automatically...

any thoughts?

[ereptur]

Well, if you are wanting people to login each and every time, then you will want to explore using session, as those are the most efficient means of doing so. If you want people to be able to auto-login each time, then explore using cookies. Both have their own advantages/disadvantages.

Check out the articles section of this site and the pre-made scripts section. Not all of them will work for you, but if one does then that's great. If not, then you can at least disect them and get an idea of how they work.

[jwilley]

So if i want to order my results by last name alphabetically would i do this?

$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];
$district = $_POST['district'];

$query="SELECT * FROM membinfo WHERE id > 1";

// default condition, im assuming u have a field in table called id, else use the primary key

if($lastname!="")
$query = $query." AND lastname like '".$lastname."%'";

if($firstname!="")
$query = $query." AND firstname like '".$firstname."%'";

if($district == "None") { 
}else{ 
$query = $query." AND_district = '".$district."' order by lastname";
}

seems to work now but the database is already ordered alphabetically.... but when i put in a new record will it put the name in the MySQL database alphabetically?

I tried doing pagination also to no avail... seems way to complicated for me... no easy tutorials out there on it...

[ereptur]

I would put the order by clause seperate from the district clause otherwise, if the district is not specified, neither will the order by.

As far as pagination is concerned, I haven't really ever tried writting a script that does this so I'm afraid that I wouldn't be much help in steering you in the right direction.

I took over my website (http://www.cribzero.com) from a friend who wrote the pagination code in the archive section. I have attached the source file to this post. Maybe you can gleam something from it.

[jwilley]

I would put the order by clause seperate from the district clause

ugh.... so where and how would i do this?

sorry to be a complete idiot...

like this maybe?

if($district == "None") { 
}else{ 
$query = $query." AND_district = '".$district."' order by lastname";
}
$query = $query."ORDER BY lastname";

that is a COMPLETE guess on how to do that...

Jon

[ereptur]

Originally posted by jwilley

if($district == "None") { 
}else{ 
$query = $query." AND_district = '".$district."'";
}
$query = $query." ORDER BY lastname";

[/B]

see changes above

[jwilley]

Of course, that works perfectly... and to think i almost had it right!

here's my next ?

I have my update page for the secetary to update all the info and i want it to ALTER TABLE membinfo ORDER BY lastname (re-order the table by lastname everytime she inputs a new member)... is this how i do it? cuz this doesn't seem to be working...

$sql = "INSERT INTO membinfo (id,firstname,lastname,membernumber,license_number,address1,address2,address3,city,state,zip,workplace,workaddress,workcitystatezip,class,district,job_description,addschool,addschool2,addschool3,pager,cell,home,work,fax,email) VALUES (id,'$firstname','$lastname','$membernumber','$license_number','$address1','$address2','$address3','$city','$state','$zip','$workplace','$workaddress','$workcitystatezip','$class','$district','$job_description','$addschool','$addschool2','$addschool3','$pager','$cell','$home','$work','$fax','$email') and ALTER TABLE membinfo ORDER BY lastname"; 

It's that last part where i'm obviously getting it wrong... i took the code from when i had PHPMyAdmin do it for me yet i think i'm putting it in the wrong place...

thanks in advance...
jon

[ereptur]

actually, you would issue the ALTER TABLE statement in another query.

$sql = "INSERT INTO membinfo  (id,firstname,lastname,membernumber,license_number
,address1,address2,address3,city,state,zip,workpla
ce,workaddress,workcitystatezip,class,district,job
_description,addschool,addschool2,addschool3,pager
,cell,home,work,fax,email) VALUES  (id,'$firstname','$lastname','$membernumber','$lic
ense_number','$address1','$address2','$address3','
$city','$state','$zip','$workplace','$workaddress'
,'$workcitystatezip','$class','$district','$job_de
scription','$addschool','$addschool2','$addschool3
','$pager','$cell','$home','$work','$fax','$email'
)";
$sql2 = "ALTER TABLE membinfo ORDER BY lastname";

mysql_query($sql);
mysql_query($sql2);

but since you are sorting on the output there is really no need to sort after each input as well as that will just increase the amount of time it takes your script to run.

[jwilley]

But to keep the database oganized do you think this would be good logic? so that when the secretary downloads it as a CSV file for Excel to print labels and stuff from it is alphabetized? just thinking...

my next issue... i am setting up a page where she can just download the database to a CSV file and i am getting an error i don't like...

<? header("Content-Type: application/vnd.ms-excel"); 
header("Content-Disposition: inline; filename=\"membership.xls\""); 
//pconnect file 
mysql_connect("localhost","oataorg_ohioata","atmedical"); 
@mysql_select_db("oataorg_memdb") or die( "Unable to select database"); 
$q="select lastname, firstname, district"; 
$r=mysql_query($q); 
echo ("Last Name". "\t" ."First Name". "\t" ."District"."\n"); 
while ($a=mysql_fetch_array($r)){
echo ($a["lastname"]. "\t" .$a["firstname"]. "\t" 
.$a["district"]. "\n"); 
}//end while 
?>

here is my error:
Last Name First Name District
<br />
<b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/home/oataorg/public_html/memdatabase/exportexcel.php</b> on line <b>9</b><br />

thoughts?

[ereptur]

well, in your situation where you would like the secretary to be able to download a csv version of the db, then I would recommend your method.

As for the error, it is a problem with your SQL syntax.

$q="select lastname, firstname, district";

The statement doesn't say from what table to pull in the requested info.

Also, when you post code to a public forum, you may want to remove the username and password for your database. I know that this is probably a non-production box, but it still just a good idea not to post that data.

[jwilley]

Ahhhh, yeah, you are probably right... this is just a test run but still good point...

ok, so here is my final dilema of the night... my eyes can't hack it much longer...

This is what i came up with from the earlier help... something is wrong with my syntax obviously bc it is not working... no error, just not working...

  // here if no ID then adding else we're editing

  if ($id) {

$sql = "UPDATE membinfo SET firstname='$firstname',lastname='$lastname',membernumber='$membernumber',license_number='$license_number',address1='$address1',address2='$address2',address3='$address3',city='$city',state='$state',zip='$zip',workplace='$workplace',workaddress='$workaddress', workcitystatezip='$workcitystatezip',class='$class',district='$district',job_description='$job_description', addschool='$addschool', addschool2='$addschool2', addschool3='$addschool3', pager='$pager',cell='$cell',home='$home',work='$work',fax='$fax', email='$email' WHERE id=$id";

  } else {

$sql = "INSERT INTO membinfo (id,firstname,lastname,membernumber,license_number,address1,address2,address3,city,state,zip,workplace,workaddress,workcitystatezip,class,district,job_description,addschool,addschool2,addschool3,pager,cell,home,work,fax,email) VALUES (id,'$firstname','$lastname','$membernumber','$license_number','$address1','$address2','$address3','$city','$state','$zip','$workplace','$workaddress','$workcitystatezip','$class','$district','$job_description','$addschool','$addschool2','$addschool3','$pager','$cell','$home','$work','$fax','$email') ALTER ORDER BY lastname";
$sql2 = "ALTER TABLE membinfo ORDER BY lastname";

  }

  // run SQL against the DB 

  $result = mysql_query($sql);
  mysql_query($sql2);

  echo "Record updated/edited!<p> <a href=\"adminupdate.php\">Back to update page</a>";

why can't this stuff be easier for me! haha...

[ereptur]

try this, after each of your mysql_query()'s.. instead of putting a semicolon, finish the statement like this:

$result = mysql_query($sql) or die(mysql_error());

This will display any errors that the database is throwing.