RE: [PHP] Help: Database Search
You can't do "WHERE Industry = 1, 2, 3" unless that's something you can
do in MySQL but not the other DB's I've used.
What you CAN do is us "IN":
WHERE Industry IN (1, 2, 3)
Or..
WHERE Industry IN ('1','2','3')
"IN" basically does a Industry = 1 OR Industry = 2 OR Industry = 3 type
thing.
If there's a more efficient way to do your query, I'd recommend it.
"IN" or even the multiple OR statements can be very intensive on a
database with a high load or tons of rows. But it's definitely the
right answer for some solutions.
If you have 8 categories for Industry and you're doing IN (1, 2, 3, 4,
5) then you might consider doing a <> 6, <> 7, <> 8 type thing (I'm
guessing you can do "NOT IN (6, 7, 8)", just never done it). The fewer
things you're checking in your WHERE clause, the less work your DB
server has to do.
HTH
-TG
> -Original Message-----
> From: Stuart Felenstein [mailto:[EMAIL PROTECTED]
> Sent: Saturday, November 13, 2004 1:16 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [PHP] Help: Database Search
>
>
> I've changed my logic around but still running into a
> sql query error.
> I've tried a number of things with no success. Here
> is the error that returns on POST:
>
> SELECT PostStart, JobTitle, Industry, LocationState,
> VendorID FROM VendorJobs WHERE (VendorJobs.Industry =
> ''1','2','3''Query failed: You have an error in your
> SQL syntax. Check the manual that corresponds to your
> MySQL server version for the right syntax to use near
> '1','2','3''' at line 2 .
>
> Here is the relevant code:
>
> $Ind = $HTTP_POST_VARS['Ind'];
> if (count($Ind) > 0 AND is_array($Ind)) {
> $Ind = "'".implode("','", $Ind)."'";
> }
> $sql = "SELECT PostStart, JobTitle, Industry,
> LocationState, VendorID
> FROM VendorJobs";
> //if ($Ind)
> $sql .= " WHERE (VendorJobs.Industry = '$Ind'";
>
> I'm not trying to be a pain here. Either I'm not
> catching a syntax error or something else.
>
> Stuart
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re:[SOLVED] [PHP] Help: Database Search
--- Stuart Felenstein <[EMAIL PROTECTED]> wrote: Okay, so what did I learn. 1) That a comma delimited list (from the array) to be used correctly in the sql statement had to use the IN word. $sql .= " WHERE VendorJobs.Industry IN ($s_Ind)"; 2) I need a space between the first quotation in the first and second sql statements. 3) Debug my code. Perhaps I need to learn more about that but i was printing out the sql statements and had the mysql_error() set for the failure. Stuart -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database Search
I've changed my logic around but still running into a
sql query error.
I've tried a number of things with no success. Here
is the error that returns on POST:
SELECT PostStart, JobTitle, Industry, LocationState,
VendorID FROM VendorJobs WHERE (VendorJobs.Industry =
''1','2','3''Query failed: You have an error in your
SQL syntax. Check the manual that corresponds to your
MySQL server version for the right syntax to use near
'1','2','3''' at line 2 .
Here is the relevant code:
$Ind = $HTTP_POST_VARS['Ind'];
if (count($Ind) > 0 AND is_array($Ind)) {
$Ind = "'".implode("','", $Ind)."'";
}
$sql = "SELECT PostStart, JobTitle, Industry,
LocationState, VendorID
FROM VendorJobs";
//if ($Ind)
$sql .= " WHERE (VendorJobs.Industry = '$Ind'";
I'm not trying to be a pain here. Either I'm not
catching a syntax error or something else.
Stuart
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database Search
--- Jason Wong <[EMAIL PROTECTED]> wrote: > Why not build your sql query according to whether or > not whatever you want is > selected or not. That way debugging is easier as > you won't be looking at > queries that contain redundant "... AND 0 ...". > Jason, How would I do that ? Care to share a short exmaple ? Thank you Stuart -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Help: Database Search
What about doing something like:
$sql = "SELECT * FROM my_table WHERE col1=$var1";
if (isset($var2))
$sql .= " AND col2='$var2'";
HTH
Graham
> -Original Message-
> From: Stuart Felenstein [mailto:[EMAIL PROTECTED]
> Sent: 10 November 2004 14:37
> To: [EMAIL PROTECTED]
> Subject: [PHP] Help: Database Search
>
>
> I am creating a database search form and results.
> Running into a problem though.
> I have two form elements, both that are fed by tables
> that have int values (1, 2 , etc)
>
> my sql statement is such:
>
> SELECT vendorjobs.PostStart, vendorjobs.JobTitle,
> vendorjobs.Industry, vendorjobs.VendorID,
> vendorjobs.LocationCity, vendorjobs.TaxTerm FROM
> vendorjobs WHERE vendorjobs.Industry = '$Ind' AND
> Date_Sub(Curdate(), interval '$Days' day) <= PostStart
> "
>
> But if the user decides to only use one of the
> elements, then I don't want the query to return
> nothing because of the "And" in the where clause so I
> have these 2 statements that set a default value
> (shown here as 0 in the event one of the two elements
> aren't selected: (Having no luck as the form still
> only works correctly if I've set both elements)
>
> $Ind = "0";
> if (isset($_POST['Ind'])) {
> $Ind = (get_magic_quotes_gpc()) ? $_POST['Ind'] :
> addslashes($_POST['Ind']);
> }
> $Days = "0";
> if (isset($_POST['Days'])) {
> $Days = (get_magic_quotes_gpc()) ? $_POST['Days'] :
> addslashes($_POST['Days']);
>
> Thank you ,
> Stuart
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database Search
On Wednesday 10 November 2004 15:37, Stuart Felenstein wrote:
> --- Jason Wong <[EMAIL PROTECTED]> wrote:
> > Why not build your sql query according to whether or
> > not whatever you want is
> > selected or not. That way debugging is easier as
> > you won't be looking at
> > queries that contain redundant "... AND 0 ...".
>
> Jason, How would I do that ? Care to share a short
> exmaple ?
A simplistic pseudo-code version:
$sql = "SELECT WHERE 1";
if ($something = "selected") {
$sql .= "AND something";
}
...
...
$sql .= "... rest of query";
Anyway, someone has already posted a more detailed example.
--
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-general
--
/*
Hmm... Which would do a better job at driving physicists crazy? Travel
faster than light, or a floating-point boolean value?
-- Michael Mol
*/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database Search
On Wednesday 10 November 2004 14:37, Stuart Felenstein wrote: > But if the user decides to only use one of the > elements, then I don't want the query to return > nothing because of the "And" in the where clause so I > have these 2 statements that set a default value > (shown here as 0 in the event one of the two elements > aren't selected: (Having no luck as the form still > only works correctly if I've set both elements) Why not build your sql query according to whether or not whatever you want is selected or not. That way debugging is easier as you won't be looking at queries that contain redundant "... AND 0 ...". -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-general -- /* This system will self-destruct in five minutes. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database Search
--- Jon Hill <[EMAIL PROTECTED]> wrote: > You might want to try looking up the syntax for left > join queries. > I think this might be what you are after. > > http://dev.mysql.com/doc/mysql/en/JOIN.html > It has nothing to do with left joins. It works fine provided I do one of two things, make selections on both form elements, or change the where statement to "or" instead of and. Stuart -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database Search
You might want to try looking up the syntax for left join queries.
I think this might be what you are after.
http://dev.mysql.com/doc/mysql/en/JOIN.html
On Wednesday 10 November 2004 14:37, Stuart Felenstein wrote:
> I am creating a database search form and results.
> Running into a problem though.
> I have two form elements, both that are fed by tables
> that have int values (1, 2 , etc)
>
> my sql statement is such:
>
> SELECT vendorjobs.PostStart, vendorjobs.JobTitle,
> vendorjobs.Industry, vendorjobs.VendorID,
> vendorjobs.LocationCity, vendorjobs.TaxTerm FROM
> vendorjobs WHERE vendorjobs.Industry = '$Ind' AND
> Date_Sub(Curdate(), interval '$Days' day) <= PostStart
> "
>
> But if the user decides to only use one of the
> elements, then I don't want the query to return
> nothing because of the "And" in the where clause so I
> have these 2 statements that set a default value
> (shown here as 0 in the event one of the two elements
> aren't selected: (Having no luck as the form still
> only works correctly if I've set both elements)
>
> $Ind = "0";
> if (isset($_POST['Ind'])) {
> $Ind = (get_magic_quotes_gpc()) ? $_POST['Ind'] :
> addslashes($_POST['Ind']);
> }
> $Days = "0";
> if (isset($_POST['Days'])) {
> $Days = (get_magic_quotes_gpc()) ? $_POST['Days'] :
> addslashes($_POST['Days']);
>
> Thank you ,
> Stuart
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Help: Database Search
I am creating a database search form and results.
Running into a problem though.
I have two form elements, both that are fed by tables
that have int values (1, 2 , etc)
my sql statement is such:
SELECT vendorjobs.PostStart, vendorjobs.JobTitle,
vendorjobs.Industry, vendorjobs.VendorID,
vendorjobs.LocationCity, vendorjobs.TaxTerm FROM
vendorjobs WHERE vendorjobs.Industry = '$Ind' AND
Date_Sub(Curdate(), interval '$Days' day) <= PostStart
"
But if the user decides to only use one of the
elements, then I don't want the query to return
nothing because of the "And" in the where clause so I
have these 2 statements that set a default value
(shown here as 0 in the event one of the two elements
aren't selected: (Having no luck as the form still
only works correctly if I've set both elements)
$Ind = "0";
if (isset($_POST['Ind'])) {
$Ind = (get_magic_quotes_gpc()) ? $_POST['Ind'] :
addslashes($_POST['Ind']);
}
$Days = "0";
if (isset($_POST['Days'])) {
$Days = (get_magic_quotes_gpc()) ? $_POST['Days'] :
addslashes($_POST['Days']);
Thank you ,
Stuart
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help: Database search and results
Ooops, not solved yet. --- Stuart Felenstein <[EMAIL PROTECTED]> wrote: I have created a database search and results set. Things seem to work fine until I add the join statements into the results page query string. If I don't use the joins , the search works great. It returns the correct results based on the criteria input. If I add the joins in, then basically all i get regardless of my search criteria is a complete dump of every listing. NOW - I don't expect any suggestions based on my above question, but was not sure if it was okay to post the code. Both pages total about 325 lines. If it's okay to post it I will. Thank you , Stuart -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SOLVED Re: [PHP] Help: Database search and results
Wow~! I'm getting good. --- Stuart Felenstein <[EMAIL PROTECTED]> wrote: > I have created a database search and results set. > Things seem to work fine until I add the join > statements into the results page query string. > If I don't use the joins , the search works great. > It > returns the correct results based on the criteria > inp.. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Help: Database search and results
I have created a database search and results set. Things seem to work fine until I add the join statements into the results page query string. If I don't use the joins , the search works great. It returns the correct results based on the criteria input. If I add the joins in, then basically all i get regardless of my search criteria is a complete dump of every listing. NOW - I don't expect any suggestions based on my above question, but was not sure if it was okay to post the code. Both pages total about 325 lines. If it's okay to post it I will. Thank you , Stuart -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
