PHP Syntax, will this work? 
Author Message
 PHP Syntax, will this work?

I need to capture some input from users in a form (probably in a
$string) and be able to search a MYSQL 3.23 database table for
potential matches.
It is the type of functionality available in a Search Engine, but I
need to do it locally.

For Example(user enters search text):

database, "application development", startup

My initial thoughts were to explode my string into an array and make
multiple SQL calls to the table doing something like this:
********** code***************
$connection = mysql_connect("localhost","CompanyDB","Password") or die
("no connection to DB");

while (list($value) = each(array){
  $sql = "SELECT COMPANY ID
          FROM COMPANYTABLE
          WHERE COMPANYDESC LIKE '%$value%'";
  $sql_result = mysql_query($sql,$connection) or die ("$value query
didn't run")

Quote:
}

********** code***************
So here are my questions:
1) is this a valid approach?
2) In the WHERE clause I have $value, is that syntax valid?
3) In the $sql_result line, I have $value, is that sytax valid?
4) when I run this, will the $sql_result get appended every time,
ending in
   a large array with all the results?
5) is this approach efficient? or is there a better way to do this?
6) since my search text may include double quotes, is there another
way
   to approach this problem...

I know this is a lot to swallow, but any assistance I get would be
appreciated.
Thanks.



Tue, 21 Jun 2005 01:08:58 GMT  
 PHP Syntax, will this work?
Quote:

> while (list($value) = each(array){

----------------------------^^^^^^ $array, not array, plus you're missing
the closing quote.
Also, foreach() is preferable over while() unless you're using php3.

Quote:
>   $sql = "SELECT COMPANY ID

--------------------^^^^^^^^ missing a comma.

Quote:
>           FROM COMPANYTABLE
>           WHERE COMPANYDESC LIKE '%$value%'";
>   $sql_result = mysql_query($sql,$connection) or die ("$value query
> didn't run")
> }

Also note that mysql is CAse SENsiTIVE, so the above won't work if your
table names are small or mixed-case.

Quote:
> ********** code***************
> So here are my questions:
> 1) is this a valid approach?

Yes.

Quote:
> 2) In the WHERE clause I have $value, is that syntax valid?

Yes, but don't take my word for it: RTFM and find out for sure.

Quote:
> 3) In the $sql_result line, I have $value, is that sytax valid?

Absolutely. Variables can be used almost anywhere where other values can be
used (except in a very few rare cases involving dynamic function names, or
in the case of things which must be available at compile time, like class
names in a class Foo {...} definition).

Quote:
> 4) when I run this, will the $sql_result get appended every time,
> ending in
>    a large array with all the results?

No. See the mysql section of the PHP docs. $sql_result is NOT an array, it
is a special type called a resource. You must use the various
mysql_fetch_xxx() functions to get anything out of it. The docs explain
this very well and include examples:
http://www.php.net/manual/en/ref.mysql.php

Quote:
> 5) is this approach efficient? or is there a better way to do this?

It might be slightly more efficient to create one SQL statement. Something
like:

$q = "select company, id from companytable where ";
$foo = array();
foreach( $array as $k => $v ) {
  $foo[] = "(companydesc like '%$v%')";

Quote:
}

$q .= join( " OR ", $foo );

Quote:
> 6) since my search text may include double quotes, is there another
> way
>    to approach this problem...

Escape the quotes. Again, see the docs.
http://www.php.net/manual/en/function.mysql-escape-string.php

Quote:
> I know this is a lot to swallow, but any assistance I get would be
> appreciated.

And the docs have all the answers for you. :)
See the php docs for questions about the mysql_xxx() functions and the mysql
docs for questions about mysql's SQL syntax.

--
----- stephan beal
Registered Linux User #71917 http://counter.li.org
I speak for myself, not my employer. Contents may
be hot. Slippery when wet. Reading disclaimers makes
you go blind. Writing them is worse. You have been Warned.



Tue, 21 Jun 2005 01:28:38 GMT  
 PHP Syntax, will this work?

Quote:

> I need to capture some input from users in a form (probably in a
> $string) and be able to search a MYSQL 3.23 database table for
> potential matches.
> It is the type of functionality available in a Search Engine, but I
> need to do it locally.

If you're dealing with anything larger than titles/names etc. then this is
probably interesting: http://www.mysql.com/doc/en/Fulltext_Search.html

Andr N?ss



Tue, 21 Jun 2005 02:43:53 GMT  
 PHP Syntax, will this work?
Andr,

Quote:
> > ...search a MYSQL 3.23 database table for
> > potential matches.

> If you're dealing with anything larger than titles/names etc. then this is
> probably interesting: http://www.mysql.com/doc/en/Fulltext_Search.html

> Andr N?ss

Thanks for the input, unfortunately, I am dealing with MySQL 3.23,
which does not support fulltext search.
Thanks any way.


Wed, 22 Jun 2005 01:49:23 GMT  
 PHP Syntax, will this work?
stephan,
Quote:
> Also, foreach() is preferable over while() unless you're using php3.

we're using php4, we will use foreach()...

Quote:
> >   $sql = "SELECT COMPANY ID
> --------------------^^^^^^^^ missing a comma.

thanks, my example was supposed to be COMPANYID... but that's "ok"

Quote:

> Also note that mysql is CAse SENsiTIVE, so the above won't work if your
> table names are small or mixed-case.

Thanks again...

Quote:
> > 4) when I run this, will the $sql_result get appended every time,
> > ending in
> >    a large array with all the results?

> No. See the mysql section of the PHP docs. $sql_result is NOT an array, it
> is a special type called a resource. You must use the various
> mysql_fetch_xxx() functions to get anything out of it. The docs explain
> this very well and include examples:
> http://www.php.net/manual/en/ref.mysql.php

I'll have to look into this.... finding the right docs can be a little
difficult. Sometimes I am not sure if I should be looking at PHP docs
or MySQL SQL syntax docs...

Quote:
> > 5) is this approach efficient? or is there a better way to do this?

> It might be slightly more efficient to create one SQL statement. Something
> like:

I am trying to understand your code below and I have added comments.
Am I correct in my assumptions?

Quote:

> $q = "select company, id from companytable where ";  //
> $foo = array();                                      // initialize array
> foreach( $array as $k => $v ) {                      //what is $array, $k
>   $foo[] = "(companydesc like '%$v%')";       //load $foo[] with company,id
> }
> $q .= join( " OR ", $foo );                   // is this supposed to be inside

                                                //the }?    
                                                //the last 4 lines
confuse me
I'll try to look at the docs too..

Quote:
> > 6) since my search text may include double quotes, is there another
> > way
> >    to approach this problem...

> Escape the quotes. Again, see the docs.
> http://www.php.net/manual/en/function.mysql-escape-string.php

Thanks.


Wed, 22 Jun 2005 02:10:52 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Willing to work at Home COBOL

2. Smalltalker willing to work at home

3. Help: PHP works with .php but not .html

4. php Syntax checkers?

5. Syntax bug in php-4.2.3

6. PHP to HTML Syntax Question

7. php syntax diagrams

8. Implementing letrec-syntax using only let-syntax and syntax-rules

9. mysql is not working with php

10. Does anyone have Java working with PHP?

11. passing PHP variables stops working with Redhat 9 upgrade

12. PHP no longer works after RH 9.0

 

 
Powered by phpBB® Forum Software