Graeme - you were moving in the right direction. Since the data in the field
is varchar(250), the only thing that changes is the fact that the last
number is 3 digits. Other page queries were also affected with 4 x 2 digit
numbers in the category field (eg. '37 48 49 52').
By adding '%' between each number and using 'LIKE' as opposed to '=', the
queries through PHP return the correct value.
I think is very strange as 3x numbers work fine when using spaces (' ')
between each criteria (as in '37 48 53').
The change would look something like:
SELECT description from cpProducts where category like '39%47%48%172'
There is something between each element, but PHP can not seem to handle the
third empty space.
Thanks to all for your help and ideas!
Jason Walker
jwalker@xxxxxxxxxxxxxxx
http://www.desktophero.com
-----Original Message-----
From: graeme [mailto:graeme_foster@xxxxxxxxxxxxxxxx]
Sent: Thursday, January 06, 2005 7:55 PM
To: Jochem Maas
Cc: Jason Walker; php-db@xxxxxxxxxxxxx
Subject: Re: PHP query to mysql database returns emtpy data, but
Query Browser shows records
Sorry I was in a hurry for lunch.. I meant category='39 ' OR category
'47' etc... which of course the IN clause would address.
But if it is a character string '39 47 48 172' that is required then
double check the number of spaces that are required against those that
are produced. Maybe is is a double digit and two spaces trip[le digit
and one? Without an example of the data and what is produced it's hard
to help.
graeme
Jochem Maas wrote:
> graeme wrote:
>
>> Hi,
>>
>> You have:
>>
>> $query example = SELECT description from cpProducts where
>> category='39 47 48 172'
>>
>> don't you want to add a logical operator such as OR, possibly AND
>>
>> $query example = SELECT description from cpProducts where
>> category='39 OR 47 OR 48 OR 172'
>>
>> graeme.
>
>
> whatever it is that he is trying to do - I doubt he wants to put 'OR's
> in the character string, besides which AFAIK you can't do something like:
>
> SELECT description from cpProducts where category=39 OR 47 OR 48 OR 172;
>
> (possibly the SQL above will actually return all rows because any
> number greater than zero will evaluate to true - e.g. ($x = true || 1)
> is always true regardless of the value of $x, I am assuming the same
> general logic goes for SQL or'ing)
> it should be:
>
> SELECT description from cpProducts where category=39 OR 47 OR 48 OR 172;
>
> Jason, read on for more (possible) help (well I gave it a shot but I
> don't think it will be any help, sorry):
>
>
>> Jason Walker wrote:
>>
>>>
>>>
>>> Here is the query:
>>>
>>> function ReturnPackageDescriptions($pack, $cat, $hotcat, $hotid){
>>> $comIB = $cat . " " . $pack . " " . $hotcat . " " .
>>> $hotid;
>>> $catLength = strlen($comIB);
>>> echo $catLength;
>>> $query = "SELECT description from cpProducts where
>>> category='" . $cat . " " . $pack . " " . $hotcat . " " . $hotid . "'";
>>> echo "<bR>" . $query . "<br>";
>>> echo "combined package number = " . $comIB . "<br>";
>>> $retval = "";
>>> $link =
>>> mysql_connect($config['host'],$config['user'],$config['pass']) or
>>> die("Could not connect");
>>> mysql_select_db('stc_store') or die("Unable to connect
>>> to the default database");
>>> $result = mysql_query($query) or die("Unable to
>>> pull the menu objects for main event details");
>>> echo mysql_affected_rows() . "<br>";
>>> while ($results = mysql_fetch_array($result,
>>> MYSQL_ASSOC)){
>>> extract($results);
>>> echo $description;
>>> $retval = $description;
>>> }
>>> mysql_free_result($result);
>>> mysql_close($link);
>>> return $retval;
>>> }
>>>
>>> I have some extra 'echo' statements to see the progress on the web
>>> page. If I remove the 'where' clause within the SQL statement, I get
>>> rows. But when I add the 'where' portion, no records are returned.
>>>
>>> Here is an example of what the query looks like:
>>>
>>> $query example = SELECT description from cpProducts where
>>> category='39 47 48 172'
>>
>
> I'll assume that your table has a field named 'category' - otherwise
> the statement should throw you a big error :-) BUT is it a character
> data field (i.e. does it contain text)? AND do you actually have rows
> where the value of the category field is '39 47 48 172' - in order to
> get rows returned when running your example query the value needs to
> match this string EXACTLY.
>
> Given the fact that using mysql control center give you the desired
> result the above probably was a waste of time typing. Given that fact
> the only thing I can think of is that you have a extra space floating
> about (but I can't see it in the code your provided)
>
> does the output of mysql_error() provide any feedback?
>
> (what an odd problem!)
>
>
>>>
>>> When I run the same query in MYSQL Control center or Query Browser,
>>> no problem. I use this function template for my SELECT statements.
>>>
>>> Please let me know if there is something missing from the code.
>>>
>>> Thanks.
>>>
>>>
>>>
>>>
>>>
>>> Jason Walker
>>>
>>> jwalker@xxxxxxxxxxxxxxx <mailto:jwalker@xxxxxxxxxxxxxxx>
>>>
>>> http://www.desktophero.com
>>>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> No virus found in this outgoing message.
>>> Checked by AVG Anti-Virus.
>>> Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005
>>>
>>>
>>>
>>
>
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
